Connecting to Azure SQL Database using Azure Data Studio is a straightforward process that requires a few simple steps. First, you need to download and install Azure Data Studio from the official Microsoft website.
To begin, launch Azure Data Studio and sign in with your Azure account credentials. This will give you access to your Azure resources and allow you to connect to your Azure SQL Database.
Once you've signed in, navigate to the "Connect to Server" dialog box by clicking on the "Connect to Server" button in the top navigation bar.
Prerequisites
Before we dive into connecting Azure Data Studio to Azure SQL, let's make sure you have the necessary prerequisites in place.
You'll need to install Azure Data Studio, which is a free, open-source tool for data professionals.
Don't have an Azure SQL server? Complete one of the following quickstarts to create a server: Create DB - Portal, Create DB - CLI, or Create DB - PowerShell. Be sure to remember your fully qualified server name and sign-in credentials for later steps.
Connecting to Azure SQL
Connecting to Azure SQL is a straightforward process that requires a few pieces of information. To create a connection, you'll need the database name, hostname or IP address, port number, and SSL certificate (if required).
You can use Azure Data Studio to establish a connection to your Azure SQL Database server. To do this, select New Connection in the Connections tab, and a new pop-up will appear where you must provide all connection details, including the server name, user name, and password.
To verify your connection properties, use the query window table. After running a query, review the connection properties at the bottom of the query window to determine which server and database you're connected to, and your username.
Here's a summary of the connection details you'll need:
Connect to Resources
To connect to Azure SQL resources, you can use Azure View. This tool provides quick access to help you connect to SQL Server instances and databases for query and management.
You can explore the SQL resource you'd like to connect with from the tree view, right-click on it, and select Connect. Alternatively, you can find the connect button at the right of the resource. In the opened Connection dialog, select the appropriate Authentication type, enter your authentication information, and select whether you want to add the connection to a Server group.
To connect to Azure SQL resources, you'll need to provide the following details:
- Server name: The fully qualified server name, something like servername.database.windows.net.
- Authentication: SQL Login, which is the type of authentication used in this tutorial.
- User name: The server admin account user name.
- Password (SQL Login): The server admin account password.
- Save Password?: Yes or No, depending on whether you want to enter the password each time.
You can also use Azure Data Studio to establish a connection to your Azure SQL Database server. To do this, select New Connection in the Connections tab, and provide all connection details, including the server name, user name, and password.
Once you've entered all the details, click Connect to establish the connection. If the connection is successful, you'll see a confirmation message.
Create the Tutorial
To create the TutorialDB database, right-click on your Azure SQL server in the SERVERS sidebar and select New Query.
Paste the following SQL into the query editor: IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N'TutorialDB' ) CREATE DATABASE [TutorialDB] GO ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON GO
Run the query by selecting Run from the toolbar, and notifications will appear in the MESSAGES pane showing query progress.
The database will be created if it doesn't already exist, and the Query Store will be enabled for the TutorialDB database.
Setting Up Azure Data Studio
To set up Azure Data Studio, you'll need to download and install it from the official Microsoft website.
Azure Data Studio is available for Windows, macOS, and Linux, so you can choose the version that suits your operating system.
Once installed, you can launch Azure Data Studio and start exploring its features.
Microsoft Setup
To set up Azure Data Studio, you need to create a connection to Microsoft Azure SQL Database. This involves providing the database name, hostname or IP address, and port number.
You'll also need to set up a secure connection if your database is behind a firewall. This is especially true for Private connectivity.
To do this, you'll need to set up a secure connection using an SSL certificate, if required by the database server.
If you're using IBM Cloud Satellite, you'll need to set the Proxy connection policy on the Azure server to connect to the Azure SQL Database.
Here are the required details for connecting to Microsoft Azure SQL Database:
- Database name
- Hostname or IP address
- Port number
- SSL certificate (if required)
Create Project
To create a Database Project in Azure Data Studio, start by clicking the "Create new" button in the Database Projects window. This will open the Create new database project window.
Select Azure SQL Database as the project type and provide a name for your project. Choose a location where you cloned your Git repository. Make sure to select the SDK-Style Project checkbox.
Click the "Create" button to generate the new project, and your SQL Database Project file will be created. The SQL Database Project file will be created upon clicking "Create".
In the DEV Environment, it's crucial to integrate Git with SQL Database objects from the beginning. This can be done by leveraging the SQL Data Projects extension.
To connect to an existing database, open the Database Projects window and fill in the following details:
- Server/Database: Choose the SQL Server and Database for connection.
- Location: Folder location where you cloned the git repo.
- Folder Structure: Select Schema/Object Type (other types are optional).
- Update Action: Apply all changes or View Changes in Schema Compare if you prefer to review changes.
Click the "Update" button, and a progress window will appear in the right-hand corner. After a brief wait, all Database objects will be added to the Database Project.
Sources
- https://dataplatform.cloud.ibm.com/docs/content/wsj/manage-data/conn-azure-sql.html
- https://learn.microsoft.com/en-us/azure-data-studio/quickstart-sql-database
- https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-azure-sql
- https://learn.microsoft.com/en-us/azure-data-studio/azure-view
- https://bakshiharsh55.medium.com/setting-up-git-in-azure-data-studio-for-sql-synapse-pools-2f084140ad2a
Featured Images: pexels.com