SQL database is the flagship product of Microsoft in the database area. It is a general-purpose relational database that supports structures like relation data – JSON, spatial, and XML. The Azure platform fully manages every Azure SQL Database and guarantees no data loss and a high percentage of data availability. Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failure, deploying bug fixes, failovers, database upgrades, and other maintenance tasks.

kallu

There are three ways we can implement our SQL database

Managed Instance: This is primarily targeted towards on-premises customers. In case, if we already have a SQL server instance in our on-premises data-center and you want to migrate that into Azure with minimum changes to our application and the maximum compatibility. Then new will go for the managed instance.
Single database: We can deploy a single database on Azure its own set of resources managed via a logical server.
Elastic pool: We can deploy a pool of databases with a shared set of resources managed via a logical server.

We can deploy the SQL database as an infrastructure as a service. That means we want to use the SQL server on an Azure virtual machine, but in that case, we are responsible for managing the SQL server on that particular Azure virtual machine.

Purchasing model

There are two ways we can purchase the SQL Server on Azure.

VCore purchasing model: The vCore-based purchasing model enables us to independently scale compute and storage resources, match on-premises performance, and optimize price. It also allows us to choose a generation of hardware. It also allows us to use Azure Hybrid Benefit for SQL Server to gain cost savings. Best for the customer who values flexibility, control, and transparency.
DTU model: It is based on a bundled measure on compute, storage, and IO resources. Sizes of the compute are expressed in terms of Database Transaction Units (DTUs) for single databases and elastic Database Transaction Units (eDTUs) for elastic pools. This model is best for customers who want simple, pre-configured resource options.

Azure SQL Database service tiers

General Purpose/ Standard model: It is based on a separation of computing and storage service. This architectural model depends on the high availability and reliability of Azure Premium Storage that transparently copies database files and guarantees for zero data loss if underlying infrastructure failure happens.
Business Critical/ Premium service tier model: It is based on a cluster of database engine processes. Both the SQL database engine process and underlying mdf/ldf files are placed on the same node with locally attached SSD storage providing low latency to our workload. High availability is implemented using technology similar to SQL Server Always On Availability Groups.
Hyperscale service tier model: It is the newest service tier in the vCore-based purchasing model. This tier is a highly scalable storage and computes performance tier that leverages the Azure architecture to scale-out the storage and computes resources for an Azure SQL Database beyond the limits available for the General Purpose and Business Critical service tiers.

SQL database logical server

It acts as a central administrative point for multiple single or pooled database logins, firewall rules, auditing rules, threat detection policies, and failover groups.
It must exist before we can create the Azure SQL database. All databases on a server are created within the same region as the logical server.
The SQL database service makes no guarantees regarding the location of the database in relation to their logical servers and exposes no instance-level access or features.
An Azure database logical server is the parent resource for databases, elastic pools, and data warehouses.

Elastic pools

It is a simple and cost-effective solution for scaling and managing more than one database. The databases inside an elastic pool are on a single Azure SQL Database server and share a group of resources at a fixed price.
We can configure resources for the pool based either on the DTU- based purchasing model or the vCore-based purchasing model.
The size of a pool always depends on the aggregate resource needed for all databases in the pool. It determines the following options:
- The maximum resources utilized in the pool by the databases.
- The maximum storage bytes utilized in the pool by the databases.

Creating an Azure SQL Database using Azure portal

Step 1: Click on create a resource and search for SQL Database. Then click on create.

Step 2: Fill all the required details.

Step 3: Select a server or create a new one, as shown in the figure given below.

Step 4: Now, select the pricing tier by clicking on Compute + Storage, as shown in the figure below.

Step 5: After that, click on Review + Create and create the SQL database for your apps.

Step 6: Your SQL database is now created, now click on the go-to resources to configure additional settings for your database.

Azure SQL Database Configuration

We’ll see here the key configuration features of the Azure SQL Server and SQL database. In terms of Azure SQL database configuration, the first key thing is Firewall rules at a server level.

Firewall Rules

At a logical server within Azure, we can define some firewall rules. It can be IP rules. IP rules will grant access to the database based on the originating IP address of each request. And the second type of rule is the virtual network rule. It is based on virtual network service endpoints.

Rules for Azure SQL databases can be defined at two levels:

Server level firewall rules: These firewall rules enable clients to access our entire Azure SQL server, i.e., each database within a similar logical server. These firewall rules will be stored in the master database. Server-level firewall rules can be configured by using the portal or by using Transact-SQL statements.
Database-level firewall rules: These rules enable clients to access certain (secure) databases within the same logical server. We can create these rules for each database (including the master database), and they are stored in the individual databases.

Configuring Firewall rules in Azure portal

Step 1: Go to the firewall setting in your database server that you have already created. After that, click on Add Client.

Step 2: Now, click on Add existing virtual network and fill the required details, as shown in the figure below.

Step 3: Finally click on save, you will get the notification that your firewall rules got updated.

Geo-Replication

kallu2

It is defined at a database level, not server level, and it is designed as a business continuity solution that allows the application to perform quick disaster recovery of individual databases in case of a regional disaster or large scale outage.

When we are configuring geo-replication, we specify a secondary database at a location far away from the primary location. We can have a traffic manager that routes the traffic by default to our primary load balancer and that the primary load balancer is based on the application request. If it is read and write, then it can route to a primary logical server. If it is ‘read-only’, it can route to a secondary server. Thereby the advantage of geo-replication is that we can offload some of the read-only traffic from primary and route to secondary.

The primary performance will be good because read-only queries will consume a certain amount of CPU or DTU units installed that we have a secondary database where the data continuously get replicated.

Configuring Geo-Replication using Azure portal

Step 1: Click on the Geo-Replication option; you will see the following window.

Step 2: Now, select the location where you want to replicate your database. You can choose multiple locations.

Step 3: Now, create a SQL server for the place where you want to replicate your data.

Step 4: Your server has been created and replicated successfully.

Step 5: You can see in the following figure, where the servers are replicated.

Failover Groups

Auto-failover group is a feature of the SQL database that allows us to manage replication and failover of a group of databases on a logical server or all databases in a Managed Instance to another region.

We can initiate failover manually, or we can delegate it to SQL Database service based on a user-defined policy. When we are using auto-failover groups with automatic failover policy, any outage that impacts one or many of the databases in the group results in automatic failover. It allows the read-write SQL application to transparently reconnect to the primary database when the database change after failover.

Database backups

The SQL database uses SQL server technology to create full, differential, and transaction log backups for Point-in-time Restore (PITR). The transaction log backups generally occur every 5-10 minutes, and differential backups occur typically every 12 hours, with the frequency based on the compute size and amount of database activity. Each SQL database has a default backup retention period between 7 and 35 days that depends on the purchasing model and service tier.

Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point-time restore. These backups are copied to different storage blobs if the LTR policy is configured. We can set an LTR policy for each SQL database and specify how frequently we need to copy the backups to the long-term storage blobs.

So, this brings us to the end of blog. This Tecklearn ‘Azure SQL Database and its Configuration’ blog helps you with commonly asked questions if you are looking out for a job in Azure and Cloud Computing. If you wish to learn Microsoft Azure and build a career in Cloud Computing domain, then check out our interactive, Microsoft Azure Developer and Administrator Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/microsoft-azure-developer-associate-az-203-and-microsoft-azure-administrator-associate-az-103/

Microsoft Azure Developer Associate: AZ-203 and Microsoft Azure Administrator Associate AZ-103 Training

About the Course

This Course provides training for Microsoft Azure Administrator and Azure Developer. Tecklearn’s Azure Administrator training provides you with a deep understanding of the entire administrative lifecycle in Azure environments. This Azure course shows you how to maintain services related to computing, storage, network, and security. Enhance your Azure Administrator skills and prepare to ace the AZ-103 Azure Administrator exam. The Azure Developer certification course prepares you for Microsoft’s Azure Developer certification exam AZ-203. It covers Azure architecture, Azure technology development solutions, Azure storage services, and solutions, Cognitive Services such as Computer Vision, Q&A Maker, Azure service solutions, and API management services.

Why Should you take Azure Developer and Administrator Training?

Microsoft certified Azure Associate developer earns salary ranging from $95,000 to $135,000
Average salary of Microsoft Certified Azure Administrator is $90,000 – Indeed.com
Apple, eBay, Samsung, Citrix, UST Global, Mindtree, TCS, Wipro, Infosys & many other MNC’s worldwide use Azure across industries
According to Microsoft, more than 1,000 new sign-ups for Azure occur each day, which equates to approximately 365,000 new sign-ups each year
By 2022, 90% of enterprises will use both the IaaS and PaaS capabilities from cloud provider – Gartner

What you will Learn in this Course?

Introduction to Azure Compute Solutions and Cloud Computing

Introduction to Microsoft Azure
About Azure Certification

Overview of Azure Storage Services

Azure Storage
Azure File Use Case
Azure DNS
Azure Site Recovery

Secure and Manage Azure Storage

Security Issue
Azure Regions
Azure Services

Implementing Secure Data Solutions and Integrating Caching & CDN

Azure CDN
Azure Traffic Manager
Azure Load Balancer
Azure Scale Set

Implementing Azure App Service Web Apps and Mobile Apps

Design and Implement Azure Service Apps
Web Apps
Pricing Calculator – Azure

Managing Azure Subscriptions and Resource Groups

Create Resource Group
Create App Service Plan
Create Web App and Deploy Angular Application using SCM
Deploy .NET Application using SCM
Deploy App using Visual Studio
Web Job Types
Sendgrid

Develop Event-based and Message-based Solutions in Azure

Messaging Strategy
Design and implement Messaging Strategy
Azure Notifications
Microsoft Azure Service Bus
Queues
Topics
Create Topic
Create Subscription
Azure Relay
Using EventHubs

Implementing Azure App Service API Apps & Azure Functions

Azure PAAS Services
API Management
Function Apps
Logic Apps

Overview of Azure Virtual Machines and Configure Virtual Machines for High Availability

Virtual Machines
Create VM
PowerShell DSC and Custom Script Extension
Scale ARM VMS
VMSS
Monitoring VMs
Dev Test Labs
VM Storage

Design and implement Azure DevOps

CI/CD Pipelines
VSTS
Deployment in pass and VM’s
Scheduled deployments

Manage Azure Active Directory (AD)

Manage Identity, Application and Network Services
Overview of Azure Active Directory
Azure Active Directory B2C
Azure Active Directory B2B
Key Vault
Azure Graph API

Azure Virtual Networks and Network Security

Redis Caching
Azure search
Virtual Networks
Configure Virtual Network
Hybrid Network Connectivity
ARM VM Networking
Azure security and recovery services

Developing Solutions That Use Relational Database and Azure Blob Storage

Azure DB Services – SQL DB
Azure Notifications
Backup and Restore
Enabling Geo-Replication
Export source Database
Scale Azure SQL Databases

Developing Solutions That Use Azure Table Storage & Cosmos DB

COSMOS DB
Azure Key Vault
Azure App Insights

Azure Command Line Interface (CLI) and PowerShell

Resource management and deployments using PowerShell and CLI
ARM templates
Implement ARM templates
Control Access
PowerShell runbooks
Azure Automation
Real Time Examples

Got a question for us? Please mention it in the comments section and we will get back to you.

688