Mastering Azure SQL Database: A Practical Guide to SSMS Integration

INTRODUCTION

A completely managed relational database service, Azure SQL Database enables smooth interaction with SQL Server Management Studio (SSMS). This tutorial explains setting up security settings, creating an Azure SQL database, and running SQL queries.

PROCEDURE

Part 1: Creating an Azure SQL Database

1. Sign in to Azure Portal
   • Navigate to Azure Portal and log in.
2. Create a New SQL Database
   • Click Create a resource > Search for SQL Database > Select Create.
3. Configure Database Settings
   • Choose a subscription and resource group.
   • Provide a Database Name
   • Click Create new server, set authentication to SQL auth only, define an admin login and password, and select a region.
   • Choose Basic pricing for this lab.
   • Click Review + create > Create.
4. Configure Firewall Rules
   • Navigate to SQL Server > Networking.
   • Click Add Client IP > Save.

Part 2: Connecting to Azure SQL Database with SSMS

1. Launch SSMS on your computer.
2. Connect to the Server
   • Server type: Database Engine.
   • Server name: Copy from Azure Portal.
   • Authentication: SQL Server Authentication.
   • Login: Use the admin username and password.
   • Click Connect.
3. Expand Databases in Object Explorer to view.

Part 3: Running SQL Queries in SSMS
1.Create a Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
);

Click Execute.

2.Insert Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', '[e-mail]'),
       (2, 'Jane', 'Smith', '[e-mail]');

Click Execute.

3.Query the Data

SELECT * FROM Employees;

Click Execute.

4.Update and Delete Data

UPDATE Employees SET Email = '[e-mail]' WHERE EmployeeID = 1;
DELETE FROM Employees WHERE EmployeeID = 2;

Click Execute.

Part 4: Additional Features

1. Query Performance Insight
   • Navigate to your database in the Azure Portal.
   • Under Intelligent Performance, open Query Performance Insight to monitor slow queries.
2. Enable Automatic Tuning
   • Go to Automatic tuning > Enable options like Force Last Good Plan.
3. Backup and Restore
   • Azure SQL Database provides automatic backups.
   • To restore, go to Backups > Point-in-time restore.

Writing SQL Queries in Azure Portal
1.Open Query Editor in Azure Portal under SQL Database > Query Editor (preview).
2.Log in using SQL Authentication.
3.Run Queries Directly in the Portal

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    PhoneNumber NVARCHAR(15)
);

Click Run.

CONCLUSION

This guide covered creating and configuring an Azure SQL Database, connecting to SSMS, executing SQL queries, and exploring advanced features like performance insights and backups. These foundational skills are essential for managing databases in a cloud environment.