Contents
Introduction...1
Taking Steps Now to Secure Your Data ...2
Step 1: Back Up Everything ...2
Step 2: Simplify as Much as Possible ...3
Step 3: Centralize the Backup Administration...3
Step 4: Perform Fast Backups ...3
Step 5: Simplify Recoveries ...3
Step 6: Shorten Recovery Time ...4
Step 7: Test Recovery Procedures ...4
Step 8: Test the Master Recovery Procedure ...4
Backup and Recovery Solutions ...4
PATROL Recovery for Microsoft SQL Server: Comprehensive, Expert Backup and
Recovery ...5
Extending Backup Functionality and Flexibility ...5
Improving Backup and Recovery Performance...6
Improving Manageability and Security ...7
Page 1
Introduction
If you are running Microsoft SQL Server databases to support critical enterprise applica-tions, you are part of a growing trend. The low cost and performance benefits of using Microsoft SQL Server on the Microsoft Windows NT and Windows 2000 platforms have fueled the growth of SQL Server as a platform for enterprise-class applications.
However, an easy-to-use and cost-effective platform does not ultimately alleviate the prob-lems that come with administering production databases. As databases grow in size and the number of databases in production systems increases, protecting those databases ade-quately and efficiently becomes a significant challenge.
The native backup utilities distributed with Microsoft SQL Server are usually adequate for development systems or small production databases. However, if you start using SQL Server databases in heavily used production environments that have high volumes of data and transactions, managing and maintaining those databases suddenly becomes more complex.
Consider the range of problems that a DBA handling several large production databases might encounter:
• A batch job destroys a single table. That table needs to be recovered from a backup. With the native utilities, the only option is to restore the entire database. This can take a long time for a large database, stretching out costly downtime.
• Database objects need to be moved or manipulated between servers. For example, some stored procedures and triggers on the test system are ready to be rolled into production, as is a new table. If a table has a number of dependencies, re-creating it on the produc-tion system can be very labor-intensive.
• A disk problem has corrupted the master database and it must be recovered quickly. The master database configuration that existed before the outage must first be determined. • Three new production databases have been added but no additional DBAs have been
hired. The senior DBA will be on vacation next week, so coverage will be limited.
Clearly, a better plan is needed for securing database data—one that accounts for a DBA’s expertise and procedures as well as the data itself. This paper describes the requirements of such a plan. It also describes how the PATROL®
Page 2
Taking Steps Now to Secure Your Data
If you are running Microsoft SQL Server in a production environment, you can verify that your databases are adequately protected by completing the steps in this section.
Step 1: Back Up Everything
The types of backups that you can perform and the data that you should back up are as fol-lows:
• Physical backups are necessary for disaster recovery; you need to perform regular phys-ical backups of the entire server.
• File and filegroup backups are physical backups of specific SQL Server files and file-groups. You may want to perform a file or filegroup backup if you want to recover a file or filegroup rather than an entire database.
• Logical backups copy the data in a database to a binary file but do not record the origi-nal location of the data. If you perform a logical backup of an object, you can recover it to another database, move things easily from test to production, and recover a single object. Objects include tables, triggers, stored procedures, users, and so on. The PATROL Recovery for Microsoft SQL Server product provides logical backup and recovery capabilities.
• Object-level backups are logical backups of specific database objects. You may want to perform an object-level backup if you want to recover an object rather than an entire database.
• Transaction log backups are backups of the file or files to which changes made to a database are recorded. These files (called transaction logs) record the changes to the data since the last full or differential backup. Transaction log backups are critical to the ongoing operation and recoverability of a database. You should create a plan for back-ing up and trackback-ing transaction logs.
• An effective strategy for systematic backups of the master database is critical. The mas-ter database is the foundation from which other databases in the system derive startup information and knowledge. A corrupted master database would render the SQL Server instance unusable. When you back up your master database, you should also back up the model, msdb, and distribution databases that are associated with it. A full, physical backup is required to back up the master database.
Page 3
Step 2: Simplify as Much as Possible
You can support your DBAs by automating regular backup procedures and simplifying recoveries to the greatest extent possible. Because people inevitably make mistakes, the less you rely on any one individual’s record-keeping and detail, the more secure your data.
A comprehensive backup and recovery solution has the following characteristics: • easy-to-use graphical user interface for performing backups and recoveries • single enterprise console for all relational databases
• automated, wizard-guided backups and recoveries
• Web interface that allows you to perform backups and recoveries
Step 3: Centralize the Backup Administration
One way to work more efficiently is to centralize database administration as much as pos-sible. A good backup and recovery solution allows you to:
• manage and track backups and recoveries for multiple servers from a single location • back up and recover data from Oracle, DB2®
UDB, Microsoft Exchange Server, and Microsoft SQL Server across your enterprise from a single console
• store all backup and recovery information in one location • have all managed hosts available from one console
Step 4: Perform Fast Backups
As databases increase in size, backup performance becomes critical. A good backup solu-tion should improve backup performance by:
• performing “hot” backups, that is, backups taken while the server is online and in use • writing only data that has changed; differential backups will only back up data that has
changed since the last full backup.
• backing up specific database objects rather than an entire database • sending a backup to multiple physical devices (dump striping)
• compressing backups to reduce network impact and overall backup time
Step 5: Simplify Recoveries
Recoveries are often performed during times of crisis. Therefore, recovery procedures need to be as simple as possible. A good recovery solution:
• provides automated, wizard-guided recoveries
Page 4
• provides recovery options, such as table-level recovery from logical and physical back-ups and object-level recovery
Step 6: Shorten Recovery Time
Any downtime is too long. A good recovery solution needs to speed recovery. Look for: • automated, wizard-guided recoveries
• the ability to recover a single table or database object and its dependencies from a logi-cal backup
• the ability to recover database files or database filegroups from backup files
• the ability to extract tables, views, stored procedures, triggers, user-defined functions, constraints, and their dependencies from a physical backup
Step 7: Test Recovery Procedures
It is not enough to have a good plan in place—you need to make sure it works. A good solution provides dry-run recovery operations, so you can ensure that your backup desti-nation (device or storage manager) is installed and working properly and that you have all resources that are needed for a recovery at any point.
Step 8: Test the Master Recovery Procedure
Recovering from a problem with the master database requires a good understanding of the current master database configuration. This is information that some DBAs do not track carefully.
A comprehensive recovery solution should guide you through:
• backing up the master database and its associated model, msdb, and distribution data-bases on a regular basis
• recovering the master database and its associated databases by creating recovery files from the backup
Backup and Recovery Solutions
Most backup and recovery solutions cannot meet all of the requirements described in steps 1 through 8. Native Microsoft utilities, while useful, have limited capabilities. Solutions from storage management vendors manage some of the requirements but usually do not extend the native capabilities, except for device support and management. Storage man-agement vendors cannot provide a robust logical backup and recovery solution for com-plete coverage. PATROL Recovery for Microsoft SQL Server does meet these
Page 5
PATROL Recovery for Microsoft SQL Server:
Comprehensive, Expert Backup and Recovery
PATROL Recovery for Microsoft SQL Server provides comprehensive backup and recov-ery support for large, production SQL Server databases.
As part of the extensive PATROL Recovery family of database-specific backup and recov-ery products, PATROL Recovrecov-ery for Microsoft SQL Server provides unique functionality and database expertise. It extends the backup and recovery capabilities of Microsoft SQL Server for a truly enterprise-class solution.
PATROL Recovery for Microsoft SQL Server provides SQL Server DBAs with: • extended flexibility and functionality
• improved management and security • high-performance backup and recovery
The following sections describe these benefits in more detail.
Extending Backup Functionality and Flexibility
The PATROL Recovery for Microsoft SQL Server functionality and flexibility features include:
• data migration
• object-level backup and recovery
PATROL Recovery for Microsoft SQL Server provides a number of unique capabilities that are available only with this product. Many of these capabilities are implemented through the product’s unique logical backup and recovery features.
A logical backup extracts the content and structure of database objects, including their interdependencies. You can use logical backups to migrate data between database servers. This feature provides unparalleled safety and security in an environment with multiple in-house database servers.
Logical backup and recovery operations provide a number of new capabilities for DBAs: • object-level backups: PATROL Recovery for Microsoft SQL Server provides robust
archival capabilities. You can back up individual objects, including triggers, stored pro-cedures, and tables. These logical database objects can be reloaded to another database or to another SQL Server. This capability provides an easy way to move and manipulate database objects or copy and resize databases.
Page 6
Recovery for Microsoft SQL Server can extract objects from either SQL Server 2000 physical backups, or SQL Server 7.0, or SQL Server 2000 logical database backups. To recover a database object from a logical backup, you must have a logical backup of the object that you want to recover.
Improving Backup and Recovery Performance
The PATROL Recovery for Microsoft SQL Server performance features include: • backup compression
• dump striping • object-level recovery • point-in-time recovery
PATROL Recovery for Microsoft SQL Server uses the native capabilities of Microsoft SQL Server as a basis for its backups and recoveries but adds a number of features that improve overall performance:
• PATROL Recovery for Microsoft SQL Server improves backup and recovery perfor-mance by writing larger block sizes to backup media and compressing data before send-ing it to the backup device.
• PATROL Recovery for Microsoft SQL Server supports backup striping to multiple devices. PATROL Recovery for Microsoft SQL Server writes portions of a single backup to multiple devices in parallel. By keeping multiple devices running at their capacity, this technique increases the overall backup throughput rate, which is critical to backup performance for very large databases.
• By enabling object-level recovery, PATROL Recovery for Microsoft SQL Server speeds recovery if only a single table or object needs to be recovered. You can extract tables, views, stored procedures, triggers, user-defined functions, constraints, and their depen-dencies from a SQL Server 2000 physical backup. The database itself can remain online and available while PATROL Recovery for Microsoft SQL Server recovers a user table or object. PATROL Recovery for Microsoft SQL Server is able to provide this recovery feature because of a collaborative technical effort between BMC Software and
Microsoft Corporation.
Page 7
Improving Manageability and Security
The PATROL Recovery for Microsoft SQL Server manageability and security features include:
• automated, unattended backups
• centralized backup management through a graphical console • guided master database recovery
• backup encryption
• dry-run recovery operations • integrated security
Part of ensuring the safety of the database lies in simplifying and automating the adminis-trative processes as much as possible. The safety of the database should not depend on any one individual’s expertise. Database administration should fit into an overall enterprise management structure.
PATROL Recovery for Microsoft SQL Server secures the database by managing the backup and recovery processes:
• PATROL Recovery for Microsoft SQL Server provides true automated, unattended
backup operations. Its graphical interface and simplified recovery make the DBA’s job
considerably easier. For example, PATROL Recovery for Microsoft SQL Server pro-vides wizard-guided backups to simplify backup automation.
• The PATROL Recovery for Microsoft SQL Server graphical console can provide cen-tralized administration for a number of SQL Server systems. Using the console, a DBA can configure, run, and monitor backup operations on many database servers. The easy-to-use interface simplifies the process of cross-training DBAs and the centralized con-sole makes it simpler to manage multiple database servers.
• The PATROL Recovery products let you back up and recover data from Oracle, DB2 UDB, Microsoft Exchange Server, and Microsoft SQL Server across your enterprise from a single console. The PATROL Recovery products also let you perform backups and recoveries from a Web interface.
• PATROL Recovery for Microsoft SQL Server provides guided recovery if you need to recover the master database. PATROL Recovery for Microsoft SQL Server backs up the master database and its associated databases and guides you through recovering those databases to restore them to their appropriate state. This capability reduces the chance of errors and generally speeds the recovery process when the master database is dam-aged. No other product, including SQL Server, provides this level of expertise and guid-ance for master database recovery.
Page 8
no need to log in. Just connect to a host with integrated security, and you’re ready to do business.
• PATROL Recovery for Microsoft SQL Server provides a backup encryption option for sensitive data. No one can recover an encrypted backup without the backup encryption key. This feature provides an extra level of security for sensitive data.
• Dry-run recovery options let you test a recovery procedure without actually writing data. You can use dry-run recovery to validate that the media is readable and that you have the necessary data for recovery.
Summary
If you are using Microsoft SQL Server to host critical production applications, you need to ensure that you have implemented a robust backup and recovery environment for produc-tion servers. PATROL Recovery for Microsoft SQL Server helps you do just that, provid-ing the performance, manageability, and flexibility to provide enterprise-class coverage for your critical systems.
About BMC Software
BMC Software is the world’s leading provider of management solutions that ensure the availability, performance, and recovery of business-critical applications. We provide
Application Service Assurance™
For more information visit BMC Software on the Web at www.bmc.com
BMC Software, the BMC Software logos and all other BMC Software product or service names are registered trademarks or trademarks of BMC Soft-ware, Inc. All other registered trademarks or trade-marks belong to their respective companies. © 2001, BMC Software, Inc. All rights reserved.
