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
• 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 :)

1. Double blick on Setup
2. Product Key → Enter your product key → Next
3. License Terms → Accept → Next
4. Microsoft Update → Next
5. Install Setup Files → If all is fine → Next
6. Feature Selection → Click on “Database Engine Services” → Next
7. Instance Configuration → Choose Instance Name → Next
8. Server Configuration → Choose relevant → Next
9. Database Engine Configuration → Choose relevant → Next
10. Ready to Install → Next

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

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

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

1. SQL Server Configuration Manager → SQL Server services → Right Click “SQL Server” → Properties
2. 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.

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

And let's follow the configuration:

1. Introduction → Next
2. Specify Options → Specify the Availability Group Name → Next
3. Specify Database → Specify the databases you wish in that Group → Next
4. Specify Replicas → Choose which Server to be primary and which Secondary and other configuration settings → Next
5. Select Data Synchronization → Choose the approriate → Next
6. Validation → Validate that the checks passed → Next
7. Summary → Next
8. Results → Close

The result of the configuration should be something like that:

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