Creating your first Azure SQL Database

Introduction

You might be thinking why do you want to create Azure SQL Database and what are the best configurations. This article will provide you with the basic configurations of the Azure SQL Database.

First of all, let us look at what is the advantage of having your database in the cloud. If you want to travel from one place to another, of course, you can drive your own car. Then you have the freedom but you need to maintain the vehicle. The next option is you hire a car that will leave out maintaining the car. This is the same when it comes to using the Cloud.

If you want to create a database, you can create a database on-premise that will leave you to maintain the operating system and database server. Further, in the case of an on-premise database, disaster recovery and High Availability will become your headache.

Apart from the above features in the cloud, what about the upgrade to facilitate future scalability. Sometimes, you will have adhoc data loads that need additional resources for a short duration of time. Your on-premises database will not be able to support short time resources that can be supported by the Azure SQL Database.

Create an Azure SQL Database

When you log into the azure portal from portal.azure.com. You will see available services from Azure.

In the above list of services, you can select SQL Database and before creating a database, you will see the following screen.

In the above figure, you will see the free training from Microsoft and those links are given at the end of this article. Further, you can look at the pricing of Azure SQL Database for different configurations to suit your budget.

First, you need to choose an Azure subscription. Then you need to select or create a new resource group. Resource groups are like folders which will enable you to organize and manage your resources. For example, you can place the resources for development, quality assurance and production separately. Further, you can place the resources project wise in order to maintain the budget.

Next, we need to provide database details for Azure SQL Database. The database name should be less than 128 characters and it should not contain any special characters. Next, we need to configure the server for the database.

In this configuration, the server name is dinesha.database.windows.net and you need to provide an administrator login to the server.

Elastic pooling a cost-effective feature in the Azure SQL Database. If you are running multiple databases on one server, you can share the DTUs and storage between these databases. This feature is important when you are having multiple databases on a server.

Configuring the Database

In the database, an important configuration is Configure Database. This defines the storage and CPU usages of the databases.

There are mainly two types of configuration DTU and v-core based. DTU stands for the Database Transaction Unit that will define how much resources your database has.

As shown in the above figure, there are three configurations for DTU. As indicated, Basic is for less demanding workloads, Standard is for average performance requirements while the premium is for IO incentive workloads.

Basic DTU

The Basic DTU provides, 5 units of fixed DTUs and a maximum of 2 GB is of storage is provided for 5 USD for a month. Though this is named as basic, this DTU can provide many more options. While it provides 99.9% availability and this configuration will retain database backup for 7 days. On the negative side, this configuration will not provide the options for column store indexing and In-Memory OLTP.

Standard DTU

In the standard DTU configuration, you have the option of configuring the number of DTUs.

As shown in the above figure, the DTU configuration ranges from 10 to 3,000 while storage ranges from 100 MB to 1 TB. Each DTU is 1.5 USD, so you can choose the required DTU depending on your budget.

Unlike Basic DTU, in this configuration, backup retention will be 35 days compared to 7 days in Basic DTU. While you cannot create an in-Memory OLTP database, you can create column store indexes for this configuration with more the 100 DTUs.

Premium DTU

Premium DTU configuration in Azure SQL Database has more features than the other two configurations.

In this configuration, DTU can be configured from 125 to 4,000. In this configuration, DTUs up to 1,000 is priced as 3.72 USD per DTU while DTU more than 1750 is priced at 4 USD per DTU. In this configuration, you can configure data to a maximum of 4 TB depending on the selected DTU. Apart from these standard configurations, two other configurations were not available previously. Read scale-out option provides the scale-out option while the database zone redundant option provides enhanced availability by spreading replicas across availability zones within one region.

In the Premium DTU option, you have the option of creating column store indexes while in-memory OLTP is supported. In this configuration, 25 IOPS per DTU while is 1-4 in other configurations. This shows that the Premium DTU is the most suitable for heavy workloads. However, it has a much higher cost than the other two DTU options.

Similarly, you can configure the Azure SQL Database with v-core options. In this configuration, you need to provide the number of v-cores, database size with log files as well.

Now the question is what you select. Obviously, it will depend on the workload that you will be working with. However, it is always better to choose lesser configurations first and move ahead as and when needed. Since you can monitor the usage of the database in the Azure Portal, you can get an understating of the resource configurations.

Additional Settings

Apart from the database configuration, there are few other configurations that you can make in Azure SQL Database as shown in the following figure.

You can create an empty database, or restore from a database backup or else you have the option of creating a sample database that has the schema of AdventureWorksLT database.

If you are choosing an empty database, you can choose the collation that will define the language or case sensitiveness of the data. It is important to note that this configuration cannot be changed after the database is created.

Further, you can protect your data with Azure defender for SQL. You can start with the one-month free trial and can be extended further for 15 USD per month.

Connecting from SSMS

Your popular SSMS can be used to connect to the newly created Database in Azure. You need to enable your IP address so that you can connect to the created database. Once you are connected, it is something similar to the standard database in On-Premise as shown in the below figure.

Conclusion

Azure SQL Database is a cloud-based database that can be used without worry about the relevant hardware and DR options. In this database, there are multiple configurations options such as Basic, Standard and Premium. These options are driven by DTU and storage. Further, you can select a sample database such as AdventureWorksLT. When you are choosing it is always better to choose the minimum configuration you required so that you can increase the load when needed. You can enable security and auditing options in the Azure portal.

Extra Readings

Pricing: https://azure.microsoft.com/en-gb/pricing/details/sql-database/
Purchasing Models: https://docs.microsoft.com/en-gb/azure/azure-sql/database/purchasing-models
Provisioning: https://docs.microsoft.com/en-gb/learn/modules/provision-azure-sql-db/

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka

SQLShack

Creating your first Azure SQL Database

Introduction

Create an Azure SQL Database