In the case studies in Part 3 of this book, we discuss a Forum application that allows you to create a discussion group on your Web site. The original was developed in Microsoft Access, with no regard for the problems that would arise from upsizing it to SQL Server. In this case, it's an ideal example of how we go about the process.
Upsizing Wizard allows us to create a new SQL Server database or if we have previously upsized our Access database (or just want to add Microsoft Access tables to an existing SQL Server database) we can upsize to an existing database. Typically, we will have created a database beforehand, as opposed to having the wizard create it. By creating the database up front, we are assured that it is created with the attributes and permissions required.
The next step is to install and start Upsizing wizard from the Access Tools | Add_Ins menu.
Here we will be using a database that has already been created. Clicking Next brings up a Data Source dialog requesting the name of the data source to upsize to. This list reflects the ODBC data sources defined on our system—in our case we select a Data Source Name (DSN) which we previously defined using the ODBC Administrator for the SQL Server we are upsizing to. If we hadn't already created the DSN, we can define it at this point.
Using the DSN we selected, the wizard will attempt to connect to the SQL Server specified. Once connected, it presents a list of the tables stored in the Access database. We simply select the tables we want to be upsized.
Now things get a bit more interesting. Although Access and SQL Server have many common characteristics, there are several significant differences in the way data is structured, and the indexes maintained. The wizard goes a long way in trying to simplify the conversion from Access to SQL Server, but we need to give it a bit of guidance. First, we need to decide what attributes that we want to upsize. The wizard can convert indexes, validation rules, Access defaults and relationships to SQL Server.
Indexes in SQL Server and Microsoft Access are very similar. If we choose to have the Access indexes upsized, the wizard will convert primary keys to SQL Server clustered, unique indexes named aaaaa_PrimaryKey. In an Access database, a primary key does not have a user-assigned name. However, SQL Server requires a name for all objects, including the unique index created to represent the Access primary key.
All other indexes retain their names, except where they contain illegal characters—which are replaced with an underscore. Unique and non-unique Access indexes become unique and non-unique SQL Server indexes. SQL Server doesn’t support ascending or descending indexes, and so this attribute is ignored.
Default attributes are directly supported by the SQL Server catalog, and as such are ported directly between the two platforms. However, validation rules are by necessity treated differently. Validation rules and referential integrity, in Access, are part of the data dictionary and are enforced at the engine level. SQL Server validation rules and referential integrity are not part of the data dictionary, and are enforced through code bound to a table (stored procedures and triggers). This can lead to difficulties if a validation rule cannot be implemented as a stored procedure. The wizard is very good at making this conversion, but it is always a good idea to check the procedures generated for accuracy.
A data dictionary is a repository of information concerning the structures in the database.
When we create a relationship between two Access tables, a new index on the foreign key in the relationship is created automatically. These system-generated indexes do not appear in the Access index editor. Access names these relationship-indexes Reference and, if the index name is not unique within a database, adds a suffix. Because an index named Reference could also be created by a user, Upsizing Wizard exports all indexes, and does not distinguish between system-generated and user-created ones. System-generated relationship indexes improve performance when tables are joined. However, if we end up with two identical indexes, one user-created and the other system-generated, we can drop one of them.
Relationships can also be enforced in SQL Server 6.5 through Declarative Referential Integrity (DRI) as opposed to triggers. Upsizing Wizard for Access 97 allows us to enforce the relationships defined in our Access tables using DRI in SQL Server. DRI can be easier to administer, better performing, and more flexible then referential integrity enforced through triggers. If you are upsizing to SQL Server 6.5 you, may want to consider this option.
For more information on SQL Server 6.5 and DRI see Professional SQL Server 6.5 Admin (ISBN 1-874416-49-4), published by Wrox Press.
By default, Upsizing Wizard creates additional new columns, with the timestamp datatype, in SQL Server tables generated from Access tables that contain floating-point (single or double), memo or OLE fields. A timestamp field contains a unique value, generated by SQL Server, which is updated whenever that record is updated. Access uses the value in timestamp fields to see if a record has been changed before updating it.
Allowing Upsizing Wizard to create timestamp columns is most often used when Access Tables will be attached to the corresponding SQL Server. In this instance, Access will use these timestamp columns to determine if data in a table has changed, without having to scan the whole table. Upsizing Wizard can also attach the upsized tables to an Access database. This can be of value in a system where Access databases will provide local storage. In our case, this would not be practical.
When we reach the final screen of the Upsizing Wizard, it offers to create an Upsizing Report. This documents the objects that the wizard has created in SQL Server. It includes information about any databases that were created, as well as a complete explanation of how each Access object that was upsized maps to an SQL Server object. After upsizing is complete, we can view this report on screen, or print it for future reference.
As we mentioned earlier, Access and SQL Server are not 100% compatible. These differences, as well as design decisions made by Upsizing Wizard, mean that much of the Microsoft Access data dictionary cannot be mapped directly to SQL Server constructs. The following table summarizes how objects are mapped from Microsoft Access objects to SQL Server:
| Microsoft Access object | SQL Server object |
| Database | Database |
| Table | Table |
| Indexes | Indexes |
| Field | Field |
| Default | Default |
| Table validation rule | Update and Insert triggers |
| Field validation rule | Update and Insert triggers |
| Field Required property | Update and Insert triggers |
| Relations | Update, Insert and Delete triggers |
An Access mdb file maps directly to an SQL Server database. A Microsoft Access table, excluding much of its data dictionary, maps to an SQL Server table. Access maintains this information as a part of the mdb database. Upsizing Wizard replaces illegal characters with the _ symbol. Any names that are SQL Server keywords, for example FROM and GROUP, have the _ symbol appended to them, resulting in the names FROM_ and GROUP_.
However, this does not always avoid problems. For example, the Access Forum database has two column names that SQL Server cannot work with. Here's our Access table in design view:
Notice that one column has been named When and another From. These are both keywords in SQL Server. As such, the wizard attempts to create a table using:
CREATE TABLE TemporaryUpsizedTable
(
ID int IDENTITY(1,1),
Subject varchar (50),
From_ varchar (50) ,
Email varchar (40) ,
Body text ,
When varchar (30) ,
MsgLevel smallint ,
PrevRef int ,
TopRef int ,
CONSTRAINT aaaaaOld2_Message_PK
PRIMARY KEY NONCLUSTERED
(When ,TopRef ,ID)
)
SQL Server rejects this syntax as invalid, and fails to create the table. To correct the problem, we will need to rename these tables to something that SQL Server can work with. For expediency sake, we can simply rename the columns FromMsg and WhenMsg. Of course, we then need to make sure that any dependent queries reflect the changes that we make.
Also, we need to keep in mind that the data types supported by SQL Server differ from those supported by Access. Upsizing Wizard will convert our Access data types to the nearest matching SQL Server types. The conversions are made as follows:
| Access Data Type | SQL Server Data Type |
Yes/No |
Bit |
Number (Byte) |
Smallint |
Number (Integer) |
Smallint |
Number (Long Integer) |
Int |
Number (Single) |
Real |
Number (Double) |
Float |
Currency |
Money |
Date/Time |
Datetime |
Counter |
Int |
Text(n) |
varchar(n) |
Memo |
Text |
OLE Object |
Image |
The Upsizing Report indicates whether the Upsizing Wizard was successful in converting all the field names and creating the new tables.