When SQL Server 2012 rolled out, the big feature for the release was Availability Groups. This new technology to support high availability in SQL Server is pretty impressive and has become a cornerstone for database operations in many shops. When it was introduced, the SQL team added more than twenty new cmdlets for Availability Groups in SQLPS. For those keeping score at home, this is about half of the available cmdlets in the module. Microsoft has documented them fairly well online, making them some of the more useful cmdlets available in the SQLPS module.
While the full details of Availability Groups are outside the scope of this series, we will cover many details of Availability Group management and creation as we review the different cmdlets in the module. The first of these have to do with the SQL Server service and enabling AlwaysOn for that service. Normally, we would open up the properties of the service in the Configuration Manager, check off the box for the functionality, and restart the service:
That’s a lot of clicking, isn’t it? Fortunately, Powershell gives us an easier way. Let’s do a quick lookup of of all the cmdlets containing SqlAlwaysOn:
Get-Command -Module SQLPS *SqlAwaysOn
Running this returns two cmdlets, one for enabling the feature, the other for disabling. Pretty handy, eh? Now, instead of having to open up all sorts of Windows, we can run Enable-SqlAlwaysOn to enable the feature:
Enable-SqlAlwaysOn -ServerInstance KIRK -Force
The cmdlet will accept the instance name, the provider path, or a Smo.Server object to identify the service it needs to alter. By default, running the command will force a service restart, but you can postpone that by using the -NoServiceRestart switch. Disable-SqlAlwaysOn works in the same way, just disabling the feature instead of turning it on.
Before we can create an Availability Group, there’s one other piece of prep that needs to be done within SQL Server. In order for databases to replicate their data to replicas, it needs an endpoint. Normally, we would create this via T-SQL, but fortunately there are cmdlets within SQLPS that make creating these easier:
Get-Command -Module SQLPS *HADREndpoint
Setting up the endpoint is a simple two step process. First we create it using the New-SqlHADREndpoint cmdlet, which creates the endpoint with all the options we want use. Once we have the endpoint created, we need to start it, using the Set-SqlHADREndpoint to alter its state. Fortunately, we can combine these two steps into one using the pipeline:
New-SqlHadrEndpoint HADR_Endpoint -Port 5022 -Path SQLSERVER:\SQL\KIRK\DEFAULT | Set-SqlHadrEndpoint -State ‘Started’
Note, this only creates the endpoint and makes it available. We still have to map the Availability Group to the endpoint, which we will do when we create it.
One difference with the endpoint commands from some of the other cmdlets is that they don’t accept ServerInstance as an argument. We can only pass the provider path or an Smo.Server object. This is where having a cmdlet for retrieving the instance as an SMO object would be handy, but we’ll have to settle for either creating the Smo.Server object ourselves or using the provider path.
With this, we’ll have an instance ready for creating an Availability Group. I’ve skipped over cluster creation, as that is handled by cmdlets from the FailoverCluster Powershell module. Hint, it’s pretty easy. Next up, we’ll go over the cmdlets and steps for building our Availability Group.
I’m tweeting!