Steve Kirk
Microsoft Developer Network
Summary: Discusses the issues involved with converting the Duwamish Books database from Access 97 to SQL Server. (3 printed pages) Also covers:
In this article I'll discuss conversion of the Duwamish Books sample database from a Microsoft® Access 97 .mdb file to Microsoft SQL Server™ version 6.5. Although you don't have to actually perform this conversion (Setup programs for future phases will create SQL Server databases), an examination of the process suggests issues to be resolved in subsequent phases. If you do want to perform the conversion you will need SQL Server 6.5 (or the Workstation version included with Microsoft Visual Studio®, Enterprise Edition) installed on a computer running Microsoft Windows NT® version 4.0 Server or Workstation. You will also need the Phase 2 Duwamish Books database (db2data.mdb, available with the Phase 2 source code) and Microsoft Access 97 with the Upsizing Tools add-in (see www.microsoft.com/AccessDev/ProdInfo/AUT97dat.htm).
Conversion of the Phase 2 database is relatively easy with the Microsoft Access Upsizing Tools, and the ease of this step may de-emphasize the significant differences between a file-oriented desktop database management system (Access) and a server database engine (SQL Server). The Phase 2 applications reveal their desktop ancestry through the use of dynamic SQL rather than stored procedures, and through (limited) use of cursors to work on records. The use of cursors in these applications is limited—they use disconnected recordsets in many areas to reduce server load. The Phase 3 application moves to a more scalable transaction model where stored procedures provide faster execution and reduce round trips.
Changes to SQL queries due to differences between Access and SQL Server bring up another issue that will be addressed in Phase 3. Because the business logic application layer that contains SQL expressions is part of the Phase 2 executables, each application had to be modified and recompiled to work with the converted database. In Phase 3 the business logic layer will become a separate component, so application maintenance due to future database changes can be limited to one component.
The diagram in "A Phase-to-Phase Guide to the Duwamish Books Data Access Strategy" illustrates the upsizing step within the context of the overall migration plan. The diagram shows how changes to the database are coordinated with changes in other layers of the application and that the upsizing step will be followed by implementation of stored procedures and by database schema changes. The diagram also includes links to articles that document development of the database model up to this point.
Although the Upsizing Tools can create a new SQL Server database, I found that I could see what was being built in more detail by creating database devices and the new database outside of the Upsizing Tools.
From the SQL Server Enterprise Manager:
For greater detail about upsizing your Access database to SQL Server, see "Migrating Your Access Database to Microsoft SQL Server 7.0."
Modifications to SQL expressions are required due to differences between Access SQL and American National Standards Institute (ANSI) SQL, differences in field types between the systems, and SQL Server configuration.
Expressions that contain True/False also fail due to Access Yes/No fields being represented as a bit in the SQL Server database. A quick fix is to substitute 1 and 0 for True/False.
SQL Server configuration options affect how much conversion has to be done to the Phase 2 SQL expressions in order for them work with the converted database. SQL Server 6.5 Setup allows you to specify a sort-order option when the master database is created. Because the sort order can only be changed by rebuilding the master database (and all other databases), a change of sort order may not be feasible to accommodate an application that requires case insensitivity. The Phase 2 queries were developed in a case-insensitive environment and contain many case discrepancies that cause failure in a case-sensitive SQL installation.
The Phase 2 applications use an ODBC data source named Duwamish_Phase 2 that is created by the Phase 2 Setup program. You can redirect the applications to use the SQL Server database by creating a new data source with this name that points to the new database. The applications pass only the name of the data source (without user name, password, or database name) to the data access layer when accessing data, so this information must be included within the data source. A simple preliminary strategy is to configure the data source to use SQL Server authentication and the SA user name and password, and to set the default database to the new Duwamish SQL database.
Although a conversion of this type does improve performance, the primary result of the process is a list of issues to be resolved in subsequent sample phases. At this point the application does not efficiently use the SQL Server database, and changes to the database require that the applications be modified and recompiled. In Phase 3 these issues will be resolved as the business logic layer is broken out as a separate component.