WhatsUp Professional / Goldv11- How to move from a MSDE database to a SQL 2000 database

Product: WhatsUp Professional, WhatsUp Goldv11
Version: 2005 SP1 - 2006, v11
Platform: Windows 2003 Server, Windows XP SP1 or later, Windows 2000

Question/Problem: I want to use my SQL server to store the database information used by WhatsUp Professional / Goldv11. How can I do this?

Answer/Solution: This article has the following prerequisites:

1.) A running, configured copy of Microsoft SQL Server 2000 (English), Service Pack 3a. **Please Note: MS SQL Server 2005 and 2005 Express are not supported at this time.**** Also non-English installations of SQL Server are not supported. Use of a non-English SQL back-end may cause loss of data. **
2.) The SQL Server must support SQL Authentication. This is enabled by default.
3.) The SQL Server must have the TCP/IP network library enabled. This is enabled by default.

PART I

1.) On the WhatsUp machine, make a backup of the database. This is done by selecting the Database Utilities option from the Tools menu and then selecting Back Up SQL Database. We will save our database as database.dat in the root of D:\ (D:\database.dat).
2.) Close the WhatsUp console and stop the Ipswitch WhatsUp Engine service from the Windows Services control panel.
3.) If the SQL server to which you will be migrating the database is on another machine, copy the .dat file created in Step 1 to the SQL server. Otherwise, proceed to Part II.

PART II

1.) On the SQL server (in our example, the SQL server's computer name is SQL_1), open Enterprise Manager and create a new database named WhatsUp. **Please Note: If the default Collation name on your SQL server is not set to Latin, make sure to set the WhatsUp database to use 'Latin_General_CP1_CI_AS' for the Collation name.**
2.) Once the database has been created, right-click it and select Properties.
3.) Select the Data Files tab and note the path listed in the Location column. In our example, it is C:\Program Files\Microsoft SQL Server\MSSQL\data\WhatsUp.MDF.
4.) Select the Transaction Log tab and note the path listed in the Location column. In our example, it is C:\Program Files\Microsoft SQL Server\MSSQL\data\WhatsUp.LDF.
5.) Close Enterprise Manager and any other applications which may be accessing the WhatsUp database on the SQL server.
6.) Open a command prompt on the SQL server and execute the following command to import the database into SQL. If your SQL install is a named instance, rather than the default instance, specify your SQL server name as SQL_server_name\Instance_name.

osql -E -S <SQL_server_name> -Q "restore database WhatsUp from disk='<location of .dat file>' WITH REPLACE, MOVE 'WhatsUp_Dat' to '<data file for SQL database>', Move 'WhatsUp_Log' to '<data file for SQL transaction log>'"

In our example, this becomes:

osql -E -S SQL_1 -Q "restore database WhatsUp from disk='D:\database.dat' WITH REPLACE, MOVE 'WhatsUp_Dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\WhatsUp.MDF', Move 'WhatsUp_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\WhatsUp.LDF'"

7.) Once you receive the response that the database was successfully restored, close the command prompt.

PART III

1.) On the WhatsUp machine, open the Data Sources (ODBC) control panel (Control Panel > Administrative Tools > Data Sources (ODBC)).
2.) Select the System DSN tab.
3.) Click Add..., select SQL Server and click Finish.
4.) Specify a name for the DSN. It cannot be WhatsUp or any other in-use user or system DSN name. We'll call ours WhatsUp2.
5.) Enter the IP Address or name of the SQL Server in the Server field. You can browse for it from the menu or enter it manually. Click Next.
6.) Select SQL Server authentication... and enter the credentials for the sa user on your SQL server. Before clicking Next, click Client Configuration....
7.) Select TCP/IP from the Network libraries list and click OK. Click Next.
8.) Check Change the default database to and select WhatsUp from the menu. Click Next.
9.) No changes need to be made on this screen, click Finish.
10.) Click Test Data Source... and verify that no errors are listed. Click OK, and then OK again.

PART IV

1.) Run nmconfig.exe. This application is located in your WhatsUp installation directory (usually C:\Program Files\Ipswitch\WhatsUp Professional 2005 or C:\Program Files\Ipswitch\WhatsUp Professional).
2.) Change the text in the Database connection string (DSN) field to match the new DSN created in Part 3. Ours will read DSN=WhatsUp2.
3.) Enter the Username and Password for the SQL sa user from Part 3, Step 6. Click OK.
4.) On the WhatsUp machine, open the Windows Registry Editor (regedit.exe), and browse to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Ipswitch WhatsUp Engine. Make a backup of this key.
5.) Delete the DependOnGroup and DependOnService values.
6.) Restart the WhatsUp machine.

Note: If you wish, you can now change the properties for the MSSQL$WHATSUP service so that it does not run automatically at boot. It is not recommended that you uninstall MSDE from the WhatsUp machine until you are satisfied with the SQL server. The MSDE is good for a backup but can be uninstalled after you are satisfied with the SQL server.

Document #: WP-20050303-DM03.htm
Revision Date: 11/14/07