This module may be licensed from Data-Basics if your internal IT staff is sufficiently experienced with database administration for Sybase / SQL Server.
This process should only be undertaken if you are VERY familiar with database manipulations. If you don't understand what is being done during this process then you shouldn't be performing the operation. In other words if this document makes 100% perfect sense then you are qualified to use it. If not, stay away from the function.
This document details the process for converting an existing Sybase Sampro database to a matching SQL Server Sampro database. This involves the following general steps:
- Preparing the target SQL Server database and the corresponding Data Source.
- Running the conversion function. This will create and load the tables in the target database.
- Performing the standard Sampro version update process.
0. Add the Sybase to SQL Server function to SAM Pro.
a. If you have not updated to a new version of SAM Pro recently, the Start Sybase to SQL Conversion function may not appear on your list of Starter functions. If this is the case, run Start Function List Update.
b. Log out and back in again; the Start Sybase to SQL Conversion should now appear your list if you are logged on as a member of User Group All. If you are not a member of this Group, you will need to add it to your User Group. Note: access to this function should be severely restricted.
c. Note that you must have a vrgstry.cus file in place that allows you access to this feature; this file may be obtained from Data-Basics when your license has been secured.
1. Prepare the SQL Server Database
a. Create a new database within the instance.
b. Give the new database the appropriate privileges. The user must have sufficient privileges to create tables in the database. The user must also have ‘Bulk Copy’ privileges.
2. Create and validate a Data Source which references the newly created database.
3. Identify the Appropriate Paths
The files used to exchange data between the Sybase server and the SQL Server instance must be stored in a folder accessible to
a. The Sybase server
b. The SQL Server instance
c. The Sampro image.
These may require up to 3 different paths. If you are using a UNC (i.e. a pathname beginning with ‘\\’), the same path should apply for all 3 usages.
Before continuing to the next step, identify the pathname or pathnames required.
4. Copy the database tables from the current database to the target database
a. Run the Start Sybase to SQL Server function.
b. Fill out the options screen. See the sample below:
c. The following fields are required:
Target Data Source Name
i. File Path From Sampro
ii. File Path From Sybase
iii. File Path From SQL Server
d. If you make an entry in the Default File Path field, this entry will automatically propagate to the three File Path fields mentioned above.
e. If you want the conversion to write an activity log, enter the filename. This file will be written within the folder used for staging the data.
f. If you want the conversion to delete each work file after the data has been exported, check the Delete Work Files box.
5. Press the Go! Button. Extensive error checking of path names and database privileges will take place. You must resolve any problems before you will be allowed to proceed.
6. If no errors are identified, you will be asked to confirm that you want to proceed.
a. Empty tables will then be created on the target database. If a table already exists in the target database, It is assumed that its schema is correct and no error message is issued.
b. Next, the contents of all those files will be copied from the current database to the target database.
c. Before moving data to a target database table, the number of rows in the target table is checked. If the table already has rows, no data is copied.
d. An error message may be displayed. This usually implies that an invalid character (e.g. ‘|’) has made its way into a table.
e. After all the tables have been copied, the Revision Level of the target database will be bumped down one level.
f. Finally, a completion message will be displayed.
7. Check the log for errors.
8. Take corrective action if any error messages have been displayed or if the log shows any errors. This might include modifying the contents of the source database (for example, removing any instances of the ‘|’ character in a database table).
9. If you need to reload a table in the target database (e.g, one you had to remove the ‘|’ character from), drop the table and repeat the above process. The dropped table or tables will be re-created and loaded. All other tables will be left untouched.
Once all errors are corrected, you will proceed with the standard SAM Pro upgrade processing on the Target Database
10. Log out of the current database.
11. Log into the target database.
12. Wait for the standard upgrade processing to complete. This processing will establish the appropriate indexes and foreign keys for the database.