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