Someone asked me recently about a couple of points about SQL Migrations or Upgrades.
I’ve been lead SQL DBA on few large project lately. In one case company just reached $1 billion in another is about $10 billion and the latest was one that is close to $200 billion. I was lead DBA on all three.
All three SQL migrations went very well!
Why do dollars matter here? Billion dollar companies have a lot more complexities than a startup with 50 people. From SQL 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 10years.
But enough about me.
The key in SQL Upgrades projects success is preparation. Prep work is going to take 80-90% of the time. SQL install and setup is going to be an easy part.
HERE ARE COUPLE OF ITEMS TO CONSIDER (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 taking SQL Server resources.
Some of the settings are not as obvious though. Such as how CPU cores are presented.
In those cases, I suggest to 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 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 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 SQL migration perspective this is probably easiest component out of all listed. Yes, it takes a bunch of steps. But it simple compared to 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 DR site.
This is all that I can think of for now. Its bit early and it’s time for coffee! 🙂
I’d love to talk to you about any questions you may have about your SQL Upgrade/migration project specifics (even if you don’t hire me!). Just use contact us form below (it will come directly to me.)
The breadth and depth of knowledge of the Microsoft SQL Database platform and ancillary technology stack that Mark brought, has already yielded some very impressive results from both a physical and logical architecture perspective.
Mark was able to quickly identify and fix several SQL performance issues we had been struggling to trace for several weeks. Additionally, he made several other recommendations and configuration changes that had a significant positive impact on database performance.
After a weekend of work, the customer realized a 60% decrease in CPU utilization as a direct result of Mark's work.
He has mastered the understanding of complex, heavy load TSQL transactions. When tuning, database structure, indexes, stored procedure, best practices, up-time, and smooth performance are all important, Mark is the type of DBA who truly knows how to keep these high standards.