How to Move the BAM Primary Import Database

Article
01/05/2022

You can use this procedure to move the BAM Primary Import database to another server. From an end-to-end scenario perspective, moving the BAM Primary Import database involves two major steps:

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

Moving the BAM Primary Import Database

Perform the steps in the following procedure to move the BAM Primary Import database.

To move the BAM Primary Import database

Stop any BAM cube update and data maintenance SSIS packages, or prevent them from running until you have restored the BAM Primary Import database.
Stop all BizTalk Server services. For more information, see the topic How To Start, Stop, Pause, Resume, or Restart BizTalk Server Services (https://go.microsoft.com/fwlink/?LinkId=154394) in BizTalk Server Help.
Stop the IIS service.
Stop the BAM Alerts Notification service:
1. Click Start, click Run, type cmd, and then click OK.
2. At the command prompt, type:
  
  Net stop NS$BamAlerts
Back up the BAM Primary import database on the old server. For instructions on backing up a database, follow the instructions on how to back up a database at How to: Back Up a Database (SQL Server Management Studio) (https://go.microsoft.com/fwlink/?LinkId=156510) in SQL Server Books Online.
Copy the BAM Primary Import database to the new SQL Server computer.
Restore the BAM Primary import database on the new server. For instructions on restoring the database, follow the instructions on how to restore a database at How to: Restore a Database Backup (SQL Server Management Studio) (https://go.microsoft.com/fwlink/?LinkId=156511) in SQL Server Books Online.

Note

If you restore the BAM Primary Import database from a backup, then you should also restore the BAM Archive, BAM Star Schema, and BAM Analysis databases by using a backup older than the BAM Primary backup.

Updating References to the New BAM Primary Import Database

After you have moved the database, you must update all the references to the new BAM Primary Import Database. The following references must be updated:

Update all the BizTalk databases with the new server name. You can do so by using the UpdateDatabase.vbs script. See To update BizTalk Databases with the new server name.
Update the Web.config file for the BAM portal. See To update the Web.config file for the BAM portal.
Update the reference to the BAM Primary Import Database in all BAM Livedata Microsoft Excel files. See To update reference in BAM Livedata Microsoft Excel files.
Update the new server and database names in all BAM analysis SSIS packages. See To update server and database names in all BAM SSIS packages.
Update the new server and database names in data sources for all OLAP cubes. See To update server and database names in data sources for all OLAP cubes.

To update BizTalk Databases with the new server name

On a computer running BizTalk Server, browse to the following folder:
- If BizTalk Server is installed on a 64-bit version of Windows Server:
  
  %ProgramFiles(x86)%\Microsoft BizTalk Server 2010\bins32\Schema\Restore
- If BizTalk Server is installed on a 32-bit version of Windows Server:
  
  %ProgramFiles%\Microsoft BizTalk Server 2010\Schema\Restore
Right-click SampleUpdateInfo.xml, and then click Edit.
Comment out all of the database sections except for the BizTalkMgmtDb, OldPrimaryImportDatabase, PrimaryImportDatabase, ArchivingDatabase, AnalysisDatabase, StarSchemaDatabase, and Alert.
In the OldPrimaryImportDatabase section of the file, for the ServerName property, replace SourceServer with the name of existing server where the database resides.
In the PrimaryImportDatabase section of the file, for the ServerName property, replace DestinationServer with the name of the server where you have moved the BAM Primary Import database
For the BizTalkMgmtDb, ArchivingDatabase, AnalysisDatabase, StarSchemaDatabase, and Alert sections, set the "SourceServer" and "Destination Server" to the name of the existing server where those databases reside.

Important

Include the quotation marks around the name of the source and destination systems.

Note

If you renamed any of the BizTalk Server databases, you must also update the database names as appropriate.
When you are finished editing the file, save it and exit.
Click Start, click Run, type cmd, and then click OK.
At the command prompt, navigate to the following directory:
- If BizTalk Server is installed on a 64-bit version of Windows Server:
  
  %ProgramFiles(x86)%\Microsoft BizTalk Server 2010\Schema\Restore
- If BizTalk Server is installed on a 32-bit version of Windows Server:
  
  %ProgramFiles%\Microsoft BizTalk Server 2010\Schema\Restore
At the command prompt, type:

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

To update the Web.config file for the BAM portal

On a computer running BizTalk Server, update the Web.config files under <drive>:\Program Files\Microsoft BizTalk Server 2010\BAMPortal\BAMManagementService\Web.Config. Update the server and database names in the following section in the Web.config:
```
<appSettings>
  <add key="BamServer" value="<ServerName>" />
  <add key="BamDatabase" value="<DatabaseName>" />
</appSettings>
```
On a computer running BizTalk Server, update the Web.config files under <drive>:\Program Files\Microsoft BizTalk Server 2010\BAMPortal\BAMQueryService\Web.Config. Update the server and database names in the following section in the Web.config:
```
<appSettings>
  <add key="BamServer" value="<ServerName>" />
  <add key="BamDatabase" value="<DatabaseName>" />
  <add key="MaxResultRows" value="2000" />
</appSettings>
```
Save and close the files.

To update reference in BAM Livedata Microsoft Excel files

Open the Excel live data file. The file name ends with _LiveData.xls.
On the BAM menu, click BAM DB Connection.
In the Select BAM Database dialog box, enter the SQL Server computer and the BAMPrimaryImport database, and then click OK.
On the File menu, click Close and Return to Microsoft Excel.
On the File menu, click Save.

To update server and database names in all BAM SSIS packages

Update the server and database names in all BAM analysis SSIS packages, which are prefixed with "BAM_AN_" or "BAM_DM_". To do so, click Start, click All Programs, click Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008 SP1, and then click SQL Server Business Intelligence Development Studio.
In SQL Server Business Intelligence Development Studio, create a new project. Click File, click New, and then click Project.
In the New Project dialog box, in the Project Types box, click Business Intelligence Projects. In the right pane, in the Templates box, click Integration Services Project, and then click OK.
In the Integration Services Project dialog box, in Solution Explorer, right-click SSIS Packages, and then click Add Existing Package.
In the Add Copy of Existing Package dialog box, in the Server drop-down list box, select the server that contains the BAM_AN_* and BAM_DM_* packages.
In Package Path, click the ellipses button.
In the SSIS Package dialog box, select the package you want to update, click OK, and then click OK.

The package is now listed in Solution Explorer.
In Solution Explorer, double-click the package you added in the previous step. In Connection Managers tab (available towards the lower half of the screen), double-click data source number 1 (BAMPrimaryImport database).
In the Connection Manager dialog box, in the Server name box, enter the name of the server, and then click OK.
Click the Package Explorer tab, double-click the Variables folder, and then update the values for the PrimaryImportDatabase and PrimaryImportServer variables. You must update the values to point to the new server and database.

Note

Repeat step 4 through 10 for all the packages that you want to update.
Click then File menu, and then click Save All.
Start the SQL Server Management Studio. Click Start, click All Programs, click Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008 SP1, and then click SQL Server Management Studio.
In the Connect to Server dialog box, from the Server type drop-down list, select Integration Services.
Specify the server name and credentials to connect to the server and click OK.
In the Object Explorer, expand Integration Services, expand Stored Packages, and then click MSDB.
In the Object Explorer Details tab, right-click the package that you updated earlier and then click Import Package.
In the Import Package dialog box, from the Package location drop-down list, select File System.
In Package Path, navigate to your saved project, select the .dtsx file for the package you want to import, and then click Open.
Click inside the Package Name box to automatically populate the box.

Note

Repeat step 16 through 19 for all the packages that you want to update.
Click OK, and then click Yes to overwrite.
Enable any BAM cube update and data maintenance SSIS packages.

To update server and database names in data sources for all OLAP cubes

Update the server and database names in data sources for all OLAP cubes. To do so, click Start, click All Programs, click Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008 SP1, and then click SQL Server Management Studio.
In the Connect to Server dialog box, for the Server type drop-down list select Analysis Services, provide the server name, select an authentication method (and provide credentials if required), and then click Connect.
In the Object Explorer, expand Databases, expand BAMAnalysis, expand Data Sources, and then double-click a data source.
In the Data Source Properties dialog box, click the ellipsis button (…) against the Connection String property.
In the Connection Manager dialog box, in the Server name box, enter the name of the server hosting the BAMPrimaryImport database, click OK, and then click OK.
Start all BizTalk Server services. For more information, see the topic How To Start, Stop, Pause, Resume, or Restart BizTalk Server Services (https://go.microsoft.com/fwlink/?LinkId=154394) in BizTalk Server Help.
Start the IIS service.
Start the BAM Alerts Notification service:
1. Click Start, click Run, type cmd, and then click OK.
2. At the command prompt, type:
  
  Net start NS$BamAlerts
Resolve any incomplete trace instances. For information about resolving incomplete BAM activity instances, see How to Resolve Incomplete Activity Instances (https://go.microsoft.com/fwlink/?LinkId=151475).