How do I move the VigilEnt Policy Center SQL database from one machine to another? (NETIQKB35130)

goal
How do I move the VigilEnt Policy Center SQL database from one machine to another?

To move your VigilEnt Policy Center (VPC) database from one machine to another, please follow the steps below:

1. Important! Backup up the VPC database (default name, NetIQVPC)

2. Install VPC on the new SQL Server box.  The VPC application must be the same version as the VPC database.  If you are currently running VPC 5.5: 

   1. VPC 5.5 comes packaged in an installation kit. Run the executable (by double clicking the file) to extract the installation to the default directory (C:\Program Files\VPCSetup).

   2. You will see a tab labeled Install now, select this to install VPC. 

   3. Alternatively, you may go to the subdirectory (C:\Program Files\VPCSetup) and run the installVPC.exe (by double clicking the file).

3. An install wizard will come up.  Follow the defaults on the installation wizard pages until you get to the page asking for the database you want. Choose Microsoft SQL instance. The next page will show the name of the SQL database that will be created, by default it will be named NetIQVPC.

   Note: You must make this database name the same as the populated database you will replace it with.

4. Once you have selected Microsoft SQL instance, you will then be prompted to enter the parameters of the SQL host and the port.

   1. Enter either the qualified name of the SQL server (if it is in DNS) or enter the IP address of the server.

   2. Enter the port number of the SQL server.  The default is 1433.

   3. Enter a SQL Server administrator?s username and password for accessing the SQL Server.  The default (administrator) username is SA.  This administrator account is used to connect and create the database on the SQL Server.  If your DBA cannot give you the SA password, you may need to have him/her type it in.  Alternatively, have your DBA create a user account that has the privileges to create databases on this SQL server. Then replace the default SA (userid) with this new userid.  Type in the password for the account and select the Next button.

5. When you are prompted for the VPC database user, use the default of vpc_user or type in a new username and password.  VPC will use this account for all transactions. Finish setup.

   Note: You must make this name the same as the VPC database user you have on your current SQL Server.

6. On the VPC server, find the db.properties file in the default directory, .\Server\shared\classes and rename it.  On the SQL server, find the db.properties file in the default directory .\Server\shared\classes and copy it to the install directory on the VPC server (replacing the file you just renamed).

7. On the VPC server stop and restart the VPC service.  (You may stop these by going to Start | Programs | Administrative Tools | Services.  Locate the service, right click, and choose Stop. Then once the service has been stopped, click Start.)

8. On the new SQL server, go to the Add or Remove Programs and uninstall VPC. Choose Automatic from the uninstall wizard.  When asked, Do you want to perform a rollback, choose No.

   Note: The uninstall does not remove the database. The uninstall is being run so that you do not inadvertently have two installations of VPC pointing to the same database.

   You should now have your VPC service pointing to the new SQL Server (and the newly installed database).

9. As a test, open a browser to the VPC admin site (the default path is HTTP://localhost:8080/VpcAdmin).  If the VPC admin site opens and you ca.
   n log on, all is well.  Go to the Policy Center tab, View/Edit Policy page. There should be no policies under Draft, Review, Published or Archived, since VPC is now connected to the new database on the new SQL Server.

   If all is well from step 9, then it is time to change the databases.

10. Stop the two VPC services, VigilEnt Policy Center service and VigilEnt Policy Center Agent service.  You may stop these by going to Start | Programs | Administrative Tools | Services.  Locate the service, right click, and choose Stop.

11. Go to the SQL Enterprise Manager on the new SQL Server, open to the VPC database, and detach it. Now, on your old SQL Server, copy your mdf and ldf files for the populated VPC database (located in the directory, .\Microsoft SQL Server\MSSQL\Data) to the new SQL Server.  Just overwrite the mdf and ldf files that are there.

12. Go to the SQL Enterprise Manager on the new SQL Server, open and attach the VPC database.  You should now be attaching the populated VPC database.

13. If there are problems with vpc_user and ownership of the tables, between the old VPC database (unpopulated one that has been overwritten) and the new VPC database (populated one) then, using your SQL Enterprise Manager:

    1. Highlight the PentaSafeVPC database (or whatever you may have named the VPC database).

    2. Go to the Tools menu and select SQL Query Analyzer. Once it opens up, paste both of these stored procedures (one at a time) into the blank window labeled Query:
       
    • sp_change_users_login @Action='Auto_Fix', @Usernamepattern='vpc_user'
    • sp_changedbowner @loginame = 'vpc_user', @map = 'false'


    3. Then, go to the menu bar and click Query | Execute. This will run the query and return some orphaned messages.

14. Restart the VPC services (You may stop these by going to Start | Programs | Administrative Tools | Services.  Locate the service, right click, and choose Start) and try logging on to the VPC admin site again.  You should now be connected to the populated database with all the data you are expecting.

.

Formerly known as NETIQKB35130