Setting up Azure SQL Database for your app

Posted on by Panther

Introduction

Azure SQL Database is a PaaS (Platform as a Service) offering from Microsoft Azure that provides a fully-managed, highly available, and scalable relational database service in the Cloud. It is built on the latest version of SQL Server engine and provides features such as automated backups, security, and performance tuning. This tutorial is a step-by-step guide for setting up Azure SQL Database for your application and covers the following topics:

  • Creating an Azure SQL Database
  • Creating Firewall rules for secure access
  • Connecting to Azure SQL Database using SQL Server Management Studio
  • Creating Tables and Data in Azure SQL Database
  • Querying data in Azure SQL Database using SQL Server Management Studio
  • Conclusion

Prerequisites

Before we begin, there are a few prerequisites that we need to have in place.

  1. Microsoft Azure Account – You will need an active Azure account to create and manage Azure SQL Database.
  2. SQL Server Management Studio – This is a tool for managing and querying SQL Server databases, including Azure SQL Database.
  3. Azure SQL Database connection string – You will need a connection string to access Azure SQL Database from your application.

Creating an Azure SQL Database

  1. Log in to the Azure portal using your credentials.
  2. Click on the + Create a resource button in the left-hand navigation pane.
  3. Search for SQL Database and select it from the search results.
  4. Click on the Create button to start the process of creating a new Azure SQL Database.
  5. In the Basics tab, fill in the details for your new SQL Database:
    • Subscription – Select the subscription that you want to use for this resource.
    • Resource group – If you have an existing resource group, you can use it, or create a new one.
    • Database name – Choose an appropriate name for your database.
    • Server – If you don’t have an existing Azure SQL Server, create a new one. You will need to name the server, select a location where the server will be hosted, and provide administrator credentials for the server.
    • Compute + storage – Select the appropriate tier for your database, based on the expected workload. The options include Basic, Standard, Premium, General Purpose, and Business Critical.
  6. Select the Networking tab and configure the firewall settings for your database. Firewall rules control access to Azure SQL Database. By default, all connections from outside Azure are blocked for security reasons. There are two ways to configure the firewall settings.
    • Add your computer’s IP address – This option enables traffic from your computer’s public IP address to connect to your Azure SQL Database. To add your computer’s IP address, select Add client IP.
    • Add Azure Services – This option enables traffic from Azure services to connect to your Azure SQL Database. To add Azure services, select Add Azure services.
    • You can also configure custom firewall settings for specific IP addresses and ranges. Once you are done configuring firewall settings, click on Next: Additional settings.
  7. In the Additional settings tab, you can configure additional options for your database such as Data source, Collation, Sample database, Advanced data security, and Automatic backups. Once you are done configuring, click on the Review + create button to review the configuration.
  8. Review your settings and click on the Create button to create the database. It may take several minutes to create the database.

    Creating Firewall rules for secure access

    By default, all connections from outside Azure are blocked for security reasons, so we need to create firewall rules to enable access to the database. You can create firewall rules during the Azure SQL Database creation process, or you can create them later by following these steps:

  9. Go to the Azure portal and navigate to your Azure SQL Database.

  10. Click on the Firewall and virtual networks option under the Security tab.
  11. Click on Add client IP to add your current IP address, or click on Add IP range to add a specific IP range that you want to grant access to.

Connecting to Azure SQL Database using SQL Server Management Studio

Now that we have created a database and added firewall rules, we can connect to Azure SQL Database from SQL Server Management Studio using the following steps:

  1. Open SQL Server Management Studio.
  2. Click on Connect > Database Engine.
  3. In the Connect to Server dialog box, enter the server name, username, and password that you set up during the Azure SQL Database creation process and click on the Connect button.
  4. Once you are connected, you can browse the databases, tables, views, and stored procedures in your Azure SQL Database.

Creating Tables and Data in Azure SQL Database

Now that we have connected to our Azure SQL Database, we can create tables and data using the following steps:

  1. Right-click on the database where you want to create the table and select New Query.
  2. In the query window, type in the CREATE TABLE statement to create the table. For example, to create a table called Employees with four columns (ID, Name, Email, and Phone), you can type in the following statement:

CREATE TABLE Employees
(
ID int primary key identity(1,1) not null,
Name varchar(50) not null,
Email varchar(50) not null,
Phone varchar(50) not null
);

  1. Execute the query and the table will be created in your Azure SQL Database.
  2. To add data to the table, you can use the INSERT INTO statement. For example, to add an employee record to the Employees table, you can type in the following statement:

INSERT INTO Employees (Name, Email, Phone)
VALUES (‘John Smith’, ‘[email protected]’, ‘555-555-5555’);

  1. Execute the query, and the data will be added to your Azure SQL Database.

Querying data in Azure SQL Database using SQL Server Management Studio

Now that we have created a table and added data, we can query the data using the following steps:

  1. Right-click on the database where your table is located and select New Query.
  2. In the query window, type in a SELECT statement to retrieve data from the table. For example, to retrieve all employee records from the Employees table, you can type in the following statement:

SELECT * FROM Employees;

  1. Execute the query, and the data will be displayed in the Results pane.

Conclusion

In this tutorial, we went through the steps required to set up Azure SQL Database, create firewall rules for secure access, connect to Azure SQL Database using SQL Server Management Studio, create tables and data, and query data. Azure SQL Database provides a fully-managed, highly available, and scalable relational database service in the Cloud. It is built on the latest version of SQL Server engine and provides features such as automated backups, security, and performance tuning. With these steps, you should now be able to set up Azure SQL Database for your application.

Related Post