SQL Server 6.5 Lands on Your System

No, Hydra's not a B-grade remake of an old Godzilla movie. It's Microsoft'slatest release of SQL Server 6.5 (formerly code named Hydra). Despite the smallbump in version number, version 6.5 includes major enhancements that programmersand administrators can incorporate quickly and easily into their databases. Someimportant improvements are in key functional areas such as Internet support,Object Linking and Embedding (OLE) support, distributed data management,data-warehousing support, and utilities.

SQL WWW Page Wizard
SQL Server 6.5's Internet strength begins with the SQL WWW Page Wizard, anintelligent assistant that helps you publish data on the Web, using standardHypertext Markup Language (HTML) pages. The SQL Server development team addedthe wizard in response to Bill Gates' decree that all Microsoft products be "WorldWide Web enabled."

The SQL WWW Page Wizard asks questions to obtain data to insert into theHTML page. Version 6.5 provides a visual query builder, but experienced SQLdevelopers may want to enter a Transact SQL (T-SQL) query or stored proceduredirectly. Then a dialog asks you to provide a publishing schedule, which you canuse to publish an HTML page for one-time-only release, refresh a page on apredefined schedule, or update a page when the underlying data changes.

The SQL WWW Page Wizard generates HTML code by calling on three new storedprocedures: sp_dropwebtask, sp_makewebtask, and sp_runwebtask. The result is aneasy-to-access interface for generating Web pages from any program that can calla stored procedure, including Visual Basic (VB), Delphi, and most developmentenvironments that access Open Database Connectivity (ODBC) data sources. Forexample, developers can call these stored procedures to create their own Webwizard.

Internet Database Connector
Microsoft's new Internet Information Server (IIS) is another manifestationof Internet commitment. IIS is available for free at www.microsoft.com and willbe part of NT 4.0. IIS includes Internet Database Connector (IDC), which letsIIS access data from any ODBC-compliant data source.

Although IDC is not new to version 6.5, it's significant because itsimplifies Web integration. IDC uses Microsoft's new Internet Server API(ISAPI), which lets you create server-based applications that users can activatefrom any standard Web browser. The ISAPI application grabs data from theclient's HTML page, acts on it, and returns information to the Web browser,using standard HTML.

ISAPI is similar to the popular Common Gateway Interface (CGI), butsuperior in two important ways. First, ISAPI is a Windows-standard API, so mostprogramming languages that call Dynamic Link Libraries (DLLs) can use ISAPI.This capability eliminates the need for text processing languages, such as Perl,that CGI uses. Second, ISAPI applications typically perform better than theirCGI counterparts because ISAPI applications can encapsulate their logic instandard DLLs that load once at runtime. This initial loading eliminates theneed to start a separate process for each request and reduces the processingoverhead that can bog down busy servers.

IDC relies on a new DLL, httpodbc.dll, for data access. This DLL uses twofile types: IDC (.idc) files to control how users access the database and HTMLExtension (.htx) files to control construction of the output Web page. The bestway to learn about IDC for creating database-ready Web pages is to experimentwith the sample applications that ship with IIS.

I recommend that you look at the Guest Book application, a handy programthat sets up a user registration form so users can insert, modify, and retrievedata from an ODBC-compliant database. The Guest Book .idc file references anODBC data source and provides login information. A select query in the filereturns an HTML-formatted result set to the Web browser. This .idc file willalso insert and format data for the client, using information in a complementary.htx file. The contents of the Guest Book .idc file are

Datasource: Web SQL
Username: sa
Template: sample.htx
SQLStatement:
+SELECTau_lname, ytd_sales
+ from pubs.dbo.titleview
+ where ytd_sales>5000

Improved OLE Support
OLE doesn't get the attention that the Internet enjoys, but developersunderstand its power. Microsoft has significantly improved SQL's OLEcapabilities. OLE Automation is a standard mechanism that lets you takeadvantage of and access data from one application (the automation server) whileyou're in another (the application controller).

In version 6.0, SQL Distributed Management Objects (SQL-DMOs) letdevelopers control most aspects of the server from an OLE Automation interface.SQL-DMOs (similar to "functions" in a 3- or 4GL environment) haveadjustable properties that describe their state and methods and affect theiractions. SQL Server 6.5 adds to this by providing several OLE-basedenhancements. Two new DMOs are BulkCopy and Transfer.

The BulkCopy Object: The BulkCopy Object provides an interface froman OLE-controller application for high-speed data transfer in and out of SQLServer. By setting advanced properties, the user controls key aspects of thedata transfer process such as the type of BulkCopy Object (e.g., character ornative mode), whether to truncate the transaction log before data loading, andwhether to drop nonprimary key indexes before data loading. If the user dropsexisting indexes, the BulkCopy Object automatically re-creates them after dataloading.

The Transfer Object: Developers often need to transfer specificobjects or even entire databases between servers. Using 25 properties, theTransfer Object lets you control all aspects of such transfers. Listing 1 showshow easily you can move databases around your network with the Transfer Object.In fact, the transfer capability built into the new SQL Enterprise Manager isbased on this Transfer Object.

Extended Stored Procedures
An important version 6.5 enhancement is that you can create extended storedprocedures. These procedures rely on languages other than T-SQL (the nativeprogramming environment for SQL Server) to encapsulate business processing inserver-based objects. Previous releases of SQL Server forced developers to writethese extended stored procedures with C or C++, which limited the procedures'mainstream use.

Although nothing is inherently difficult about writing code in C, manydevelopers and corporate IS shops are more comfortable with high-level toolssuch as VB. Now that you can build extended stored procedures with VB, theseprocedures will become more common in client/server applications. Listing 2shows a VB-generated extended stored procedure (identified as xp) that echoes aparameter the user supplies.

Replication
Microsoft added replication in version 6.0 to let users copy databasesacross servers. Version 6.5 expands this idea by letting you distribute data toODBC-compliant data sources. Although the replication publisher must be the SQLServer that maintains the master data copy, you can now publish data todatabases such as DB2 and Microsoft Access. With replication, for example, yoursales representatives in the field can get the latest data every time they dialin.

Distributed Transactions
A big challenge facing distributed database architects is ensuring thequality of data while maintaining performance and availability. Althoughreplication was in SQL 6.0, many critics contend 6.0 didn't provide the samelevel of distributed data support that some Microsoft competitors offered.Version 6.5 leaps forward by including a Distributed Transaction Coordinator(DTC) that integrates with X/Open DTP XA-compliant transaction-processingmonitors such as Tuxedo. DTC also lets you write distributed transactions andremote procedures that you can control from a central location.

A major difficulty with managing a distributed transaction is gettingseveral entities to agree on the transaction's success or failure. If anycomponent fails, the application resource manager must roll back the entiretransaction to ensure its atomic nature. The DTC manages this process in adistributed environment by letting application resource managers registertransactions. Then the DTC tracks the state of global transactions. A two-phasedcommit (2PC) protocol lets all registered resource managers commit or roll backtransactions under their control.

Developers can initiate distributed transactions in one of two ways. Theycan incorporate API functions in ODBC and DB-Lib, or they can issue one of twonew T-SQL commands: BEGIN DISTRIBUTED TRANSACTION orSETdistributed_proc_transaction. Both let developers tell SQL Server to registerthe next transaction with the DTC. The following example shows how to updatedata on multiple servers by issuing the first of these new T-SQL commands.

BEGIN DISTRIBUTED TRANSACTION UPDATE Loan SET AcctBalance = 1,000,000    WHERE AcctId = 00105 EXECUTE remote.BankDB.dbo.   ChangeAcctBal 1,000,000,   00105 COMMIT TRAN

This update is easier than ever. Microsoft apparently listened closely tousers frustrated by difficulties supporting distributed data. Without a lot oflow-level DB-Lib programming, the DTC lets you do things with SQL Server thatwere never before possible.

Data Warehousing
Data warehousing is the latest buzzword describing the underlying databasecapabilities necessary to perform online analysis of sales, inventory, and othercritical business data from various systems across an enterprise. Fasterhardware and a new generation of online analytical processing (OLAP) tools helpmake this market segment a hotbed of activity.

SQL Server 6.5 integrates data warehousing, solving the problem of dataaggregation. Most queries in a warehouse environment return highly aggregateddata, so users can see the big picture and visualize data in several ways.Unfortunately, ANSI SQL support for data aggregation is limited. For instance,the GROUP BY clause lets users group in one dimension, but it doesn't addressmore complex, multidimensional grouping or the ability to generate histogram orpivot table result sets. Version 6.5 includes two new SQL operators, CUBE andROLLUP, that address these aggregation needs.

DB Maintenance Plan Wizard
Besides the SQL WWW Page Wizard, version 6.5 includes a Database MaintenancePlan Wizard that lets you automate common administrative tasks with minimumeffort. The wizard helps automate running the Database Consistency Checker(DBCC), managing database and transaction log dumps, and other importantday-to-day tasks such as running periodic update statistics.

As a result, the Database Maintenance Plan Wizard will greatly affect smallorganizations without full-time database administrators. By simplifyingmanagement of necessary administrative functions, Microsoft is helping sites ofall sizes use SQL Server effectively. This wizard uses a command-line executablecalled sqlmaint.exe to generate the tasks that SQL Executive Service manages andschedules.

The Database Maintenance Plan Wizard begins by asking how you use your dataand how often you update it. With your answers, SQL Server schedules an UPDATESTATISTICS job to run at appropriate intervals. This scheduling maximizes SQLServer efficiency during a query for database maintenance.

SQL Performance Monitor
SQL Server's tight integration with NT's Performance Monitor has been a bigselling point since SQL Server's initial release. Version 6.5 improvesperformance monitoring by adding 17 new counters in key areas.

SQL Server 6.5 even allows user-defined counters so you can measure whatyou consider important. You can add custom counters by creating specialstored procedures that return one integer value. The Probe login account runsthese procedures to get the appropriate statistic, and the Performance Monitorreports the appropriate integer value.

If you've ever had to set up and tune large SQL Server installations, youknow that properly sizing the procedure cache and tempdb can be frustrating. Theprocedure cache and tempdb are crucial in maintaining an efficient server. Butaccurately monitoring their use has been difficult--until now. The new procedurecache object has 10 new counters and a counter that measures the maximum tempdbspace used, so you can more accurately track these resources.

Transfer Management Interface
If you've worked with SQL Server for NT since version 4.2, you probably lovethe SQL Transfer Manager utility. Transfer Manager provides an intuitive,easy-to-use interface for reverse engineering a database into SQL scripts ormoving an entire database, data included, to another server. And you wereprobably disappointed when version 6.0 couldn't handle new features such asdeclarative referential integrity. If you incorporated any of version 6.0's newfeatures into your databases, Transfer Manager was essentially useless.

Version 6.5 includes a new transfer management utility built into SQLEnterprise Manager that provides all of Transfer Manager's old features and somenew ones. Now you can specify whether to create clustered indexes before orafter the transfer utility moves data among databases. Microsoft built thisfeature using the SQL-DMO Transfer Object to provide a powerful OLE Automationinterface for transferring whole databases or selected objects from one locationto another.

Because SQL Server exposes this functionality in a standard OLE interface,you can write your own custom transfer utility with any standard OLE Automationclient. In fact, you'll need to do so if you support 6.0 servers. Microsoft haslimited the Transfer Object's usefulness by not permitting a 6.0 server to actas a destination server during a transfer (maybe if enough people complain,Microsoft will change its mind.)

Microsoft SQL Trace
Another important addition, SQL Trace, is a graphical utility for monitoringand recording database activity at the connection level.

SQL Trace can display all server activity in realtime or use filters tofocus on the actions of particular users, applications, hosts, or SQL commands.

Administrators can monitor connections in three ways: connections shown onscreen, connections written to an activity log, or connections reproduced inT-SQL scripts to be rerun later so you can use the Trace utility to scriptcomplex activities that you run with a GUI tool. Administrators can also writecustom trace utilities with the xp_sqltrace extended stored procedure, whichsimplifies use of this tool.

Additional Features
Other features include point-in-time recovery; single table restore;standards compliance; support for redundant SQL servers, SQL Mail for Exchange,ANSI join syntax, and SNMP; enhanced SHOW PLAN output; data definitionstatements in a transaction; and ALTER TABLE syntax. Microsoft has raised thebar for NT database servers. SQL Server 6.5 is a powerhouse full ofenhancements, improved usability, and added functionality.