You should review the following items within the Microsoft Access application and SQL Server installation prior to migrating your application.
Current backups of your data are always important, but they are essential when you make major changes to your database. Though the upsizing process does not delete any data, you can expect to delete the local Microsoft Access tables in the .mdb file after the process is complete and has been tested. Also, you may need to consult a working copy of your original Microsoft Access application in the future.
Using Windows Explorer or File Manager, make a backup copy of the .mdb file containing your database, giving your file an extension of .bak. Thus Northwind.mdb becomes Northwind.bak. You needn't copy the file with the .ldb extension if one is already present. If you created user and group IDs for your application, make a backup of the system database as well. (By default, the filename is System.mdw in Microsoft Access 95 and System.mda in Microsoft Access 2.0.) Do not rename or move the original system file.
Review the names of your tables and columns. Objects in Microsoft Access can contain spaces, for example, you can name a table "January Orders." Microsoft SQL Server does not permit spaces in table or column names. The Upsizing Wizard changes the spaces to underscores, transferring your table to SQL Server as "January_Orders."
Microsoft Access tables ending in "_local" cannot be upsized to SQL Server. The Upsizing Wizard renames the original Microsoft Access tables, adding "_local" to the original table names. For example, a Microsoft Access table originally named "customers" is renamed "customers_local" by the Upsizing Wizard. This permits the new SQL Server table to be linked to the Microsoft Access database with the original name of "customers," while leaving the original table untouched with the new name of "customers_local."
Macros and code modules treat tables differently than queries. Open them in design view and redirect them to the table name as it has been renamed by SQL Server. (Alternatively, attach to a table named "January_Orders" and alias the attachment to "January Orders." However, this can confuse people who see the attached "January Orders" table, but see the different name "January_Orders" on the server.)
A common way to avoid the underscores is to use capitalization to separate the portions of the names, for example "JanuaryOrders". If you have used this naming convention in your Microsoft Access tables, you needn't make any changes when moving your data to SQL Server.
SQL Server limits table names to 30 characters. No symbols, except #, $, and _, are allowed in table names. The first character of a table name cannot be a number. Do not use the number sign (#) as the first character of an upsized table name because it indicates a temporary table.
Though the default installation is case-insensitive, SQL Server can be installed with case sensitivity so that a table called MyTable is not the same as a table called mytable or MYTABLE. These three object names can coexist if the server was installed with a "Case Sensitive" sort order. Determine the sort order on your server by running the stored procedure sp_helpsort. If you plan to change any table names, do it before the Upsizing Wizard starts copying your tables.
SQL Server offers two methods of maintaining referential integrity:
A trigger is Transact-SQL code that runs when set off by an action such as an INSERT, UPDATE, or DELETE to a table. Triggers are flexible and can be written to do almost anything. Triggers are your only choice for cascading updates and deletes discussed below. The Upsizing Wizard writes them for you if you choose. For more information about cascading deletes and updates, see Knowledge Base Article Q142480, "INF: Cascading Deletes and Updates of Primary Keys."
SQL Server's DRI will enforce referential integrity. (For example, an order cannot be created for customer XYZ unless there is a corresponding customer XYZ in the orders table.) However, DRI does not offer the cascade update feature. (You cannot change the customer name to ABC and expect all existing orders to be changed automatically.) Similarly, DRI does not support cascading deletes. In Microsoft Access, if you delete the customer XYZ, Microsoft Access prompts you to delete all of XYZ's orders from the orders table. With SQL Server DRI, you have to do this in two steps: first delete the orders for the customer, then delete the customer.
Set the cascade updates and deletes feature in Microsoft Access from the Relationships window. Double-click a relationship line to see the option. The Upsizing Wizard detects the cascading option and suggests triggers to support your referential integrity. If that option is not selected on your joins, the Upsizing Wizard uses SQL Server DRI to support referential integrity.
One drawback to using triggers is slower performance in the case of inappropriate data. If you use DRI, the primary key constraints fire before data is entered into the table. Inappropriate data is thus rejected quickly. Triggers, on the other hand, permit the data to be entered into the table, then the trigger fires to validate the entry. If the relationship is not valid, the change to the table is then rolled back.
The Upsizing Wizard uses triggers to prevent entry of data into fields that were marked as "Required" in Microsoft Access. A trigger can easily be changed, retaining the flexibility that Microsoft Access exposes for a table. SQL Server developers generally define a required field as NOT NULL when creating the table. The Upsizing Wizard can be configured to do that for you.
For more information about configuring the Upsizing Wizard to specify NULL or NOT NULL, see "Server Error 191: SQL Statement Nested Too Deeply," in the "Common Error Messages" section of this paper.
Now you are ready to upsize your database.
SQL Server should be installed and running on the server computer.
A Microsoft Access database is held in the .mdb file on the computer's hard drive. SQL Server data is also kept in a file on the hard drive. It uses the .dat file extension (for example, Data.dat). SQL Server refers to the hard drive file as a "device." A database can be placed on one device or spread over several devices. Several databases can also be placed on one device. The Upsizing Wizard prompts you to select a device or create a new device to hold your data. Generally, it is best to place your database on a new device created specifically for it. This reduces your administrative complexity. You will also be prompted for the location of the transaction log. If you want to postpone the decision, put the transaction log on a separate device for now.
The size of your database will not change greatly when it is transferred to SQL Server. It could be somewhat smaller than your Microsoft Access .mdb file because you are moving the tables only, not forms, reports, or Visual Basic® for Application modules.