Hey guys,
In this blog I am going to explain you Step
by Step implementation
on SQL Server Always on Availability Group
Always on Availability group – The Always On availability groups feature is a high availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012 (11.x), Always on availability groups maximizes the availability of a set of user databases for an enterprise.
· Ensure that the system is not a domain controller.
· Ensure that each computer is running Windows Server 2012 or
later versions.
· Ensure that each computer is a node in a WSFC.
· Ensure that the WSFC
contains sufficient nodes to support your availability group configurations.
Step
by Step implementation of SQL Server Always on Availability Group
STEP -1: First you have to open instance and
select databases which you want to put in AG
Note: the databases are in full recovery mode
STEP -2: Go to Always on high availability -> Availability Groups
-> New Availability group Wizard as shown below,
Fig -1
STEP
-3:
In Availability groups option you have to specify an Availability group name
and Cluster type i.e., “Windows
Server Failover cluster “
Click on NEXT >
Fig -2
STEP
-4:
In this step, you have to select databases as shown in Fig-3
Click on NEXT >
Fig-3
STEP
-5:
In Specify Replicas option, you have to click on Add Replicas -> Server-name -> connect to
secondary replica which you want to put in AG
Click on NEXT >
STEP
-6:
In End-points, you have to go with default values
Fig-5
STEP -7: In Backup Preferences, you have to select Prefer Secondary so that Automated backup for the availability group should occur on a secondary replica.
Click on NEXT >
Fig-6
STEP -8: In Listener, you have to go with “create an availability group listener” and select a Listener DNS name and port as your requirement.
In Network Mode,
you have to add IP Address for which listener listen
to that IP and this IP is not used by another user
Click on NEXT
>
Fig -7
STEP
-9:
In Read-Only Routing, you have to go with default parameters,
Click on NEXT >
Fig -8
STEP -10: In Data Synchronization, you have to select automatic seeding so that SQL server automatically creates databases for every selected secondary replica.
Click on NEXT >
Fig-9
STEP
-11:
In validation, you have to validate all the parameters
Click on NEXT >
Fig -10
STEP
-12:
After progress click on close.
Fig -11
STEP
-13:
Availabilty group has been created in both the server as shown below,
Fig -12
STEP -14: In below diagram, it is showing same databases on a different instance.
In this, primary instance database is in read/write mode while secondary instance is in read only mode, Both the server is Synchronized with each other.
Fig -13
STEP -15: Now, if you want to do the failover on primary server so that your primary server becomes secondary and secondary server becomes primary then follow these steps primary instance –> Always on High Availability -> Availability Groups -> Right click -> Availability group name -> Failover
If you do the failover than your primary server is in read only mode while secondary server is in read/write mode.
STEP
-16:
For Failover cluster process for the availability group, Go to Availability Group name -> Right click -> Failover
Click on NEXT >
Fig -15
STEP
-17:
In Select new primary replica, go with the default parameters.
Click on NEXT
>
Fig -16
STEP
-18:
In this you have to connect to the secondary server and
Click on NEXT >
Fig -17
STEP
-19:
In summary, click on Finish
Fig -18
STEP
-20:
Check the results and click on close
Fig -19
STEP
-21:
Now, you can check that your primary server becomes secondary and secondary
server becomes primary as shown below.
Fig -20
No comments:
Post a Comment
If you have any doubt or question, please contact us.