Productivity Hacks: Migrate in Minutes to the Most Current Version of SQL Server
In July 2019, SQL Server 2008, Microsoft’s relational database management system (RDMS) application, reached end-of-life. SQL Server 2012 is slated to go end-of-life in 2022. Once an application reaches its extended support end date, there are no longer patches, security updates, or support from Microsoft. If your organization is using a version of SQL that is or is soon to be unsupported, it’s almost guaranteed that your security risk will increase, your productivity will be impacted, and your data protection compliance jeopardized. Over time, these risks will escalate, so it’s important to take action.
Organizations are looking to migrate to a newer version of SQL Server for many reasons. It could be the end-of-service event, which is a very compelling reason because there’s a significant business justification behind it. Database administrators (DBAs) generally want to move up to the latest version because they see the application, its performance, and security benefits.
No matter the reason behind it, when administrators get the approval to upgrade, they must deal with complex details and logistics around how to move from their legacy version of SQL Server to a modern version with the least amount of downtime possible. (Upgrading is always a tough sell to users because it means the application is going to be unavailable for a period of time.)
To make the cutover more challenging, the business will usually add time requirements such as: you can only take the application down between the hours of 12 and 2 on a random Tuesday. This means that administrators need to transition as quickly as possible (so quickly that users don’t even notice) while also ensuring that they are doing so in a safe and secure manner.
To help my DBA friends, here are two pieces of advice that come with links to timesaving (and stress-decreasing) hacks:
Plan Your Migration Right with a Comprehensive Checklist
To reduce the pain of migrations, do thorough upfront preparation work. By getting everything ready to go and asking questions (even though they may not have anything to do with the product at hand), you usually start to uncover things – things that if you didn’t do the prep work or ask the questions, you’d miss.
For example, many years ago I remember getting caught on something with a migration. An application vendor said the database files couldn’t be put onto a mount point for SQL Server. This made zero sense to me. I had been building all my SQL Servers with mount points because that was the new way to add drives to SQL Server so you wouldn’t exhaust your drive letters. Had I not found out that information ahead of time, I would have been in an unsupported state by the vendor and had to redo all of the work.
If you miss key elements in the preparation, it can lead to having to scrap the entire migration because something didn’t work properly or a barrier was missed. That failure puts the database administration team’s reputation at risk. Leaders will ask: Why didn’t you understand that? Why didn’t you uncover that? Why didn’t you know that? These migration failures are often attributed to some weird requirement that no one understands or knows about (other than some person with deep institutional knowledge, who has probably left the company since the initial application was adopted).
So, to help give the database administration team a helping hand, I’ve actually created my own detailed checklist—a living document that I give to customers to ensure they’ve thought of everything. I’ve also made the checklist available to the online SQL community. You can find it here.
Automate Your Migration with a Working Script
Automating the SQL migration is going to speed up the process. You can basically have everything prepared and ready to go ahead of time. Then you come in on the day of the migration, hit a button, and watch everything move over.
Last year, when SQL server 2008 and 2008 R2 officially reached end-of-life, there was a big push by Microsoft to move customers into Azure. Rubrik did some work with Microsoft to help accelerate customers’ cloud migrations. This project was the start of a script. That core code blossomed and became a hack to help our customers.
It’s the same basic principles used to set up log shipping in Rubrik, which helps cut down on the effort of backup and restore because Rubrik has taken care of that for you. You just have to set up the relationship between point A and point B. That process is all of eight clicks, and even less work if you do it through PowerShell. Native log-shipping from Microsoft to set up for one database is a 24-step process. Rubrik reduces it down to just eight clicks. If we do it with PowerShell, it’s maybe 20 lines of code.
I have created a Jupyter notebook with code examples of how to backup, restore, export, live mount, set up log shipping, and then finally complete a database migration—utilizing all the aspects from before.
That script is on GitHub now. I hope that it saves you time and headaches.
SQL Hacks in Action
I’ve been working with various customers to help them cut their migrations down to minutes, using the checklist to plan and the scripts to automate. In one case, a customer had to migrate a database from a legacy version of SQL Server to something modern. The process needed to go from one server to multiple SQL Servers and into an availability group. They had to have the least amount of downtime possible. They were able to migrate the database utilizing Rubrik’s log shipping functionality, which basically staged everything for us. The script I gave them, which utilizes Rubrik PowerShell and a little bit of T-SQL, was able to cut the database over from an old SQL Server into the multiple target servers and add it into the availability group in under a minute.
Another customer is utilizing a few different versions of my script to do a migration of their medical software application. They’ve been testing it, and what I hear is: This is going to save us so much time because you’re handling all of the hard work upfront. All we have to do is just cut this thing over and it’s all just push-button.
If you’re interested in more productivity hacks, check out my on-demand session FORWARD Digital Summit session: Operationalize Your SQL Server Backups with Rubrik.