Introduction & Overview
The course is part of these learning paths
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 firstname.lastname@example.org.
- 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.
Azure SQL leverages the Azure platform which allows you to change the performance characteristics of your database on the fly. You can allocate more resources when needed and equally as important reduce assigned resources when workload decreases to reduce costs.
Resources can be scaled vertically or horizontally. Vertical scaling is making qualitative changes to the resources. You can increase computing power with more or faster CPUs and add more Ram and disk storage. As well as infrastructure changes, you can upgrade the SQL server edition to access more features.
Vertical scaling is an easy way to increase system performance, but at a cost. There are physical limits on vertically scaling one system, although it's likely you'll hit the financial limit first. Scaling horizontally is akin to distributed computing where workload can be spread across many smaller systems. This approach can be used to service workloads in different geographic regions.
Horizontal scaling can be an alternative, although more likely to be used in conjunction with vertical scaling, but is scenario specific. The phrase "No free lunch" is as accurate for database performance as anything in the physical universe. And optimizing for performance invariably conflicts with optimizing for cost.
Looking at SQL server running on an Azure virtual machine, we have several configuration options to achieve the sweet spot of maximum performance for least cost. VMs come in many sizes and memory to CPU ratios targeting different workload types. Azure provides VM series that have a higher ratio of Ram to vCores, or higher I/O limit more suited to OLTP and data warehouse systems.
In terms of long-term storage, you can go with old but cheap mechanical hard drives, or select from three types of SSD drive, standard, premium or ultra. Where latency decreases as cost increases. Some VMs allow you to attach multiple disks to a single machine, so you can configure disk striping to further improve hard drive performance.
Some VM support features such as blob cache that further enhance I/O performance. You can achieve additional performance gains by implementing features that are available in specific SQL server editions. SQL managed instances give you most of the functionality available in the SQL server engine, combined with low maintenance features of Azure SQL.
Apart from vertical scaling that we have already discussed, SQL managed instances can be deployed in an instance pool. An instance pool is similar in concept to running multiple instances of SQL server on one physical machine. Instead of one machine that is a pool of vCores that are allocated to the managed instances, each instance maintains its own dedicated CPU and Ram resources, so the sum of these must not exceed the total of the pool.
Overall an instance pool has a maximum storage limit of eight terabytes and cannot host more than a hundred databases. Compute and storage can be scaled independently within an instance pool. Where vCore cost is charged irrespective of the number of deployed instances. Azure SQL database also supports the pool concept. Except that elastic pool databases share CPU and memory resources. This is an easy, cost effective solution for managing multiple databases and is best suited to scenarios where the pool databases experience high workload demands at different times, allowing each to take advantage of more than its fair share of the pool's resources.
As with other resources, elastic pools can be scaled via the Azure portal, PowerShell, Azure CLI, or the Rest API. The time to complete a scaling operation does vary depending on what you are scaling that has compute or memory. But in general, completion time increases with the amount of storage used.
When you change your elastic pools service tier or compute size, there is a minimal impact on the service. When adding new compute to an elastic pool, a replica of the database must be created in a new compute instance, which involves copying data. The amount of data and the power of the new compute will directly impact how long the upgrade option will take. The databases remain online during the provisioning operation, while requests continue being directed to the original compute instance.
If the service tier change requires routing connections to be switched to the new compute instance, then existing connections will be dropped and connections established to the new instance. The connection rerouting does involve a small interruption to service, typically ranging from a few seconds up to 30 seconds.
When scaling storage for standard or general-purpose service tiers, increases or decreases have to be done in 10-gigabyte increments. For premium or business-critical service tiers, increments happen in 250-gigabyte chunks.
Before decreasing storage size, you may need to shrink your database to remove surplus file space. There is a range of tools that can assist you in administering elastic pool databases. Elastic database client library is available for .net and Java frameworks, and focuses on shard management where data is distributed across multiple databases.
The elastic database split-merge tool, moves data between databases, either consolidating or sharding data. Elastic database jobs allow you to run and schedule jobs to run against multiple databases. Elastic database query enables you to perform cross-database queries over multiple Azure SQL databases.
In a similar vein, elastic transactions allow you to run transactions that span numerous Azure SQL or managed instance databases using ado.net within .net applications. Managed instances also support T-SQL distributed transactions, using Microsoft distributed transaction coordinator.
Course Introduction - Azure SQL Databases Overview - Deployment Options - ARM Templates Deployment - DEMO: Deploying Azure SQL Databases - Business Continuity - Security Database Services - 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.