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.