publishing data on the Internet, and features that extend the power of database procedures.
Managing Multiple Servers
Microsoft has taken heat for the cumbersome way SQL Serv
er handles data changes on multiple servers. The process, called
two-phased commit
, is a mechanism for guaranteeing that changes to multiple servers are either all committed (permanently written) or all rolled back. In phase 1, the system ensures that all servers are ready to commit; in phase 2, the servers perform the commit. SQL Server required programmers to code the two phases, rather than the database system doing it automatically.
With a new feature in version 6.5, the Distributed Transaction Coordinator (DTC), users can simply execute Transact-SQL routines that update multiple servers within a transaction. The DTC works behind the scenes to track and coordinate changes. Alternatively, a DTC client utility allows applications to associate multiple-server SQL statements with a single distributed transaction. The advantage of this client utility is that it lets applications reference each server directly. In contrast, Transact-SQL routines execute on a single server, with references to othe
r servers limited to remote stored procedures.
The DTC uses OLE to coordinate the servers involved in a transaction. Although SQL Server is the only resource manager with which the DTC can work, OLE will let Microsoft eventually support distributed transactions that incorporate multiple resource managers. (A resource manager is a program that manages the resources accessed in a transaction.) Each resource manager will be an OLE server, with the DTC functioning as an OLE client that uses OLE automation to control the server's behavior.
SQL Server's Enterprise Manager provides an intuitive point-and-click interface for setting up replication among multiple servers. Individual tables on a server are made available by "publishing" them; other servers then "subscribe" to a published table. Administrators can specify that changes should be replicated immediately after they happen or according to a schedule. Anyone who has dealt with the complexities of Sybase's Replication Server or Oracle's various rep
lication options will appreciate the simplicity of Microsoft's approach.
New in version 6.5 is the ability to replicate to other databases besides SQL Server. This feature, called
heterogeneous replication
, uses Open Database Connectivity (ODBC) to transfer data from SQL Server to such databases as Access and Oracle. Because SQL Executive monitors changes to the data that is to be replicated and schedules the data transfers, replication in the other direction (from other databases to SQL Server) is not supported. You can't, for example, enter data into departmental Access databases and have SQL Server replicate the changes to a central location.
Internet Support
The Web Assistant that's included in SQL Server 6.5 is one of two tools introduced by Microsoft this year for producing Web pages that incorporate database contents. The other one is the Internet Database Connector (IDC), part of Microsoft's separate Internet Information Server (IIS).
While the IDC runs on
the Web server and "pulls" data from the database, the Web Assistant "pushes" data from the database to the Web. The Web Assistant has a wizard that lets Web-page designers enter selection criteria and formatting information; designers can also use templates similar to those the IDC uses. Although the resulting pages are static, rather than generated on the fly at run time as with the IDC, SQL Executive can regenerate them periodically, based either on a schedule or whenever the data changes.
OLE Automation via Stored Procedures
Microsoft has long recognized that, no matter how powerful a database's built-in language, some operations can be performed more efficiently by other programs. A previous release of SQL Server introduced external procedures, which let Transact-SQL routines call procedures in DLLs. In version 6.5, Microsoft has expanded on this concept by providing a set of predefined stored procedures to facilitate OLE automation. You can call these procedures in Transact-SQ
L routines to access OLE servers and execute their automation methods.
Developers can also write their own OLE-server programs for access from within SQL Server, extending functionality in much the same way as extended procedures. OLE automation is the centerpiece of Microsoft's strategy for applications partitioning. A large part of the Visual Basic 4.0 manual for developing client/server applications deals with transferring portions of an application's functionality to OLE-server programs running on a remote machine.
Having the option of performing OLE automation from within SQL Server rather than from client applications lets the automation routine be part of built-in validation procedures. For example, a credit check used to validate a purchase might require the use of an external application to dial into a remote location to check information.
Your Mileage May Vary
In our benchmarking (see the table in the sidebar "Making Book on SQL Server 6.5"), we found SQL Server
6.5 to be at least as fast as, and usually faster than, version 6.0, but you'll need to benchmark your own applications to get a more realistic measure of performance. Also, tuning the database, such as setting a table to be either row- or page-locked (see the sidebar "Row Locking vs. Page Locking"), can affect your results.
There is room for additional improvement in version 6.5. The graphical interface for loading data, which disappeared in the upgrade from 4.2 to 6.0, needs to be put back, and the inability to modify existing columns in tables is a major inconvenience. However, SQL Server is still head and shoulders above the competition in usability, particularly in the intuitiveness of its administration tools. It's a solid upgrade.
Where to Find
Microsoft SQL Server 6.5...................$1399 (five-user license)
Microsoft Corp.
Redmond, WA
Phone: (800) 426-9400 or (206) 882-8080
Fax: (206) 936-7
329
Internet:
http://www.microsoft.com
Circle 976 on Inquiry Card.
SQL Server: The Sequel's Better
