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. Create an Azure SQL Server Instance and a Database To create a new Azure SQL Server instance, log in to the Azure portal with your credentials. On the screen, click : welcome SQL databases In the next screen, click : SQL Databases 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 subscription. Pay-As-You-Go To create a new resource group, click . Provide the desired resource group name and click . Create New OK In the field, set the appropriate name for your database. To create a new SQL Server, click : Database name Create New In the section, provide the following details: New Server – the name of the desired server. Server name – here you need to add an administrator account with the and server role. This account is necessary for connecting the SQL database. Thus, provide the username for that account. Server admin login dbmanager loginmanager – provide the password for the server administrator account and confirm that password. Password – enter the nearest geographical location where you want to deploy the Azure SQL Server (select it from the drop-down list). Location After that, we get to the screen demonstrating the data we’ve already entered. Here, we need to configure the parameters. Create SQL Database Compute + storage I am using the variant in this demonstration, but you can select another option for your requirements. Basic Click to proceed to the further configuration step. Next: Networking The section provides the options for configuring the network access and connectivity for the Azure SQL database. Networking In this demonstration, I am using the . Public endpoint Also, I have enabled the option to connect to the Azure SQL Database. It adds an entry of my current IP address to the server firewall. Add current client IP Address Click to configure the following details: Next: Additional settings . Here you can create a blank SQL database, restore a backup, or populate the new light-version database with sample data. In our case, we've chosen to create the sample database. Data source AdventureWorks . Here you point to the rules of data sorting and comparing. Specify the default collation of the database. Note: These rules aren’t subject to change after the database creation. Database collation . You can enable it to use the security package or disable it if you prefer other methods. The Defender is paid after the 30-days free trial period. Azure Defender for SQL We are installing an database so click on Sample. We were not changing the collation and did not enable the Azure Defender for SQL. Click on . AdventureWorksLT 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 page. All Resources Connect to Azure SQL Server Instance 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 . The server name and admin login of the will be on its resource page: myazuresqlserverdb Azure SQL Server instance Now, let’s apply the SQL Server Management Studio. In the window, specify the details: Connect to Server should be . The server name myazuresqlserverdb.database.windows.net should be (select it from the drop-down menu). Authentication SQL Server Authentication Provide the appropriate username and password and click . Connect 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 dialog box gets enabled. There, you need to provide , and click . Firewall Rule the desired name, the IP Address 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: Use master Go Print @@version Now, let us create an Azure SQL database. Create the Azure SQL Database Using SQL Server Management Studio In this section, we’ll explore creating the SQL database with the help of SSMS. Another popular tool for doing the task is that applies to both SQL Server and SQL Azure. You can use this tool as an alternative. dbForge Studio for SQL Server Here, let us examine creating the Azure SQL Database with the SQL Server Management Studio. Open SSMS > right-click on Databases > New Database. The window opens. New Database 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 field: Database Name In the section, you can see various options to configure the SQL database. Apply or change them according to your business requirements: Options Click to create the database. Once it is created, you can view it in the folder in : OK Databases Object Explorer Alternatively, you can run the following query to view the database: database_id,a.name,b.name,a.create_date,compatibility_level,collation_name sys.databases a sys.server_principals b a.owner_sid=b.sid select from left join on Output: Summary 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/
