SQL Server Magazine

SQL Server 2005 introduces enhancements and features that affect almost every aspect of the product, as well as completely new infrastructures and platforms. For the DBA in particular, there are many exciting features to learn about. When I started writing this article, I created a list of features that are either significantly enhanced or completely new, and before long I had dozens of items to cover. But if I cover all the items on my list, you won't be able to see the forest for the trees. So I decided that for DBAs, this article needs to present two main areas of information: which new features in SQL Server 2005 make it worthwhile for your organization to upgrade, and which features will immediately affect you in terms of managing and maintaining the new product. I don't cover enhancements in tools. You can learn more about that topic in Kalen Delaney and Ron Talmage's "SQL Server 2005 Management Tools" (August 2005, InstantDoc ID 46798).

Database Mirroring
Database mirroring is one of the new features in SQL Server 2005 that I find the most exciting for high availability. In simple terms, database mirroring lets you maintain a standby server that provides data redundancy. That is, SQL Server maintains a mirrored copy of the principal database on another server and can automatically fail over to the mirror if the principal server fails.

In SQL Server 2000 Enterprise and Developer editions, log shipping lets you maintain a standby server. Log shipping was widely adopted because it provides an affordable standby server solution that's fairly easy to set up and doesn't require specialized hardware. However, log shipping is a hybrid of several features that weren't originally designed for the sole purpose of maintaining a standby server. Essentially, log shipping backs up the transaction log in the source server, copies the backup files, and restores them to the target server. SQL Agent jobs maintain the process. Log shipping has several limitations:

Database mirroring addresses these limitations by introducing a native solution for maintaining a standby server.

Three servers play major roles in database mirroring. The principal holds the primary database. The mirror holds the copy. The witness (which is an optional server) allows support for auto failover. A new client feature, auto-client redirect, appears to support the client functionality in a failover event. No specialized hardware is necessary, and setting up a mirroring session is amazingly simple. Database mirroring transfers transaction log records over a mirroring session rather than copies backup files. The mirror copy of the database is in a non-recovered mode, meaning it's not accessible for read purposes. Figure 1 illustrates the database mirroring server configuration. An option exists to make a snapshot of the mirror available for read purposes. I discuss database snapshots next.

You can set up a mirroring session to work in synchronous or asynchronous mode. In synchronous mode, changes to the principal database are committed only after the mirror receives them. In this mode, latencies are based on the network speed and number of changes. Asynchronous mode is the faster mode because changes sent to the principal can be committed immediately. However, in asynchronous mode there's no guarantee that the mirror received all changes that were committed in the principal. (For more information about database mirroring, refer to Ron Talmage's Microsoft article, "Database Mirroring in SQL Server 2005," available at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx.)

Author's Note: After evaluating the early beta test coverage, Microsoft learned that the database mirroring feature has been tested and deployed by fewer customers than is necessary to build full confidence in such a crucial database-availability feature. Therefore, Microsoft decided that database mirroring needs more time in the hands of customers before it's made generally available for production use. The feature is complete, has passed extensive internal testing, and Microsoft is committed to making it generally available in the first half of 2006. The SQL Server 2005 release to manufacturing (RTM) will have database mirroring turned off by default, but the feature will be available for evaluation under a trace flag. Microsoft will continue to field-test the feature with customers and will release it for general use as soon as it is ready.

Database Snapshots
Database snapshots is a new feature in SQL Server 2005 that lets you provide new functionality and higher availability for your databases. A database snapshot is an object that looks like a read-only copy of a database at a particular moment. With a database snapshot, you create a new database that's a snapshot of an existing database, providing the snapshot a name and a corresponding data file for each existing data file in the source database. Because snapshots are read-only and don't accept changes, no transaction log files are needed. A database snapshot is extremely space-efficient. The data files in the snapshot are initially created completely empty. SQL Server uses a trick to maintain the snapshot as a point-in-time picture of the source database: Upon the first change that a page in the source database incurs after a snapshot is created, the page is copied to the corresponding snapshot file before the change is applied. Further changes to the same page don't result in copying because the snapshot maintains the state of the page before any changes were made to it.

When you query a snapshot, SQL Server reads from the snapshot files pages that were modified and reads from the source database pages that weren't modified. The largest a snapshot can become is the size of the source database's data files, and only if each and every page of the source database is changed after the snapshot is created.

You can create multiple snapshots of the same database at different points in time. For example, you might want to create a snapshot periodically (e.g., every day or every hour). In cases in which data is accidentally deleted, you can recover the data from the most recent snapshot. You can also create a snapshot before applying a very intensive process that has the potential to damage the data. For example, the process might be too big to maintain in a single transaction. If the process fails, you can revert the database back to its snapshot's state as long as only one snapshot of the database was created. You can also use snapshots for reporting purposes for applications that logically need to access all data at a certain point in time.

Finally, you can create a database snapshot on the mirrored database, and in this way make the mirror available for reading purposes. Doing so lets you reduce the query load from the principal database.

Although the benefits to working with database snapshots are obvious, keep in mind that whenever a page is modified for the first time after a snapshot is created, its image before the change is copied to the snapshot. The copying process has a cost and will slow down some of the modifications against the source database.

   Prev. page   [1] 2 3     next page