If you have ever wondered and would like to know how to Deploy and configure servers and database for Azure SQL and query some sample data then this article is just the cup of tea you need to get you going.
Before you get started make sure you have the following:
Prerequisites
- Azure subscription account (you can create a free trial)
- Azure Data Studio
- SQL Server Management Studio-SSMS
Firstly in order for you to deploy and configure anything you need to be on the Azure portal and create the server and database for Azure SQL. These two go hand in hand as the database would require a database server in order to run.
STEP 1: Sign in to the Azure portal (https://portal.azure.com).
STEP 2: Create a new resource group for your Azure SQL deployment.
STEP 3: Create a new Azure SQL Server instance.
You will need to specify a server name, choose the subscription, resource group, location, and administrator login and password.
STEP 4: Create a new Azure SQL database.
Search for database and click on SQL Database and click create
Click the + symbol or click on create
Under the Basic Tab:
Select the subscription account and and choose the resource group you previously created.
Fill in the Database name , make sure that the Server you have created is selected, if you have any issues you can always click on the Create new link and add a new server.
Next up is the Service Tier, Compute + storage , the default compute and storage is a bit costly and not really what we need so we'll configure the preferred service tier and go for the basic tier .
Next up you have to configure backups and disaster recovery settings for your Azure SQL database.
By default, Azure SQL Database stores backups in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region.
Under the Networking tab:
Configure firewall settings for the server. By default, Azure SQL will block all connections that originate outside of Azure.
Set the connectivity method to Public Endpoint . Adjust the firewall settings to *Yes * to Allow access to the server, and to add the current client IP address
Under the Additional Settings tabs:
You can choose to create an empty database, import an existing database, or create a database from a template. For this exercise you can select use sample data .
Now you can create and deploy your database.
Once it has completed deploying you can click on the Go to resource button to navigate your database resources and settings.
Now you need to configure you firewall.
Add a client IP address. You can use the Azure portal to specify which IP addresses or ranges can access your Azure SQL server.
STEP 5: Test your connection
Test the connection to the Azure SQL database to ensure that everything is working as expected.
Click on Open with and choose Azure Data Studio
Click Launch it now link
Click Open Azure Data Studio
Connect by entering the details of your database and sql server
Well done you have connected and here is your database
If you have downloaded Microsoft SQL Server Management Studio
You can simply open the app and connect to the database.
Congratulations! Here is your database
STEP 6: Query data
You can go a step further and query some data as indicated below and execute to run the query.
SELECT * FROM SalesLT.Product
WHERE color = 'red';
Summary
What we have done and learned in summary is the process of setting up infrastructure to host and manage data in a scalable and secure manner on Microsoft's cloud platform, Azure.
We went a step further and connected the database to SQL Server Management Studio-SSMS.
Lastly we queried some sample data that is provided by Microsoft.
Top comments (0)