SQL Server: migrations/upgrades – 10 things to think about

Here at Pythian we have planned and implemented dozens of migrations and upgrades for our customers. In this blog post I am trying to summarize the 10 most important things to consider when migrating or upgrading a SQL Server environment, based on our lengthy experience.
If you need to migrate/upgrade your SQL Server environment to a new server/environment but you are afraid of having long outages, losing data or degrading performance, here are the things to consider:

1. To Cloud or not to cloud: if you are considering moving to the cloud, you should research carefully around  the capabilities, constraints and limitations of the cloud solutions you are considering. For example, there are few things you could do on a “regular” server with a SQL Server instance installed that you cannot implement with Amazon RDS or Azure Database (DaaS). I am not posting any links here because things are changing very fast on the cloud currently, so you need to ensure you are up to date about this.
2. Physical to Virtual: Often, when moving from a physical box with attached local disks to a VM environment with shared storage, SQL Server performance may degrade due to multiple potential factors. It is recommended to test performance of new environments prior to Production cutover and compare to the performance on the existing one. Following configuration Best Practices in all levels of the architecture is essential (please see more details in section 5).
3. Minimal downtime during Production cutover: there are few methods to copy an instance and databases to the new environment without affecting exiting Production and then cutover with minimal downtime.
   The options range between transferring backups through portable media, setting up Database Mirroring or Log Shipping or simply backing up and restoring databases.
   The solution depends on few things such as:
   1. Current performance of your environment
   2. Database SLAs and how much downtime can be afforded
   3. Network bandwidth and capacity between existing and new environment (is there a connection between the environments at all?)
   4. Volume of data updates in your current environment
   5. Size of the databases
   6. Etc.
4. Performance baseline comparison before and after: this is a step that is usually missed during migration and upgrade projects. Performance degradation when moving to a new environment may be a common issue but we need to understand what exactly are the new bottlenecks. For that reason, it’s important to record the performance baseline from the old environment before migrating and another baseline after migration (not right away, we need the instance to “warm up” for a while, say: a week). In comparing the two baselines, fixing the problem may be much easier and faster because it will give us an indication of what to look for.
   
5. Best Practices: Microsoft and other providers or partners provide lists of Best Practices. We need to ensure we follow Best Practices in all levels: Hardware, storage, VM, OS and SQL Server. Some examples of Best Practices:
   – VMWare: VMWare Best Practices
   – Storage: Disk partition alignment for SQL Server
   – SQL Server: Best Practices list
6. Testing phase: before implementing the real cutover, it is strongly recommended that you implement a test migration without affecting current Production environment or as required. This step may take longer and add time to the schedule but it will reduce or (hopefully) prevent issues that may occur during the cutover (i.e.: missing components, application not being able to connect to SQL Server, etc).
   
7. Rollback and Plan B: when preparing for a migration or an upgrade of a critical Production environment, it’s always important to plan for a rollback in case something goes wrong. Even more important is to understand how much time a rollback would take, and the implications (technical and business wise).
   
8. Upgrade plan: if you are planning to upgrade the SQL Server version or the application version at the same time as the migration is happening, you should ensure that all components can be upgraded ahead of time and decide if any database can stay in older compatibility level. There’s the Upgrade Advisor that can help you check things but there is never a 100% verification unless you test all part of the application(s).  Well.. If there’s not a lot of code running, you may be able to trace everything and have 100% verification of the code, but this rarely happens.
   
9. HA and DR planning: When migrating to a new environment, it is probably time to redesign the HA and DR plan in advance. Some of the HA/DR SQL Server native solutions: Cluster, Always On Availability Groups (SQL 2012+), Database Mirroring (to be deprecated in future versions), Log Shipping, Replication, backups. There are other non-SQL Server solutions such as VM or storage mirroring as well as 3-rd party and cloud solutions. You can combine some of the HA/DR solutions together in order to meet higher SLAs as well.
   
10. Refresh your monitoring abilities: since you are moving to a new environment, it’s probably also time to refresh your monitoring tools if required. A good monitoring tool will notify you about the right issues and on time, preferably one that can also do pro-active monitoring and help you prevent outages. It is important to be able to monitor hardware and storage, network, Virtual Machine (if applies), Operating System and SQL Server instance(s) so that you can always stay on top and understand in which layer there is a problem. You will need to make sure that someone gets the notifications and is able to connect on a timely manner based on your system’s SLAs to fix issues.
    

Discover more about our expertise in SQL Server.