Steps to setup new Availability Group and Listener in SQL 2012

Follow the below steps by step approach to setup new Availability Group and Listener using powerShell Script in SQL Server 2012.

1. Before starting ensure that you have the AG name, listener name and IP address ready.

2. On a new instance, endpoint need to be created. Only one endpoint is needed per instance pair. If you are creating a new AG in an existing instance where there are other AGs, skip to next step.

Edit the below highlighted values with appropriate values. In SQL Server Studio, execute the script in SQLCMD mode.

Setup HADR Endpoint using PowerShell Script

— On Primary
:CONNECT SQLP01\P01
create endpoint Hadr_endpoint authorization sa
state = STARTED
as TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
use [master]
GO
CREATE LOGIN [DOMAIN1\sqldba] FROM WINDOWS — SQl Service Account
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN1\sqldba]
GO
— On Secondary
:CONNECT INVSQLR01\R01
create endpoint Hadr_endpoint authorization sa
state = STARTED
as TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
use [master]
GO
CREATE LOGIN [DOMAIN1\sqldba] FROM WINDOWS — SQL Service Account
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN1\sqldba]
GO

3. Edit the below highlighted values with appropriate values. In SQL Server Studio, execute the script in SQLCMD mode.

Setup AG/Listener

— Update the variables for each AG
—
:setvar ag_name “P04_AG04”
:setvar li_name “SQLP04_L04”
:setvar pri_host “SQLP04”
:setvar pri_instance “SQLP04\P04”
:setvar sec_host “SQLR04”
:setvar sec_instance “SQLR04\R04”
:setvar ip_end “51”
— On Primary
:CONNECT $(pri_instance)
USE [master]
GO
CREATE AVAILABILITY GROUP $(ag_name)
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR
REPLICA ON
‘$(pri_instance)’ WITH
(
ENDPOINT_URL = ‘TCP://$(pri_host).ammy.abc.net:5022’,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT ,
FAILOVER_MODE = MANUAL
),
‘$(sec_instance)’ WITH
(
ENDPOINT_URL = ‘TCP://$(sec_host).ammy.abc.net:5022’,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
)
LISTENER ‘$(li_name)’ (WITH IP ( (‘10.101.11.$(ip_end)’,’255.255.255.121′), (‘10.101.11.$(ip_end)’,’255.255.255.121′)), PORT = 55055);
GO
— On Secondary – Join AG
:CONNECT $(sec_instance)
USE [master]
GO
ALTER AVAILABILITY GROUP $(ag_name) JOIN
GO

4. Update DNS entry parameters using the below PowerShell commands in a PowerShell window. Edit the highlighted values with appropriate values

PS> Import-module FailoverClusters
PS> nslookup SQLP04_L04
This will return two IP addresses specified during creation
PS> Get-ClusterResource “P04_AG04_SQLP04_L04″|Set-ClusterParameter RegisterAllProvidersIP 0
PS> Get-ClusterResource “P04_AG04_SQLP04_L04″|Set-ClusterParameter HostRecordTTL 300
PS> Stop-ClusterResource P04_AG04_SQLP04_L04
PS> Start-ClusterResource P04_AG04
PS> Get-ClusterResource “P04_AG04_SQLP04_L04″|Update-ClusterNetworkNameResource
PS> nslookup SQLP04_L04

Now this should return only one IP address where the instance is active.