Upsizing SuiteFactory 7.0 to SQL Server

Description

This document describes how to upsize SuiteFactory 7.0 from MS Access 2000 database to SQL Server. 

This requires Microsoft Access 2000 or newer.

If you are upgrading from SuiteFactory versions earlier thanV6.5, you must first upgrade to V6.5 MS Access then from V6.5 to V7.0 MS Access before proceeding.

Prepare SQL Server for use with SuiteFactory

Before starting the upsizing process, the administrator of the SQL Server installation that will host the SuiteFactory database must configure the SQL Server installation for use with SuiteFactory.

1.  Make sure that the user who will be performing the “upsize” is authorized to create new databases on your SQL Server installation.  This user must be a SQL Server System Administrator.

2.  On the computer that hosts the SQL Server installation where the upsized SuiteFactory database is to be located, create a new, empty folder to contain this new SuiteFactory database. You may give this folder any name. We refer to this folder as the “SuiteFactory SQL Server Databases Folder”.  For example, SuiteFactoryDB. 

3. Share the “SuiteFactory SQL Server Databases Folder” so that it is visible to all computers on which SuiteFactory will be installed.  Make sure that all Windows users who will run SuiteFactory have full rights to the shared “SuiteFactory SQL Server Databases Folder”.

Copy the MS Access-based SuiteFactory database

1. Shut down all instances of all the SuiteFactory 7.0 programs that use the MS Access-based database that you plan to upsize to SQL Server. This includes all instances of the Communications Engine and the Maintenance Engine.

2. Choose a SuiteFactory SQL Server-based database Catalog Set Name.  (A SuiteFactory SQL Server-based database consists of three individual SQL Server databases functioning as a coordinated set. The names of these databases are <set name>_Primary, <set name>_History, and <set name>_Archive. For example, if the Catalog Set Name is “AcmeDNC”, then the individual SQL Server databases in the SuiteFactory database Catalog Set are AcmeDNC_Primary, AcmeDNC_History, and AcmeDNC_Archive).

3. Create a new, empty folder inside the shared “SuiteFactory SQL Server Databases Folder” that you created previously. Name the new folder to the Database Set Name you chose in the preceding step. This folder will be the “SuiteFactory SQL Server Database Support Folder”.   For example, C:\SuiteFactoryDB\AcmeDNC. 

4. Copy the entire contents of the SuiteFactory V7.0 MS Access database folder, including the contents of all sub-folders, to the new “SuiteFactory SQL Server Database Support Folder” that you created in the previous step. Make sure that all of the copied folders and files are set to be read-write, and that they are accessible to all Windows users who will run SuiteFactory.

Upsize the MS Access Database to SQL Server

The SuiteFactory database is implemented as a coordinated set of 3 individual databases, regardless of which database provider you choose. These individual databases are: 

To upsize a SuiteFactory database from MS Access to SQL Server, you must upsize at least the Primary database. If you want to continue to have access to the archived the copies of DNC Files and Controlled Documents, you must upsize the Archive database. If you want to continue to have access to your collected History data for DNC Files, Controlled Documents and Packets, you must upsize the History database.

Follow this procedure to upsize each of these databases.

1.  Navigate to the “SuiteFactory SQL Server Database Support Folder” to which you have previously copied the contents of the original SuiteFactory MS Access-based Database Folder. To upsize the Primary database, locate the file named ‘DNCWIN.MDB’, and open it using MS Access 2000 or newer. To upsize the History database, locate the file name ‘HISTORY.MDB’, and open it using MS Access 2000 or newer. To upsize the Archive database, locate the folder named ‘ARCHIVE’, and open it. Then locate the file named ‘ARCHIVE.MDB’, and open it using MS Access 2000 or newer.

2. Select the menu item ‘Tools’, then the sub-menu ‘Database Utilities’, then the sub-menu ‘Upsizing Wizard’. The first Wizard page looks like this:
   
    
    

3. Select the option ‘Create new database’, and click ‘Next’. The next Wizard page looks like this:
   
   
    

4. In the drop-down list box headed by the prompt “What SQL Server would you like to use for this database?” type or select the name of the MS Windows computer that hosts the SQL Server installation that will host the upsized SuiteFactory database. (In this example, we use ‘AcmeServer’.)

Make sure that if it is available the check box labeled “Use Trusted Connection” is checked.  If not you will have to login as a user who is a SQL Server System Administrator.

If your Database Set Name is ‘AcmeDNC’ and you are upsizing the Primary database (dncwin.mdb), specify the database name as ‘AcmeDNC_Primary’.

If your Database Set Name is ‘AcmeDNC’ and you are upsizing the History database (history.mdb), specify the database name as ‘AcmeDNC_History’.

If your Database Set Name is ‘AcmeDNC’ and you are upsizing the Archive database (archive.mdb), specify the database name as ‘AcmeDNC_Archive’.

Select the ‘Next’ button. The next Wizard Page looks like this:

5. The picture for this Wizard Page shows the list of available tables when you are upsizing the Primary database. This Wizard Page will have a different list of Available Tables when you are upsizing either the History database or the Archive database.
   
   Click the button labeled ‘>>’ to select all tables for exporting to SQL Server.

When you are finished the Wizard page should look like this:

6.        Click ‘Next’. The next Wizard page should look like this:

7.        Make sure the check box labeled ‘Indexes’ is NOT checked.

• Make sure the check box labeled ‘Validation Rules’ is checked.

• Make sure the check box labeled ‘Defaults’ is checked.

• Make sure the check box labeled ‘Table relationships’ is NOT checked.

• Make sure the drop-down list labeled ‘Add timestamp fields to tables?’ is set to ‘Yes, let wizard decide’.

• Make sure the check box labeled ‘Only create the table structure; don’t upsize any data’ is NOT checked.

Click ‘Next’. The next Wizard page should look like this:

8.        Make sure that the selection labeled ‘Create a new Access client/server application’ is checked.

Accept the default ‘ADP File Name’ setting.  (This image shows a typical default name for when you are upsizing the Primary database. Typical default names when you are upsizing the History database or the Archive database may be different.)

Make sure that the check box labeled ‘Save password and user ID’ is NOT checked.

Click Next. The next Wizard page looks similar to this:

9.        If present, select the option ‘Open the new ADP file’.

Click ‘Finish’. The Wizard starts upsizing the data, and displays a progress box that looks something like this: If you get security warnings along the way select OPEN each time.

10. When upsizing is finished, MS Access will display a printable Upsizing Wizard Report. When you have finished reviewing or printing the report, close MS Access. Provided that the report does not indicate that there were errors or problems while upsizing the database, it is ready to use.

Note: Remember to repeat this procedure for history.mdb and archive.mdb.

Troubleshooting the Upsize Wizard

You may occasionally experience problems upsizing a MS Access database to SQL Server. You should be able to find a variety of troubleshooting guidelines through the MS Access help system by searching for topics by the keyword ‘upsize’.

One type of problem that we have encountered in the past relates to differences in how MS Access and SQL Server manage certain column data types. Specifically, MS Access and SQL Server have different rules regarding date values.

 If a MS Access database table contains a date/time column, and if any record in that table contains a value that is earlier than January 1, 1753, then the Upsize Wizard will report an error for that table and it will not copy any data for the table. This situation is most likely to occur when you have configured a Search Criteria column to have the Date/Time data type, and a user has entered a date earlier than January 1, 1753.

If you discover that the Upsize Wizard fails to upsize a table because it contains a Data/Time column that has at least one record with a value earlier than January 1, 1753, you should delete the invalid upsized database from the SQL Server, then use either SuiteFactory or MS Access to locate the records that contain Date/Time values that are causing problems and edit the columns so that they contain Date/Time values that SQL Server allows, then try Upsizing again.

Point SuiteFactory to the SQL Server Database

1. Start SuiteFactory 7.0 on a client workstation.  Log in as Administrator.

2. Select the menu item ‘Configure’, then the sub-menu item ‘Select Database’. This will display the ‘Select SuiteFactory Database’ dialog box, which looks like the following:  Note! The Database folder shown is that of the original V7.0 MS Access database and is not significant anymore.  This is an example of how the ‘Configure” form will look when first selected. 

3. In the drop-down list labeled ‘Select a Database Provider’, select the item ‘SQL Server’. The dialog will change to look like the following:

4. In the drop-down list labeled ‘Available SQL Servers’, select the name of the computer that hosts the SQL Server installation that will host the new SuiteFactory SQL Server-based Database. In this example, we will use the server named ‘AcmeServer’.

NOTE!  If you are performing this ‘upsize’ on the actual SQL Server computer the server will be designated as <Local>.  The dialog box will now look like the following:

5. If the target database set already existed, it will appear in the list. If it does not exist yet, you need to create it by clicking the ‘Create’ button. This will display the dialog box ‘Create SQL Server Database’, which looks like the following:

6. In the text box labeled ‘Catalog Set Name’, type the name you previously selected for the SuiteFactory Catalog Set. For this example, we use ‘AcmeDNC’.
   
   In the text box labeled ‘SuiteFactory Data Folder’, type the full UNC path name of the SuiteFactory SQL Server Database Folder that you previously created and that contains the original MS Access-based database that you upsized. Alternatively, you can navigate to and select the path through a folder-selection dialog box by clicking the ‘Browse’ button.
   
   When you are finished, the ‘Create SQL Server Database’ dialog box should look something like this:

7. Click ‘OK’ to confirm these parameters and close this dialog box. The SQL Server database set list in the ‘Select SuiteFactory Database’ dialog box will contain the new entry you just created. The dialog box looks something like this:

8. Make sure that the new catalog set name is highlighted, then click ‘OK’.
   
   SuiteFactory will take a few minutes to complete the conversion of the new SuiteFactory SQL Server-based Database. When it is finished, you may register this login session to a SuiteFactory Workstation definition and start using the system.

Complete SQL Server User Management

In section ‎2 “Prepare SQL Server for use with SuiteFactory”, the SQL Server administrator configured the set of Windows users who have rights to create a new SuiteFactory database in the SQL Server installation. Now that the databases have been created, the SQL Administrator must establish the rights for all other Windows users who need to use SuiteFactory.  We suggest creating a Windows User Group.  Give that user group Full Rights to the “SuiteFactory SQL Server Database Support Folder”.   

The next step is to give that user group read and write rights to the SuiteFactory SQL Server database. To do this start up Microsoft’s SQL Server Enterprise Manager.  (Start/Programs/Microsoft SQL Server/Enterprise Manager)  Navigate to the Logins object of the Security folder for the instance of Microsoft SQL Server where the SuiteFactory databases are located.  An example is show below:

In the pane on the right, select the user group that is to use SuiteFactory:  “DNCusers” in this example.  Double Click to get to the Properties of DNCusers as shown below:

Make sure Grant access is selected.  Select the Database Access tab.

Checkmark the databases in question.  This includes the three catalog set databases  ..._Primary, ..._History and ..._Archive and the database called CCISuiteFactory.  For each of the 4 databases go to the bottom pane, “Database roles for ...”   and in addition to the default role, public,  checkmark db_datareader and db_datawriter.  Click OK.

That’s it.  You are ready to use the system now.

Appendix A

Making a user a SQL Server System Administrator

 Start up Microsoft’s SQL Server Enterprise Manager.  (Start/Programs/Microsoft SQL Server/Enterprise Manager)  Navigate to the Logins object of the Security folder for the instance of Microsoft SQL Server where the SuiteFactory databases are located. 

In the pane on the right, select the user you wish to make SQL Server System Administrator.  Double click on that user ,  “CCIdomain\Administrator” in this example to get to its Properties.  Select the Server Roles tab.

Checkmark System Administrator.  Click OK.