In the previous modules, you learned about how to audit your data estate, and the most recent SQL Server improvements. In this module, you will learn about the different options available for running SQL Server in Azure and all the considerations that are involved with running an architecture in the cloud, like Manageability, Availability, Performance, Security, and Deployment.
(Make sure you check out the Pre-Requisites page before you start. You'll need all of the items loaded there before you can proceed with the workshop.)
Using the processes, tools and procedures from the Auditing Module, the WideWorldImporters company has completed an initial audit of their data estate. They have settled on many architectural improvements in the latest version of SQL Server on-premises, and have decided to move to the latest version for many of their systems, using the Compatibility settings where required. They have also started a test installation of SQL Server running on Red Hat Linux, and developers are now using SQL Server in Containers.
WideWorldImporters is now turning its attention to workloads they can use in the Microsoft Azure platform. They are particularly interested in the security, availability and elastic scale available in a cloud service.
For consistency, they are continuing their evaluation using the same categories that they investigated for the on-premises installations of SQL Server:
- 4.1 - Deployment Options
- 4.2 - Manageability
- 4.3 - Availability
- 4.4 - Performance
- 4.5 - Security
- 4.6 - Development
- 4.7 - Analytics
Since Microsoft Azure and the Azure Data Platform are constantly updated, there are improvements that are not included in this Module. You can keep track of these changes in a few locations, such as the Azure Updates feed, the Azure SQL Database "What's New" page, and the Data Exposed video channel, as well as the "Year In Review" Posts on the Azure SQL Blog.
Wide World Importers does have a basic understanding of the Microsoft Azure Platform, and has deployed database offerings in the past. If you are new to these topics, consult the following references:
- Microsoft Certified: Azure Fundamentals - a hands-on free course on the basics of the Microsoft Azure platform
- Microsoft Certified: Azure Database Administrator Associate - a free hands-on course that guides you through the fundamentals of the Azure SQL architecture
With a firm understanding of the basics of Microsoft Azure and the Azure SQL Database environments, you can continue on with your modernization evaluation.
Microsoft Azure has three basic deployment options for a SQL Server database. All of these use the same basic RDBMS engine, with a high level of compatibility for your data operations. The primary decision point is how much control and responsibility you want for each type of deployment. The data platform in Microsoft Azure includes:
WideWorld Importers has decided to take their Data Estate audit of the applications they have and decide if it is a candidate for Microsoft Azure, and which of these workloads requires a higher level of control, and which are better suited to a more fully managed environment.
As you learned in the last Module, SQL Server can be installed directly on a Virtual Machine environment just as it is on bare metal hardware, with a few considerations. This provides the highest level of control and responsibility - you own and control everything from the operating system to the installation of SQL Server. You can choose various Microsoft Windows or Linux offerings for your installation of SQL Server.Microsoft Azure provides a huge array of Virtual Machine sizes, configurations, and licensing (or no licensing) options. You can find a sizing tool at this reference.
After you select a Microsoft Azure Virtual Machine that fits your needs, you can install SQL Server Instances on it like any other Virtual Machine environment. Using Azure Virtual Machines gives you all of the advantages of the Azure Platform, such as scaling storage quickly, rapid backup and restore operations, cloning systems, scripted and automated deployments, flexible networking into subnets and Network Security Groups, global regions for deployments, extensive logging, and the Microsoft Defender security environment, and many other advantages.
Once you've installed SQL Server on a Microsoft Azure Virtual Machine, you can register the installation with the Azure Resource Provider system which enhances the management, monitoring and other aspects of Microsoft Azure for your instance. You can learn more about that process at this reference.
While you can install SQL Server on a Virtual Machine, Microsoft Azure has multiple sizes, operating systems and more with SQL Server pre-installed, all with pay-as-you-go or license migration options. You can learn more about these deployments at this reference.
Once you have your image selected, you can find a checklist to learn how to implement optimal performance of your Virtual Machines at this reference, and you can find guidance for proper storage configurations for SQL Server on Azure Virtual Machiness at this reference.
For the next level of control, WideWorld Importers has decided some workloads require the broadest SQL Server Instance database engine compatibility with all the benefits of a fully managed and evergreen platform as a service. Azure SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for existing SQL Server customers.If you are evaluating SQL Server on Red Hat Linux (RHEL) Azure VMs, you can find an operations guide at this reference.
SQL Managed Instance allows you to "lift and shift" on-premises applications to the cloud with minimal application and database changes. Azure SQL Managed Instance preserves all PaaS capabilities (automatic patching and version updates, automated backups, high availability) that drastically reduce management overhead. It can also be used as a Disaster Recovery (DR) site, among many other interoperations with on-premises and in-cloud data systems. These advantages will be discussed in the next Module.
Several new improvements are announced for the latest release of SQL Managed Instance, including:
- Endpoint policies allow you to configure which Azure Storage accounts can be accessed from a SQL Managed Instance subnet. Grants an extra layer of protection against inadvertent or malicious data exfiltration
- Instance pools are a convenient and cost-efficient way to migrate smaller SQL Server instances to the cloud
- Managed Instance link gives you an online replication of SQL Server databases hosted anywhere to Azure SQL Managed Instance
- Maintenance window advance notifications for databases allows you to configure a non-default maintenance window, and receive advanced notifications of maintenance
- Migrate with Log Replay Service allows you to migrate databases from SQL Server to SQL Managed Instance by using Log Replay Service
- You can now use Microsoft.Build.Sql for SDK-style SQL projects in the SQL Database Projects extension in Azure Data Studio or VS Code. SDK-style SQL projects are especially advantageous for applications shipped through pipelines or built in cross-platform environments
- There is new support for cross-instance message exchange using Service Broker on Azure SQL Managed Instance
- A new SQL Database Projects extension allow you to develop databases for Azure SQL Database with Azure Data Studio and VS Code. A SQL project is a local representation of SQL objects that comprise the schema for a single database, such as tables, stored procedures, or functions
- SQL Insights is a comprehensive solution for monitoring any product in the Azure SQL family. SQL Insights uses dynamic management views to expose the data you need to monitor health, diagnose problems, and tune performance
- Transactional Replication improvements allow you to replicate the changes from your tables into other databases in SQL Managed Instance, SQL Database, or SQL Server. You can also update your tables when some rows are changed in other instances of SQL Managed Instance or SQL Server
- New threat detections notifies you of security threats detected to your database
You can learn more about Azure SQL Managed Instance at this reference.
WideWorld Importers has now reviewed the highest level of control and responsibility (Azure Virtual Machines), and then evaluated abstracting the platform, operating system, and many of the responsibilities for the SQL Server deployment (Azure SQL Managed Instance). Using the Data Estate Audit process from the first Module, they find they are investing more heavily in web-based applications, not only for customers, but for some internal applications as well.
They want the highest level of compatibility for the databases these applications use, but they do not want to stand up more SQL Server Instances, or further task the data professionals to create and manage databases. They do want automatic backups, elastic scale, automatic tuning (wherever possible) and geographic flexibility. They would like to keep the option of moving any of these databases to their other Instances or deployments.
Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical.With Azure SQL Database, you can create a highly available and high-performance data storage layer for the applications and solutions in Azure. SQL Database works with a variety of modern cloud applications because it enables you to process both relational data and non-relational structures, such as graphs, JSON, spatial, and XML.
Azure SQL Database is based on the latest stable version of the Microsoft SQL Server database engine. You can use advanced query processing features, such as high-performance in-memory technologies and intelligent query processing. In fact, the newest capabilities of SQL Server are released first to Azure SQL Database, and then to SQL Server itself. You get the newest SQL Server capabilities with no overhead for patching or upgrading, tested across millions of databases.
SQL Database enables you to easily define and scale performance within two different purchasing models: a vCore-based purchasing model and a DTU-based purchasing model. SQL Database is a fully managed service that has built-in high availability, backups, and other common maintenance operations. Microsoft handles all patching and updating of the SQL and operating system code. You don't have to manage the underlying infrastructure.
In addition, WideWorld Importers has learned that their developers can each have a local running instance of the Azure SQL Database offering in a local emulator. This allows each developer to follow the newly established DevOps process they are setting up in the IT department.
The Azure SQL Platform is constantly evolving. You can find a list of the latest improvements at this reference. Note that the information that follows is accurate as of 10/23/2022.
As WideWorld Importers works through the process of evaluating workloads for Azure SQL Database, they are faced with various choices and decisions.
Decision 1: The purchasing model for optimal cost management
There are two options for the cost-model in Azure SQL Databases: Virtual core (vCore)-based deployments (recommended) or Database transaction unit (DTU)-based pricing.
The vCore-based model is recommended because it allows you to independently choose compute and storage resources, while the DTU-based model is a bundled measure of compute, storage and I/O resources, which means you have less control over paying only for what you need. This model also allows you to use the Azure Hybrid Benefit for SQL Server for additional cost savings. In the vCore model, your costs include:
- Compute resources (the service tier + the number of vCores and the amount of memory + the generation of hardware)
- The type and amount of data and log storage
- Backup storage (such as read-access, geo-redundant storage (RA-GRS)).
For the purposes of this workshop, we'll focus on the vCore purchasing model (recommended), but you can compare vCores and DTUs at this reference.
Decision 2: The service tier for performance and availability There are three tiers available in the vCore model for Azure SQL Database:
- General purpose: Suitable for most business workloads. Offers budget-oriented, balanced, and scalable compute and storage options
- Business critical - Business applications with low-latency response requirements. Offers highest resilience to failures by using several isolated replicas. This is the only tier that can leverage in-memory OLTP to improve performance
- Hyperscale: For business workloads with highly scalable storage and read-scale requirements
You can find a discussion of the service level agreements that set an expectation for uptime and performance at this reference. This resource will help you make an informed decision about which tier to use. A video discussion is available at this reference.
For a deeper explanation between the three tiers (including scenarios), you can also see this reference on the service-tier characteristics.
Decision 3: The provisioning model for best use patterns If you choose the General Purpose within Single databases and the vCore-based model, you have an additional decision to make regarding the compute costs:
- Provisioned compute is designed for regular usage patterns with higher average compute utilization over time, or multiple databases using elastic pools
- Serverless compute is designed for intermittent, unpredictable usage with lower average compute utilization over time. Serverless has auto-pause and resume capabilities (with a time delay you set), meaning when your database is paused, costs involve storage
For a deeper explanation between the two compute options (including scenarios), you can find a detailed comparison in the documentation at this reference. If you're looking for compute cost saving opportunities, you can prepay for compute resources with Azure SQL Database reserved capacity. You can learn more at this reference.
Decision 4: Choose the hardware generation for proper sizing The vCore model lets you choose the generation of hardware you want to run the offering on:
- Gen4: Up to 24 logical CPUs based on Intel E5-2673 v3 (Haswell) 2.4-GHz processors, vCore = 1 PP (physical core), 7 GB per core, attached SSD
- Gen5: Up to 80 logical CPUs based on Intel E5-2673 v4 (Broadwell) 2.3-GHz processors, vCore = 1 LP (hyper-thread), 5.1 GB per core, fast eNVM SSD
The Gen4 hardware offers substantially more memory per vCore. However, Gen5 hardware allows you to scale up compute resources much higher.
WideWorld Importers does have one mobile application that they anticipate to have extremely high demand, throughput, and size requirements.If you choose General Purpose within Single databases and want to use the serverless compute tier, Gen5 hardware is the only option
The Hyperscale service tier in Azure SQL Database is the newest service tier in the vCore-based purchasing model. This service tier is a highly scalable storage and compute performance tier that uses the Azure architecture to scale out the storage and compute resources for an Azure SQL Database substantially beyond the limits available for the General Purpose and Business Critical service tiers.
You can learn more about Azure SQL Hyperscale at this reference.
There are many options and decisions for moving a workload to the Azure SQL offerings. The Azure SQL environment provides flexibility so you get exactly what you need. You can find a summary of the service tier options with some additional considerations for cost at this reference.
Activity: Install the Azure SQL Database Emulator
Steps
- Open this reference
- If time and resources permit, install the components as listed in the steps at that reference
- If you do not have the resources on your system, or you do not have the time to follow those steps, review with the instructor and watch their demonstration of the process
You can use SQL Server Management Studio, SQLCMD, and the other on-premises tools you reviewed in the last Module for on-premises tooling. In this Module, you will focus on the Azure Portal, the Azure Data Studio tool, and the Azure Command-line Interface (az commands in a cross-platform tool).
The Microsoft Azure Portal The Microsoft Azure Portal is used to deploy, manage, monitor, or delete resources in your subscription. It has sections, or "blades" for each of these operations and can be accessed with all major web browsers as well as cell phones. You can also set up various "Dashboards" to show your resources and other information.
You can learn more about the Azure Portal at this reference.
Azure Data Studio Azure Data Studio is a cross-platform database tool for data professionals using on-premises and cloud data platforms on Windows, macOS, and Linux. Azure Data Studio offers a modern editor experience with IntelliSense, code snippets, source control integration, and an integrated terminal. It's engineered with the data platform user in mind, with built-in charting of query result sets and customizable dashboards.
You can learn more about Azure Data Studio here.
Azure Data Studio is a highly extensible tool that helps developers to manage and develop against multiple data processing and storage engines. Its versatility through the use of application extensions allows it to be as useful as the developer can make it. Multiple extensions exist for helping developers to migrate to Azure SQL, develop "clean" looking code, monitor and manage Azure Synapse, work on PostgreSQL instances, or even develop using Jupyter Notebooks
Jupyter Notebooks A Jupyter Notebook is a web-page-based interface consisting of Cells that can contain text (using the Markdown specification) or code. The code depends on the Kernel that has been installed for the Notebook. Traditionally, Python and R Kernels are installed by default. Notebook Servers run .ipynb files (the Notebooks).
Notebooks are JSON files that contain areas called Cells, which have text or code in them. When you double-click a Notebook, the Notebook server renders and can display text or run code, such as R or Python, using a Kernel. Cells can hold text (such as Markdown, HTML, or LaTeX) which you can mix together, or Code. Double-click a Cell in a Notebook to edit it, and then click the "Run" button to render what you typed. Code runs and displays an output below the cell. You can toggle the result for code to show or hide it.
Markdown is a simplified markup language for text. Use it for general text and simple graphics. You can read more about Markdown here, and there's a great cheat-sheet on Markdown here.
You'll use Notebooks within Azure Data Studio to work with SQL Server on-premises, in-cloud, or in the new Development Environment you set up in the last Activity. You can learn more about Jupyter Notebooks here.
Azure Command-Line Interface You can work with all of your Azure properties using a command-line tool. The Azure command-line interface (Azure CLI) is a set of commands used to create and manage Azure resources. The Azure CLI is available across Azure services and is designed to get you working quickly with Azure, with an emphasis on automation. It runs on Windows, Linux, and macOS, and there is also a CLI interface in the Azure Portal and it's available as an extension in the Azure Data Studio tool.
You can learn more about the Azure Command-Line Iterface at this reference.
Activity: Review the Azure Data Studio Overview
Steps
- Install the Azure Data Studio if you have not already done so using this reference
- Open this reference to review a video walkthrough of the Azure Data Studio tool
WideWorld Importers is also interested in the on-premises to Azure and Azure to on-premises links, but will review that as a separate task, which you will cover in the next Module
Azure Availability Zones are physically separate locations within each Azure region that are tolerant to local failures.
You can learn more about Azure Availability Zones at this reference.
In addition, the Azure Data Platform offers other advantages:
-
Azure SQL Database High availability of Azure SQL Database guarantees your databases are up and running 99.99% of the time, no need to worry about maintenance/downtimes.
-
Azure SQL Managed Instance Auto-Failover Groups feature allows you to manage the replication and failover of all user databases in a managed instance to another Azure region.
-
Automated backups are created and use Azure read-access geo-redundant storage (RA-GRS) to provide geo-redundancy.
-
Long term backup retention enables you to store specific full databases for up to 10 years.
-
Geo-replication by creating readable replicas of your database in the same or different Azure Region.
Activity: Review Azure SQL Availability
In this Activity you will review a video which walks you through various considerations when evaluating high availability and disaster recovery needs and features in Azure SQL.
Steps
- Navigate to this reference and watch the resource you find there. Make notes in your class document about which HADR features you need to investigate further.
- Explain estimated and actual query plans. The topic of actual versus estimated execution plans can be confusing. The difference is that the actual plan includes runtime statistics that aren't captured in the estimated plan. The operators used, and order of execution will be the same as the estimated plan in nearly all cases.
- The Query Data Store The SQL Server Query Store is a per-database feature that automatically captures a history of queries, plans, and runtime statistics to simplify performance troubleshooting and query tuning. It also provides insight into database usage patterns and resource consumption. This is on by default in all Azure SQL Databases.
- Wait Statistics When SQL Server executes any process it must wait for resources. As that occurs Wait Statistics are accumulated by the SQL OS. Wait Statistics will tell you what Azure SQL is waiting on. If it is a hardware wait stat, then you may need to consider increasing your service tier.
Other considerations are available in Azure that are not the same as an on-premises installation of SQL Server:
- Auto Scaling Azure Serverless database or regular scaling Azure SQL Database, Managed Instance, or Synapse may be key to resolving hardware performance.
- Tune and maintain indexes. The most common (and most effective) method for tuning T-SQL queries is to evaluate and adjust your indexing strategy.
- Automatic tuning analyzes your workload and provides you the recommendations that can optimize performance of your applications by adding indexes, removing unused indexes, and automatically fixing the query plan issues.
- Built-in intelligence automatically identifies the potential issues in your workload and provides you the recommendations that can help you to fix the problems.
Activity: Understand more about the Automatic Tuning processes in Azure SQL
Automatic tuning in Azure SQL is a great feature that analyzes your workload and gives recommendations on configuration changes you can make to improve performance. In this video, you are given an overview of the feature.
Steps
- Open this reference and review the information you see there. Make notes in your class notebook for items you wish to review later.
- Open this reference and review the information you see there. Make notes in your class notebook for items you wish to review later.
Security is often discussed as a series of "layer", as illustrated in this diagram:
Each of the following list items address the areas of concern above:
-
Network security - To help protect customer data, firewalls prevent network access to the server until access is explicitly granted based on IP address or Azure Virtual network traffic origin.
-
IP firewall rules - IP firewall rules grant access to databases based on the originating IP address of each request. For more information, see Overview of Azure SQL Database and Azure Synapse Analytics firewall rules.
-
Virtual network firewall rules - Virtual network service endpoints extend your virtual network connectivity over the Azure backbone and enable Azure SQL Database to identify the virtual network subnet that traffic originates from. To allow traffic to reach Azure SQL Database, use the SQL service tags to allow outbound traffic through Network Security Groups.
-
Access management - Managing databases and servers within Azure is controlled by your portal user account's role assignments.
-
Authentication - Authentication is the process of proving the user is who they claim to be. Azure SQL Database and SQL Managed Instance support SQL authentication and Azure AD authentication. SQL Managed instance additionally supports Windows Authentication for Azure AD principals.
-
Authorization - Authorization refers to controlling access on resources and commands within a database. This is done by assigning permissions to a user within a database in Azure SQL Database or Azure SQL Managed Instance. Permissions are ideally managed by adding user accounts to database roles and assigning database-level permissions to those roles. Alternatively an individual user can also be granted certain object-level permissions.
-
Row-level security - Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (for example, group membership or execution context). Row-Level Security can also be used to implement custom Label-based security concepts.
-
SQL auditing in Azure Monitor logs and Event Hubs - SQL Database and SQL Managed Instance auditing tracks database activities and helps maintain compliance with security standards by recording database events to an audit log in a customer-owned Azure storage account. Auditing allows users to monitor ongoing database activities, as well as analyze and investigate historical activity to identify potential threats or suspected abuse and security violations.
-
Advanced Threat Protection - Advanced Threat Protection is analyzing your logs to detect unusual behavior and potentially harmful attempts to access or exploit databases. Alerts are created for suspicious activities such as SQL injection, potential data infiltration, and brute force attacks or for anomalies in access patterns to catch privilege escalations and breached credentials use. Alerts are viewed from the Microsoft Defender for Cloud, where the details of the suspicious activities are provided and recommendations for further investigation given along with actions to mitigate the threat. Advanced Threat Protection can be enabled per server for an additional fee.
-
Transport Layer Security - SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections. This ensures all data is encrypted "in transit" between the client and server irrespective of the setting of Encrypt or TrustServerCertificate in the connection string.
-
Transparent Data Encryption (Encryption-at-rest) - SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections. This ensures all data is encrypted "in transit" between the client and server irrespective of the setting of Encrypt or TrustServerCertificate in the connection string.
-
Key management with Azure Key Vault - Bring Your Own Key (BYOK) support for Transparent Data Encryption (TDE) allows customers to take ownership of key management and rotation using Azure Key Vault, Azure's cloud-based external key management system.
-
Always Encrypted (Encryption-in-use) - Always Encrypted is a feature designed to protect sensitive data stored in specific database columns from access (for example, credit card numbers, national identification numbers, or data on a need to know basis). This includes database administrators or other privileged users who are authorized to access the database to perform management tasks, but have no business need to access the particular data in the encrypted columns.
-
Dynamic data masking - Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking automatically discovers potentially sensitive data in Azure SQL Database and SQL Managed Instance and provides actionable recommendations to mask these fields, with minimal impact to the application layer.
-
Data discovery and classification - Data discovery and classification (currently in preview) provides basic capabilities built into Azure SQL Database and SQL Managed Instance for discovering, classifying and labeling the sensitive data in your databases. Discovering and classifying your utmost sensitive data (business/financial, healthcare, personal data, etc.) can play a pivotal role in your organizational Information protection stature
-
Vulnerability assessment - Vulnerability assessment is an easy to configure service that can discover, track, and help remediate potential database vulnerabilities with the goal to proactively improve overall database security. Vulnerability assessment (VA) is part of the Microsoft Defender for SQL offering, which is a unified package for advanced SQL security capabilities.
- Virtual Network Service Endpoints - Extend your virtual network over the Azure networking backbone and identify, and block if need be, traffic from a specific azure virtual network subnet that the traffic is originating from.
Activity: Manage Azure Security with Microsoft Defender for Cloud
Steps
- Open this reference and complete Unit 8 of 10: Secure your data with Azure SQL.
- Follow the steps in the section marked Exercise - Manage security and Microsoft Defender for Cloud.
- Proceed to Unit 9: Learn about Data classification, dynamic data masking, and SQL Audit in this MS Learn Module.
The Developer experience for a cloud platform, beyond tools and languages, is a very involved topic. You can find a complete reference for development using Azure SQL Database and the Azure SQL Managed Instance at this reference.
You can also find a series of sample applications for the Azure SQL deployment options at this reference. Many developers use example code as a mechanism for learning.
Activity: Review an end-to-end application using Azure SQL Database
Steps
- Open this reference and review the information you see there. Bookmark for later review.
You can find a deeper overview of Azure Synapse at this reference.
For Further Study
- You can find a complete hands-on, beginner course on Azure SQL for free at this reference
- You can find out how to develop Apps with Azure SQL Database for free at this reference
- You can watch a short video on some of the new improvements for Azure Data Studio at this reference
Next, continue on to Module 05 - Extending SQL Server to the Microsoft Azure Platform