9 Things You Should Know About SQL Azure Databases


This post has been written by Victor Moreno, a Microsoft Azure MVP.  

SQL Azure Databases are one of the most useful relational databases on the cloud, and their flexibility makes them easy to implement into any software project. I consider the following 9 features to be most notable in SQL Azure Databases:

1) They are independent from a server with an operating system

When you work with relational databases like SQL Server, you need a database engine and an operating system to work on (like Windows or Linux) - this can be a physical or virtual machine. To deploy an SQL Azure Database, start with the following steps:

Access the website (portal.azure.com) and create a new service for the SQL Azure Database. Screen Shot 2016-10-19 at 8.11.15 AM

Database name:  Define the database name.

Subscription: Select your Azure subscription (BizSpark, DreamSpark, pay as you go, etc.).

Resource group:  You may have several services in the same region, if one already exists, select it. If not, you need to create one.

Source:  Define the type of source you want to create. In this case it’s a “blank database.”

Server: Choose the server to host the database. If one already exists, select it. If not, you must create one.

Pricing tier: Define a price for the database. This depends of amount of storage and level of performance you need.

Collation: We selected by default SQL_Latin1_General_CP1_CI_AS.

Now, you have a server with a relational database on the cloud.

2) They scale as needed

When our SQL Azure Database was created, we selected an initial size in gigabytes, as well as a performance tier measured in DTU. These features can be changed by simply accessing the website (portal.azure.com) and choosing a new pricing tier for the existing database. Screen Shot 2016-10-19 at 8.14.38 AM

When we created our SQL Azure Database, we started with 2GB. We completed the following procedure to escalate the database to 500GB in less than a minute.

Based on how many DTUs and GBs you’d like, there’s multiple pricing to choose from - you can chose to purchase just 5 DTUs or 5 GBs, all the way up to 125 DTUs and 500GB.

What is a DTU? Click here.

3) They can be restricted by Internet Protocol (IP)

When you create a resource on the cloud, there’s always a chance that hackers will attempt to access your services or data. The Microsoft Azure Portal has an easy-to-implement mechanism, in which databases can only be accessed through the specific IP addresses. To restrict a SQL Azure Database, access the website (portal.azure.com) and assign the allowed ranges of IP. Screen Shot 2016-10-19 at 8.16.05 AM Screen Shot 2016-10-19 at 8.16.14 AM

In the case that any client attempts to connect to our database, we only need to specify a range such as: –

4) They can be managed from multiple frameworks

While an SQL Azure resource on the cloud can be accessed through Microsoft technologies, other connections are also available:

  • ADO.Net: For connections from .Net framewok
  • ODBC: For connections without a specific provider like Microsoft Access
  • PHP: For connections with PHP
  • JDBC: For connections with Java

To obtain connection strings for the SQL Azure Database, choose the connection you need on portal.azure.com. Screen Shot 2016-10-19 at 8.21.28 AM

Now, copy the connection string you'd like to use. Screen Shot 2016-10-19 at 8.21.42 AM

Each connection string should be copied in the code that corresponds to your programming language.

5) They are easy to geographically replicate

Doing this in on-prem environments can be a little complicated. However, thanks to Microsoft Azure Cloud’s flexibility, the information on databases can move from continent to continent in a couple of minutes. Anything that happens on one database, is automatically reflected on another.

To replicate SQL Azure databases, choose a new region you’d like to use. Screen Shot 2016-10-19 at 8.23.13 AM

Screen Shot 2016-10-19 at 8.23.21 AM

When choosing a region, it must be the closest one to you. The further away it is, the more latency issues you’ll have.

6) They are duplicated immediately

You will probably need to test in other environments before production. The obvious solution to this is to make a copy of a text file; this is how you transfer from one folder to another in Azure.

To copy a SQL Azure Database, simply choose the database and click ‘copy’. Screen Shot 2016-10-19 at 8.26.34 AM Screen Shot 2016-10-19 at 8.27.18 AM

Once you've written the name of your database, click 'accept'.

7) They can be managed diversely

Relational databases like SQL Azure can be explored and managed by two tools, which are:

  • SQL Server Management Studio
  • Visual Studio

To manage a SQL Azure Database with SQL Server Management Studio, follow these steps:

Take the server data from the Azure website. Screen Shot 2016-10-19 at 8.29.55 AM

Open SQL Server Management Studio and start the session on the server, using the copied server data from the Azure Portal. Screen Shot 2016-10-19 at 8.30.15 AM Screen Shot 2016-10-19 at 8.30.32 AM

And to manage a SQL Azure Database with Visual Studio, select your database and choose to open it with Visual Studio. Screen Shot 2016-10-19 at 8.34.35 AM Screen Shot 2016-10-19 at 8.34.44 AM Screen Shot 2016-10-19 at 8.34.55 AM

8) They can be recovered when deleted by mistake

Sometimes human mistakes happen. But if you delete your database by mistake, don’t worry too much. It can be restored.  

To recover a deleted SQL Azure Database, search “SQL Servers” in “My services” and then choose the server hosting the deleted SQL Azure Database. Screen Shot 2016-10-19 at 8.39.17 AM Screen Shot 2016-10-19 at 8.39.47 AM

9) They can be encrypted

SQL Azure Databases can encrypt data in less than three clicks.

To do so, go to the data encryption option and enable the feature. Screen Shot 2016-10-19 at 8.42.24 AM

Behind this simple process, the Azure portal generates encryption and certificate keys. So, you don't have to write any SQL statements.

When you go to begin a new project and are stuck on which database to chose, I hope you will be able to refer back to this post and get some useful pointers.

Victor MorenoVíctor Moreno is a specialist in Cloud Computing, and has 10 years of experience working in software development with Microsoft, .Net and open source technologies. He's been a Microsoft Azure MVP since 2015. He blogs in Spanish here.

Follow him on Twitter @vmorenoz