Introduction & Overview
This course explores how to plan and implement data platform resources specifically with regards to Azure SQL offerings. In particular, we will explore the benefits and features of the SQL PaaS offerings along with billing models, service tiers, and high availability options. We'll also cover migration scenarios and provide a demo that migrates a sample database from an on-premise SQL Server to an Azure SQL managed instance using DMS.
If you have any feedback relating to this course, feel free to contact us at email@example.com.
- Get a general understanding of Azure SQL databases
- Learn how to deploy Azure SQL databases
- Understand business continuity and security tie in with SQL databases
- Learn how to scale, upgrade, and partition your databases
- Learn how to migrate a database from an on-premise SQL Server to an Azure SQL instance
- Anyone who wants to learn about Azure SQL Offerings
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
Business continuity is the ability of an organization to continue to operate or provide services in the event of a disaster or some other event that interrupts regular operation. The two main strategies to ensure business continuity are high availability and disaster recovery. High availability refers to mechanisms that allow a seamless switchover to backup or secondary systems when the primary system fails. Disaster recovery acknowledges that there will be some interruption to service, but the aim is to resume normal operation as soon as possible.
High availability solutions are more expensive and complex to implement as they involve keeping at least one replica of production data synchronized at all times. Disaster recovery is usually cheaper to implement and can cover a wide range of solutions that can vary by the amount of potential data loss.
Always On availability groups is a technology built on Windows Server Failover Cluster technology. It requires all VMs that are running SQL Server to be within the same Azure region and belong to the same active directory domain. Within each cluster of database servers, there is a primary server where data is read and written to, and there are secondary replica servers that can be read from. Data can be replicated from the primary to secondary servers, either synchronously or asynchronously. Synchronous replications ensures no data loss, but can impact server performance.
Log Shipping is a tried and tested disaster recovery method where one's database's transaction log can be restored to a replica database, applying all the transactions to the replica, bringing it up to the time the primary database failed. Log Shipping is relatively easy to set up, but it does require the replica database to be regularly updated to ensure minimal data loss.
Transaction logs are usually more up-to-date than a backup, but the replica database is offline while the transaction log is restored. Database backups with a full differential or log, can be saved directly to Blob storage in any region, making them available for disaster recovery, even in the event of a complete Azure region-wide failure.
Azure Site Recovery is a generic mechanism for replicating workloads running on virtual machines in one region to VMs in a secondary location. While not specific to SQL Server, if you are running your database server on a VM, you can utilize this technology. You can implement high-availability solutions without relying on Windows Failover Cluster technology by using the standard availability model. This involves storing stateful data, as in a database's MDF and log files on Azure Blob storage, with its own built-in data redundancy mechanisms.
Suppose Azure Service Fabric detects a failure of the SQL Server Database Engine. In that case, it will spin up another instance of SQL Server on another VM, but accessing the same database files on Azure Blob storage. The same mechanism will be activated in an OS upgrade or other system updates affecting the Database Engine. Database backups are saved to globally redundant storage.
The premium availability model is a variation of the standard model where data is replicated to secondary database servers. Premium availability is a more expensive model, as multiple copies of the data need to be synchronized with multiple secondary replicas kept warm and ready to step in, should the primary fail. However, I/O performance is better, resulting in higher transaction processing rate that is suitable for mission critical applications.
Secondary replica servers can step into the breach during maintenance activities and because the data is synchronized across machines, secondaries can be used for reading data, thereby reducing the workload on the primary server. Like the standard model, premium backups are saved to globally redundant storage.
Hyperscale database architecture is divided into four layers or services that are responsible for specific functionality. The SQL Server engine runs in the stateless compute layer where you can have multiple replica nodes. There is a stateless storage layer of page servers that orchestrate the I/O of information from database files.
A stateful transaction log storage layer is formed by the compute node running the log service process. Transactions are written to disk by the log service and then passed onto the page server that will use the transactional information to update the data files. The data storage layer is the database .mdf and .ndf files that are persisted in Azure Storage. It is the almost instantaneous file snapshot technology that enables Hyperscale databases to be backed up and restored very quickly.
Database files are backed up independently of the server engine, so there is no impact on database performance. Data restoration is also very quick as it is a case of just reverting to the appropriate file snapshot. This means extremely large databases can be restored in minutes, rather than hours or days.
Zone redundant databases and elastic pools are currently only supported in the Premium and Business Critical service tiers in select regions. Zone redundant configuration is only available when using Gen5 compute hardware in the Business-Critical tier.
Zone redundancy is not available for managed instances. To find out which region supports Zone redundant configuration, refer to the Microsoft website. Zone redundant configuration means that a cluster of redundant nodes, or VMs, are created across a minimum of three physically separate data centers within an Azure region.
An Azure Traffic Manager directs requests to the appropriate zone or zones in the event of the primary server being unavailable. Within each zone, a control ring of Gateway Managers directs requests to the appropriate server. Data is synchronized across all zones via an Always On availability group. Databases are backed up to geo-redundant storage.
Azure SQL Databases and SQL Managed Instances, come with automated backup features straight out of the box. Full backups are taken weekly, differential backups every 12 hours, and transaction log backups every 5 to 10 minutes. The backup data is stored in read-only geo-redundant storage for at least seven days for all service tiers to allow for point in time restoration. The retention time for backup data is configurable.
Temporal tables are used to track data changes at row-level and can be used for highly-targeted data restoration. Geo-replication is used to backup your data to another Azure region, making it available in the event of a complete data center failure. Although this will require a manual failover to a replica. Databases can be restored if accidentally deleted, but only if the server still exists. And as we've seen, these auto-failover groups allow for fully automated recovery.
Course Introduction - Azure SQL Databases Overview - Deployment Options - ARM Templates Deployment - DEMO: Deploying Azure SQL Databases - Security Database Services - Scale and Performance - DEMO: Scaling Azure SQL Databases - Partitioning Data - Migrating to Azure - Migration Scenarios - DEMO: DMS Azure SQL Database Migration Process - Upgrade Scenarios - Summary
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.