ID 1071

HOW TO: Move your Web Tracks Database to SQL Server 2012

Description
Applies to Web Tracks 9.1 and newer


Note: This only applies to databases which are currently running on SQL Server 2005 or SQL Server 2008 that you wish to migrate to SQL Server 2012.

CREATE A BACKP OF YOUR CURRENT DATABASE
1. Open Microsoft SQL Server Management Studio and connect to the database server that is currently storing your Web Tracks Database.
2. Expand Databases and right-click the Web Tracks database you wish to convert then choose Tasks --> Back up…
3. Verify Backup type is Full.
4. For destination choose a filename that you will have access to from your SQL 2012 Server.
5. Click OK to initiate the back.

RESTORE DATABASE TO YOUR SQL 2012 SERVER
1. Open Microsoft SQL Server Management Studio and connect to your SQL Server 2012 instance.
2. Right click on Databases and choose 'Restore Database…'
3. For 'To database:' type in the name you wish to give your database (e.g., WebTracks) -- do NOT select a database from the drop list.
4. In the Source for restore section choose 'From device'. Select the backup file created in step 4 above.
5. In the 'Select the back sets to restore' list choose the most recent backup.
6. Switch to the 'Options' setting in the left hand navigation pane.
7. For the file names you should select where you want the files to be restored to (e.g., C:\Program Files\Microsoft SQL Server 2012\MSSQL11.SQLSERVER2012\DATA\WebTracks.mdf). Repeat for the .ldf file.
8. Click OK to initiate the restore.

SET DATABASE COMPATIBILITY MODE
1. Open Microsoft SQL Server Management Studio and connect to your SQL Server 2012 instance.
2. Expand Databases and then Right-click on your new database and choose 'New Query'
3. Enter the following command in the query window..

ALTER DATABASE [YourWebTracks2012DatabaseName] SET COMPATIBILITY_LEVEL=100

4. Click Execute

REMOVE SQL ACCOUNT TIED TO DATABASE
The SQL Account Associated with the database is from the 2005 or 2008 instance (depending on which you upgraded from). We will need to recreate this in SQL Server 2012.

1. Open Microsoft SQL Server Management Studio and connect to your SQL Server 2012 instance.
2. Expand Databases and Expand the database you created in step 3 in prior section.
3. Expand Security--Users
4. Right click on the Web Tracks account (typically wtUser or WebTracksUser) and choose delete.

CREATE SQL ACCOUNT ON 2012 SERVER
1.  Open SQL Server Management Studio to the server where you created your database.
2.  Open the tree: Server--Security--Logins (right click Logins and choose 'New Login')
3.  Enter a Login name (e.g., WebTracksUser)
4.  Choose 'SQL Server Authentication'
5.  Uncheck 'Enforce Password Policy'
6.  Change the 'Default Database' to your Web Tracks 2012 database.
7.  Switch to the 'User Mapping Page'
8.  Check the box for 'Map' on the database created in Step 6 above.
9.  In the Database role membership area choose 'db_datareader', 'db_datawriter' and 'db_owner'
10. Click the 'OK' button to save the record.

CONFIGURE WEB TRACKS TO USE THE NEW CONNECTION*
1. On the Server where Web Tracks is installed open Web Tracks (it will still be pointing to your old database).
2. From Web Tracks Open Admin--License & Registration.
3. Near the bottom of the content you will see a link to the setup wizard. Follow this link to setup Web Tracks to use your 2012 Database.

*alternatively you may directly edit the db.config file located in C:\Program File (x86)\Gritware\Web Tracks\WebRoot.

TEST SAVING A HELP DESK OR INVENTORY RECORD IN WEB TRACKS

You will need to fix some triggers in the database if you receive the following error...

STATUS: Error Saving Record. Incorrect syntax near '44446'.

If you do not receive this error you are finished with your SQL 2012 Conversion.

We currently do not know the exact reason why this error occurs but we see it more on older Web Tracks systems which have been upgraded from Mouse Tracks. We think it's due to the version of MS Access which was used to upsize the database to SQL Server.

Here are the steps to fix the triggers in the SQL Database…
  1. Open SQL Management Studio and Connect to your Web Tracks database.
  2. Open the tables folder.
  3. For each table in the database do the following...
  • Click the + icon to expand the table
  • Click the + icon to expand triggers
  • Manually modify each trigger (if no triggers are listed move to the next table) by right clicking on the trigger and choosing 'Modify'.
If a trigger contains RAISERROR ##### 'Some Error Message' then it needs to be modified.

Change all occurrences of:
  RAISERROR ##### 'Message'

To:
  RAISEERROR(#####, -1, -1, 'Message')

After making changes to the RAISERROR statement(s) execute the query to commit this change.



Date: Thursday, January 2, 2014

Knowledge Base Search