Altering and Restoring Biztalk Databases | Master Secret , Restore , BiztalkMgmt

Q:

We restored the BizTalk production databases of BizTalk 2006 R2 to a new (for various reasons) test environment. We made changes in BizTakMgmt  tables and BAMPrimaryImport tables, more or less followed the http://msdn.microsoft.com/en-us/library/aa561125(v=BTS.20).aspx and http://msdn.microsoft.com/en-us/library/ee267635(v=BTS.10).aspx

But got into following two issues
1. It is showing in the event viewer that this test computer is receiving secret from production server's SSO, rather than test compter.
There is ESSO service running on the test computer. Both production server and test server environments use the same domain id.
How do   I make it to point to test ESSO it self ?

Event viewer entries
 Got the previous secret from the master secret server.
 Secret Server Name: ProdBizTakServer
 MSID: 8f26122c-4830-4216-bba5-e49dca3ba13b

 Got the current secret from the master secret server.
 Secret Server Name: ProdBizTakServer
 MSID: 8f26122c-4830-4216-bba5-e49dca3ba13b

2. HAT's Preferences,Tools, Live Data radio is pointing to test databases, but still query is running against producion database and hence production services/names are shows in the result.
How do I make it to get the result from test environment ?

Thanks 

Sol:

Generate the master secret from production.

Restore it on test

Update the Secret server name as mentioned below:

File: NewSecretServer.xml
<sso>
  <globalInfo>
    <secretServer>TestSecretServer</secretServer>
  </globalInfo>
</sso>

Run command
<drive>:\Program Files\Common Files\Enterprise Single Sign-On\ssomanage -updatedb NewSecretServer.xml

you will have to follow the steps mentioned in this link http://msdn.microsoft.com/en-us/library/ee267632%28v=BTS.10%29.aspx

To update references to the BizTalk Tracking database name

1. Using SQL Enterprise Manager, open the BizTalk Configuration database.
2. In the adm_Group table, modify the columns corresponding to the original databases to reference the appropriate values for the new databases.

<DBType>DBServerName and <DBType>DBName indicate the locations of the databases, where <DBType> corresponds to the type of the database: Tracking.

1. If you are using Human Workflow Services, in the HWS Administration database, modify the HWS_Core table to reference the new locations:
   • TrackingDatabaseMachineName = BizTalk Tracking database server
   • TrackingDatabaseName = BizTalk Tracking database name
2. In the BizTalk Configuration database, update the BAM Event Bus service to point to the new Tracking database:
   • Using SQL Enterprise Manager, open the BizTalk Configuration database.
   • In the TDDS_Destinations table, modify the ConnectionString column for the rows corresponding to the original database to reference the appropriate values for the new Tracking database.