Esat Erkec
Create SQL Database page in Azure

Getting started with Azure SQL Database

February 5, 2020 by

In this article, we will uncover the basics of the Azure SQL Database.

Introduction

Since cloud computing has entered our lives, we have started to gain an ability to access various services at incredible speeds through the cloud. Servers, storages, databases, networking, and software can be counted as an example of these services. The ability to quickly access these resources continues to directly affect productivity positively and it seems that it will continue to do so with more acceleration than today. In this way, we can increase our flexibility in challenging market conditions. Also, effective cost management benefit is another essential advantage of the cloud-computing. Buying new hardware and software causes the increasing expense of the organizations. On the other hand, the organizations need to employ IT experts to manage these new infrastructures. However, cloud-computing helps to reduce these expense items.

What is Azure SQL Database

Azure SQL Database is a cloud-computing database service (Database as a Service), that is offered by Microsoft Azure Platform which helps to host and use a relational SQL database in the cloud without requiring any hardware or software installation. Also, it provides various advanced features to its users and some of them can be listed as the following;

  • Long-term backup retention enables us to keep backups for up to 10 years
  • Geo-replication provides to create readable secondary databases in different data center locations
  • Automatic tuning is an autonomous artificial intelligence-based performance tuning option that fixes the performance problems automatically
  • Business continuity
  • High-availability
  • Scaling database resources feature is the most powerful feature that provides scaling the database resources either up or down
  • Automated backups

How to Create an Azure SQL Database

In this section, we will learn to create a SQL Database in Azure but before starting this, we will first need an Azure user account. Microsoft allows creating an Azure free account with some credit so if you don’t have an Azure account you can use this option. After logging into Azure, we will click the Go to the Portal link and navigate to the Azure Management Portal.

Navigate to Azure SQL Portal

We will write the SQL keyword into the search textbox and then click the SQL databases option.

Creating a Azure SQL Database

In order to create a new database, we will click the Add or Create SQL database link on the SQL databases page.

Create SQL database on Azure

The resource group helps to group the related Azure services under a logical collection. A website deployment can require different types of resources such as IIS, database, servers, etc. We can group these services in the same resource group which looks like the folder in the file system.

On the Create SQL Database page, firstly, we can choose an existing resource group or we can create a new one. For this demonstration, we will create a new resource group for the Azure SQL database.

  1. Click Create New button to create a new resource group
  2. Specify a name for the new resource group
  3. Click OK to generate this resource group

Create a  new resource group for Azure SQL database

In the Database Details section, we will give a name to this database and this name must satisfy naming policies.

  • The database name should not include any special characters. For example Demo??DB or DemoDB== can not be used as a database name

    Azure SQL database names do not contain any special characters

  • The database name must be at most 128 characters
  • The database name should not contain any reserved keywords. For example, we cannot give the Azure name to any database

    Azure SQL database names do not contain any reserved words

  • We must not use the name of an existing database on the same server second time

Finally, we will give SQLShackDemoDB name to the database and it satisfies all policies.

Azure SQL database names do not use the one database second time

After giving a proper name to the database, we will create a new server. When we click the Create New link, the New server page appears on the right side of the page. In this screen, we will define the server name, admin login password, and location of the server.

Create SQL Database page in Azure

The purchasing model determines the segmentation of the performance and price options and these are:

  • Virtual core (vCore)-based Purchasing Model
  • Database transaction unit (DTU)-based Purchasing Model

In this step, we will choose the purchasing model of the Azure SQL database. This option directly affects the performance and price of the deployed database. By default, Gen5, 2 vCores, 32 GB storage configuration will be offered.

Purchasing Model selection

The estimated cost of this configuration will be about 371.87 US $.

Cost of the Azure SQL database

Through the Configure database option, we can choose other purchasing model. This is the most important option during creating a database because we determine the performance of the database on this screen. For the vCore-based purchasing model, we can change the number of vCores so that we can increase or decrease the performance of the database. In addition, we can find out the cost summary of the selected purchasing model according to the Azure account location currency.

Scaling the database

We will click the Apply link to set these settings and then click the Review + create option. On the Create SQL Database page, all details of the configured database will be offered to check.

Review page of the database

After clicking the Create button, the Azure SQL Database has been starting to deploy and the status of the deployment process will be shown under the Notifications menu.

Notifications menu of the Azure

Once the deployment is done, the notification will be changed to Deployment succeeded and when we click this link, we can show the Deployment Details.

Details of the deployment notifications

How to Connect an Azure SQL Database

After creating the database on Azure, we can connect and use it. We will write the name of the deployed database in the search textbox and the deployed database will appear.

Managing the deployed database

We will click on the SQLShackDemoDb and it navigates to the management screen of the database. In this screen, we can monitor and manage the database. Also, we can configure the features and settings of the firewall.

Set firewall rules of the Azure SQL database

We can easily connect to the database with the help of the SQL Database Query editor.

Connecting to SQL Database Query Editor on Azure

In this query editor, we can perform the DML statements and we can also expand the tables, views and stored procedures in the database.

SQL Database Query Editor on Azure

In order to connect to a database out of the Azure Portal, we need to configure the firewall settings of the server. We will click the Set server firewall button. In the Firewall settings screen, we will click the Add client IP and it automatically finds the public IP and adds a new rule to the list. Finally, we will click the Save option to configure this setting.

Setting firewall rules of the Azure SQL

Now, we can connect the Azure SQL database through the SSMS.

Connecting to Azure SQL database with SSMS

The Object Explorer tab will be shown on the connected server and the databases which are placed under this server.

Object Explorer view of the Azure SQL on SSMS

Conclusion

In this article, we learned about the Azure SQL Database basics and we also learned how to create a database on it.

Esat Erkec
SQL Azure

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

4,394 Views