Azure Power Shell and SQL Server Virtual Machines

Azure, Power. Shell, and SQL Server Virtual Machines Rob Volk

Abstract - Original • While the web portal makes it easy to deploy SQL Server on Azure Virtual Machines, there a number of benefits to using Power. Shell to do the job. • This session will introduce Azure Power. Shell command-lets and how to create a basic Azure infrastructure to support a SQL Server VM. • We'll include the Azure Resource Manager, how to use ARM VM templates and Custom Script Extension, and how users can modify an existing Azure Gallery template. • Desired State Configuration (DSC) will be covered to complete the Azure Powershell automation toolkit, allowing attendees to automate the deployment of dozens or more SQL Server virtual machines. • Finally, we will configure Availability Groups and explain their eccentricities in the Azure environment as well as how to make them work effectively.

Abstract – Since • While the web portal makes it easy to deploy SQL Server on Azure Virtual Machines, there a number of benefits to using Power. Shell to do the job. Web Portal is much nicer now. • This session will introduce Azure Power. Shell command-lets and how to create a basic Azure infrastructure to support a SQL Server VM. • We'll include the Azure Resource Manager, how to use ARM VM templates and Custom Script Extension, and how users can modify an existing Azure Gallery template. We’ll demo this on the portal and Power. Shell. • Desired State Configuration (DSC) will be covered to complete the Azure Powershell automation toolkit, allowing attendees to automate the deployment of dozens or more SQL Server virtual machines. • Finally, we will configure Availability Groups and explain their eccentricities in the Azure environment as well as how to make them work effectively. Load Balancer configuration.

What We'll Cover • Why Azure? Why VMs? • Azure Portal vs. Power. Shell • Resource Manager Templates

Why Azure? • Cloud (Virtual Data Center) • No hardware dependencies • Geographical replication/availability • Easy scalability for highly variable load • - Dependent on Microsoft

Why VMs? • Infrastructure as a Service (Iaa. S) • VM image can be deployed as package • Can include custom features, retain full control • Platform as a Service (Paa. S) • Some feature restrictions • Updated/changed continuously • Regulatory & compliance levels (less likely today)

Azure Portal • Web based management and deployment • Deployment of hundreds of VMs or services cumbersome • Manual (Runbooks can do automation)

Azure Power. Shell • Full scripting support • Parameters, automation • Easier for large deployments

Azure Resource Manager • Better deployment model, more features • Can use templates for VM config • Huge library of predefined templates

Desired State Configuration (DSC) • Good for non-SQL installs (DC, Web, basic OS) • SQL DSC tricky/incomplete • Mike Fal • http: //www. mikefal. net/ • Azure DSC • https: //azure. microsoft. com/enus/documentation/articles/automation-dsc-overview/ • It has improved, it will continue to improve…

Other Quirks • Disk arrangement • VHDs on spinning disks (Standard) • VHDs on SSDs (Premium) • Striping of disks via Storage Spaces • Network Load Balancer • Public IP allocation/reservation • Monitor costs (Executive Reports in Portal)

Resources Azure & Resource Manager (ARM) • ARM Templates: • https: //azure. microsoft. com/en-us/documentation/articles/resource-group-authoring-templates/ • https: //azure. microsoft. com/en-us/documentation/articles/best-practices-resource-manager-design-templates/ • https: //azure. microsoft. com/en-us/documentation/articles/resource-group-template-deploy/ • SQL Server ARM Templates: • https: //azure. microsoft. com/en-us/documentation/templates/? term=sql+server • Example: https: //azure. microsoft. com/en-us/documentation/templates/201 -vm-sql-full-autobackup/ • Github: https: //github. com/Azure/azure-quickstart-templates/tree/master/201 -vm-sql-full-autobackup • Power. Shell for Taming Azure: • https: //blogs. technet. microsoft. com/andrew/2016/04/20/powershell-for-taming-azure/ • Azure Runbooks: • https: //azure. microsoft. com/en-us/blog/azure-automation-runbook-management/ • Azure Dev Test Lab: • https: //azure. microsoft. com/en-us/services/devtest-lab/

Resources (cont’d) Tips and Gotchas: • Configure ILB for Availability Group Listener • https: //blogs. msdn. microsoft. com/igorpag/2016/01/25/configure-an-ilb-listener-for-sql-server-alwaysonavailability-groups-in-azure-arm/ • 15 Character naming limit for AD objects (Including Listeners): • http: //blog. sqlauthority. com/2016/08/01/sql-server-unable-create-listener-alwayson-availability-group-azure-via -template-deployment/ • Pre-stage cluster objects in Active Directory: • https: //technet. microsoft. com/en-us/library/dn 466519. aspx Powershell and Desired State Configuration: • Mike Fal blog: http: //www. mikefal. net/ • Virtual Academy: https: //mva. microsoft. com/en-US/training-courses/powershell-for-sql-data-professionals 16532? l=Xg. A 5 w 0 Pg. C_8805121157

Conclusion • Azure VMs are viable for production • Costs are shifted over different resources (bandwidth, CPU use, data egress) • Power. Shell is an excellent deployment model • Portal has been improving steadily • ARM templates recommended deployment • Azure improves and changes constantly

Contact • Email: sql_r@outlook. com • Twitter: @sql_r • Blog: http: //weblogs. sqlteam. com/robv/

Thanks! • Sponsors • Volunteers & Organizers • YOU!