Environment normalization and challenges

When you start working in the cloud, you end up making choices, of course, for how you want to implement things.  If you look at options for your SQL Server, there are more than a few different ways to approach that type of implementation.  From virtual machines and container-oriented configurations to Software and Platform as a service to fully managed instances.  I’ve even seen some cases where there’s a bit of a mix of these depending on your applications, support needs and the like. 

When you add to this the fact that your SQL Server is probably talking to other databases (other SQL Servers or other installation types or other platforms), things are starting to look pretty typical for most of us working in this area now.  

One thing that I’ve found can really up your game in understanding the platforms is moving a database and application from one implementation to another – or even one major feature-set to another.  In other words, moving your application and supporting database infrastructure from a VM-based instance to a hosted solution (RDS, Azure, etc.) is a great way to learn about the differences in implementations. 

We’re going through one such move now, and have just completed a few other phases of doing just that.  We’ve taken the time, too, to double check security, best practices and such.  It’s interesting how much things change not only between versions (we’re updating the engine version along the way) but also with options for security and managing the instances.  

In the move, for example, we’re moving the instance to a completely off-the-public-net segment and adding an application firewall to the mix for the web servers.  This has shown the light on legacy ports and access points that had previously been used over the years at this company.  These weren’t needed any more, but it was one of those “if it ain’t broke, please, please don’t touch it!” things at the company. 

At the same time, the new hosting environment (managed services) has forced the reevaluation of all user IDs, access techniques, legacy applications, etc.  What’s more, features have to be re-established to make sure they’re supporting the applications.  From encryption set up to full-text indexes that need to be double-checked, from firewalls to ACLs.  

We’ve discovered features that were enabled, user names no longer around that were still active, temporary working tables that were set up for some sort of housecleaning.  All sorts of things.  In addition, some features are implemented that we can take better advantage of now, and there are just flat out better approaches to some of the coding techniques. 

Clearly this can get out of hand.  I can hear the pointy-haired-boss now – “I thought we were just moving to a new database server – NOW look what you’ve done!”  Yeah, kinda.  But you can make notes too.  You don’t have to do it all now, but you can look for changes, look for things that need to be revisited.  Most feature activations can be done in phases, especially if you plan for the date that you want to return later and activate TDE or want to clean house on those tables, or need to re-evaluate the 13,425,678 indexes that are on that simple table.  

But during that conversion – that’s your time to get a reset, based in reality look at the features and setup of that SQL Server, find out what and how it’s being used.  Then use that migration and update or move process to get a handle on how new features are working, and how they integrate into your future projects.