How to move an HP Systems Insight Manager SQL 2005 SP3 database from local to remote server White Paper Subtitle Table of Contents Introduction......................................................................................................................................... 2 Enabling Remote Connection to SQL Server 2005 SP3............................................................................. 2 Enabling SQL Server and Windows Authentication mode ...............................................
Introduction This white paper describes how to move an HP Systems Insight Manager SQL Server 2005 SP3 database from a local server to a remote server. Enabling Remote Connection to SQL Server 2005 SP3 Enabling SQL Server and Windows Authentication mode 1. Log in to SQL Server using SQL Server Management Studio on local SQL Server with Windows ® Authentication user. The Windows account authenticates SQL Server. Figure 1: Connecting to server 2.
Figure 2: Object Explorer 3. On the left side, select Security, and change Server authentication to SQL Server and Windows Authentication mode. If this setting is already selected, then SQL Server authentication is enabled.
Figure 3: Server Properties 4. Restart the SQL Server so that the changes can take effect. Figure 4: Information message 5. Right click the instance name again, and select Restart.
Figure 5: Object Explorer 6. Select Yes. Figure 6: Information dialog 7. Wait for service to stop and start.
Figure 7: Attempting to stop service 8. Both SQL Server and Windows authentication have been enabled. You can now log in to SQL Server with your current Windows account or your created account. Enabling remote connection on SQL Server Surface Area Configuration 1. Open SQL Server Surface Area Configuration. Figure 8: SQL Server Surface Area Configuration 2. Select Surface Area Configuration for Services and Connections.
Figure 9: SQL Server 2005 Surface Area Configuration 3. On the left side, expand your SQL Server instance by selecting Database Engine and then Remote Connections. On the right side select, Local and remote connections and then using both TCP/IP and named pipes. Click Apply.
Figure 10: SQL Server 2005 Surface Area Configuration Step 1 4. On the left side, select SQL Server Browser, and then select Service. On the right side, if the startup type is Disabled, you must change it to Automatic. Click OK.
Figure 11: SQL Server 2005 Surface Area Configuration Step 2 5. The configuration is complete. Log in to your SQL Server from a remote computer using SQL Server authentication mode.
Figure 12: Connect to server 6. Log in to SQL Server succeeded. Figure 13: Microsoft SQL Server Management Studio Moving the database from a local to a remote server 1. Stop and disable the Systems Insight Manager service running on the local server.
2. Open SQL Server Management Studio on the local server: ProgramsMicrosoft SQL server 2005SQL Server Management Studio Figure 14:. Connecting to SQL Server 3. With default settings in the Connect to Server window, click Connect. 4. Record the database name, for example, Insight_v50_xxxxxxxxx. 5. Create a new SQL server 2005 Database on the target database server. a.Open SQL Server Management Studio on the target database server: ProgramsMicrosoft SQL server 2005SQL Server Management Studio b.
Figure 15: Creating a database c. Enter the name of the database recorded in step 4, and then click OK with default settings selected. 6. On the local database server, right-click the name of the Insight database, for example, Insight_v50_xxxxxxxxx. 7. Select TasksExport data.
Figure 16:. Export Data 8. In the Welcome to SQL Server Import and Export Wizard window, click Next.
Figure 17: Import and Export Wizard 9. In the Choose a Data Source window, use the default settings Windows Authentication, or choose the authentication method you used for your database. Click Next.
Figure 18: Choose a Data Source 10. In the Choose a Destination window, select the target database server from the list. Select the database created in Step 5 from the Database list at the bottom of the window.
Figure 19: Choose a Destination 11. In the Specify Table Copy or Query dialog window, select the Copy data from one or more tables or views, and then click Next.
Figure 20: Specify Table Copy or Query 12. In the Select Source Tables and Views window, select the All box, and then click Next. A window appears.
Figure 21: Select Source Tables and Views 13. Click Yes. Figure 22: Pop-up Window 14. In the Save and Execute Package window, keep the default setting (Execute immediately), and then click Next.
Figure 23: Save and Execute Package 15. In the Complete the Wizard window, click Finish.
Figure 24: Complete the Wizard 16. After the tasks have completed, stop and disable the SQL Service. Data is successfully migrated from source database server to target database server.
Figure 25: Performing Operation Systems Insight Manager Installation Directory 1. Create a backup of the database.props, database.admin, databaseadminupd.tpl and databaseupdate.tpl files located in \HP\Systems Insight Manager\config. 2. You must modify some settings for the new database changes in Systems Insight Manager in each of the following property files: database.props, database.admin, databaseadminupd.tpl and databaseupdate.tpl files.
d. If moving to a different SQL server, it may use a different port, if this is the case, update the value after hp.Database.portNumber= to the new port number. 3. Create a backup of the hpsim-ds.xml file located in the \HP\Systems Insight Manager\jboss\server\hpsim\deploy directory, do not place the backup file in this directory. 4. Modify the hpsim-ds.xml file located in the \HP\Systems Insight Manager\jboss\server\hpsim\deploy directory. a.
For more information For more information on Systems Insight Manager, visit the HP website (http://www.hp.com/go/hpsim. Call to action Send comments about this white paper to TechCom@HP.com. Technology for better business outcomes © Copyright 2010 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services.
