FASTER RESTORING
You might need to restore a database as part of disaster recovery, but you might also perform this operation when you move a database to a new drive or copy it to a new machine. Restoring from a backup is also a way to revert a test database to an earlier point in time so you can resume testing from a known earlier state.To restore a database, SQL Server first copies the data and the log records from the backup media, then goes through a process called recovery.
Usually, recovery applies to all files and filegroups and involves two phases. In the first phase, called redo, all transactions marked in the transaction log as committed are verified in the data files and redone, or rolled forward, if necessary. In the second phase, called undo, SQL Server checks to see whether any uncommitted transactions have made changes to data files; those transactions will be undone, or rolled back. In SQL Server versions before SQL Server 2005, the database wasn't available for any use until both the redo and the undo phases were finished.
Fast restore. A new restore feature available only in SQL Server 2005 Enterprise Edition is fast restore. Fast restore makes the database available as soon as the redo phase is finished. The data involved in any transactions that were uncommitted when the backup was made are locked and unavailable in case an undo must be performed, but the rest of the data in the database is fully available.You needn't do anything to enable this feature other than use SQL Server 2005 Enterprise Edition.
Online restore. Another new restore feature available in SQL Server 2005's Enterprise and Developer editions is online restore. Online restore lets you restore damaged files or pages while the rest of the database remains fully available. For a database to be online, its primary filegroup must be online.Therefore, if any files in the primary filegroup are damaged, online restore isn't available. However, some or all of the secondary filegroups can be offline. You can restore the damaged files from backup while the rest of the database is online. Only the file and filegroup being restored are offline. In addition, if your SQL Server 2005 database is running under the Full recovery model, you can also restore one or more individual pages from a file. Only the filegroup containing those pages is offline; the rest of the database is online.
Piecemeal restore. A final restore enhancement is piecemeal restore, which is new in all editions of SQL Server 2005 and enhances the SQL Server 2000 partial restore. A partial restore in either SQL Server 2005 or SQL Server 2000 lets you restore only selected filegroups within a database. After the initial partial restore of the primary filegroup and perhaps some of the secondary filegroups, piecemeal restore lets you restore additional filegroups. Filegroups that aren't restored are marked as offline and aren't accessible until they're restored. In SQL Server 2000, you can perform a partial restore from a full database backup only, but that's no longer a requirement for SQL Server 2005.
DATABASE SNAPSHOTS
One more new SQL Server 2005 feature that many people mention when they discuss high availability is database snapshots. However, by themselves, database snapshots aren't strictly an availability feature. Although it's beyond the scope of this article to go into any detail about database snapshots, be aware that making a snapshot of a database has some availability benefits. First, if you're running tests and want to revert to an earlier point in time,the database is unavailable while restoring from a backup. If you revert to a snapshot instead, the period of unavailability is drastically reduced. Second, you can use snapshots in conjunction with database mirroring to provide a copy of the database for reporting purposes. If you don't use snapshot isolation, locking in the source database can make data unavailable for short periods of time, but a read-only reporting database ameliorates some of that unavailability.
Final Words
The availability of your system, your databases, and your data is crucial to good performance in your environment. SQL Server 2005 has added new features at every level to improve availability and has enhanced many existing features to provide increased availability with more ease than ever before. This discussion of new high-availability features and enhancements to existing features should help you see which features will best support the availability of your systems, databases, and data.
Note: Database mirroring is disabled in the SQL Server 2005 release to manufacturing (RTM) version, reportedly to permit fuller testing. Although you can enable it by using the documented trace flag 1400 as a startup parameter, as of this writing, Microsoft doesn't support mirroring for production use. Most observers expect Microsoft to finish its testing and release database mirroring in the first half of this year. Database mirroring is one of the most eagerly awaited SQL Server high-availability technologies.
End of Article
Prev. page
1
2
3
[4]
next page -->
