Sql Server 2012 Always on Availability Groups

Sql Server 2012 Always on Availability group is a new form of   High availability  which has many advantages  to the database  mirroring  and it can be  seen as an alternative solution  to the database  mirroring solution  for  high availability

Some of the important advantages of Always On Groups

• Always on availability group enables  group of data bases to fail over  when compared to single database  of mirroring
• It allows up to 4 secondary  replicas  which  can be used as an active readable database and fail over over occurs on a per replica bases. 
• Supports  automatic fail over , automatic page repair  and the fail over occurs at the instance level

Setting up Always on Availability group

Key Steps:

• Sql server Always on requires all the participating nodes to be joined to a failover cluster
• It doesn't require sql server to be installed as a clustered instance
• Use the domain accounts for the sql server service accounts of the all the participating instances as it enables to create mirroring end points of the all the participating nodes automatically 
• Always On requires the primary replica  database to be in full recovery mode and  if you got the secondary instances with different names than  you need to manually take the full backs and transaction log backups and leave it in a recovery state

Creating the Failover Cluster

Enable the fail over  future on each participating  node  from the add futures section of  server configuration dashboard as shown below

• The next step is to create fail-over cluster   which can be done by  going to fail over cluster manager from the start administrative tools  and when on the cluster manager click create new cluster as shown below

• In the next step   add the participating nodes  by browsing through AD directory as shown below

• After joining the nodes to cluster, enable always on from the sql server configuration manager and restart the sql server service as shown below

Creating the AlwaysonGroup

• Go to any one  sql server instance which you want make  as a primary replica  by going to sql server management studio  and right clicking always on management  and new availability group  as shown below

• Click next as shown below

• Click next  give a name

• In the next step select a data base  to be part of availability group , as i have already setup the always on group previously it shows  as already part of the availability group but you can select any  user database  which is in full recovery mode 

In the next step u have the option to  configure the  mode of the availability group as in mirroring you can choose high performance asynchronous mode or highly available synchronous mode,in the same section u need to add the secondary replicas ,  and also the option to configure  how  the secondary replicas to be configured you got three options read intent only ,yes, no,read intent only allows for read only connections and if you select yes it allows all connections if you set no its doesn't allow any connections  as shown below

• In the next step you can configure whether you want the databases  should be taken backup and applied automatically at the secondary replicas or whether you want to join to the already existing databases  which already have been backed up and restored manually in a recovering state i have chosen the second option to join as my secondary  instances are on different locations from the primary instance   as shown below

• Click next to perform the validations as shown below

Click next to verify the configuration choices you made as shown below and later click next to finish