An Enterprise-Class Plan for Securing Microsoft
SQL Server Databases
If you are running Microsoft SQL Server databases to support critical enterprise applications, you are part of a growing trend. The cost/performance benefits of 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 problems that come with administering production databases. As databases grow in size and the number of databases in production systems increases, protecting those databases adequately and efficiently becomes a significant challenge.
The native backup utilities distributed with Microsoft SQL Server are adequate for development systems or small production databases. If you are not running large databases in production, that might be all you need. But once you start using SQL Server databases in heavily used production environments with high volumes of data and transactions, the task of managing and maintaining those databases suddenly becomes more complex.
Consider the range of problems that a DBA handling several large production databases might need to handle:
Clearly, you need a better plan for securing database data one that accounts for the 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 SQL-BackTrack for Microsoft SQL Server product from BMC Software helps you implement a comprehensive plan, securing your vital data against any number of potential problems.
- A batch job destroys a single table; you need to recover that table from the backup. With the native utilities, your only option is to restore the entire database. This can take a long time for a large database, stretching out costly downtime.
- You need to move or manipulate database objects 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 production system can be very labor-intensive.
- A disk problem has corrupted the master database. You need to recover quickly, but first you have to determine the existing master database configuration before the outage.
- You have added three new production databases without adding DBAs. Your most senior DBA wants to go on vacation next week and you are worried about coverage.
Taking Steps Now to Secure Your Data
If you are running Microsoft SQL Server in a production environment, run through the following steps to be sure that your databases are adequately protected.
Step 1: Back Up Everything
This seems obvious, but it is easy to lose sight of everything to back up.
- Physical backups are necessary for disaster recovery; you need to perform regular physical backups of the entire server.
- Transaction log backups are critical to the ongoing operation of the database, as well as its recoverability. Set up a plan for backing up and tracking those transaction logs.
- Logical backups provide another level of security; logical backups write the logical description of each object in the database. Objects include tables, triggers, stored procedures, users, etc. If you have a logical backup of an object, you can recover it to another database, move things easily from test to production or recover just a single object. The SQL-BackTrack for Microsoft SQL Server product provides logical backup and recovery capabilities.
- You need to record certain information about the master database to be able to recover quickly and appropriately if something happens to it. SQL-BackTrack automatically tracks this information in its master database backups; without SQL-BackTrack, you need to track this information by hand.
- If you maintain online records of your backups, you must back these up too.
Step 2: Simplify as Much as Possible
Support your DBAs by simplifying and automating regular backup procedures as much as 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 solution has the following characteristics:
The use of a graphical interface provides easy cross-training for DBAs, helping you manage growing numbers of servers with existing staff.
- A graphical user interface for configuring backups easily
- Automated physical, transaction log and logical backups
- A graphical, easy-to-use interface for running ad hoc backups
Step 3: Centralize the Backup Administration
One way to work more efficiently is to centralize database administration as much as possible. A good backup and recovery solution allows you to manage and track backups and recoveries for multiple servers from a single location.
Step 4: Perform Fast Backups
As databases grow in size, backup performance becomes critical. A good backup and recovery solution should improve backup performance by:
- Performing "hot" backups, i.e., 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
- Sending a backup to multiple physical devices (dump striping)
- Compressing backups to reduce network impact and overall backup time
Step 5: Simplify Recoveries
Recoveries tend to happen during times of crisis. Therefore, recovery procedures need to be as simple as possible. A good recovery solution:
- Automatically finds and restores data appropriately, not relying on hand-maintained backup records
- Will not fail if the wrong tape is mounted; instead, it prompts for the correct tape
- Applies full, differential, and transaction log backups in the correct order on recovery
- Automatically applies transaction logs for up-to-the-minute recoverability
Step 6: Shorten Recovery Time
Any downtime is too long. A good backup and recovery solution needs to speed recovery. Look for:
- Automated tracking and restoration of data from backup media
- The ability to recover a single table or database object from a logical 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 be sure that your backup media is readable and you have what is 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. Too often, this is information that DBAs do not track carefully.
A comprehensive backup and recovery solution should:
- Back up critical information about the master database on a regular basis
- Guide you through re-creating the master database using configurations and values that existed before the outage
Evaluating Your Coverage
Most backup and recovery solutions cannot meet all of these requirements. Microsoft's native utilities, while useful, do not go far enough. Solutions from storage management vendors manage some of the requirements but do not extend the native capabilities except in device support and management. None of them, for example, provide a robust logical backup and recovery solution for complete coverage.
SQL-BackTrack for Microsoft SQL Server does meet these requirements and more, enabling you to create truly reliable, manageable backup and recovery procedures.
SQL-BackTrack for Microsoft SQL Server: Comprehensive, Expert Backup and Recovery
SQL-BackTrack for Microsoft SQL Server provides comprehensive backup and recovery support for large, production SQL Server databases.
As part of the extensive SQL-BackTrack family of database-specific backup and recovery products, the SQL-BackTrack product provides unique functionality and database expertise. It extends the backup and recovery capabilities of Microsoft SQL Server for a truly enterprise-class solution.
SQL-BackTrack provides SQL Server DBAs with:
The following sections describe these extensions in more detail.
- Extended flexibility and functionality
- Improved management and security
- High-performance backup and recovery
Extending Backup Functionality and Flexibility
Functionality and flexibility features:
SQL-BackTrack for Microsoft SQL Server provides a number of unique capabilities, unavailable without the use of this product. Many of these are implemented through the product's unique logical backup and recovery capabilities.
- Data migration
- Long-term data archival
- Object-level backup and recovery
A logical backup is much more than a bcp of table data; it extracts the content and structure of database objects, including their interdependencies. You can use logical backups to migrate data between different database servers. This provides unparalleled safety and security in an environment with multiple database servers in-house.
Logical backup and recovery operations provide a number of new capabilities for the DBA:
- Object-level recovery: If you need to recover only a specific table or database object, you can do so, in most cases, while the database is still online and available. SQL-BackTrack can extract objects from either physical or logical backups.
- Object-level backups: SQL-BackTrack provides robust archival capabilities. You can back up individual objects, including triggers, stored procedures and tables. These logical database objects can be reloaded to another database or to another SQL Server. This provides an easy way to move and manipulate database objects or copy and resize databases.
Improving Backup and Recovery Performance
Backup performance is always important. Recovery performance is critical. SQL-BackTrack uses Microsoft SQL Server's native capabilities as a basis for its backups and recoveries but adds a number of features to improve overall performance:
- Differential backups
- Backup compression
- Dump striping
- Object-level recovery
- Differential backups only write data changed since the last full backup. This reduces the amount of data written and, in most cases, the overall backup time.
- SQL-BackTrack improves backup and recovery performance by writing larger block sizes to backup media and compressing data before sending it to the backup device.
- SQL-BackTrack supports "striping" backups to multiple devices. With backup striping, SQL-BackTrack writes portions of a single backup to multiple tape devices in parallel. By keeping multiple tape devices running at their capacity, this technique increases the overall backup throughput rate, which is critical for very large databases.
- By enabling object-level recovery, SQL-BackTrack speeds recovery if only a single table or object is required. The database itself can remain online and available while SQL-BackTrack recovers a user table or object.
Improving Manageability and Security
Part of ensuring the safety of the database lies in simplifying and automating the administrative processes as much as possible. For ultimate security, the safety of the database should not depend on any one individual's expertise. Database administration should fit into an overall enterprise management structure.
- Automated, unattended backups
- Centralized backup management through a graphical console
- Guided master database recovery
- Backup encryption
- Dry-run recovery operations
- Integrated security
SQL-BackTrack does much to secure the database by securing the backup and recovery processes.
- SQL-BackTrack supports true automated, unattended backup operations. Its graphical interface and simplified recovery make the DBA's job considerably easier. For example, SQL-BackTrack automatically generates appropriate backup scripts to simplify backup automation.
- The SQL-BackTrack graphical console provides centralized administration for a number of SQL Server systems. Using the console, a database administrator 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 console makes it simpler to manage a number of database servers.
- SQL-BackTrack provides guided recovery if you need to restore the master database. SQL-BackTrack records critical information in the master database and generates an appropriate recovery procedure based on that information. You can use this procedure to re-create a master database and restore it to the appropriate state. This reduces the chance of errors and generally speeds the recovery process when the master database itself is damaged. No other product, even SQL Server itself, provides this kind of expertise or guidance for master database recovery.
- Users may log in with standard, mixed or integrated security modes. Users can also migrate databases from one security mode to another. With integrated security, there is no need to log in. Just connect to a host with integrated security and you're ready to do business.
- SQL-BackTrack provides a backup encryption option for sensitive data. No one can recover an encrypted backup without the backup encryption key. This provides an extra level of security for sensitive data.
- Dry-run recovery options let you test a recovery procedure without actually writing data. This validates that the media is readable and that you have the necessary data for recovery.
If you are using Microsoft SQL Server to host critical production applications, you need to be sure you have implemented a robust, production backup and recovery environment for those servers. SQL-Backtrack for Microsoft SQL Server helps you do just that, providing the performance, manageability and flexibility to provide enterprise-class coverage for your critical systems.