Upgrading the WBT Manager™ 
database from MS Access to MS SQL Server

September 13, 2001

Mark Schupp, Head of Development, Integrity eLearning

Many WBT Manager users begin using their systems with the Microsoft Access™ database that is shipped with the WBT Manager installation files. It is installed automatically, is simple to use, and requires no additional purchase. However, Access is inadequate for heavily used systems or where stored training data is considered critical to the user's operation. At some time or other most users will need to upgrade to a server-based DBMS such as Oracle™ or Microsoft SQL Server™. This document explains the procedure for upgrading from Access to SQL Server.

Upgrading to SQL Server
You could do a direct upgrade from the WBT Manager Access database to SQL Server by using the SQL Server Data Transformation Services wizard to import the table definitions and data. This is not recommended because DTS:

It is possible to correct some of the above problems by running the WBT Manager SQL Server database creation scripts after the data is transferred but there will still be enough differences to probably cause future upgrades of WBT Manager to fail.

The method described below takes a little more effort but will result in exactly the same database structure as you would have from a new WBT Manager installation. You should read and understand the WBT Manager SQL Server database setup procedure before continuing. The database setup procedure is described in sqlserversetup.html. The WBT Manager installation process is described in the "WBT Manager Installation and Customization Manual" (instcust.pdf)

Upgrade procedure

Part A - set up the database tables

  1. Create the SQL Server database and associated user logins as described in sqlserversetup.html. Stop before running any script files.
  2. Create a DSN to connect to the new database and verify that you can connect using the wbtmanadmin login and the wbtmanuser login.
  3. Verify the version of the WBT Manager that you are converting from and make sure that you have the installation files for that version available. You can see the WBT Manager version and the database version by selecting Help - About in the System Administrator module. For example: WBT Manager version 1.51 uses database version 1.07. If you have applied any patches which included structure changes to the database you should upgrade to the next full version of WBT Manager before continuing if possible. Otherwise, contact customer support for assistance.
  4. Make 2 copies of the sqlserv.sql script found in the utilities/database/scripts directory of the WBT Manager installation set. Name one copy sqlserv_noconstraints.sql and the other sqlserv_constraints.
  5. Open sqlserv_noconstraints.sql in a text editor and remove all of the ALTER TABLE xxxxx ADD CONSTRAINT... statements from the end of the file. Save the file.
  6. Open sqlserv_constraints.sql in a text editor and remove everything except the ALTER TABLE xxxxx ADD CONSTRAINT... statements. Save the file. (You can download the files for WBT Manager 1.51 by clicking these links. sqlserv_noconstraints_v151.sql sqlserv_constraints_v151.sql).
  7. Start the runsql.exe utility in the utilities/database directory of the WBT Manager installation set. Connect to the DSN created in step 2 and log on as wbtmanadmin.
  8. Open the sqlserv_noconstraints.sql file from within runsql (click "..." across from the "SQL Script File" text box). Click the "Execute" button to run the script. The Script should run with no errors.

Part B - transfer the data

  1. Start SQL Server Enterprise Manager and log on as an administrator (sa if possible).
  2. Start the Data Transfomation Services import wizard from the tools menu.
  3. On the "Choose a Data Source" page, select Microsoft Access as the source and the WBT Manager Access database (wbtman.mdb) as the database file. Then click "Next".
  4. On the "Choose a Destination" page, select Microsoft OLE DB Provider for SQL Server as the destination.  Select the server containing the new database as the server. Select "Use SQL Server Authentication" (log on as the same account used to create the database). Select the new WBT Manager database from the drop-down list of databases. Click "Next".
  5.  On the next page select "Copy table(s) from source database" and click "Next".
  6. On the "Select Source Tables" page click "Select All"
  7. For each table click on the button in the "Transform" column. Verify that the source and destination column names match for each column. Modify the destination name as necessary.

    This is very important!! If the column names are mismatched you will either experence an error in transferring the data (and have to drop the tables and start over) or data will be placed in the wrong column in the destination database (this is worse than having to start over). See the graphics below for an example of a correction to the column mapping.
  8. When all of the column names for all of the tables have been verified click "Next" to continue.
  9. Click "Next" again to start the data transfer. Watch for errors. 

Part C - add the database constraints and finish up

  1. Start the runsql utility and connect to the SQL Server database as wbtmanadmin.
  2. Open and execute the sqlserv_constraints.sql script. It should run with no errors.
  3. Open and execute the grantdb.sql script found in utilities/database/scripts. It should run with no errors.
  4. Modify the second line of settings.asp in the WBT Manager web application's virtual directory to use the SQLServer DSN and the wbtmanuser login and password.
  5. Start the System Administrator module (connected to the Access database). Select Edit - Database connection and select the SQLServer DSN. (If for some reason you are unable to connect to the Access database you can clear the System Administrator DSN by running regedit, opening the HKEY_CURRENT_USER\Software\Integrity eLearning\WBT Manager\Settings key and deleting the WBTMANDBDSN string value).

All done!