Tuning and authoring tools - Page 4

The SQL Server 7 release includes a Query Analyzer which is very similar in nature to the current ISQL/W. The graphical ShowPlan is available through this tool, as is the index tuning wizard (the latter can be run standalone as well.) The index tuning wizard is very clever. It got two out of three indexes right on one of my test cases (ones I give students in my classes). It did not propose the third index, and indeed, that index gives only a miniscule performance increase any way. It found the two "big bang" indexes very quickly. Casey Kiernan (Product Manager for all of the new tools) reported that the index wizard seemed to suggest much wider, covering, indexes than people would normally expect, but that they work very well.

The presenter stated that the features aren't yet cast in concrete. I have a little trouble with that statement as Beta 2 is supposed to be largely feature complete, and this seems to me to mean that there won't be substantial changes. But I could be wrong.

The goal is to have SQL Server everywhere. (Though they didn't talk about a Windows CE version, so I guess it won't be in my refrigerator any time soon!) The plan is to have the same product on all platforms. Pricing, packaging and licensing decisions are not complete yet. However this appears to be what is going to happen:

Desktop SQL Server - runs on Windows 9x and Win NT Workstation

SQL Server - runs only on WinNT server

SQL Server Enterprise - runs only on WinNT Enterprise Server

Plain SQL Server today runs on WinNT workstation; this probably won't be possible in SQL Server 7.0.

Desktop SQL Server will be a full-featured product that supports a single user or a small workgroup. It will use the same programming model as the server version and applications will run on all versions. It will be optimized for smaller systems and memory - apparently the goal is to get it to run on a 16MB machine. It will probably support a maximum of 5 users (hard limit, not expectation of how many can connect and have it still continue to function well.)

Limitations in the product will be those imposed by the platform. For example, clustering won't be supported, integrated security, async I/O and named pipes are not available in Windows 95/98. [The absence of integrated security seems to me that it could make some apps that run on Server fail on Win 9x.]

The presenter reported that the Win 9x version has been running on some machine since January with no signs of DB corruption. The desktop version will have the same dependability, integrity and recoverability as the server version.

The automatic tuning features of the Server product and the automatic resource management (database and log autogrow and autoshrink, allocate and free memory depending on OS load) will be in the desktop product.

The desktop version will be able to do the same distributed heterogeneous queries as the server version.

Replication will be supported, but there are some limits. A database on SQL Server on NT Workstation or Windows 9x can publish only if one is using Merge replication (see forthcoming article.) For transaction-based replication, databases on the desktop platform can only participate as subscribers, they can not publish.

There is also to be an embedded SQL Server. This is a minimal version consisting of the database engine and core components. This is designed to allow people to build SQL Server into their applications "invisibly". It is intended to provide a lightweight, full function database with a small disk footprint (target: < 30 MB) and low memory requirements (2-5 MB under Operating System pressure). The embedded version can be built into ISV applications and is the version available through the next version of Microsoft Access.

The presenter also talked about how SQL Server will integrate with the next release of Access. There will be a setup option to use the SQL Server engine; it will not be the default. Access can use a local or a remote SQL Server. The Access forms and reports will still be available, and the "DaVinci" design tools [Visual Database Tools] will be available for tables, queries, views, schema, stored procedures. It will be possible to do basic SQL Server admin (security, replication, database creation, backup and restore) from the Access UI.

Access 9 will connect natively to OLE/DB data sources (I presume this means without JET). Application objects will be stored in an ADP (Access Database Project) file. This can include the connection to SQL Server. There will be client-side caching for form and table browse. There will be updateable snapshot cursors, local filtering, sort and search, and "optimistic updates". [I assume this means something like the client batch libraries in VB 5.]