When you run SQL Server on an Azure virtual machine or an Amazon EC2 instances there are certain weaknesses on these platforms that could cause unstable or poor performance in your databases. To arm yourself against this it is important to collect some information from your on-premises SQL Server before you migrate. This information will be very valuable if you run into performance problems or if your application starts generating errors after migration. Make sure that you do this before the migration and while your on-premises SQL Server is still in use.
Performance
Measure SQL Server performance on two levels and make sure there are plenty of users actively using your application databases while you collect these metrics:
Query performance: use a SQL Server profiler trace or extended events to capture the execution statistics (duration, writes, reads, and cpu) of all the queries being executed against the database. This will give you a good idea of what the most resource intensive queries are and you will be able to compare their performance before and after the migration. Why not use the DMVs for this? Because this doesn’t allow you to drill down into specific outliers. It’s the outliers that we’re interested in because they cause the performance problems and timeouts in your application.
Operating System and SQL Server performance: use performance monitor to capture the important counters at the operating system and SQL Server level. For a quick primer on performance counters, see my post “4 Performance Counters for SQL Server”. In addition to the counters mentioned in this post, also capture the following objects: SQL Server: Access Methods, SQL Server: General Statistics, SQL Server: Latches, SQL Server: Locks, SQL Server: SQL Statistics.
Disk Usage
Disk performance is one of the main causes of performance problems on Amazon EC2 and Azure VMs. Especially if you have an OLAP application that is reading or writing large amounts of data. Do a review of your current SQL Server and find out how much disk space each of the databases is using and which databases are causing the most load on the disks. Don’t forget to include TempDB in your analysis because it is a frequent cause of bottlenecks. This knowledge will also prepare you for properly setting up your disks on the target platform. For more information on how to configure your disks on an Azure VM, see the article SQL Server Disk Configuration on Azure.
Application Architecture and Network Latency
If you plan to have IIS or any other type of “middle tier” application layer running on a separate server you should pay special attention to how the application retrieves data from your SQL Server. If your application is “chatty” and makes many round-trips to the database to retrieve data then this “chattiness’ can become a major source of application slow-downs on Azure or Amazon EC2. For more information about this see this post about slow application performance on Azure. There is also a good paper about this from the Microsoft Customer Advisory Team. The paper is called “AZR376-How to scale Windows Azure SQL Databases (Rm224-225).pdf” and can be downloaded from Microsoft.
Scheduled Processes
Take inventory of the SQL Server agent jobs that are scheduled and also any processes scheduled via the Windows task scheduler. Also note down the duration of each scheduled process because they can be used to compare performance between your old and new SQL Server.
instaDBA SQL Server Baseline
You can collect all this data at the click of a button by using our instaDBA baseline tool, you will also be able to compare two baselines after your SQL Server has been migrated to the target platform. The baseline costs $49. For more information, see our SQL Server Baseline page.