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.
Not only has cloud technology removed the expensive hardware expenditure and maintenance, but it has freed us from many of the repetitive and tedious administrative tasks associated with, in this case, running database servers.
Instead of thousands of system administrators worldwide performing the same generic tasks involved in setting up a database server, the steps to provision a server resource are bundled up into an automated job that can be easily and quickly executed by anyone with the appropriate access. Cloud customers can get on with running their line of business applications rather than be bogged down with a trivial yet time consuming administration.
Specialization and economy of scale are two of the main pillars of cloud technology's success. These have led to the uptake of as a Service solutions where cloud customers can have most of the ownership benefits without the associated headaches.
As-a-service solutions are like the difference between renting a car and owning a car. Use it when you need it, and not be concerned about garaging it when you don't. And like car rentals where various options range from taking an Uber, where you don't even drive, to leasing a car for many months or years, using cloud services offers similar flexibility and customization options.
This graphic illustrates how different SQL database offerings fit within the cost administration and location continuums. Starting at the lower left, we have the traditional on-premise physical machine dedicated to running one organization's databases. In this scenario, you have to buy the computer or bare-metal, house it, hook it up to a network and install the operating system and server software.
From this point on you must ensure that the machine is kept up to date with the appropriate patches, has a reliable power supply and is regularly backed up. The next step up from a dedicated physical machine is a virtual machine. This scenario is most often associated with private cloud providers that supply virtual machines. The cloud provider takes on the responsibility of maintaining the hardware infrastructure but the customer still has to install and maintain the OS and server software. This is where we start to see economies of scale come into play as there can be many virtual machines running on one physical computer.
The next step marks a significant shift to what is commonly thought of as Infrastructure as a Service. Its shown in purple, the hardware in the form of a virtual machine and network, along with the OS and database server are supplied as a package. Cloud customers still have to perform some maintenance tasks like scheduling software patches and configuration, but the cloud provider does most of the heavy lifting.
The next virtualization level involves removing OS and database server engine administration. This is called Platform as a Service or PaaS. You are responsible for administration directly related to running the database server, like managing logins, maintaining indexes, implementing data security, and ensuring the databases are performing optimally. All elements that the database relies on to function correctly like hardware, OS virtualization, and database server engine are the responsibility of the cloud provider.
Running SQL Server on an Azure virtual machine is the most common form of Infrastructure as a Service regarding relational databases. IaaS is the easiest migration path from on-premise to the cloud. And is often termed lift and shift as an on-premise SQL Server database backed up can be restored directly to the cloud-based VM.
IaaS allows database applications to access operating system features or other software applications installed on the VM. A virtual machine gives you access to hardware at a fraction of the cost of purchasing a machine outright. It is also an easy path to take if other elements of your application are already hosted in the Azure environment. In contrast to Platform as a Service, IaaS gives greater control to the SQL database engine configuration.
Depending on your service level, VMs offer up to 99.99% availability. Because the VM runs the same version of SQL server as an on-premise installation, you have all the same capabilities such as dynamic table creation with the select into statement that you don't get with PaaS SQL.
A VM will give you a private IP address within the Azure virtual network. And each SQL server instance can support up to 256 terabytes of storage. SQL Server VMs come in a wide range of hardware, OS and server configurations with related billing options.
Azure SQL Platform as a Service offering comes in three main types: Managed instance, single instance, and elastic pool. SQL Managed Instance is the closest PaaS offering to Infrastructure as a Service model. It supports most of the capabilities of an on-premise SQL Server instance allowing multiple databases to be used in unison while freeing you from operating system responsibilities.
Each managed instance is guaranteed access to its own resources, mitigating some of the multi-tenant environment concerns. Automatic backups, engine patching and recovery are built into the managed instance. Like a VM, an SQL managed instance has an IP address within the Azure virtual network. A managed instance supports up to eight terabytes of database storage. A single instance database is just that, a database that exists in isolation and has practically no access to the underlying database engine.
While multiple databases can share a virtual server. Each database has access to guaranteed compute, memory, and storage resources. Once you've created a single database with a virtual server you can create other databases on that server, but each database has its own allocation of compute and memory resources. I call the database server virtual as the databases that run under it cannot do things like cross database queries that you would expect in a traditional server database scenario. No access to the operating system and database engine translates into very little administration is required.
An elastic pool is a collection of single databases that do share CPU and memory resources. As each database has access to the pool's full compute resources, it is ideal for situations where databases experience erratic workloads at different times. Elastic pools can be a very cost-effective method for rationing resources across multiple databases and applications.
Platform as a service SQL offerings is most suited for modern cloud applications that require the latest stable SQL server features. Azure SQL databases are very quick to deploy. So they are ideal when time is a constraint. Because all the hardware infrastructure is managed in the background a very high availability level is guaranteed. Backups, patching and recovery are provided as standard features of the service.
The next level of Platform as a Service is serverless databases which we shall discuss later on.
Managed databases are delivered with a high degree of consistency as they are fully automated services from Microsoft Azure. Many of the functions that you would have to undertake manually at on-premise situation are done for you. This includes patching, updating, tuning, and configuring high availability. You don't have to test basic administration tasks like backups and disaster recovery as the services are automatically and expertly provided.
In addition to automated administration, you can also benefit from other features not present in SQL Server. Some of these features include intelligent advanced threat detection, automated vulnerability assessments, and alerts and compliance evaluation to ensure your setup is adhering to industry standards. Depending on your service tier you can pay for only what you use, while simultaneously being able to scale when demand dictates automatically.
Not only do you get a raft of additional features, but this extra functionality comes with very little administrative overhead. At the same time, you are guaranteed a very high level of service availability. There are two purchasing or billing models for Azure SQL Platform as a Service databases, the first or oldest is DTU or database transaction unit.
DTU is a single metric that combines CPU memory and IO performance. The DTU figure guarantees a minimum level of compute memory and IO resources. DTU simplifies database resource provisioning by giving the consumer only two levers: computing performance and maximum data storage to adjust. DTU pricing is only available with single instance databases and elastic pool. Virtual cores or vCore is the latest way to specify compute and storage capacity. It enables the consumer greater flexibility control and allows them to use their own SQL server license, should they have one to reduce costs.
Unlike the DTU model, vCore pricing is available for all three PaaS deployment options. Microsoft recommends the use of the vCore model as computing storage and IO resources can be independently scaled giving the customer greater control and flexibility over their database resources. vCore comes with the added benefit of using an existing SQL server license to reduce costs.
The DTU model is best for customers who are unaware of their resource requirements and want a simple way of deploying a pre-configured resource. Microsoft provides tools like the DTU calculator that help you to estimate how much compute and storage resource you will consume. It is possible to migrate from a DTU model to a vCore model. And once you are consuming over 300 DTUs this may be a cost-effective option.
There are three Azure SQL service tiers: General purpose, premium and business-critical, and hyper-scale. The general purpose or standard service tier is designed for generic workloads and has a read latency of around five milliseconds and a write latency of about 10 milliseconds.
As the name implies, the service tier is suitable for most workloads and is available for all deployment options. The business-critical or premium tier is for high-performance applications that require very low latency for reading and writing. As such, this service tier has a one-millisecond read latency and two milliseconds for writing and is also available for all deployment options.
The main point of difference for the hyperscale tier is very large database support. Although hyperscale architecture has added benefits in the area of high availability and disaster recovery, the hyperscale service tier is only available under the vCore purchasing model and is not compatible with SQL managed instances.
Serverless computing is a recent addition to the Azure SQL database stable. This pricing model is the closest to only paying for what you use. A database's compute resources are defined in terms of the minimum and maximum number of virtual cores to use. vCore usage is charged by the second and you will only be billed for the vCores being used at any one time as the database scales up and down according to the current workload.
When there is no activity for a specified period, the database can be configured to pause automatically. So you are only charged for storage and not computing when the Azure SQL service receives a request that will bring the database back online.
Let's compare serverless and provision database computing. In terms of usage, a serverless database is best for intermittent, unpredictable, or unknown use patterns but with a lower average use in total. Provision computing is best for regular, consistent, and sustained usage patterns. Serverless databases require less administrative input because they automatically scale as workloads change, whereas provisioned have to be scaled manually.
One downside to serverless databases is the slow response time when receiving a request while it is in a paused state. Provisioned compute is best for databases that cannot tolerate auto resuming from a paused state, translating into a lengthy response time.
Use an elastic pool when you want to use provisioned databases with erratic or unpredictable usage patterns. As long as the pool databases don't experience high workloads simultaneously each can benefit from the total pool resources available to them.
Billing granularity is another factor to consider in conjunction with usage patterns.
So how do we create and deploy an Azure SQL resource? Apart from manually creating a deployment through the Azure portal, we have several options at our disposal. Azure Resource Manager or ARM templates is the preferred method for deploying Azure resources. A template is a JSON file that specifies the resources to be deployed along with other resources that they may be dependent on. A template allows you to treat infrastructure as code which means it can be stored in source control and version, enabling you to control and track release changes.
DACPAC, which stands for data tier package, is a way to package and deploy database schema changes. You can create a DACPAC using the SQL database project in Visual Studio, or by selecting Extract data tier application from a databases context task menu within SQL Management Studio. This is not to be confused with BACPAC which has a similar format but can include data along with the schema definition.
DACPAC is focused on deploying schema changes to existing databases, while BACPAC is focused on the export and import of databases from one server to another. SQL scripts are the standby if all else fails option available to DBAs.
Scripts can create and modify a database and schema as well as import or export data. Not only can Powershell scripts configure aspects of Azure SQL, but you can manipulate and configure services like firewall rules, network settings, and other attributes and services external to the database.
The Azure Command Line Interface or CLI has very similar functionality to PowerShell and uses many of the same commands. When you are running your SQL server on a virtual machine you can use Azure Automation to monitor, manage, and deploy patches.
If you select Update management from the Azure Automation account you will see a console like this. You can see which systems or VMs are compliant or non-compliant. You can drill down into each virtual machine to see which patches have been applied along with multiple other metrics that contribute to compliance. You can use Azure automation to schedule patches deployment to a particular VM or groups of VMs.
In the case of Infrastructure as a Service or when running SQL Server on a VM, an administrator is responsible for applying OS patches and SQL server updates.
As we move into the Platform as a Service territory who becomes responsible for making sure the OS and server is up to date? Microsoft as the cloud provider is responsible for operating system patches in a PaaS environment. Critical updates are given the highest priority and are deployed immediately. OS patches and updates are applied without interrupting the Azure SQL service. The same can be said for patches, updates and enhancements applied to the Azure SQL service.
New features are applied to the Azure SQL platform without the need for an explicit upgrade process. Microsoft makes the following statements about Azure SQL updates. There is no risk for data or schema loss for either service updates or operating system updates. There is no need to back up your data before a scheduled service update. Failover capabilities are maintained during updates.
Course Introduction - Azure SQL Databases Overview - ARM Templates Deployment - DEMO: Deploying Azure SQL Databases - Business Continuity - 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.