I recently presented at SQL Saturday 184 in North Haven and was able to listen to Grant Fritchey’s present “Query Tuning in the Clouds”. What I got out of the presentation was the sense all your tuning skills could be used to optimize a Windows Azure SQL database.
I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications?
I am continuing a series of articles that will investigate what features are missing in the cloud and what benefits the cloud provides your business.
Today, I will be starting with the sample TSQL script to build out the [AUTOS] database. We will be using SQL Server Management Studio (SSMS) to perform these tasks.
The first step is to connect to the Azure SQL database by entering the full web path to our database. This assumes that you have an active connection to the internet. We will use the [AzureTest] account to manage the database.
All of the features related to files are removed from the cloud version of the server. Thus, any object creation that was using the “ON” key word for file placement needs to be rewritten.
As a default, a Azure SQL Server has one [master] database and one or more user databases. Thus, all the objects that were related to mirroring, replication, mail, scheduling, and etc are not available since [msdb] does not exist.
We do not need to worry about [tempdb] or growth options since file allocation is managed by the provider, Microsoft Windows Azure.
Lets take a look our re-written TSQL script to handle the differences in Azure SQL Database.
Our first finding is that the USE statement does not work. The work around is to right click the database name in SSMS and choose the new query action. This will connect the query window to the correct database.
Our second finding is that DATABASE OWNERSHIP is not supported. Any executed TSQL code trying to change database ownership generates an error message.
Our third finding is that some statements like DROP DATABASE and DROP LOGIN need to be the only statement in a batch. I usually wrap them around an IF EXISTS statement so that my script does not produce errors when the object does not exist.
A work around is to run the drop statements using a EXEC call. This will spawn a new session with one statement to execute. Please see code below.
|
1 2 3 4 5 6 7 8 9 10 |
-- NOT VALID IN SQL AZURE IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS') DROP DATABASE [AUTOS]; GO -- VALID IN SQL AZURE IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS') EXEC ('DROP DATABASE [AUTOS]'); GO |
Our fourth finding is that all tables must have a CLUSTERED INDEX. I can live with this restriction. In know this will mean some older, poorly designed databases, will need to be changed.
Our fifth finding is that the database might be THROTTLED and/or MOVED depending upon the load of physical server in cloud. A noisy neighbor, a person who is running a intensive T-SQL job, may be one cause of this situation. I was surprised that this happened to my server since I was running a very tiny test.
To recap our testing so far, schemas, tables, indexes, constraints, views, functions, and stored procedures are available to the developer on Azure SQL Server. The image below shows all the objects from the AUTOS database.
Because we are not in a hosted database, there are limitations on the TSQL language set. I suggest you refer to this MSDN page to review the changes.
However, many of the menu selections available in SSMS for a local database are not available.
For instance, if we can edit the top 200 rows on a local database but this can not be done on a Azure database.
In summary, Windows Azure SQL Database simplifies database file administration at the sacrifice of advance features such as FULL TEXT INDEXING, COMMON LANGUAGE RUNTIME, and SERVICE BROKER. If you have a business solution using these design patterns, a Windows Azure SQL Database might not be for you.
On the other hand, if you do not want to worry about physical hardward, data center heating/cooling, licensing/support costs, and capacity/growth planning, a Azure SQL Database might be the ticket you are looking for.
I know this has been a high level test of a Windows Azure SQL database. I guess my next step is to calculate all the internal costs versus external cost of having the database housed in the clouds. One thing I would like to investigate is how often does a server get throttled or go offline in the cloud. Also, we have not had a chance to talk about geo redundant data and database federations.
Next time, I will be looking at the Azure Web Interface. We will investigate all the web pages that can be used to manage a database.
