First summary of previous articles:

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

Part 4: We discussed the SQL Azure architecture

Part 5: We discussed the SQL Azure security model

Part 6: We discussed how to migrate Databases to SQL Azure

Now, in this module, we are going to see the following administration tasks:

1. Improving Performance
2. Backup and restore strategy

In addition, in the next module, we will see the following administrative tasks:

1. Managing firewall rules
2. Managing SQL Azure Servers, Databases and Logins
3. Troubleshoot connection issues
4. Monitoring database size and costs
5. Monitoring SQL Azure availability
6. Migrating to SQL Azure

Now, Let’s get started with the goal of this module:

First, Improve Performance

First up is how to Improve SQL Azure performance? Now, though SQL Azure is a cloud-based service and few details are abstracted from the service administrator – we still need to perform tasks from our side to get the best performance out of SQL Azure. We still need to tune DB queries and build indexes to derive better performance from SQL Azure. Moreover, we still need to optimally design and normalize databases to get the best out of SQL Azure. So let us discuss the options that we have to improve performance of SQL Azure.

Now, first task is to select the optimal location of the SQL Azure Server in which your SQL Azure database resides. As of this writing, there are six data centres available worldwide and you have the option to provision a SQL Azure Server at one of the six data centres. Now, this may be NEW to people from SQL server world – but it’s one of the decision that could affect the performance that you get out of your SQL Azure database. This is so because, more the distance between the SQL Azure database and the application, the more is the chance of increased latency between application and the SQL Azure database and so, more latency between application and database means decreased performance. Therefore, you need to choose the location of SQL Azure such that the latency between SQL Azure and the application is Minimum. In addition, if you have a Windows Azure application that accesses SQL Azure databases, then consider keeping the Azure application as well as the SQL Azure database in the same data centre. As an added benefit, you will save on the data transfer cost as the data transfer in “same” data centre is FREE!

We can also investigate and tune queries by using information made available by few DMV’s. For instance, if we want to longest running queries, run the following TSQL query:

select est.text from
sys.dm_exec_query_stats as exqs cross apply sys.dm_exec_sql_text(exqs.sql_handle) as est 
order by exqs.total_elapsed_time/exqs.execution_count desc
go

Now I found out that one of my longest running queries was:

select Name, productnumber from saleslt.product where listprice > 10.00

Now since SQL Azure also allows access to execution plans of queries. So what I am going to do is to examine the execution plan of this query.

Here is what I got:

The execution plan suggested me to create a non-clustered index on this query. Doing so would have improved the performance! Therefore, you just saw one way in which I used the information from DMV’s and Execution plans to tune SQL Azure database.

You can also consider turning ON the statistics IO and Time via following TSQL commands:

SET STATISTICS IO ON
go
SET STATISTICS TIME ON
Go

If you do so, you will be able to see statistics information in the “messages” tab. I am not getting into discussion of how DMV’s, Execution plans and Statistics can help you tune a SQL Azure database – it is out of the scope of this article. My aim is to just give you a brief overview of various tools and techniques that you could employ to improve performance.

For complete list of available DMV’s, please refer: http://social.technet.microsoft.com/wiki/contents/articles/troubleshoot-and-optimize-queries-with-sql-azure.aspx

You can have a look at this free tool: http://blogs.msdn.com/b/psssql/archive/2011/04/25/css-sql-azure-diagnostics-tool-released.aspx

Further reading: For more detailed version of the discussion on how to tune a SQL Azure DB using information available from Execution plan and DMV’s, please refer to articles that I have written in SolidQ Journal:

Tuning SQL Azure databases Part 1 Tuning SQL Azure databases Part 2

Therefore, the good news for you is that you do have Execution plans and DMV’s to tune your SQL Azure database. and so if you come from SQL Server world, your skills are directly transferable. Moreover, this is one of the best thing Microsoft is trying to achieve i.e. coherent experience in both SQL Server and SQL Azure environment. Just to cite one example: SSMS can be used to connect to SQL server as well as SQL Azure. Therefore, we do not need to learn different tools to manage different environments. However, not so good news is that following tools are currently NOT supported: Index tuning wizard and the SQL server profiler. You are limited to perform tuning based on information available from DMV’s and execution plans.

Note: Support for these tools may be added in future.

Now, one more option that affects performance is the “throttling” of workloads in SQL Azure. Now this would be NEW to folks from SQL Server world. So let me first explain what “throttling” is. Here it goes: SQL Azure has a multi-tenant architecture and that means a single SQL Azure computer would host databases from few different subscribers. Call it a shared environment, if you will. So in such an environment, how does Microsoft make sure that each tenant (Database) gets “Fair” amount of resources? So Microsoft has components that operate under the hood that penalize workload that access more than fair amount of resources and if that happens – the workload is “throttled”. Call it prematurely terminated, if you will. Now if a workload is throttled, it returns a “reason” code that point to the reason behind the workload being throttled. For information about each reason code, please refer http://msdn.microsoft.com/en-us/library/4cff491e-9359-4454-bd7c-fb72c4c452ca.

Since workload is throttled, the time taken by that workload before being throttled was wasted – and that means more time taken to carry out a particular task and thus delivering poor performance. In addition, you would always want to gather more information via the reason code and take appropriate steps to make sure that the workload is not throttled again.

Now, let's discuss one more way through which performance can be improved. Traditionally, we upgrade the system on which our databases run and thus improve performance. This is known as "scaling up" model. In SQL Azure world (Or in cloud model) - to improve performance you will need to think, "Scale out". That means, instead of upgrading the system - what you essentially do is that - you partition the database and run those "partitions" on different servers. For instance, if you have a 12 GB database, we partition that into three databases each with a size of 4 GB (just an example). The benefit? As you can imagine, the load on the database would now be spread across three databases instead of just one database. In addition, if you run queries in parallel over these partitions, you will improve performance. Also, scale out model in SQL Azure helps you to overcome the limitation of the maximum allowed size of a SQL Azure Database. So a 200 GB local database can be partitioned into five databases, which are 40 GB in size, and these partitions can then be migrated to SQL Azure. Now, as of now, such architecture requires you to handle the complex mechanisms of "scaling out" from the application layer. It's complex but soon, SQL Azure would provide an out of the box solution called SQL Federations that would let you "scale out" your databases. The discussion on this topic does not stop here - what I have written is a high level overview of one more powerful tool that you will be using to improve performance. There are important questions like "what would be criteria to partition database", "Is it horizontal partitioning or vertical partitioning?" etc. You can read more about federations here: http://social.technet.microsoft.com/wiki/contents/articles/2281.aspx. But remember, in SQL Azure world, you would need to start thinking scale out instead of scale up. That is the key take-away from this paragraph.

So though SQL Azure database has relieved administrator from “physical” administration side of things – it has couple of NEW administrative tasks that can be categorized into the “logical” administration of SQL Azure.

Apart from these techniques, following techniques improves performance:

1. Connection pooling.

2. Stored procedures.

3. Indexed views

4. Proper application design. For instance, avoid excessive round trips to database, release connection after usage.

So let’s recap: 1. Choose optimal location of SQL Azure server.

1. Tune Database via information available through

   a.  Execution plans 
   b.  DMV’s 
   c.  STATISTICS.
   

2. Study throttling scenarios and study reason codes.

3. Think "Scale Out" with SQL federations

4. Connection pooling, stored procedures, Indexed views and proper application design

Now let’s discuss the next part of this module i.e.

BACKUP and Restore strategies

Microsoft keeps three replicas of the SQL Azure database – and so in case, one of the replicas dies, there are other replicas to take its place. Therefore, we do not have the worry about the hardware failures. In fact, we are guaranteed 99.9% availability under the SQL Azure SLA (Service level agreement)

We do not worry about hardware failures! Good news. However, we need to worry about Human errors.

Before we begin the discussion, just note that as of this writing, you do not have the option for “Point in Time” recovery option and on the My Great Windows Azure Site, they have mentioned that it would be added in some future service update of SQL Azure. For now, to plan your backup and restore strategies – you have the following options:

1. Run the TSQL command: “as COPY of”.
2. Export a SQL Azure database to the Azure storage using SQL Azure Import/Export from Azure Management Portal

3. Use following tools to migrate from SQL Azure to SQL server and thus backing up a SQL Azure database on local SQL Server instance:

   a.  SQL server Integration services (running locally)
   b.  [SQL Azure migration wizard][19]
   c.  [SQL server DAC API][20]
   d.  Generate script wizard via SSMS
   e.  BCP to migrate data from SQL Azure to SQL server
   

Now, since you a backup as a local SQL Server database – when need arises, you could just migrate the SQL Server database to SQL Azure.

(There are third party tools out there – a quick search would help you find those easily)

Now let’s discuss COPY command and the SQL Azure import/export that you can consider when you plan your backup strategy.

Run the TSQL command: “as COPY of”

Basically, COPY TSQL command lets us create a transitionally consistent replica of a SQL Azure database.

It’s very simple. How simple? Have a look:

create database adventureworkslt as copy of adventureworksltaz2008r2

Prerequisites to run this query:

1. You need to logon to the master database
2. You should be logged in through server level principal login or login through which you created the database you wish to copy or login which is a member of ‘dbmanager’ role

Note that the database with the database name of the target should not be present prior to executing the query. In this case, if I already have the database with name ‘adventureworkslt’, then if I run the above query, it will show the error: “Database 'adventureworkslt' already exists. Choose a different database name.”

Now, is there a way to monitor the copying progress? Yes, there is a way to monitor the progress. Let us explore it:

As you can see, statedesc column show ‘copying’ for databases that are being copied. Once, the copying process is completed, the statedesc for that database will be updated to ‘online’.

There is also a DMV sys.dmdatabasecopies that can be used to see the copying details:

Note that my previous TSQL code copied the SQL Azure database to the same server. How to copy the SQL Azure to different server (in same data centre)?

Let’s explore that part now: say I have a database ‘adventureworksltaz2008r2’ on serversource and I wish to copy this database to say servertarget. here is the TSQL code:

create database adventureworkslt* 
as copy of <server_source>.adventureworksltaz2008r2;

Note: I am copying the database from vaa4ed8e02 server to m2ggyvdfse server. So it is a cross server copying (same data centre)

Prerequisites to run this query:

1. you need to logon to the master database of the target server

2. The login used on the target server should have the same name and password as the database owner of the database on the source database.

3. Login of the target server must be a principal server login or a member of the dbmanager role

For cross server copying, after the copying process is completed we need to remap users from target database to logins of the source server.

Alter user <username> with login = <loginname>

Now, when the need arises, hit restore, you could point the Application to the replica and not the original database.

Thus, you can see that it is easy to backup a SQL Azure database with this TSQL command. And when you are planning your backup strategy, just note that you need to pay for the COPY that you create.

SQL Azure Import/export:

Now, let’s see the place in SQL Azure Management Portal where you can find the SQL Azure Import/Export tool:

When you do a export of your SQL Azure database – it will create a BACPAC. Now bacpac is a “zipped” version of your entire database. In addition, if you wish to restore – make use of the “import” feature.

Now, you will need an Azure storage account for this. Just note that, you pay for the storage. As of now, the cost of Azure storage is 0.14$ per GB per month. In addition, bandwidth and transaction costs are applicable.

Thus, you do have few options to plan your backup and restore strategies in SQL Azure. Remember that the “point in time” recovery is coming!

Conclusion:

In this article, we covered two important administrative tasks in SQL Azure i.e.

1. How to improve performance
2. Backup and restore strategies.