Using an alternative database setup

We recommend using the default database, which is Microsoft SQL Server 2005 Express Edition (SSEE), and letting the installation program set up the database for you.

Important: If you want to migrate to SQL 2005, you must first install WhatsUp Gold and its SSEE instance and then migrate to the SQL 2005 Database Engine.

You can manually configure WhatsUp Gold to:

• Use Microsoft SQL Server 2005 instead of Microsoft SQL Server 2005 Express Edition (SSEE). SSEE is essentially a scaled down version of Microsoft SQL Server 2005. It can support up to 4 GB of data. If you need to use more space, you can purchase Microsoft SQL Server 2005 to use with WhatsUp Gold.
• Run the database on a computer separate from the one on which you have installed WhatsUp Gold. This may result in improved database performance by allowing separate resources to be used by both the database engine and the WhatsUp Gold application.

  Warning: While WhatsUp Gold supports the SQL Server 2000 Database Engine, you cannot migrate from the SSEE database engine to SQL Server 2000. The SQL Server database engines are not forward compatible and SQL Server 2000 cannot read the database data stored in a SQL Server 2005 database.

Either of these options requires manually configuring the database, so you need to have knowledge of how to manage and configure SQL Server 2005. There are several points to consider before deciding to move from a default database environment that uses SSEE to a non-default configuration:

• When upgrading WhatsUp Gold in the future, you will be required to manually upgrade your database schema. In the default database configuration, the upgrade wizard will complete this database upgrade automatically.
• For most WhatsUp Gold users, 4 GB of drive space is adequate to store network data for at least one year. If you find that you are running out of database space in the default WhatsUp database, you may need to adjust your data collection or retention settings. Migrating to SQL Server 2005 because of the database size may be unwarranted in many cases.
• Previous versions of WhatsUp Gold were limited by the Microsoft SQL Server 2000 Desktop Edition workload governor. When polling a large number of devices or monitors, this could lead to performance problems that could only be solved by a migration to a version of SQL that did not have the governor. SQL Server 2005 Express Edition now removes this governor and significantly increases the database performance thresholds.
• After migrating to a non-default SQL instance of the WhatsUp database, the database management requires manual maintenance on a routine basis. In most organizations, this is managed by a professional database administrator (DBA) or someone familiar with maintaining databases and optimizing them for best performance. If your organization does not have a DBA or someone with similar skills, it may be best to continue using the SQL Server 2005 Express Edition database that ships with WhatsUp Gold.

Supported databases

• Microsoft SQL Server 2005 Enterprise Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Express Edition

Database pre-requisites:

• Microsoft SQL Server 2005 English (with Service Pack 2) installed on the WhatsUp Gold host computer or another computer. If SQL Server is installed on another computer, remote connections to the SQL Server must be allowed. For more information, see How to configure SQL Server 2005 to allow remote connections.

  Important: WhatsUp Gold does not support non-English versions of SQL Server 2005.

• We recommend using an SQL account as the WhatsUp database user. However, if the SQL Server 2005 database exists on the same computer or both computers are members of the same domain, you may use Windows authentication. For more information about enabling mixed mode authentication in SQL Server 2005, see the Microsoft article about Authentication Mode.

  Note: When using Windows Authentication, the WhatsUp Gold services and applications must run as the database user.

• A database user with the following privileges:
  • Full administrator privileges to the WhatsUp database. WhatsUp Gold v12 only supports accessing the database via an account with full administrator rights to the database. We do not support a limited access account.
  • User's language is (United States) English. This can be set in the properties for the login used by WhatsUp Gold.

If you need assistance configuring or verifying these prerequisites, contact your database administrator or refer to the Microsoft SQL 2005 documentation.

Part I - Backing up the WhatsUp Database

1. From the WhatsUp computer, make a backup of the database. From the main menu in the WhatsUp Gold console, select Tools > Database Utilities > Back Up SQL Database. For this example, we will save our database as database.dat in the root of D:\ (D:\database.dat).
2. Close the WhatsUp Gold application; then, stop the Ipswitch WhatsUp Engine and the Ipswitch Web Server$WhatsUp, for more information see Stopping and starting the Ipswitch WhatsUp Engine and Stopping and starting the internal web server in the Help. If you are running IIS as your web server, stop IIS, then restart it.
3. If open, close the Ipswitch Task Tray application. Right-click on the icon and select Close Task Tray Application.
4. If the SQL server to which you will be migrating the database is on another computer, copy the .dat file, created in Step 1, to the SQL server. Otherwise, proceed to Part II.

Part II - Importing the WhatsUp Database into SQL 2005

1. Create a database in SQL 2005 which has the following properties:
   1. Database name is WhatsUp.
   2. Default collation is SQL_Latin1_General_CP1_CI_AS.
2. Find the location of the Data and Log file for this new database. This information is required later in the migration process.
   In SQL Server Management Studio, right-click the database, select Properties, then select the Files page. You can identify which file is the Data file by the value in the File Type column. The locations for the Data and Log files are under the Path column. In our example, the Data file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WhatsUp.mdf and the Log file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WhatsUp.ldf.
3. Close Management Studio and any other applications which may be accessing the WhatsUp database on the SQL server.
4. Open a command prompt on the SQL server and execute the following case-sensitive command to import the database into SQL.
   In our example, we have a default instance installed on a computer named SQL_1. If your SQL install is a named instance, rather than the default instance, specify your SQL server name as SQL_server_name\Instance_name.
   sqlcmd -E -S "<SQL_server_name>" -Q "RESTORE DATABASE [WhatsUp] FROM DISK='<location of .dat file>' WITH REPLACE, MOVE 'WhatsUp_dat' TO '<location of data file for SQL database>', MOVE 'WhatsUp_log' TO '<location of log file for SQL database>'"
   
   In our example, this becomes:
   sqlcmd -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.1\MSSQL\Data\WhatsUp.mdf', MOVE 'WhatsUp_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WhatsUp.ldf'"
5. After you receive the response that the database was successfully restored, close the command prompt.

Part III - Configuring the DSN

Important: Make sure that the instructions below are completed on the computer that WhatsUp Gold is installed on.

1. On the WhatsUp computer, 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, then click Finish.
4. Specify a name for the DSN. It cannot be WhatsUp or any other in-use user or system DSN name. In this example, we will call ours DSN WhatsUp2.
5. In the Server box, enter the IP Address or name of the SQL Server. You can browse for it from the menu or enter it manually. If you are using a named SQL instance, be sure to also enter the Instance Name. Click Next.
6. Click to select the SQL Server authentication... option, then enter the credentials for the SQL user on your SQL server. Click Next.
7. Click to select the Change the default database to option and select WhatsUp from the menu. Click Next.
8. Click Finish.
9. Click Test Data Source... and verify that no errors are listed. Click OK, then click OK again.

Part IV - Configuring WhatsUp to use the new database

1. Run the Connection String Configuration Utility. This application is located in your WhatsUp installation directory (usually C:\Program Files\Ipswitch\WhatsUp\NmConfig.exe).
2. Change the text in the Database connection string (DSN) field to match the new DSN created in Part III. In our example, it will read DSN=WhatsUp2.
3. Enter the Username and Password for your SQL user from Part III, Step 6. Click OK.
4. Click Yes to restart the Ipswitch services.

   Note: A dialog opens and tells you that you need to restart the WUG Engine. When you click Yes, not only are you restarting the WUG Engine, you are restarting the WUG Web Server. If you are running IIS as your web server, stop IIS, then restart it.

5. On the WhatsUp computer, 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.
6. Delete the DependOnGroup and DependOnService values.
7. Restart the WhatsUp computer.

   Note: If you prefer, you can now change the properties for the MSSQL$WHATSUP service so that it does not run automatically at boot. We do not recommended that you uninstall SQL Server 2005 Express Edition from the WhatsUp machine.