No, Hydra's not a B-grade remake of an old Godzilla movie. It's Microsoft's latest release of SQL Server 6.5 (formerly code named Hydra). Despite the small bump in version number, version 6.5 includes major enhancements that programmers and administrators can incorporate quickly and easily into their databases. Some important 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, an
intelligent assistant that helps you publish data on the Web, using standard
Hypertext Markup Language (HTML) pages. The SQL Server development team added
the wizard in response to Bill Gates' decree that all Microsoft products be "World
Wide Web enabled."
The SQL WWW Page Wizard asks questions to obtain data to insert into the HTML page. Version 6.5 provides a visual query builder, but experienced SQL developers may want to enter a Transact SQL (T-SQL) query or stored procedure directly. Then a dialog asks you to provide a publishing schedule, which you can use to publish an HTML page for one-time-only release, refresh a page on a predefined schedule, or update a page when the underlying data changes.
The SQL WWW Page Wizard generates HTML code by calling on three new stored procedures: sp_dropwebtask, sp_makewebtask, and sp_runwebtask. The result is an easy-to-access interface for generating Web pages from any program that can call a stored procedure, including Visual Basic (VB), Delphi, and most development environments that access Open Database Connectivity (ODBC) data sources. For example, developers can call these stored procedures to create their own Web wizard.
Internet Database Connector
Microsoft's new Internet Information Server (IIS) is another manifestation
of Internet commitment. IIS is available for free at www.microsoft.com and will
be part of NT 4.0. IIS includes Internet Database Connector (IDC), which lets
IIS access data from any ODBC-compliant data source.
Although IDC is not new to version 6.5, it's significant because it simplifies Web integration. IDC uses Microsoft's new Internet Server API (ISAPI), which lets you create server-based applications that users can activate from any standard Web browser. The ISAPI application grabs data from the client'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), but superior in two important ways. First, ISAPI is a Windows-standard API, so most programming 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 their CGI counterparts because ISAPI applications can encapsulate their logic in standard DLLs that load once at runtime. This initial loading eliminates the need to start a separate process for each request and reduces the processing overhead that can bog down busy servers.
IDC relies on a new DLL, httpodbc.dll, for data access. This DLL uses two file types: IDC (.idc) files to control how users access the database and HTML Extension (.htx) files to control construction of the output Web page. The best way to learn about IDC for creating database-ready Web pages is to experiment with the sample applications that ship with IIS.
I recommend that you look at the Guest Book application, a handy program that sets up a user registration form so users can insert, modify, and retrieve data from an ODBC-compliant database. The Guest Book .idc file references an ODBC data source and provides login information. A select query in the file returns an HTML-formatted result set to the Web browser. This .idc file will also 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:
+SELECT
au_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 developers
understand its power. Microsoft has significantly improved SQL's OLE
capabilities. OLE Automation is a standard mechanism that lets you take
advantage of and access data from one application (the automation server) while
you're in another (the application controller).
In version 6.0, SQL Distributed Management Objects (SQL-DMOs) let developers control most aspects of the server from an OLE Automation interface. SQL-DMOs (similar to "functions" in a 3- or 4GL environment) have adjustable properties that describe their state and methods and affect their actions. SQL Server 6.5 adds to this by providing several OLE-based enhancements. Two new DMOs are BulkCopy and Transfer.
The BulkCopy Object: The BulkCopy Object provides an interface from an OLE-controller application for high-speed data transfer in and out of SQL Server. By setting advanced properties, the user controls key aspects of the data transfer process such as the type of BulkCopy Object (e.g., character or native mode), whether to truncate the transaction log before data loading, and whether to drop nonprimary key indexes before data loading. If the user drops existing indexes, the BulkCopy Object automatically re-creates them after data loading.
The Transfer Object: Developers often need to transfer specific objects or even entire databases between servers. Using 25 properties, the Transfer Object lets you control all aspects of such transfers. Listing 1 shows how easily you can move databases around your network with the Transfer Object. In fact, the transfer capability built into the new SQL Enterprise Manager is based on this Transfer Object.
Extended Stored Procedures
An important version 6.5 enhancement is that you can create extended stored
procedures. These procedures rely on languages other than T-SQL (the native
programming environment for SQL Server) to encapsulate business processing in
server-based objects. Previous releases of SQL Server forced developers to write
these extended stored procedures with C or C++, which limited the procedures'
mainstream use.
Although nothing is inherently difficult about writing code in C, many developers and corporate IS shops are more comfortable with high-level tools such as VB. Now that you can build extended stored procedures with VB, these procedures will become more common in client/server applications. Listing 2 shows a VB-generated extended stored procedure (identified as xp) that echoes a parameter the user supplies.
Replication
Microsoft added replication in version 6.0 to let users copy databases
across servers. Version 6.5 expands this idea by letting you distribute data to
ODBC-compliant data sources. Although the replication publisher must be the SQL
Server that maintains the master data copy, you can now publish data to
databases such as DB2 and Microsoft Access. With replication, for example, your
sales representatives in the field can get the latest data every time they dial
in.
Distributed Transactions
A big challenge facing distributed database architects is ensuring the
quality of data while maintaining performance and availability. Although
replication was in SQL 6.0, many critics contend 6.0 didn't provide the same
level 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-processing
monitors such as Tuxedo. DTC also lets you write distributed transactions and
remote procedures that you can control from a central location.
A major difficulty with managing a distributed transaction is getting several entities to agree on the transaction's success or failure. If any component fails, the application resource manager must roll back the entire transaction to ensure its atomic nature. The DTC manages this process in a distributed environment by letting application resource managers register transactions. Then the DTC tracks the state of global transactions. A two-phased commit (2PC) protocol lets all registered resource managers commit or roll back transactions under their control.
Developers can initiate distributed transactions in one of two ways. They can incorporate API functions in ODBC and DB-Lib, or they can issue one of two new T-SQL commands: BEGIN DISTRIBUTED TRANSACTION or SETdistributed_proc_transaction. Both let developers tell SQL Server to register the next transaction with the DTC. The following example shows how to update data 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 to users frustrated by difficulties supporting distributed data. Without a lot of low-level DB-Lib programming, the DTC lets you do things with SQL Server that were never before possible.
Data Warehousing
Data warehousing is the latest buzzword describing the underlying database
capabilities necessary to perform online analysis of sales, inventory, and other
critical business data from various systems across an enterprise. Faster
hardware and a new generation of online analytical processing (OLAP) tools help
make this market segment a hotbed of activity.
SQL Server 6.5 integrates data warehousing, solving the problem of data aggregation. Most queries in a warehouse environment return highly aggregated data, 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 address more complex, multidimensional grouping or the ability to generate histogram or pivot table result sets. Version 6.5 includes two new SQL operators, CUBE and ROLLUP, that address these aggregation needs.
DB Maintenance Plan Wizard
Besides the SQL WWW Page Wizard, version 6.5 includes a Database Maintenance
Plan Wizard that lets you automate common administrative tasks with minimum
effort. The wizard helps automate running the Database Consistency Checker
(DBCC), managing database and transaction log dumps, and other important
day-to-day tasks such as running periodic update statistics.
As a result, the Database Maintenance Plan Wizard will greatly affect small organizations without full-time database administrators. By simplifying management of necessary administrative functions, Microsoft is helping sites of all sizes use SQL Server effectively. This wizard uses a command-line executable called sqlmaint.exe to generate the tasks that SQL Executive Service manages and schedules.
The Database Maintenance Plan Wizard begins by asking how you use your data and how often you update it. With your answers, SQL Server schedules an UPDATE STATISTICS job to run at appropriate intervals. This scheduling maximizes SQL Server efficiency during a query for database maintenance.
SQL Performance Monitor
SQL Server's tight integration with NT's Performance Monitor has been a big
selling point since SQL Server's initial release. Version 6.5 improves
performance monitoring by adding 17 new counters in key areas.
SQL Server 6.5 even allows user-defined counters so you can measure what you consider important. You can add custom counters by creating special stored procedures that return one integer value. The Probe login account runs these procedures to get the appropriate statistic, and the Performance Monitor reports the appropriate integer value.
If you've ever had to set up and tune large SQL Server installations, you know that properly sizing the procedure cache and tempdb can be frustrating. The procedure cache and tempdb are crucial in maintaining an efficient server. But accurately monitoring their use has been difficult--until now. The new procedure cache object has 10 new counters and a counter that measures the maximum tempdb space 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 love
the SQL Transfer Manager utility. Transfer Manager provides an intuitive,
easy-to-use interface for reverse engineering a database into SQL scripts or
moving an entire database, data included, to another server. And you were
probably disappointed when version 6.0 couldn't handle new features such as
declarative referential integrity. If you incorporated any of version 6.0's new
features into your databases, Transfer Manager was essentially useless.
Version 6.5 includes a new transfer management utility built into SQL Enterprise Manager that provides all of Transfer Manager's old features and some new ones. Now you can specify whether to create clustered indexes before or after the transfer utility moves data among databases. Microsoft built this feature using the SQL-DMO Transfer Object to provide a powerful OLE Automation interface for transferring whole databases or selected objects from one location to another.
Because SQL Server exposes this functionality in a standard OLE interface, you can write your own custom transfer utility with any standard OLE Automation client. In fact, you'll need to do so if you support 6.0 servers. Microsoft has limited the Transfer Object's usefulness by not permitting a 6.0 server to act as 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 monitoring
and recording database activity at the connection level.
SQL Trace can display all server activity in realtime or use filters to focus on the actions of particular users, applications, hosts, or SQL commands.
Administrators can monitor connections in three ways: connections shown on screen, connections written to an activity log, or connections reproduced in T-SQL scripts to be rerun later so you can use the Trace utility to script complex activities that you run with a GUI tool. Administrators can also write custom trace utilities with the xp_sqltrace extended stored procedure, which simplifies 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 definition
statements in a transaction; and ALTER TABLE syntax. Microsoft has raised the
bar for NT database servers. SQL Server 6.5 is a powerhouse full of
enhancements, improved usability, and added functionality.
| SQL Server 6.5 |
|
System Requirements: 486 or higher processor, Windows NT Server 3.51 or later,
16MB of RAM (32MB with replication), 80MB of free disk space (95MB with books online),
CD-ROM drive Microsoft * 206-882-8080 Web: http://www.microsoft.com Price (estimated retail): Five-client access license: $739, SQL Server Internet Connector: $2999 |
