SQL Azure Essentials for the Database Developer

  • Article

 

I admit it, I am a SQL geek. I really appreciate a well designed database. I believe a good index strategy is a thing of beauty and a well written stored procedure is something to show off to your friends and co-workers. What I, personally, do not enjoy, is all the administrative stuff that goes with it. Backup & recovery, clustering, installation are all important but, it’s just not my thing. I am first and foremost a developer. That’s why I love SQL Azure. I can jump right in to the fun stuff: designing my tables, writing stored procedures and writing code to connect to my awesome new database, and I don’t have to deal with planning for redundancy in case of disk failures, and keeping up with security patches.

There are lots of great videos out there to explain the basics: What is SQL Azure,  Creating a SQL Azure Database. In fact there is an entire training kit to help you out when you have some time to sit down and learn. I’ll be providing a few posts over the coming weeks to talk about SQL Azure features and tools for database developers. What I’d like to do today is jump right in and talk about some very specific things an experienced database developer should be aware of when working with SQL Azure.

You can connect to SQL Azure using ANY client with a supported connection library such as ADO.NET or ODBC

This could include an application written in Java or PHP. Connecting to SQL Azure with OLEDB is NOT supported right now. SQL Azure supports tabular data stream (TDS) version 7.3 or later. There is a JDBC driver you can download to connect to SQL Azure. Brian Swan has also written a post on how to get started with PHP and SQL Azure. .NET Framework Data Provider for SQLServer (System.Data.SqlClient) from .NET Framework 3.5 Service Pack 1 or later can be used to connect to SQL Azure and the Entity Framework from .NET Framework 3.5 Service Pack 1 or later can also be used with SQL Azure.

You can use SQL Server Management Studio (SSMS) to connect to SQL Azure

In many introduction videos for SQL Azure they spend all their time using the SQL Azure tools. That is great for the small companies or folks building a database for their photography company who may not have a SQL Server installation. But for those of us who do have SQL Server Management Studio, you can use it to manage your database in SQL Azure. When you create the server in SQL Azure, you will be given a Fully Qualified DNS Name. Use that as your Server name when you connect in SSMS. For those of you in the habit of using Server Explorer in Visual Studio to work with the database, Visual Studio 2010 allows you to connect to a SQL Azure database through Server Explorer.

imageimage

 

The System databases have changed

  • Your tempdb is hiding – Surprise, no tempdb listed under system databases. That doesn’t mean it’s not there. You are running on a server managed by someone else, so you don’t manage tempdb. Your session can use up to 5 GB of tempdb space, if a session uses more than 5GB of space in tempdb it will be terminated with error code 40551.
  • The master database has changed – When you work with SQL Azure, there are some system views that you simply do not need because they provide information about aspects of the database you no longer manage. For example there is no sys.backup_devices view because you don’t need to do backups (if you are really paranoid about data loss, and I know some of us are, there are ways to make copies of your data). On the other hand there are additional system views to help you manage aspects you only need to think about in the cloud. For example sys.firewall_rules is only available in SQL Azure because you define firewall rules for each SQL Azure server but you wouldn’t do that for a particular instance of SQL Server on premise.
  • SQL Server Agent is NOT supported – Did you notice msdb is not listed in the system databases. There are 3rd party tools and community projects that address this issue. Check out SQL Azure Agent on Codeplex to see an example of how to create similar functionality. You can also run SQL Server Agent on your on-premise database and connect to a SQL Azure database.

SystemDatabases

You don’t know which server you will connect to when you execute a query

When you create a database in SQL Azure there are actually 3 copies made of the database on different servers. This helps provide higher availability, failover and load balancing. Most of the time it doesn’t matter as long as we can request a connection to the database and read and write to our tables. However this architecture does have some ramifications:

  • No 4 part names for queries –  Since when you execute a query you do not know which server it will use, 4 part queries that specify the server name are not allowed.
  • No USE command or cross database queries – When you create two databases there is no guarantee that those two databases will be stored on the same physical server. That is why the USE command and cross database queries are not supported.

Every database table must have a clustered index

You can create a table without a clustered index, but you won’t be able to insert data into the table until you create the clustered index. This has never affected my database design because I always have a clustered index on my tables to speed up searches.

Some Features are not currently supported

  • Integrated Security – SQL Server authentication is used for SQL Azure, which makes sense given you are managing the database but not the server.
  • No Full Text Searches – For now at least, full text searches are not supported by SQL Azure. If this is an issue for you, there is an interesting article in the TechNet Wiki on a .NET implementation of a full text search engine that can connect to SQL Azure.
  • CLR is not supported – You have access to .NET through Windows Azure, but you can’t use the .NET to define your own types and functions, but you can still create your own functions and types with T-SQL.

You can connect to SQL Azure from your Business Intelligence Solutions

  • SQL Server Analysis Services - Starting with SQL Server 2008 R2 you can use SQL Azure as a data source when running SQL Server Analysis Services on-premise.
  • SQL Server Reporting Services – Starting with SQL Server 2008 R2, you can use SQL Azure as a data source when running SQL Server Reporting Services on-premise.
  • SQL Server Integration Services – You can use the ADO.NET Source and Destination components to connect to SQL Azure, and in SQL Server 2008 R2 there was a “Use Bulk Insert” option added to the Destination to improve SQL Azure performance.

Today’s My 5 of course has to relate to SQL Azure!

5 Steps to get started with SQL Azure

  1. Create a trial account and login
  2. Create a new SQL Azure server – choose Database | Create a new SQL Azure Server and choose your region (for Canada North Central US is the closest)
  3. Specify an Administrator account and password and don’t forget it! – some account names such as admin, administrator, and sa are not allowed as administrator account names
  4. Specify the firewall rules – these are the IP Addresses that are allowed to access your Database Server, I recommend selecting the “Allow other Windows Azure services to access this server” so you can use Windows Azure services to connect to your database.
  5. Create a Database and start playing – You can either create the database using T-SQL from SSMS, or using the Create New Database in the Windows azure Platform tool which gives you a wizard to create the database.

Now you know the ins and outs, go try it out and come back next week to learn more about life as a database developer in SQL Azure