Cloud and Datacenter Management Blog

Microsoft Hybrid Cloud blogsite about Management

#Microsoft SQL Always-On Cluster vs #SQL Managed Instance in #Azure

Leave a comment


SQL Always-On Cluster in Azure

Before we start with building this SQL Always-On Cluster we already have some Azure SDK Components Active in the Azure Subscription to work with:

  • Virtual Network VNET-001 is already installed
  • Subnet-SQL and Subnet-Domaincontrollers
  • Network Security Groups (NSG) with the right rules active
  • Two domain controllers
  • Azure Keyvault ( for disk Encryption)

We deployed three Virtual Machines in an Availability Set :

  • Primary SQL Node VM01
  • Secondary SQL Node VM02
  • Witness Server

The deployment was with ARM Template :

VM Deployment

Copy and paste the JSON in your template editor like Visual Studio Code for Example or in the Azure Portal Template builder.

Visual Studio Code

Azure Portal Template

Read more how to deploy ARM Templates via Microsoft Azure Portal here

You also can create a Private or Public Repository on GitHub and store your ARM Templates there in a Library.

GitHub Learning Lab
Learn new skills by completing fun, realistic projects in your very own GitHub repository. Get advice and helpful feedback from our friendly Learning Lab bot.

Create a SQL Server 2014 Always On Availability Group in an existing Azure VNET and an existing Active Directory instance via GitHub :

https://github.com/Azure/azure-quickstart-templates/tree/master/sql-server-2014-alwayson-existing-vnet-and-ad

Configure Always On Availability Group in Azure VM manually :

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

Important Tip :

Don’t forget to get the right connectivity between Azure Load Balancer and the SQL Always-On Listener :

## Get the Cluster Resource Information:
Clear-Host
Get-ClusterResource `
| Where-Object {$_.ResourceType.Name -like “IP Address”} `
| Get-ClusterParameter `
| Where-Object {($_.Name -like “Network”) -or ($_.Name -like “Address”) -or ($_.Name -like “ProbePort”) -or ($_.Name -like “SubnetMask”)}

#############################################################

## Set Cluster Parameters:
$ClusterNetworkName = “Cluster Network 1” # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = “IPlistener” # the IP Address resource name
$ListenerILBIP = “10.x.x.x” # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
[int]$ListenerProbePort = 80

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{“Address”=”$ListenerILBIP”;”ProbePort”=$ListenerProbePort;”SubnetMask”=”255.255.255.255″;”Network”=”$ClusterNetworkName”;”EnableDhcp”=0}

############################################################

Before you start with your SQL workloads from On-premises Datacenter on Microsoft Azure, have a look if PaaS Azure SQL Managed Instances is something for your Organization with all the benefits.

What is Microsoft Azure SQL Managed Instance?

Managed instance is a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers. The managed instance deployment model allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, the managed instance deployment option preserves all PaaS capabilities (automatic patching and version updates, automated backups, high-availability ), that drastically reduces management overhead and TCO.

Read here more on Microsoft Docs about Azure SQL Services

Conclusion :

When you have a lot of SQL workloads and want to go to Microsoft Azure Cloud Services, analyze your existing workloads well and have a look first at Microsoft Azure SQL Managed Instances. With this Azure PaaS Service, you don’t have to manage the Complete Infrastructure like in a SQL Always-On Cluster (IaaS).

Have a good look at the requirements and Microsoft Data Migration Services can help you out.

SQL Server instance migration to Azure SQL Database managed instance

 

Author: James van den Berg

I'm Microsoft Architect and ICT Specialist and Microsoft MVP Cloud and Datacenter Management

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.