How to use SetupMDB to upgrade the backend MDB database for SQL Server

Document ID : KB000017488
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Basic guide on how to use SetupMDB.

Solution:

Question: What is SetupMDB?

Answer: It is a script. It is included with many of the binary cumulative patches for Service Desk, as part of the DB update package. Regardless of which cumulative patch you are installing, the usage of SetupMDB remains the same.

Question: How do you get at it?

Answer: When you install a given SD cumulative patch and some standalone patches, there will usually be a subdirectory, under NX_ROOT/patches. The subdirectory is usually called "cum1" or "cum2", but the name might vary. Either way, there is going to be a CAZ file in there, usually of the form "MSSQL_MDB.CAZ" (for SQL Server) or "ORACLE_MDB.CAZ" (for Oracle). Maybe even "MDB_50724.caz" Inside this CAZ file, there will be several install files, and the setupMDB script.

Question: What do I do with this?

Answer: When you apply a cumulative patch, the post install steps often will instruct you to run SetupMDB to perform maintenance work on the backend database.

Question: Why do I need to do this?

Answer: Many of the cumulative patch updates will rely on schema and data updates which the setupMDB patch will apply directly to the backend database.

Question: Is it possible to run this script from another server, connecting via network connectivity to the backend SQL Server?

Answer: Running the script from a remote SD Client connecting to the backend server runs the risk that the script will be interrupted by another process or a network communication fault. It is strongly advised to run the install and update on the direct SQL Server.

Question: What will I need to run this script on the backend database?

Answer: Long story short, you will need:

  • The MSSQL_MDB.CAZ file

  • The Java Runtime Environment installed

  • Cazipxp

Question: So I have all these items over to the SQL Server. What next?

Answer: This is what you do to install the MDB patch.

  1. Install the JRE on the SQL Server.

  2. Put the Cazipxp executable and the MSSQL_MDB.CAZ file in a temp directory

  3. Run Cazipxp -u MSSQL_MDB.CAZ

  4. The SetupMDB batch file is created, along with other supporting files. From here, run SetupMDB.

Question: Is that it? Just do the above and I have my database updated?

Answer: Not exactly. There are things to consider.

  • Is your database part of a cluster? You might have to run this update on all nodes in your cluster.

  • Is your database running on a named instance? If it is, you'll need to add additional parameters so that it runs on the right instance.

Question: What can happen when running SetupMDB?

Answer: Ideally, the backend schema will be updated with whatever changes the SetupMDB was trying to put in. Other than that, the install might fail because it was unable to connect or had insufficient rights to update the database. Another is that the MDB might get created afresh, which is applicable if the MDB was not even there in the first place.

Question: What are some of the parameters I should look out for in SetupMDB?

Answer: These are the big ones to keep in mind.

  • JRE_DIR: This is the JRE install directory. It should be written in the 8.3 notation of the specified location (which you can get by running "dir /x").

    • For example: "C:\PROGRA~2\CA\SC\JRE\1.6.0_30". Under this directory, there should be a bin and a lib directory.

  • DBHOST: This is what you use to specify a named instance that SQL is running on.

    • For example: SQLSERVER1\namedinstance

  • DBPORT: This specifies the port that the named instance runs. It is important to note that a given SQL Server will have each instance present running on its own database port. While the default is 1433, all instances of SQL on the same server run on their own unique ports.

  • DBUSER/ DBPASSWORD: This is the SQL user that will be used to run the install and its password. Generally speaking, the user should be sa, but in lieu of being given the sa credentials, one can run using any user, so long as it is a user defined in SQL Server, and:

    • The user's default schema is set to "dbo" on the mdb (user mapping)

    • Default database is set to "master" (General setting on the user properties)

    • Server Roles for sysadmin and public are turned on.

    • User Mapping for master, model, msdb, tempdb are all set to dbo schema

    • For the MDB database, the user should have db_owner and public privileges.

  • DBDRIVER and WORKSPACE: These can both be set to Service_Desk

  • debug: Add this flag to write out the entire run to the display screen.

Question: What are ALL of the available parameters available for SetupMDB?

Answer: This is a comprehensive list of all such parameters, what they do, any known sample values and their defaults. Required parameters are underlined.

  • DBVENDOR: DBMS to be used (valid values are mssql or oracle).

  • JRE_DIR: JRE Directory path

  • MDB_SOURCE_DIR: mdb install media source (default is current directory)

  • MDB_PRIVATE_DIR: private mdb destination directory (default is current directory)

  • MDB_TARGET_DIR: target directory for log output (default is current directory)

  • MDB_COMMON_DIR: MDB Common directory (usually ignored)

  • DBNAME: Database Connect ID (Oracle only, default: mdb)

  • ORA_SERVICE_NAME: Service Name (Oracle only, default: DBNAME)

  • DBHOST: Database server name (default: localhost)

  • DBPORT: JDBC Port (for SQL Server, default: 1433)

  • DBUSER: login username (mssql: dbms user; oracle: SYS user)

  • DBPASSWORD: Corresponding password for the DBUSER parameter

  • MANIFEST: mdbtools driver name (usually ignored)

  • WORKSPACE: mdbtools workspace name (can be set to "Service_Desk")

  • COMPAT104: Add full MDB 1.0.4 schema (usually ignored)

  • ORA_DATA_TBLSPACE: data tablespace name for oracle (default: MDB_DATA)

  • ORA_DATA_SIZE: data tablespace size for oracle (default: 400)

  • ORA_INDEX_TBLSPACE: index tablespace name for oracle (default: MDB_INDEX)

  • ORA_INDEX_SIZE: index tablespace size for oracle (default: 100)

  • ORA_TBLSPACE_PATH: tablespace path for oracle (opt w/omf)

  • MDB_ADMIN_PSWD: mdbadmin password (required for oracle only)

  • PRECHECK: validate without doing anything (default: unset)

  • PRECHECK_MSGDIR: validation status msg file (default: console)

  • MDB_EXIT_SCRIPT: exit script using /B {yes,no} (default: yes)

  • debug: Run with "echo on" (default: unset)

Question: So what's an example command for setupmdb?

Answer: The following commands are to be executed from the local SQL Server command prompt, from the location where setupMDB is placed, and in a single line:

This one usually works.

Standalone SQL Server (no named instances)

  setupmdb 
  -DBVENDOR=mssql
  -JRE_DIR=C:\PROGRA~2\Java\jre7
  -DBUSER=sa
  -DBPASSWORD=<password>
  -DBDRIVER=Service_Desk
  -WORKSPACE=Service_Desk

SQL Server with a named instance (SQLSERVER/namedinstance)


  setupmdb       
  -DBVENDOR=mssql 
  -JRE_DIR=C:\PROGRA~2\Java\jre7
  -DBHOST SQLSERVER\namedinstance
  -DBPORT=1433
  -DBUSER=sa
  -DBPASSWORD=<password>
  -DBDRIVER=Service_Desk
  -WORKSPACE=Service_Desk

Standalone SQL Server (no named instances) with debug flag:


  setupmdb       
  -DBVENDOR=mssql 
  -JRE_DIR=C:\PROGRA~2\Java\jre7
  -DBUSER=sa
  -DBPASSWORD=<password>
  -DBDRIVER=Service_Desk
  -WORKSPACE=Service_Desk
  -debug

One can also redirect the debug output, to an output file (ie: output.dat):


  setupmdb -DBVENDOR=mssql -JRE_DIR=C:\PROGRA~2\Java\jre7 -DBUSER=sa       
  -DBPASSWORD=<password> -DBDRIVER=Service_Desk -WORKSPACE=Service_Desk -debug > output.dat 

Question: How do I figure out which port to use for a given named instance?

Answer: The ports are assigned dynamically. Running "netstat -a -b" on the SQL Server, one would find:

Default instance of SQL:


  TCP   [::]:1433   SQLHOST:0   LISTENING
  [sqlservr.exe]

Only other entries found for sqlservr.exe (named instance)


  TCP   [::]:2866   SQLHOST:0   LISTENING       
  [sqlservr.exe] 

One may also see:


  TCP   [::1]:49573   SQLHOST:0   LISTENING       
  [sqlservr.exe]

49573 is not a valid port here ([::1] vs [::]). The valid ports are 1433 (default instance's port) and 2866 (the named instance's port).

Alternately, to assign a TCP/IP port number to the SQL Server Database Engine

  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

  2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

  3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

  4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

  5. In the console pane, click SQL Server Services.

  6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

Question: How do I tell that the setupMDB ran successfully?

Answer: In the location you are running setupMDB, you will find two files: install_MDB.log and install_MDB_msg.log. Opening these two files in notepad:

  • install_MDB.log: At the bottom of the file, you will see this for a successful run:

    • 04-16,11:52:45 Setupmdb_post exit /B return code=0

    • 04-16,11:52:45 Updating the MDB Common Component Directory

    • 04-16,11:52:45 Copying the MDB resources to 'C:\Program Files\CA\SC\Mdb\Windows'

    • 04-16,11:53:31 MDB setup completed successfully

  • install_MDB_msg.log: This is a smaller file, but would contain:

    • 04-16,11:53:31 MDB setup completed successfully