Integrating SQL LiteSpeed in your existing Backup Infrastructure

,

Introduction

Needless to say, backups are one of the most critical aspects of a SQL Server

environment and a Database Administrators (DBA) last line of defense to resolve

a critical problem.  As a DBA, how many times have backups saved you? Even

with numerous advancements with RAID and High Availability solutions, backups

continue to serve an integral component for DBAs. In Greg Robidoux’s recent

article ‘Backup

and Restore – Back to Basics with SQL LiteSpeed’, he wrote about

essential backup and restore best practices to include1:

  • Backing up the SQL Server databases to disk first and then to tape
  • Selecting the ‘Verify Only’ option for the SQL Server backups
  • Encrypting SQL Server backups to prevent data misuse
  • Test and verify backups of all System and User databases

Further, in the article he indicated that SQL LiteSpeed delivers1:

  • Faster and smaller backups due to SQL LiteSpeed compression capabilities
  • Time savings based on the unbeatable backup and restore performance time
  • Cost savings for disk space and tape archives
  • 128 bit encryption capabilities to protect the backups

Since then, scores of companies relying on SQL Server have downloaded the

evaluation edition of SQL LiteSpeed from www.edgewoodsolutions.com/litespeedeval.asp,

tested the product and have quickly become convinced of SQL LiteSpeed’s

capabilities. Recently, the product achieved Microsoft’s touted Gold Certified

Partner status based on the product stability and reliability. All of these

items are good signs that the product has been well received by DBAs as the

trusted SQL Server backup solution for time and storage savings as well as

encryption capabilities.

Common questions that DBAs face as they work to implement SQL LiteSpeed are:

  • How do I integrate SQL LiteSpeed with existing backup solution at my

    company?

  • What are the responsibilities for various IT staff members once SQL

    LiteSpeed is implemented?

  • Should I backup directly to tape or to disk first and then to tape?

As your company is looking to SQL LiteSpeed to enhance your SQL Server backup

solution, it is necessary to determine the most appropriate fashion to integrate

SQL LiteSpeed into your existing environment by addressing the questions above.

Evaluating Existing SQL Server Backup Solutions

At most companies, existing SQL Server backup solutions consist of one of the

following:

 

ID Vendor Product Features Web Site
1 Veritas Backup Exec and Agent for Microsoft SQL Server
  • Enterprise Backup Solution with numerous backup agents that can

    be managed from a single interface

  • Leverages the SQL Server VDI (Virtual Device Interface) for

    backups

www.veritas.com/products/category/

ProductAddOnDetail.jhtml?productId=besql

2 UltraBac SQL Server Agent
  • Enterprise Backup Solution with numerous backup agents that can

    be managed from a single interface

  • Uses SQL Server API’s for backup and restore operations
www.ultrabac.com/products/

40product-descriptions/v7_agents.asp

3 Computer Associates BrightStor (formerly ARCserve)
  • Enterprise Backup Solution with numerous backup agents that can

    be managed from a single interface

  • Supports all native SQL Server Backup and Restore capabilities
www3.ca.com/Solutions/ProductOption.asp?ID=2748
4 Microsoft Windows 2000 Native Backups
  • Subset of the Veritas product that Microsoft used to replace its

    previous product in Windows NT 4.0

  • No true SQL Server capabilities, requires SQL Server native

    backups designed by Microsoft

www.microsoft.com/windows2000/techinfo

/administration/fileandprint/recovery.asp

5 DBAssociates SQL LiteSpeed
  • GUI and command line driven capabilities for T-SQL scripting

    backup and restorations in SQL Server Jobs or existing processes

  • Compression capabilities for smaller backups (up to 15% of the

    original size) and faster backups

  • Encryption of SQL Server database backups on disk and tape
  • Direct disk backups for improved performance and flexibility for

    restorations by DBAs and permanent backups to tape

  • Ability to configure Threads, Priority and Latency for backup

    and restorations

  • Expanded capabilities from the native SQL Server functionality
  • Compliant with Microsoft’s Virtual Device Interface (VDI)

    standard to efficiently and accurate access the data for backup

    operations

  •  Ability to integrate with existing enterprise backup

    solutions

www.sqllitespeed.com

 

At a first glance, it may seem easier or more consistent to simply purchase a

SQL Server backup offering from your existing backup vendor. These products each

have their own strengths and merits.  In most circumstances, they do not

provide the following items for a significant Return on Investment (ROI):

  • Smaller backups requiring less disk and tape space
  • Increased time for other SQL Server Jobs and business processes
  • Configurable performance parameters
  • Encryption for disk and tape backups

In the current business climate, making the case for any purchase requires

tangible benefits and the potential for an ROI in one year to recover the

product cost. With this being said, there is also an excellent case for

returning the management of the SQL Server backups to the DBAs. DBAs are best

suited to manage all of the complexities of SQL Server especially the critical

SQL Server backups.

Integrate SQL LiteSpeed with an Existing Enterprise Backup Solution

When introducing SQL LiteSpeed into the environment it is necessary to

determine how to leverage the strengths of each of these products. SQL LiteSpeed

has proven SQL Server capabilities and integrates cleanly with all of these

backup products and many others. Below outlines implementation options to

integrate SQL LiteSpeed with an existing backup solution:

Option Description Responsibilities Advantages Disadvantages
1 In Veritas, BrightStor, etc  -

Setup the SQL LiteSpeed backups as a Pre-Job with a OSQL statement

that executes a SQL Server Agent Job (sp_start_job)

  • Network Administrators or Data Center Staff – Schedule and

    verify backups via the backup application as well as continue to

    be responsible for tapes and archiving

  • DBAs – Create the necessary script for the Pre-Job in Veritas,

    BrightStor, etc.

  • DBAs - Verify the SQL Server backups on a daily basis
  • Network Administrators or Data Center Staff do not need to learn

    how to use SQL Server in order to leverage SQL LiteSpeed backup

    capabilitiesAll of the backup applications are able to send emails

    based on basic job status

  • SQL LiteSpeed has command line capabilities
  • Leverage the strength of both SQL LiteSpeed and the existing

    backup application

  • Some of the backup applications do not have as sophisticated

    notification as desired

2 Setup SQL Server Agent Jobs to backup

all of the databases and then start the Windows portion of the backup

via a command line commands as the final step

  • Network Administrators and Data Center Staff – Responsible for

    tapes and archiving

  • DBAs – Setup Jobs in SQL Server Agent for all backups with

    notification to all staff on Success and Failure

  • DBAs - Verify the SQL Server backups on a daily basis
  • Setup Operators for notification based on the SQL Server Job

    logic

  • Setup Alerts based on conditions to directly notify the staff
  • Ability to setup Master and Target servers with a stored

    procedure to backup all databases in a dynamic fashion

  • Leverage the strength of both SQL LiteSpeed and the existing

    backup application

  • Possible paradigm shift from Network Administrators or Data

    Center Staff responsible for all backup aspects to shared

    responsibilities with DBA’s

  • All backup applications may not have a command line interface
3 Coordinate with Network Administrators

and Data Center Staff to have all SQL LiteSpeed backups finish before

starting the Veritas, BrightStor, etc backups on each SQL Server

  • Network Administrators and Data Center Staff – Setup Jobs and

    verify completion

  • DBAs – Setup Jobs in SQL Server Agent for all database backups

    with Alerts to the remainder of staff if a problem occurs

  • DBAs - Verify the SQL Server backups on a daily basis
  • Definable division of responsibility between the groups – DBAs

    issue backups to disk and Network Administrators backup those

    files

  • Ability to have SQL LiteSpeed backup to a centralized location

    or individual SQL Servers depending on needs

  • Leverage the strength of both SQL LiteSpeed and the existing

    backup application

  • It is necessary to monitor the backup times on a regular basis

    to adjust the start times

Based on the analysis in the two charts above, leveraging the strengths of both

the enterprise backup solution such as Veritas’s Backup Exec, etc. with

DBAssociates SQL LiteSpeed can be accomplished via a few options which will meet

your business needs. If you are in a large organization with centralized

backups, SQL LiteSpeed can quickly save time and money just like the enterprise

backup solution integrated with your SAN, Filers or centralized tape library for

SQL Server. If you are in a mid-sized organization, SQL LiteSpeed can work well

by saving disk space and tape storage for locally attached tape drives. For

small organizations, SQL LiteSpeed will quickly compliment native Windows

backups with the same functionality as the larger organizations at an affordable

cost.

The bottom line with any decision is to choose the appropriate tool for the

job. In this case, leverage the enterprise backup solution for the various file

systems backups across the organization in conjunction with SQL LiteSpeed for

its high performance and small backups for SQL Server.

Additional Recommendations

Once the appropriate option is chosen to integrate SQL LiteSpeed in your SQL

Server environment it is advantageous to address the following items:

  • Document the backup and restoration process with associated

    responsibilities

  • Ensure the documentation is included in the disaster recovery plan for the

    SQL Servers

  • Record the encryption key in a secure location and change on a frequent

    basis

  • Review the SQL Server Error Log or the MSDB database for backup

    verification on a daily basis via a manual or automated reporting process

  • Test the database restores on a regular basis perhaps weekly or monthly

    depending on the environment as a verification process

  • Automate notification of issues via a product like NetIQ’s AppManager

    for SQL Server which can monitor the error log and events SQL Server raises

  • Backup the databases to disk at a centralized file share or Filer in order

    to simplify the tape backups

  • Do not store the SQL Server database, transaction logs and backups on the

    same physical disk, because if you experience a disk failure it is feasible

    to lose all three items

  • Archive tapes on a monthly or quarterly basis and retain based on industry

    regulations

  • Apply SQL Server and Windows Service Packs, Hot Fixes, Security Patches in

    a timely manner to prevent known vulnerabilities

  • As the DBA is accountable for the SQL Server environment, take time to

    ensure all of your backup needs are properly addressed

Conclusion

When evaluating a SQL Server backup solution it is necessary to ask the

following questions:

  • How many times have SQL Server backups saved the company from a critical

    problem?

  • Is the company experiencing shrinking backup windows and need smaller and

    faster backups, especially for vital SQL Server data?

  • Is the department working to prevent misuse of the SQL Server backups with

    encryption as the data is stored internally and externally?

SQL LiteSpeed has the flexibility to address these issues from the SQL Server

perspective and integrate cleanly with existing enterprise backup solutions from

Veritas, Computer Associates, UltraBac, etc. SQL LiteSpeed uniquely delivers

high performance backups that are compressed to a fraction of the typical

database size in conjunction with 128 bit encryption capabilities. Further, DBAs

are best suited to manage the SQL Server backups because they understand it

best, and with the SQL LiteSpeed flexibility, can provide this value to the

organization.

In summary, as with any SQL Server project, a comprehensive plan, thorough

documentation, and talented staff go a long way to ensure a successful

implementation. With SQL LiteSpeed, the Network Administrators will be happy to

have more time and storage; Management will start to see the value of SQL

LiteSpeed during their next order of tapes or other mass storage; and the DBAs

will now have more time for important SQL maintenance tasks, thereby decreasing

down time and increasing value to the organization.

Additional Information

For additional information about SQL LiteSpeed review - www.sqllitespeed.com.

Resources

  1. Backup and Restore – Back to Basics with SQL LiteSpeed – Greg Robidoux

    – www.edgewoodsolutions.com/resources/BackupAndRestoreLiteSpeed.asp -

    Accessed 02.28.2003

  2. SQL Server 2000 Books Online - Microsoft Corporation – Published January

    2003 - Accessed 02.28.2003

  3. SQL LiteSpeed – Advanced SQL Server Backup – DBAssociates Pty Ltd -

    www.sqllitespeed.com - Accessed 02.28.2003

Published 03.03.2003 – Jeremy Kadlec – Edgewood Solutions. All rights

reserved 2003

About the Author – Jeremy Kadlec

Jeremy Kadlec serves as the Principal Database Engineer at Edgewood

Solutions. He has been building technical solutions for the last five years

primarily focused on SQL Server 6.5, 7.0 and 2000. Mr. Kadlec has set and

implemented a number of SQL Server standards to include Upgrades to SQL Server

2000, EMC Migrations, Unattended Installations, Hardware/Software

Configurations, Disaster Recovery, Database Security, Server Maintenance and

System Performance Tuning. Mr. Kadlec has been able to implement these solutions

as a Project Manager and Lead DBA by standardizing a project management

methodology which can be used across projects and passed on to new team members.

Edgewood Solutions

Edgewood Solutions (www.edgewoodsolutions.com)

is focused on enhancing the Microsoft SQL Server platform and delivering

database solutions to further benefit the investments companies have already

made based on this product platform. We recommend and implement processes that

are often overlooked, but should be part of every database installation. We have

partnered with additional companies we feel enhance the SQL Server platform to

include: Lumigent, DBAssociates and Precise Software Solutions.

In addition, our employees have taken an active role in the SQL Server

community. We are members of PASS and one of our employees is the Vice Chair of

the PASS DBA Special Interest Group. We had the distinct opportunity to provide

two presentations at the PASS Summit in Seattle, ‘Change Management for SQL

Server’ and ‘Successful Project Management for Database Administrators’.

Edgewood Solutions delivers the following SQL Server services:

  • Disaster Recovery Planning
  • Security Strategy
  • Performance Analysis and Tuning
  • Data Modeling and Database Development
  • Database Maintenance
  • System Upgrades
  • Change Management
  • Project Management
  • Customized Training

Learn more about how Edgewood Solutions can deliver ‘databases at their

finest’ at www.edgewoodsolutions.com.

Copyright © 2002-2003 Edgewood Solutions All Rights Reserved. Some names and products listed are the registered trademarks of their

respective owners.

Rate

Share

Share

Rate