I was really stoked to deliver a session at Microsoft Ignite with my long-time friend and fellow Head Geek
™, Tom LaRock, on the topic of migrating an on-premises SQL Server database to the Azure cloud. You can watch the session on demand
here. In addition, Microsoft MVP and SQL Server expert David Klee provides an excellent recap on each of the major elements Tom and I discussed. Check out
David’s blog post here.
David did such a good job I felt like it would be better to provide more tips and tricks here in this blog post based on two major points that had to be excluded due to time constraints, rather than write a summary of our session and thereby duplicate David’s efforts. But what two points did Tom and I leave out? Here they are:
- What if I want a quick ‘n dirty migration to the cloud? Tom and I discussed how to conduct a deliberately planned migration that mitigates as much risk as possible and also helps to save dollars in the process. But what if you don’t need something elaborate?
- What are the Microsoft tools available to move my data or database from on-premises to the Azure cloud? Our session was primarily about the planning and steps needed for a successful migration. But at the step where data moves from your local server to the cloud, we said “Magic happens here,” vaguely waving our hands around this step in the plan. 😊
So let’s quickly touch on these two points.
Disposable or Quick ‘n Dirty Azure Migrations
There are a handful of different ways to quickly and easily migrate a local SQL Server database to the Azure cloud. Let’s touch on those and, where needed, provide a bit of added commentary:
- Move your VM from the ground to the cloud: This approach is also called “Lift and Shift” or “Rehosting.” In this situation, you simply take a virtual machine dedicated to SQL Server from the local data center and put it into an Azure Virtual Machine. No muss and no fuss. I only recommend this approach with simple, small, or disposable databases and VM estates. The reason for this recommendation is, while quick and easy, rehosting your VMs to the cloud means you don’t really reap any of the benefits of cloud computing, such as easier administration or greater scalability, while also facing potentially increased costs. It’s not risky per se, but you’d be surprised how many other organizations were disappointed after using this approach.
- Backup and Recovery: Another method that quickly springs to mind for many is to simply take a database backup from your on-premises SQL Server and restore it to the cloud. This is problematic because you cannot use this method on an Azure SQL Database (Azure SQL) target. You can, however, use this method for Azure SQL Database Managed Instance (Azure SQL-MI), which has its own unique SKU’s and costing structure. So this approach will work like a charm in certain situations, as long as the situation means using Azure SQL-MI. Again, this isn’t risky per se and I personally prefer Azure SQL-MI, but it might not be what you originally planned.
- PowerShell and DBATools.io: If you can do it with a GUI on Windows, then you can do it with PowerShell. And probably a whole lot more. I’ve been a strong advocate for database professionals to learn and use PowerShell for automation. It’s the bomb. But here’s even better news for people who are lazy like me—someone smarter and less lazy than us has already written those scripts. In fact, they’ve written more than FIVE HUNDRED scripts, all available on https://dbatools.io. In particular, check out the cmdlet Start-DbaMigration. But if this website is new to you and/or you’d like to learn PowerShell, then be sure to explore all of the other great features of this site and community.
- SQL Server Management Studio (SSMS): There are a few quick n’ dirty methods built right into SSMS to get you up and running in Azure pronto. For example, many of the old school ways of moving data around work just fine with an Azure target. So you can use options like Import/Export wizard, DACPACs, generated SQL scripts, and the like. They all work great for small databases. In addition, I constantly run into DBAs and database pros who only run the version of SSMS included with the version of their production SQL Server databases. So, it’s not at all uncommon to see IT organizations who are running SQL Server 2014 and only have that version of SSMS. Bad move! Update immediately. You’re missing out on several new features, including features specifically put in place to make ground to cloud migrations quiet and easy (image below).
My only caveat for the migration methods detailed above is you don’t use any of these for big databases. What’s big in this case? I think the answer varies somewhat depending on your local internet provider. But in my case here in the USA, on a solid backbone, is somewhere between 20 to 30 Gb. Then again, I generally have 500 to 600 Mb/sec bandwidth. So you mileage may vary—a lot.
Tools for Full Azure Migrations
The previous discussion flows nicely into the topic of moving data and databases to Azure using “built for purpose” tools. These are tools that, while they can work quickly, you should consider them to include more rigorous features, checks, and processes. I’ll give a quick rundown of this set of tools from simplest to most sophisticated:
- Azure Data Box: Let’s say you have a massive 350Tb SQL Server database you want to move to the cloud. Even with a fast internet connection, moving this data over the wire could end up taking a long time. Microsoft’s solution is to simply send you a big storage appliance in a box. Just copy the database locally and mail it back. Voila! You’re online 24 hours later. Azure Data Box isn’t for everyone, but when it makes sense it’s like an answered prayer. Details are here.
- Azure Migrate: This portal was one of the first tools introduced by Microsoft to help you assess your database’s suitability to migrate. This tool first reports on several issues that might hamper or trip up your migration. It’s strongest use case is lift-and-shift style migrations, with a strong focus on VMs. Learn about Azure Migrate here.
- Azure Database Migration Services (DMS): My favorite tool for a large migration project, DMS is a super set of earlier tools including the features found in Azure Migrate and Azure Database Migration Assistant (a separate tool that helps assess a database’s readiness for migration), with extra features added in for good measure. DMS supports migration from SQL Server, PostgreSQL, MySQL, Oracle, and MongoDB in on-premises and other cloud environments to Azure targets. Plus, it also allows you to perform a one-time data sync for an immediate turn-key deployment or continuous data sync for a phased deployment. Learn more here.
- SQL Server Migration Assistant (SSMA): Not strictly a cloud migration tool, SSMA is instead a tool to help you migrate from another database platform, such as DB2 or MySQL, and onto SQL Server or Azure SQL. It’s incidentally helpful since it works well for on-premises databases or cloud-based databases. This tool is especially useful for translating procedural code, such as Oracle PL/SQL packages into SQL Server stored procedures. However, due to the highly variable nature of code, it’s prudent to expect an 80/20 successful translation ratio. Every such migration I’ve seen or participated in has taken at least a couple weeks of manual effort to translate the most complex of the procedural code. Get more info here.
- Azure Site Recovery: Let’s say you need to migrate not just a SQL Server database, but also the app tier, and front-end servers. That’s a lot of moving parts with a high degree of complexity. The good news is Azure Site Recovery is up to the task. Get more details here.
Each of these latter tools are for bigger and/or more sophisticated migration projects, each with their own scenarios where they shine. As you gain experience, I suggest focusing on using either DBAtools.io, PowerShell scripts, or SSMS features for moving small or simple database or the Azure Database Migration Services for large and complex databases.
Want More Azure Migration Information?
As I mentioned at the start of this blog post, start by watching our Microsoft Ignite session on demand
here and/or by reading
David Klee’s great blog post here.
You can watch Tom and I in an hour-long session on the topic where we go into greater detail, and demo how SolarWinds tools can make migrations much easier and more successful, at
Lab #95. In addition, I have several migration related webinars posted at
https://SentryOne.com/webinars. (SentryOne is now a part of SolarWinds!)
Finally, Microsoft has loads of great learning resources about Azure migration. But to make things easy for you, start at
https://datamigration.microsoft.com/ where you can link to all the other topics I discussed and much, much more.
