Creating SQL Azure Database

SQL Azure is a fully managed and hosted MS SQL database, with a few differences. It allows you to access and create databases on your own SQL server. While you may be sharing resources, you won’t be sharing a SQL Server instance. SQL Azure is a great relational database, perfect for any Azure cloud hosted applications to access, such as an API for your mobile app. It’s also great value for money.

Setup

Go to the Azure Portal and search for SQL. Click on SQL Database and create a new server and database. You can always delete or add more databases at a later date. There are two types of databases you can choose from, Elastic Pool and their Single database. Unless you are running a multi-million user site, choosing their single option is the best option.

When you choose your type of SQL Server, I would recommend S0 for any production site. You can change which plan you are on at any time via the portal, don’t worry if you decide you need more or less at a later date, it can easily be changed. However initially, lets just create a Basic database, as it is the cheapest, and we can always just upgrade later.

Once deployed, you now have your own Azure SQL Server and database. For pricing please see Pricing for Azure SQL or play around with the pricing when you are creating the database.

When you look at the deployments, you will notice there is a Server and a Database. The server configuration is for all databases and you can add more databases to the server.

Firewall

By default, the firewall will keep any connections from connecting to your SQL server. There are two ways you can open up your firewall as needed. First and hopefully the only one needed, is to open your SQL Server to other Azure services. This means if you are running an API on an Azure instance, it will have access to the database. You can set this by selecting on, in the Allow access to Azure services toggle.

To get to this screen, click on the SQL server (not database) in the resources section)

The second way, is to open up IP addresses as required. This is generally not needed, but can be handy if you want to connect to your database, through SQL Server Management Studio. Go to Firewall option and enter in your IP address.

As a handy hint, it includes your current IP address at the top, under Client IP address.

Connection String

The connection string to your Azure SQL Server will be as follows. Replace the following options as required.

Server=tcp:[server].database.windows.net;Database=[database];User ID=[username]@[serverName];Password=password;Trusted_Connection=False;

When you are viewing your database, you will also notice there is a Connection Strings link, where you can get the connection string, directly from the portal.

Clustered Index

SQL Azure is setup differently than a single SQL Server. It is clustered, meaning, you actually have 3 instances of your server at any point in time, that all sync. This provides great reliability and also decent read speeds. However it does slow down write performance. The other main issue, is you will get even worse performance on writes, if you use a GUID as a primary key and your clustered index.

You will find many posts out there, continuously saying not to use a GUID as your primary key, however using a GUID as your primary key is perfect if you ever want to scale your database out to other data centers as redundancy or load balancing. While these concerns might not be an issue now, it’s easier to account for them now, rather than the future.

To have the best of both worlds, you can have your primary key as a GUID, but you need to create a new column for your ClusteredIndex. This works by having a separate column called ClusteredIndex and setting it to an int and auto increment. This index is used for the 3 local instances during writes, but you never need to worry about syncing it, if you decide to have a separate database.

Create your table with a separate clustered index as shown.

CREATE TABLE [dbo].[TableName](
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](255) NULL,
 [ClusteredIndex] [int] identity(1,1) NOT NULL, 
 CONSTRAINT [PK_dbo.TableName] PRIMARY KEY NONCLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE CLUSTERED INDEX IX_TableName  
    ON dbo.TableName (ClusteredIndex);   
GO

Performance

Performance of Azure SQL is measured in Database Units (DU). What a database unit is, is still a bit of a mystery, as there is no exact Read/Write rate to DU. But if you search around, there are comparison charts, showing estimated performance for each level.

A few things to consider when making an application against a SQL Azure database, is network connectivity and write performance. You may be used to a SQL Server on your own local network, dedicated to your apps, now, the SQL Server is shared and network traffic a lot noisier. While not much of an issue now, in the early days, it was very common to have a SQL Connection drop, hence you need to make sure you retry, the connection if it fails. You have to retry, because you don’t know if the connection was dropped until you try to send something down it.

Writing is also a little slower, due to the cluster. Each time you write, it has 3 databases to write to, as your database is actually a mini-cluster. This is why you want your ClusteredIndex as an int and not a GUID, as mentioned above.

Summary

If you need a relational database, for your services on Azure, SQL Azure is a great choice, in terms or scalability, reliability and cost. Make sure you place your SQL server in the same data center as your app, for performance and bandwidth reasons. Start off small and monitor your SQL database to see if you need to expand its capacity.

Microsoft MVP | Xamarin MVP | Xamarin Forms Developer | Melbourne, Australia

Leave A Comment?