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:

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:

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.