Create Availability Group in SQL Server 2012 Step by Step

The following are the steps needs to create an Always on Availability Group.

The SQL Instance used for this exercise are SQL Server1 and SQLServer2.

I going to use 3 user databases (ContainedDB, ReportServer and AGTest) in my SQLServer1 for creating Availability group

1.       Take a Full Backup followed by T-Log backup of all the databases on the primary server (SQLServer1) which needs to be included to availability group.

BACKUP DATABASE [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_full.bak' WITH INIT,STATS = 1

GO
BACKUP DATABASE [ReportServer] TO DISK = 'T:\Backup\ReportServer_full.bak' WITH INIT,STATS = 1
GO
BACKUP DATABASE [AGTest] TO DISK = 'T:\Backup\AGTest_full.bak' WITH INIT,STATS = 1
GO

BACKUP LOG [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_trn.bak' WITH INIT,STATS = 1

GO
BACKUP LOG [ReportServer] TO DISK = 'T:\Backup\ReportServer_trn.bak' WITH INIT,STATS = 1
GO
BACKUP LOG [AGTest] TO DISK = 'T:\EREISQLUS01_TEST2012\Backup\AGTest_trn.bak' WITH INIT,STATS = 1
GO
 
2.       Restore the Full backup and Log backup taken in step1 in NORECOVERY mode on the secondary server(SQLServer2), You can ignore Step 1 and 2 and choose Availability group wizard to take the backup and restore but I find this way quicker and efficient.

RESTORE DATABASE [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_full.bak'

WITH MOVE 'ContainedDB' TO 'G:\Data\ContainedDB.mdf',
MOVE 'ContainedDB_log' TO 'P:\TLog\ContainedDB_log.ldf',NORECOVERY,STATS = 1
GO
RESTORE LOG [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_trn.bak' WITH NORECOVERY,STATS = 1
GO

RESTORE DATABASE [AGTest] FROM DISK = 'H:\Backup\AGTest_full.bak'

WITH MOVE 'AGTest' TO 'G:\Data\AGTest.mdf',
MOVE 'AGTest_log' TO 'P:\TLog\AGTest_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [AGTest] FROM DISK = 'H:\Backup\AGTest_trn.bak' WITH NORECOVERY,STATS = 1

GO

RESTORE DATABASE [ReportServer] FROM DISK = 'H:\Backup\ReportServer_full.bak'

WITH MOVE 'ReportServer' TO 'G:\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO 'P:\TLog\ReportServer_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [ReportServer] FROM DISK = 'H:\Backup\ReportServer_trn.bak' WITH NORECOVERY,STATS = 1

 GO

3.   Connect  to SSMS of SQLServer1 expand AlwaysOn High Availability-> Right Click on the folder ->select New Availability Group Wizard

4.  Give a name to the Availability Group you want to create and Click Next, 

5.  Select the databases which need to be a part of Availability group and click next

6. Click Add Replica and connect to the secondary server, repeat the step if you want to add more than one replica.

7. I always keep the secondary databases read-only, select appropriately in Readable secondary column.

8. Select the Backup preferences menu on the page and select the appropriate backup preference, I leave it as default(Prefer Secondary)

9. Listeners can be created after the AG is set up.

10. Since we have restored the databases in secondary earlier I select Join option.

11. Click Next

12. Click Next

Availability group has been created now; let’s go back to the server to check it,
 

As shown in the picture above Availability group AGTest can be seen in both Primary and secondary server with corresponding Replica Details.
 

Now let’s see how to create a listener for the Availability group, Go to AlwaysOn High Availability -> AGtest->Availability Group Listeners; Right Click on the folder and select Add Listener.

 

Now fill the port number and Listener group Name(Note:- This Listener Group name and Port number are the details which will be used to connect to SQL Server by the users)

 

I usually chose Static IP Network mode to avoid any firewall and network issues, you can use DHCP if you like.

 

Click OK

Now that the Listener AGTestInstance has been created let’s see how users need to connect to AGTest.

 

In SSMS connect to the <ListenerName,Port Number> as the server name and connect to the AG.