Deploying a Database on Azure SQL
Azure SQL Database is a Platform as a Service (PaaS) offered by Microsoft Azure. Hence, you need the Azure subscription to use this SQL Database. Azure offers new subscribers a free subscription and $200 worth of resources for 30 days. Recently I revived my relationship with MS Azure and explored the platform a little bit, using the Warner Chaves’ PluralSight Azure SQL Database for SQL Server DBAs training for the start.
In this article, I will explore the steps required to create an SQL Server in Azure and deploy a small database. Here I am using a free tier subscription, and the resources are on the basic level. Further, I’ll explain these terms.
Exploring Azure Database Services
The landing page on Azure looks similar to Figure 1. Typically, you may not see all services offered on Azure on the first page. For that, click More Services > All Services (Figure 2).
Microsoft has an impressive list of services broken down into separate sections. The Database section is in Figure 2. Three options for databases might initially confuse new subscribers. I’ll explain them below:
- Azure SQL. It is Microsoft’s platform as a service for SQL Server. It compares to Amazon’s Relational Database Service, but it is 3.6 times faster and 86% cheaper as the providers claim. This option allows you to create either an SQL Database or an SQL Database Server. You can consider Azure SQL as a “mother” for both SQL Server and SQL Database in the cloud.
- SQL Server (also known as an Azure SQL Database Server). This option allows you to create an SQL Server right away. Using on-premise terminology, it is an instance, a logical container for SQL Databases. Note that it is not a physical entity, and it may not map to a virtual machine.
- SQL Database. This option allows you to create a single database. When you make your first database with this route, you must also produce your first SQL Server. If you already have existing servers, select that one where you want to deploy your new database.
Microsoft has an impressive list of services broken down into separate sections. The Database section is in Figure 2. Three options for databases might initially confuse new subscribers. I’ll explain them below:
- Azure SQL. It is Microsoft’s platform as a service for SQL Server. It compares to Amazon’s Relational Database Service, but it is 3.6 times faster and 86% cheaper as the providers claim. This option allows you to create either an SQL Database or an SQL Database Server. You can consider Azure SQL as a “mother” for both SQL Server and SQL Database in the cloud.
- SQL Server (also known as an Azure SQL Database Server). This option allows you to create an SQL Server right away. Using on-premise terminology, it is an instance, a logical container for SQL Databases. Note that it is not a physical entity, and it may not map to a virtual machine.
- SQL Database. This option allows you to create a single database. When you make your first database with this route, you must also produce your first SQL Server. If you already have existing servers, select that one where you want to deploy your new database.
Creating Your First Database
Review the easy route for the first database. Starting in Figure 1, we choose to create a resource. It transfers us to the page shown in Figure 3. There, we select the SQL Database.
Then we proceed by populating the options shown in Figure 4:
The concept of a Free Trial subscription is obvious. A resource group is a collection of related resources on Microsoft Azure that are managed together. For example, if I were creating an entire software solution on Azure, I might want to include the databases, virtual machine, and other solution components into the same resource group.
The Database Name must be unique within the server, in the same way as database names on-premise must be unique within an SQL Server instance.
The Server name, on the other hand, must be unique globally across the entire Azure platform. If someone else has already used a name on their subscription, you cannot use the same name.
I use the Basic service tier, which is the lowest service tier. The service tier’s meaning is determining the size of the database, performance, recovery features, and recovery characteristic of a SQL Database in the cloud. We select the service tier according to the budget and the use case in question.
Click Next: Networking when done.
Note the information about the Firewall rules. Azure SQL allows us to configure firewall rules at BOTH the server and database levels. Later, we’ll demonstrate a simple approach to this capability usage.
At the Additional settings tab, we choose to use existing data from the AdventureWorksLT sample database. After that, our Logistics database will contain data from AdventureWorksLT.
Tags may be useful for the organization in terms of billing, but we shall skip that for now.
We can now see the summary of options chosen (Figure 7), including the estimated monthly cost of our database. Click Create, and the database will be deployed (Figure 8).
It takes a few minutes to complete the deployment. While this is in the process, we see that our server already has an existing database. Thus, you can have multiple databases on an Azure SQL Database Server, just like we have in on-prem instances.
It takes a few minutes to complete the deployment. While this is in the process, we see that our server already has an existing database. Thus, you can have multiple databases on an Azure SQL Database Server, just like we have in on-prem instances.
Reviewing the SQL Server Using Management Studio
Once the deployment is complete, an overview of the database appears on a dashboard. Our Server Name is igirisrv01.database.windows.net. We can use this name for an SQL Server Management Studio session and any SQL Server instance (Figure 10).
In the course of creating this server earlier (not shown in this article), we made the SQL login credentials. Currently, we are using them (kigiri). Azure SQL also supports Active Directory credentials.
When we try to log in, we face something alien for on-premise instances. It prompts us to provide our Azure account details. It happens because we did not explicitly allow our client IP address on the SQL Server firewall. We mentioned earlier that firewall rules on Azure SQL are defined at both server and database levels.
After providing our Azure subscription credentials, we introduce a new firewall rule to access the SQL Server. We created that server in Azure from our local computer. Now, we can log in to the instance (Figure 12).
Note a few more things in Azure SQL that are new, compared to on-premise installations of SQL Server (Figure 13). First, there is only a single system database — master. Then, the SQL Server version is 12.0.2000.8. Strictly speaking, it is not an SQL Server as we know it, but Azure SQL that has the latest version 12. When we use Azure SQL, there is no need to worry about updates and patches. They come automatically.
Finally, the data are already existing in the Logistic database we created. We opted to populate the new database with data from the AdventureWorksLT sample database.
Conclusion
Deploying databases on Azure SQL Data is helpful. There is no need to worry about maintaining operating systems, patching SQL Server, setting up backups, setting up HA, and DR, and even troubleshooting. Many everyday database tasks are offloaded to Microsoft. A little more demand is for the DBA to learn cloud concepts and technologies.
A few things have changed in SQL Server as we know it on-premise. As most organizations consider migration to the cloud as a cheaper, more efficient, and more secure option, traditional database administration assigns to data scientists, developers, architects, or other related roles.
Azure SQL is on the leading edge in providing a database as a service in today’s fast-paced world.
Originally published on https://codingsight.com/deploying-a-database-on-azure-sql/, a community platform for database administrators and Microsoft stack technologies specialists.
Subscribe here to get more industry insides 👋
