PART 2: SQL Server Program Primary Installation

06/02/2016 - Added three graphics to this documentation.

NOTE: Depending on how you originally set up vMedia, it might be located in the root of \vMedia rather than in the root of \IMASTER. If this is the case, any references to an “\IMASTER” folder should be substituted with “\vMedia.”

Prerequisites

Install the latest vMedia update from the Vertican website if it is not already current.
Make a backup copy of the \IMASTER folder.
VM2SQL MUST BE RUN ON THE PRODUCTION DATABASES, NOT A COPY!! The import process updates a “status” flag in the database to indicate when a document has been successfully imported to a SQL database. Running the import on a copy of a production database risks duplicating all documents if the import is run in “Append” mode at a later date on the production database.
Users must be out of vMedia to convert an existing database to SQL to add the RECLINK field.
If there are any automated (batch) processes that run EXPORT2CM, they must be disabled from running until VM2SQL has completed its initial run to a new database on SQL.

For New SQL Installations

vMedia has been enhanced to accept the variables UseSQLServer and DisableDBF. These variables have been promoted into the [Global] section of the IMASTER.ini file. Specifying these values in the [Global] section will affect vMedia system-wide across all Machine IDs as long as a contravening value is not specified under the [StationX] section of the INI file.

Configuring vMedia for SQL

Enable SQL in IMASTER.INI.
Open \IMASTER\IMASTER.INI in any text editor (such as Notepad). For each Machine ID in use, locate the section header for the station configuration. This will look like [StationA], where A corresponds to the Machine ID.

Add a new line below each corresponding [Station..] line in the file:

Type: UseSQLServer=1

Add a new line below the UseSQLServer= line:

Type: DisableDBF= choose the appropriate option for your configuration from options below:
0 = If legacy importers such as the Hanna Importer will be used.

1 = If the SQL database is going to be used exclusively (no DBF table).

NOTE: The Spectrum Importer is a replacement for the Hanna importer that offers greater speed, greater control over the image output format and quality, and more flexibility for indexing expressions. The Spectrum Importer is SQL only and therefore does not require that legacy DBF support be enabled.

Configure EXPORT2CM to utilize SQL:

Locate the section header [ExportCM] in the IMASTER.INI file.

NOTE: Regarding DBNAME: Only specify the path and the first six characters of your vMedia database filename.

Add a new line below CMEXEC=

Type: UpdateType= choose the appropriate option for your configuration from options below:
1= Update using SQL tables as the data source. DBF tables are not used (SQL only).

2 = Update using DBF tables as the primary data source, but also update the corresponding tables on SQL server if a file DSN exists for the database. UpdateType should be 2 ONLY if legacy importers such as the Hanna Importer will still be used. Otherwise UpdateType should be set to 1.

Save and close the modified IMASTER.INI file.

Create the vMedia SQL Database

On the SQL Server using the SQL Server Management Tools or other configuration tools, create a database on the SQL server. The database can be named as desired, although a descriptive name such as "vMedia" is recommended.

Configure access and authentication to the created database for the users. For ease of access, set the authentication to use the network credentials.
Create a file DSN.

NOTE: In the following example, the items in italics must be substituted with settings appropriate for your site. The remaining lines must be typed as they appear.

Open any text editor (such as Notepad). Type the following:
[ODBC]

DRIVER= SQL Server Native Client 10.0

DATABASE=SQL database name created above

APP=vMedia

Trusted_Connection=Yes

SERVER=your SQL server name

Description=vMedia SQL Server Access

CAUTION: In the next step, do not overwrite the DBF table! If unsure, make sure you have a backup copy of your IMASTER folder before choosing to replace any file.

Save this DSN file with the same name as the vMedia database CF table, but with a DSN extension (i.e. if the primary CF table is F:\imaster\CLS000CF.DBF, then save the file DSN as F:\imaster\CLS000CF.DSN)

Replicate existing documents in DBF table to SQL

NOTE: Depending on the number of documents and the speed of your network this next step may take a considerable time to complete. A progress bar will be provided. Typical upload speeds average approximately 12,000-30,000 documents per minute.

Run the \IMASTER\VM2SQL.EXE module.
The pre-configured database in the “vMedia Database to Use” field is the default database configured in the IMASTER.INI file for the Station ID on which VM2SQL is being run. If this is not correct, use the button to open a browse window and locate the appropriate database.
Set the “DBF to SQL Options” to “All Documents” to create the SQL table on the SQL Server and populate it.
The “Attempt Exclusive Access to DBF Tables” box should be checked. All users must be out of vMedia.
If you are also importing archive databases (“overflow tables”), they should be imported at this time by clicking the “Overflow Tables” button and selecting the tables to import using the browse window.
Choose [Start Upload] to begin the process of copying the existing data to the corresponding table on the SQL server.

NOTE: If the table already exists in SQL, you will be prompted to either “Overwrite SQL Table”, “Append to SQL Table” or “Cancel.”

Choosing the “Overwrite SQL Table” option is destructive and will remove all of the existing document entries (but not the images themselves) from the SQL Server. Only the documents migrated from the DBF files in the current session will be active in SQL Server.

This completes the initial deployment.

Next Topic

SQL Server Usage Instructions