Web Tracks
    Help and Documentation
BookmarkPrint
Display Legacy Contents

Home > Administrative > Upsizing to SQL Server

Upsizing to SQL Server

IMPORTANT: Web Tracks will only operate with SQL Server with an Enterprise Registration Key.

If you purchased the Enterprise Edition of Web Tracks, you have the option of upsizing your Database to SQL Server. Doing so eliminates the corruption issues associated with large Microsoft Access databases, speeds up performance across WAN connections, and provides a highly scalable database.  Web Tracks supports SQL Server 2005, 2008, 2012 and 2014.

The following instructions are for converting an existing Access database to SQL Server.  If you wish to start with a new SQL Server database you can do so by Opening Admin--Licenses and Registration.  Near the bottom of the form there is a link to the Setup Wizard.  You may manually visit this link by visiting: http://YourServer/WebTracks/setup/SetupWizard.aspx. (to use this link you must be on a browser on the server where Web Tracks is installed or be signed into the current site as a Web Tracks administrator).

Step-by-step Instructions for converting your database from MS Access to SQL Server 

Step 1 - Launch Microsoft Access

IMPORTANT: You must use a copy of Microsoft Access 2003, 2007 or 2010 to run the Upsize Wizard (DO NOT USE Access 97 or 2000).  To Upsize a Database to SQL Server 2008 or 2012 you must use Access 2007 SP2 or newer.

Step 2 - Open the WebTracks.mdb file using the following method 

  1. Under the Open a File section, choose More Files...
  2. Browse to the location of your WebTracks.mdb file and highlight it (do not double-click).
  3. From the Open command drop down list, choose Open Exclusive. If you are unable to open this exclusively, make sure other users do not have this file open then try this again.

Step 3 - Upsizing the Database

  1.  From the Tools Menu, choose Database Utilities--Upsizing Wizard.
  2.  Select the Create New Database Option.
  3.  Click Next.
  4.  Type in the name or IP Address of your SQL Server Database Server
  5.  Enter an Administrative Logon ID and Password for the SQL Database Server (or use the trusted connection option if your current login ID has permissions to create a database on your SQL Server).
  6.  Enter the name of the database you wish to create (e.g., WebTracksSQL).
  7.  Click Next.
  8.  Click the >> command to have all tables exported.
  9.  Click Next.
  10.  Select Indexes, Validation Rules, Defaults, and Table Relationships.
  11.  For the Table Relationships option, choose Use triggers.
  12.  From the Time Stamp Option, choose 'No, Never'.
  13.  Make sure that the 'Create Table Structure Only' option is unchecked.
  14.  Click Next.
  15.  Select the No Application changes option.
  16.  Click Next.
  17.  Click Finish.

Step 4 - Create User Account In SQL 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 the one created in Step 6 above.
  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.

Step 5 - Modify the db.config file

NOTE: as an alternative to manually editing the db.config file you may open http://YourServer/WebTracks/setup/SetupWizard.aspx (to use this link you must be on a browser on the server where Web Tracks is installed or be signed into the current site as a Web Tracks administrator).  Follow the Wizard through the path of connecting to an existing SQL Database.

Edit the db.config file located in the ‘C:\Program Files\Gritware\Web Tracks\WebRoot’ folder.

Rename the entry for the current connection name from "WebTracks" to "NOT_USING"...

<connectionStrings>
  <add name="NOT_USING" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|WebTracks.mdb" providerName="System.Data.OleDb"/>
</connectionStrings>

Add a Connection String (name must be "WebTracks") to point to your SQL Server Database...

<connectionStrings>
  <add name="NOT_USING" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|WebTracks.mdb" providerName="System.Data.OleDb"/>
  <add name="WebTracks" connectionString="Provider=sqloledb;Data Source=mySQLServer;Initial Catalog=WebTracksSQL;User Id=WebTracksUser;password=mypassword"/>
</connectionStrings>