Availability Enhancements

High availability involves keeping your servers and their services up and running. Organizations running large enterprise applications, with users from all around the globe, demand a high-availability solution to assure 24x7 accessibility of the application by users. Continuous availability is essential to maintaining a successful online business. The need for a high-availability solution is easily justifiable by looking at the cost of unavailability, which in some instances can exceed the cost of the application and infrastructure.

The key to building highly available solutions is to minimize or eradicate the points of failures in the system architecture. The design of a highly available solution is focused on minimizing the points of failure within the available budget. Designing for availability involves anticipating, detecting, and automatically resolving hardware or software failures before they result in service errors, event faults, or data corruptionthereby minimizing downtime.

The following are the high-availability options in SQL Server 2000:

  • Failover clustering A failover cluster is a set of two or more independent computers that share resources so that if one of the servers fails, another server in the cluster will take over the resource and the processing load.

  • Replication SQL Server replication can be used for creating one or more synchronized, warm standby servers. The three replication configurations available in SQL Server 2000 are snapshot replication, transactional replication, and merge replication. A replication-based high-availability solution requires some downtime and is not as seamless and automatic as failover clustering. In addition, replication is generally a database-level high-availability solution, unlike clustering, which provides server-level high availability. Replication is a simpler and less expensive solution than failover clustering.

  • Log shipping Log shipping automatically synchronizes databases by continually backing up the transaction logs from the source database, copying and restoring the logs to the destination database. Like replication, log shipping is also easy to configure and provides a database-level availability solution.

SQL Server 2005 continues to support and enhance these three high-availability solutions. In addition, a lot of new features, such as database mirroring, database snapshots, online and parallel indexing, and early restore access, are provided to increase availability. Let's look at a new, interesting high-availability solution, called database mirroring, that has the potential of becoming the best high-availability solution for SQL Serverbased applications.

Database Mirroring

Like replication and log shipping, database mirroring is a software solution for increasing the availability of a specific database. Database mirroring was originally called real-time log-shipping. However, database mirroring is the accurate and more appropriate term for this high availability technology. Mirroring maintains two copies of the database, only one of which is accessible by clients at a given time. The accessible database is known as the principal database, and its duplicate is known as the mirror database. The two server instances communicate and cooperate as partners in a database mirroring session. The principal and mirror databases must reside on different server instances, which are known as the principal server and the mirror server, respectively. In production environments, the principal and mirror servers are installed on different servers to enable the mirror server to operate as a "hot" standby server for the database. In typical mirroring configurations, if the principal server is lost, the mirror server is readily available as a failover partner.

In its simplest form, database mirroring involves only the principal and mirror servers. A third server instance, known as the witness, can be added to a mirroring session. The witness server enables automatic failover. Automatic failover causes the mirror server to roll forward its copy of the database until it is completely up-to-date and then switch principal role. The former mirror server becomes the principal server, and the former mirror database becomes the principal database. In addition to automatic failover, the mirror server also plays the tie-breaker role between the principal and mirror to prevent a split-brain scenario where both machines think they are the principal. For instance, if the principal and mirror servers come up at the same time and both think they are the principal, then the witness can break the tie.


Database mirroring works only with databases that use the full recovery model. It does not work with databases that use the simple and bulk-logged recovery models. In addition, the master, msdb, temp, and model databases cannot be mirrored.

Figure 8.1 shows a typical database mirroring configuration.

Figure 8.1. Database mirroring configuration consists of a principal server instance hosting the live database, a mirror server instance hosting the "hot" standby copy of the mirrored database, and, optionally, a witness server instance to be used for automatic failover.

Before we look at database mirroring in more detail, let's review some of the benefits offered by this high-availability solution and also compare it with failover clustering.

Benefits of Database Mirroring

The following are some of the benefits offered by a database mirroringbased high-availability solution:

  • Database mirroring offers an easy-to-manage alternative to failover clustering. Database mirroring provides zero-data-loss high availability for individual databases through complete or near-complete redundancy of data.

  • Database failover is instantaneous (it takes less than 3 seconds). This is possible because of the new fast recovery technology in SQL Server 2005 (which is available only in the Enterprise Edition). When the original principal server is fixed, it can then rejoin the partnership, although when it does, it takes on the mirror server role. Role reversal or switching can be either automatic or performed manually.

  • Database mirroring works with a standard computer and storage, and it does not require a special and identical hardware configuration, as failover clustering does. In addition, database mirroring does not rely on any shared storage components.

  • Database mirroring provides an optional, self-monitoring configuration that allows automatic failover when a failure is detected.

  • Clients can use the mirror database as a reporting server by creating one or more database snapshots on the mirror database.

  • Servers can be in separate locations. A mirror server instance can be at a geographically remote location from the principal.

  • Database mirroring differs from log shipping because changes are reflected in real-time on the mirror (standby) server, and failover is automatic in database mirroring.

  • The data access API is enhanced to support database mirroring and facilitate automatic, transparent client redirection. In other words, when a database failover happens, clients can be automatically redirected to the new principal server. Clients can specify the name of the failover partner in the connection string while connecting to the principal server. All committed transactions remain intact; however, any uncommitted transactions are rolled back. The "Failover Partner = <partner_name>;" clause in the connection string instructs the client to connect to the specified partner, if it cannot connect to the principal. If the client can connect to the principal, it obtains the failover partner name from the principal server, ignores the failover partner value specified in the connection string, and caches the value that it obtained from the principal server. If the connection string does not specify the failover partner value, and if the client is not able to connect to the principal, the connection fails. Therefore, it is recommended that you always specify the failover partner value in the connection string.

  • Database mirroring supports full-text catalogs. This means you can mirror a database that has a full-text catalog. Database mirroring synchronizes the catalog along with the database. Changes to a full-text catalog are logged, copied to the mirror server, and replayed there. You can add or drop the full-text catalog on the principal server, and the actions are repeated on the mirror server. Note that if the replay fails on the mirror server, the mirroring session is paused, and the DBA must fix the problem on the mirror server and resume the session.

  • The impact of database mirroring on transaction throughput is zero to minimal.

Setting Up Database Mirroring

You can configure database mirroring by running T-SQL statements or by using Object Explorer in SQL Server Management Studio. The Mirroring page on the database properties dialog provides an interface to configure and monitor database mirroring. This section discusses the T-SQL approach.

Database mirroring requires SQL Server 2005 to be installed on the primary and the mirror instances and on the witness server instance (if present). After you have installed SQL Server 2005 on these servers and verified that the principal database is running in full recovery mode, you need to take these steps to set up database mirroring:


Use the CREATE ENDPOINT...FOR DATABASE_MIRRORING statement to create an endpoint over TCP to accept the database mirroring payload. With this step, you can specify that the transport security use Windows authentication or certificate-based authentication. In addition, you can specify one or more database mirroring roles (that is, witness, partner, or all) that the endpoint supports. Create a database mirroring endpoint on the principal, mirror, and witness servers. Note that only one database mirroring endpoint can be created per server instance. Use the sys.endpoints catalog view to find out whether a database mirroring endpoint already exists on the server instance.


After the endpoints are created and started, create the mirror database by using NORECOVERY to restore a recent full backup of the principal database. The mirror database must have the same name as the principal database, and these databases cannot be renamed during a database mirroring session. You must restore all the log backups done since the full backup. The mirror database needs the last log backup from the principal database. The principal and mirror database names must be the same. It is also recommended that the path of the mirror database files be identical to the path of database files on the principal.


Ensure that logins exist on the mirror server for all the database users.


Use SQL Server Management Studio or the ALTER DATABASE...SET PARTNER and ALTER DATABASE...SET WITNESS T-SQL statements to configure database mirroring.


If the witness and partners are running in different (but trusted) domains, you should create a login on the witness server for the domain user account of partner instances and grant CONNECT permission on the witness endpoint to the login just created. Similarly, on each partner server instance, you should create a login for the domain user account of the witness instance and grant CONNECT permission on the partner endpoint to the login for the witness server instance just created. If the witness and partners are running in different (and not trusted) domains, you must use the certificates for authentication.


You can pause database mirroring by running the ALTER DATABASE...SET PARTNER SUSPEND statement. You can resume mirroring by running the ALTER DATABASE...SET PARTNER RESUME statement. You can stop mirroring by running ALTER DATABASE...SET PARTNER OFF.

Database Mirroring Operating Modes

A database mirroring session can operate in either synchronous or asynchronous mode. In synchronous mode, every transaction committed on the principal database is also committed on the mirror server, guaranteeing the protection of the data (that is, high-protection mode). This is achieved by waiting to commit a transaction on the principal database until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. In other words, synchronous mode forces the principal to not consider a transaction committed until the mirror has entered the transaction into its transaction log and sent back an acknowledgement. Note that the wait for this message increases the latency of the transaction. The time required for synchronization essentially depends on how far the mirror database was behind the principal database at the start of the session (as measured by the number of log records initially received from the principal server), the workload on the principal database, network latency, and the speed of the mirror system.

Synchronous operation is maintained in the following manner:

  1. On receiving a transaction from a client, the principal server writes the transaction to the transaction log.

  2. The principal server writes the transaction to the database, and concurrently sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming any of the following to the client: a transaction, preparation to commit, a commit, or a rollback.

  3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.

  4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.

In synchronous operation mode, if a witness server is present, the session supports both automatic and manual failover (that is, high-availability mode). In the absence of a witness, synchronous sessions run in high-protection mode, and only manual failover is supported. In other words, high-availability mode requires the presence of a witness, which ideally resides on a third computer.

If transaction safety is set to OFF (that is, ALTER DATABASE <database> SET PARTNER SAFETY OFF), the mirroring session operates in asynchronous mode, also known as highperformance mode. In asynchronous mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. The database is synchronized after the mirror server initially catches up to the principal server. However, transactions commit without waiting for the mirror server to harden the log. Asynchronous operation permits the principal server to run with minimal transaction latency. The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database always lags somewhat behind the principal database, never quite catching up. The gap between the databases is typically small. However, the gap can become substantial if the principal server is under a heavy workload or the system of the mirror server is overloaded. If the witness server is absent in the mirroring configuration, it is not recommended that you turn off transaction safety and run in asynchronous mode. Such a configuration can lead to data loss and split-brain between the principal and mirror.


Changing the transaction safety restarts the database, disconnecting all clients that are currently using the database.

Monitoring Database Mirroring

The following are some system catalog views, dynamic management views (DMVs), Profiler trace events, and PerfMon (that is, System Monitor) counters that you can use to monitor database mirroring:

  • The sys.database_mirroring catalog view contains a row for each database present in the current instance. If a database does not participate in mirroring, all the columns prefixed with mirroring are NULL. This catalog view contains information such as the mirroring state, role, sequence, safety level, and connection timeout. The database mirroring state can be either synchronizing, synchronized, suspended, pending_failover, or disconnected.

  • The sys.database_mirroring_endpoints catalog view contains one row for the database mirroring endpoint of the server instance.

  • The sys.database_mirroring_witnesses catalog view contains a row for every witness role that a server plays in a database mirroring partnership.

  • The sys.dm_database_mirroring_connections DMV returns a row for each database mirroring network connection and can be used to view mirroring activity, including connection time, authentication method, encryption details, total bytes sent on a connection, and so on.

  • The Database Mirroring State Change event under Database Event Class in Profiler can be used to trace events when the state of a mirrored database changes.

  • Database mirroring writes log messages into the Windows application event log (eventvwr.exe). You can use a third-party event log monitoring solution to filter and monitor database mirroring events.

  • The database mirroring performance object ("SQLServer:Database Mirroring" for an unnamed instance, and "MSSQL$instance_name:Database Mirroring" for a named instance) contains about 11 counters that you can use to monitor database mirroring activity. For instance, the Transaction Delay counter displays the average delay in transaction termination waiting for acknowledgement; Bytes Sent/sec displays the number of bytes sent per second; and so on.

You might want to avoid using your OLTP system (that is, the principal server) for reporting; this helps you better utilize the mirror server resources. As mentioned earlier, to use a mirror database for reporting, you can create one or more database snapshots on the database. However, remember that creating snapshots and using the mirror server for reporting increases the load on the mirror server, and this performance hit can slow down the entire database mirroring solution. Let's take a detailed look at database snapshots feature.

Database Snapshots

The CREATE DATABASE statement now supports a new clause, AS SNAPSHOT OF, that can be used to create a static, read-only, transaction-consistent snapshot of a database as it existed at the moment of the snapshot's creation. The most common application of database snapshots is to create a snapshot of a mirror database so that clients can access the snapshot database for purposes such as reporting. In order to provide updated data to the clients, new snapshots must be created periodically, and new client connections must be directed to the most recent snapshot. Each database snapshot exists independently of any other database snapshots. Database snapshots on a mirror database may reduce performance on the principal database. Therefore, after all the clients have disconnected from an out-of-date snapshot, it should be deleted.

The other application of database snapshots is in the event of a user error on a source database. You can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.

You cannot create a database snapshot graphically by using Management Studio. The only way to create a database snapshot is to run the CREATE DATABASE statement with the AS SNAPSHOT OF clause. SQL Server creates an NTFS sparse file to hold the snapshot. Snapshots cannot be created on FAT32 or RAW partitions.


Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005.

The following is an example of creating a database snapshot:

SET NOCOUNT ON USE master; GO IF EXISTS(SELECT database_id FROM sys.databases    WHERE [name] = 'AW_05082005_SS1')    DROP DATABASE AW_05082005_SS1; GO CREATE DATABASE AW_05082005_SS1 ON    (NAME = AdventureWorks_Data, FILENAME='C:\AW_05082005_SS1.ss')  AS SNAPSHOT OF AdventureWorks; GO SELECT * FROM sys.databases    WHERE name = 'AW_05082005_SS1'; GO USE AW_05082005_SS1; GO SELECT * FROM sys.database_files; GO

This script checks whether a database named AW_05082005_SS1 exists. If it does, the script drops the database snapshot. Dropping a database snapshot is exactly the same as dropping any other user database. (Dropping a database snapshot deletes all the sparse files used by the snapshot.) Next, the preceding script creates a database snapshot by using the CREATE DATABASE statement. You can use the sys.databases, sys.master_files, and sys.database_files catalog views to see the database snapshot metadata. You can identify the source database for a snapshot by using the source_database_id column in the sys.databases catalog view. You can obtain the NTFS sparse filename for a database snapshot by using the physical_name column in the sys.master_files catalog view. You can use the fn_virtualfilestats function to determine the actual size and other statistics about the sparse file. The following SELECT statement uses the fn_virtualfilestats function to return the sparse file details for the database snapshot created in the preceding script:

SELECT * FROM  fn_virtualfilestats(DB_ID('AW_05082005_SS1'),FILE_IDEX('AdventureWorks_Data'))


A database snapshot must exist on the same instance as the source database. Multiple snapshots can exist on a source database, and they must always reside on the same server instance as the database.

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file. Figure 8.2 shows the properties of a sparse file used for a database snapshot just created on the AdventureWorks sample database. Note that the Size value is 165MB; however, the Size on Disk value is 128KB.

Figure 8.2. When a database snapshot is created, the sparse file reserves the space but does not use any space until the source database is updated.

When users read data from a database snapshot, SQL Server always accesses the original data pages, regardless of where they reside. If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database. After a page has been updated, a read operation on the snapshot still accesses the original page, which is now stored in a sparse file.

Sparse files grow in 64KB increments; thus, the size of a sparse file on disk is always a multiple of 64KB. The latest 64KB increment holds from one to eight 8KB pages, depending on how many pages have been copied from the source database. This means that, on average, the size of a sparse file slightly exceeds the space actually filled by pages. It is important to remember that a snapshot is not an entire copy of the source database. The snapshot refers to the source database for data that has not changed. Therefore, if the source database goes into recovery mode or if the source database takes a file offline, some data might not be available in the snapshot. If a filegroup was offline when a snapshot was created and you bring the filegroup online later, it is still considered offline in the snapshot.

Here are some considerations to keep in mind when creating and using database snapshots:

  • As long as a database snapshot exists, the source database cannot be dropped, detached, or restored. However, backing up the source database works normally; it is unaffected by database snapshots. The following SELECT statement shows an example of obtaining a list of database snapshots that use AdventureWorks as a source database:

    SELECT name FROM sys.databases   WHERE source_database_id = DB_ID('AdventureWorks');

  • Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

  • As long as a database snapshot exists, files cannot be dropped from the source database or from any snapshots.

  • Snapshots of the model, master, and temp databases are prohibited.

  • You cannot back up or restore and attach or detach snapshots.

  • You cannot create snapshots on FAT32 file systems or RAW partitions.

  • Full-text indexing is not supported on database snapshots, and full-text catalogs are not propagated from the source database.

  • A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed, and permission changes made to the source will not be reflected in existing snapshots.

As mentioned earlier, a database snapshot can sometimes be very helpful in recovering from user errors. The following script shows that a table is dropped from a database and then a database snapshot is used to recover that deleted table:

SET NOCOUNT ON USE master; GO IF EXISTS(SELECT database_id FROM sys.databases    WHERE [name] = 'AW_05082005_SS1')    DROP DATABASE AW_05082005_SS1; GO CREATE DATABASE AW_05082005_SS1 ON    (NAME = AdventureWorks_Data, FILENAME='C:\AW_05082005_SS1.ss')  AS SNAPSHOT OF AdventureWorks; GO USE AdventureWorks; GO DROP TABLE dbo.DatabaseLog; GO --SELECT * FROM dbo.DatabaseLog USE master; GO RESTORE DATABASE AdventureWorks    FROM DATABASE_SNAPSHOT = 'AW_05082005_SS1'; GO USE AdventureWorks; GO SELECT * FROM dbo.DatabaseLog; GO

The RESTORE statement allows you to restore from a database snapshot, provided that the following are true:

  • The source database does not contain any read-only or compressed filegroups.

  • Filegroups that are online were not offline when the snapshot was created.

  • Only one snapshot of the source database exists at the time of restoration.

  • The database does not have any full-text catalog at the time of restoration. You can drop the full-text catalogs and then start the restoration.

You have now learned about two new availability solutions introduced in SQL Server 2005. In addition to these new capabilities, SQL Server improves the classic availability solutions. The next two sections provide an overview of enhancements made to failover clustering and replication.

Failover Clustering Enhancements

Earlier versions of SQL Server supported only two-node or four-node clusters. SQL Server 2005 now supports failover clusters with up to eight nodes on 32-bit and 64-bit systems. In addition, Analysis Services has been enhanced to be cluster aware. Similarly to the database engine, you can configure Analysis Services on a failover cluster with up to eight nodes on 32-bit systems and four nodes on 64-bit systems.

SQL Server 2005 on Windows Server 2003 now supports dynamic Address Windowing Extensions (AWE) memory management. In an active-active cluster scenario, when the failover happens, SQL Server can dynamically adjust the amount of memory use based on the current workloads on the instances. It is recommended that you set the minimum server memory configuration setting to achieve a better balance after failover.

SQL Server 2005 setup has been enhanced to provide a simpler and more robust installation experience. Setup now allows scripted installation and also allows you to select components that you want to install. In SQL 2000, setup log files were dispersed across nodes and in various directories. In addition, the logging behaved differently for noncluster installations than for clustered installations. In SQL 2005, all logs are consolidated on the host computer. These logs use a standard naming convention that includes the machine name and the name of the component. The logs are placed by default into %ProgramFiles%\Microsoft SQL Server. Logging is now the same for clustered and nonclustered installations.

SQL Server 2005 now supports mount points. Clustered installations of SQL Server are limited to the number of available drive letters. Assuming that you use only one drive letter for the operating system, and all other drive letters are available as normal cluster drives or cluster drives hosting mount points, you are limited to a maximum of 25 instances of SQL Server per server. A mounted volume, or mount point, allows you to use a single drive letter to refer to many disks or volumes. If you have a drive letter G: that refers to a regular disk or volume, you can connect, or "mount," additional disks or volumes as directories under drive letter G: without the additional disks or volumes requiring drive letters of their own.

Replication Enhancements

SQL Server 2005 significantly enhances the replication support to allow you to implement a secure, easy-to-set up, easy-to-manage, and scalable enterprise data movement and availability solution. Complete description of these new features is beyond the scope of this chapter. However, the following are some of the replication enhancements introduced in SQL Server 2005:

  • Peer-to-peer transactional replication A new publication type for transactional replication has been designed to help improve application performance and system availability. Peer-to-peer transactional replication allows applications to read and modify the data at any of the databases participating in replication. All nodes in a peer-to-peer topology are peers: Each node publishes and subscribes to the same schema and data. Changes (that is, insertions, updates, and deletions) can be made at all nodes. Replication recognizes when a change has been applied to a given node, preventing changes from cycling through the nodes more than one time.

  • Replication of schema changes Replication is often considered a static schema environment. As applications evolve, they may require changes to the database schema. If a database is published, DBAs often find it challenging to have those schema changes propagated to all the subscribers with minimal downtime and effort. Previous SQL Server releases provided two special system stored procedures, sp_repladdcolumn and sp_repldropcolumn, to add a column to or remove a column from a published table. Use of these two stored procedures ensured that a table schema change was sent to all the subscribers. Basically, previous releases limited the schema changes on a published object to just adding and deleting a column and that, too, required use of special stored procedures. SQL Server 2005 fixes this by supporting a wide range of schema changes to published objects, without using any special stored procedures. You can continue to use statements such as ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION, and ALTER TRIGGER on a published object, and SQL Server takes care of propagating the schema change to all SQL Server subscribers.

  • Publishing from Oracle databases to SQL Server 2005 Snapshot and transactional replication topologies can now include an Oracle database as a publisher. Oracle version 8.0.5 and above on any Oracle supported hardware and operating system can be configured as a publisher. Oracle publishing does not require any special programming or the installation of any software on the Oracle side, nor does it require expertise in Oracle.

  • New Replication Monitor Replication Monitor has been completely redesigned for SQL Server 2005 and now runs as a separate application (sqlmonitor.exe). To start Replication Monitor, in Management Studio, you right-click the Replication folder in the Object Explorer and select Launch Replication Monitor. Replication Monitor allows you to monitor the overall health of a replication topology, providing detailed information about the status and performance of publications and subscriptions.

  • Initializing a transactional subscription from a backup By default, a subscription to a transactional publication is initialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. With this behavior, setting up replication between databases that initially contain large volumes of data can be time-consuming and requires large amounts of storage. SQL Server 2005 provides a new publication option that allows any backup made after the creation of a publication to be restored at the subscriber, rather than by using a snapshot to initialize the subscription. When you configure replication by using Management Studio, the Subscription Options page of the Publication Properties - <Publication> dialog box provides the option "Allow Initialization from Backup Files, which you" can use to avoid initializing using a snapshot. Similarly, the sp_addpublication stored procedure now accepts a new parameter, @allow_initialize_from_backup, to provide the functionality of initializing a transactional subscription from a backup. Later, the sp_addsubscription stored procedure must be called with the @sync_type parameter having 'initialize with backup' as its value, and backup details must be provided via parameters such as @backupdevicetype, @backupdevicename, and so on.

  • Replication Management Objects (RMO) SQL Server 2005 provides a .NET-based API to automate configuring, managing, and scripting of replication, and for synchronizing subscribers.

  • Transactional publications Transactional publications now allow up to 1,000 columns in each published table.

Online and Parallel Index Operations

SQL Server 2005 Enterprise Edition now supports index operations such as creating, altering, and dropping an index to be performed while users are still accessing the table data and using other indexes on the table. The CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements support the ONLINE option with a value of either ON or OFF (the default is OFF). The ONLINE option being set to ON specifies that underlying tables and associated indexes are available for queries and data modification during the index operation.


ONLINE cannot be set to ON when an index is being created on a local temporary table, when an XML index is being created, while you're creating a clustered index of a table that contains large object data types, or while you're creating nonclustered index on a large object data type.

When the ONLINE option is set to ON, long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an intent share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S lock is acquired on the source if a nonclustered index is being created; or a schema modification (SCH-M) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt.

The underlying table cannot be modified, truncated, or dropped while an online index operation is in process. Under certain circumstances, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server 2005 database engine selects the user or application activity as a deadlock victim. Although online index operations permit concurrent user update activity, the index operations take longer if the update activity is very heavy. Typically, online index operations are slower than equivalent offline index operations, regardless of the concurrent update activity level.

Let's try an online index operation and at the same time monitor it by using the Profiler. You need to start SQL Server Management Studio and connect to a SQL Server instance, using Query Editor, and then type the following query:

USE AdventureWorks; GO ALTER INDEX ALL ON Sales.SalesOrderHeader   REBUILD WITH (ONLINE = ON); GO

Before you execute this query, you need to start SQL Profiler and connect to the same SQL Server instance. On the Trace Properties dialog, you need to select Blank from the Use the Template combo box. Then you should select the Events Selection tab, expand Progress Report, and check Progress Report: Online Index Operation. You can use this event to view the progress of an online index build while it is running. Then you click the Run button to begin the Profiler trace. Next, you execute the preceding query in Management Studio. Figure 8.3 shows an example of trace events that result from running this query.

Figure 8.3. You can use Profiler to track the progress of an online index build while it is running.

In addition to online index operations, SQL Server 2005 Enterprise Edition also enables support for parallel index execution. The MAXDOP option can now be specified with the CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX, and ALTER TABLE DDL statements. The MAXDOP index option cannot be specified in the ALTER INDEX REORGANIZE statement.

Enhanced Multi-instance Support

The SQL Server 2000 supported up to 16 instances of the relational engine per computer. SQL Server 2005 Enterprise Edition increases the number of instances supported to 50, making server consolidation a more viable option. Other SQL Server 2005 editions support up to 16 instances of the relational engine. When you install multiple instances, it is possible to install different editions of SQL Server 2005. For instance, you may have a few instances of Enterprise Edition and a few instances of Standard Edition on the same computer. One instance of the Microsoft search service MSSearch 3.0 (a full-text search engine) exists per instance of SQL Server.

In addition to the database engine, SQL Server 2005 Analysis Services now supports the notion of multi-instance installations. Previous releases did not support installing multiple instances of Analysis Services. With SQL Server 2005 Enterprise Edition, up to 50 instances of Analysis Services can be installed on a single computer. Other SQL Server 2005 editions support up to 16 instances of Analysis Services.

Dedicated Administrator Connection

In previous releases, when SQL Server appeared to be unresponsive or running in an abnormal state, administrators struggled to connect to that instance in order to terminate problematic processes and fix the problem. SQL Server failed to respond to client connections in such situations. This has been fixed in SQL Server 2005 by means of Dedicated Administrator Connection (DAC). The new command prompt utility SQLCMD.exe supports the -A switch, which you can use to connect to an instance of SQL Server even if the server appears to be locked or running in an abnormal state. Members of the sysadmin fixed server role can activate the dedicated administrator connection locally over TCP/IP. Because DAC is established over TCP/IP, you need to ensure that the SQL Browser service is running if you are connecting to a named SQL Server 2005 instance. The connection is only allowed from a client running on the server. No network connections are permitted.

When connected using DAC, to avoid any potential blocking scenarios, it is recommended that you run all diagnostic queries with the lowest transaction isolation level, READ UNCOMMITTED, and that you use the LOCK_TIMEOUT value to set to a short time-out period. You should run only diagnostic queries and avoid potentially long-running operations such as defragmenting or rebuilding indexes. You can also use SQL Server Management Studio to connect to an instance by using DAC, by prefixing the instance name with ADMIN:. However, note that any Management Studio activity that attempts a second connection fails. For instance, let's say you connect to a Query Editor window in Management Studio by using DAC. Now, if you try to change the database by using the Database combo box on the toolbar, the operation fails, and you get an error message.

Here is an example of using SQLCMD.exe to use DAC on a SQL Server 2005 instance:


This command prompt statement connects to a SQL Server 2005 instance by using DAC over Windows authentication.

Early Restore Access

A restoration is a multi-phase process. There are three possible phases of a restoration:

  • Data copy The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files. The data copy phase initializes the contents of the database, files, or pages being restored. This phase is accomplished by restore database, restore file, and/or restore page operations, using full or differential backups. A restoration allows you to restore a subset of the data contained in a data backup; for example, you can restore one or more files from a full backup.

  • Redo (roll forward) The redo (roll forward) phase applies the logged transactions to the roll forward setup to the recovery point of the restore. The database engine processes log backups as they are restored, beginning with the log contained in data backups. At this point, a database might contain changes made by transactions that are uncommitted. End roll forward or redo phase brings the data to the point in time, called the recovery point, to which the user specifies that the set of data be recovered. Under the full or bulk-logged recovery model, you can specify the recovery point as a particular point in time or log record.

  • Undo (roll back) The undo (roll back) phase ends a restoration sequence by rolling back any uncommitted transactions and making the database available to users.

The database engine in SQL Server 2005 Enterprise Edition now lets users access a database after the redo, or roll forward, phase of a database restore operation completes. Earlier versions of SQL Server did not allow access to the database until completion of the roll back, or undo, phase. The other editions of SQL Server 2005 also do not let users access the database until recovery completes.

Instant File Initialization

If the database and transaction log files do not already exist, they must be created before data can be restored to them. The database and transaction log files are created, and the file contents are initialized to zero. Separate worker threads create and initialize the files in parallel. In addition to while creating a database, the file initialization process is also run when you add files to an existing database by using the ALTER DATABASE statement.

On Windows XP and Windows Server 2003 systems, SQL Server 2005 enables data files to be initialized instantaneously, which allows for fast execution of database or filegroup restore operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. To use instant file initialization, you must run the SQL Server service under an account that has the SE_MANAGE_VOLUME_NAME special Windows privilege. This privilege is assigned to the Windows Administrators group by default. If you have system administrator rights, you can assign this privilege by adding the Windows account to the Perform Volume Maintenance Tasks security policy.


Instant file initialization works only for data files and not for log files. A log file is a circular chain of log blocks, and the log manager relies on a block parity bit change to detect the end of a log at startup. SQL needs to initialize the log files so that SQL can establish the initial parity bit to correctly detect the end of a log after a restart.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net