Azure SQL Database

When we're considering SQL Server deployment, we can use SQL Server either on-premises or in the cloud as a service. There are several different cloud offerings for SQL Server. We'll concentrate on the offerings in Microsoft Azure. Azure SQL Database is a managed cloud database, which went live around 2010. SQL Database is available as:

• A single instance
• A managed databases in elastic pool
• A managed instance

Azure SQL Database shares the code with the SQL Server Database engine, which allows for a code compatibility to some extent (some features or code are not available in the cloud service). While the on-premise SQL Server requires manual patching and upgrades to deliver new features, those are seamlessly delivered in the cloud first before they are available for the on-premise server at all, which is a great benefit, with those features being tested across millions of databases.

Performance of such a database is dependent on the pricing level, which you can choose when you're creating the Azure SQL Database or anytime afterwards. Scalability is one of the benefits of the Azure cloud, where you can change the performance tier as you go.

While selecting the performance tier, you can choose from three tiers:

• Basic
• Standard
• Premium

The basic tier allows you to create databases up to 5 GB of size and offers the performance of five DTUs. The standard tier allows you to create databases with a size up to 250 GB and has a variable DTU range available, ranging from 10 to 3,000. The premium tier, which is used for most IO-intensive operations, allows you to create databases with a size up to 1 TB and starts with 125 DTUs, which can be scaled up to 4,000. A DTU is a bundled measure of compute, storage, and IO resources that are available to the database. When your workload running in the database exceeds the the amount of any of the mentioned resources it get's automatically throttled.

The differences between the tiers are not strictly only performance based, as you can see in the following table. There are also some disaster recovery and feature based-differences between the tiers, so the choice has to be made wisely. The DTUs scale linearly, so doubling the DTU will give you twice the performance on the SQL Server. As you may expect, the higher the DTU, the higher the cost of your Azure database deployment: 

When you're creating the Azure database, you can choose the performance tier and select the expected database size and expected performance demand:

The very same dialog will be presented to you, when you would like to change the performance tier on an existing database, once it has been deployed. When the database has been successfully created, you can see the overview on the management portal, available at https://portal.azure.com/.

Another option, which is quite new to Azure SQL Database, is to use the vCore purchasing method for independent scaling of compute and storage resources. You can configure your Azure SQL Database to use up to 80 vCores and 4 TB of storage, allowing you to achieve superior IO performance with up to 200,000 IOPS.

As you can see from the following screenshot, Azure SQL Database does include numerous features, which are available also with on-premise SQL Server, and some of them were actually available first in the cloud offering, such as dynamic masking, allowing you to protect the data. Features such as advanced threat protection are not yet available with on-premise installation at all, and are still cloud-only offerings:

Once you have created your Azure SQL Database, you can log in to the SQL Server either with Management Studio, or SQL Operations Studio if you prefer the GUI interface, or any command-line tool that you would normally use with on-premise SQL Server. Bear in mind that, by default, the connection is controlled via a firewall, so you need to enter your IP address to the firewall list on the Azure portal, or add your IP address via the GUI tools, which may ask you to do so.

While having a cloud database available, you can use the data for analysis with other available services either in Microsoft Azure or other vendors who have online analytical services. Considering Microsoft Azure, another services used for data analysis in the Azure SQL Database could be the Power BI Service, Azure Analysis Services, and others.

While having a solitaire database in the cloud might be useful for some analysis, it will be much better to have the database synchronized with other data sources or on-premise databases. Azure allows you to configure Azure SQL Data Sync, which can keep your on-premise and cloud databases synchronized based on your selected schedule. While the data is primarily being stored on the on-premise server, you can perform data science tasks in the cloud with Azure services.