Are you starting to experiment with Azure SQL Databases? Is your company interested in moving to the Cloud and asking you to investigate different options?
This blog will show you three options to connect to an Azure SQL Database using the same tooling you would use for on-premises SQL Databases.
I will demonstrate the three tools: SQL Server Management Studio, Azure Data Studio, and PowerShell. No matter your favorite, I will show you what you need to know to connect.
SQL Server Management Studio
Every DBA has a love or hate relationship with SSMS. However, over the last few years, the tool has become more reliable as it seems that Microsoft has dedicated more focus.
To perform work on an Azure SQL Database, the first thing you will do is Connect.
We will use a SQL Account to keep the demo simple for this example. There are different authentication types for an Azure SQL Database, but we will save those for another blog post.
First, you will input the Server Name from Azure. For our demo, we will use sqldemodb2022.database.windows.net. Secondly, we will select the proper Authentication type to be used. As mentioned above, I will utilize SQL Server Authentication for this demo. Third, enter the Login you created when you built your Azure SQL Database. For this example, I have configured sqlAdmin. And finally, input the Password and click Connect.
Once a connection is established, you will browse the database just like usual.
You can see we have a DemoDB and then a list of all of the tables in this database. Querying data and navigation will now be the same as if this were an on-premises SQL Server database.
Azure Data Studio
Now let us take a look at Microsoft’s latest tool for querying and working with SQL Server databases. Azure Data Studio is a new tool framework that utilizes the popular VS Code architecture. While it provides some of the same functionality built-in, it is much more extensible.
One good thing about this tool is that it comes pre-installed with SSMS starting with version 18.7. So you don’t have to fumble around to find Azure Data Studio. It is just there!!!
Azure Data Studio has a different look than SSMS, but navigation is straightforward.
The first thing you want to do is click the New drop-down menu. Then, choose New Connection to open the connection dialog box.
You will enter the information from the Connection Detail screen just like it was done for SSMS. First, the Server will be our demo server sqldemodb2022.database.windows.net. Next, you will choose the Authentication Type of SQL Login. Third, enter the User Name used to create the Azure SQL Database. And finally, enter your Password and click Connect.
You can see we get our database and then table list just like SSMS. Now we can query data similar to how you would in SSMS.
Powershell
The final connection method I will demonstrate is PowerShell and dbatools. These two tools combined provide a very powerful way to manage SQL Server, whether on-premises or Azure.
First, you will open a PowerShell prompt with your terminal of choice. Then you will run the Connect-DbaInstance function to make the initial connection to the database server. I will store this in the $server variable so I can reuse the connection information.
See in our command line that the SqlInstance used matches the server name sqldemodb2022.database.windows.net that we used in earlier demos. Then enter the Database name DemoDB. Next, for authentication, you will populate the SqlCredential parameter. And finally, when you hit enter to execute the command, you will be prompted to input the Password for the credential you used and click OK.
Now we have a variable loaded with the connection information that we can utilize on different functions in dbatools.
Using the Get-DbaDatabase function, you can see how the DemoDB information is returned and data from the master database.
Conclusion
To recap, I walked you through three different methods for connecting to an Azure SQL Database. Of course, this topic can be more complex depending on your authentication type and other configuration options that can be set up on the Azure SQL Database.
