Table
of Contents
1.
Introduction
2.
AlwaysOn Availability Groups
2.1
Step-by-step instructions to configure AlwaysOn availability group
2.1.1 How to convert this into
AlwaysOn single availability group?
2.1.2 How can we look at the created AlwaysOn availability
groups?
2.1.3 What is the Application connection String
format that connects to primary of our availability group?
2.2 How
to test the Manual failover?
2.3 How
to setup for the readable secondary?
2.4 How
to Enabling backup on Secondary – Active Secondary?
3.
AlwyasOn Failover Cluster Instances
4. What is the
improvement in reduction of planned downtime?
1. Introduction
SQL
Server 2012 introduces new features called AlwaysOn which gives integrated
high-availability and Disaster recovery solution. It provides data access for
all the time and faster performance for less money.
Earlier versions, we have
used Database Mirroring for Database level protection, Clustering for Instance
level protection and Log Shipping for Disaster Recovery. Sometimes depending
upon the business requirement, we use the combination of Database Mirroring for
local high availability and Log-Shipping for Disaster Recovery. AlwaysOn
provides single unified, integrated, flexible and efficient solution for both
High-availability and Disaster Recovery [HADR].
AlwaysOn feature can be
categorized into below mentioned two types.
1.
AlwaysOn Availability Groups [For Database
level Protection].
2.
AlwaysOn Failover Cluster Instances [For
Instance level Protection].
2.
AlwaysOn Availability Groups
It enhances and combines
database mirroring and log shipping capabilities. We can failover multiple databases
as single unit.
It can have maximum of 4
Secondary instances in which two secondary instances can be in synchronous and
one can be in automatic failover pair. We can also setup both asynchronous and
synchronous secondary instances. These secondary instances are active that are
readable (can be used in reporting applications) and backup from secondary
instances are possible.
Failover is not only in
database but also application needs to be reconnected to new primary database
server during failover process. It is achieved through availability group
listener or virtual network names.
We can also find much
improvement in configuration wizard and Dashboard which is used to identify the
state of each secondary instance.
We can also setup built-in
compression, encryption, automatic page repair, automatic and manual failover
process.
All these setup and
configuration can be done through automated power shell solution as well.
2.1
Step-by-step instructions to configure AlwaysOn availability group
Let’s assume that we have
below mentioned 4 different servers in our environment.
- SRV2 and SRV3 for local HA
- SRV3 for Regional HA
- SRV4 for DR
2.1.1
How to convert the above environment into AlwaysOn single availability group?
1.
Let us ensure that all these servers are
part of AlwaysOn availability groups. In order to do this, we need to use
Windows Server Cluster failover Manager.
2.
Go to Cluster
failover Manager. Right Click and select create a cluster. And add all of these servers.
3.
After adding all of these servers, we will
able to find all the added servers listed under Nodes.
4.
Go to SQLServer Configuration Manager.
Right clicks the SQL Server Service and go to properties and we will see a new
tab AlwaysOn High Availalbility and click
on Enable AlwaysOn availability Groups and
restart the Service.
5.
Repeat Step-4 for each one of the SQL
Server Service.
6.
Let’s connect to SRV2 which will be acting as a Primary Server and it has 2
databases (Test1 and Test2). We are going to create availability groups for
both of these databases. In order to do this, go to AlwaysOn High Availability and Right click on Availability Groups and Select New
Availability Group.
7.
Specify the Availability group Name say Group1.
8.
Select the databases (Test1 and Test2).
9.
Then Specify Replicas. [Replica is the term
which is used for specifying all the servers in AlwaysOn]. Click on Add Replica and each Server and click
on Automatic failover and synchronous commit based on the
business requirement.
10. Then
Click on Listener Tab and add
listener where application is connecting to our primary of our availability
group. Click on Create an availability
group listener and specify below things.
Listener DNS Name : Lis1
Port : 1433
Network Mode : DHCP
Subnet : <IP
Address>
11.Then
Click Next. Select Data Synchronization.
This is backup\restore of the databases for each one of the secondary from
primary. Click on Full and then
Next.
12. Then Validation and
Summary and Finish.
AlwaysOn uses data storage
from its own system and it doesn’t require any shared Disk like in failover
clustering.
2.1.2
How can we look at the created AlwaysOn availability groups?
1.
Go to AlwaysOn
High Availability and Availability
Groups and right click on SRV2 and Select show dashboard.
2.1.3
What is the Application connection String format that connects to primary of
our availability group?
Provider=SQLNCLI11.1; Data
Source=Lis1<Listener Name>; Integrated Security=SSPI; Initial
Catalog=Test1<database Name>; Timeout=1
2.2
How to test the Manual failover?
1.
Go to AlwaysOn
High Availability and Availability
Groups and right click on SRV2
and Select failover.
2.
Click next and Select the new primary
replica for this availability group. Let’s select SRV3 as a new replica.
3.
Connect to the new primary replica and
click next.
4.
Failover process begins and application
will try to reconnect to Listener for which, it needs to have retry logic setup
on it.
5.
Now we can find that application has
automatically connected to SRV3 which is a new primary replica after the fail
over process.
2.3
How to setup for the readable secondary?
If we would like to
offload some of the reporting activities to secondary instances, as primary
server is getting busy due to business growth. To setup readable secondary, we
would need to follow below steps.
1.
Go to AlwaysOn
High Availability and Availability
Groups and right click on SRV3 (It
is new primary replica now) and Select properties.
2.
Change to Yes for the Readable
secondary option.
3.
Then use the below query to setup the Read only routing.
-- This Script
sets the routing URL on the two replicas enabled for
-- read-access.
-- It is
required that the routing URL be set on each replica before
-- you create
the list using those replicas.
ALTER
Availability Group [Group1]
MODIFY REPLICA ON
'SRV2'
WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL='TCP://SRV2.Testmachine.com:1433'
)
)
ALTER
Availability Group [Group1]
MODIFY REPLICA ON
'SRV3'
WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL='TCP://SRV3.Testmachine.com:1433'
)
)
ALTER
Availability Group [Group1]
MODIFY REPLICA ON
'SRV4'
WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL='TCP://SRV4.Testmachine.com:1433'
)
)
ALTER
Availability Group [Group1]
MODIFY REPLICA ON
'SRV5'
WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL='TCP://SRV5.Testmachine.com:1433'
)
)
-- Setting
Routing List
-- When the
primary replica is 'SRV3', then it trys connecting to 'SRV2' as -- secondary replica.
-- If 'SRV2' is
down, then it trys connecting to 'SRV4'
ALTER
AVAILABILITy GROUP [Group1]
MODIFY REPLICA ON
'SRV3'
WITH
(
PRIMARY_ROLE
(
-- ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = ('SRV2,SRV4')
)
)
-- When the
primary replica is 'SRV2', then it trys connecting to 'SRV3' as -- secondary replica.
-- If 'SRV3' is
down, then it trys connecting to 'SRV4'
ALTER
AVAILABILITy GROUP [Group1]
MODIFY REPLICA ON
'SRV2'
WITH
(
PRIMARY_ROLE
(
-- ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = ('SRV3,SRV4')
)
)
4. Then
include the new parameter [Application Intent] in the application connectivity
string to connect to read-only secondary replica.
Provider=SQLNCLI11.1;
Data Source=Lis1<Listener Name>; Integrated Security=SSPI; Initial
Catalog=Test1<database Name>; Timeout=1; Application
Intent = ReadOnly;
5. Now
you will see that reporting application will automatically reconnect to SRV2
which is secondary replica by considering SRV3 as primary replica.
2.4 How
to Enabling backup on Secondary – Active Secondary?
1. Backup
can be taken from primary and secondary replicas.
2. We
can perform copy-only full back up or log backup from the secondary replicas.
3. Log
backups which are taken on all replicas will form a single log chain. So it is
recommended to keep the backups on single location.
4. It
is recommended to use Database Recovery
Adviser to make database restore process as simple.
3. AlwyasOn Failover Cluster Instances
It
is an enhancement for current SQL Server failover clustering.
Major
enhancements are listed below.
1. Faster
instance failover.
2. Native
support for multisite clustering.
AlwaysOn failover cluster instance
work across two subnets. And it is referencing primary using the virtual
network name. If failover happens from one subnet to another, application does
not require doing any changes. We can reference primary irrespective of the
subnet by using the virtual network name.
3. It
supports for SMB (It is a Consolidation of 26 drive letters).
4. It
also supports to have tempdb on our local drive. So no need to have expensive
drives.
5. Flexible
failover policy:
It
provides administrators control over the conditions when an automatic failover
should be initiated.
In
earlier versions of SQL Server failover cluster, it identifies the alive state
of SQL Server by attacking through some SQL server calls (DLL). For some
reason, if SQL Server is under more pressure or memory leaks, the server may
not be able to answer back to this call. So cluster might assume that Service
is down and will initiate the failover process. This can cause false failover
at rare situation.
It
is improved in SQL Server 2012 by executing sp_server_diagnostics which is always running in SQL Server which
sends SQL Server health information (unresolvable deadlocks, memory issue etc.)
to the cluster. As a customer\admin, we can setup the process for what error
situation, it should raise the failover.
So
Cluster compares each time our defined settings with the values which comes
from SQL Server. If it matches then it raises failover.
All
these diagnostics information is saved in diagnostics log. So we can decide for
which condition, failover should occur.
4.
What is the
improvement in reduction of planned downtime?
1. It supports for Windows Server core.
--
Reduce OS Patching by as much as 50-60%.
2. It supports for rolling upgrade and
patching of SQL Server for both Availability Groups and Failover Cluster
Instance.
3. New Online operations supported for LOB
Index and Adding of Column with default.
Awesome post Ranjith..
ReplyDelete