Beyond the Upsizing Wizard

Microsoft Access is a popular database. Although Access is great for single-user desktop applications, it has limited performance, scalability, multiuser capability, and database size. You can overcome these limitations by converting your Access system to Microsoft SQL Server. (To keep this discussion simple, I concentrate on converting Access 97 to SQL Server 6.5, except where noted.)

You can convert Access databases to SQL Server databases in several ways. The easiest method is to use Microsoft’s Access Upsizing Wizard to move the database tables and the data. However, using the wizard is only one part of the conversion process. Before you use the wizard, you need to select a sort order, prepare the Access database, and install the wizard. After you use the wizard, you need to modify your client applications and test your new SQL Server system.

Selecting a Sort Order

Selecting a sort order is the first major preparation issue in an Access conversion project. The choice is critical to the project's success.

Access is a case-insensitive system. You can specify the name of an object (e.g., table, query, column) using any mixture of case, no matter which case you used to create the object. Customers.ContactName, CUSTOMERS.CONTACTNAME, and CuStOmErS.CoNtAcTnAmE all refer to the same table and column. Access also ignores case when parsing SQL statements and performing string comparisons.

SQL Server has many options for handling case issues. These options affect the way you write source code and how SQL statements execute. For example, case can be a factor when using Group By, Order By, and Distinct clauses in a Select query. In addition, the case of strings in string comparisons can affect SQL Server's behavior with unintended results.

Although you can install SQL Server as case-insensitive, you have several other options, including custom sort orders. Screen 1 shows the sort order selection screen for SQL Server that appears during the installation process. The best solution when converting Access databases is to install SQL Server with the Dictionary order, case-insensitive sort order. With this setting, SQL Server behaves like Access both when parsing SQL statements and when comparing strings.

However, other databases that other applications use can dictate the sort order. For example, applications migrated from UNIX-based versions of Sybase SQL Server use a case-sensitive or binary sort order. (Use the sp_helpsort stored procedure to determine the sort order that an existing SQL Server system is using.) Thus, you might have to use SQL Server in a case-sensitive mode and adapt the Access application to fit.

If you must use a case-sensitive SQL Server, you have three choices: Continue to use mixed case, use all uppercase, or use all lowercase. Although you can keep mixed-case object names, users must correctly remember and type mixed-case text. The simplest solution is to convert all object names to lowercase before running the Upsizing Wizard. You can perform this conversion manually or by writing a conversion program. However, you must also modify the case in all your client applications' inline SQL statements. (The section "Converting Access Queries and Inline SQL Statements" discusses this topic in detail.)

If you must use case-sensitive string comparisons, you might need to add Upper() functions to change string comparisons. Upper() is the SQL Server equivalent of the Access ucase() function. However, using the Upper() function on a column might prevent the optimizer from using indexes that include the column. You can minimize this risk two ways. First, you can store only uppercase data (or, in rare cases, lowercase data) by modifying client applications to store the proper case or by using insert and update triggers to set the proper case. Second, you can use duplicate columns. One column contains the data values you use for display; the other column contains the uppercase data values you use for comparison. You must use insert and update triggers to maintain the duplicate columns. You must also change all references to the duplicated columns, except in the SELECT list in the client application and stored procedures. Both approaches require a lot of work, but they are better than changing how the application behaves or forcing users to correctly type mixed-case text.

Preparing the Access Database

Before you use the Upsizing Wizard, you need to prepare the Access database and its client applications. You must delete unnecessary items, change cases (if necessary), change object names (if necessary), and perform a backup.

Delete unnecessary items. Cleaning up the database is the first task. Delete unnecessary items, such as obsolete tables, obsolete queries, and Paste Errors tables. That way, you have less to convert.

Change cases. If you decide to use a sort order other than Dictionary order, case-insensitive, you need to change the case of the objects and strings in the database. Change the cases following the guidelines in the previous section.

Change object names. Table 1 documents some of the differences between the Access and SQL Server databases. SQL Server typically has more generous database limits than Access, making the conversion process easier. However, Access permits more characters in object names, fields in a table, and bytes in a row. If you're using SQL Server 6.5 or earlier, the object name limit of 30 characters is likely to affect you the most. Because Access allows 64-character names, you must shorten object names for SQL Server. However, in SQL Server 7.0, object names can have 128 characters.

Another issue between the two databases might be the use of embedded spaces, special characters, and keywords in object names. Access lets you use embedded spaces, special characters, and keywords if you put the object names in square brackets. SQL Server lets you use embedded spaces, some special characters, and keywords if you put the object names in quotes and set the QUOTED_IDENTIFIER option to ON. If you create stored procedures later in SQL Server, you must also use quotes in them.

Although you can use quoted object names, this practice isn't a good idea. Some SQL Server utilities, such as SQL Enterprise Manager, don’t insert quotes in the SQL they execute. As a result, the utilities might fail when performing seemingly routine operations. In addition, if users forget to use quotes or don't use them correctly when writing stored procedures, the procedures might fail.

Instead of using quotes, I recommend that you change the object names in the Access database and any client applications before the conversion. Eliminate embedded spaces and special characters, or change them to underscores. Eliminate keywords by adding a trailing underscore or dollar sign. If you use underscores, don't use more than one underscore in a row. Consecutive underscores run together on the screen so you can't tell how many underscores you have.

Perform a backup. As with any potentially destructive operation, performing a backup is essential in case something goes awry. You can't rerun the wizard on the same tables after you've converted them.

Installing the Upsizing Wizard

You can get the latest version of the Upsizing Wizard, which is part of the Microsoft SQL Server Upsizing Tools for Access 97. After you download the tools, run aut97.exe or upsize97.exe to install the wizard add-in. The executable also installs another add-in, SQL Server Browser.

The Upsizing Wizard is a module written in Microsoft Visual Basic for Applications. VBA is the native programming language for Access. You can modify the wizard's code if you want, but because of licensing restrictions, you can't redistribute any changes you make.

Along with installing the latest version of the Upsizing Wizard, I recommend that you use the Convert Database utility under Tools, Database Utilities to upgrade to the latest version of Access. Having the latest version maximizes your chances of a successful conversion.

Using the Upsizing Wizard

With the preparations and installation complete, you're ready for a trial run of the Upsizing Wizard. I recommend that you perform trial runs on copies of the database before you perform the actual conversion.

The wizard walks you through a series of options before it performs the upsizing operation. Figure 1 shows the four option areas: database, tables, conversion, and report.

Database options. You must tell the wizard whether you're using an existing database or creating a new one. If you have an existing database, you need to select the ODBC data source. If you don't have an existing database, the wizard helps you create one. You can use SQL Enterprise Manager or isql to create the database and the wizard to create the ODBC data source.

Tables option. You don’t have to convert the entire Access database in one operation. As Screen 2 shows, you can select the tables you want to convert.

Conversion options. You have many conversion options to choose from. As Screen 3 shows, these options appear in three sections.

In the first section, you select the table attributes you want to convert. You can convert indexes, defaults, validation rules, and table relationships. Typically, these attributes convert cleanly. However, you might have problems if you used Access functions in any of your table's validation rules or defaults.

In the Table relationships option, you select whether you want to use Declarative Referential Integrity (DRI) or SQL Server triggers to convert referential integrity constraints. Access implements DRI as part of the database and uses DRI to implement cascading updates and deletes. (Access doesn't have triggers.) SQL Server can implement referential restrictions but can’t use DRI to implement cascading updates and deletes. Thus, you must use triggers to convert cascading updates and deletes. Triggers typically give the database better performance, but they require more work to maintain.

In the second section, you tell the wizard how to handle the Access data by specifying whether to add timestamp columns. Access uses timestamp columns to prevent simultaneous updates. SQL Server uses timestamp columns to manage concurrent update activity.

If you plan to use Access or Data Access Objects (DAOs) to access data in client applications, I recommend letting the wizard decide whether to add timestamp columns. The wizard adds them when a table has floating-point, text, or image columns. If you plan to use another data access method, you can either force timestamp columns into all converted tables or leave the timestamp columns out.

Another option in this section is whether you want to import the data from Access to SQL Server. If you select the Only create table structure, don't upsize any data check box, the wizard only creates the SQL Server database; it doesn't import the data. If you clear this check box, the wizard moves the data while it creates the SQL Server database.

Importing the data can take hours, especially if your database is large. Thus, during trial runs, I recommend that you create the table structure only. In the actual conversion, you can let the wizard import the data. However, if you have an Access database with referential integrity, don’t let the wizard import the data. The wizard might upsize child tables before parent tables, causing the data load for those tables to fail. When referential integrity is an issue, you can either upsize just the table structures or upsize the tables in the order that the referential integrity checks enforce.

In the third section, you specify how to modify the Access database. If you're going to continue to use Access to retrieve the data after the conversion, you must select the Link newly created SQL Server tables check box. Be aware that if you link all tables, the size of your Access database doubles because the wizard copies the data to the new tables. In addition, linked tables can reduce the performance of your SQL Server system. (The section "Picking the Architecture" discusses this topic in detail.)

If you link your tables, you must specify whether you want to save the passwords and user IDs associated with the linked tables. If you save them, users don't need to type this information each time they connect to the SQL Server database. If you intend to use integrated security and a trusted connection, you don't need to save the passwords and user IDs.

Report option. This option tells the wizard whether to produce a log report. This report details all the input the wizard used and all the actions the wizard took (including the text of any triggers it created) during the conversion. Although this report is long, I recommend that you produce it because it includes error messages.

After you select whether you want this report, click Finish. The wizard goes to work, taking only about a second per table if you’re not moving data or if you don't have much data to move. Check the report for errors. Typical errors include data types that don’t convert, functions in validation rules that don’t convert, and names that don’t conform to SQL Server’s limitations. Fix the problems in the Access database, and perform another trial run. When the trial-run report has no errors, perform the actual conversion.

In the Web-exclusive article "15 Steps to Upsize Your Access Data to SQL Server," Angela J.R. Jones describes her experiences with the Upsizing Wizard. This case study can give you further insight on how to use the wizard.

Modifying the Client Applications

The Upsizing Wizard is great for simple conversions but doesn’t take advantage of all the power that SQL Server has to offer. In fact, client applications might run more slowly after the conversion because of an increase in network traffic. To take full advantage of SQL Server, you must modify the client applications to move the processing of all SQL statements to the server. This modification involves changing the default architecture and converting Access queries and inline SQL statements (i.e., SQL statements contained in a client application) to equivalent SQL Server code. I typically use Microsoft Visual Basic (VB) with one its Data Access libraries (e.g., DAO, RDO, ADO) to modify client applications, but you can use other development tools such as Access, C++, Java, or Delphi.

Changing the Default Architecture

The Upsizing Wizard assumes that your client applications will use linked tables in the Access database to connect to the SQL Server database. By default, the wizard sets up these links for you, unless you tell it not to (i.e., clear the Link newly created SQL Server tables check box).

If you use the linked-database architecture, the local Access database still exists. It might contain only links, or it might contain links and data. You can set up your system so that each client application has a copy of the Access database or the client applications share the database. Either way, the client applications are working with the same data, which the SQL Server database stores. (The exception is that SQL Server doesn’t store any tables that you didn't include in the conversion.)

Using a local Access database minimizes the need for changes in client applications, but this advantage comes at a high price: a decrease in performance of your SQL Server system. Although simple queries and inline SQL statements pass through directly to SQL Server and execute efficiently, problems arise when language incompatibilities occur. In these situations, Access retrieves the data and processes it locally, which is inefficient and defeats the purpose of converting to SQL Server.

To discover firsthand how inefficient using a local Access database is, create a new blank Access database and a link to the Authors table in the Pubs database on SQL Server. Use the File/Get External Data/Link Table… menu option in Access to create the link. (You need an ODBC connection to Pubs.) Start SQL Trace, and watch what happens. In Access, use the SQL view to create the query

SELECT min(dbo_authors.au_fname) FROM dbo_authors; 

In the SQL Trace window, you see the query

SELECT MIN("au_fname") FROM "dbo"."authors" 

This query tells you that Access retrieved one row. Now change the Access query to

SELECT min(format(dbo_authors.au_fname)) FROM dbo_authors;

In the SQL Trace window, you see the query

SELECT "au_fname" FROM "dbo"."authors"

Because format isn’t a SQL Server function, Access retrieved all the rows in the Authors table to process the query.

Although the two queries produced the same result (i.e., Abraham), how the SQL Server system reached that result differed. In the first case, SQL Server performed a table scan and returned only one result row in Authors—the one with the minimum au_fname. In the second case, SQL Server performed a table scan, returned the au_fname from every row in Authors, and sent this result set across the network to Access. Access then performed the format function (which doesn’t do anything in this case) and processed the rest of the query to evaluate the minimum function.

How the SQL Server system processes a query affects its performance. Any extra work, such as retrieving extra rows and sending them across a network, reduces the system's performance. In this example, you probably didn't notice a decrease in performance between the two queries because Authors had only 23 result rows. If Authors had 10,000 rows, you would've noticed.

In the linked-database architecture, a query must pass through three pieces of code: the Microsoft Jet Database Engine that implements Access, the DAO library, and ODBC. The ODBC driver often creates a stored procedure to retrieve each record. ODBC then retrieves the keys with a SELECT statement and executes the stored procedure once per record. Each roundtrip adds to the response time and server load. (The impact on performance is even more dramatic when the connections aren't local.)

Fortunately, you can reduce the number of roundtrips by eliminating the Access database and letting SQL Server execute Access queries and inline SQL statements on the server. However, this solution has a catch: You must translate the syntax embedded in the code of your queries and statements.

Converting Access Queries and Inline SQL Statements

The Upsizing Wizard doesn't convert Access queries. You must convert all the syntax of Access queries to SQL Server views or stored procedures. You must also convert the syntax of inline SQL statements, but the amount of syntax you must convert varies. If you use the DAO library and the ODBC driver (i.e., the DAO/ODBC combination driver) for communications with SQL Server, they resolve many of the syntax differences. If you don't use the DAO/ODBC combination driver, you'll probably have to convert all the syntax.

Each Access query consists of a SQL statement (i.e., SELECT, DELETE, UPDATE, or INSERT). You can convert some SELECT statements to SQL Server views, which is the easiest conversion to make. You simply create code that reads

create view viewname as   select ...go

Unfortunately, this conversion code doesn't work on many Access queries. You must use stored procedures to convert these types of queries:

Table 2 describes the approaches you can use to translate these queries into stored procedures.

Several fundamental language constructs differ between Access and SQL Server. When you translate a query to a stored procedure, you must follow SQL Server’s syntax. If SQL Server invokes the DAO/ODBC combination driver, it changes the Access constructs for you. If SQL Server doesn't invoke the DAO/ODBC combination driver (e.g., within the text of a stored procedure), you must modify the Access constructs to comply with SQL Server’s syntax. Table 3 details some of the Access constructs that you must change.

Access provides numerous VBA functions that are not part of SQL Server. Thus, you need to convert VBA functions. The DAO/ODBC combination driver makes some of these conversions for you but not all. Table 4 describes some of the Access functions that you must change.

Zero-length strings can also present problems in queries and inline SQL statements. Access allows zero-length strings; SQL Server 6.5 or earlier converts them to single spaces. (SQL Server 7.0 doesn't.) For example, in Access, the expression

"abc" & "" & "def"

evaluates to "abcdef". In SQL Server, the equivalent expression

'abc' + ' + 'def'

evaluates to 'abc def', with a space between the c and d.

The Upsizing Wizard automatically converts zero-length strings to spaces. If you want to avoid problems, you can convert zero-length strings to null before running the wizard with the Access UPDATE query

update mytable set mycolumn = null where len(mycolumn) = 0

You must run a similar Access query for every column that allows zero-length strings.

Other language differences between Access and SQL Server include:

You must modify queries to accommodate the restrictions you find.

When you change a query to a stored procedure, you must change the SQL statement. For example, instead of using select * from myQuery or myQuery, you need to use execute myQuery. You must also supply the correct parameters. How you supply the parameters depends on your development tool. For VB and DAO, I prefer to subclass the Workspace, Database, and QueryDef objects of DAO. That way, the code you write acts as an intermediary between the existing code and DAO. You can also use this technique to switch between object libraries.

Testing the SQL Server System

After you finish modifying the client applications, you must exhaustively test the SQL Server system. Even if your system appears in order, you're likely to run into situations where SQL Server works differently from Access. Thus, you must change the SQL Server system or the client applications to accommodate the differences.

Pay particular attention to multiuser applications. Many applications that use an Access database don’t make adequate provisions for multiple users. Applications can use either the default Pessimistic locking (i.e., holds a lock while a user has a record on the screen) or Optimistic locking (i.e., applies a lock when the user performs an update). Many applications that use Optimistic locking don’t have code to resolve update conflicts. Therefore, they ignore conflicts or let the last update stand. So, if your applications use Optimistic locking, you must write conflict-resolution code or switch to Pessimistic locking.

Reap the Benefits

SQL Server can offer substantially more benefits than Access when it comes to performance, scalability, multiuser capability, and database size. However, the conversion from Access to SQL Server isn't a quick one. You must consider many issues and perform many tasks, including selecting a sort order, preparing the Access database, installing and using the Upsizing Wizard, modifying your client applications, and testing your new system.