Azure SQL Database Configuration

Key Configuration Features

Firewall Rules

In Azure SQL Database, firewall rules are critical for securing access to your databases. There are two levels of firewall rules that can be configured:

1. Server-Level Firewall Rules:
   • These rules grant access to all databases within a logical server.
   • Stored in the master database.
   • Can be configured using the Azure portal or Transact-SQL statements.
   • IP Rules: Allow access based on the originating IP address.
   • Virtual Network Rules: Allow access from specific virtual network service endpoints.
2. Database-Level Firewall Rules:
   • Enable access to specific databases within the same logical server.
   • Stored in individual databases, including the master database.

Configuring Firewall Rules in the Azure Portal

Step 1: Navigate to the firewall settings of your database server and click on “Add Client.”

Step 2: To add an existing virtual network, fill in the required details.

Step 3: Click on “Save.” You should receive a notification confirming that your firewall rules have been updated.

Geo-Replication

Geo-replication is a disaster recovery feature designed to ensure business continuity by allowing rapid recovery of individual databases in the event of a regional disaster or large-scale outage. This configuration occurs at the database level and involves:

• Specifying a Secondary Database: Choose a location distant from the primary database for replication.
• Traffic Management: Use Azure Traffic Manager to route application traffic. It can direct read-write traffic to the primary server and read-only traffic to the secondary server, which helps offload some read operations from the primary database.

Configuring Geo-Replication in the Azure Portal

Step 1: Click on the “Geo-Replication” option in the database settings.

Step 2: Select the location for replicating your database; multiple locations can be chosen.

Step 3: Create a SQL server for the selected replication location.

Step 4: Your server should now be created and the replication process completed successfully.

Failover Groups

Auto-failover groups enable the management of replication and failover for a group of databases on a logical server or all databases within a Managed Instance to another region.

• Manual and Automatic Failover: Failover can be initiated manually or set to occur automatically based on user-defined policies.
• Automatic Failover: In case of an outage affecting databases within the group, automatic failover occurs, allowing applications to reconnect transparently to the primary database after the failover.

Database Backups

Azure SQL Database employs SQL Server technology to facilitate backups that support Point-in-Time Restore (PITR):

• Backup Types:
  • Full Backups: Complete copies of the database.
  • Differential Backups: Changes made since the last full backup.
  • Transaction Log Backups: Capture all transactions and database modifications.
• Backup Frequency:
  • Transaction log backups typically occur every 5-10 minutes.
  • Differential backups are conducted approximately every 12 hours, depending on database activity and compute size.
• Backup Retention:
  • Each SQL database has a default backup retention period ranging from 7 to 35 days, depending on the service tier and purchasing model.
• Long-Term Backup Retention (LTR):
  • Allows for the configuration of policies that enable full database backups to be copied to different storage blobs.
  • Each SQL database can have a unique LTR policy that specifies the frequency of backups to long-term storage.