SQL Server Migrations and Upgrades
Database upgrades and migrations happen every 3-5 years. Or less. Which means if you are using your internal staff, those people haven’t done this type of task a lot.
We have. We typically run 2-5 simultaneous SQL Server Migration and Upgrade projects in tandem for various clients.
​
Why is that important? Because to do this right – you only get one try! Or rollback is real nasty. A ton of downtime. The manager leading the project will not look very good.
​
Someone asked me recently about a couple about SQL Migrations or Upgrades.
​
I’ve been the lead SQL Server DBA on few large projects lately. The latest upgrades were for companies of $1 billion, $10 billion and $200 billion sizes. CDMBase leads all three SQL Server migrations and Upgrades.
​
All three SQL migrations went very well! Done on time. Without problems. And most importantly without nasty surprises.
Why do company sizes matter here? Billion-dollar companies have a lot more complexities than a startup.
From SQL Server perspective if you can handle complex migration, you can surely knock out small ones.
-
In one case I migrated from one data center to another while doing an SQL upgrade. In another case I upgraded 18 SQL Servers from SQL2008R2 to SQL2016 – including PROD, Reporting (with 5000 daily reports), DataWarehouse, DEV, QA, UAT, Test, and DR environments) in a record amount of time for this size of the organization and considering the line of business they are in.
​
-
I estimate this company will not upgrade for another 10 years. Because it's a massive undertaking.
​
-
What is the t key to SQL Upgrades project success? Preparation. Prep work is going to take 80-90% of the time. SQL Server install and setup is going to be an easy part.
​​
HERE ARE COUPLE OF ITEMS TO CONSIDER when migrating SQL Server (event if you go with someone else)
1. Failover clustering on VMWare
There are few settings in virtualization that you need to get right for SQL Servers. Make sure you talk to VMWare person to get some specific settings turned on for SQL Server. If you just google “VMware SQL Server best practices”, you will find them. The key here is to NOT to allow other guest OS’es to take SQL Server resources.
​
Some of the settings are not as obvious though. Such as how CPU cores are presented. In those cases, I suggest grabbing production workload and re-running it on the NEW SQL server with setting A and then with setting B. Then compare results.
​
I have used the SQL Distributed Replay feature for this – it worked great! It’s a lot of steps and maybe your SQL Server is not that important to go through extra pain and time this will take, but it is – is really worth it! I can probably even send you some Excel files that show a comparison of run1 vs. run2 and what you can see there.
SQL Distributed Replay eliminates SQL migration unpredictability
​
2. SQL Clusters and AlwaysOn
If you get pre-setup correctly – this part is going to be a breeze. Just make sure to think about how you will keep SQL security, SQL Agent jobs, linked servers synched between all nodes at all times (and No, AlwaysOn doesn’t help you there). Setting this up the first time – is easy. Keeping things in synch – requires more work. (I used some automated SQLAgent jobs for this).
​
3. SAN Config for SQL Server – I’d make this item #1 on this list
Storage is where most SQL Servers bottleneck on today. Here I want to compare how SAN theoretical advertised maximum performance compares to what I am actually seeing. Those two numbers never match – but that’s OK. The real question is how far apart are we? You can do some basic SAN testing using a program such as CrystalDiskMark. But if you playing with a serious SQL Server, you will end up doing diskspd.exe testing (built by Microsoft) and running a ton of tests on each volume. My suggestion – do not proceed with SQL installation until this is done.
​
Also, save your SAN performance metrics. SAN admin will get you your volumes. But after SQL upgrade is no longer a top item on the project list, other workloads start to creep into your SQL volumes and SAN perf mysteriously goes down. If you know what perf metrics were on day1 and ask that perf drop question quickly, there is a good chance SAN admin may move that extra workload someplace else.
​
4. Prior experience in upgrading SQL
You want to see past success. I would personally choose the SQL DBA who has done the most complex upgrades in the past. And how fast were they? I have seen upgrade projects drag out for 12 months for one SQL Server! Why do I care about the most complex upgrade? Because there is a high chance you going to run into a weird case somewhere during an upgrade (90% chance probably) which you can’t find an answer to online. For one issue, I have run into the past, if I did not go to SQLPASS conference and talked to some serious people there – I don’t know I would have figured it out myself.
​
5. Architecting High Availability Disaster Recovery site
From the SQL migration perspective, this is probably the easiest component out of all listed. Yes, it takes a bunch of steps. But it simple compared to the above items. SQL2016 makes HADR simple. I don’t know what requirements you have, but I am guessing it will be AlwaysOn replica with asynchronous synchronization and you going to want that work as possible. AlwaysOn makes this easy. You just have to put some safeguards in place so you don’t accidentally failover to the DR site.
​
This is all that I can think of for now. It's a bit early and it’s time for coffee! 🙂
​
We’d love to talk to you about any questions you may have about your SQL Upgrade or Migration project specifics (even if you don’t go with us!) Just use Contact Us form or tel to reach out.