architecture that's been widely adopted prior to SQL Server 2012 is Failover
Cluster Instances + Database Mirroring, which leverages FCI for local high
availability and DBM for disaster recovery(remote DR if the mirrored databases
are located from a remote distance).
Besides the fact
that database mirroring will be removed from the future version of SQL Server,
the FCI + DBM architecture has several obvious limitations:
- Only one mirror of the
- Only one active database at a
time (can utilize database snapshot for offloading reporting to another
server, but it won't be real-time and requires extra maintenance)
Let's take a deep
dive in a similar solution provided by the SQL Server 2012 new feature Always
On Availability Group.
Instead of the database mirroring,
availability groups (AG) is used for (remote) disaster recovery. It is referred
as an FCI+AG architecture in our later introduction.
FCI+AG solution require two or more nodes under the same WSFC plus at least one
shared storage disk to form a failover instance. If a server level failure or
even as small as a network glitch occurs, the other node will automatically
take over so that the users can experience the minimal downtime without the
data loss. There would also be no data synchronization overhead between two
nodes since they are sharing the same storage for files and logs.
Addition to using a
FCI for local high availability, Always On feature supports up to 4 secondary
replicas to perform disaster recovery as well as read-only replicas for BI
reporting usage. In our later
illustration, another FCI will be set up as a read-only secondary replica, but
keep in mind that you can always use stand-alone instances instead of FCIs for
Availability Groups. I am doing so simply because I am using hyper-v virtual
environment and the cost is my least consideration.
I strongly recommend
you try setting up your own AlwaysOn Availability Groups for the learning
purpose, as it isn't resource
intensive(8G RAM and 200G disk would be more than enough) and you can acquire
all the necessary products from MSDN subscription downloads (please contact ITS
if you do not already have a MSDN subscription key).
- 1: Windows Server 2012 virtual machine for domain controller, DNS server , storage server (I combine 3 in 1 for
the simplicity's sake)
- Two same-size-storage virtually disks
that are connected to DC server but consumed by two FCIs as data disks
- One witness disks (referred as
quorum disk) since we have even number of nodes so we need an extra
witnesses to vote
- 4 Windows Server 2012 virtual machines to act like four nodes to form two FCIs(1 primary 1 secondary)
The creation and
configuration steps are quite straight forward. I will list all the necessary
steps and highlight some noticeable point for comments.
- Set up domain controller, DNS
server and storage server. Create
a domain and two virtual SCSI disks, then expose them to the four nodes.
- Set up four node servers and
make them all join the domain that's been newly created.
- Create the WSFC with the two
primary nodes and connect to the virtual storage disk 1.
- Add the quorum disk to the
WSFC and change the Quorum Configuration to Node and Disk Majority.
- Install the SQL Server
Failover Cluster Instance on the two primary nodes. Please note that you
might need to manually install Net3.5 under Windows Server 2012 using PowerShell -dism to enable Net3.5 before
SQL Cluster installation.
- Join the two secondary nodes
to the existing WSFC and install the SQL Server Failover Cluster Instance
on the two secondary nodes.
- Perform the initial backup
and recovery for the databases (both full backup and log backup) in AG.
Please note that WITH MOVE must be specified if different locations are
used at secondary replicas.
- Enable Always On Availability
Group on both FCIs and create an Availability Group by joining two FCIs.
Please note that there is no overlap possible owner for all for nodes,
otherwise you will see the 'overlap possible owner' error in later steps.
In my case, I have to manually de-select node HA1 and node HA2 for SQL
- Set the Readable Secondary to
True if you want to offload the reporting burden to the secondary
- Validation. You should test
the data synchronization, transaction performance, failover ability