Database Availability Enhancements

Although the previous scenarios all address important factors that reduce database availability, there’s probably no single factor that affects database availability and recoverability more than database maintenance. While it’s certainly possible to go for years without a database failure, database maintenance is a daily issue that you can’t avoid. SQL Server 2005 tackles the challenges of this vital area head-on with the addition of two new features: Database Snapshots and early restore access. In the next part of this chapter, we’ll look at each of these new availability and recoverability features in more detail.

Database Snapshot

The new Database Snapshot feature provides a read-only snapshot of a database at a specific point in time. A Database Snapshot is best suited either for creating copies of a database for reporting or for creating a backup copy of the database that you can use to revert a production database back to a prior state. When a Database Snapshot is created, the system makes a metadata copy of the specified database at that particular point in time. Any subsequent changes that are made to the original database are not reflected in the Database Snapshot. Applications connect to a Database Snapshot exactly as if it were another database. A Database Snapshot can be created for any database. Database Snapshots are created as part of the CREATE DATABASE DDL statement. You can see how to create a view on the example AdventureWorks database in the following listing:

CREATE DATABASE AdWSnapShot_080104_0700 ON ( NAME = AdventureWorks_Data,    FILENAME = 'C:\Program Files\Microsoft SQL Server      \MSSQL.1\MSSQL\DATA\AdventureWorks_data_040422_1800.ss') AS SNAPSHOT OF AdventureWorks;

In this example, you can see that a Database Snapshot named AdWSnapShot_080104_0700 is created on the AdventureWorks database. Database Snapshots are created using the AS SNAPSHOT OF clause that you can see in the listing. When you create a Database Snapshot, you must specify all of the data files that are used in the source database. Since the AdventureWorks database consists of a single data file, only the AdventureWorks_Data file must be specified in the CREATE DATABASE statement. You do not specify the log files.

Creating a Database Snapshot is an inexpensive operation server-wise, as the server basically uses metadata in conjunction with recovery to create the viewpoint. It’s important to understand that Database Snapshots are not a complete copy of a database. Instead, creating a Snapshot is a metadata-only operation. A Database Snapshot uses the same data pages as the original database, so it doesn’t require a great deal of additional disk space. Database Snapshots are built using copy-on-write technology where anytime a change is made to one of the source database’s data pages, a copy of that page is saved for the Database Snapshot and then the updated page is written the same way it normally would be. When the Database Snapshot is accessed, it uses the shared data pages until it gets to the changed page, and then it will look at the pages that have been copied rather than the data pages that contain the updated data. In this way, the Database Snapshot needs storage for only those pages that have been changed since the time the Database Snapshot was created. Figure 3-3 illustrates how Database Snapshots work.

Figure 3-3: Database Snapshots

Database Snapshots can be combined with Database Mirroring to create a reporting server based on the data that’s on the mirrored server (Figure 3-4). Normally, the data on the mirrored server is always in recovery mode, so it can’t be accessed by an application. However, you can create a Database Snapshot that’s based on the mirrored database and that Database Snapshot can be accessed in read-only mode for reporting.

Figure 3-4: Database Mirroring and Database Snapshot create a reporting server

Although the database on the mirroring server can’t be directly accessed because it’s in an ongoing state of recovery, that doesn’t affect the Database Snapshot, which accesses a snapshot of the data pages in the mirroring server’s database. Creating a Database Snapshot on the mirroring server enables you to better utilize the processing power of the mirroring server by enabling you to shift your static reporting to that server. One thing to consider when using Database Snapshots on a Database Mirror is that in the event of a failover, the existing Database Snapshots that were created on the mirroring server will remain intact.

It’s important to realize that Database Snapshots are an availability feature, not a failover feature. They provide more ways to access your data, increasing your data’s availability. They are not a failover feature—if the original database is unavailable, the Database Snapshot will also be unavailable.

Early Restore Access

Every time the SQL Server database is started or a database is restored, that database must go through a period of recovery where any transactions that are in the log are applied to the data files. The phase of recovery is often referred to as redo. As soon as the redo portion of the recovery process is complete, then all of the undo operations are performed as any incomplete transactions in the log are rolled back. With SQL Server 2000, the database was not available until all of the redo and undo operations were completed. If a database was brought down while it was active and that database was quite busy, then there could be a somewhat lengthy delay before that database was available again, as you needed to wait until all of the entries in the log were processed.

SQL Server 2005’s new Early Restore Access enables the databases to become available immediately after the redo portion of the recovery process is completed. With SQL Server 2005, when the database is restarted, all of the open transactions that are in the log are redone and then the database is immediately available. The net result is that the database is available much sooner. For the transactions that weren’t committed, SQL Server still holds the locks on the data pages used by those transactions so that the transactions will remain consistent even though the database is in use. SQL Server 2005 then begins the process of undoing these transactions while the database is active. Users can initiate read/write operations to the database during the undo phase of recovery. However, any attempts to access the data that SQL Server has locked during the undo process will experience blocking until the undo process releases the locks on that data. Figure 3-5 illustrates the difference in availability between SQL Server 2000 and SQL Server 2005.

Figure 3-5: Early restore access

The RESTORE action is now very granular and defines the scope of the redo option. You can restore full, partial, or filegroup backups. If you indicate that you want to restore a filegroup, then only that filegroup’s data is added to the roll-forward operation. If you indicate that you want to restore a full backup, then all of the data in the backup set will be used for the redo operation.

Online Index Operations

With prior versions of SQL Server, when an index was being rebuilt you couldn’t perform any update operations on the table until the index rebuild had finished. SQL Server 2005’s new online index operations feature extends SQL Server’s availability by enabling applications to update, insert, and delete rows from the table while the index is being rebuilt. The new online index feature performs this magic by keeping two copies of the index: one that the applications can continue to use and a second temporary index that’s used while the index is being rebuilt. The SQL Server engine maintains both indexes with any changes as the rebuild is being performed. When the rebuild is finished, the old index is dropped and replaced with the new index. Online index rebuild is supported for CREATE INDEX, ALTER INDEX REBUILD, ALTER INDEX DISABLE, DROP INDEX, ALTER TABLE ADD CONSTRAINT, and ALTER TABLE DROP CONSTRAINT statements. Although SQL Server 2005 now supports online index rebuild, it does come at the cost of additional overhead, so you can still choose to rebuild your indexes offline.

The following listing illustrates how the new online indexing feature is used:

CREATE INDEX MyIndex ON Person.Contact(LastName) WITH (ONLINE=ON) 

Here, you can see that the standard CREATE INDEX statement is used to create an index named MyIndex on the LastName column of the table named PersonContact that’s in the AdventureWorks database. The ONLINE=ON clause enables the index to be created online.

Fine Grained Online Repairs

Another new feature that enhances the availability of SQL Server 2005 is the ability to perform fine-grained restores. While not the table-level restores that some people have looked for since that feature was removed after SQL Server 6.5, the new fine-grained restore feature in SQL Server 2005 enables you to restore select filegroups in a database while the remainder of the database continues to be available. For SQL Server 2000, that basic unit of availability is the database. All of the components of the database must be intact before that database is available. With SQL Server 2005, the unit of availability is now the filegroup. SQL Server 2005 enables you to restore a filegroup at a time or even a page or group of pages at a time, and the rest of database can continue to be available as long as the primary filegroup is up.

Damaged Page Tracking

A closely related new feature that ties in with SQL Server 2005’s ability to perform page-level restores is the ability to track damaged pages. Any bad pages that are encountered on a read operation are tracked in a table, and by using the fine-grained restore capability, you can restore on a page-by-page basis while the database remains online. Any transaction that uses data from a damaged page is rolled back. If the bad page happens to turn up during a transaction rollback, then the database will be forced to restart.

Dedicated Administrator Connection

Another new feature found in SQL Server 2005 that helps to provide better availability is the new dedicated administrator’s connection. The dedicated administrator’s connection provides the DBA with access to the server regardless of the server’s current workload. This enables the DBA to access to the server and kill any runaway processes. To start the new SQLCMD tool in dedicated administrative mode, you would enter the following command:

C:\Sqlcmd –A

More information about using the dedicated administrator’s connection appears in Chapter 1.

Hot-Plug Memory

Another new availability feature that’s derived from Windows Server 2003 is Hot-Plug memory. Hot-Plug memory allows you to add RAM while the system is running. As you might expect, this feature requires support from the underlying OS and the hardware platform. In the case of SQL Server 2005, this means that SQL Server must be running on Windows Server 2003 to be able to take advantage of this availability feature. If the platform supports this feature, you can add memory on the fly and SQL Server 2005 will be able to dynamically recognize the additional RAM without requiring a server reboot or any downtime. While Hot-Plug memory allows you to dynamically add RAM on the fly, it doesn’t allow you to remove it.

Improved Dynamic Configuration

All configuration within SQL Server 2005 is now dynamic, including CPU and I/O affinity. With SQL Server 2005, you can now change these values and the effects will take place immediately. There’s no longer any need to restart the server. In addition, changes to Address Windowing Extensions (AWE) are also all dynamic. SQL Server 2005 can automatically adjust to changes to the physical AWE size dynamically. This feature is designed to work in conjunction with the ability to use Hot-Plug memory on the fly. This feature requires Windows Server 2003.

SQL Server Transaction Isolation Levels

Another area that affects database recoverability is the transaction isolation level that is used by the application. SQL Server 2005 provides a new level of transaction called Snapshot Isolation, giving it added flexibility for data access. The standard four ANSI isolation levels that were supported on earlier version of SQL Server (Serializable, Repeatable Read, Read Committed, Read Uncommitted) all protect transactions from one another by taking locks on the underlying data rows so that other applications can’t change the data. These transaction levels all continue to be supported. The new Snapshot Isolation provides increased data availability for read applications. With Snapshot Isolation, SQL Server 2005 performs a type of optimistic locking where SQL Server doesn’t take any locks on the rows involved in a transaction. Instead, it keeps track of the row’s state when the transaction is opened. When a Snapshot transaction is opened, the system essentially copies the row data for the transaction, enabling your application to continue to see the row data as it was at the time the transaction was started. No locks are placed on the rows. This enables non-blocking consistent reads in an OLTP application. Because Snapshot Isolation doesn’t hold any locks, data writers don’t block readers and readers don’t block writers. The new Snapshot Isolation level is really meant for applications that have an emphasis on read operations. When your application reads the rows in a transaction, it may be reading old data because other applications are able to change the data. While this new isolation level does allow writes, there is additional overhead in all updates. Snapshot Isolation is useful when you need a time-consistent view of all of the data in your database. For update applications, Snapshot Isolation is best used in scenarios where the cost of locking the data outweighs the cost of occasionally rolling back a transaction.