Migrating (MSDE one server) to SQL 2000 (different server)

[Bill_241]

Hi,

I have MSDE running on my CMS server. I want to stop MSDE from running on my CMS server and move my IM and PMP databases to a larger, more robust SQL 2000 server on our SQL Farm. How is this done, simply by creating new DSNs?

Thank you,
Bill

[Haridas · Accepted solution]

You can remotely administer MSDE by pointing your SQL Server box to the MSDE box and uploading the MSDE data to the SQL Server machine. Finally, you can detach the MSDE data file and reattach it on a SQL Server machine.

Visit the below URL.

http://support.microsoft.com/default.aspx?scid=kb;en-us;325023

Cheers!
Hari

[Bill_241]

Hari,

Thank you. These links simply show the mechanics of moving from MSDE to a new server with SQL. This is no problem at all. The issue lies with pointing the DSNs over to the new server. Originally when I set up this server (with SIM 4.1 and MSDE on the same server) I was told that the user I was logging in with (to do the install) would be the one that would have access to the database. Now that I would like to move the database to another server how do I set my DSNs, account that access the new database, etc.? Is there anything else...?

Bill

[Haridas]

Bill,

Sorry for the delay in responding to your query!
------------------------------------------
To move the data from the MSDE system to the SQL Server system do the following

Start SQL Enterprise manager on the SQL Server system.

Connect to the MSDE system.

Select the database that we want to move and right click on the database name and select Export Data from the All Tasks.

Ensure that the right database server name, authentication and database name are on the Data source page and click next.

On the destination page, select the local server as the destination server and select for the database name. This will bring up another box to create a new database. Ensure that you give the same name for the new database and click OK to create the database.

Click Next.

Select the radio button that says â Copy objects and data between SQL Server databasesâ and click next.

Leave all the settings default except check the check box that says â Include extended propertiesâ and click next.

Click next and finish and when it is all done you will have copied the database to the SQL Server.

Here are the steps to connect HP SIM to the new database. Please note that only Windows authentication works. The user on HP SIM system should be able to connect to the SQL Server system using the same user information.

Change the ODBC DSN.

On the HP SIM system open the ODBC Data Source Administrator. This is under the menu â Administrative Tools -> Data Sources (ODBC)â

Click on the tab â System DSNâ

Select the name that says Insight_v1_0 and click â Configureâ

Change the server name to the name of the SQL Server.

Click next, next and finish.

Click the button that says â Test Data Sourceâ and verify that the test completes successfully.

Click OK and OK again to get back to the System DSN page.

The insight_v1_master DSN is only used during installation. This can be left unmodified.

Repeat steps c to f for PMP_V3_0 DSN.

Change the database.props file

Edit the file called database.props under the config folder under the HP SIM installation path. (The default location for this file is C:\Program Files\HP\Systems Insight Manager\config folder).

Change the value for the entry that says hp.Database.hostName from 127.0.0.1 to the SQL Server name (or IP address).

Save and close the file.

You should be able to start HP SIM and PMP and the remote database on the SQ