Migrating SQL Server to Azure: What Should You Migrate?

The first post in this series covers reasons why you should migrate to Azure.  In this post, I will cover methods for determining what to migrate.

If you have an inventory of your SQL Server instances, you can start there.  If you do not have an inventory, or are not sure of its completeness, the next option is the Microsoft Assessment and Planning (MAP) Toolkit.   The MAP Toolkit will scan an inventory of servers for instances of SQL Servers. The MAP Toolkit can scan Active Directory for SQL instances, a list of servers from a file, or a list of manually entered servers.

The MAP Tool will provide a high-level report showing the number of SQL Server instances, what SQL Server components are running, the number of licensed editions, and the number of free and developer editions.

In the upper right-hand corner under options, the MAP Toolkit will generate a pair of csv files.  The SQL Server Assessment Report provides information about the servers running instances of SQL Server and the SQL Server instances.  The SQL Server Database Details report provides information about each database, including size, file layout, and security. 

Additionally, the MAP Toolkit creates a SQL Server Express instance to store the assessment data.  You can access this database in Management Studio by connecting to instance (localdb)\maptoolkit.  If you are interested in returning more detailed information, you can run queries directly against the database.  Here are two examples:

The code listed above can be found in this text file. DMAQueries.txt

The MAP Toolkit provides information about the number of instances and the number, size, and configuration of databases.  You also want to know what resources each instance is consuming in your current environment.  If you are using a monitoring tool for your SQL Server environment, you can use it pull historical metrics.  If you are not, you can set up Performance Monitor traces to capture metrics.  An overview of Performance Monitor can be found here.

Tracking CPU consumption and memory usage for the instances you are planning to migrate over a period will help identify opportunities for consolidation.  Because Azure provides the ability to increase and decrease resources, workloads can be consolidated.  If you know when those instances reach a peak of CPU and memory usage, you can schedule an increase in resources.

In the next post in this series, I will cover the different options for where to migrate in Azure.