With SQL
Server 2012 AlwaysOn is centralized and better method to provide HADR (High Availability Disaster Recovery) compared
to older versions. AlwaysOn added with read-only secondary replicas provide
handy alternatives while defining database, data warehouse and application
architectures along with hardware sizing considerations. It perfectly suits the
middle and small scale business by providing HADR and significant load
balancing without denting costs on hardware(s) & software(s).
As a part of
prerequisite it should have read-only routing enabled and client should use
read-intend connection request. Enabling and setting up read-only routing is
explained here
in detail and T-SQL approach for the same can be found here.
The figure-1
shows some of the scenarios where active secondary provides handy architectural
alternatives along with HADR. These scenarios are discussed below in detail. Resource governor
can be combined wisely with the AlwaysOn availability groups to provide even
more robust and responsive system(s). Connection string received by the
availability group listener is solely responsible for diverting request to
secondary so additional AlwaysOn connection property “ApplicationIntent=ReadOnly”
should be added on the connection string for each request.
Figure-1
Real Time Reporting
This is one of the frequently asked requirements from the users. Most of
the times the reports come from multiple databases because one database is
suffices one single purpose. Using AlwaysOn feature multiple DBs can be
maintained with single availability group reducing the overhead of setup and
maintenance. With a simple change in the connection string i.e. adding AlwaysOn
connection property “ApplicationIntent=ReadOnly”
of the report application the requests will be routed to the secondary
replicas. This will off-load the primary servers resulting fast response time
for Application and Report Users with better resources utilization.
Database Backups
Taking
backups is one of the important and daily tasks. Executing this task in peak
hours can impact the user experience. It becomes even more difficult if the
application is having users around the globe converting every minute to peak
hour. This scenario is detailed my Microsoft in this link.
Data Distribution Replicas for ETL
Analysis is
a critical for any of the business today. Most of the times analysis results in
data warehouses (DW) and ETLs. ETLs most of the times put lot of load to the
main application(s) resulting performance degrade and performance escalations. We
can configure ETL connection strings to use read intend connection resulting
queries execution on one of the secondary replica.
SSAS Cube Processing
SSAS cubes
are active part of most of the enterprise applications. SSAS cube processing is
very resource intensive task. SSAS generates queries at runtime based on the
cube design and fires them on the respective supporting database. We can embed
the AlwaysOn connection property to the SSAS data source and the application
machines are off loaded. Taking a data warehouse scenario where we have several
cubes and user queries as well. The read-only secondary replicas provide handy
and robust method to offload the core data warehouse along with the HADR
capabilities.
Conclusion
AlwaysOn
availability groups provide many handy solutions but the secondary replica routing
is very highly dependent on routing tables and it should be considered with
deep thoughts while defining the solution. Although the feature provides very
fast synchronization of data but in true sense it can never be truly real time;
the time spend to move the logs from one replica to another also depends on the
networking and hardware resources.
To have
better insight of AlwaysOn below links are just a click away.
Enjoy
AlwaysOn …
