In the last blog posting about AlwaysOn you have seen how you can deploy your first Availability Group through the AlwaysOn wizard provided by SQL Server Management Studio 2012. As usual this approach is very handy during development or testing, but as soon as you want to move your Availability Group into production, you need a more automated approach. For that reason you can also deploy and configure Availability Groups through T-SQL statements. As I have already mentioned in the previous blog posting, the wizard gives you the possibility to script your whole setup before you hit the Finish button.
Before you are actually creating your Availability Group through T-SQL you have to make the same preparations, as when you do it through the wizard, like that the database is in the FULL Recovery Model. You also have to restore your databases on the other Replicas with NO RECOVERY, so that they can be joined afterwards into your Availability Group. In the first step you have to configure security between the Replicas that are involved in the Availability Group. AlwaysOn provides the same security authentication mechanism as Database Mirroring:
- Security through Windows Authentication
- Security through Certificate Authentication
Certificate Authentication was used with Database Mirroring if both partners were not in the same Windows Domain. But as I have already mentioned in the first blog posting about AlwaysOn, ALL your Replicas must be in the SAME Windows Domain, so setting up security through Windows Authentiction is the most common and preferred scenario in AlwaysOn.
When all SQL Server Instances of your Replica are running under the same Windows Service Account, you don’t have to bother about separate logins in each SQL Server Instance. But when your Instances are running under different service accounts, you have to create on each Replica all the logins for the other service accounts that are connecting to that Replica. Today I’m assuming 2 Replicas, where each Replica runs under a separate service account:
- Replica 1: sqlpassion0\ag-node1_sqlsvc
- Replica 2: sqlpassion0\ag-node2_sqlsvc
So you have to create a login for the other service account on each Replica.
-- Create a new login for AG-NODE2 on Replica 1 CREATE LOGIN [SQLPASSION0\ag-node2_sqlsvc] FROM WINDOWS GO -- Create a new login for AG-NODE1 on Replica 2 CREATE LOGIN [SQLPASSION0\ag-node1_sqlsvc] FROM WINDOWS GO
In the next step you have to create a new SQL Server endpoint for Database Mirroring. You need that endpoint again on both Replicas. Through that endpoint AlwaysOn handles the communication between the Replicas involved in your Availability Group.
-- Create a new Database Mirroring Endpoint on Replica 1 CREATE ENDPOINT Hadr_Endpoint AS TCP ( LISTENER_PORT = 5022 ) FOR DATA_MIRRORING ( ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES ) GO -- Start the Endpoint on Replica 1 ALTER ENDPOINT Hadr_Endpoint STATE = STARTED GO -- Create a new Database Mirroring Endpoint on Replica 2 CREATE ENDPOINT Hadr_Endpoint AS TCP ( LISTENER_PORT = 5022 ) FOR DATA_MIRRORING ( ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES ) GO -- Start the Endpoint on Replica 2 ALTER ENDPOINT Hadr_Endpoint STATE = STARTED GO
When you are planning to create Availability Groups between SQL Server Instances on the same physical machine, you have to make sure that you are using different ports for each endpoint.
Note: Please make also sure to open the corresponding port number of the endpoint on your firewall.
As you can see from the previous listing, the endpoint must be also explicitly started. In the next step you have grant the CONNECT permission on the endpoint to the previous created login.
-- Grant the CONNECT permission to the login for Replica 2 on Replica 1 GRANT CONNECT ON ENDPOINT::[Hadr_Endpoint] TO [SQLPASSION0\ag-node2_sqlsvc] GO -- Grant the CONNECT permission to the login for Replica 1 on Replica 2 GRANT CONNECT ON ENDPOINT::[Hadr_Endpoint] TO [SQLPASSION0\ag-node1_sqlsvc] GO
When you are installing a SQL Server 2012 Instance, the installation program also configured an Extended Event Session for AlwaysOn. This event session is disabled by default. So it’s a good practice to enable that event session, because it records some critical events about AlwaysOn that can help you in troubleshooting your deployment.
-- Start the AlwaysOn Health Extended Event Session 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 GO 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
As you can see from the listing, the event session will be also automatically started, as soon as you are restarting your SQL Server Instance. By now you have configured the whole security stuff for AlwaysOn and both Replicas are now able to communicate with each other.
In the next step you have to create your actual Availability Group. SQL Server 2012 provides you for this task the CREATE AVAILABILITY GROUP T-SQL statement. See the description from Books Online for further information about it: http://msdn.microsoft.com/en-us/library/ff878399.aspx.
Note: You have to call CREATE AVAILABILITY GROUP from that Replica that should be the initial Primary Replica.
The following T-SQL code shows how to create your Availability Group between 2 Replicas, where the Availability Group contains the databases TestDatabase1 and TestDatabase2.
-- Create a new Availability Group with 2 Replicas
CREATE AVAILABILITY GROUP TestAG
WITH
(
AUTOMATED_BACKUP_PREFERENCE = SECONDARY
)
FOR DATABASE [TestDatabase1], [TestDatabase2]
REPLICA ON
'AG-NODE1' WITH
(
ENDPOINT_URL = 'TCP://ag-node1.sqlpassion.com:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE
(
ALLOW_CONNECTIONS = NO
)
),
'AG-NODE2' WITH
(
ENDPOINT_URL = 'TCP://ag-node2.sqlpassion.com:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE
(
ALLOW_CONNECTIONS = NO
)
)
GO
The property ENDPOINT_URL must match with the endpoint that you have created earlier through the CREATE ENDPOINT T-SQL statement. So you have to make sure that the port numbers are identical. To get the other Replica joined into the Availability Group you have to run the following statement on it:
-- Make the Availability Group available on AG-NODE2 ALTER AVAILABILITY GROUP [TestAG] JOIN GO
By now you have set up the Availability Group between both Replicas, but you also have to explicitly include the databases on the other Replica into the Availability Group. So for that case, you have to restore the databases on the other Replica (with NO RECOVERY), and finally execute an ALTER DATABASE statement.
-- Move each database into the Availability Group ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG ALTER DATABASE TestDatabase2 SET HADR AVAILABILITY GROUP = TestAG GO
After that final step your Availability Group is up and running, and you can check its status through the Dashboard, as I have shown you in the previous blog posting.
I hope that you got a good overview in this blog posting how to deploy Availability Groups through T-SQL. This should be your preferred option when you are finally deploying your Availability Group into production. In the next installment of this series we will talk about Failovers in AlwaysOn.
Stay tuned and thanks for reading!
-Klaus
