Badger TraCS Guides
Task 1.6.1: Migrate from MS-Access to SQL Server
- Geraldine Polster
- David Malisch
Task 1.6.1: Migrate from MS-Access to SQL Server Draft
Purpose: Upgrades your TraCS office database to SQL server because your agency has outgrown MS-Access.
Requirements: Master computer, SQL Server/SQL Server Express installed and accessible on your network, SQL Server Management Studio, SQL Server administration skills.
TraCS will work with any edition of SQL Server, 2019 or better, including Express. it is always the best practice to get the latest version of SQL.
These instructions should be completed on your TraCS Master Computer.
Note: The Fall 2021 Additional Instructions MUST have been completed before attempting to follow the instructions outlined in this document. If you are unsure whether these instructions have been followed please contact your TraCS Administrator or the Badger TraCS Help Desk for confirmation.
Did You Know?
See SQL Server 2022 | Microsoft for information on the different editions of SQL Server available from Microsoft.
SQL Server Express is available free of charge from Microsoft. The download link is here: http://www.microsoft.com/en-us/download/details.aspx?id=29062
If you decide on the Express edition, you need to install ENU\x64\SQLEXPRWT_x64_ENU.exe if you have a 64-bit server or ENU\x86\SQLEXPRWT_x86_ENU.exe if your server is 32-bit. These EXEs contain the database engine as well as the tools necessary to administer the database.
When installing SQL Server Express, be sure to choose “Express” rather than “LocalDB.” LocalDB will not work with TraCS.
Preparation:
This can only be completed after a successful install of the Fall 2021 WIPack.
Updating agency to latest WIPack version could be beneficial and needed if any issues come up trying this from minimum version levels mentioned.
Master Computer Baseline minimum version: 19.02.35
Only Update Baseline on the Master Computer
Once agency is successfully migrated to SQL databases.
Run the 19.02.20 Baseline on the Master to put it back on same level as the rest of the computers.
WIPack minimum version: WIPack220207
- 1 Preparation:
- 2 Reset Pack Level on Master:
- 3 Gather Current Data:
- 4 Reset and Setup Master Computer to be a SQL Database - Task 1.4(c)
- 5 Start Auto-Update on Master Computer - Task 1.5
- 6 Finish Database Setup – Task 1.6(c)
- 7 Import Access Data
- 8 Best Practices (Redo the Pack Update on Master Computer)
- 9 Create Distribution and Update Mobile computers
Reset Pack Level on Master:If the agencies Access environment is running the latest pack, you can skip to Step #10
|
|---|
Delete the files/folders in found in the following directory: C:\ProgramData\Tracs\TraCSClientUpdate\.
Delete the DistributionLog.xml file found in the following directory: C:\ProgramData\Tracs\Settings\.
Delete the whole TraCSPack line in the RegistryKeyName.ini file:
C:\Program Files\TraCS\RegistryKeyName.ini OR
C:\Program Files (X86)\TraCS\RegistryKeyName.ini
Example: TraCS Pack=WIPack130226b
Should look like below: Do not change the TraCS= line in your file.
Gather Current Data: |
|---|
Create a location to store the files; if you already have a network share create the directory there. i.e. \\myserver\TraCS\MigrationFiles
Log into TraCS Configuration Manager as a user with the SystemAdmin access level.
Navigate to Users | Access Levels
Select SystemAdmin
Click Add
Select FixLogs
Click OK
Click Apply
Click OK
Navigate to Update | Run Distribution File
Run the following five distribution files. These must be run in order. Click OK after each is run and click on Update | Run Distribution File to run the next one, until all five are run:
C:\ProgramData\TraCS\Distribution\Templates\BTDBData.dist.exml
C:\ProgramData\TraCS\Distribution\Templates\BTDBLog.dist.exml
C:\ProgramData\TraCS\Distribution\Templates\BTDBSupportAgency.dist.exml
C:\ProgramData\TraCS\Distribution\Templates\BTDBUsers.dist.exml
C:\ProgramData\TraCS\Distribution\Templates\BTDBSync.dist.exml
This final distribution may take a while; between 5 and 10 minutes.
Minimize TraCS Configuration Manager
Log into the regular TraCS application as a user with the SystemAdmin access level.
Navigate to Tools | Maintenance | FixLogs
Click OK
Click Export Support
Click Yes | OK
Copy file from c:\programdata\tracs\distribution\AgencySupportTables.dist.exml
To \\myserver\TraCS\MigrationFiles\AgencySupportTables.dist.exml
Export Users
Log into Configuration Manager as an user admin.
Click Utilities tab
Click Location Migration button
Click Export Users button left side
Verify correct agency name and click Export Users button
Save file to \\myserver\TraCS\MigrationFiles Note: this should have created one file (###_UsersExport_###.uexp)
Export Forms
Verify correct agency name and click Export Forms button (This step can take several hours)
Save file to \\myserver\TraCS\MigrationFiles Note: Should have created one file (agencyID_FormsExport….)
Close TraCS Configuration Manager.
Reset and Setup Master Computer to be a SQL Database - Task 1.4(c) |
|---|
Task 1.4(c): Set up Databases, E-Mail, and Proxy Server Connections, SQL Server Alternative
Start Auto-Update on Master Computer - Task 1.5This step is only needed if you are making a new Master Computer. |
|---|
Skip Step #1 where is says to close Configuration Manager.
Stay logged into the configuration manager for now.
Task 1.5: Master Computer Auto-update
Finish Database Setup – Task 1.6(c) |
|---|
Task 1.6(c): Complete Database Setup, SQL Server Alternative
Import Access Data |
|---|
Import Support
Update tab
Run Distribution File button
Highlight AgencySupportTables.dist.exml (make sure it has today’s date as a last modified).
Click Open
Click OK on ‘Distribution has completed.’ message box.
Using the Location Migration Tool to import users, data and logs
Click Utilities Tab
Location Migration Button
Click Import Users button
Click 3 dots to select and select the ###_UsersExport_###.uexp file saved in \\myserver\TraCS\MigrationFiles.
Click Next
Click Next on Location Import
Click Next on Import Access Levels
Review User groups and default.
Click Next
Click Next
Click Import Forms.
Select the file exported from file (agencyID_FormsExport….) saved in \\myserver\TraCS\MigrationFiles.
Test Office Computers
Create a form and close out. (Makes sure the forms are saving to databases)
End-shift some forms in from an MDC.
Create a distribution.
Best Practices (Redo the Pack Update on Master Computer) |
|---|
Note: It’s a good idea to run the update down again on the Master Computer. This ensures that the data which we imported is going to work in the SQL environment. This step isn’t necessary, we just recommend it in case there are any issue with forms saving, end-shifting, etc.…
Reset Pack Level on Master.
Delete the files/folders in the C:\ProgramData\Tracs\TraCSClientUpdate\ folder.
Delete the DistributionLog.xml File C:\ProgramData\Tracs\Settings\ folder.
Delete the whole TraCSPack line in the RegistryKeyName.ini file
C:\Program Files\TraCS\RegistryKeyName.ini or
C:\Program Files (X86)\TraCS\RegistryKeyName.ini
Example: TraCS Pack=WIPack130226b
Should look like below. Do not change the TraCS= line in your file.
Restart Auto-Update on Master Computer
Requirements: Master Computer needs to be connected to the Internet.
Close the configuration manager if it is still open from the previous task.
Start TraCS.
Click OK.
Log into TraCS using User 00001 and Password “tracs”.
You will receive notifications in you Windows task bar that files are downloading.
When you receive the notification that the download is complete, exit TraCS by clicking the Cancel button on the log in screen.
Start TraCS.
Click Ok.
Log into TraCS using User 00001 and Password “tracs”.
Click Yes to start the update process.
TraCS will restart when the download is complete.
Create Distribution and Update Mobile computers |
Refer to Installation Task 4.1(a): Create Custom Installation Files, Standard Alternative Run the distribution on the mobile computers.
Verify that end-shifting is working.
Related content
email badgertracs@dot.wi.gov or call 608-267-2096