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:

Description: Description: cid:image021.png@01CCCF90.03FACC70

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.

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |