Microsoft Azure is a cloud computing service for building, testing, deploying, and managing applications through the Microsoft data centers. It provides the Software as Service (SaaS), Platform as Service (PaaS), and Infrastructure as a Service (IaaS), and supports different database servers, programming languages, and tools.
Azure allows creating databases on various database server platforms, both open source and paid. The deployment is fast and scalable. Besides, the Azure license model is flexible, which helps to reduce the infrastructure costs.
The current article will highlight the following points:
1. Create an Azure SQL Server instance.
2. Connect to the Azure SQL Server using SQL Server Management Studio.
3. Create an Azure SQL database using SQL Server Management Studio.
Let’s proceed to these points.
To create a new Azure SQL Server instance, log in to the Azure portal with your credentials. On the welcome screen, click SQL databases:
In the next SQL Databases screen, click Create SQL Database:
You should specify the following:
1. Subscription or the Resource group.
2. Database Server
3. Compute + storage.
4. Use the SQL elastic pool.
In this demonstration, I have chosen the Pay-As-You-Go subscription.
To create a new resource group, click Create New. Provide the desired resource group name and click OK.
In the Database name field, set the appropriate name for your database. To create a new SQL Server, click Create New:
In the New Server section, provide the following details:
After that, we get to the Create SQL Database screen demonstrating the data we’ve already entered. Here, we need to configure the Compute + storage parameters.
I am using the Basic variant in this demonstration, but you can select another option for your requirements.
Click Next: Networking to proceed to the further configuration step.
The Networking section provides the options for configuring the network access and connectivity for the Azure SQL database.
In this demonstration, I am using the Public endpoint.
Also, I have enabled the Add current client IP Address option to connect to the Azure SQL Database. It adds an entry of my current IP address to the server firewall.
Click Next: Additional settings to configure the following details:
We are installing an AdventureWorksLT database so click on Sample. We were not changing the collation and did not enable the Azure Defender for SQL. Click on Review + create.
The deployment process starts. Once it is complete, you can see the Azure SQL Server instance and the Azure SQL database on the All Resources page.
To connect to the Azure SQL Server instance, we need Server Name, Username, and password. These details are present on the SQL Server resource group page.
Log in to the Azure portal and click on the Azure SQL Server instance named myazuresqlserverdb. The server name and admin login of the Azure SQL Server instance will be on its resource page:
Now, let’s apply the SQL Server Management Studio.
In the Connect to Server window, specify the details:
A new dialog window will open. There, you should add the firewall rule.
Note: When the IP Address of the workstation/Network used to create Azure SQL Server differs from the IP Address of the computer used to connect to the Azure SQL Server, the dialog window opens. It allows us to add the IP Address of the computer used to connect to the Azure SQL Server.
Click Sign In.
Provide the appropriate username and password of the Microsoft account.
Once you are successfully authenticated, you can set the firewall rules. The Firewall Rule dialog box gets enabled. There, you need to provide the desired name, the IP Address, and click OK.
We have successfully connected to the Azure SQL instance. To check the Azure SQL Server version, run the following command in the SSMS query editor:
Now, let us create an Azure SQL database.
In this section, we’ll explore creating the SQL database with the help of SSMS. Another popular tool for doing the task is dbForge Studio for SQL Server that applies to both SQL Server and SQL Azure. You can use this tool as an alternative.
Here, let us examine creating the Azure SQL Database with the SQL Server Management Studio.
Open SSMS > right-click on Databases > New Database.
The New Database window opens.
Note: The SSMS wizard serving to create and configure a new database is entirely different from the wizard that we use for creating an on-premises database.
Enter the desired database name into the Database Name field:
In the Options section, you can see various options to configure the SQL database. Apply or change them according to your business requirements:
Click OK to create the database. Once it is created, you can view it in the Databases folder in Object Explorer:
Alternatively, you can run the following query to view the database:
select database_id,a.name,b.name,a.create_date,compatibility_level,collation_name from sys.databases a left join sys.server_principals b on a.owner_sid=b.sid
This article demonstrated the ways of configuring an Azure SQL Server instance. Also, it explained how we can connect it using SQL Server Management Studio.
If you liked the article, go on CodingSight to find out more similar ones. Here you can find everything you need to know about SQL and other database technologies.
Previously published at https://codingsight.com/connect-to-sql-azure-via-ssms/