TechEd 99 - Distributing SQL Server Solutions

Migrating from Access to SQL Server
(Jim Sturms)

July 9 - Migrating from Access to SQL Server must be a common requirement, so it was a surprise that there were only about 70 delegates at the session. Perhaps the late nights in Amsterdam were beginning to tell...
This session covered three broad areas:

Jet versus SQL Server - which you should chose under which circumstances.
The Upsizing Wizard - what it can do.
Optimising tips for moving applications from Access to SQL Server.

The first topic is reasonably well understood, although the story has been complicated by the introduction of MSDE. However this area has been covered in another session (Distributing SQL Server compatible solutions for the desktop) so we'll concentrate here on the latter part of Jim's talk.
The appropriate method for migrating to SQL Server depends upon the version of Access you are currently using. If you are using Access 97 then the Access 97 Upsizing Wizard, available free from www.microsoft.com/accessdev will upsize Access 97 MDBs to SQL Server 4.21, 6.0, 6.5 and 7.0. Access 2000, on the other hand, has an integrated wizard to upgrade Access 2000 applications to SQL Server 6.0, 6.5 and 7.0.
The wizard recreates a jet database on SQL Server. This process includes moving:

Table Structure
Indexes
Validation Rules
Defaults
Relationships
AutoNumbers

Then it:

Moves the data
Links SQL server tables to the Access MDB
Creates Access project files
Handles field renames

Or, at least, it can do all of these, but some are optional. In addition, you still need to apply a reasonable degree of human thought to the process. Jim's talk covered the areas where care is needed. The following are some of the excellent tips and tricks he divulged:

You can expect the SQL Server version to be about 1.5 times the size of the original Access database.
If the data size is greater than about 25Mb, the upsize wizard will take a long time to move the data and, indeed, the process may fail. So, given more data than this, the advice is to use the wizard, but de-select the 'move data' option. Once it has created the structure, suck the data into the SQL Server database using the DTS.
Many of the objects map in the expected way:
      Database - Database
      Tables - Tables
      Fields - Fields
      Indexes - Indexes
      Defaults - Defaults
Some map in a more complex way:
Validation rules - Triggers
Relationships - either to Triggers or to Foreign Keys
Upsizing queries can become complex. For example, View can be nested (like Access Queries) but they don't support 'ORDER BY' and 'Parameter' queries. Stored Procedures support both, but don't allow nesting.
Each Access query can generate:
One View - for nesting.
One Proc - for ORDER BY, Parameters.
This means that queries may map 1 to 1 or 1 to 2, so be careful out there.

As a general rule, you should consider future SQL Server upgrading when designing any Access database from now on. If you are working with Access 2000 this means you should use a project file. If you are working with Access 97 or earlier, you can reduce later potential grief by simple expedients such as:

Use SQL Server object naming conventions (no spaces for a start!).
Design forms that do not initially return data - use unbound forms that only populate when data is requested.
Be careful with combo. Think about using 'nested combo': for example, the first selects country, the next is then used to select the branch office in that country. This way you can avoid one combo that returns all of the branch offices in the world.
Do not use Jet specific code

The upsizing Wizard doesn't move un-enforced relationships (or joins). In addition, even if the joins are enforced, if you upsize the tables one at a time, the join will not be upsized.
A typical upsizing exercise moves the data to SQL Server but leaves the forms in the MDB file. Thus users will see the same UI as before, which is good. However be aware that the behaviour of the forms will probably change in moderately subtle ways.
For example, your users will be used to seeing default values appearing in forms as soon as the form is opened for data entry. The Wizard will upsize the default settings to SQL Server, but these will only appear in the form when the record is posted. You can, of course, duplicate the default value in the form (so that it then appears at the 'correct time') but now you have introduced the possibility of conflicting default values, particularly if the database is updated at a later date by another programmer. If you remove the default from SQL Server, you are back to one location, but you are in trouble if a new form is created. You can't win, but if you are aware of the problem you may at least be able to break even.

Mark Whitehorn

Migrating from Access to SQL Server (Jim Sturms)

Migrating from Access to SQL Server
(Jim Sturms)