Pages

BizTalk : How To : Backup BAM Databases and Update References to BAM Databases

Saturday, April 13, 2013
The following tables describe the databases used by BizTalk Server and identify which methods are used to back up the databases.

Databases Backed Up by the Backup BizTalk Server Job

The following table lists the databases that are backed up and restored as a part of the Backup BizTalk Server job. You can modify the Backup BizTalk Server job to back up custom databases by adding them to the adm_OtherBackupDatabases table.

Database
Default database name
Description
BAM Primary Import database
BAMPrimaryImport
This is the database where the Business Activity Monitoring (BAM) collects raw tracking data.
BAM Notification Services Application database
BAMAlertsApplication
This database contains alert information for BAM notifications. For example, when you create an alert using the BAM portal, entries are inserted in the database specifying the conditions and events to which the alert pertains, as well as other supporting data items for the alert.
BAM Notification Services Instance database
BAMAlertsNSMain
This database contains instance information specifying how the notification services connect to the system that BAM is monitoring.
HWS Administration database
BizTalkHwsDb
This database contains all administration information related to Human Workflow Services (HWS).
BizTalk Tracking database
BizTalkDTADb
This database stores health monitoring data tracked by the BizTalk Server tracking engine.
BizTalk Management database
BizTalkMgmtDb
This database is the central meta-information store for all instances of BizTalk Server.
BizTalk MessageBox database
BizTalkMsgBoxDb
This database is used by the BizTalk Server engine for routing, queuing, instance management, and a variety of other tasks.
Rule Engine database
BizTalkRuleEngineDb
This database is a repository for:
·         Policies, which are sets of related rules.
·         Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.
SSO database
SSODB
This Enterprise Single Sign-On database securely stores the configuration information for receive locations.
TPM database
TPM
This database stores trading partner data for Business Activity Services (BAS). By default, TPM database is combined with BizTalk Management (BizTalkMgmtDb) database.
BizTalk Base EDI database
BizTalkEDIdb
This database stores state for the Base electronic data interchange (EDI) adapter, which has been deprecated in BizTalk Server 2006 R2. The Base EDI adapter can be used in upgrade scenarios, but for new installations of BizTalk Server 2006 R2, use the native EDI and AS2 functionality.

Databases Backed Up by the BAS Backup Process

The following table lists the Microsoft Windows SharePoint Services databases that are backed up and restored using the procedures in Backing Up and Restoring BAS:

Database
Default database name
Description
Windows SharePoint Services configuration database
User-defined
This database contains all of the global settings for the server.
Windows SharePoint Services content database
User-defined
This database contains all of the site content, such as list items and documents.

Databases Backed Up by the BAM Backup Process

The following table lists the databases that are backed up and restored using the procedures in Backing Up and Restoring BAM:

Database
Default database name
Description
BAM Star Schema
BAMStarSchema
This database contains the staging table, and the measure and dimension tables.
BAM Analysis
BAMAnalysis
This database contains BAM OLAP cubes for both online and offline analysis.
BAM Archive
BAMArchive
This database archives old business activity data. Create a BAM Archive database to minimize the accumulation of business activity data in the BAM Primary Import database.
Tracking Analysis Server
BizTalkAnalysisDb
This database stores health monitoring online analytical processing (OLAP) cubes.

How to Back Up the BAM Analysis and Tracking Analysis Server Databases

The Business Activity Monitoring (BAM) Analysis database and the Tracking Analysis Server database store content in SQL Server Analysis Services cubes. The Backup BizTalk Server job does not back up these databases. Instead, to backup these databases, you must use SQL Server Analysis Manager.
After you back up these databases, you may want to purge the OLAP cubes. When you purge the OLAP cubes, you must also perform the following steps:
1.     Before you purge the OLAP cubes, in the BAM Star Schema database, truncate the fact table(s) for the cube you want to purge. The table naming convention is "bam_<CubeName>_Facts".
2.     After you purge the OLAP cubes, you must fully process active, completed, and virtual cubes.
For instructions about backing up the analysis databases, see "Archiving an Analysis Services Database" in SQL Server Books Online.

Scheduling backups for the BAM databases

If you are using BAM, verify that neither the BAM cube process nor data maintenance Data Transformation Services (DTS) packages are running when the backup package is scheduled to run.
To ensure consistent schema across all BAM databases, back up the BAM databases and DTS packages each time you deploy or undeploy a BAM activity.
Back up the BAM Analysis database and BAM Star Schema database each time you deploy or undeploy a BAM view.
Back up the BAM databases in the following order:
1.     Run the Backup BizTalk Server job to back up the BAM Primary Import database and your other BizTalk Server databases.
2.     Run the BAM data maintenance DTS package for all activities.
Incorporate these steps into a DTS package, and schedule the package to run on a regular basis. To ensure data integrity, make sure no other BAM cubing or data maintenance DTS packages run when this backup package is scheduled to run.
To ensure that you can recover a complete set of archived data if the BAM Archive database fails, back up the BAM Archive database after you copy the partition into the BAM Archive database, but before you delete the partition from the BAM Primary Import database. To do this, modify the data maintenance DTS package for each activity to insert a step to back up the BAM Archive database before the last step in the DTS package, "End Archiving."
3.     Back up the BAM Analysis database, and then the BAM Star Schema database.

How to Update References to the BAM Analysis Server Database Name

1.     Stop any BAM cube update and data maintenance SSIS packages, or prevent them from running until you have restored the BAM Analysis database.
2.     Stop the BizTalk Application service (which includes the BAM Event Bus service) so it does not try to import more data into the database.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Stop.
3.     Click Start, click Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.
4.     In SQL Server Business Intelligence Development Studio, create a new project. Click File, click New, and then click Project.
5.     In the New Project dialog box, in Templates, click Integration Services Project, and then click OK.
6.     In the Integration Services Project dialog box, in Solution Explorer, right-click SSIS Packages, and then click Add Existing Package.
7.     In the Add Copy of Existing Package dialog box, in the Server drop-down list box, select the server that contains the BAM_AN package.
8.     In Package Path, click the ellipses button.
9.     In the SSIS Package dialog box, select the BAM_AN package, click OK, and then click OK.
The package is now listed in Solution Explorer.
10.  In Solution Explorer, double-click the BAM_AN package. In Connection Managers, double-click database number 3 (MSDB database).
11.  In the Connection Manager dialog box, in the Server name box, enter the name of the MSDB server, and then click OK.
12.  Click the Package Explorer tab, double-click the Variables folder, and then update the values for the primary import server name and primary import database name.
13.  Click File, and then click Save All.
14.  In Microsoft SQL Server Management Studio, click Connect.
15.  Click Integration Services, double-click Stored Packages, click MSDB, right-click the BAM_AN package, and then click Import Package.
16.  In the Import Package dialog box, in Package location, select File System.
17.  In Package Path, navigate to your saved project, select the BAM_AN*.dtsx file, and then click Open.
18.  Click inside the Package Name box to automatically populate the box.
19.  Click OK, and then click Yes to overwrite.
20.  Restart the BizTalk Application service.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Start.
21.  Enable any BAM cube update and data maintenance SSIS packages.

How to Update References to the BAM Star Schema Database Name

1.     Stop any BAM cube update and data maintenance SSIS packages, or prevent them from running until you have restored the BAM Star Schema database.
2.     Stop the BizTalk Application service (which includes the BAM Event Bus service) so it does not try to import more data into the database.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Stop.
3.     Click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.
4.     In SQL Server Business Intelligence Development Studio, create a new project. Click File, click New, and then click Project.
5.     In the New Project dialog box, in Templates, click Integration Services Project, and then click OK.
6.     In the Integration Services Project dialog box, in Solution Explorer, right-click SSIS Packages, and then click Add Existing Package.
7.     In the Add Copy of Existing Package dialog box, in the Server drop-down list box, select the server that contains the BAM_AN package.
8.     In Package Path, click the ellipses button.
9.     In the SSIS Package dialog box, select the BAM_AN package, click OK, and then click OK.
The package is now listed in Solution Explorer.
10.  In Solution Explorer, double-click the BAM_AN package. In Connection Managers, double-click database number 3 (MSDB database).
11.  In the Connection Manager dialog box, in the Server name box, enter the name of the MSDB server, and then click OK.
12.  Click the Package Explorer tab, double-click the Variables folder, and then update the values for the primary import server name and primary import database name.
13.  Click File, and then click Save All.
14.  In Microsoft SQL Server Management Studio, click Connect.
15.  Click Integration Services, double-click Stored Packages, click MSDB, right-click the BAM_AN package, and then click Import Package.
16.  In the Import Package dialog box, in Package location, select File System.
17.  In Package Path, navigate to your saved project, select the BAM_AN*.dtsx file, and then click Open.
18.  Click inside the Package Name box to automatically populate the box.
19.  Click OK, and then click Yes to overwrite.
20.  Restart the BizTalk Application service.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Start.
21.  Enable any BAM cube update and data maintenance SSIS packages.

How to Update References to the BAM Archive Database Name

1.     Stop any BAM cube update and data maintenance DTS packages, or prevent them from running until you have restored the BAM Archive database.
2.     Stop the BizTalk Application service (which includes the BAM Event Bus service) so it does not try to import more data into the database.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Stop.
3.     Click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.
4.     In SQL Server Business Intelligence Development Studio, create a new project. Click File, click New, and then click Project.
5.     In the New Project dialog box, in Templates, click Integration Services Project, and then click OK.
6.     In the Integration Services Project dialog box, in Solution Explorer, right-click SSIS Packages, and then click Add Existing Package.
7.     In the Add Copy of Existing Package dialog box, in the Server drop-down list box, select the server that contains the BAM_DM package.
8.     In Package Path, click the ellipses button.
9.     In the SSIS Package dialog box, select the BAM_DM package, click OK, and then click OK.
The package is now listed in Solution Explorer.
10.  In Solution Explorer, double-click the BAM_DM package. In Connection Managers, double-click database number 3 (MSDB database).
11.  In the Connection Manager dialog box, in the Server name box, enter the name of the MSDB server, and then click OK.
12.  Click the Package Explorer tab, double-click the Variables folder, and then update the values for the primary import server name and primary import database name.
13.  Click File, and then click Save All.
14.  In Microsoft SQL Server Management Studio, click Connect.
15.  Click Integration Services, double-click Stored Packages, click MSDB, right-click the BAM_DM package, and then click Import Package.
16.  In the Import Package dialog box, in Package location, select File System.
17.  In Package Path, navigate to your saved project, select the BAM_DM*.dtsx file, and then click Open.
18.  Click inside the Package Name box to automatically populate the box.
19.  Click OK, and then click Yes to overwrite.
20.  Restart the BizTalk Application service.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Start.
21.  Enable any BAM cube update and data maintenance SSIS packages.

How to Update References to the BAM Primary Import Database Name and Connection String

1.     Stop any BAM cube update and data maintenance Data Transformation Services (DTS) packages, or prevent them from running until you have restored the BAM Primary Import database.
2.     Stop the BizTalk Application service (which includes the BAM Event Bus service) so it does not try to import more data into the database.
1.     Click Start, click Run, and then type services.msc.
2.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Stop.
3.     Restore the BAM Primary Import database, performing the steps in How to Restore Your Databases.
4.     Update the following Web.Config files:
·         C:\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BamManagementService\Web.Config.
Replace the <ServerName> string with the new server name and <DatabaseName> with the new database name. Update the following connection strings:
<appSettings>
<add key="BamServer" value="<ServerName>" />
<add key="BamDatabase" value="<DatabaseName>" />
<add key="MaxResultRows" value="2000" />
</appSettings>
·         C:\Program Files\Microsoft BizTalk Server 2006\BAMPortal\BamQueryService\Web.Config.
Replace the <ServerName> string with the new server name and <DatabaseName> with the new database name. Update the following connection strings:
<appSettings>
<add key="BamServer" value="<ServerName>" />
<add key="BamDatabase" value="<DatabaseName>" />
<add key="MaxResultRows" value="2000" />
</appSettings>

5.     Click Start, click Run, type cmd and then click OK.
6.     Navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.
7.     Right-click SampleUpdateInfo.xml, and then click Edit.
0.     Comment out all of the database sections except for the BizTalkMgmtDb, OldPrimaryImportDatabase, PrimaryImportDatabase, ArchivingDatabase, AnalysisDatabase, StarSchemaDatabase, and Alert.
1.     For the BizTalkMgmtDb, OldPrimaryImportDatabase, PrimaryImportDatabase, ArchivingDatabase, AnalysisDatabase, StarSchemaDatabase, and Alert sections, set the "SourceServer" and "Destination Server" to the name of the existing server where those databases reside.
2.     For PrimaryImportDatabase, set the "SourceServer" to the name of the server where you have moved the BAM Primary Import database.

Description: http://i.msdn.microsoft.com/Aa561586.Important(en-us,MSDN.10).gifImportant
Include the quotation marks around the name of the source and destination systems.
Description: http://i.msdn.microsoft.com/Aa561586.note(en-us,MSDN.10).gifNote
If you renamed any of the BizTalk Server databases, you must also update the database names as appropriate.

3.     When you are finished editing the file, save it and exit.
8.     At the command prompt, type:
cscript UpdateDatabase.vbs SampleUpdateInfo.xml

Description: http://i.msdn.microsoft.com/Aa561586.note(en-us,MSDN.10).gifNote
You only need to run UpdateDatabase.vbs once.
Description: http://i.msdn.microsoft.com/Aa561586.note(en-us,MSDN.10).gifNote
On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt.
9.     At the command prompt, navigate to the following directory:
%SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Tracking
10.  At the command prompt, edit bm.exe.config, change the value of key="DefaultServer" to the new server name, and then save the file.
11.  Update the reference to BAM Primary Import Database in all BAM Livedata Microsoft Excel files. For each file:
0.     Open the Excel live data file. The file name ends with _LiveData.xls.
1.     On the BAM menu, click BAM DB Connection.
2.     In the Select BAM Database dialog box, enter the SQL Server and BAMPrimaryImport database, and then click OK.
3.     On the File menu, click Close and Return to Microsoft Excel.
4.     On the File menu, click Save.
12.  Restart the BizTalk Application service.
0.     Click Start, click Run, and then type services.msc.
1.     Right-click the BizTalk Service BizTalk Group: BizTalkServerApplication service and then click Start.
13.  Enable any BAM cube update and data maintenance DTS packages.
14.  To resolve any incomplete trace instances, see How to Resolve Incomplete Activity Instances.

How to Update References to the BAM Notification Services Databases

1.     Click Start, click Run, type cmd, and then click OK.
2.     At the command prompt, navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Tracking.
3.     Type: bm.exe get-config –filename:config.xml
4.     Open the xml file created in step 2 to obtain the list of the computers on which you must re-register Notification Services.
The computer names are listed in the <Property Name=> parameters in the <DeploymentUnit Name="Alert"> section of the xml file:
        <DeploymentUnit Name="Alert">
               <Property Name="GeneratorServerName" />
               <Property Name="ProviderServerName" />
               <Property Name="DistributorServerName" />
  </DeploymentUnit>
5.     On each computer listed in the xml file, stop the NS service and then unregister an instance of Notification Services:
1.     Click Start, click Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click Notification Services Command Prompt.
2.     At the command prompt, type: net stop NS$BamAlerts
3.     Type the following command to unregister the instance:
nscontrol unregister -name BamAlerts
Unregistering an instance removes the registry entries, removes the NS$instance_name service (if present), and deletes the performance counters for the service.
6.     Re-register the Notification Service:
1.     Click Start, click Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click Notification Services Command Prompt.
2.     At the command prompt, type: nscontrol register -name BamAlerts -server <ServerName> -service -serviceusername "<ServiceUserName>" -servicepassword "<ServicePassword>"
This enables Notification Services to log on to the correct database (this information is maintained in the registry of the service machine by nscontrol).

Description: http://i.msdn.microsoft.com/Aa578110.Important(en-us,MSDN.10).gifImportant
Remember to use the new Notification Services databases server in the -server option when re-registering the service. In addition, you should use the same user name for the new Notification Services service as the old one.

7.     On the computer that hosts the BAM portal, click Start, click Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click Notification Services Command Prompt.
8.     At the command prompt, type:
net stop NS$BamAlerts
9.     At the command prompt, type:
nscontrol unregister -name BamAlerts
10.  At the command prompt, type:
nscontrol register -name <BamAlerts> -server<NotificationServicesDatabaseServer>
11.  At the command prompt, type: net start NS$BamAlerts.
12.  Click Start, click Run, type cmd, and then click OK.
13.  At the command prompt, navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Tracking.
14.  At the command prompt, type:
bm.exe update-config –FileName:config.xml

How to Resolve Incomplete Activity Instances

1.     Run the following query against the BAM Primary Import database:
Select ActivityID from bam_<ActivityName>_Active where IsComplete = 0
2.     If data from external systems indicates that the activity instance is in fact completed, run the following query to manually complete the instance:
exec bam_<ActivityName>_PrimaryImport @ActivityID=N'<ActivityID>', @IsStartNew=0, @IsComplete=1

Description: http://i.msdn.microsoft.com/Aa561272.note(en-us,MSDN.10).gifNote
You can follow the same process to complete a continuation activity by replacing ActivityID with ContinuationID.
Description: http://i.msdn.microsoft.com/Aa561272.note(en-us,MSDN.10).gifNote
If the main trace has any active continuation traces, it remains active until the continuation traces are completed.

Read more ...
Posted by at No comments:
Labels: , , , ,
Subscribe to: Posts (Atom)
loading..