Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Redundancy for SMBs

By Bilal Ahmed,

 

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.

Total article views: 5658 | Views in the last 30 days: 4
 
Related Articles
BLOG

SQL Server 2008 Replication: High Availability Solution Part One – by Abi Chapagai

Introduction Replication is the process of copying data between two databases on the same server or...

FORUM

Implementing DR solution using Merge Replication

Implementing DR solution using Merge Replication

FORUM

Common table in two databases/servers - Replication

Common table in two databases/servers - Replication

FORUM

Linked Server "provider" issues.

Linked Server "provider" issues.

FORUM

Replication of replicated database

Replication of replicated database to a distant server

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones