SQL Server Redundancy for SMBs

,

 

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.

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)