There are multiple pathways to modernization Azure SQL

There are multiple pathways to modernization Azure SQL Database Intelligent, fully managed relational cloud database service App scenarios Benefits SQL Server in a VM SQL Server installed and hosted in the cloud SQL Server 2017 Physical database with best-in-class performance and flexibility Apps needing elastic scale and/or reduced overhead Apps requiring full OS and DB control or SSRS, SSAS, and/or SSIS Apps needing to remain on-premises for operating model or compliance SQL Server compatibility Full control over administration No need for additional IT resources for support and maintenance Full control on the OS and/or SQL Server level Leverages existing infrastructure investments Flexible scale and lower dedicated costs Let’s discuss how to modernize with SQL Server on Azure VMs

1 Reduced capex/pay-asyou-go pricing 2 Fast deployment 3 Reduced configuration 4 Lift and Shift Legacy Application

LICENSE FLEXIBILITY AND MOBILITY Maximize current on-premises licenses with Azure Hybrid Benefit (AHB) SQL Server on Azure vs. SQL Server on AWS • Move licenses in dynamic environments while ensuring compliance with Software Assurance • Get one core of SQL Server in Azure VMs for every one license core you own on-premises $1. 045/hr 43% savings vs. AWS • 180 -day grace period to use licenses both on- premises and in the cloud to facilitate migration • Combine AHB for SQL Server with AHB for Windows $0. 598/hr Server to save up to 85% $0. 598/hr • Move your SQL Server licenses to Iaa. S or Paa. S on Azure, unlike AWS RDS Standard Edition on AWS EC 2 (SQL License Mobility) Standard Edition on Azure (AHB pricing) Enterprise Edition on AWS EC 2 (SQL License Mobility) Enterprise Edition on Azure (AHB pricing) Note: Savings vs. AWS based on 8 -Core D 13 v 2 Azure VM in East US 2 region at Cent. OS/Ubuntu Linux rate (combining Azure Hybrid Benefit for SQL Server with Azure Hybrid Benefit for Windows Server); comparable AWS VM is r 3. 2 xlarge in US East (N. Virginia) at Windows rate (with SQL Server License Mobility). Savings analysis excludes Software Assurance costs for SQL Server and Windows Server, which may vary based on EA or any applicable volume licensing agreement. Actual savings may vary based on region, instance size, and compute type. Publicly-available prices as of June 5, 2018, subject to change.

SQL Server on VM You have full control over the SQL Server engine. Managed instance in SQL Database High compatibility with SQL Server on-premises. Single database / elastic pool in SQL Database The most commonly used SQL Server features are available. Single VM: 99. 95%, AS: 99. 95%, AZ 99. 99% availability. Full parity with the matching version of on-premises Built-in backups, patching, recovery. SQL Server. Built-in backups, patching, recovery. Fixed, well-known database engine version. Latest stable Database Engine version. Easy migration from SQL Server on-premises. Easy migration from SQL Server. Ability to assign necessary resources (CPU/storage) to individual databases. Private IP address within Azure VNet. Built-in advanced intelligence and security. You have ability to deploy application or services on Built-in advanced intelligence and security. the host where SQL Server is placed. Online change of resources (CPU/storage). You need to manage your backups and patches. There is still some minimal number of SQL Server features that are not available. Migration from SQL Server might be hard. You need to implement your own High-Availability solution. No guaranteed exact maintenance time (but nearly transparent). Some SQL Server features are not available. There is a downtime while changing the resources(CPU/storage) Compatibility with the SQL Server version can be achieved only using database compatibility levels. No guaranteed exact maintenance time (but nearly transparent). Compatibility with the SQL Server version can be achieved only using database compatibility levels. Private IP address cannot be assigned (you can limit the access using firewall rules).

SQL Enterprise VM (Iaa. S) with AHB for Windows Server and SQL Server ˜ 54% ˜ 85% SQL and Windows SA Azure Hybrid benefit for Windows and SQL Including software assurance costs

SQL Server goes further in Azure than other clouds Azure Hosting, AWS & GCP What are the advantages of the hybrid benefit over license mobility when adopting Iaa. S? Use the SQL cores on premise and in Azure simultaneously for up to 180 days while migrating No need to complete and submit license verification forms ! AND the hybrid benefit for Windows and SQL can be used together for Iaa. S (Paa. S abstracts the OS)

DSv 2 have 25% more storage bandwidth DS v 2 , DS & FS Series VM • • Recommended for SQL Prod Work Load Local SSD storage Premium Storage Portal Optimizes VM for SQL Workload GS Series , Ls-Series VM • Local SSD Storage • Premium Storage • Intel® Xeon® processor E 5 v 3 family. GS 1 GS 2 GS 3 GS 4 GS 5 VM Size CPU cores Memory DS 1_v 2 DS 2_v 2 DS 3_v 2 DS 4_v 2 DS 5_v 2 DS 11_v 2 DS 12_v 2 DS 13_v 2 DS 14_v 2 DS 15_v 2 1 2 4 8 16 20 3. 5 GB 7 GB 14 GB 28 GB 56 GB 112 GB 140 GB 2 4 8 16 32 28 GB 56 GB 112 GB 224 GB 448 GB Max Cached Uncached IOPs/ MBPs IOPS/MBPs 4, 000/32 3, 200/48 8, 000/64 6, 400/96 16, 000/128 12, 800/192 32, 000/256 25, 600/384 64, 000/512 51, 200/768 80, 000/640 64, 000/960 10, 000/100 20, 000/200 40, 000/400 80, 000/800 160, 000/1, 600 5000/125 10, 000/250 20, 000/500 40, 000/1, 000 80, 000/2, 000

TOOLS TO MOVE TO THE CLOUD Database Migration Assistant • Detect compatibility issues that can impact migration and receive recommended performance and reliability improvements SQL Server on Azure VMs • Migrate schema and data as well as uncontained objects from your source server Hybrid Availability Groups • Run availability replicas both in Azure VMs and on-premises for Database Migration Assistant Azure Site Recovery cross-site disaster recovery • Configure the failover cluster to span both networks (a multi- subnet failover cluster), and access with a VPN connection between Azure and the on-premises network Azure Site Recovery • Create a replica server, complete with installed and configured third-party tools • Keep on-premises applications available during outages with automatic recovery to Azure On-premises

An Azure Availability Set distributes VMs in different failure domains (racks) and upgrade domains VMs are not impacted at the same time by • Rack/Host failures • Azure Host upgrades Managed disks • Distributes disks of different VMs to different storage stamps

Availability Zones Unique physical locations within an Azure region Each zone is made up of one or more datacenters equipped with independent power, cooling, and networking. Minimum of three separate zones in all enabled regions With Availability Zones, Azure offers industry best 99. 99% VM uptime SLA

Solid-State Drives (SSDs) Uncached premium storage disk Cached Premium storage Disk Local SSD Disk • 5000 IOPs or 200 MB/s p/disk • Avg Less than 5 ms Support local read cache (SSD) • Avg 1 m latency • Frees VM bandwidth to Azure storage (for Log) Server SSD

• Throttling indicates you are going beyond your purchased limit • VM limit o IOPS o Throughout MB • Disk limit o IOPS o Throughout MB • Remedy • Optimize load/SQL Server • Go to higher size VM • Create storage pool with multiple disks Uncached premium storage disk Cached Premium storage Disk Local SSD Disk Server SSD

To connect from onpremises: • Configure VPN tunnel • Or create a VM endpoint for SQL and ACL it (source subnet/IP) Scope connections to VNET Don’t disable VM firewall Open SQL port in VM firewall to allow remote connections Use Windows Authentication Ensure the Critical Patches are installed Enable SSL

Configure prerequisites for SQL Server Always On availability groups on Azure Virtual Machines Create Always On Availability Group to improve availability and disaster recovery

https: //docs. microsoft. com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowsportal-sql-availability-group-tutorial

https: //azure. microsoft. com/en-us/blog/automate-always-on-availability-group-deployments-with-sql-virtual-machineresource-provider/

https: //azure. microsoft. com/en-us/blog/simplify-always-on-availability-group-deployments-on-azure-vm-with-sql-vm-cli

https: //docs. microsoft. com/en-us/azure/architecture/reference-architectures/n-tier-sql-server

https: //docs. microsoft. com/en-us/azure/architecture/reference-architectures/n-tier-sql-server

Store Database backups in Geo Replicated Readable Storage • Restore backups in a remote region (~30 min) Availability Group Options • Configure Azure as remote Region for On -premise • Configure On-Premise as DR for Azure • Replica in Azure Remote Region • Failover to Remote in (~30 s) • Offload remote reads

• • • Automated Backup Automated Patching Azure Key Vault Integration Change SQL License Setup SQL Connectivity • • Dependent on SQL Iaa. S Extension Available with SQL images only

Set SQL connectivity – private/public Set Static port • Firewall and NSG are updated automatically • SQL Authentication – Enable /Disable • •

• • Predictable solution for patching (Windows & SQL) Simple: just specify a time window Uses SQL Agent Extension and Windows Update Portal and Powershell

• • • For all DBs in the SQL instance Simple: just specify a retention period Depends on the log growth Supports Compression and Encryption Portal and Powershell

REST API for SQL VM Resource Provider https: //azure. microsoft. com/en-us/blog/sql-server-on-azure-virtual-machine-resource-provider/

https: //azure. microsoft. com/en-us/blog/sql-server-on-azure-virtual-machine-resourceprovider/

Physical Security Infrastructure Security Many certifications! SQL Security Keys can be stored in Azure Key Vault • Data centers monitored all the time • Microsoft Ops and Support personnel don’t have access to customer storage • Virtual Networks – Deployments are isolated in their own private networks • Storage – Encrypted Storage and authenticated via strong keys • ISO 27001/27002, SOC 1/SSAE 16/ISAE 3402 and SOC 2, • Cloud Security Alliance CCM, FISMA, HIPAA, EU Model Clauses, FIPS 140 -2, … • Auto Patching • Encryption of Databases and Backups • Encryption of connections • Authentication: Windows / SQL • Row-level Security & Always Encrypted (SQL Server 2016+)

Single-VM SLA: 99. 9% (<43 min downtime per month) Witness Multi-VM SLA: Availability Sets: 99. 95% (<22 min downtime per month) Multi-VM SLA: Availability Zones : 99. 99% (<5 min downtime per month) • Includes o Planned downtime due to host OS patching o Unplanned downtime due to physical failures • SLA is guaranteed by money back. For example, for Availability Zones Uptime Credit < 99. 99% 10% < 99% 25% < 95% 100% P S S P Doesn’t include servicing of guest OS or software inside (e. g. SQL) SQL Server Multi-VM deployments need AG or FCI • If VM becomes unavailable, fail over to another (~15 s) • Detects SQL instance failures (e. g. service down or hung) VM VM

https: //blogs. msdn. microsoft. com/sqlserverstorageengine/2018/09/25/storage-configuration-guidelines-for-sql-server-on-azure-vm/

Performance #2 - Disks Storage Single disk max value Preview Standard HDD Standard SSD Premium SSD Ultra SSD Low-cost storage Consistent performance High performance Sub-millisecond latency 32 Ti. B* 64 Ti. B 2, 000 20, 000 160, 000 500 MBps 750 MBps 2, 000 MBps SQL VM Storage - Premium SSD or Ultra SSD https: //azure. microsoft. com/en-us/blog/mission-critical-performance-with-ultra-ssd-for-sql-server-on-azure-vm/

High Availability WSFC Provision a DC replica (separate Availability Set) Parameter Fast Failover (Default) Same. Subnet. Delay 1 second 5 heartbeats (Get-cluster). Same. Subnet. Threshol= 20 Same. Subnet. Threshold /HA on Iaa. S Relaxing Windows Cluster failure detection for transient network failure Relaxed Maximum 1 second 20 heartbeats 2 seconds 120 heartbeats For AG -Configure Cluster to Ignore Storage For FCI – Create S 2 D

SQL Server 2017+ Red Hat SUSE Ubuntu Docker

§ What is SQL Server on Azure Virtual Machines? (Windows) § Provisioning a SQL Server Virtual Machine on Azure § How to create a SQL Server virtual machine in Azure using the existing on-premises SQL Server virtual machine § Performance Best Practices for SQL Server in Azure Virtual Machines § High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines § Tutorial: Always. On Availability Groups in Azure (GUI) § Using Azure SQL VM CLI to configure Always. ON § SQL on Azure VM - FAQ

Backup on-premises/ Hybrid workload Azure Backup Server/ DPM MARS Agent File/Folders/Volumes/System State Backup Cloud workloads More Iaa. S & Paa. S workloads…

Backup Options for SQL Server in Azure VMs Automated Azure Backup Manual Backup for SQL Server backup Manage multiple servers in one dashboard Point-in-time restore 15 -minute (RPO) Long-term backup retention policy (months, years) Built-in support for SQL Server Always On Consolidated email alerts for failures Restore databases with SSMS or Transact -SQL scripts © Microsoft Corporation http: //aka. ms/sqlbackupoptions Azure