Troubled and uncertain times have seemed to dog the major players in the relational database game lately. Try as they might, the RDBMS giants can offer nothing that excites anymore. On the contrary, the most recent releases of Informix, Oracle, and Sybase have either backfired or elicited barely a yawn from their customer base. Informixıs Universal Server release caused a major reshuffling at the highest levels in the company and forced the corporation to back off on its universal database approach (at least for now). Determined not to let Informixıs cloud follow it, Oracle pulled out all the stops at its unveiling of Oracle8 and made barely a peep about extended datatypes. And yet, once all was said and done, most of the crowd at the Radio City Music Hall launch said Oracle seemed to be pushing the network computer more than Oracle8. One thing is for sure: the adoption of Oracle8 by both existing clients and new customers is going much more slowly than Oracle would like. In fact, Oracle has stopped offering version 7 for new sales and has gone to 8 alone in hopes of pushing its installed base forward. Finally, the Emeryville, California-based Sybase has given its investors some restless nights with its less-than-thrilling financial reports, even though its database engine has never looked better. Troubled and uncertain times indeed.
On the heels of all the turbulence surrounding the most recognized names in the database market, another company is ready to step out and show the information systems community its new baby. Rarely is it easy to feel sorry for Microsoft, but watching all its competitors fall flat with product upgrades had to put the fear of God into the hearts of SQL Serverıs product managers. What, if anything, would a winning ticket have to include? How could the company charm its existing clientele and also raise the eyebrows of potential customers who have yet to see a SQL Server install window?
When you look over version 7.0 of SQL Server ı or Sphinx as itıs been called ı various product strategies begin to come to light. First, something that is not news is that version 7.0 continues in its predecessorıs footsteps by running its enterprise-styled engine only on Windows NT (although a scaled-down version is available for Windows 95 users). Microsoft never said it had the best database ı only the best database that runs on Windows NT. Is this a bad thing? Back when Windows NT was a suspect enterprise operating system, it might have been, but no more. Look at any of the operating system forecasts that are produced by the major IS think tanks, and youıll notice basically flat lines describing Unix, NetWare, and mainframe system growth. But the Windows NT servers line goes decisively upward. As Windows NT rises, it canıt help but lift SQL Server with it. On the Windows NT front, Microsoft isnıt going to compromise by porting to Unix, and this may prove to be an incredibly wise decision.
What about the base engine itself? Has Microsoft tried to do anything fancy such as appending universal database characteristics or embedding object support? Although the SQL Server engineers have addressed these aspects somewhat, the message in version 7.0 is that the kernel remains relational at its core. The competitors of SQL Server have recently stated that their sales have slowed because the relational database market has matured and past growth will not likely reoccur. If that is true, then why did Microsoft see its database sales in the third quarter of last year increase by 125 percent compared with the same period a year ago (according to the January 12, 1998 Information Week article, "Database Sales Slow"). The minds behind SQL Server 7.0 still see a lot of room for growth in their product, and instead of trying to get fancy, they have concentrated on making their engine perform faster, increasing its scalability, and plugging holes that their competitors previously exploited. Version 7.0 was also constructed to provide more flexibility and power, while at the same time retaining the ease of use that makes SQL Server a hit in shops that arenıt blessed with strong DBA support. No one can find fault there.
The final two areas that stick out in the new release revolve around OLAP processing and the Internet. Although OLAP has taken some time to get fully up to speed in many corporations, the market for such products jumped 40 percent in 1997 and nothing but steady gains are predicted in the years that lie ahead. Clearly seeing this trend, Microsoft unveils with SQL Server 7.0 its entry into the OLAP market ı Microsoft OLAP Server (also known by its nickname, Plato). Bundling Plato with SQL Server could spell big gains for Microsoft, and system gurus are already proclaiming that Plato will bring OLAP farther into the corporate mainstream. As far as the companyıs SQL Server and Internet strategy is concerned, Microsoft clearly wants to be the Internet database of choice and has extended its support for the Active Server Pages (ASP) product and provided easier methods of replicating data across the Internet to anonymous subscribers.
Are these the types of features that will produce a standing ovation from the corporate database world, or will SQL Server 7.0 be met with the same lack of enthusiasm that greeted its rivalsı new releases? Letıs peer under the covers a little more and reveal exactly what Microsoftıs latest offering can really do and see if itıs got what it takes to be a hit.
Although the complete version 7.0 is not out the door yet, Beta 2 has enough of Microsoftıs enhancements to show what the Redmond folks have up their sleeve. Microsoft has been trying for a long time to revamp SQL Serverıs image from a departmental-at-best database to one that can be trusted at the enterprise level. Many of the changes in version 7.0 are aimed at overcoming the stigma of being a database that plays in the minor leagues. The trick is producing a database engine sophisticated enough to handle complex business requirements, reliable enough to be trusted with critical systems, and easy enough to implement and maintain that precious analysis and development time isnıt wasted on configuration, tuning, and other housekeeping issues. Once these basic requirements have been satisfied, other heavy-duty prerequisites must be hurdled such as large implementation scalability (heavy user base coupled with large data volumes and intense transaction loads) and unique support for both OLTP and DSS applications. SQL Server has made great strides toward these ends.
With version 7.0, Microsoft has plugged a number of holes in SQL Serverıs armor that rivals used to capitalize on when making product comparisons, and in doing so it has narrowed the feature gap considerably. A large change that should be welcomed by all is the implementation of dynamic row-level locking for table and index entries. Previous releases of the database were able to lock at the row level on inserts only, but SQL Server now has the capability to lock individual rows for all types of SQL requests (instead of entire pages), with the intended result being more concurrency and less chance of the dreaded lock contention that can occur in busy OLTP systems. The RDBMS will automatically choose which level of locking is optimal given the situation and will have the ability to lock at the row, page, multiple page, or table level. Unlike other databases that let the DBA specify unlimited row-level locking through a databaseıs configuration file, SQL Server decides when to lock at the row level. Developers do, however, have the option specifically to request row locking in their SQL statements using a new programming option called ROWLOCK. The one minor flaw in the new lock implementation of SQL Server is that readers may still block writers. This is in contrast to Oracle, which constructs a SELECT query from both committed data and information contained within its rollback segments, thereby returning a result set that is based on the instant the query was issued. However, SQL Server does let readers get around this problem by allowing both dirty reads as well as providing a new locking option for SELECT statements called READPAST. This option permits data locked at the row level by another task to be skipped in the query.
Row-level locking has become necessary in version 7.0 because of another enhancement, which is a larger page size of 8K (as opposed to 2K in previous versions of SQL Server). More database vendors are going to large page sizes primarily because the relational engine can read in more I/O during a data request, which then reduces response times for large table scans. The page size in SQL Server is not configurable, however, like it is in other databases such as Oracle. As long as row-level locking is available to SQL Server, a larger page size should be a plus in OLTP and data warehouse environments. However, should the kernel incorrectly choose to lock at the page level, the potential to have lock contention problems will increase by fourfold.
One thing that is quite noticeable as you look at the new features of SQL Server 7.0 is how many things the server software automatically handles. Whether it is choosing the locking strategy for a SQL statement or allocating more resources for SQL Server when user demand becomes heavy on the server, Microsoft has attempted to create an intelligent database engine that examines and adjusts itself to satisfy whatever requirements are being placed on it at a given time. This approach can be a double-edged sword. If the kernel makes correct decisions, a DBAıs beeper may remain silent when it would have otherwise beckoned him or her to return to work. If a wrong choice is made, however, that same beeper may interrupt what would have otherwise been a restful nightıs sleep for the administrator. The other battle that such an approach may cause revolves around the question of control. For shops without adequate DBA support, a self-adjusting database server is a great idea. But for those shops with seasoned DBAs, the idea of a database making choices regarding resource allocation and other such important decisions will cause some discomfort. Administrators neednıt really worry though because Microsoft has mostly allowed DBAs to control whether or not SQL Server decides whatıs best for the server.
Other than row-level locking, what are some of the other database functions that SQL Server will automatically handle if instructed to? A dynamic lock-related feature is that Sphinx will increase the number of lock resources when needed, which should eliminate the hated SQL Server OUT OF LOCKS error message. Another big area that can put a stranglehold on database performance is memory allocation. In previous versions of the database, memory was a fixed element whose allocation could only be changed by altering the configured value and cycling (or shutting down and restarting) SQL Server. Version 7.0 introduces the concept of dynamic memory management whereby the kernel will automatically detect a shortage of memory resources and increase the amount of memory allocated to SQL Server. This is supposed to be done in a way where stolen memory does not induce the server to page and swap and, in addition, once the heavier memory demand has passed, SQL Server should release its acquired additional RAM back to the operating system. Again, for untrusting DBAs, this option may be configured in the previous versionıs nondynamic manner.
One other area of dynamic management that has found its way into the new version of the database revolves around disk space management. As most DBAs will tell you, when critical database-related jobs crash in the middle of the night, the cause can many times be traced back to a space shortage. To help make problems like this a thing of the past, SQL Server has changed the way that databases and their accompanying transaction logs use space. First, gone are the logical database devices that were the first step in space allocation. In their place, Microsoft has chosen to let databases and transaction logs be defined using normal operating system paths and file names. The underlying file properties may be set to let them expand automatically when more space is necessary with the growth increments being specified by the administrator. Growth may be restricted to a specific amount or unrestricted (thereby only constrained by the serverıs available disk space). Microsoft is playing catch up to some of its rivals who have offered this autoexpand feature for some time. But regardless, for any DBA who has had a database freeze because of a full transaction log, this enhancement will be welcomed.
The one thing commonly heard about SQL Server is that it is fine for small to moderate data storage needs but lacks the moxie to get things done at the enterprise level. Some administrators that currently use 6.x versions of the database would find fault with this statement and point to SQL Server databases that run just fine at the hundred or so gigabyte level. Still, the engineers at Microsoft knew that SQL Server needed some beefing up and that there were some feature gaps in the product that needed to be fixed before the database would be ready to serve large amounts of corporate data. Microsoftıs main strategy can be found in its Data Warehousing Framework, which offers a number of new and existing products as well as an extensible set of COM-based interfaces to ease the integration and management of data warehouses and data marts. The entire framework will not be available with the initial release of version 7.0, but there is enough to give customers a taste of whatıs in store.
Beginning at the core, SQL Server has been overhauled to offer intraquery parallel execution of a userıs single query across a serverıs multiple processors. With all the new industrial-strength Windows NT servers being offered by Compaq, Hewlett-Packard, and others that house four, eight, or more processors, the ability to exploit multiple CPUs in processing data warehouse-styled queries becomes extremely important. SQL Server 7.0ıs parallelism is enabled by default and is set at the server level, while databases such as Oracle provide a somewhat more granular approach by allowing a degree of parallelism to be set at either the table level or controlled through SQL hints. Microsoft definitely takes the more automated approach, which should find favor with the "hands off" crowds in IS shops. As SQL Serverıs optimizer inspects a query, the engine determines whether the proposed SQL statement will benefit from a parallel operation. If so, SQL Server embeds exchange operators into the execution plan. These operators control data redistribution, flow control, and process management for parallel tasks. SQL operations run in parallel will (obviously) use more than one Windows NT thread, with the actual number of used threads decided by the server at query plan execution time.
Parallel processing also makes itself known in SQL Server 7.0 in backup and restore operations. This often-overlooked area can be a real bottleneck for DBAs who must back up monolithic warehouses and not-so-small data marts. Parallel processing can speed large backups along so the window required for these sometimes resource-intensive tasks can be minimized. And should a database recovery be necessary, parallel execution in Sphinx can shorten the downtime that is required during a large databaseıs restore operation. As an aside, other improvements in SQL Serverıs backup and restore functionality include incremental backups that capture only changed data since the last complete backup (which, again, should shorten backup times) and restore utilities that create the databaseıs underlying operating system files as well as rebuild the backed up database.
Assisting parallel execution in SQL Server 7.0 are new join operations designed to improve query performance in large databases. Microsoft is playing catch up again with its rival databases in this area because hash and merge joins (along with nested-loop joins, which were present in 6.5) have been offered for quite some time by Oracle and others. Regardless, the availability of these join methods should help SQL Serverıs scalability and give a boost to queries that were held back by the nested-loop methods. In addition, the optimizer has been enhanced to recognize the common star and snowflake schemas so query execution plans based on these data warehousing mindsets will run more efficiently.
Version 7.0ıs database limits have also vastly expanded from previous releases for those wishing to test the upper boundaries of Windows NT data warehouses. The theoretical maximum database size has increased from one terabyte to more than 1,048,516TB, and the maximum file sizes for data and log files have increased to 32TB and 4TB respectively. Other things that should help data warehousing performance include the already mentioned 8K page size up from 2K and 64K extents (the amount of contiguous space SQL Server can grab for space allocations), both of which should increase I/O speed for large table scans as well as produce more benefits in SQL Serverıs read-ahead technology. Because data warehouses are typically hungry for memory, Microsoft has also increased the amount of RAM that SQL Server can address ı up to 32GB of memory with 64-bit memory addressing is available when running Windows NT 5.0 on Digitalıs Alpha hardware.
With many of its major competitors offering support for extended data types (even though the numbers using such functionality have been fairly small), Microsoft has addressed the universal database concept with version 7.0 of SQL Server but in a different fashion. Rather than choosing to store complex data types within the database itself, Microsoft has provided COM-based hooks from SQL Server to nonrelational data structures, allowing them to be referenced by the relational engine and yet remain in their current form and location. This method, argues Microsoft, is better than storing the structures within the database because it allows complex objects to exist and be used outside the database and yet be available to the RDBMS when needed.
Absent data warehouse features in SQL Server 7.0 include bitmap indexes and partitioned tables and indexes. Bitmap indexes produce speedy results for queries referencing columns that contain low-cardinality data. Partitioning is useful for tables and indexes that contain logical data separations (such as monthly sales data) that can be physically segregated in the database so scans can be restricted to a particular partition. The end result, of course, is faster response times. Microsoft plans to add these features but has given no specific timeframe.
Database companies always focus on how much data their engine can handle and other such scalability issues when the topic of data warehousing comes up, and you canıt really blame them because that is what many IS managers want to see. Few ever seem to be concerned about how legacy data is going to find its way into a warehouse or data mart ı even after all the corporate horror stories about the difficulties in transferring, cleansing, and maintaining heterogeneous information for data warehouses. Recognizing this need, Microsoft has bundled a toolset called Data Transformation Services (DTS) within version 7.0 whose purpose is to help provide relief for data movement and cleansing headaches. Data may be imported into or exported from SQL Server and any other data source reachable by OLE DB or ODBC. Data movements may be performed interactively or scheduled to occur on a regular basis. Intelligent data scrubbing may be programmed into movement operations by applying a set of transformation operations designed to massage data into the necessary formats required by the target server. Developers may use a variety of programming techniques to construct transformation routines including JScript and VBscript. As a final touch, DTS functions may be integrated with the Microsoft Repository to maintain a source of metadata that describes any and all data transformation routines.
There is no doubt that DTS will be a hit in data warehouse and data mart shops, especially because it positions SQL Server as just the hub for movement between databases and not actually a data source or destination. That has to wrinkle the brow of vendors who specialize in data transformation, migration, and cleansing products such as those from Informatica Corp. Considering that DTS is free to SQL Server users and that those specialty vendors offering high-end transformation products charge a hefty price for their solutions, you could say that Microsoft is yet again at the throat of its competition.
Microsoft recognized the need to connect and distribute information to both the Internet and corporate intranets early on and has already provided built-in functions such as the SQL Server Web Assistant, which helps publish database information in HTML formats. This feature has been enhanced in version 7.0 to allow the importing of tabular HTML data into a SQL Server database. Additional functionality includes the ability to read from and post to HTTP and FTP locations and support for multiple queries on a single HTML page.
Sphinx has also beefed up replication support to provide anonymous subscriptions across the Internet to SQL Server data and, in so doing, has put together a powerful yet easy-to-implement method of data distribution over the Web. When combined with new 7.0 COM interfaces that let non-SQL Server data be used in Sphinxıs replication infrastructure, SQL Server has the potential to become the focal point for distributing all types of data to corporate and Web-based clients. Miscellaneous Web-related enhancements include the ability to store HTML forms and GIFs in the database and limited administration support from Web browsers (more on this later).
Vendors can make their databases seem as slick as they want. But if a development staff finds it difficult to develop for or discovers that it lacks the mechanisms to perform robust work, corporate-level acceptance of the RDBMS will not happen. Knowing this, Microsoft has introduced a number of developer-related enhancements that will be met with open arms by both SQL coders and DBAs alike. Removing an unwanted column from a table (especially a table that has a ton of data and dependencies) has always been a pain for administrators, but version 7.0 of SQL Server will now allow a column and any data to be dropped from a table without disturbing the remaining columns and data. Other object-type improvements include the ability to allow a trigger, procedure, or view to be modified while leaving its current permissions and dependencies in place.
In the programming department, SQL Server 7.0 offers a slew of new options. For those of you who are heavy into stored-procedure work, version 7.0 will let you create procedures that reference nonexisting tables and debug procedure code in the Visual C++ or Visual Basic debuggers. Other help on the SQL analysis and debugging front includes more detail made available in SQL PLAN outputs so developers can see more of what the RDBMS optimizer is doing with their code.
More coding enhancements can be found with respect to database triggers. Earlier releases of SQL Server allowed only one trigger per DML statement (INSERT, UPDATE, and DELETE triggers). Now, however, more than one trigger can exist per DML command type, and this will allow developers to create more modular trigger code as opposed to cramming everything into one trigger type. Version 7.0 still lacks some of the trigger flexibility of Oracle that allows both BEFORE and AFTER triggers to be created instead of just AFTER-based triggers, which is all SQL Server supports.
Other miscellaneous items of interest to developers include a new command-line interface osql, which uses ODBC to communicate with SQL Server 7.0 (lovers of isql neednıt fear ı this utility is still present for those wishing to connect natively with DB-Lib). Also, table limits for joins have been increased ı up to 32 tables may now be referenced in join statements as opposed to the previous versionıs 16.
Microsoft may have been late to market with its relational database, but one thing it pioneered was the concept of making the relational engine easier to manage. The other RDBMS heavyweights original port was Unix, which left them with little desire to provide easy-to-use GUI interfaces into their products. Because SQL Server started out on graphical operating systems, the Microsoft engineers were able to design GUI administrative modules that truly made a DBAıs life easier. Almost immediately, Oracle, Sybase, Informix, and others began to bundle graphical tools with their database engines although, in the beginning, the tools were really only sad GUI representations of their Unix command line-driven interfaces. Today, Microsoft still reigns supreme when it comes to ease of administration and it has no plans on relinquishing its crown any time soon. Version 7.0 is packed full of more helpful database administration functions as well as new support for nagging enterprise-related management tasks.
DBAs will now find SQL Serverıs all-in-one administration tool, SQL Enterprise Manager (EM), running inside a GUI framework dubbed the Microsoft Management Console (MMC). The MMC is Microsoftıs environment for managing not only SQL server but other tools such as Transaction Server as well. Users that dealt with previous versions of Enterprise manager will find navigating the updated GUI a little different, but those using other Microsoft development tools such as Visual Studio will recognize the quasi-windows/browser interface immediately. (See Figure 1.) A subset of EM is also available in pure Web browser form for those wishing to perform administrative tasks in that manner. Microsoft accomplishes this feat by using DCOM and ActiveX components to mimic EM on the Web.
With SQL Server 7.0, Microsoft has further addressed the needs of administrators on an enterprise level. DBAs can now use a central SQL Server to communicate and distribute tasks to other remote servers. For example, a DBA can schedule backups for all servers from a single machine if they like. DBAs can monitor and tune all remote databases from a central location.
The scheduling piece of SQL Server, SQL Executive, has also been overhauled to provide help for those needing involved batch scheduling and job support. Renamed SQL Server Agent for version 7.0, the toolset includes enhanced support for job scheduling in the form of allowing either single or multiserver jobs to be created and controlled by a single SQL Server Agent. Present also is the ability to define multistep jobs complete with dependencies so developers can set up more of a rich batch environment to run mission-critical tasks. Job scripts can be written in VBScript, JScript, Transact-SQL, or NT O/S commands. You can program actions to take on either the success or failure of a job into the agent. You can also send notification to an operator (to review immediately) or to the NT event log (to review later). One thing is for sure: Automating such routines provides real help for applications that require database-related batch jobs under Windows NT.
Microsoft has added some 25 new wizards to SQL Enterprise Manager that streamline typical day-to-day administration tasks such as configuring replication, creating databases, building backup schedules, and more. As an example, one new tuning wizard provides the DBA with the ability to analyze a databaseıs current workload and suggests index additions to tables so performance be improved.
Final additions on the ease-of-use front for administrators are found in the areas of security and server analysis. With respect to security, DBAs can now easily use either Windows NT- or SQL Server-based security when managing login accounts. Previous versions of SQL Server forced the security choice as an installation option, but version 7.0 lets administrators simply choose a security model based on their preference or application situation at any time. Other easy-to-use security enhancements include the addition of SQL Server roles, which allows for increased flexibility in issuing privileges at the server level.
In terms of server analysis, proving that if you name an existing feature something new you can say itıs a new option, Microsoft offers SQL Server Profiler (formerly called SQL Trace). Regardless of its name change, SQL Server Profiler is a very valuable and easy resource for DBAs to use in collecting real time snapshots of server activity down to the actual SQL statements being issued.
Space considerations prohibit the detailed inclusion of several other 7.0 additions such as the ability for SQL Server to run under Windows 95, newer and more robust replication strategies, and the easy migration path that Microsoft has provided from earlier versions to their new release. The one negative concept I noticed in upgrading SQL Server, however, is that database dumps done in earlier releases are not readable by version 7.0. Most, if not all, of the other RDBMS vendors provide backward compatibility in this respect, so you can migrate data with greater confidence.
Admittedly, nothing earth-shattering or truly original can be found in version 7.0 of SQL Server. Then again, Microsoft has not exactly ever tried to be ground-floor innovative with most of its products. What the Redmond army specializes in is making a good idea better and easier to use and selling it at a price that canıt be beat. The question that must be asked with respect to SQL Server 7.0 is: Has Microsoft improved its database arsenal enough so that corporations can use the product suite for mission-critical applications? Looking over the list of improvements in Sphinx, the question must be answered in the affirmative. Going a step farther, adoption of version 7.0 is likely to be widespread, unlike the slow acceptance witnessed by Microsoftıs competitors with its latest offerings. While quasi-object support for extended datatypes may be of interest and solve the business problems of a small minority in the RDBMS community, row-level locking and greater ease of use are what all database users want and need. Although certainly not perfect, SQL Server 7.0 provides the flexibility, relational power, and continued ease of use that should put the product in the corporate winnerıs circle.
Figure 1. The enhanced SQL Enterprise manager running within the Microsoft Management Console.
What did you think of this article? Send a letter to the editor.