Rev 05112012 Page 1 of 5
ProTracker Software, Inc.
support@protracker.com Many of our customers run the process themselves with great success. We are available to run the process for you or we can be available on standby from 9:00-5:00(Eastern) to provide assistance if needed. The whole process takes about 1 hour. Downloading the installation files is usually the longest part of the process.
Please read the Help articles about SQL Server. Go to Help> ProTracker Advantage Help and type “SQL”
in the index tab. Please review all the contents under the SQL Server section.
SERVER SETUP
1. Make a backup copy of your AdvantageData.mdb. (Copy and paste works fine) If you are not sure where your database resides, open ProTracker and Select Help> About ProTracker Advantage from the menu.
2. Download and run the installer. Then, using SQL Server Management Studio, confirm
that the blank ProTracker database is installed and running.
32 bit: http://www.protracker.com/download/files/setupprotrackeradvantageserver_x86.exe 64 bit: http://www.protracker.com/download/files/setupprotrackeradvantageserver_x64.exe What this installer does:
Installs SQL Server 2008 R2 Express
Installs SQL Server Management Studio
Attaches a blank instance of ProTracker Database
Schedules a task to make a .bak file every day at 11p (overwrites previous .bak file)
Enables TCPIP and Named Pipes, enables SQL browser service
Disables firewall to allow client connections
*Please note: Once you connect and run the database on the 2008 SQL Server, it will no longer run under 2005 SQL Server. If you plan to use 2005 SQL Server express, please ask for alternate installation steps.
WORKSTATION PREP
Each workstation that uses Advantage must have the SQL Native Client Driver 10.0 installed prior to connecting to the SQL Server database. Any workstations with a recent (last 2 years) installation of ProTracker should already have the latest driver.
If you have a 32-bit workstation, install this file:
https://www.protracker.com/Download/Tools/sqlncli_2008.msi If you have a 64-bit workstation, install this file:
https://www.protracker.com/Download/Tools/sqlncli_2008_64bit.msi
Rev 05112012 Page 2 of 5
ProTracker Software, Inc.
support@protracker.com
RUN THE CONVERSION PROCESS FROM ANY WORKSTATION
1. Go to a workstation and open ProTracker Advantage. Make sure everyone else is out of ProTracker Advantage. Go to Operations> Users to find out who is connected.
2. Select Tools> Convert 4.1 Database to SQL from the menu 3. Browse to your current Access database (AdvantageData.mdb)
4. Select the destination SQL Server on the server. Typical connection values are:
Authentication: SQL Server
Database: SERVERNAME\SQLEXPRESS Username: sa
Password: SQL.admin
5. Wait for the conversion process to complete.
6. When prompted, connect to the SQL database
7. Enter new SQL registration key which we will provide to you at no cost for existing customers.
Use this link to contact customer support for a new key.
ON EACH ADDITIONAL WORKSTATION
The next time users open ProTracker Advantage, they will be prompted with a message that the database has been converted to SQL, connect now? If they select Yes, the connection is made to the new SQL database. If they select No, they will connect to the Access version. If they connect to the Access version, each time they open it, they will be reminded that the database has been converted and given a chance to connect to the SQL. Any data entered into the Access database version will lost.
When they connect to ProTracker, they will be prompted to connect to the new SQL database. After they click YES on the prompt, this window should appear. Enter the Server Name and database name- ProTrackerAdvantage. The default password is SQL.admin
AFTER THE CONVERSION
We recommend that once you make the switch to a SQL database and confirm that the data is complete, you move and/or rename the Access (.mdb) version of the database so users cannot accidently connect to it.
As long as your Access version license key is valid, you can still open the Access version but data entered into the Access copy will not show up in the SQL copy and vice versa. To get to the Access version after a SQL connection, go to Tools> Options> Connect to Access database in the Assistant area. Browse to locate the database. To get to a SQL version, go to Connect to SQL Server database in the same window.
Rev 05112012 Page 3 of 5
ProTracker Software, Inc.
support@protracker.com
** IMPORTANT: Please confirm that the backup script is working correctly and that the SQL database is getting backed up each night.
DOCUMENTS AND PHOTOS
If moving the client documents and photos are part of the migration to a SQL Server, take care to move them into a location where you can mirror the current path. In order to maintain the integrity of the document paths stored in ProTracker, the mapped drive path must match the old path exactly. Go to Operations> Database Admin to view the current path.
Trouble-shooting
The latest SQL Server setup installer should automatically configure the follow settings. This
information is provided for trouble-shooting when a user cannot connect to the database on
the new server after the upgrade process has been completed.
Workstation
Verify correct server name and database.
The problem may be as simple as an incorrect entry. Verify the Server Name is selected from the drop- down list or type in the name of the server. You may or may not need the \SQLEXPRESS . You may need to type in the name of the ProTrackerAdvantage database if it doesn’t show up in the database list.
Server
Start or Restart the SQL Browser Service
Perform these steps on the server hosting your SQL database:
1. Click Start > Settings > Control Panel.
2. Double-click Administrative Tools.
3. Double-click Services.
4. Locate SQL Server (SQLEXPRESS) in the list of services.
5. If the Status is not "Started", right-click and select Start.
6. If the Status is "Started", right-click and select ReStart.
7. Locate "SQL Server Browser" in the list of services 8. If the Status is not "Started", right-click and select Start.
9. If the Status is "Started", right-click and select ReStart.
Rev 05112012 Page 4 of 5
ProTracker Software, Inc.
support@protracker.com
Allow port 1433 through the firewall
Perform these steps on the server hosting your SQL database:
1. Click Start>Settings>Control Panel 2. Double-click Windows Firewall 3. Click the Exceptions tab 4. Click the Add Port button
In the Name field, enter SQL TCP
In the Port Number field, enter 1433
Select TCP
Click OK
5. Click the Add Port button
In the Name field, enter SQL UDP
In the Port NUmber field, enter 1433
Select UDP
Click OK 6. Click OK
Disable the Windows Firewall
Perform these steps on the server hosting your SQL database:
1. Click Start>Settings>Control Panel 2. Double-click Windows Firewall 3. Select "Off"
4. Click OK
Add network trusted users
Perform these steps on the primary domain controller. This may be the same server hosting your SQL database:
1. Click Start>Administrative Tools>Active Directory Users and Computers 2. Expand the entry for your domain, ex "YOURDOMAIN.local"
3. Expand the entry for your domain users, ex "YOURDOMAINusers"
4. For each user who will need access to your SQL Server, perform the following steps:
Right-Click the user name and select Properties
Click the Member Of tab
Click the Add button
Type "SQLServer" and click the Check Names button
Select the entry that begins with "SQLServer2005MSSQLUsers$"
Click OK
Click OK
Rev 05112012 Page 5 of 5
ProTracker Software, Inc.
support@protracker.com
Click Apply
Click OK
These users can now connect to the database using Windows authentication.
Add a User Account to SQL Server
Perform these steps on the server hosting your SQL database:
1. Click Start>Run
2. Type "cmd" and click OK 3. In the command window type:
osql -E -S <COMPUTERNAME>\SQLEXPRESS -Q "sp_addlogin @loginame='<UID>',
@passwd='<PWD>'"
Where:
ComputerName> is the name of the computer hosting the SQL database
UID> is the name of the account you wish to add, such as "sa"
PWD> is the password for the account you wish to create, such as "SQL.admin"
Press Enter
4. Close the command window