Understanding the Role of SQL Server and ADP

Microsoft's announcement in mid-1995 that Access 2000 would include an "alternate database" led to a flurry of "Jet is dead" pronouncements in the computer press. These stories gained credence when members of the SQL Server 2000 team described their forthcoming product as "Microsoft's strategic database direction." The reality is that Jet obituaries are very premature. Jet plays a major role in more than 25 Microsoft products, and variants of the Jet database engine serve as the message store for Microsoft Exchange. Jet is likely to be alive and well, at least through the first few years of the twenty-first century.

Regardless of Jet's prospects for long-term survival, there's a definite trend toward the use of client/server back ends when database reliability is the primary objective. Production Web-based applications require client/server back ends for security and scalability. Thus, SQL Server 2000 will play an increasingly important role as even small firms migrate database applications to intranets and the Internet. You can expect Microsoft to add new ADP features to future releases, but don't look for any upgrades to Jet.

SQL Server Editions, Licensing, and Features

SQL Server 2000 comes in Evaluation, Windows CE, Desktop (MSDE), Personal, Developer, Standard, and Enterprise editions. The Standard and Enterprise editions run only under Windows 2000+/NT Server, and the Personal edition is restricted to use on workstations. MSDE is licensed only for a "stand-alone desktop device," and is intended to run under Windows 98, Windows NT 4.0 Workstation (with SP4 or later applied), Windows XP or 2000 Professional, or Windows 2000+/NT Server. Unlike Access 2000's version of MSDE (sometimes called MSDE 1.0), you can't install MSDE 2000 under Windows 95.

Note

graphics/globe.gif

Links from Microsoft's SQL Server Web pages at http://www.microsoft.com/sql/ offer product and licensing information for all SQL Server 2000 editions. The "Appropriate Uses of MSDE" page at http://www.microsoft.com/sql/howtobuy/msdeuse.asp has questions and answers regarding your rights or more accurately, lack of rights when using MSDE in production projects.


All SQL Server editions, except Windows CE, share a common code base, and data files are fully interchangeable between the versions. All editions use Transact-SQL (T-SQL), which includes many extensions to ANSI-92 SQL. The primary difference between MSDE and the other SQL Server 2000 editions (except Windows CE) is that MSDE doesn't include the SQL Server Enterprise Manager, which provides graphic tools for creating and managing databases and executing T-SQL queries. MSDE installs OSQL.exe, which lets you run T-SQL statements and execute stored procedures from a command prompt, but there's no online help for using OSQL. Access 2003 provides the graphic tools you need to create and modify MSDE databases, but not to manage MSDE user logins and database permissions.

For an example of using OSQL to add logins and database permissions to MSDE, see "Adding User Logins with the OSQL Utility," p. 776.


Note

Access 2003 doesn't include Access 2000's Tools, Security, Database Security menu choice, which opens the SQL Server Security dialog to let you set permissions on a project's database. In the absence of an SQL Server 2000 license, you can't use the Enterprise Manager tools with MSDE 2000.


Microsoft designed SQL Server 2000 for ease of installation and minimum maintenance. Unlike version 6.5 and earlier, you don't need to create fixed-size .dat files (devices) to hold SQL Server objects. SQL Server 2000 uses the NTFS or less commonly FAT32 file system to store all its objects. Files expand or contract automatically as tables grow or shrink, and you don't need to compact SQL Server databases to regain disk space after large-scale deletions, such as those that occur when you archive noncurrent data. SQL Server 2000 databases are self-tuning, so you don't need to be an accomplished database administrator (DBA) to get maximum performance from MSDE.

Benefits and Drawbacks of Access Data Projects

Chapter 19, "Linking Access Front-Ends to Jet and Client/Server Tables," describes the benefits of moving multiuser applications from shared-file to client/server back ends. There's little controversy among application developers that client/server architecture ultimately will replace all shared-file databases for production applications.

The benefits of moving from Jet front ends with linked client/server databases to ADP aren't so clear-cut. The newer OLE DB and ADO technology is more flexible and efficient than ODBC and Jet's Data Access Objects (DAO). ADO is compatible with scripting languages such as VBScript and JScript for Web applications, but DAO isn't. All application objects in the project share a single OLE DB connection to SQL Server, and consume inconsequential server resources when they're idle. Jet applications usually require multiple, active connections to the back-end server.

OLE DB and ADO don't offer dramatic performance improvements over Jet, ODBC, and DAO for databases having tables with a 100,000 rows or fewer. However, connecting projects directly to SQL Server lets you take advantage of precompiled views and stored procedures that do offer improved server response, especially with databases having tables with a very large number of records. If you expect your databases ultimately to grow to hundreds of thousands or millions of rows, your best bet is to connect directly to SQL Server with ADP.

ADP are best viewed as an advanced form and report engine for SQL Server. Unlike Jet, which can connect to any client/server RDBMS having an ODBC 2+ driver, ADP connect only to SQL Server. If you need to connect to an IBM DB2, Informix, Oracle, or Sybase RDBMS, you must set up SQL Server views on linked server tables. Using ADP requires a long-term commitment to SQL Server for your production databases.

To learn how to link SQL Server to other data sources, see "Linking Remote Servers," p. 842.


Access is unsurpassed as a RDBMS instructional tool, and ADP with MSDE combine to form an ideal method of learning up-to-date client/server database design and programming techniques. If your goal is to become proficient in managing client/server RDBMSs in general and SQL Server in particular ADP is a far better choice than working with linked Jet tables.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net