mssql_alwayson_groups [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Overview======
AlwaysOn group is functionality for Microsoft SQL server which aims to provide high availability by providing failover capabilities for a database.

In order to configure a AlwaysOn Group in SQL Server you firstly must configure:

  * Failover Windows Cluster (You can check how in the windows section: [[windows_advanced_failover|Failover Cluster Config]] )
  * 2 SQL Stand-alone Binary installations

P.S. You can check the windows section fo how to configure it.

Once it is configured let's start the installation of stand-alone SQL :)

=====Installation of SQL binaries=====

  - Double blick on Setup
  - Product Key -> Enter your product key -> Next
  - License Terms -> Accept -> Next
  - Microsoft Update -> Next
  - Install Setup Files -> If all is fine -> Next
  - Feature Selection -> Click on "Database Engine Services" -> Next
  - Instance Configuration -> Choose Instance Name -> Next
  - Server Configuration -> Choose relevant -> Next
  - Database Engine Configuration -> Choose relevant -> Next
  - Ready to Install -> Next

In the end you should have something similar to the image below, depending on the settings you provided:

{{ :sqlinstanceinstallation.jpg?400 |}}

=====Enable AlwaysOn=====
As everything with Microsoft Servers, stuff have to be enabled, otherwise you will receive the following issue which you try to configure them:

{{ :alwaysonnotenabled.jpg?400 |}}

As said on the error, let's enable it on both servers:

  - SQL Server Configuration Manager -> SQL Server services -> Right Click "SQL Server" -> Properties
  - SQL Server Properties -> Click on the "AlwaysOn High Availability" tab -> Click the "Enable AlwaysOn Availability Groups"

After the change has been made, make sure to restart the service, so the changes will take effect.

=====Configuration=====
To configure it, open the AlwaysOn Tab on the Management studio:

{{ :alwaysonwizard.jpg?400 |}}

And let's follow the configuration:

  - Introduction -> Next
  - Specify Options -> Specify the Availability Group Name -> Next
  - Specify Database -> Specify the databases you wish in that Group -> Next
  - Specify Replicas -> Choose which Server to be primary and which Secondary and other configuration settings -> Next
  - Select Data Synchronization -> Choose the approriate (p.s. I started with automatic but then I provided shared location) -> Next
  - Validation -> Validate that the checks passed -> Next
  - Summary -> Next
  - Results -> Close

The result of the configuration should be something like that:

{{ :alwaysonconfigurationdonedb2.jpg?400 |}}


You can use also the following script for manual configuration in the Appendix.

After all that you should have all databases selected throughout the configuration in restoring mode ON the second machine:

{{ :alwaysonconfigrunning.jpg?400 |}}


I know that from security point of view, this isn't the best, but I try to make it working and later will improve security, I promise.




=====Fixing Issues=====
There were couple little problems, as always.

====Wrong Service User====
The default user didn't have right to access the DCOM, therefore I had to change the user which was used from Local user to SQLDOM\Administrator.

====AlwaysOn Group Listener====
The listener, I have decided to create it after I create the AlwaysOn group. So be sure to enable it after the creation if you didn't do it during.

====Not Synchronizing====
After the initial creation, the replicas were fine, but the database wasn't synching. So don't forgot to join the database to the group after that


=====Appendix=====
Manual AlwaysOn Script
<sxh bash>
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect 10.20.14.5

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect 10.20.14.5
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect WINNODEC
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect WINNODEC
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect 10.20.14.5
USE [master]
GO
CREATE AVAILABILITY GROUP [SQLAWG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR DATABASE [AlwaysOnDB1]
REPLICA ON N'WINNODEB' WITH (ENDPOINT_URL = N'TCP://WINNODEB.sqldom.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
	N'WINNODEC' WITH (ENDPOINT_URL = N'TCP://WINNODEC.sqldom.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
:Connect WINNODEC
ALTER AVAILABILITY GROUP [SQLAWG] JOIN;
GO
:Connect 10.20.14.5
BACKUP DATABASE [AlwaysOnDB1] TO  DISK = N'\\WINNODEB\Shared\AlwaysOnDB1.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect WINNODEC
RESTORE DATABASE [AlwaysOnDB1] FROM  DISK = N'\\WINNODEB\Shared\AlwaysOnDB1.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
:Connect 10.20.14.5
BACKUP LOG [AlwaysOnDB1] TO  DISK = N'\\WINNODEB\Shared\AlwaysOnDB1.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect WINNODEC
RESTORE LOG [AlwaysOnDB1] FROM  DISK = N'\\WINNODEB\Shared\AlwaysOnDB1.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
GO
</sxh>
=====Disable/Enable Jobs on Primary=====
<sxh bash>
DECLARE @SQL NVARCHAR(MAX)

;WITH DBinAG AS (  -- This finds all databases in the AG and determines whether Jobs targeting these DB's should be turned on (which is the same for all db's in the AG)
SELECT  distinct
        runJobs = CASE WHEN role_desc = 'Primary' THEN 1 ELSE 0 END   --If this is the primary, then yes we want to run the jobs
        ,dbname = db.name
        ,JobDescription = CASE WHEN hars.role_desc = 'Primary'  -- Add the reason for the changing the state to the Jobs description
                THEN '~~~ [Enabled] using automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) looking for jobs running against Primary Replica AG ~~~ '
                ELSE '~~~ [Diabled] using Automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) because the job cant run on READ-ONLY Replica AG~~~ ' END 
FROM sys.dm_hadr_availability_replica_states hars
INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
INNER JOIN sys.Databases db ON  db.replica_id = hars.replica_id
WHERE is_local = 1
AND ag.Name = @AGname
) 

SELECT @SQL = (
SELECT DISTINCT N'exec msdb..sp_update_job @job_name = ''' + j.name + ''', @enabled = ' + CAST(d.runJobs AS VARCHAR) 
                + ',@description = ''' 
                + CASE WHEN j.description = 'No description available.' THEN JobDescription -- if there is no description just add our JobDescription
                       WHEN PATINDEX('%~~~%~~~',j.description) = 0 THEN j.description + '    ' + JobDescription  -- If our JobDescription is NOT there, add it
                       WHEN PATINDEX('%~~~%~~~',j.description) > 0 THEN SUBSTRING(j.description,1,CHARINDEX('~~~',j.description)-1) + d.JobDescription  --Replace our part of the job description with what we are doing.
                       ELSE d.JobDescription  -- Should never reach here...
                    END 
                + ''';'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
INNER JOIN DBinAG d ON d.DbName =s.database_name     
ON j.job_id = s.job_id
WHERE j.enabled != d.runJobs   -- Ensure we only actually update the job, if it needs to change
FOR XML PATH ('')
)
PRINT REPLACE(@SQL,';',CHAR(10))
EXEC sys.sp_executesql @SQL
</sxh>