Migrate System33 MDB to SQL Server

Steps below as described by John to migrate System33.mdb to SQL Server database

Step-by-step guide

Note: Microsoft Access 2010 is required to perform these steps. MS Access 2007 will not communicate with SQL Server 2012. Versions of MS Access > 2010 have this functionality to upsize DB removed.

1. Create SQL Server Database using SQL Server Management Studio / command line.
   
   1. Open Microsoft SQL Server Management Studio and log in as an administrative user.
      
      
      
      
   2. Right-click on the Database folder, and select New Database in the context menu:
      
      
      
      
   3. Enter the database name (in this example System33). If you wish to change the path where SQL server will create they files on the filesystem, change the Path option in the Database files for the data & log files:
      
      
      
      
      
      
   4.  Click on OK to create database
      
      
      
      
2. Create a 32-bit ODBC DSN to SQL Server database using SQL Server Native client. Name the DSN to something unique for the migration (i.e. ir_system33_msv)
3. Take copy of System33.mdb you are upsizing so to not disturb production system from possible DB locks on database during migration
4. Open copy of System33.mdb you created in step 3 in Microsoft Access 2010.
   
   
   
   
5. In the Database Tools menu, click on SQL Server button in the toolbar:
   
   
   
   
   1. Choose Use existing database in the upsizing wizard, and click on Next:
      
      
      
      
   2. In the popup window which is displayed, click on Machine Data Source, and select the ODBC connection you created in step2, and click OK:
      
      
      
      
   3. For the SQL Server login details, either use Trusted Connection (if it has been setup while creating the database), or enter in a SQL Server user account to access the database (in this example, the sa account is used). Click OK to continue:
      
      
      
      
   4. Select the option to migrate all tables to SQL Server:
      
      
      
      
      
      
      • Note: For final migration, some tables maybe excluded, including the following:
        • AuthToken
        • OrganisationDefinitions
        • OrganisationDefinitions_Old
        • CompanyDefinitions
        • CountryDefinitions
        • DataTypeMap
        • ETL_LogDirectory
        • ETL_Schedule
        • ETL_ScheduleLog
        • StateDefinitions
      
      
      
   5. Click Next:
      
      
      
      
   6. Ensure that the option No application changes is selected, then click Next:
      
      
      
      
   7. Click Finish to start the data migration process:
      
      
      
      
   8. Once the data migration has been completed, Access will display a report. Close it, there is no useful information provided.
6. Close Microsoft Access.
7. Open Open Microsoft SQL Server Management Studio and log in as an administrative user.
   
   
   
   
8. Expand the Databases folder, System33 database, and the Tables folder:
   
   
   
   
9. Check some tables which are known to have AutoNumber columns (like Companies / Mappings), and check that the data migration has converted these to Identity Columns. To check this, do the following:
   1. Right-Click on the table (in this example, Companies) and in the context menu, select Script Table as \  CREATE To \ New Query Editor Window:
      
      
      
      
   2. In the query editor window, find the column known as an autonumber column, and check its datatype is INT IDENTITY(1,1) (in this case, company_id):
      
      
      
      
   3. Repeat task on other tables.
   4. Execute the following query to check that the identity column seed (last number issued by server) is correct:
      
      

      SELECT TABLE_NAME, COLUMN_NAME, IDENT_CURRENT(TABLE_NAME) AS IdentitySeedValue

      FROM INFORMATION_SCHEMA.COLUMNS

      WHERE TABLE_SCHEMA = 'dbo'

      AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

      ORDER BY TABLE_NAME

      
10. Run DB Upgrade MSV_DBUpgradeV0 This updates the System33 database with some configuration changes.
    
    
11. Run DB Upgrade MSV_DBUpgradeV1 This updates the Setup database with some configuration changes.
    
    
12. Run DB Upgrade MSV_DBUpgradeV2 This updates the databases with some reconfiguration of Organisation User-Defined fields.
13. Run Datafix program FixIRConnections to view any duplicate connection entries in table System33.dbo.Connections. If any have been found, run program with -d switch to remove them. 

Note: The following tables have NTEXT columns and may require modifications:

Related articles

• Page:
  Migrate System33 MDB to SQL Server
• Page:
  Emailer Setup for MSV System33