In the Real WorldStrategic Database Schizophrenia

In the Real World Strategic Database Schizophrenia

Microsoft has invested a substantial amount of developer time and energy in making the transition from Jet to SQL Server more palatable to Access users and developers. Computer press columnists especially ZD Net's David Coursey have derided Access as difficult to use. The simpler alternatives these pundits propose are flat-file managers, not RDBMSs. Designing and managing a relational database for business or even personal use isn't a simple task, but Access's success is in no small part due to the fact that it was easier to use than its early desktop database competitors, such as dBASE, FoxPro, Clipper, and Paradox. Wizards made tasks like creating basic forms and reports a relatively simple process. Of course, inclusion in the Microsoft Office Professional suite was another major factor in the demise of most competitive desktop RDBMSs.

Access 2003's improved user interface for designing SQL Server database objects, and easy addition of parameters to functions and stored procedures bring ADP close to parity with Jet's Table and Query Design views. The Link Table Wizard makes connecting SQL Server to other OLE DB-enabled data sources easier than using ODBC and Jet. The obvious problem with views over linked tables is their lack of updatability in Datasheet view. Forms bound to SQL Server with Jet linked-table views remain read-only. The first page of the Link Table Wizard states: "A Linked Server provides the most functionality, including data updates if the OLE DB data source allows...." Microsoft's failure to correct this defect in Access 2003 is difficult to fathom.

MSDE's lack of even rudimentary menu-based or graphical database management tools makes ordinary database administration chores difficult for developers and next to impossible for ordinary Access users. Using OSQL for server and database management requires familiarity with T-SQL data definition and data security language commands, plus experience executing SQL Server 2000 system stored procedures. The demise of Access 2000's basic tools for administering SQL Server security was a definitive step in the wrong direction for Microsoft's "strategic database." Only minimal developer effort would have been required to upgrade Access 2000's tools to Windows authentication for MSDE 2000. Thus, it's fair to conclude that ADP and MSDE 2000 are tactical, not strategic features of Access 2003.

Note

The MSDE User Manager utility (UserMan.exe) is a Visual Basic 6.0 program that uses SQL Server Data Management Objects (SQLDMO) to manage server logins and database permissions for MSDE 2000. The setup program to install UserMan.exe and its source code is in the \Program Files\Seua11\UserMan folder of the accompanying CD-ROM.

To learn more about the MSDE User Manager utility, see "Securing Projects with the MSDE 2000 Login/User Tool," p. 936.

Most users of MSDE in medium-sized and larger concerns probably will migrate to SQL Server 2000 Standard Edition when putting ADP into full-scale production, fulfilling Microsoft's strategic objective in providing MSDE with Access 2003. The $4,995 price tag for an unlimited-user license for the Standard edition undoubtedly will deter small firms from upgrading. The performance of MSDE, however, is likely to meet the needs of most small- to medium-sized businesses.

Tip

Small firms with 50 users or fewer should consider licensing Microsoft Small Business Server (SBS) 2000. SBS 2000 includes Windows 2000 Server, SQL Server 2000 Standard Edition, Exchange 2000 Server, and Internet Security and Acceleration Server 2000 (formerly Proxy Server). A server and five client licenses costs US$1,499 and 20 additional client licenses cost US$999. (SBS 2000 is limited to a total of 50 users.) All Access 2003 clients that connect to or replicate with the SQL Server instance must have a client license. For more information on SBS 2000, go to http://www.microsoft.com/sbserver/.

Where SQL Server 2000 shines is in ease of installation, maintenance, and administration. You can run medium-sized SQL Server 2000 installations without a trained DBA, and you'll probably find that SQL Server 2000 databases require less maintenance attention than shared-file Jet back ends. Unlike Jet, SQL Server 2000 databases are largely self-tuning and self-managing. Choosing the optimum set of indexes for server tables traditionally has been a hit-or-miss operation based on DBA intuition. If you have Enterprise Manager, SQL Server's Profiler and Index Tuning Wizard analyze table usage, and the Wizard recommends the fields to index. This is an especially important feature for databases having usage patterns that change significantly over time.

If you decide to stick with Jet front ends linked to MSDE 2000, instead of moving to ADP, you gain most of the advantages of SQL Server 2000 without the pain and suffering of rewriting complex Jet applications for ADP compliance. Query performance won't match that of views and stored procedures, but your crosstab queries execute without modification, and all Jet and VBA query functions remain intact. If MSDE provides the database back end, just create a simple Access project that connects to the SQL Server database to manage occasional table modifications.