• No results found

Upgrade ProTracker Advantage Access database to a SQL database

N/A
N/A
Protected

Academic year: 2021

Share "Upgrade ProTracker Advantage Access database to a SQL database"

Copied!
5
0
0

Loading.... (view fulltext now)

Full text

(1)

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

(2)

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.

(3)

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.

(4)

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

(5)

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

References

Related documents

• In addition, allow ½ mark for relevant trends which form part of the business risk evaluation, e.g.. Allow 1 mark per audit risk for relevant materiality calculations, to a maximum

Partners from North East of Gothenburg: the Community Center Hammarkullen, the Folk High School in Angered, the Library and Citizens’ Advice Bureau operated by the City District of

The San Francisco Tech Pulse is constructed with indexes that reflect production of technological goods, such as personal computers and communication equipment, employment in

The successful qualification of the modernized floater includes buoyancy tests with different masses, a long duration buoyancy test, a recovery hoisting test and the

High-reso- lution diatom and sediment grain size data were analysed using time series analysis techniques (spectral, wavelet) and the results of these analyses were compared to

For example, Moore &amp; Green (2012) observed that for expert users a single search is unable to provide the level of specificity needed for a particular subject specialty; while

Scientists can have many incentives to move, citing both salary and career progression, as the quality of their research environment, availability of funding, or the opportunity

Connect to the database using the username and password of iuclid5 in order to verify that the database server accepts a connection made by iuclid5 user..