Module 7
Planning to Deploy SQL Server on
Microsoft Azure
Module Overview
• SQL Server on Virtual Machines and Azure SQL
Database
• Azure Storage
• Azure SQL Server Authentication
• Deploying Databases in Azure SQL Database
Lesson 1: SQL Server on Virtual Machines and
Azure SQL Database
• SQL Server on Azure Virtual Machines
• Azure SQL Database
• Virtual Machine or Azure SQL Database?
• Azure Virtual Networks
• Azure Active Directory
• SQL Database Performance Tiers
• Database Transaction Units
• Demonstration: Provisioning an Azure Virtual
Machine
SQL Server on Azure Virtual Machines
• Infrastructure as a service model
• Azure provides compute and storage resources
• Customer administers Windows and SQL Server
• Licensed either through a prebuilt virtual
machine image or with an existing license
• Web, Standard, and Enterprise Editions for SQL
2008 R2 and later are available as prebuilt
images
• Linux images also available for SQL Server 2017
• Azure virtual machines have an availability SLA of
99.95 percent
• Resource groups
Azure SQL Database
• Platform as a service and Software as a service
model
• Hardware, software, and almost all administration
provided by Azure
• No license considerations or additional storage
costs
• Some concepts and Transact-SQL commands not
supported
• Limits on database size
• Azure SQL Database has an availability SLA of
99.95 percent
Virtual Machine or Azure SQL Database?
• Is this a new or existing application?
• Will databases of greater than 4 terabytes be
needed?
• Are IT resources available for support and
administration?
• Is full administrative control required?
• Will the application be a cloud/on-premises
hybrid?
Azure Virtual Networks
VNet Internet
VNet
VPN
NSG
On-Premises VNet
Azure Active Directory
• Use user accounts in Azure Active Directory to
access SQL Server
• Use the same accounts to access all other Azure
resources and applications
• Synchronize user accounts in Azure Active
Directory with on-premises Active Directory
SQL Database Performance Tiers
• Single database—each database has its own
service tier
• Elastic database pool—a group of databases
share a pool of resources, allocated dynamically
• Service tiers
• Basic—small databases that have low concurrency
• Standard—suitable for most concurrent databases
• Premium—highest levels of performance and
availability
• Switch tiers at any time
Database Transaction Units
• Single database performance measured in
database transaction units (DTUs)
• 1 DTU = 1 transaction per second
• Elastic database pool performance measured in
elastic database transaction units (eDTUs)
• 1 eDTU = 1 transaction per second
• eDTUs are only allocated to databases in the pool as
they are required
Demonstration: Provisioning an Azure Virtual
Machine
In this demonstration, you will see:
• How to provision an Azure virtual machine with
SQL Server
Lesson 2: Azure Storage
• Sizes of Azure Virtual Machines
• Virtual Disk Types
• Data Compression
• Instant File Initialization
Sizes of Azure Virtual Machines
• Azure virtual machines are defined by:
• CPU cores
• Memory
• Temporary hard disk
• Maximum number of data disks
• Maximum data throughput
• Maximum network interface cards/network bandwidth
• The Azure compute unit
• Virtual machine sizes are divided into ‘series’:
• D-Series—fast
• F-Series—best value
• G-Series—more memory
Virtual Disk Types
• Azure virtual machine disk types:
• Operating system disk
• Stores the operating system
• 1,023 GB SATA drive
• Labelled Drive C
• Temporary disk
• Labelled Drive D
• Used for temporary data
• No data is persisted in the event of failure
• Do not use to store data
• Data disks
• The number that you can add depends on the size of the
virtual machine
• They determine the size of disk, up to a maximum of 1,023 GB
Data Compression
• Azure SQL Database supports row and page
compression
• For rowstore tables and indexes with high I/O
workloads, enable compression
• Columnstore compression is always used for
columnstore indexes
• Further columnstore compression can be applied
using columnstore archive compression
• This reduces the space required, but also reduces
performance
• Use for data that is accessed infrequently, and where
you want to minimize storage space
Instant File Initialization
• Instant file initialization
• Faster initialization of disk space
• Does not overwrite disks with zeros before using
• Off by default
• Use with on-premises SQL Server or SQL Server
running in an Azure virtual machine
Lesson 3: Azure SQL Server Authentication
• Security Overview of Azure SQL Database
• Connection Security
• Authentication
• Authorization
• Auditing and Compliance
Security Overview of Azure SQL Database
• Azure SQL Database uses a layered security
model
• Azure SQL Database V12 now supports:
• Always Encrypted
• Transparent data encryption
• Row-level security
• Dynamic data masking
• Data encryption
• Making data unreadable to attackers
• Controlling access
• Controlling permissions to database objects
• Auditing and monitoring
Connection Security
• Connection security is enforced by using firewall
rules
• Firewall rules allow only certain IP addresses to
connect
• Firewall rules can be set at the server or database
level
• You can add or amend firewalls either through
the Azure portal or using Windows PowerShell
Authentication
• Azure SQL Database supports both SQL Server
Authentication and Windows Authentication
• Use Azure Active Directory for Windows
Authentication
Authorization
• Authorization is required to work with SQL Server
securables
• Securables are things such as tables, views, and
stored procedures.
• Authorization is granted to principals: logins or
database users
Auditing and Compliance
• No system is 100 percent secure, so auditing
forms an important part of data protection
• Enable Azure SQL Database auditing through the
Azure portal
• Event logs are stored in an Azure storage
account
• View logs by using Azure Storage Explorer
• View logs in the Azure portal
Lesson 4: Deploying Databases in Azure SQL
Database
• Demonstration: Provisioning a Database in Azure
SQL Database
• Demonstration: Connecting to a Database in
Azure SQL Database
Demonstration: Provisioning a Database in Azure
SQL Database
In this demonstration, you will see:
• How to provision a database in Azure SQL
Database by using Azure PowerShell
Demonstration: Connecting to a Database in
Azure SQL Database
In this demonstration, you will see:
• How to connect to a database in Azure SQL
Database
Lab: Plan and Deploy an Azure SQL Database
• Exercise 1: Plan an Azure SQL Database
• Exercise 2: Provision an Azure SQL Database
• Exercise 3: Connect to an Azure SQL Database
Logon Information
Virtual machine: 20765C-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa55w.rd
Estimated Time: 45 minutes
Lab Scenario
As a pilot project for Adventure Works Cycles, you
have been tasked with planning and deploying a
new database to Microsoft Azure.
Lab Review
Having completing this lab, you will now be able to:
• Plan an Azure SQL Database
• Provision an Azure SQL Database
• Connect to, and configure, an Azure SQL database
Module Review and Takeaways
• Real-world Issues and Scenarios
• Review Question(s)
