10.0.2 Upgrade Scripts

The following implementation scripts must be run on your Responder SQLServer database to add the correct tables, views, and indices to upgrade Responder to version 10.0.2. When upgrading Responder execute ONLY the Upgrade script(s) that fall between your previous release and the new release to which you are upgrading. Do NOT execute any Implementation Scripts.

Responder requires two databases: Responder and Archive. Some scripts are run on the Responder database and some on the Archive database. In the list below, the database(s) on which the script should be executed is in parentheses.

By default, these scripts are installed here: C:\Program Files (x86)\Miner and Miner\Responder\Developer Resources\10.0.1 SP1 - 10.0.2 Upgrade Scripts\MS_SQL.

1002_01_RxTables_mssql.sql (Responder)

1002_02_ArcJoinView_mssql.sql (Responder)

1002_03_ArchiveTables_mssql.sql (Archive)

1002_04_SnapshotIsolation.sql (Responder and Archive)

The instructions below do not execute the scripts in the order listed above. Note that the first, second, and fourth scripts are executed on the Responder database first. The third script (and fourth script again) is executed on the Archive database last. The instructions execute them in this order simply for the implementer's convenience.

Open the SQL Server Query Analyzer window, connecting to the Responder database as the schema owner (e.g., RESPONDER).
Execute the 1002_01_RxTables_mssql.sql script. This script creates the database tables required by Responder.
Repeat step 2 for the 1002_02_ArcJoinView_mssql.sql script.
Open the SQL Server Query Analyzer window, connecting to the Archive database as the schema owner (e.g., ARCHIVE).

Execute the 1002_03_ArchiveTables_mssql.sql script. This script creates the database tables required by Responder Archive Explorer.

The 1002_04_SnapshotIsolation.sql script requires that the user executing it have sysadmin privileges. To execute the script, only the connection running the script should connect to the database. If any other connections are present, the query will try to execute indefinitely.

Execute 1002_04_SnapshotIsolation.sql script on both the Archive and Responder databases. This script configures your SQL database to use Read Committed Snapshot Isolation. You will receive errors if this is not configured. Responder supports Read Committed Snapshot Isolation to provide performance enhancements.
Execute this script even if you already have regular Snapshot Isolation enabled.

Optional Script to Improve Performance (recommended)

For the 10.0.2 release, the Responder team collaborated with Microsoft to identify ways to optimize database performance on a SQL Server database. This collaboration determined that the NUMERIC(38,0) data type on many columns had a negative impact on performance. To improve performance, Telvent recommends that you change these NUMERIC(38,0) data types to INT. The 10.0.2 release includes an executable that creates a SQL script to perform this modification in your database. Follow the steps below to run this tool and execute the resulting script.

SQL Server Versions: This tool works against all versions of SQL Server 2008. If you're using an older version of SQL Server, Microsoft provides the necessary support files as standalone tools and free installations. These can be downloaded from the Microsoft download website on the Microsoft SQL Server 2008 Service Pack 2 Feature Pack.

The following subcomponents of the feature pack are required:

This step is optional. You can specify which Responder tables are impacted by the script. If there is a table that has a column with a Numeric data type and you do not want it changed to an Int data type, then you will need to create a text file that lists all of the tables that can be modified. When creating this text file, place each table name (without the schema) on a line with no extra characters (or spaces). Each table name will be on its own line.

Note that a table that provides a foreign key to a table in the list will be modified as well, regardless of whether it is in the list or not.

Open a command prompt and change the directory to the one in which SqlNumericColumnsUpdateScriptGenerator.exe is stored. By default, this tool is installed here: C:\Program Files (x86)\Miner and Miner\Responder\Developer Resources\10.0.1 SP1 - 10.0.2 Upgrade Scripts\MS_SQL\.
Next, execute sqlNumericColumnsUpdateScriptGenerator.exe with the necessary parameteters (see table below). The table below shows multiple ways to enter the parameters (-s, -S, --server). You need choose only one format. This creates a SQL script.

Parameter Description

-s, -S, --server

This is the SQL Server instance to connect to. There is no default value.

-d, -D, --database

This is the Responder SQL database. The default value is Responder.

-u, -U, --username

This is the user name for the login. If you omit this parameters, the tool will use Windows Authentication and the user currently logged into the local machine.

-p, -P, --password

This is the password that corresponds to the user name provided.

-o, -O, --output

Indicate the output file in which the script is stored. If you do not specify an output file, the script will appear in the console.

-t, -T, --tables

This is a text file that contains a list of the Responder tables to be updated. If you do not specify this text file, then all tables that start with RX_ will be impacted.

-?, -h, -H, --help

Display the help information.

Parameter	Description
-s, -S, --server	This is the SQL Server instance to connect to. There is no default value.
-d, -D, --database	This is the Responder SQL database. The default value is Responder.
-u, -U, --username	This is the user name for the login. If you omit this parameters, the tool will use Windows Authentication and the user currently logged into the local machine.
-p, -P, --password	This is the password that corresponds to the user name provided.
-o, -O, --output	Indicate the output file in which the script is stored. If you do not specify an output file, the script will appear in the console.
-t, -T, --tables	This is a text file that contains a list of the Responder tables to be updated. If you do not specify this text file, then all tables that start with RX_ will be impacted.
-?, -h, -H, --help	Display the help information.

Run the SQL script created by the executable. Depending on the parameters you set, this will be in the output file you designated or in the command prompt window.