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.
