Evaluating the Benefits of Migrating to ClientServer Databases

Evaluating the Benefits of Migrating to Client/Server Databases

Modern client/server databases, typified by Microsoft SQL Server 2000, provide a much more reliable and scalable data storage environment than shared-file databases, such as Jet. The vast majority of production databases used by all but very small organizations follow the client/server model. Oracle currently claims the lion's share of the client/server relational database management system (RDBMS) business, but IBM, Microsoft, and Sybase each own significant market share.

Client/server technology offloads much of the data-processing workload to the server. When an RDBMS client instructs the server to execute an SQL SELECT * statement having WHERE clause criteria, only those rows that meet the criteria pass over the network to the client. If you replace * with an explicit field list, the RDBMS only populates query columns that correspond to the specified fields. Minimizing the amount of data transmitted to the client saves costly network bandwidth and improves performance, especially for remote users who access the database over low-speed connections.

Another advantage of migrating from conventional multiuser Jet applications to client/server back-end databases is elimination of routine compact/repair operations to remove deleted records from Jet tables. When you delete records from a table, Jet marks the records as deleted but doesn't remove them from the table. You must compact the database periodically to remove deleted records and regain the disk space they occupy. Using a client/server back end also eliminates the Jet database locking problems that often occur after a power outage or unscheduled shutdown when users are in the process of making changes to Jet tables.

Client/Server Reliability and Scalability Benefits

Reliability also called availability in this context is the most important property of a production database. The goal of most database administrators is to assure that the database is available to users at least 99.9% of the time. 99.9% (called "three nines") availability means that the database has a maximum downtime of 7.3 hours per month. Four nines reduces downtime to about 45 minutes per month, and five nines to five minutes per year. Achieving 99.99% or better database availability requires very costly server clusters, but it's reasonable to expect at least three nines from SQL Server 2000 running under Windows 2000 Server.

Database scalability primarily is hardware-related. You can increase the number of concurrent users without suffering a performance slowdown by increasing the amount of RAM, CPU speed, and number of CPUs in the server(s). Unlimited-use licenses for most RDBMSs are based on the number of CPUs. You can compare licensing costs of Oracle, SQL Server, and IBM DB2 for different software editions, and server CPU and CPU speed configurations at http://www.microsoft.com/sql/productinfo/pricecomparison.htm.

SQL Server 2000 Desktop Engine Features and Limitations

SQL Server 2000 Desktop Engine (called MSDE 2000 when referring specifically to this version of SQL Server 2003 with Service Pack 3) running under Windows 2000/2003 Server and the NTFS file system has the same level of reliability as SQL Server Standard or Enterprise Edition running on one machine. You can achieve similar reliability when running MSDE 2000 under Windows XP or 2000 Professional, if you dedicate the machine to running MSDE and don't use the machine to run desktop applications. The source code of MSDE 2000 is identical to that of other SQL Server 2000 editions; the scalability limitations applied to the freely distributable Desktop Engine don't affect its reliability.

Tip

One of the advantages of running MSDE 2000 or other SQL Server editions under Windows 2000/2003 Server is the capability to manage the server from a Windows XP, 2000, NT, 9x, or Me workstation by running Terminal Services in administrative mode.

You don't need to install Active Directory on the Windows 2000/2003 Server to run MSDE 2000 or other SQL Server 2000 versions. If your network uses Active Directory, installing MSDE 2000 on a member server, not a domain controller, devotes more of the server's available resources to database management and improves performance.

Microsoft limits the scalability of MSDE 2000 by restricting it to using a single CPU, regardless of how many processors you plug into your multiprocessing server or workstation. The Access 2003 help file states that MSDE 2000 "also limits database size and user workload." The maximum size of any MSDE 2000 database is 2GB, and MSDE 2000 "is designed and optimized for use on smaller computer systems, such as a single-user computer or small workgroup server." MSDE can't act as a transactional replication publisher, but this limitation isn't likely to affect most Access users and developers.

Note

As mentioned earlier, shared Jet databases have a fixed limit of 255 concurrent users. MSDE 2000 has a limit of five simultaneous batch operations, which translates to five users executing queries simultaneously. The sixth and higher concurrent users' queries are held in a queue pending completion of previous operations. There's no limit on the number of simultaneously connected clients. Windows 2000 and XP Professional have a fixed maximum of 10 inbound (client) connections, so the maximum number of simultaneous networked users connected to MSDE 2000 running under either of these operating systems is 10.

A much more serious limitation of Access 2003 is the lack of management tools for MSDE 2000. Access 2000's data projects provided a Tools, Security, Database Security command to let you add user logins and set user-level database permissions for the MSDE 1.0 version of SQL Server 7.0 with a set of dialogs. Microsoft removed this feature, which used elements of SQL Server 7.0's Enterprise Manager, from Access 2002 and MSDE 2000. Instead, Microsoft recommends use of the OSQL.exe command-line utility to add SQL Server user accounts, but provides no online help for OSQL with Access 2003. OSQL's arcane Transact-SQL (T-SQL) syntax for adding server logins and database permissions with SQL Server system stored procedures requires the command-line skills of a UNIX or Linux administrator.

Note

SQL Server 2000 Developer, Standard, and Evaluation editions include Enterprise Manager. The Developer Edition license, however, is restricted to development projects. Microsoft takes the position that you cannot use the Evaluation or Developer Edition's Enterprise Manager and other SQL Server tools with MSDE 2000 databases.