SQL Server 7: Is It for You?Embedded, desktop, and server versions make SQL Server 7.0 the logical choice for scalable VB database apps. by Ken Spencer Reprinted with permission from Visual Basic Programmer's Journal, 5/98, Volume 8, Issue 6, Copyright 1998, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com In the midst of agony at Informix, excuses from Oracle, and downsizing by Sybase, Microsoft's SQL Server 6.x sales just keep on growing. So what's the incentive for Microsoft to completely overhaul its budget-priced, client/server, database back-end? Like Windows, the objective is "SQL Server Everywhere." Microsoft has made SQL Server 7, the database formerly known as Sphinx, the first client/server RDBMS to span the entire gamut of Windows platforms, ranging from Win95 laptops to clustered NT Enterprise servers with dozens of Pentium II processors running multiterabyte data warehouses. Independent software vendors get an embeddable SQL Server 7, sans management tools and documentation, to compete with Pervasive Software's venerable Btrieve engine. But the biggest news is that the single-user variant of SQL Server 7 will become an "alternative engine for Access," offering millions of Office Professional users the dubious opportunity to substitute SQL Server 7 for the latest and greatest version of Jet. (For a quick look at SQL Server 7, see the sidebar, "SQL Server Pros and Cons.")
I've used every version of Microsoft SQL Server since the late 1980s, when it was known as "The Sybase Database Server for PC Networks" and ran under Microsoft's very bland flavor of OS/2. I've been a technical beta tester for every Windows NT version of SQL Server, beginning with 4.21a on NT 3.1 Advanced Server. In my view, the transition from SQL Server 6.x to 7.0 involves more fundamental changes to the product than all previous upgrades combined. Many of the new features were missing documentation in Beta 2, so I spent two days at Microsoft's SQL Server 7 Technical Reviewer's Conference to get Microsoft's slant on the new SQL Server architecture. (I also got the release from my beta NDA needed to write this article.) Paul Flessner, Microsoft's general manager of SQL Server development, opened the conference by saying, "SQL Server 7 is the largest non-1.0 release in the industry." Members of the SQL Server 7 development team went on to project hundreds of PowerPoint slides, interspersed with a few demos, to prove Flessner's statement. But SQL Server 7 also is one of the most delayed non-1.0 releases; back in May 1996, the product was scheduled to reach the market in the second quarter of 1997. The current schedule calls for Beta 3 of SQL Server 7 to be dropped on more than 30,000 MSDN Universal subscribers in the second quarter of 1998 and released to manufacturing (RTM) in the second half. Microsoft promises a "major [SQL Server 7] push at Tech·Ed in June," and I won't be surprised if Tech·Ed attendees also receive a copy of Beta 3. This article's illustrations and code examples use an "almost feature-complete" Beta 2 of SQL Server 7 running under Win95, Win98 Beta 3, WinNT 4 SP3 and Mini-Service Pack (on the Sphinx Beta CD-ROM), and WinNT 5 Beta 1.
One Size Fits All (Almost) SQL Server traditionally has been relatively easy to set up and administer; that's always been one of its major selling points. But involving desktop users with chores such as specifying the size of Master.dat and dealing with fixed-size database and log devices requires at least a modicum of DBA skills. Fixed-size device files are a holdover from the 15-year Unix heritage of the Sybase database engine. To eliminate the need to train desktop and laptop users as DBAs, as well as to take full advantage of NT security and File System (NTFS) for servers, Microsoft eliminated DeviceName.dat files. Databases now begin as a pair of conventional files, DatabaseName_Data.mdf and DatabaseName_Log.ldf, with default sizes of 4 MB and 2 MB, respectively. The minimum MDF file size is 3 MB in order to accommodate tables copied from the Model database. The MDF and LDF files grow automatically as you add data. More interesting, they shrink automatically as data and log space is freed, eliminating Jet's need for compacting databases periodically to recover space occupied by deleted records. File groups let you add space to MDF and LDF files from other drives or partitions. Unlike DAT files, which can store more than one database, the MDF/LDF pair is dedicated to a single database. Memory management is a feature that's especially critical on the desktop and laptop. Microsoft says, "the 1998 typical laptop is increasingly 233 MHz Pentium MMX, over 3 GB disk, and 64 MB memory." This may be Microsoft's 1998 laptop spec, but most desktop and laptop users I know are running at 150 MHz to 200 MHz with a 1 GB or 2 GB drive, and 32 MB of RAM. The engine development team's original goal was to match Jet 3.5's memory footprint, about 3.5 MB. Microsoft's Ron Soukup says that single-user SQL Server 7 "isn't as small as Jet, but it's in the same range," with a working set under 4 MB. One of the keys to improved memory management is a single, unified buffer pool. SQL Server 7 autoconfigures memory usage on startup, expands the pool to accommodate more database users, and responds to operating system pressure by contracting the pool when necessary. DBAs will appreciate that it's no longer necessary to specify a maximum number of simultaneous connections. Microsoft claims to have eliminated 22 manual tuning parameters with the new autotuning approach.
Upgrade Drudgery Pipelining and offlining are the two basic paths to SQL Server 7 upgrades. Pipelining uses a named-pipe connection between the two RDBMSs and requires sufficient free disk space to install SQL Server 7 (about 130 MB) and duplicates of your existing databases. Offlining means dumping your 6.x databases to tape, dropping the databases and their devices, then restoring from tape to SQL Server 7 databases. Offlining sounded like too much trouble to me, so I freed up as much space as possible on the OAKLEAF0 NT 4.0 main server to pipeline a conversion of my relatively small nwind and OLTP databases, derived from the Northwind database of Access 97 and Access 2, respectively. First, I set up a new account in the Administrator's group and logged on as SQLS70, not Administrator, to enable dual-version operation. Installation of an NT mini-service pack and SQL Server 7 on OAKLEAF0 took about 20 minutes, about 15 minutes of which was unattended. On completion, my Microsoft SQL Server 6.5 Start menu choice changed to SQL Server - Switch (Common) with two subchoices, Microsoft SQL Server 6.5 and Version Upgrade Wizard. After specifying upgrade options and the destination location in a few dialogs, the Wizard began the upgrade process. Watching the progress of the exceedingly complex upgrade operation made it clear that wizardless manual upgrading isn't in the cards. The Version Upgrade Wizard created nwind_Data.MDF, nwind_Log.LDF, oltp_Data.MDF, and oltp_Log.LDF in six and a half minutes. All indexes, triggers, stored procedures, and other database objects migrated perfectly. Pipelined upgrades of large databases reportedly run at 1 GB to 4 GB per hour on fast, multiprocessor servers (OAKLEAF0 has one 133 MHz Pentium). Microsoft claims the new database file system is "a solid foundation for the next 10 to 20 years," so future upgrades are likely to be much simpler.
Off to See the Wizard(s) The Data Transformation Service (DTS) Import Wizard eases the pain of traditional Bulk Copy Process (BCP) operations. You can select from a wide range of input formats, including Jet, xBase, Excel, Paradox, text, Oracle, or any ODBC-accessible table. I gave the Import Wizard a quick test by importing 26,000 rows of a test mailing list from a Jet 3.5 database. The import process completed in less than 30 seconds, and the Create Index Wizard generated a unique clustered index on the telephone number field in a similar period. Using Access 97 to export the same table to SQL Server 7 through ODBC took more than four minutes. OLE DB-based DTS is just one of the major performance improvements in SQL Server 7. A new Query Analyzer replaces SQL Server's aging, text-based ISQL_w. Query Analyzer color-keys SQL reserved words and delivers resultsets in a resizable grid for easier viewing. Microsoft is justifiably proud of its new query optimizer and OLE DB-based query engine that offers multi-index operations, merge and hash joins, and hash aggregations. Query Analyzer offers a graphical ShowPlan window that displays the decision tree for complex queries (see Figure 2). Passing the mouse over an icon displays a Properties sheet for the operation, which calculates a Total Subtree Cost in a mysterious currency known only to the SQL Server Relational Engine team. The ShowPlan window is read-only; you don't get to second-guess ShowPlan with your own optimization scheme. The Create View Wizard helps you write simple CREATE VIEW Transact-SQL statements, but right-clicking on SQL Server Views and choosing New SQL Server View opens a four-pane graphical window for generating VIEWs (see Figure 3). The da Vinci-based Visual Modeling Tools are updates to those included with today's VB5 Enterprise Edition and Visual InterDev. The panes display table relationships, table.column selections and criteria, Transact-SQL for the view, and a query result grid. Microsoft says that the next version of Access also will include the da Vinci tools to support the single-user SQL Server 7 as the "alternative engine." You can drag tables from a database selected in the MMC primary view to the upper pane, but I found it easier to type the table names into the T-SQL statement's FROM clause, then click on the upper pane to display the table/column boxes. You drag a primary key column over a corresponding foreign key column to establish table relationships and enable SQL-92 INNER JOIN syntax. Column name aliases and WHERE clause criteria go in the second pane; each action in the top two panes adds the appropriate T-SQL clauses to the third pane. When you're ready to test your VIEW, click on the Run button to execute the query and display the resultset in the bottom pane. Clicking on the Save button lets you name your VIEW and sends the complete CREATE VIEW Name AS SELECT ... statement to the server. I can't find a ready reason for Microsoft's provision of two dramatically different approaches to designing VIEWs. On the whole, I prefer the single-window graphical approach to the Wizard's stepwise method. The multipane window appears to me to be a cross between Access's Query Design window and the ODBC-based Microsoft Query tool. Access users will miss the drag-and-drop features of the Query Design window, the graphic elegance of the Relationships window, and the ability to specify cascading updates and deletions as JOIN properties. Version 7 of T-SQL still doesn't support SQL-92's ON DELETE CASCADE and ON UPDATE CASCADE predicates. SQL Server 7 supports multiple, recursive triggers for INSERT, DELETE, and UPDATE operations, but I couldn't find a Create Trigger Wizard to aid in writing complex referential integrity triggers. Publishing limitations prevent a full discussion of other important wizards for setting up replication, establishing database maintenance plans, creating SQL Agent (formerly SQL Executive) jobs, and generating alerts. I found all the wizards to be useful, especially for new features that weren't fully documented. Fortunately, the Microsoft Total-Cost-of-Ownership Police haven't required the SQL Server team to include popup animated "Database Assistants" in this version.
Serving OLE DB and ADO Setting up the Sphinx beta on Win9x or WinNT also installs the latest update to ADO 2.0 (the file name, Msado15.dll, is the same for both ADO 1.5 and 2.0), a collection of native OLE DB data providers, and ODBC 3.6, which doesn't differ markedly from prior 3.x versions. To check out the new OLE DB data provider for SQL Server, I wrote a simple VB5 app that creates an ADODB.Connection with the SQLOLEDB provider. The app takes three different approaches to fill an unbound MSFlexGrid control with data from the Orders table of the nwind database that I had upgraded earlier to SQL Server 7. To experiment with parameterized stored procedures and the ADODB.Command object, I used the Create Stored Procedure Wizard to add sp_orders to the nwind database:
Both ODBCDirect's QueryDefs and RDO's rdoQueries use { call sp_name ?, ?} ODBC escape syntax to execute stored procedures with input parameters. The ADODB.Command object takes a much more straightforward approach (see Listing 1). Specify adCmdStoredProc as the CommandType property, set the CommandText property to the name of the stored procedure, then specify the Size, Direction, and Value properties of Parameters(1) to Parameters(n). Parameters(0) is reserved for a return value, regardless of whether your stored procedure returns a value. I came up with some interesting speed differences displaying ADODB.Recordsets from stored procedures, Command objects, and Connection objects. Unfortunately, I can't spill the performance beans here because the early version I tested, according to Microsoft, hasn't yet been tuned for performance. But you canand shouldtry it yourself. Download the Sphinx app from http://www.windx.com, and give Beta 3 a test run with ADO 2.0. Plan on migrating your VB front ends to ADO to get the full benefit of SQL Server 7's performance improvements, but be sure to run a full set of speed checks before settling on a particular object approach.
Listing 1
At the low end, rumor has it that Jettoday's king of desktop databaseswill be dethroned by the single-user version of SQL Server 7. (For an opinion on why you should stick with Jet, see the sidebar, "Jet: It's a Proven Winner.") Like alternative automobile engines, alternative database engines are by no means a sure bet. (Remember the Wankel rotary engine?) Tens of millions of Access and VB users currently run Jet in both single-user and multiuser mode, and are likely to continue to do so well into the next millennium. So far, I see robust replication and embedded databases as the primary applications for desktop SQL Server 7. Regardless of the short-term acceptance of SQL Server 7 as a Jet replacement, count on Microsoft's marketing machine to achieve the goal of "SQL Server Everywhere."
SQL Server 7.0 has not yet proven itself to me as a desktop database engine, and it has at least a 50 percent larger memory footprint than Jet. Being such a robust server RDBMS, it might take a cycle or two for SQL Server on the desktop to really prove itself. SQL Server 7.0 has many core changes in such important areas as storage and OLE DB; it really is like a version 1.0. When SQL Server runs in "embedded" mode on Windows 95, all query processing will have to run locally, like Jet. It has proven difficult scaling SQL Server downward.
Should you start developing all your desktop and file server database systems in SQL Server? Should you migrate all your old Jet apps to SQL Server 7.0? The answer is quite simple: all "desktop" and file server database systems that you develop should continue to use the Jet engine. Jet is a proven File Server database with an excellent track record, and many tools exist for Jet development. Jet has optimizations for smaller types of database systems that SQL Server just does not have. Aggregates are one example of this. Until SQL Server "desktop" proves itself and evolves, until 128 MB of RAM and Pentium IIs are standard, and until we get a clear sign from Microsoft that Jet is dead, we should continue to develop with Jet on the desktop. SQL Server 7.0 running in Windows 95 embedded mode will have a memory footprint of around 7 MB. Jet's average is less than 3 MB.
Currently, I suggest you use the desktop SQL Server only when doing fast prototyping of a client/server system. Because it is the same engine, you can use Microsoft Access to rapidly build a prototype. (Microsoft Access will not support all the administration functions of SQL Server, but it will enable you to create an excellent prototype.) When you're ready to move the database to a server, simply change the location property in your code. Using SQL Server for your desktop applications is overkill. The Jet engine is just as fast, easier to program, and has a much smaller footprint. Stick with a proven winner.
S.F.
|