Deploying SQL Server With Microsoft Azure Virtual Machine
- Slides: 25
Deploying SQL Server With Microsoft Azure Virtual Machine Balakrishnan Shankar, Microsoft balaks@Microsoft. com Credits to Bob Ward and Keith Elmore Based on actual customer experiences
Thank You Sponsors! Visit the Sponsor tables to enter their end of day raffles. Turn in your completed Event Evaluation form at the end of the day in the Registration area to be entered in additional drawings. Want more free training? Check out the North Texas SQL Server User Group Details at http: //ntssug. sqlpass. org/
Introduction Objectives Take Aways 3 | • • • Understand our cloud database offerings Understand deployment options for Azure VM Configure for optimal performance and connectivity How to deploy Always. On Availability Groups in the Cloud Learn hybrid cloud scenarios • Deploying an Azure VM with SQL Server can be fast • Some configuration required for optimal performance • Think “SQL Box in a VM” in the Cloud
Introduction Just do It! Do you know Cloud? How to provision SQL Server in Azure VM What’s Next? Configuration for Success Validating your deployment Review Always. On Availability Groups Deployment Watch out for these Futures for Azure VM 4 |
Demo § Just do It! § Let’s spin up a SQL VM in Azure § Review SQL Gallery Images 5 | You can get a 1 month free trial with $200 credit
Do you know the Cloud? We host your virtual machines on our hardware We provide services such as VM failover and redundant storage Infrastructure as a Service (IAAS) – SQL Server You have complete control and ownership to manage your VM and SQL Server You own your SQL HA solution using “box” technologies You Infrastructure as a Service (IAAS) VM DB 6 | Microsoft
Do you know the Cloud? We abstract the VM and “physical” aspects of SQL Server We host your database Build a database very quickly Platform as a Service (PAAS) – Azure SQL Database Don’t worry about instance, physical files, and storage Performance predictability, auto HA, geo-replication You Platform as a Service (PAAS) VM DB 7 | Microsoft
SQL Server in Azure VM - Provisioning pay as you go license Use the Azure Management Portal No setup required • Gallery images • Provision Windows VM – SQL Setup on your license own Bring your own VM • sysprep a VM, install SQL, and upload the image to Azure storage Azure Powershell cmdlets 8 |
VM Sizes table here A & D Series VM • Don’t use for Prod SQL Workload The G Series VM • Intel® Xeon® processor E 5 v 3 family. • Larger sizes (G 5: 32 cores, 448 Gb RAM, 6. 5 TB Local SSD) Made wrong choice? Change it DS v 2 & DS Series VM • • Recommended for SQL Prod Work Load Local SSD storage Premium Storage Portal Optimizes VM for SQL Workload GS Series VM • Local SSD Storage • Premium Storage • Intel® Xeon® processor E 5 v 3 family. 9 | Read this blog post
Choices to Make ü Administrator account ü Use ARM Deployment Model – except for… ü Use existing resources from Classic Model ü DNS Label name ü Can be different than VM name. This is the “network name” ü Virtual Network ü Review Virtual Network Overview to help create Azure Virtual network ü Storage Account ü Use Premium Storage ü Max 50 Gigabits Per Second Inbound + Outbound ü 35 TB Disk Capacity ü Max IOPs for an account is 80, 000 ü Availability Set ü Separate VMs from same fault and update domain ü Required for Always. On AG ü NSG ü Default enables only RDP port 10 |
What’s Next? – Configuration for Success Connect with Remote Desktop Make storage decisions • • IOPs limit per disk Bytes/sec bandwidth restrictions exist At Minimum use 2 Premium P 30 Disk Storage Pool for more Throughput Do not use System Drive Enable Read caching for Premium Disk 64 K NTFS Allocation Unit Size Enable Instant file initialization The checklist “Portal Optimizes” this for you DS and GS Series only. Steps Enable Locked Pages Turn off Geo-Replication 11 | Doesn’t guarantee write ordering
Connectivity Configuration Did you create a Public Network Interface/IP ? Did you configure NSG to Allow SQL Port 1433 Firewall considerations on Azure • Just like box make exception for port 1433 in your VM 12 | Enable SQL Authentication • Unless you connect with Virtual Network and domain authentication Internal load balancing supported
Validating the deployment Verify connectivity and network latency Verify your IOPS Tools to use • • 13 | Perfmon Standard “SQL” tools Portal dashboard Portal alerts Operation IOPS Index 4 -6 k checkpoint 1 -2 k Read-ahead 300 -500 CHECKDB WITH 1 k PHYSICAL_ONLY
SQL Iaas Extension 14 | • Plugin installed with SQL Virtual Machine from Gallery • Provides Automated backup & Auto Patching SQL Automated Backup • Enable/Manage the Managed Backup from Portal • SQL Backups to Azure Blob Storage SQL Automated Patching • Patch Management for SQL VM from Portal • Incompatible with Windows Automatic Update • Patches installed which are marked “Important”
Demo § Review Deployed Azure Virtual Machine 15 |
Deploying Always. On AG § 3 scenarios § All in Azure in same data center § #1 + Secondary's across data centers async § Hybrid – secondary on premise and in data centers A very good resource § Do it yourself § This is a fairly lengthy process but well documented § Remember you need a DC in Azure and a Virtual Network Internal load balancer § Automate with “Always. On Template” to control access § AG Listener supported for both “all in cloud” and hybrid § You cannot bring your own VM with AG already installed 16 |
Futures for Azure VM FCI Support for SQL 17 | Always. On Template for Existing Domain Microsoft Templates in Git. Hub (in progress) Diagnostics with SQL Iaas Extension
References § Getting Started with SQL Server in Azure Virtual Machines § 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) 18 |
Review Objectives Take Aways 19 | • • • Understand our cloud database offerings Understand deployment options for Azure VM Configure for optimal performance and connectivity How to deploy Always. On Availability Groups in the Cloud Learn hybrid cloud scenarios • Deploying an Azure VM with SQL Server can be fast • Some configuration required for optimal performance • Think “SQL Box in a VM” in the Cloud
Appendix Bring your Own VM More Configuration Fun Hybrid Cloud Scenarios Gotchas ARM vs ASM (Classic) 20 |
Bring your Own VM Full SQL install or sysprep SQL install Install and sysprep Windows in Hyper-V No portal Support 21 | Upload your “image” Provision multiple new VMs with this image Through Power. Shell
More Configuration Fun q Copy of data (backup files, bcp import files, …) q Look at this resource q. Use the azcopy tool. See this resource q Backup database q Backup to Azure blog storage separately from your disks q SQL 2012+ Supports Backup to URL q Use Premium Disk if you have bandwidth q Consider RG I/O and –k for any I/O throttling q Apply fix for tempdb eager writes if SQL 2012 q Turn off unused services (SSAS, SSRS, …) 22 |
Hybrid Cloud Scenarios SQL 2012 and 2014 Backups to Azure • Backup path now a URL • VDI application communicates to Azure • Managed backups allow “auto backup to the cloud” Check out Express. Route AG Secondary in Azure • Requires multi-subnet cluster and VPN connectivity • Make this async SQL 2014 SQL Database Files in Azure Storage 23 | • • Database/Log file path supports URL Uses HTTP REST API Some limitations Expectations with network latency
Gotchas I/O Performance Expectations • Use Premium Storage • Batching transactions are more important • Set autogrow correctly Licensing • Can’t transfer your own license for gallery images • Requires Passive node to be Licensed Upgrade Support Restrictions Autoscale for Azure Max Limits for Azure 24 | • Can’t upgrade a Std SKU gallery image to EE SKU • Stay up to date with this KB article • Only for apps that don’t need I/O • Not supported for SQL Server • see here
ARM Vs ASM (Classic) Azure Resource Group (ARM) Azure Service Manager (Classic) 25 | • • Provides dependencies between resources Provides Role Based Access to resources Provides templates to define deployment Virtual Machine requires Virtual network Not completely compatible with ARM Does not require Virtual Network Virtual Machine requires Cloud Service Resource Group created by Classic is not same as ARM RG • Virtual machine deployed cannot be part of ARM based Virtual Network
- Sql server machine learning services
- Kubernetes gpgpu
- Fire hose loads
- Azure secure enclave
- マイクロソフト データベース
- Logical server
- Azue sql server stretch monitoring
- What is windows azure virtual machine
- Microsoft sql server 2005 analysis services
- Microsoft sql server introduction
- Microsoft sql server security best practices
- Microsoft sql server data virtualization
- Server management studio express
- Sql server parallel data warehouse
- Xxxdbo
- Kereberos
- Raspberry pi mssql server
- Ms sql security best practices
- Microsoft sql server compact
- Mvmc download
- Azure erdrich
- Azure cdc
- Azure sql database benchmark
- Sys.dm_pdw_exec_requests
- Mpp architecture azure
- Azure sql database sharding