SQL Server 2012 - Always On
AlwaysOn Availability Groups are an
alternative to database mirroring. An availability group is a collection of
user databases, termed availability databases, that can fail over together. Unlike
mirroring that is limited to a principal and a mirror database, availability
groups support a set of read-write primary databases and up to four sets of
secondary databases. Availability groups also enable you to configure one or
more sets of secondary databases so that they are accessible for read-only
operations.
Pre-requisites
For production
environments, only SQL Server 2012 Enterprise edition supports AlwaysOn Availability
Groups. When planning the deployment of AlwaysOn Availability Groups, the host server
must meet the following conditions:
Ø Host servers cannot be
domain controllers.
Ø Each host server must be
a participant node in a Windows Server failover cluster. Failover clustering is
supported only on Windows Server 2008 Enterprise and Datacenter editions and
Windows Server 2008 R2.
Ø You must ensure that
appropriate hotfixes are applied to the host server operating system.
Availability Modes
AlwaysOn Availability Groups support similar
modes to database mirroring. The type of availability mode that is appropriate
depends on data loss and transaction latency requirements. You configure
availability modes on a per-availability replica basis. AlwaysOn Availability Groups
support the following availability modes:
Asynchronous-commit
mode: This mode is suitable
when you must place availability replicas at geographically dispersed
locations. When you configure all secondary replicas to use asynchronous-commit
mode, the primary will not wait for secondaries to harden the log (write log
records to disk) and will run with minimum transactionlatency. If you configure
the primary to use asynchronous-commit mode, the transactions for all replicas
will be committed asynchronously independently of which mode you’ve configured
on each secondary replica.
Synchronous-commit mode:
This mode increases transaction latency but
minimizes the chance of data loss in the event of automatic failover. When you
use this mode, each transaction is applied to the secondary replica before
being written to the local log file. The primary verifies that the transaction
has been applied to the secondary before entering a SYNCHRONIZED state.
Failover Modes
Availability groups
fail over at the availability-replica level. Failover involves another instance
becoming the primary replica, with the original primary replica being demoted
to become a secondary replica. AlwaysOn Availability Groups support three forms
of failover:
Automatic failover : This form of failover occurs without
administrator intervention. No data loss occurs during automatic failover.
Automatic failover is supported only if the current primary and at least one
secondary replica are configured with a failover mode set to AUTOMATIC, and at
least one of the secondary replicas set to AUTOMATIC is also synchronized.
Automatic failover can occur only if the primary and replica are in synchronous-commit
mode.
Planned manual failover:
This form of failover is triggered by an
administrator. No data loss occurs during planned manual failover. You perform
this type of failover when you must perform a type of maintenance on a host
instance that requires the instance or the host server to be taken offline or
restarted. Planned manual failover can occur only if at least one of the
secondary replicas is in a SYNCHRONIZED state. You can perform planned manual
failover only if the primary and replica instances are in synchronous-commit
mode.
Forced manual failover: This
form of failover involves the possibility of data loss. Use forced manual
failover when no secondary replica is in the SYNCHRONIZED state or when the
primary replica is unavailable. This type of failover is the only type
supported if asynchronous-commit mode is used on the primary, or if the only
available replica uses asynchronous-commit mode.
Enabling and Disabling AlwaysOn
Using
SQL Server Configuration Manager
1.
Connect to the Windows Server Failover Clustering (WSFC) node that
hosts the SQL Server instance where you want to enable AlwaysOn Availability
Groups.
2.
On the Start menu, point to All Programs,
point to Microsoft SQL Server 2012 , point to Configuration Tools,
and click SQL Server Configuration Manager.
3.
In SQL Server Configuration Manager, click SQL
Server Services, right-click SQL Server (<instance name>),
where <instance name> is the name of a
local server instance for which you want to enable AlwaysOn Availability
Groups, and click Properties.
4.
Select the AlwaysOn High Availability tab.
5.
Verify that Windows failover cluster name field
contains the name of the local failover cluster node. If this field is blank,
this server instance currently does not support AlwaysOn Availability Groups.
Either the local computer is not a cluster node, the WSFC cluster has been shut
down, or this edition of SQL Server 2012 that does not support AlwaysOn
Availability Groups.
6.
Select the Enable AlwaysOn Availability Groups check
box, and click OK.
SQL
Server Configuration Manager saves your change. Then, you must manually restart
the SQL Server service. This enables you to choose a restart time that is best
for your business requirements. When the SQL Server service restarts, AlwaysOn
will be enabled, and the IsHadrEnabled server property will be set to
1.
Using SQL Server PowerShell
Change
directory (cd) to a server instance that you want to enable for AlwaysOn
Availability Groups.
Use
the Enable-SqlAlwaysOn cmdlet to enable AlwaysOn Availability Groups.
Enable-SqlAlwaysOn -Path
SQLSERVER:\SQL\Computer\Instance
Using SQL Server Configuration Manager
1.
Connect to the Windows Server Failover Clustering (WSFC) node that
hosts the SQL Server instance where you want to disable AlwaysOn Availability
Groups.
2.
On the Start menu,
point to All Programs, point to Microsoft
SQL Server 2012 , point to Configuration Tools, and click SQL Server Configuration Manager.
3.
In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want
to disable AlwaysOn Availability Groups, and click Properties.
4.
On the AlwaysOn High Availability tab, deselect the Enable AlwaysOn Availability
Groups check box, and click OK.
SQL Server Configuration Manager saves your change and restarts
the SQL Server service. When the SQL Server service restarts, AlwaysOn will be
disabled, and the IsHadrEnabled server property will be set to 0, to indicate that AlwaysOn
Availability Groups is disabled.
Using SQL Server PowerShell
Change directory (cd) to a currently-enabled server instance that that you want
to disenable for AlwaysOn Availability Groups.
Use the Disable-SqlAlwaysOn cmdlet to enable AlwaysOn Availability Groups.
For example, the following command
disables AlwaysOn Availability Groups on an instance of SQL Server (Computer\Instance). This command
requires restarting the instance, and you will be prompted to confirm this
restart.
Disable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance
In next article we will be discussing on the commands involved in Failover and Failback process..
Stay glued..........