Implement SQL Server on an Azure VM

This presentation is for those of you who are interested in moving your on-prem SQL Server databases and servers to Azure virtual machines (VM’s) in the cloud so you can take advantage of all the benefits of being in the cloud. This is commonly referred to as a “lift and shift” as part of an Infrastructure-as-a-service (IaaS) solution. I will discuss the various Azure VM sizes and options, migration strategies, storage options, high availability (HA) and disaster recovery (DR) solutions, and best practices.

1. James Serra Implement SQL Server on an Azure VM

2. About Me Business Intelligence Consultant, in IT for 30 years Microsoft, Big Data Evangelist Worked as desktop/web/database developer, DBA, BI and DW architect and developer, MDM architect, PDW/APS developer Been perm, contractor, consultant, business owner Presenter at PASS Business Analytics Conference and PASS Summit MCSE: Data Platform and Business Intelligence MS: Architecting Microsoft Azure Solutions Blog at JamesSerra.com Former SQL Server MVP Author of book “Reporting with Microsoft SQL Server 2012”

3. Agenda Azure VMs Migrating data Scaling VMs SQL Server VM features VM storage HA/DR architectures Best practices

4. Who manages what? I nfrastructure a s a S ervice Storage Servers Networking O/S Middleware Virtualization Data Applications Runtime Managed by Microsoft You scale, make resilient & manage P latform a s a S ervice Scale, Resilience and management by Microsoft You manage Storage Servers Networking O/S Middleware Virtualization Applications Runtime Data On Premises Physical / Virtual You scale, make resilient and manage Storage Servers Networking O/S Middleware Virtualization Data Applications Runtime S oftware a s a S ervice Storage Servers Networking O/S Middleware Virtualization Applications Runtime Data Scale, Resilience and management by Microsoft Windows Azure Virtual Machines Windows Azure Cloud Services

5. Virtual Machine components Compute Storage Networking Flexible, Scalable CPU and memory Standard and Premium Storage tiers Internal/External Networking

6. Azure VM VM hosted on Microsoft Azure Infrastructure (“IaaS”) From Microsoft images (gallery) or your own images (custom) SQL 2008R2 / 2012 / 2014 / 2016 RC0 Web / Standard / Enterprise Images refreshed with latest version, SP, CU Fast provisioning (~10 minutes). Provision groups of servers with resource templates Accessible via RDP and Powershell Full compatability with SQL Server “Box” software Pay per use Per minute (only when running) Cost depends on size and licensing EA customers can use existing SQL licenses (BYOL) Network: only outgoing (not incoming) Storage: only used (not allocated) Elasticity 1 core / 2 GB mem / 1 TB   32 cores / 512 GB mem / 32 TB

7. Windows Azure virtual machine tiers Basic Standard A0 – A4 1 – 8 CPU cores 768 MB – 14 GB RAM Max 16 datadisks w/300 IOPS per disk For dev/test workloads or applications that don’t require load-balancing, auto-scaling, or memory-intensive VM’s. G series 2 – 32 CPU cores 28 GB – 448 GB RAM Up to 64 datadisks with 500 IOPS/disk GS Series 2 – 32 CPU cores 28 GB – 448 GB RAM Up to 64 datadisks with 5000 – 80000 IOPS/disk https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-size-specs/ A0 – A11 1 – 16 CPU cores 768 MB – 112 GB RAM Max 16 datadisks with 500 IOPS/disk D1 – D14 & D1_v2 – D14_v2 1 – 16 CPU cores 768 MB – 112 GB RAM Max 16 datadisks with 500 IOPS/disk DS1 – DS14 Up to 50,000 IOPS 32 – 512 MB second

8. Compare compute performance ACU = Azure Compute Unit

9. Microsoft Azure VMs - Performance Different VM Options A-Series: Slowest CPU, least memory (A8-A11 compute intensive) D-Series: Faster CPU, more memory. Non-persistent SSD drive (good for TempDB ) Dv2-Series : 35% faster CPU than D-Series DS-Series: Same CPU and memory as D-Series. Support Premium Storage (good for Data, Log, and TempDB !!) G-Series: Fastest CPU, most memory. GS-Series: Fastest CPU, most memory. Support Premium Storage G5: 32 cores, 448 GB mem, 6 TB SSD, 64 1TB disks - Biggest VM in the market Different Storage Options Standard Storage: Low throughput (max 500 IOPs p/disk), High latency ( avg 40ms), pay for used space Premium Storage: High throughput (max 5000 IOPs p/disk), Low latency ( avg 4ms), pay for allocated space Azure calculator: https://azure.microsoft.com/en-us/pricing/calculator/

10. SQL Server in Azure VMs – Performance Premium Storage High throughput (max 5000 IOPs p/disk) Low latency ( avg 4ms) SSD disks in the backend Uses local SSD disk as read cache Avg 1ms read latency Cache hits not part of max IOPs/bandwidth VM Size CPU cores Memory Cache size Max. IOPS Max. Storage Bandwidth DS1 1 3.5 GB 43 GB 3,200 32 MB/s DS2 2 7 GB 86 GB 6,400 64 MB/s DS3 4 14 GB 172 GB 12,800 128 MB/s DS4 8 28 GB 344 GB 25,600 256 MB/s DS11 2 14 GB 72 GB 6,400 64 MB/s DS12 4 28 GB 144 GB 12,800 128 MB/s DS13 8 56 GB 288 GB 25,600 256 MB/s DS14 16 112 GB 576 GB 50,000 512 MB/s Disk Type Disk Size Storage IOPS Storage Bandwidth (MB/s) P10 128 GB 500 100 P20 512 GB 2300 150 P30 1024 GB 5000 200 GS5 32 448 GB 4,608 GB 100,000 2048 MB/s

11. VM Gallery Images

12. Storage configuration Automatically creates one Windows storage space (virtual drive) across all disks

13. Services by region All Azure regions support the VM sizes Standard_A0 – A7 and Basic_A0 – A4 . If the region supports D(v1) sizes and Premium storage then DS(v1) will be supported. Similarly if the region has G-Series and Premium storage then GS is supported.

14. Hyper scale Infrastructure is the enabler 30 Regions Worldwide, 22 Generally Available… 100+ datacenters Top 3 networks in the world 2.5x AWS, 7x Google DC Regions G Series – Largest VM in World, 32 cores, 448GB Ram, SSD… Operational Announced/Not Operational Central US Iowa West US California East US Virginia US Gov Virginia North Central US Illinois US Gov Iowa South Central US Texas Brazil South Sao Paulo State West Europe Netherlands China North * Beijing China South * Shanghai Japan East Tokyo, Saitama Japan West Osaka India South Chennai East Asia Hong Kong SE Asia Singapore Australia South East Victoria Australia East New South Wales India Central Pune Canada East Quebec City Canada Central Toronto India West Mumbai Germany North East ** Magdeburg Germany Central ** Frankfurt North Europe Ireland East US 2 Virginia United Kingdom Regions United Kingdom Regions US DoD East TBD US DoD West TBD * Operated by 21Vianet ** Data Stewardship by Deutsche Telekom

15. Microsoft Azure VMs - Security Physical Security Data centers monitored all the time Microsoft Ops and Support personnel don’t have access to customer storage Infrastructure Security Virtual Networks – Deployments are isolated in their own private networks Azure Storage – Storage accounts authentication via strong storage keys Many certifications ISO 27001/27002, SOC 1/SSAE 16/ISAE 3402 and SOC 2, Cloud Security Alliance CCM, FISMA, HIPAA, EU Model Clauses, FIPS 140-2, … SQL Security Encryption of Databases and Backups Integrated with Azure Key Vault Encryption of client connectivity Client Authentication (SQL / Windows)

16. Migrating Data Migrate from on- prem SQL server to Azure VM IaaS: Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard . Recommended method for migrating an on-premises user database when the compressed database backup file is less than 1 TB. Use on SQL Server 2005 or greater to SQL Server 2014 or greater Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine and then do a restore (only if you cannot use the above wizard or the database backup size is larger than 1 TB). Use on SQL Server 2005 or greater to SQL Server 2005 or greater Perform a backup to URL and restore into the Azure virtual machine from the URL. Use on SQL Server 2012 SP1 CU2 or greater to SQL Server 2012 SP1 CU2 or greater Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL. Use on SQL Server 2005 or greater to SQL Server 2014 or greater Convert on-premises physical machine to Hyper-V VHD , upload to Azure Blob storage, and then deploy as new VM using uploaded VHD. Use when bringing your own SQL Server license , when migrating a database that you will run on an older version of SQL Server, or when migrating system and user databases together as part of the migration of database dependent on other user databases and/or system databases. Use on SQL Server 2005 or greater to SQL Server 2005 or greater Ship hard drive using Windows Import/Export Service . Use when manual copy method is too slow, such as with very large databases. Use on SQL Server 2005 or greater to SQL Server 2005 or greater If you have an AlwaysOn deployment on-premises and want to minimize downtime, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance. Use on SQL Server 2012 or greater to SQL Server 2012 or greater If you do not have an AlwaysOn deployment on-premises and want to minimize downtime, use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance. Use on SQL Server 2005 or greater to SQL Server 2005 or greater Others: data-tier application, transact-SQL scripts, sql server import and export wizard, SSIS, copy database wizard

17. Migrate existing SQL Server apps 17 Key Benefits Low TCO for existing apps New Migration Wizard Familiar development tools “We could just pick up our SQL installs, move them over to Azure VMs, and pick up where we left off." Adam Salvo, Trek Virtual Machine Windows Server Hyper-V VHD Non-Virtualized Virtualized Option 1 Option 2 VHD Database P2V V2V Fast, easy scalability Cut IT hosting costs by $15K per month

18. 18 Key Benefits Reduce project overhead Speed time to market Secure, redundant source code “Telenor saved 70% on test, development and demo that could be turned off when finished to minimize their capital outlays,” Marius Pedersen, Telenor Group 70% savings Ready in hours, not weeks No resource limits SQL Server Dev Tools On-Premises Development Work Stations SQL Server On-Premises Deploy SQL Server in a Microsoft Azure Virtual Machine Test TFS in Microsoft Azure SQL Server Develop and test in the cloud

19. Scale VMs

20. Scale VMs Be aware a region may not support the VM size Resize requires just a VM reboot if in same family or if Azure hardware cluster supports new VM size If hardware cluster does not support new VM size: If using Resource Manager (ARM) deployment model you can resize VMs by first stopping your VM, selecting a new VM size and then restarting the VM If using Classic (ASM) deployment model, VMs must be deleted and then recreated using the same OS and data disks. See PowerShell script

21. Microsoft Azure VMs – Management Automated Patching Predictable solution for patching (Windows & SQL) Simple: just specify a time window Uses SQL Agent Extension and MS Update Portal and Powershell It relies on the Windows Update and the Microsoft Update infrastructure and installs any update that matches the ‘Important’ category for the machine

22. Microsoft Azure VMs – Management Automated Backup For all DBs in the SQL instance Simple: just specify a retention period Supports Compression and Encryption Portal and Powershell Full database and transaction log backups

23. Demo Manual Provisioning Virtual Machines on Azure

24. Licensing/bring your own license Obtain SQL image from Azure VM gallery pay per use Install or upload your own SQL Server Image

25. Connecting to Azure VMs Virtual Network VPN GW Frontend 10.1/16 Mid-tier 10.2/16 Backend 10.3/16 Internet On Premises 10.0/16 VPN & ExpressRoute AD / DNS Azure Direct Internet Connectivity Virtual Machine networking Create subnets with private or public IP addresses Bring your own DNS or use Azure-provided DNS Secure with Network Security Groups ACLs Control traffic with user-defined routes

26. Connectivity options and hybrid offerings Cloud Customer Segment and workloads Secure site-to-site VPN connectivity SMB, Enterprises Connect to Azure compute Secure point-to-site connectivity Developers POC Efforts Small scale deployments Connect from anywhere ExpressRoute private connectivity SMB, Enterprises Mission critical workloads Backup/DR, media, HPC Connect to Microsoft services Internet connectivity Consumers Access over public IP DNS resolution Connect from anywhere

27. Virtual Machine storage architecture C:\ OS d isk (127 GB) E:\, F:\, etc. Data d isks (1 TB) Attach SSD/HDD up to 1TB. These are . vhd files D:\ Temporary d isk (Contents can be lost) SSD/HDD and size depends on VM chosen Disk Cache Azure Blob storage

28. Azure Default Blob Storage Azure Storage Page Blobs, 3 copies High durability VHD disks, up to 1 TB per disk (64 TB total)

29. Geo-storage replication 3 copies locally, another 3 copies in different region Disable for SQL Server VM disk (consistent write order across multiple disks is not guaranteed). Instead use DR techniques in this deck Premium storage does not support Geo-storage Defend against regional disasters Geo replication

30. Blog Storage Replication Options LRS = Locally Redundant Storage (synchronous, same facility) ZRS = Zone-Redundant Storage (synchronous,, multiple facilities) GRS = Geo-Redundant Storage (asynchronous, multiple regions – secondary region chosen for you) RA-GRS = Read-Access Geo-Redundant Storage (readable)

31. HA/DR pieces Virtual Machine Application Storage Network SQL Server (main focus of this deck)

32. HA/DR deployment architectures AlwaysOn Failover Cluster Instances (FCI) AlwaysOn Availability Groups Database Mirroring Log Shipping Backup to Azure (blob storage) Azure Site Recovery Azure Only Availability replicas running across multiple datacenters in Azure VMs for disaster recovery. Cross-region solution protects against complete site outage. Hybrid Some availability replicas running in Azure VMs and other replicas running on-premises for cross-site disaster recovery. HA only, not DR FCI on a two-node WSFC running in Azure VMs with storage supported by a third-party clustering solution. FCI on a two-node WSFC running in Azure VMs with remote iSCSI Target shared block storage via ExpressRoute. Azure Only Principal and mirror and servers running in different datacenters for disaster recovery. Principal, Mirror, and Witness run within same Azure data center, deployed using a DC or server certificates for HA. Hybrid One partner running in an Azure VM and the other running on-premises for cross-site disaster recovery using server certificates. For DR only / Hybrid only One server running in an Azure VM and the other running on-premises for cross-site disaster recovery. Log shipping depends on Windows file sharing, so a VPN connection between the Azure virtual network and the on-premises network is required. Requires AD deployment on DR site. On- prem or Azure production databases backed up directly to Azure blob storage for disaster recovery. SQL 2016: Backup to Azure with file snapshots Simpler BCDR story Site Recovery makes it easy to handle replication, failover and recovery for your on-premises workloads and applications (not data!). Flexible replication You can replicate on-premises servers, Hyper-V virtual machines, and VMware virtual machines. Eliminate the need for secondary SQL Server data files in Azure Native support for SQL Server data files stored as Azure blobs

33. RPO/RTO RTO – Recover Time Objective. How much time after a failure until we have to be up and running again? RPO – Recover Point Objective. How much data can we lose? HA – High Availability RTO: seconds to minutes RPO: Zero to seconds Automatic failover Well tested (maybe with each patch or release) DR – Disaster Recovery RTO: minutes to hours RPO: seconds to minutes Manual failover into prepared environment Tested from time to time

34. AlwaysOn Availability Groups AlwaysOn Availability Groups Azure Only Availability replicas running across multiple datacenters in Azure VMs for disaster recovery. Cross-region solution protects against complete site outage. Hybrid Some availability replicas running in Azure VMs and other replicas running on-premises for cross-site disaster recovery. Availability replicas running across multiple datacenters in Azure VMs for disaster recovery . This cross-region solution protects against complete site outage. Within a region, all replicas should be within the same cloud service and the same VNet . Because each region will have a separate VNet , these solutions require VNet to VNet connectivity. For more information, see Configure a Site-to-Site VPN in the Azure classic portal . All availability replicas running in Azure VMs for high availability within the same region. You need to configure a domain controller VM, because Windows Server Failover Clustering (WSFC) requires an Active Directory domain. For more information, see Configure AlwaysOn Availability Groups in Azure (GUI) .

35. AlwaysOn Availability Groups (Hybrid) AlwaysOn Availability Groups Azure Only Availability replicas running across multiple datacenters in Azure VMs for disaster recovery. Cross-region solution protects against complete site outage. Hybrid Some availability replicas running in Azure VMs and other replicas running on-premises for cross-site disaster recovery. Some availability replicas running in Azure VMs and other replicas running on-premises for cross-site disaster recovery. The production site can be either on-premises or in an Azure datacenter. Because all availability replicas must be in the same WSFC cluster, the WSFC cluster must span both networks (a multi-subnet WSFC cluster). This configuration requires a VPN connection between Azure and the on-premises network. For successful disaster recovery of your databases, you should also install a replica domain controller at the disaster recovery site. It is possible to use the Add Replica Wizard in SSMS to add an Azure replica to an existing AlwaysOn Availability Group. For more information, see Tutorial: Extend your AlwaysOn Availability Group to Azure.