Modifying the size of a nvarchar column of a data item of an activity

 Applies to: Microsoft BizTalk Server 2004

You have already deployed a BAM definition with a text data item but now you want to change the maximum length of data item. The recommended way would be to remove the bam deployment and re-deploy the BAM definition with the new length for the data item. However, it is not always possible as you may already have data in the BAM tables and cannot lose the data.

This blog will detail how you may modify the length of the text data item without having to remove and re-deploy. Note that this is unsupported by Microsoft.

1.      Modify the columns of tables in Bam Primary Import database

• bam_<Activity Name>_Active
• bam_<Activity Name>_Completed

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Item Name> NVARCHAR(<New Length>)

      GO

• bam_<View Name>_<Activity Name>View_ActiveInstancesSnapshot if a view has been deployed

     ALTER TABLE <Table Name>

            ALTER COLUMN <Alias Name> NVARCHAR(<New Length>)

      GO

2.      Modify the stored procedure in Bam Primary Import database

• bam_<Activity Name>_PrimaryImport

Modify the stored procedure input parameter for the nvarchar data item to its new length.

3.      Modify the columns of tables in Bam Archive database

• bam_<Activity Name>_Instances    

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Item Name> NVARCHAR(<New Length>)

   GO

4.      Modify the columns of tables in Bam Star Schema database

• bam_<View Name>_<Activity Name>View _Staging

      ALTER TABLE <Table Name>

            ALTER COLUMN <Alias Name> NVARCHAR(<New Length>)

   GO

• bam_<Cube Name>_Dim_<Data Dimension> 

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Dimension Member Name> NVARCHAR(<New Length>)

   GO

The steps to create a report based on data in Bam infrastructure is no different from what one would do for other SQL data sources. It is recommended that you read the “Creating a Report with Report Designer” section in SQL Server 2005 Books Online on how to create a report using the Microsoft SQL Server Report Designer. 

Report Wizard

“Select a data source” page

Bam provides two data sources that Reporting Services may point to:

1.                  Bam Primary Import database for views on activity instances or real-time aggregations. Make sure to select Type=”Microsoft SQL Server” and Connection String=”Data Source=<server name>; Initial Catalog=<database name>”, where <server name> and <database name> are the server and database names of your Bam Primary Import database.

2.                  Bam Analysis database for querying the analysis cube. Make sure to select Type=”Microsoft SQL Server Analysis Server” and Connection String=”Data Source=<server name>; Initial Catalog=<database name>”, where <server name> and <database name> are the server and database names of your Bam Analysis database.                                        

“Design the Query” page

For the Bam Primary Import database, there are two types of views:

1.                  dbo.bam_<view name>_<activity name>View_View. This is the view for instance data.

2.                  dbo.bam_<view name>_<real time aggregation pivot table name>_RTAView. This is the view for the real-time aggregations.

You can type in “select * from view” to return the desired result set.

For the Bam Analysis database, click on the query builder and drag and drop the dimensions and measures of the cube named <view name>  to return the desired result set.                      

Other steps

Go through the rest of the steps to decide what and how you want the data to be presented.

The following applies to BAM in BizTalk 2006. It would work similarly in BizTalk 2004, without SQL NS databases.

Deployment and Removal

To ensure consistent schema across all BAM databases, run the “Backup BizTalk Server” SQL job for each deployment of activities and/or views. In addition, backup the DTS packages, BAM analysis, star schema and archive databases.

Runtime

To ensure databases are in sync after a restore, it is necessary to back up the BAM databases in the following order:

1. Run SQL agent job “Backup BizTalk Server” to back up BAM Primary Import, SQL NS as well as other BizTalk databases.
2. Run BAM analysis and data maintenance DTS/SSIS packages for all activities and cubes respectively.
3. Backup BAM star schema, analysis and archive databases. 

The BAM star schema, analysis and archive databases are modified when the analysis and data maintenance DTS/SSIS packages are executed. Hence, in case of failure of Primary Import, restore the backup of star schema, analysis and archive databases BEFORE the last successful backup of Primary Import.

It is possible that the archive database may fail in between the successful execution of the data maintenance DTS/SSIS package and the next backup of the database. In order to ensure that at any time a complete set of archived data can be recovered, modify the data maintenance DTS/SSIS package for each activity to insert a step to backup the Archive database before the last step “End Archiving”. The “End Archiving” step deletes the  archived data from Primary Import database, updates metadata tables and views.

The following assumes that you are using Internet Information Services (IIS) 6.0 or higher and your operating system is Microsoft Windows Server 2003.

1.   Create Application Pool

a.       Click Start->Programs->Administrative Tools->Internet Information Services (IIS) Manager.

b.      Right mouse click on “Application Pools”->New->“Application Pools”.

c.       Type in name (say, “BAMAppPool”) of application pool to create.

d.      On the properties page of the application pool, choose an application pool identity. Click OK when done.

e.       Make sure that the application pool identity is added to the “IIS_WPG” local group and if Sharepoint is configured, make sure it is also added to the “STS_WPG” group.

2.   Create Virtual Directory

a.       Using Internet Information Services (IIS) Manager, create a virutal directory named “BAM”.

b.      Expand “Web Sites” node->Right mouse click on “Default Web Site”->New->Virtual Directory.

c.       In the “Virtual Directory Creation Wizard”, type in the virtual directory name, choose “<install location>\BAMPortal” as the content directory and only allow “Read” permissions.

d.      On the properties page of the virtual directory, in the “Virtual Directory” tab, create an application named (say) “BAM”, choose “Script only” execute permissions and the application pool “BAMAppPool” created in previous step. Click “Edit” on the Authentication and access control tab. Uncheck the “Enable anonymous access” checkbox on the “Directory Security” tab and make sure that Integrated Windows Authentication checkbox is checked. Click OK when done.

e.       On the properties page of the “BAMManagementService”, create an application named (say) “BAMManagementService”, choose “Script only” execute permissions and the application pool “BAMAppPool” created in previous step. Click “Edit” on the Authentication and access control tab. Uncheck the “Enable anonymous access” checkbox on the “Directory Security” tab and make sure that Integrated Windows Authentication checkbox is checked. Click OK when done.

f.        On the properties page of the “BAMQueryService”, create an application named (say) “BAMQueryService”, choose “Script only” execute permissions and the application pool “BAMAppPool” created in previous step. Click “Edit” on the Authentication and access control tab. Uncheck the “Enable anonymous access” checkbox on the “Directory Security” tab and make sure that Integrated Windows Authentication checkbox is checked. Click OK when done.

3.   Setup web.config For Web Services

            There are 3 web.config files to create.

a.     Web.config in <install location>\BAMPortal folder

                                             i.      Make a copy of web.config.tmpl in <install location>\BAMPortal folder and renamed it to web.config.

                                           ii.      Open the web.config file, replace “localhost” with the name of your server hosting the web service for the following two lines:

<add key="BamQueryWSUrl" value="http://localhost/BAM/BamQueryService/BamQueryService.asmx"/>

<add key="BamManagementWSUrl" value="http://localhost/BAM/BamManagementService/BamManagementService.asmx"/>

                                          iii.      Set culture to “neutral” for the assembly.

<assemblyIdentity name="Microsoft.BizTalk.Bam.WebServices" publicKeyToken="31bf3856ad364e35" culture="" />

                                         iv.      Replace “Everyone” with the group that you want to use for controlling access to the portal for the following line:

<allow roles="Everyone"/>

                                           v.      Save the web.config file.

b.     Web.config in <install location>\BAMPortal\BAMManagementService folder

                                             i.      Make a copy of web.config.tmpl in <install location>\BAMPortal\ BAMManagementService folder and renamed it to web.config.

                                           ii.      Open the web.config file, replace “localhost” and “BAMPrimaryImport” with the name of your primary import server and database respectively.

             <add key="BamServer" value="localhost"/>

<add key="BamDatabase" value="BAMPrimaryImport"/>

                                          iii.      Replace “Everyone” with the group that you want to use for controlling access to the web service for the following line:

<allow roles="Everyone"/>

                                         iv.      Save the web.config file.

c.     Web.config in <install location>\BAMPortal\BAMQueryService folder

                                             i.      Make a copy of web.config.tmpl in <install location>\BAMPortal\ BAMQueryService folder and renamed it to web.config.

                                           ii.      Open the web.config file, replace “localhost” and “BAMPrimaryImport” with the name of your primary import server and database respectively.

             <add key="BamServer" value="localhost"/>

<add key="BamDatabase" value="BAMPrimaryImport"/>

                                          iii.      Replace “Everyone” with the group that you want to use for controlling access to the web service for the following line:

<allow roles="Everyone"/>

                                         iv.      Save the web.config file.

4.   Use ASP.Net To Encrypt Web Service Credentials

a.       From the command prompt, type the following:

aspnet_setreg.exe -k:"SOFTWARE\Microsoft\BizTalk Server\3.0\BAM\WebServices\identity" -u:<yourDomainName\userName> -p:<password>

b.       Press Enter.

Note  For more information about aspnet_setreg.exe, see Knowledge Base article 329290, "Use the ASP.NET Utility to Encrypt Credentials and Session State Connection Strings" located at http://go.microsoft.com/fwlink/?LinkID=16728

5.   Grant Permissions in Registry

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor (regedit.exe) at your own risk.

a.       Grant the web service identity “Query Value”, “Enumerate Subkeys”, “Notify” and “Read Control” permissions to the following registry key.

                                                         i.      HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0

b.      Grant the application pool identity “Query Value”, “Enumerate Subkeys”, “Notify” and “Read Control” permissions to the following registry key.

                                                         i.      HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\BAM\WebServices\identity\ASPNET_SETREG

6.   Grant Permissions to Folders

a.      Grant the application pool identity “Traverse Folder / Execute File”, “List Folder / Read Data”, “Read Attributes”, “Read Extended Attributes”, “Create File / Write Data”, “Create Folders / Append Data”, “Write Attributes”, “Write Extended Attributes” and “Read Permissions” permissions to the Windows temp folder, its sub folders and files.

7.   Update Bam Configuration

a.       Insert the following as a child of BAMConfiguration element in the BamConfiguration.xml, replacing “yourServer” with the server where you want to install the BAM web service.

<GlobalProperty Name="BAMVRoot">http://yourServer/BAM </GlobalProperty>    

b.       Run "bm.exe update-config -FileName:BamConfiguration.xml"

8.  Grant Permissions in BAM Primary Import Database

a.       Add the web service identity to SQL Logins.

b.      Add the web service identity to the “BAM_ManagementWS” role in the BAM Primary Import Database

If you have alerts configured, refer to my blog "BAM without BizTalk Configuration (Part 3)" for the additional step.

In BizTalk 2004, in order to add BAM Analysis after an initial configuration without, one can use a command in bm.exe to create the BAM Analysis as well as the BAM Star Schema databases. The star schema database acts us a staging database for the building of the BAM cube. The steps are:

1. Modify the BamConfiguration.xml in the \Microsoft BizTalk 2004\Tracking folder. Add the following and type in the server and database names for the star schema and analysis databases.

<DeploymentUnit Name="StarSchemaDatabase">
   <Property Name="ServerName"></Property>
   <Property Name="DatabaseName"></Property>
</DeploymentUnit>
<DeploymentUnit Name="AnalysisDatabase">
   <Property Name="ServerName"></Property>
   <Property Name="DatabaseName"></Property>
</DeploymentUnit>

   2.   Run "bm dbsetup BamConfiguration.xml" from the same folder.

In BizTalk 2006, re-run the BizTalk Server Configuration (Start Menu->Programs->Microsoft BizTalk Server 2006->BizTalk Server Configuration)

If you are using the tracking profile editor to map orchestration shapes/events to BAM activity, the date time value that appears in views and cubes will be in UTC (Coordinated Universal Time) time. With a little tweaking of a stored procedure for each activity, you can display the date time value in your local time.

The stored procedure to modify is: bam_<Activity Name>_PrimaryImport. Add the following couple of lines in this stored procedure:

DECLARE @TimeDifference int
SELECT @TimeDifference = ROUND(DATEDIFF(mi, GetUTCDate(), GetDate()), -1)

For each variable of datetime type passed into this stored procedure, add the following:

SELECT @yourVariable = DATEADD(mi, @TimeDifference, @yourVariable)

That's it!

I want to thank Seong Joon Kwak for asking the question and going through a couple of solutions before I arrive at this simple solution.

Thanks for coming to this blog. I've been active in answering newsgroup questions on Microsoft BizTalk Server; my specialty is in the Business Activity Monitoring (BAM) technology. After some time, I find myself repeating the answers/solutions to similar questions posted by different people. It may be very well that customers and partners are finding it hard to find the answers to their questions in the newsgroup. So my primary intention is to provide a location to gather up these questions and reply in a succinct way. Also, this forum allows me to explain in greater details aspects of the BAM technology than is usually possible in replying to questions in newsgroups. Please note that any solutions included here do not in any way imply that they are officially supported by Microsoft.

Usual disclaimer: The postings and any code are provided "AS IS" with no warranties.