Migrating databases to the cloud takes careful planning, as we’ve discussed in various SentryOne webinars and blog posts over time. And having the right tools helps, too. Understanding the migration resources that are available can speed the migration and yield better results. As part of the one-day SentryOne Accelerate 2020 virtual event that we hosted in October 2020, I led a discussion with other SQL Server experts who weighed in on some of their favorite tools for planning and executing cloud database migrations.
This illustrious panel of experts included:
- John Sterrett, principal consultant and founder of Procure SQL LLC, Microsoft Certified Trainer (MCT), and Microsoft MCSE: Data Platform
- Erin Stellato, principal consultant at SQLskills and Microsoft Data Platform MVP
- Dustin Dorsey, director of data management at LifePoint Health
- Patrick Kelley, customer success engineering manager at SentryOne
Here is an excerpt from our conversation, which not only highlights some valuable tools for migration but also provides some rich context for the scenarios in which you’d use them.
One more note: During this session, we discussed the Microsoft Database Experimentation Assistant (DEA): This is such an important tool that we’ve developed a SentryOne webinar on the topic, Introduction to the SQL Server Database Experimentation Assistant, co-presented by John Sterrett and me and now available on-demand.
Beyond the SQL Server Database Experimentation Assistant
Kevin Kline: What are some migration tools that you’ve found useful in addition to the Database Experimentation Assistant?
John Sterrett: For migrating databases over, there are quite a few. For example, I have a pretty good collection of Azure SQL Databases, and if I want to go through the process of automating that migration process, my favorite way is to use the dbatools PowerShell component because you can go through the process of building the backups and importing them.
Kevin Kline: I’ve personally used the Azure Database Migration Service. (And note that there are a lot of acronyms out there, so be careful not to confuse this with similar sounding names.) One thing that’s really cool about that particular set of tools is that you can not only migrate SQL Server but you can also move databases to other popular database platforms. You can use this tool to move, say, an on-premises installation of Postgres to Azure. Or you can use it to move an Amazon RDS instance of SQL Server over to Azure SQL Database.
Another big advantage of Azure Database Migration Service is that you can turn a switch and have it do a single-sync turnkey migration, in which you can turn one database that is on-premises “off” and turn the cloud database “on,” or you can set it up so that it keeps the two synchronized as you go along. Maybe you start in the West region with your migration but continue to run the Central and Eastern regions on-premises, so you have to synchronize the on-prem data to the cloud frequently. Then, you later migrate the Central region, while still synchronizing the Eastern region. And finally, you migrate the Eastern region so that all three regions are now in the Azure cloud. I like that it can support both of those scenarios. Any other tools anyone uses?
Patrick Kelley: I can speak to how I have helped other users go through a migration. One of the gotchas with a cloud migration is future planning. Let’s say you’re doing your workload analysis only on current state and you throw everything up in Azure—or whatever cloud platform—and it’s working great today. Is that going to be the case in a month, 3 months, 6 months later? With a solution like SQL Sentry, you have a great wealth of historical data about your workloads, so you can create baselines and look at the percentage of change over time. You can look at the last, say, 3, 6, 12 months of data and leverage that data to see where you will be in the future so that you’re not surprised from a budgeting perspective. You don’t want to be in a situation where you weren’t planning to go up to the next tier or to scale out the workload—which would incur a dual cost.
John Sterrett: SQL Sentry is also phenomenal for helping through the testing portion of a cloud migration. You can use it to very quickly go through and—with a few clicks—slice and dice information about the resource queries. You can go to the query plan and find things that have changed. Another critical component of SQL Sentry that helps with the post-migration process—as workloads are constantly evolving and changing—is having the database baseline, the snapshot, to get quick insight into why things are changing and how they changed.
Erin Stellato: And, of course, you always have your friend Query Store in SQL Server. If you’re running SQL Server 2016 or later on-prem and then moving it to the cloud, you should have that whole query history from before and after just in case you don’t have budget for a third-party tool.
Kevin Kline: Erin, any special steps needed to migrate your Query Store, along with the rest of the database?
Erin Stellato: Assuming you’re just taking the database as is—you’re just doing backup-restore—then no, because the Query Store is stored in the system tables in your user database, so it’s going with you. There nothing that needs to be done.
Kevin Kline: And, by default, doesn’t Query Store capture the 200 most recent queries?
Erin Stellato: Depending on which version you’re using, by default, you can capture every single query in Query Store, which I don’t recommend. Or you can use “auto,” which I would recommend. With the “auto” setting, anything that is insignificant in terms of execution, duration, CPU, or compile time or is less than three executions is not going to be captured in Query Store. But anything else that is ad hoc that exceeds those thresholds or is part of a stored procedure is going to be captured so that should be representative of your workload.
John Sterrett: Query Store is great if you want to capture the benchmark on the query level. If you’re doing multiple replays, for example, you can get a query-level representation of all those metrics and compare back and forth.
Importance of Building a Data Dictionary for Cloud Migrations
Kevin Kline: One aspect of planning a cloud migration that I want to bring up is the importance of building a data dictionary before moving to the cloud. When you’re moving databases to the cloud, even if you don’t plan on making significant changes, it’s a great time to look for any glaring problems in your SQL code or your system design. For example, you might have stored procedures that are causing a huge number of implicit conversions because the stored procedures use an incompatible data type compared to what is defined in the database table. To take a look at those types of issues, it helps to build a good data dictionary.
Kendal Van Dyke has a PowerShell library called SQL Power Doc that will build a data dictionary and an inventory of your SQL Server estate. There’s another great free tool called the Microsoft Assessment Planning (MAP) Tool Kit, which does a good job of building out your data dictionaries and a map of your infrastructure. Then we have our own product, SentryOne Document, which not only builds a data dictionary, but enables you to do impact analysis and lineage analysis. For example, you can tell whether your data came from an Oracle database, through a staging server, then to your production SQL Server. You can see all those steps with SentryOne Document. And if you need to change a specific data type, for example, you could see the impact that change would have across each system involved in the process.
Those capabilities will help if you’re also trying to ease the burden of technical debt because of bad mistakes that have cropped up in your system over the years. One of the main motivators for addressing these types of problems before the migration is that once a database goes into production in the cloud, every poorly performing query costs extra money because it burns more CPU, consumes more IO, takes up more storage space, and causes your Azure bill to go up. So, if you have a very poorly performing piece of SQL code, getting it to even a mediocre level of quality might save you 20 or 40 bucks a month. If you have dozens of queries like that, they can add up to some real money. I believe the dbatools.io also has some documentation features that will help you build a data dictionary and make sure everything in your database is consistent and in good form for the migration.
A Roundup of Most Useful Cloud Migration Tools for Database Administrators
This discussion provides plenty of context surrounding our expert panel’s picks for most useful cloud migration tool. The following is a complete list of the tools we discussed, with links to learn more:
- SQL Server Database Experimentation Assistant, which helps you capture database workloads on-premises or in the cloud, replay those workloads, and analyze and compare the results
- Azure Database Migration Service for moving databases among various platforms
- dbatools PowerShell module, which helps DBAs build backups and import them during the cloud migration process
- SQL Sentry, which DBAs can use to capture performance benchmarks
- SQL Server Query Store, which helps capture performance information of SQL Server queries
- SentryOne Document, which helps DBAs create data dictionaries in preparation for cloud migrations
- SQL Power Doc, a collection of Windows PowerShell scripts that discover, document, and diagnose SQL Server instances
- Microsoft Assessment Planning (MAP) Tool Kit, an inventory, assessment, and reporting tool
These powerful tools (many of which are free) will give you a head start in planning your database migration to the cloud.
If you have other free and useful tools applicable for aiding in the process of migrating SQL Server databases to Azure, I would love to hear about them! Please share them with me in the comments.
Cheers,
-Kev
Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell.
Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.
