Differences

This shows you the differences between two versions of the page.

--- mssql_alwayson_groups [2019/11/05 14:26] – andonovj
+++ mssql_alwayson_groups [2019/11/09 19:58] (current) – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
 In order to configure a AlwaysOn Group in SQL Server you firstly must configure:
-  * Failover Windows Cluster
+  * Failover Windows Cluster (You can check how in the windows section: [[windows_advanced_failover|Failover Cluster Config]] )
   * 2 SQL Stand-alone Binary installations
@@ Line 40: / Line 40: @@
 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>