How To Deploy SQL Database To Azure

Well, let’s discuss here, how to deploy a database from local to Azure. Follow the below steps to deploy the SQL database to Azure Portal using SQL Server Management Studio (SSMS).

Deploying SQL Database To Azure With SQL Server Management Studio (SSMS)

Note that, in this article, we will use the “deploy the database to SQL Azure” option from the SQL Server Management Studio (SSMS) to deploy the local SQL database to Azure.

How To Publish SQL Database To Azure

Before starting the actual functionality, we should know the Prerequisites needed here.

Prerequisites

Below are the Prerequisites needed here.

You must have an Azure Subscription or Azure Account, If you don’t have an Azure Account till now, You can create an Azure free account now.
You must install SQL Server Management Studio on your local or dev machine.

Assuming, you are ready with all the prerequisites needed here, let’s start with the actual functionality.

Deploy SQL Database To Azure

The first step is to open the SQL Server Management Studio (SSMS) and connect it using your credentials.
Now, to deploy the SQL Database to Azure, Right-click on the name of the database –> select the Tasks option –> Click on Deploy Database to Microsoft Azure SQL Database.

3. Now, it will show you the below wizard, Click on the Next button on the Introduction page.

4. On the Deployment Settings tab, click on the Connect button, Specify the Azure SQL Database server name and click on Connect button on the Connect to server window as shown below.

deploy a sql database to azure using sql server management studio

5. Specify the new Database name, specify the edition of Microsoft Azure SQL Database, Provide the maximum database size and the service objective and then click on the Next button on the Deployment Settings tab as shown below.

how to deploy a database from local to Azure

6. Below is the Summary tab, where you can verify if all the provided details are correct, if you want to modify any of the options, you can click on the Previous button, else click on the Finish button to finish the complete process.

how to deploy database from local to Azure

7. On the Results tab, you will see the Operation complete message with all the parameters as a success, and in case of any issue, it will show you error instead of success.

This is How To Deploy SQL Database To Azure using SQL Server Management Studio or How do I deploy an SQL database in Azure?.

Validate The Local Database In Azure

Now, to make sure that the deployment is completed successfully, Let’s login to the Azure Portal to verify if the new database has been deployed successfully.

Log in to the Azure Portal (https://portal.azure.com/).
Search for the SQL databases and then click on the search result SQL databases.

You can able to see the TSINFODB database that we have deployed from the local SQL server management studio to Azure.

ssms deploy database to azure sql database

Click on the database name to find the complete details.

Azure SQL Database Deployment Options

When you are thinking of deploying your SQL database to Azure, you should consider two key deployment options in this case. Those are as below

SQL Server on Azure Virtual Machines
Microsoft Azure SQL Database

Each deployment option has its own benefits and differs in terms of its costs and the control they actually provide you with respect to the platform. Best on your requirement, you need to choose the best deployment options that actually suit for you.

SQL Server on Azure Virtual Machines

SQL Server on Azure Virtual Machines deployment option is an infrastructure as a service (IaaS) solution which runs on a set of standard hardware that is owned and maintained by Microsoft, you no need to take any headache for the maintenance and infrastructure. Another advantage is you can pay as you go model that is already included with your SQL server license.

One more recommendation from Microsoft is, it is always better to use these deployment options if you are trying to migrate the applications that actually require OS-level access. This is one of the best options if you want fast migration with low cost and the coolest option for rapid development.

Microsoft Azure SQL Database

Microsoft Azure SQL Database is a relational database service from Microsoft. It is basically a platform as a service (PaaS). This also runs on a set of standard hardware and software that is owned and maintained by Microsoft, you no need to take any headache for the maintenance part.

One more advantage with Microsoft Azure SQL Database is, this is also based on the pay-as-you-go model. You can also deploy an Azure SQL Database Managed Instance within Azure SQL Database.

The coolest benefit of the Azure SQL Database is, it is nearly 100% compatible with the on-premises SQL Server that helps you a lot during the migration of local SQL database to Azure.

Note that the recommendation from Microsoft is, Better to choose this deployment option when you are working with modern cloud applications and in case you have any time constraints for the development activity.

Now, based on your requirement, you decide which Azure SQL Database Deployment Options suits best for you.

How To Host A Database On Azure

We have already discussed above, How To Deploy SQL Database To Azure. You can refer to the above section for the steps to host a database on Azure.

Migrate SQL Database To Azure

In real time, many time we will have the requirement to migrate the SQL database to Azure. In fact, it is quite easy to migrate the on-premise SQL database to Azure, if you will follow the correct approach.

Check out an excellent article on How To Migrate SQL Database To Azure now.

What is The Size Of SQL Database In Azure

The maximum size of the Azure SQL database depends on which tier or edition of the Azure SQL Database you are using.

If you are using Azure SQL database basic edition, then the maximum size it supports is up to 2 GB.
In case you are using the Standard tier of Azure SQL database, then it supports up to 250 GB.
If you are using the Azure SQL Database Premium tier, then it supports up to 1 Tb.

This all about the maximum size of the Azure SQL Database.

What Is DMV Collector In Azure SQL Database

Well, here we will discuss DMV collector in the Azure SQL database. DMV is nothing but Dynamic Management Views.

Dynamic Management Views is responsible for providing you the information about the server state which can help you to easily monitor the server health and helps you to easily diagnose the problems of the server, Based on the server information, you can easily tune the performance of the server.

Basically, Dynamic Management Views and functions are categorized into two main types. Those are as below

Server-scoped dynamic management views: You need to provide the VIEW SERVER STATE permission on the server in case of Server-scoped dynamic management views.
Database-scoped dynamic management views: You need to provide the VIEW DATABASE STATE permission on the database in the case of Database-scoped dynamic management views.

You can refer the Dynamic Management Views in Transact-SQL with the help of two-part, three-part or four-part names.

An important point to note down here is, you must need the SELECT permission on object and the VIEW DATABASE STATE permission or the VIEW SERVER STATE permission to query the Dynamic Management Views.

This permission actually helps you to restrict the user to access the dynamic management views.

The complete process to this restriction functionality is as below

The first thing is, in the Master DB, you need to create a user.
As a second step, on the dynamic management views, you need to deny the user SELECT permission.

Now, it will not allow the select operation from these dynamic management views.

You may also like following the below articles

Wrapping Up

Well, in this article, we have discussed, How To Deploy SQL Database To Azure, Deploying SQL Database To Azure With SQL Server Management Studio (SSMS), How To Publish SQL Database To Azure, Azure SQL Database Deployment Options, Migrate SQL Database To Azure, How To Host A Database On Azure, deploy SQL server database to Azure, What is The Size Of SQL Database In Azure, What Is DMV Collector In Azure SQL Database. Hope you have enjoyed this article !!!