20 - SQL Server Advanced Topics

• How to set up replication using SQL Enterprise Manager - Explore the possibilities offered by replicating data. Use SQL Enterprise Manager to create a distribution database and then "publish" data on a publication server. Create a subscription server and complete the replication process.

• How to view data replication in action - Learn how to use SQL Enterprise Manager to monitor the tasks involved in replication and to view tables for evidence of data replication.

• Definition of the Microsoft Distributed Transaction Coordinator (MS DTC) - Learn about the nature of distributed transaction processing and explore some existing methods for implementing this functionality. Find out about a new addition to SQL Server version 6.5 that will help you implement transactions that access and update databases on more than one server.

• How to use the Microsoft Distributed Transaction Coordinator (MS DTC) - Learn how to monitor distributed transactions and how to use new tools to resolve problem transactions that may occur due to such causes as hardware failures or disruptions in network communications.

At this point, you have been introduced to enough administrative techniques to handle common situations that arise when using SQL Server. In this chapter, some advanced topics are explored. These techniques are not required for every SQL Server installation, but they can be powerful additions to the basics that you have already learned. You are introduced to data replication, the new Microsoft Distributed Transaction Coordinator, and a few techniques for resolving problems that may occur.

Data Replication

Put simply, data replication is a process that automatically copies information from one database to one or more additional databases. In the context of high-end database products like Microsoft SQL Server, data replication implies that this transfer of data is accomplished while maintaining a high degree of consistency and reliability. In other words, the information transferred is generally available within seconds or minutes of a change being made, rather than hours, and the transfer is accomplished accurately.

Traditional methods, such as sending a weekly tape with new information for example, can accomplish moving information slowly, but the servers involved are never (or rarely) synchronized with one another. Other available techniques, such as the two-phase commit (2PC) protocol, can be used to replicate information to one or more servers and to guarantee that the information at all locations is identical by preserving the ACID nature of transactions involving multiple servers. However, 2PC requires that all servers, and the communications links between them, be available or the transaction cannot occur. Due to the realities of network management and computer reliability, it is relatively common for one of the components to be unavailable. With 2PC, a transaction involving multiple servers comes to a halt if a single component fails.

You may recall from Chapter 17, "Understanding SQL Server," that the term ACID is an acronym representing four potential properties of a transaction - atomicity, consistency, isolation, and durability.

See "ACID," (Chapter 17)

When the Microsoft Distributed Transaction Coordinator is explored later in this chapter, you learn about an important new tool that can improve the management of 2PC processing, thereby making it more practical than in the past.

Replication strikes a balance between availability and consistency. Replication is useful in situations where information must be kept loosely synchronized. It does not guarantee that at every instant in time all copies of a particular data element will be identical. This is not a serious limitation. Many situations require the level of availability and consistency provided by SQL Server's replication.

Many sales organizations, for example, publish new pricing on a weekly or monthly basis. The distribution of this information often involves physically delivering a printed copy, a process that is subject to routine interruptions and failures. An automated mechanism that can provide all sales offices and distributors with pricing information that is current to within 30 minutes (or less) would be a welcome change.

The actual time required for replication events to take place can vary dramatically. It depends on the amount of information, the speed of the communications link, and the additional workload being handled by the servers involved. With small amounts of information on a high-speed LAN, replication can occur in less than a minute. However, the real-world situations in which replication is most useful often involve less desirable elements. Establishing a set of reasonable expectations is an important part of the planning process.

Before exploring replication in more detail, it is useful to learn some basic terminology. Microsoft has adopted a metaphor to help describe and define replication using SQL Server. A server that replicates information is said to publish it, and is known as a publisher or publication server. Servers that receive the information are subscription servers or subscribers. Another role in the process is that of moving the information. This may be done by the publication server, or an entirely separate computer. The server that performs this process is called the distribution server or distributor. Therefore, the publisher and distributor may refer to the same machine.

The information that is replicated is described with a number of special terms. A publication is a group of tables published as a unit. A publication is made up of one or more articles. Each article represents information from a single table. An article may contain an entire table or a partition. A horizontal partition contains only selected rows; a vertical partition contains only selected columns. Partitioning is also known as filtering. Publications and articles are replicated from a publication database to one or more subscription databases located on subscribers.

Overview of the Replication Process

You already know the three distinct roles played by servers in a replication event: publisher, distributor, and subscriber. As mentioned earlier, the distribution server can be the same computer as the publisher, or it can be a separate computer. Furthermore, a server may act as both a publisher and a subscriber. It is possible, for example, for a server to replicate information to itself, although it is difficult to imagine a use for this setup other than as a demonstration. A more likely situation would be for a publisher of one publication to act as a subscriber of a different publication from another publication server.

Under ordinary circumstances, you will want the publication database and subscription database(s) to be synchronized before the first new transactions applied to the publisher are replicated to subscribers. This job can be handled automatically by a special synchronization process (a Sync task). In the case of large databases and slow communications links, you may want to perform manual synchronization utilizing one or more high-speed tape dump devices, for example, to back up the publisher and restore to the subscriber. This is a more involved process, but is often more practical than attempting the transfer of many megabytes or gigabytes of information over a slow link.

When a publication is created, the LogReader task on the distribution server reads the transaction log of the publication server and copies transactions that have been earmarked for replication. They are copied to the distribution database on the distribution server. The Distribution task on the distribution server is now responsible for moving the information to the appropriate subscription databases on all subscribers.

A Sample Replication Scenario

This scenario involves only two servers - DATASRV and PRIMSRV. DATASRV is the publisher and the distributor. PRIMSRV is a subscriber to publications on DATASRV. A publication will be created from the information found in the sample database included with SQL Server - pubs - to make it easier for you to follow the example with your own servers. PRIMSRV will use a database named pubs2 as the subscription database. The titles table from the pubs database on DATASRV will be replicated to the pubs2 database on PRIMSRV.

It is only a coincidence that the sample database provided with SQL Server happens to be based on an example involving publishers. The pubs database has been provided as a sample for several years and was used well before replication brought about servers in the role of publisher. Hopefully, this won't make the replication scenario more difficult to follow. Clearly the subject matter of the information in the database has no impact on the replication process.

Both servers are members of the domain named INTERNAL. PRIMSRV is the Primary Domain Controller (PDC) for the INTERNAL domain, and DATASRV has been configured with the server role (that is, it is not a domain controller). The master domain model is being used for the servers on this network. A one-way trust relationship has been established between the INTERNAL resource domain and the GSULLIVAN master domain. INTERNAL trusts GSULLIVAN. Both SQL Servers (and both SQL Executives) are running in the security context of the same master domain service account (GSULLIVAN\sqlsvc).

See "Using Service Accounts," (Ch. 4)

See "Understanding Trust Relationships," (Ch. 5)

The Role of the SQL Executive

The SQL Executive plays an active role in replication. It might even be called the engine of the replication process. Three special purpose tasks are used to implement replication - the Sync, LogReader, and Distribution tasks. As you complete the dialog boxes used to tell SQL Server about your replication needs, these tasks are automatically created and run. You learn how to monitor what tasks are spawned, and how to check their success or failure.

Setting Up Replication with the SQL Enterprise Manager

Figures 20.1 through 20.19, which depict the replication process, are from a computer running Windows 95. The SQL Enterprise Manager and the two SQL Servers are all version 6.5. Some of the dialog boxes have been enhanced from version 6.0, but most are very similar. The concepts are identical. If you are using version 6.0, you should still be able to follow the procedures provided here with only minor differences. The biggest single change made to replication from version 6.0 to 6.5 is the capability to replicate to ODBC subscribers. In particular, the use of Microsoft Access and ORACLE subscribers is supported.

Setting up replication is a process involving many steps. There are some natural stopping points along the way, or you may proceed rapidly through the entire process. In an actual production environment, there might be a delay while waiting for synchronization to occur. It is common for automatic synchronization to be scheduled for off-peak hours, or to accomplish synchronization manually.

Publication and Distribution Servers

The first step in configuring replication is to install publishing on the publication server. At the same time, you will establish a distribution database on the distribution server. In the example presented here, the publisher is also the distributor - the server named DATASRV. To install publishing and create the distribution database, follow these steps:

1. Start SQL Enterprise Manager. If you haven't already done so, register both servers that will be used for replication. Establish an active connection to both servers by clicking the plus sign next to the server name. (Although this is not strictly necessary, it is a convenient way to be sure that you have no connectivity problems before beginning.)

2. Highlight the server you want to use as the publisher (DATASRV in the example). From the menu, choose Server, Replication Configuration, Install Publishing. The Install Replication Publishing dialog box appears (see fig. 20.1).

   Fig. 20.1 - The Install Replication Publishing dialog box is used to initiate the creation of the distribution database that will be used in the replication process.

3. In this dialog box, you can specify a local or remote distribution server. Select Local if you want to use the publication server as the distribution server as well. To use another server, select Remote. The example calls for a local distribution server.

4. The name of the distribution database defaults to "distribution." You can enter another name if you want.

5. Next, the database devices for the data and transaction log must be created. In the Data Device drop-down list box, select New. The New Database Device dialog box appears, as shown in figure 20.2.

   Fig. 20.2 - The New Database Device dialog box is used to create a device on which to store the distribution database.

6. Enter a name for the new device that will contain the distribution database. In general, this device should not be used as a default device. Select a drive location for the device in the Location drop-down list box. A path and file name for the device will be offered as a default that you can override if you want.

7. Next, you can either type a size for the device directly in the Size drop-down list box, or you can move the slider control to the right of the drop-down list with your mouse. A corresponding size is entered in the Size box, and the graphical display of available drive space is updated to reflect the size of the potential new device. When you are satisfied with your selections, click Create Now. If everything works properly, you should receive confirmation that the device has been created in an information window.

8. Repeat steps 5 through 7 for the log device. The log device does not need to be as large as the data device.

   
   A good rule of thumb for the log device size is from 10 to 30 percent of the data device size.
   

9. After creating both devices, you are returned to the Install Replication Publishing dialog box with all the options for a local distribution database completed (see fig. 20.3).

   Fig. 20.3 - The Install Replication Publishing dialog box with the newly created data and log devices is depicted in this figure. At this point, you are ready to create the distribution database.

10. Click OK. After a short interval for processing, an information window appears, confirming that Replication Publishing (the distribution database) has been successfully installed. You are also asked if you want to continue by adding publishing databases and subscribers at this time (see fig. 20.4).

    Fig. 20.4 - This dialog box provides confirmation that the distribution database has been successfully created.

11. Click Yes, and the Replication Publishing dialog box appears, as shown in figure 20.5.

    Fig. 20.5 - The Replication - Publishing dialog box is used to enable publishing to a specified list of servers. You can also control which databases can be used for creating publications.

12. In this dialog box, you tell SQL Server which servers should be allowed to subscribe to this publication server. This is one method of limiting the availability of publications to selected subscribers. You can also open this dialog box later (by choosing Server, Replication Configuration, Publishing from the menu) and disable a subscriber for this publisher with a single mouse click.

    For the purposes of the example, DATASRV enables publishing to PRIMSRV and establishes pubs as a publishing database. Click OK.

13. This is a natural stopping point. If you want, click the Replication Topology toolbar button and view the results of your efforts (see fig. 20.6).

Fig. 20.6 - This figure depicts the Replication Topology window displaying the replication relationship between DATASRV and PRIMSRV.

Articles and Publications

To create a publication on your publication server, follow these steps:

1. Start SQL Enterprise Manager. Click the Replication Topology toolbar button. You can also open this dialog box by choosing Server, Replication Configuration, Topology from the menu. The Replication Topology window appears (see fig. 20.7).

   Fig. 20.7 - The Replication Topology window provides a graphical representation of the relationship between publishing and subscribing servers.

2. Using the right mouse button, click the publication server. A context-sensitive menu appears. Select Publications, and the Manage Publications dialog box appears, as shown in figure 20.8.

   Fig. 20.8 - The Manage Publications dialog box displays a list of any currently defined publications. It is used to initiate the creation, modification, or deletion of publications.

3. Any databases that you enabled for publication appear in the Publications window. At this point if you click the plus sign to open the database, no publications will appear because none have been defined. Click New, and the Edit Publications dialog box appears (see fig. 20.9).

   Fig. 20.9 - The Edit Publications dialog box is a multi-tabbed dialog used to specify the data included in a publication and other parameters affecting the replication process.

4. Enter a Publication Title and Description for the publication. Leave the (default) Transaction Based option button selected.

5. On the Articles tab, find the table(s) you want to publish in the Database Tables box. Highlight them and click Add. For the example, select and add only the Titles table. If you want to further refine instructions for an article (such as defining a partition), highlight the article and click Edit. The Manage Article dialog box then appears, as shown in figure 20.10.

   Fig. 20.10 - Use the Manage Article dialog box to specify a table that will be replicated. Alternatively, you can indicate a horizontal or vertical partition (rows or columns) that will be replicated.

6. In this dialog box, you can change the name of an article and enter a description. Using the Filters tab, you can define a vertical partition by selecting particular columns, or a horizontal partition by entering a Restriction Clause. The Scripts tab allows you to control the action SQL Server will take when an INSERT, UPDATE, or DELETE occurs on a table marked for replication. You can also customize the initial table synchronization schema script.

   For the example, you should not make any changes. If you return to this dialog box later, after processing for this publication has occurred, the blank portions of the dialog box will be replaced by the default results. Click OK to return to the Edit Publications dialog box. Click the Synchronization tab, shown in figure 20.11.

   Fig. 20.11 - The Synchronization tab of the Edit Publications dialog box is used to select the method used for synchronization. You can select a date and time that the synchronization process will occur if you don't want to initiate the process now.

7. This tab controls the method and schedule for automatic synchronization. Click Change to view the schedule for the synchronization event (see fig. 20.12).

   Fig. 20.12 - This figure depicts the Task Schedule dialog box for the Initial Synchronization event that precedes replication of transactions.

8. Theoretically, it is only necessary to perform synchronization once. Thereafter, transactions will be distributed to subscribers as they occur. In practice, it may be appropriate to apply a synchronization event at some regular interval (for example, weekly or monthly) to be certain that subscriber data is identical to the publisher. Remember, replication is based on a loose consistency model, and data is not guaranteed to be identical.

   For the purposes of the example, change the schedule to reflect a task that occurs one time approximately one hour in the future. This gives you time to set up the subscriber before synchronization occurs. You can manually run the task before the scheduled time if the subscriber is ready and you don't want to wait. After a task completes, it is disabled but not deleted, so you can always use it again if needed. Click OK to return to the Edit Publication dialog box and then select the Security tab (see fig. 20.13).

   Fig. 20.13 - The Security tab of the Edit Publication dialog box is used to limit the servers that will be allowed to view and subscribe to this publication.

9. You can restrict the visibility of this publication to only certain subscribers. For the example, either leave the Unrestricted option checked, or click the Restricted To option and click the check box to Allow Access for the subscription server (PRIMSRV).

10. Click Add. Note that if you edit an existing publication, this button becomes the Modify button. The publication is created, and tasks are launched for the synchronization event.

Subscribing Servers

So far, you have created a publication server (DATASRV), enabled publication to PRIMSRV, and created a publication. You are now ready to set up a subscription server. Before doing so, a short explanation about certain commands is appropriate to avoid confusion.

Using SQL Enterprise Manager, you can select commands from a menu, use a toolbar button, or use the right mouse button to activate a context-sensitive pop-up menu. All available methods for a particular command yield the same results. Those new to replication are sometimes confused by the similarity of some commands. This is amplified by the fact that there are two or three ways to specify the same command.

Two main dialog boxes have publishing as their subject matter - the Replication - Publishing dialog box and the Manage Publications dialog box. The first is used for configuring which servers are enabled to subscribe to a publisher and to specify the publication databases. The second is used to actually create publications. Likewise, there is a Replication - Subscribing dialog box and a Manage Subscriptions dialog box. These two dialog boxes are used similarly to their publishing counterparts: one to configure subscribing and one to create actual subscriptions. Hopefully, this short digression will help you avoid difficulties finding the right dialog box.

To subscribe to a publication, follow these steps:

1. Start SQL Enterprise Manager if it is not already running.

2. Open the Replication Topology window. Right-click the subscription server (PRIMSRV in the example) and select Replication Configuration, Subscribing from the menu. The Replication - Subscribing dialog box appears, as shown in figure 20.14.

   Fig. 20.14 - The Replication-Subscribing dialog box allows you to restrict the publishing servers that can be used by this server as a subscriber.

3. This dialog box is used to specify the publishing servers this subscriber can use and which databases can be used as the destination for replicated data. For this example, you should not need to make any changes. Click OK.

4. Right-click the subscription server again and select Subscriptions from the pop-up menu. The Manage Subscriptions dialog box appears, as shown in figure 20.15.

   Fig. 20.15 - The Manage Subscriptions dialog box lists the current subscriptions and is used to initiate subscribing to available publications.

5. The publication servers that this subscriber has enabled appear in the Publications window. By opening the server and then the publication databases on the server, you can see the publications and articles available to this subscriber. You can get additional information about a publication by selecting it and clicking More Info. For the example, select PriceList and click More Info. The Publication Info dialog box appears (see fig. 20.16).

   Fig. 20.16 - The Publication Info dialog box describes the contents of a publication and its synchronization schedule.

6. Confirm that this is the publication to which you want to subscribe and then click Close to return to the Manage Subscriptions dialog box. With PriceList still selected, click Subscribe. The Subscription Options dialog box appears (see fig. 20.17).

   Fig. 20.17 - The Subscription Options dialog box is used to specify the destination database for the replication and to select the type of synchronization you would like to apply.

7. Select the option Data Synchronization Automatically Applied. For the example, the destination database (and the transaction log and devices for each) must be created. Select New from the Destination Database drop-down list box, and the New Database dialog box appears.

8. Enter a name for the subscription database (pubs2 in the example).

9. Next, the database devices for the data and transaction log must be created. In the Data Device drop-down list box, select New. The New Database Device dialog box appears (see fig. 20.18).

   Fig. 20.18 - The New Database Device dialog box provides the opportunity to define a new device for the database and/or the transaction log.

10. Enter a name for the new device that will contain the subscription database. This device should not be used as a default device. Select a drive location for the device in the Location drop-down list box. A path and file name for the device will be offered as a default that you can override if you want.

11. Next, you can either type a size for the device directly in the Size drop-down list box, or you can move the slider control to the right of the drop-down list box with your mouse. A corresponding size will be entered in the Size box, and the graphical display of available drive space will be updated to reflect the size of the potential new device. When you are satisfied with your selections, click Create Now. If everything works properly, you should receive confirmation that the device has been created in an information window.

12. Repeat steps 9 to 11 for the log device. The log device does not need to be as large as the data device.

    
    A good rule of thumb for the log device size is from 10 to 30 percent of the data device size.
    

13. You should now be back to the New Database dialog box with the data and log devices created and specified. Click Create New to create the subscription database.

14. You return to the Subscription Options dialog box with the newly created database entered in the Destination Database list box. Click OK.

15. You then return to the Manage Subscriptions dialog box (see fig. 20.19). The display should now indicate that you are subscribed, but not synchronized. Click Close.

Fig. 20.19 - This figure depicts the Manage Subscriptions dialog showing a publication with a subscribed but not synchronized status.

Congratulations! You have completely configured replication. With no further action on your part, the synchronization event you scheduled will take place at the specified time. Any changes you make to the pubs database on the publication server (DATASRV) will be automatically replicated to the subscriber (PRIMSRV).

Monitoring the Status of Replication Events

Now that you've configured replication, you'd probably like to see replication in action - see some changes to the publication database appear in the subscription database as well. In this section, you learn techniques for monitoring the status of replication events. You will follow the progress of the replication example to see its effects.

There are a number of ways to see the effects of replication. You can view the tasks that are created and running to perform replication actions. You can also see that the new subscription database has been created and that the replicated table has automatically been created by the synchronization process. You can enable e-mail or event log notification of the success or failure of tasks, such as the distribution task, to leave evidence that the process is working. And of course, you can make a change in the publishing table and then view the subscriber to see if the change has been replicated.

To view the tasks used for replication, follow these steps:

1. Start SQL Enterprise Manager.

2. You want to review the tasks that have been launched on the distributor. Select the distribution server (DATASRV) in the Server Manager windows and click the Task Scheduling toolbar button. The Task Scheduling dialog box appears, as shown in figure 20.20.

   Fig. 20.20 - The Task Scheduling dialog box displays any defined tasks including those associated with replication.

3. Depending on the schedule you established for the synchronization event earlier, your Sync task may not have run yet. If you want, you can highlight the Sync task and click the Run Task toolbar button to run the task immediately.

4. Click the Running Tasks tab (see fig. 20.21). The LogReader and Distribution tasks should be Active. Close the Task Scheduling dialog box when you are finished viewing.

Fig. 20.21 - This figure shows the Running Tasks tab of the Task Scheduling dialog box displaying active replication tasks.

To check the subscription database for evidence that the synchronization process has completed, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, select the subscription server and open its Databases folder. Find the destination database you specified when you created the subscription and open its folder (pubs2 in the example).

3. Open the Objects folder and then the Tables folder. If the Sync task has completed, you should see the table that was created (titles in the example).

4. Right-click the mouse button on the table and select Edit from the pop up menu. The Manage Tables dialog box displays the structure of the table created during the synchronization process (see fig. 20.22). It should contain the columns you specified (all columns in the example - no partitioning was specified).

Fig. 20.22 - The Manage Tables dialog box can be used to view the structure of an existing table.

Monitoring tasks and viewing the newly created tables in the subscription database provide circumstantial evidence that replication is working, but there is no substitute for actually changing the data on the publisher and seeing the change reflected in a subscriber. To see replication in action, follow this procedure:

1. Start SQL Enterprise Manager.

2. Highlight the publication server (DATASRV) and click the SQL Query Tool button on the toolbar (this button was called the Query Analyzer in version 6.0).

3. Highlight the subscriber (PRIMSRV) and click the SQL Query Tool button again.

4. Click anywhere in the first query window (the one for DATASRV) to make it the active window; then choose Window, Tile Horizontally from the menu. Your display should look something like figure 20.23.

   Fig. 20.23 - SQL Enterprise Manager is shown in this figure with a query window open for both publisher and subscriber, a convenient arrangement for testing replication.

5. In the top (publisher) window, select the name of the publication database in the DB drop-down list box.

6. In the subscriber window, select the name of the destination database (the subscription database). In the example, the pubs database should be selected on DATASRV, and pubs2 should be selected on PRIMSRV.

7. Because replication can happen very quickly, you should view the destination table before replication to verify its contents. In the example, the new titles table in the pubs2 database should still have the original values that were installed with SQL Server unless you have used pubs.

8. To verify the current contents, enter the following query in the Query tab of PRIMSRV's window: SELECT title_id, price FROM titles. Click the Execute Query button and view the results in the Results tab of the dialog box.

9. In the Query tab of the publisher's window, enter a Transact-SQL (T-SQL) statement that will modify some of the data in the publication. For the example, the titles table will be modified to reflect a two percent price increase on all titles. This can be done with the following T-SQL statement which you should enter into the query window: UPDATE titles SET price = price * 1.02.

10. Click the Execute Query button. In the Results tab, you should see that all rows have been updated.

11. Click the Execute Query button in the subscriber's window again. You may already see the replication reflected by changed prices. If not, wait a few minutes and try again. You should see the updated prices in the Results tab (see fig. 20.24).

Fig. 20.24 - The Results tab is used to display the results of a query that has been executed. In this case, it shows the replicated information.

Microsoft Distributed Transaction Coordinator (MS DTC)

A complete discussion of 2PC is beyond the scope of this book. However, a brief overview will help you to understand the rudiments of this technology and the powerful new components that Microsoft has added to make the use of distributed transactions more practical. This discussion of 2PC is a deliberate simplification designed to help managers and SAs understand the role and importance of 2PC in distributed transaction processing. Developers wanting to use this technology are urged to seek additional resources, such as the Microsoft Distributed Transaction Coordinator Administrator's Guide and Programmer's Reference.

Sample Transaction Scenario

Trying to implement this behavior is harder than it might at first appear. For this technology to be useful, it must continue to work when one or more computers fail at the worst possible moment. Anyone who has used computers for even a short period of time knows that they can and do fail. SQL Servers have the capability to log transactions in a write-ahead log, and either commit them or roll them back. Furthermore, this capability is robust enough to handle system failures and power outages. However, this facility alone is not enough to implement distributed transactions.

Suppose that your application has sent update instructions to both databases and is ready to commit the transaction. It could send a commit instruction to the first server (call it A), wait for a confirmation, and then send a commit to B. But what if B, or the communications link between the client and B, fails after A has committed and before B gets the message? The rule has been broken. You did not achieve an all or nothing transaction. You could try sending the commit instruction to both at the same time, but this is really no better.

Two additional elements are needed. You must ask your servers to achieve a prepared state in which they can durably commit or roll back. In other words, if the computer fails in the prepared state, it can be restored to the prepared state, still ready to commit or roll back. Additionally, you need a commit coordinator to help this process take place. Of course, the application itself could take on this role, but managing the states of multiple SQL Servers in a durable manner is a lot to expect from a typical application program.

With these additional elements, the following process can occur. When an application is ready to commit its updated information, it notifies the coordinator. The coordinator instructs the SQL Servers to prepare to commit. They each attempt to adopt the prepared state. If one (or both) fails, the coordinator records in a log that the transaction has failed and advises both servers to roll back the transaction. A failed server would check with the coordinator when it was running again and find out that transaction should be rolled back. The coordinator can also recover from a failure and remember the status of the pending transaction.

If both servers report that they have successfully prepared, the coordinator records that the transaction should commit and advises both servers to commit the transaction. If a server or communications link fails at this point, all three components (the two SQL Servers and the coordinator) are able to restart and achieve the prepared state again. The coordinator's log will "remind" them all to commit the transaction. If the commit takes place (as it usually does) the application has successfully completed a distributed transaction.

A full analysis of all the possible failures that could occur, at all the worst times, requires a good deal of thought. For this discussion, suffice it to say that the 2PC mechanisms described provide a reliable and automatic problem resolution in most failure situations. A few conditions still require human intervention, however. One such case is an extended outage of a server or communications link that leaves elements in the databases locked. If the locked element is a table of all available rooms at a hotel, the front desk personnel will undoubtedly expect the situation to be resolved quickly.

Role of the MS DTC in Transaction Processing

The Microsoft Distributed Transaction Coordinator (MS DTC) adds important new components to help implement distributed transactions and make the management of 2PC a practical undertaking. Each server has a full MS DTC service that will coordinate transactions with other servers. Using the MS DTC, a server can take on the role of commit coordinator for certain transactions. In addition, the MS DTC can help resolve problem (or in-doubt) transactions by communicating with other MS DTC services running on other servers involved in a transaction.

A client-side interface to MS DTC is available for Windows NT and Windows 95 computers. This interface allows developers to create applications using distributed transactions and leverage the facilities provided by the full MS DTC service running on SQL Servers. The client components of MS DTC do not include a full MS DTC service, even on Windows NT clients. Also, the MS DTC client is only available for 32-bit versions of Windows (Windows 95 and Windows NT).

It is possible for a stored procedure running on a SQL Server to launch a distributed transaction on behalf of a 16-bit client. Therefore, older Windows clients can still benefit from MS DTC functionality.

In addition, a set of management utilities has been added to SQL Enterprise Manager. The utilities include graphical tools that allow you to dynamically monitor the state of transactions on SQL Servers running the MS DTC service. You can open multiple windows to monitor transactions occurring on multiple servers from a single workstation. The MS DTC extensions to SQL Enterprise Manager also allow an administrator to manually resolve problem transactions arising from equipment or application failure.

Although SQL Enterprise Manager can be run on Windows 95, the MS DTC utilities are only available when using SQL Enterprise Manager on a Windows NT computer.

For application developers, MS DTC offers new tools to support distributed transactions. A new statement in T-SQL, BEGIN DISTRIBUTED TRANSACTION, allows the creation of stored procedures that utilize MS DTC to coordinate transaction execution on multiple servers. This dramatically simplifies the development of stored procedures involving remote servers. In addition, an API for C/C++ programmers that conforms to the OLE Component Object Model has been provided for MS DTC. Using this interface, an application developer can create transaction objects and enlist the services of transaction resource managers and transaction coordinators to process those objects.

At this point, Microsoft SQL Server is the only available resource manager, but an OLE Transaction interface definition has been published, and other resource managers will be created by Microsoft and other software companies. Some level of interoperability is offered with several existing transaction processing monitors including Encina, Top End, and TUXEDO.

Managing MS DTC With SQL Enterprise Manager

See "Services," (Ch. 6)

The rest of this section focuses on the MS DTC capabilities added to SQL Enterprise Manager. This administrative tool has been enhanced with functionality to completely monitor and manage MS DTC capabilities. To start the MS DTC service with SQL Enterprise Manager, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group and select the server you want to manage.

3. If the SQL Server service is not yet running on the server, click the Services button - the one that looks like a stoplight - on the toolbar. The SQL Server Manager dialog box appears displayed.

4. Click the green Start/Continue portion of the stoplight control. When SQL Server starts, click the Done button.

5. Now click the plus sign to the left of the server name. The services and objects on the server are listed.

6. Right-click the MS DTC icon. It is typically listed just below the SQL Executive icon and is labeled Distributed Transaction Coordinator. Select Start from the pop-up menu. After a brief pause, the icon will turns green to indicate the service has started.

The DTC Configuration dialog box allows you to control the behavior of the MS DTC service. You can configure parameters that affect viewing transactions in the Transactions window, the tracing information sent to the Trace window, and the location and size of the MS DTC log file. To configure the MS DTC service, follow this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group and select the server you want to manage. If SQL Server and the MS DTC service are not running, start the services.

3. Right-click the MS DTC icon. Select Configure from the pop-up menu. The DTC Configuration dialog box appears, as shown in figure 20.25.

   Fig. 20.25 - Use this dialog box to configure the displays that monitor the distributed transaction coordinator service and its behavior.

4. Using the Display Refresh slider bar, you can configure MS DTC to update the display at intervals from 1 to 20 seconds with a default value of five seconds. Updating the display more frequently adds administrative overhead to transaction processing and can cause reduced performance.

5. The older a transaction, the more likely it is having difficulty completing. The Transactions Shown slider controls how old a transaction must be before being displayed in the Transactions window. You can set values from one second to five minutes.

6. The Trace slider controls how much trace information is sent to the Trace window. You can specify no tracing, increasing levels of error, warning and informational traces, or all trace information.

7. View settings can be changed while the MS DTC service is running. To change log settings, you must stop the MS DTC service. You can then change the location and size of the log.

To view the status of active transactions, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group and select the server you want to manage. If SQL Server and the MS DTC service are not running, start the services.

3. Right-click the MS DTC icon. Select Transaction from the pop-up menu. A DTC Transactions window for the selected server appears (see fig. 20.26).

   Fig. 20.26 - This figure depicts a DTC Transactions window for the server DATASRV.

4. You can select another server and open a transactions window for it as well. It is possible to monitor the transactions on a number of servers simultaneously using tiled or cascaded windows.

5. In the transactions window, you can monitor transaction states and manually resolve in-doubt transactions.

You should not manually force transactions until you thoroughly understand the interaction of all members of an MS DTC system. Please review the MS DTC Administrator's Guide and Programmer's Reference carefully before using this utility to resolve transactions.

To view the traces being sent (at the level you configured MS DTC to provide), follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group and select the server you want to manage. If SQL Server and the MS DTC service are not running, start the services.

3. Right-click the MS DTC icon. Select Trace from the pop-up menu. A DTC Traces window for the selected server appears, as shown in figure 20.27.

   Fig. 20.27 - A DTC Traces window for the server DATASRV showing an active transaction.

4. You can select another server and open a traces window for it as well. It is possible to monitor traces from a number of servers simultaneously using tiled or cascaded windows.

An MS DTC service maintains statistical information about its performance. To view the statistics that have accumulated for an MS DTC service, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group and select the server you want to manage. If SQL Server and the MS DTC service are not running, start the services.

3. Right-click the MS DTC icon. Select Statistics from the pop-up menu. A DTC Statistics window for the selected server appears, as shown in figure 20.28.

The statistics for an MS DTC service are cleared and restarted whenever the MS DTC service is stopped and restarted.

Fig. 20.28 - This figure depicts a DTC Statistics window for the server DATASRV.

From Here...

• For an review of the procedures for administering SQL Server, see Chapter 19, "Maintaining Your SQL Server."

• For information on the host connectivity component of BackOffice, SNA Server, see Chapter 21, "SNA Server Preparation and Installation."

• For information on the systems management component of BackOffice, Systems Management Server (SMS), see Chapter 25, "Preparing for SMS."

Table of Contents

19 - Maintaining Your SQL Server

21 - SNA Server Preparation and Installation