Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012
On the Select Databases page of the New Availability Group Wizard, select all the databases that you wish to be part of the availability group (all databases in this availability group will failover together as group or unit). As noted before, as with database mirroring you are not required to have only one database here in case of an availability group. Remeber also that a database can be part of one and only one availability group at any time:
Please note in the above image, you can only include databases which meet certain prerequisites for including them in the availability group, for example that database should be in full recovery mode and you should have taken at least one full or differential backup of the database. If you click on the link for the database which does not meet prerequisite, a pop as shown below will appear with details:
On the Select Replicas page, under the Replicas tab of the New Availability Group Wizard, notice that the current instance name has already been included as the primary replica (read-write databases) and next you need to add secondary replicas (you need to have at least one or up to four secondary replicas). Here, can also specify the automatic failover setting/instance or synchronous commit mode. If you set a secondary replica for automatic failover, synchronous commit mode is required and will be set automatically. Next, can specify the readability setting for the secondary replica:
On the Select Replicas page under Endpoint tab, by default endpoints (database mirroring endpoints to connect to each other servers/instances) will be created for all the servers participating in the availability group. You can customize this for specific requirements or leave the default values as is:
On the Select Replicas page (under Backup preferences tab) of the New Availability Group Wizard, you need to specify the preferred location of automated backup for the databases participating in the availability group:
- · Prefer Secondary – Takes an automatic backup on the secondary replica if one is available alterantively it takes a backup on the primary replica
- · Secondary Only – Takes a backup of the database belonging to this availability group which should occur on current secondary replica only. Selecting this means you are basically offloading the backup operation from the primary to the secondary replica.
- · Primary – Backups should always be taken on the current/active primary replica irrespective of the number of secondary replicas available.
- · Any Replica – Backup of database belonging to the availability group can happen on any replica as per the backup priority specified. You can also exclude one or more replicas from the backup operation:
On the Select Replicas page and under Listener tab of the New Availability Group Wizard, you need to specify whether you want to create an availability group listener with the availability group creation itself or whether you wish to create it later on. In either case, note that you can have only one availability group listener for an availability group.
So what is an
availability group listener is and what does it do?
An availability group listener provides a connection point to connect to an availability group from client applications. An availability group listener directs incoming connections/requests to the current primary replica of the availability group for read-write operations or to a readable secondary replica for read-only operation. Therefore, the client does not need to know the physical name of the instance it intends to connect to, but instead it will be using the availability group listener and the availability group listener will route requests to the appropriate replica/instance.