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:
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 :)
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:
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:
The result of the configuration should be something like that:
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:
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.
There were couple little problems, as always.
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.
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.
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
Manual AlwaysOn Script
--- 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
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