Analyze performance between SQL Azure and SQL Server on premise.

You need to be convinced that the performance in SQL Azure is acceptable for your end users before you can move you ron premise databases to SQL Azure. In the on-premise environment you have a lot of tools which you can use to measure the SQL performance of your application. However, in SQL Azure the tools are not so good as the on–premise versions. For instance:

• You can’t connect with SQL Profiler to a SQL Azure database.
• You can’t connect with Windows performance monitor (Perfmon) from an Azure worker role to your SQL Azure database server.

I strongly hope that this will be improved by Microsoft in the future. In this blog I will describe what you can do to analyze performance of your application in SQL Azure. Most of the methods requires a lot of manual work, but it is better than nothing.

 

First of all you need to upload a version of your on premise database to SQL Azure. Use the SQL Azure Migration Wizard. The SQL Azure Migration Wizard is an open source application, which is designed to help you to migrate your SQL Server 2005/2008/2008R2/2012 databases to SQL Azure.  SQL Azure Migration Wizard will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to SQL Azure.  SQL Azure Migration Wizard requires SQL 2008 R2 SP1.

 

 

After uploading your database to SQL Azure we can start comparing query performance between the on-premise database and the SQL Azure database. Take into account that latency between your test load application and the SQL Azure database should be minimized. This can be done in 2 ways:

• Use queries for which the result set is minimal. For instance  SELECT COUNT(*) FROM TABLEX will result in one number. This is a minimum number of bytes to transfer to the client. SELECT * FROM TABLEY will result in a lot of data transfer from SQL Azure server to the client.
• Execute queries from a Azure worker role which is hosted in the same data center as your SQL Azure server.

Record with SQL profiler some queries from your on premise solution. Store these queries in a SQL script file. In this SQL script file add next command before every query.

 

PRINT 'Query: Cashflow entries to be allocated 1'

SET STATISTICS IO ON

SET STATISTICS TIME  ON

SELECT Columns FROM MYtable

Add next command after every query:
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT '----------------------------------------------------------------------'

The SQL Script will be executed in SQL Server Management Studio (SSMS) . Enable Include Client Statistics. (Shift-ALT-S)

Result of the query is printed on the Results tab in SSMS

The IO and Time statistics are printed on the message tab in SSMS

SET STATISTICS IO ON: Will generate  ‘SQL Profiler’ read statistics per query.
SET STATISTICS TIME ON: Will generate ‘SQL Profiler’ CPU Time and total elapsed query time.
The client statistics are printed on the Client Statistics tab.

To measure the total of all queries in one script add next command to the script.
DECLARE @STARTTIME DateTimeDECLARE @ENDTIME DateTime
SET @STARTTIME = GETDATE()
Query 1
Query 2
….
Query X
At the bottom of the script add next syntax

SET @ENDTIME = GETDATE()
SELECT GETDATE(),DATEDIFF (ms, @STARTTIME, @ENDTIME) AS QueryTime

After executing the script the last result set in the Results tab will display the execution time and exection time of the total script.

Now your script is READY for testing. Execute the script on:

1. The on premise database
2. SQL Azure database

Compare the results between the on premise results and the SQL Azure results.

In the Management Portal for SQL Azure you can get an overview of the query performance.

Thinks to take into account:

• Use only SELECT queries which enables you to redo test a lot of times on the SQL Azure database without the need to restore the database.
• If you plan to use INSERT, DELETE and UPDATE statements, you need to have a backup of your SQL Azure database.  Backup and Restore is not supported in SQL Azure at this moment but you can use the CREATE DATABASE  XXX AS COPY of YYY statement. This will create a copy of your database using a new database name.
  
  CREATE DATABASE destination_database_name
  AS COPY OF [source_server_name.]source_database_name
  
  To copy the Adventure Works database to the same server, I execute this:
  CREATE DATABASE [AdvetureWorksBackup]
  AS COPY OF [AdventureWorksLTAZ2008R2]

Observations:

• SQL Azure execute queries using one processor  (MAXDOP 1). Parallelism is not possible. 
• Dynamic Views in the manage portal contain history for a small period.  It’s difficult to see long running queries for a longer period. This happens because you will be connected to one of the 3 copies of your database.  You never know to which of the copies you will be directed. Every copy will have it’s own content in the DMV’s .
• Performance is not guaranteed on SQL Azure.
• In the tests I have executed so far, the SQL Azure database (8 GB Business Edition) is significant slower in comparison with a SQL database on my laptop. (DELL Latitude E6410).  One of the reasons is the single processor usage of SQL Azure. 

Reasons and considerations to move from on premise SQL server to SQL Azure in the cloud.

A lot of times I got questions from people why to use SQL Azure instead of using the on premise version. I strongly believe that the cloud is the future.  It’s the only way to go. Big question to answer: Is it already good enough to say good bye to the on premise version? In general this question can be answered with all advantages of using the cloud but thers are still some limitations.

Advantages:

• It allows you to scale up and pull back capacity as required, enabling services to be arranged around periods of peak demand
• 24/7 availability (99,9% monthly SLA) 
• Pay for what you uses. Low upfront costs
• Anywhere access
• Always use of the latest software
• Reduced management
• No patching or maintenance on SQL server level. SQL Azure administers the physical hardware such as hard drives, servers, and storage. However, database maintenance is still needed like index fragmentation and update statistics.
• Every database has built-in high-availability, failover, and redundancy. Every SQL database is replicated twice (to make a total of three databases) over different hardware boundaries in the same datacenter. 
• No backups needed for data disaster recovery. However, backups for user failures are still needed.
• Shift in focus on SQL development by Microsoft from on premise to SQL Azure. At this moment SQL Azure is already running on version (11.0.1820) while the latest on premise version is running on version 10.50.2500 (SQL 2008 R2 SP1).

So moving to the cloud with your database will have advantages but you should look to the limitations of the current version.

Limitations:  

• No performance guarantees at this moment.  Part of the reason for this is the multitenant problem: many subscribers with their own SQL Azure databases share the same instance of SQL Server and the same computer, and it is impossible to predict the workload that each subscriber’s connections will be requesting. However, not having guarantees doesn’t mean that performance is not a critical aspect of the design of the SQL Azure infrastructure. SQL Azure provides load balancing services that evaluate the load on each machine in the data center. When a new SQL Azure database is added to the cluster, the Load Balancer determines the locations of the new primary and secondary replicas based on the current load on the machines. If one machine gets loaded too heavily, the Load Balancer can move a primary replica to a machine that is less loaded. The simplest way to do this move is to switch a primary and secondary replica for a SQL Azure database that is performing sluggishly. This switch can have a major and immediate impact on performance, because all reads and writes take place on the primary replica. See:  Inside SQL Azure
• SQL Azure provides a significant subset of the functionality of SQL 2008 R2, it does not currently have complete feature parity. For instance:
• No fill factor support for indexes.
• No Full-Text search support.
• Cross database queries are not possible.
• No support for page level or row level compression like the enterprise edition of SQL Server 2008. Work around is use the .NET CLR 2.0 System.IO.Compression namespace in your data layer
• No Azure version available of SSAS and SSIS. I expect it will come in the future but it is unknown when this will come available.
• No SQL Server Agent to schedule jobs. You need to use a Windows Azure worker role and create your own custom code to schedule jobs.
• Performance analysis tooling is minimal. SQL Profiler is still not supported but here you can vote to get it higher on the priority list. A lot of DMV's are added to get insight in query performance. However you can't get insight in query details for one application of user connection.
• Maximum of 149 user database per SQL Azure Server + one master database = 150 databases.
• Maximum database size 150 Gb.  Size limitations can be mitigated through the implementation of sharding which distributes load across multiple SQL Azure databases.
• Clustered index requirement before inserting data. This will make a clustered index change more complex. Because you can’t drop the clustered index if there is data in the table.
• SQL Azure Reporting still in CTP. No support for custom assemblies. This is needed to support multi languages and localization in reports.
• Performance of SQL Azure Reporting is not consistent. This is because not contain processing cache feature. I hope this will be fixed when general available.
• What to do to improve performance? I can’t add memory or add spindles to my SQL Azure database server.  Is the only solution to split up my database in multiple databases using SQL Azure Elasticity?
• Application can be hosted in your own data center. However to minimize network latency  you should host your application in the Windows Azure platform.
• More limitations can be found here.

Conclusion: One year ago, I already blogged about difference between SQL Azure and SQL 2008. A lot is done in the last year. Development of the (SQL) Azure platform is going very fast, but still a lot need to be done. This is not strange because it takes time to make all on premise functionality available in the cloud. I’m looking forward what is coming in the coming year(s).

SQL 2012 will be launched on 7th march 2012

On 7th March 2012, it is time to launch SQL 2012, codename 'Denali'. As we have seen in the past, this does not mean that the bits are general available at this time. In the case of SQL Server 2008 R2, Microsoft’s release to manufacturing (RTM) of the product preceded by about a month the date on which most customers could actually get the bits.

Interesting changes can be found in the BI stack, which is called Power View. (codename 'Crescent'). The biggest disadvantage of Power View is the need to have Sharepoint. A lot of organizations do not have the knowledge to install and maintain a Sharepoint server. Please read next 2 blogpost i mande in the past about Power View.
Pefect demo of SQL Server project Crescent.
SQL Denali Codename Crescent: What is it?

Enjoy it.