SQL Server Redundancy for SMBs
Introduction
In the past fifteen years, the
face of business has changed dramatically.
With the advent of new enterprise technology solutions, business models now
include data-driven processes and applications that promote faster growth and
foster a better understanding of the business.
This data, in short, provides new insight to the intricate process of
creating and maintaining business relationships and augments traditional
business methodology. The future of such
successful businesses depends on the continued growth and development of these
enterprise solutions, including the relevant data that these solutions rely on.
As a result, business
applications and client requirements demand highly available data to enhance
productivity. More and more, businesses
realize the benefits of utilizing real-time data analysis. By generating on-the-fly data reports,
companies gain an understanding of their current business practices and grasp
any existing execution deficiencies.
Ultimately, companies adapt their business models and make mission
critical decisions to direct their efforts towards more profitable avenues.
Unfortunately, performing these
reporting jobs on database solutions pulls valuable resources from database
clients that are normally reserved for regular business transactions. Database Administrators (DBAs)
are constantly struggling to provide 24 hours a day, 7 days a week database
availability. With the added load
stemming from building these business reports, DBAs
are forced to build strategic infrastructure growth plans that consider larger
loads being placed on their database servers.
One of the common solutions that these DBAs
turn to is Clustering. This particular
option involves upgrading both hardware and software to maintain high
availability in order to balance load and allow for failover environments. Given the management complexities as well as the additional resources required, this approach becomes very expensive as companies continue to grow over time.
Inexpensive solutions do exist to address this problematic issue, one of which is SQL Server Redundancy.
What is SQL Server Redundancy?
SQL Server Redundancy is a process in which a specialized tool replicates data directly from the database to an auxiliary system that is independent from mainline production environments. With an isolated environment, performing complicated reporting tasks on the same data set will not degrade the performance of normal business operations.
Moreover, SQL Server Redundancy allows business information to be gathered quickly and cost-effectively. Typically, production servers tend to be very
robust to handle the increasing load of user transactions on a day-to-day
basis. With an isolated data reporting
system, companies have the option of cutting costs by creating a less-available
environment as determined by business needs.
Furthermore, with a one-to-many capability, businesses can set up
multiple replication environments for a single database, instance or entire
server to help balance resource usage across multiple users who all require
readable access to the same data set.
Additionally, this redundancy can
provide a standby capability, proactively replicating data at a user-defined
interval, and thereby preserving valuable business data.
The process, depending on
implementation, involves entire SQL Server replication, entire SQL instance
replication or even single SQL database replication.
A suitable solution provider of
redundancy, Microsoft SQL Server Log Shipping allows for database replication
with both one-to-one and one-to-many log shipping plans. These plans are created using the Database
Maintenance Plan in SQL Server Enterprise Manager by selecting source databases
and checking the option to “Ship the transaction logs to other SQL Servers”.
However, Microsoft’s approach remains lacking, particularly in monitoring multiple database replications. SQL Server allows three ways to provide reporting information to the user—a text file in a user directory, historical
information in the msdb table of the source server
and historical information in the msdb table of a
remote server. Here, the user can pick
which if any of these mechanisms he would like to use. Unfortunately, they all provide limited
information that is difficult to access and parse.
In one particular scenario, a user wanted to monitor his Microsoft Log Shipping using scripts. Ultimately, to fully manage his replications, he had to write scripts that would parse data out of the text file and send notification e-mails to obtain status information of his Log Shipping plan. This process alone was troublesome and would not always provide adequate notification to deal with failures in a timely manner.
Another user’s approach was much
simpler—he monitored the status of the plan using Enterprise Manager without
using any automated scripts. Rather, he
would view the Plan History periodically to check status. In the event he noticed failures,
particularly LSN out-of-sequence errors, he found that forcing a restart of the
plan by starting with a complete backup was the best solution in resolving Log
Shipping issues.
LSN out-of-sequence errors can be
caused by a variety of reasons, the foremost being network packet loss. Microsoft SQL Server Log Shipping does not provide network resiliency, compression or multithreaded backups, which increases storage costs and backup time. With larger backup times and no compression, it takes longer to write the file to a shared backup folder and also to
transfer the file to the remote standby server.
During these writes and copies, if the network drops even a few packets,
a transactional log file may be lost or corrupted, which pulls the entire Log
Shipping Plan out of sequence. Sometimes
with these corruptions, Log Shipping restore tasks will simply fail without
noting LSN errors. Even still, the issue
is related to out-of-sequence because whenever a log file is unsuccessfully
restored, future log file restores also cannot be restored.
Given the fact that this feature
is already bundled in Microsoft SQL Server 2000 Enterprise Edition, many SMBs will find value in this solution in an effort to increase their Return on Investment of Microsoft’s SQL Server Enterprise software. However, more and more, companies find that other solutions exist to mitigate all the issues associated with Microsoft Log Shipping.
A third-party solution provider,
Sonasoft Corporation implements SQL Server Redundancy and seeks not only to
increase ROI, but also to limit Total Cost of Ownership by automating the
entire redundancy process.
Sonasoft® One-to-Many Standby Solution
Figure 1: Sonasoft’s SonaSafe for SQL Server One-To-Many Standby Scenario. These Standby SQL Servers can also be geographically distinct and connected through different WANs.
As part of SonaSafe
for SQL Server, Sonasoft offers a Standby capability that allows for
one-to-many live replication of a single database, instance or entire SQL
Server database. Its completely automated
solution removes the headaches associated with traditional Microsoft SQL Server
Log Shipping and with complicated SQL scripts that are difficult to
manage. The following features outline
some of the benefits of the Sonasoft approach to solving this important
business requirement.
- Users have the option to specify separate intervals over which to apply logs to each replicated database.
- Only one backup set is maintained to supply data to each of the replicated databases. This reduces storage costs by a factor of the replication standby servers.
- Maintaining multiple replications allows for remote
and local standbys that will survive and maintain high-availability in the
event of natural disaster or network failure.
- Centralized management console that consolidates
monitoring of the processes associated with performing these replication
tasks.
- Read only access to standby databases on SQL Server
Enterprise Edition.
- Replicated databases can exist in both LAN and WAN
environments.
The combination of these features
provides a highly available database solution for Microsoft SQL Server that is
cost-effective, while also ensuring data security by allowing geographical data
redundancy over WANs. Meanwhile, it is
easily manageable and configurable through its web-based User Interface.
In one scenario, a user providing
Application Services uses this product to offer high availability standby
solutions to their clients. She hosts
production environments at her data center as well as at least one standby
server for each client. Additionally,
upon request, she provides another standby server to reside at the client’s
location in order to serve as a reporting or testing server.
In another situation, a user
managing servers in a banking and financial
institution uses SonaSafe for SQL in their clustered
SQL production environment to provide a standby server in a remote location for
reporting purposes.
Summary
As companies grow and data
becomes more and more prevalent, users of all types, either internal or external,
require access to all this information.
When seeking to satisfy all individuals’ resource needs, cost becomes
the limiting factor that often prevents obtaining technology that is vital to
the efficiency of the organization.
Here, we have discussed a simple software solution that limits cost
while maintaining high-availability of data while also creating persistent
standby environments that can assume production roles at a moment’s notice.
About Sonasoft®
Sonasoft Corporation offers its highly acclaimed SonaSafe for SQL Server, SonaSafe for Exchange Server and SonaSafe
for File Server products to build a complete enterprise-level integrated backup
and disaster recovery solution with replication for Microsoft products. The completely automated products solve disk-to-disk backup and recovery issues for Microsoft Exchange, SQL and Windows Servers with its groundbreaking Sonasoft® Point-Click Recovery® solutions. Designed to simplify and eliminate human error in the backup and recovery process, SonaSafe solutions also centralize the management of multiple servers and provide a cost-effective turnkey disaster recovery strategy for companies of all sizes. For more information, please visit http://www.sonasoft.com.
Bilal Ahmed is CTO and VP of Engineering at Sonasoft Corporation and can be reached at bilala@sonasoft.com. Shivan Bindal is a Team Lead for SonaSafe for SQL Server Product at Sonasoft and can be
reached at shivanb@sonasoft.com.
