Integrating SQL Litespeed in your existing backup infrastructure
Last updated: March 14th '03 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window
About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

This month's 2 click survey:
Is .NET important for a database professional?

SQL Server Articles New

Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW



Featured Book:
Real-World SQL-DMO for SQL Server, by Allan Mitchell and Mark Allison

Click here to read a sample chapter from the book:
SQL Server Security Distilled
 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line

Integrating SQL LiteSpeed in your existing Backup Infrastructure
Written By: Jeremy Kadlec of Edgewood Solutions- March 11, 2003

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 include:

  • 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 delivers:
  • 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 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 Edgewood Solutions