Basic guide on how to use SetupMDB.
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:
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.
Question: Is that it? Just do the above and I have my database updated?
Answer: Not exactly. There are things to consider.
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.
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.
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
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:
Release: UAPMAC990JPP-12.9-Asset Portfolio Management-Asset Configuration