Home > Administrative > Upsizing to SQL Server

Upsizing to SQL Server

Step 1 - Launch Microsoft Access

IMPORTANT: You must use a copy of Microsoft Access XP, 2003, 2007 or 2010 to run the Upsize Wizard (DO NOT USE Access 97 or 2000).  To Upsize a Database to SQL Server 2008 you must using 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 to this 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., wt_user)
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

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

Rename the entry for the MS Access Database….

<connectionStrings>
  <add name="WebTracks_sql" connectionString="Provider=sqloledb;Data Source=mySQLServer;Initial Catalog=WebTracksSQL;User Id=sa;password=123"/>
  <add name="NOT_USING" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Gritware\Web Tracks\WebRoot\App_Data\WebTracks.mdb" providerName="System.Data.OleDb"/>
</connectionStrings>

Modify the Connection String to point to your SQL Server Database and change the name from WebTracks_sql to WebTracks

<connectionStrings>
  <add name="WebTracks" connectionString="Provider=sqloledb;Data Source=mySQLServer;Initial Catalog=WebTracksSQL;User Id=wt_user;password=123"/>
</connectionStrings>