BizTalk provides out-of-the-box functionality to include your custom BizTalk database in the standard backup procedure. 
You can find all details on MSDN.  Here’s a small synopsis:

• Execute these scripts against your custom database
  • %BTSINTALLDIR%\Schema\Backup_Setup_All_Procs.dsl
  • %BTSINTALLDIR%\Schema\Backup_Setup_All_Tables.dsl

• Add the SQL Agent account to the BTS_BACKUP_USERS role of your custom database

• Modify the adm_OtherBackupDatabases table of the BizTalkMgmtDb, add a record for your custom database

       

 

However, when I executed the BizTalk Backup Job after this change, I got the following exception:

• [SQLSTATE 01000] (Message 4035)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)  BACKUP LOG cannot be performed because there is no current database backup.

The cause was the fact that it is not possible to take a transactional backup of a database, if you didn’t take a full backup first.  This issue was fixed by executing the stored procedure sp_ForceFullBackup of the BizTalkMgmtDb.  Now the custom database had a initial full backup and the BizTalk Backup Job executed successfully.  This result was an extra database backup:

Often we find ourselves at a customer or project explaining why it is better to have a dedicated SQL server or cluster for your BizTalk platform.

One can easily come up with the most obvious reason as to why this is important:

BizTalk is designed and optimized for parallel processing and throughput and the BizTalk databases are resource intensive: it is the heart of your BizTalk environment.
If you are sharing a SQL server between BizTalk and any of your other applications, you are pulling away those resources from BizTalk. This is in regard to memory, threads, CPU cycles, etc…

However I find that customers are hard to persuade with that argument and I cannot blame them:

• If they look at the resource usage of their SQL server that is currently hosting their BizTalk platform, it is hardly doing anything. Memory usage is high off course (it is quite normal behavior for SQL to take up all memory for caching purposes), but the load on CPU and disk is mostly quite low.
• They have other applications (either old or new) that need a SQL server environment. Most of the times the SQL database they need hosting is nothing more then a few dozen MB, so the tendency is there to say: what harm can it bring to host it on the BizTalk SQL?
• They paid good money for the SQL server license(s) and BizTalk license(s) they are running their BizTalk platform on. They do not want to ask their manager for extra money again (cost of hardware/storage/licenses) because of budget limits or any other reason for that matter.

So in the end, what often happens at some projects is that the SQL server or SQL cluster hosting your BizTalk platform becomes a shared server or cluster hosting the BizTalk environment as well as several other databases.

Why is a shared SQL a bad thing?

Like I mentioned above, any resources you’re taking away from your SQL server, you are effectively taking away from your BizTalk platform.

Additionally, there is something on SQL called the “Max DOP” parameter or the parameter for the “maximum Degree Of Parallelism”. Learn more about max DOP on MSDN.

In short, it is the number of processors used by SQL server on a SQL Server instance to execute queries in parallel to each other in order to give the results as fast as possible.
If your Max DOP parameter is set to something higher than 1, SQL will try to process queries in parts by spreading them over a number of processors. All done by the SQL engine without you having to care about it much.

The reason why I talk about this here is that BizTalk, during configuration, will actually set this max DOP parameter to one (1) on the SQL server instance where your Message Box database is located. This is due to the fact that the BizTalk Message Box database is highly optimized and works quite differently from other "normal" databases. Other databases are just data stores where SQL needs to retrieve data from and store data to. BizTalk is built quite different in many ways while it stays in essence a data store off course. The BizTalk databases are that optimized that setting Max DOP to something higher than one (1), it will actually hurt BizTalk throughput and performance.

Having said this, your “other” non-BizTalk databases will actually perform worse in 99,9% of the cases having Max DOP set to 1.

So there it is, another reason to have a dedicated SQL server/cluster for your BizTalk platform.

Do you know any other reasons why sharing your BizTalk SQL environment is a bad idea? Feel free to put them in the comments.

When trying to install SQL Server 2008 R2 on a Windows Server 2008 R2 Virtual Machine on Azure, you might come across the following error at the Setup Support Rules step:

This has to do with two manifest files that are corrupt.

If you have another working installation, you can copy the following two manifest files from the working server to the failing server:

• C:\Windows\winsxs\Manifests\amd64_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_8a1a02152edb659b.manifest
• C:\Windows\winsxs\Manifests\x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest

If you don’t have another working installation, find and open the files mentioned above and paste the following xml in them:

• AMD64 Manifest:
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <!-- Copyright © 1981-2001 Microsoft Corporation -->
  <assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <noInheritable/>
  <assemblyIdentity type="win32" name="Microsoft.VC80.ATL" version="8.0.50727.4053" processorArchitecture="amd64" publicKeyToken="1fc8b3b9a1e18e3b"/>
  <file name="ATL80.dll" hash="99840dcc34e78af239d80841eba316c184e407cd" hashalg="SHA1"/>
  </assembly>
• X86 Manifest:
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <!-- Copyright © 1981-2001 Microsoft Corporation –>
  <assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <noInheritable/>
  <assemblyIdentity type="win32" name="Microsoft.VC80.ATL" version="8.0.50727.4053" processorArchitecture="x86" publicKeyToken="1fc8b3b9a1e18e3b"/>
  <file name="ATL80.dll" hash="6d7ce37b5753aa3f8b6c2c8170011b000bbed2e9" hashalg="SHA1"/>
  </assembly>

Note: You probably need to change the permissions for those files to be able to overwrite them.
To do so, go to the file properties, Security tab, Advanced, Owner tab, Edit, Select the Administrators group and click three times OK until all windows are closed.
Reopen the properties for the file, Security tab, Edit, Select the Administrators group and select Full Control.

Today I've encountered a problem with the party configuration, specifically while reconfiguring send ports from a party. An error occurred when I removed a send port from a party and saved my settings.

The error

The error occurred during the removing of a send port from a party: Sendport Reference [Microsoft.BizTalk.B2B.PartnerManagement.SendPortReference] cannot be deleted as it is use by agreement [Microsoft.BizTalk.B2B.PartnerManagement.SendPortReference]. (Microsoft.BizTalk.B2B.PartnerManagement)

The troubleshooting

Based on a forum thread on MSDN, this seems to be a bug in the BizTalk console, but has never been officially announced or patched. The forum thread suggests waiting for the next cumulative update for BizTalk server [link: cumulative updates Biztalk]. But even when I applied an update for BizTalk to the next version, the problem didn't got solved.

Based on the error message, a constraint prohibits removing the send port record assigned to this party. The OnewayAgreementSendPortReference table contains all send port records for each party. A foreign key is present between OnewayAgreementSendPortReference.SendPortReferenceId and the SendPortReference.Id colomn.

The solution

I had to manually remove the Foreign key record from the SendPortReference table.

Connect to the BizTalk SQL database instance of the BizTalkMgmtDb catalog.
Change the @Name variable to the name of the send port you would like to remove from the party.

DECLARE @NAME varchar(100)
SET @NAME = 'spo_3025816970108_3014683300200_INVRPT_EDI_FTP_PRD'

DECLARE @ID int
SELECT @ID = id
  FROM [BizTalkMgmtDb].[tpm].[SendPortReference]
  WHERE Name = @NAME  
  
DELETE FROM [BizTalkMgmtDb].[tpm].[OnewayAgreementSendPortReference]
  WHERE SendPortReferenceId = @ID

  DELETE FROM [BizTalkMgmtDb].[tpm].[SendPortReference]
  WHERE Name = @NAME

After running this query, refresh your BizTalk administration console and the send port is now removed from the party.

Important note:
Be aware that fiddling with the BizTalk internal databases is not a recommended approach and is not supported by Microsoft.
Any support case dealing with issues after manually changing the BizTalk internal databases will have to be paid fully or result in a re-installation!

As you all know, the BizTalk XML Disassembler checks - by default - if a schema is deployed that matches the incoming message.  BizTalk does this via the BTS.MessageType, which is a combination of TargetNamespace#RootNode.  This BTS.MessageType must be unique in your BizTalk Group.  If two schemas are deployed with the same TargetNamespace#RootNode, the XML Disassembler will throw this error message:

• Cannot locate document specification because multiple schemas matched the message type

Lately we had an issue, related to this subject.  We had two schemas deployed, that varied only by case.  Below, you find a similar example.

Now the question is: will BizTalk treat these schemas as identical schemas or not? My first guess was that BizTalk would treat these schemas as unique because it’s related to XML, which is case-sensitive.  However, when processing one of the two messages, the XML Disassembler threw an error because multiple schemas matched the message type.

I investigated how BizTalk resolved the schema, so I turned on SQL Server Profiler.  There, I discovered this entry:

• exec bt_GetDocSpecInfoByMsgType @nvcMsgtype=N'http://Codit.BizTalk.Blog.REQUEST#REQUEST',@iPipelineAssemblyId=2,@iFlag=@p3

The most important query of the GetDocSpecInfoByMsgType stored procedure is this one:

Because my SQL Server Collation, Latin1_General_CI_AS, is case-insensitive, the query results into 2 matched schemas, so the XML Disassembler will throw an exception.

After some searching on MSDN I’ve found this remark:

• If you will be using two namespaces that vary only by case, the BizTalk database must be installed with a case-sensitive collation. Examples of case-sensitive collations include binary and non-binary collations with case-sensitivity enabled. If this is not done, schema resolution will fail because XML is case-sensitive.

Conclusion: Is the BTS.MessageType case-sensitive or case-insensitive? It depends on your SQL Server Collation.

Toon Vanhoutte

The out-of-the-box BizTalk Server Operators group has limited access to the BizTalk environment. An extract from MSDN:

Members of the BizTalk Server Operators group can do the following:

• View service state and message flow
• Start or stop applications
• Start or stop orchestrations
• Start or stop send ports or send port groups
• Enable or disable receive locations
• Terminate and resume service instances

Members of the BizTalk Server Operators group can do the following:

• Modify the configuration for BizTalk Server
• View message context properties classified as Personally Identifiable Information (PII) or message bodies.
• Modify the course of message routing, such as removing or adding new subscriptions to the running system, including the ability to publish messages into the BizTalk Server runtime.

Lately, I had a request to elevate the permissions for BizTalk Operators, so they were able to see the tracked message bodies.  The content of a message is often needed for a decent troubleshooting.  Because BizTalk security is actually based on SQL Server security, it was pretty easy to implement this request.  It’s sufficient to give the database role “BTS_OPERATORS” additional EXECUTE rights on specific BizTalk stored procedures, which are related to the retrieval of BizTalk message bodies.  All details can be found in the script below:

USE BizTalkDTADb; 
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessage
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessageFragments
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessageParts
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedMessageContext
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedMessages
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPart
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartByID
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartFragment
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartNames
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedParts
		TO BTS_OPERATORS;
GO 

USE BizTalkMsgBoxDb;
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessage
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessageFragments
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::bts_GetTrackedMessageParts
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadMessageContext
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadMessages
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadPart
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadPartFragment
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadPartNames
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadParts
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedMessageContext
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedMessages
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPart
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartByID
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartFragment
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedPartNames
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::ops_LoadTrackedParts
		TO BTS_OPERATORS;
GO 

USE BizTalkMgmtDb;
	GRANT EXECUTE ON OBJECT::dpl_MessageType_Part_Save
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::dpl_MessageType_Save
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::dpl_Operation_MsgType_Save
		TO BTS_OPERATORS;
	GRANT EXECUTE ON OBJECT::dpl_SaveItem
		TO BTS_OPERATORS;
GO

By executing this SQL Server script, you can easily grant them the rights to view BizTalk message bodies which allows easy debugging or follow up in different scenarios without having to change memberships.

Update:
Please note that the above method is not supported by Microsoft, so be sure to know what you are doing!
Also note that database schemas and security may vary depending on the version of BizTalk you are using.

Recently, we've encountered some problems with SCOM 2007 R2 monitoring of a BizTalk 2010 environment. The SQL Agent Job "Backup BizTalk Server (BizTalkMgmtDb)" failed, but we didn’t receive any SCOM alert about that.  The first reflex was to look at the BizTalk Management Pack.

BizTalk Server Management Pack

The BizTalk Server Management Pack contains a rule, called “CRITICAL ERROR: A SQL Server agent job failed - Backup BizTalk Server”, which is responsible for alerting us in case of troubles.  The rule is disabled by default, but we had already enabled the rule with an override.

Why didn’t we receive an alert?  We had a closer look at the default rule and discovered that this is the way of identifying the backup job failure:

-    It’s a rule that subscribes on the event log [TypeID="Windows!Microsoft.Windows.EventProvider"]
-    It looks for event log entries with ID = 208 [EventDisplayNumber equals 208] in the Application log
-    The computer name is set to $Target/Property[Type="Microsoft.BizTalk.Server.2010.ServerRole"]/ComputerName$

When the error occurred, we indeed discovered event log entries with ID 208 on the SQL server.  But this rule is subscribing on the event log of the BizTalk server.  Conclusion: the default rule will only work on a single box installation (SQL server and BizTalk server have the same event log).

To solve this issue, we’ve created a custom rule, based on the default one.  The custom rule had exactly the same configuration, except for the computer name that we’ve changed to:
-    “$Target/Property[Type="Microsoft.BizTalk.Server.2010.BizTalkGroup"]/MgmtDbServerName$”

Now the rule was subscribing on the event log of the SQL Server (running the ManagementDb), and we received an alert when the backup job failed.  This worked fine, until we deployed this rule against a multi-server BizTalk environment, containing a SQL Server cluster.  Suddenly we’ve received SCOM errors, complaining that the SCOM Agent couldn’t access the event log of the MgmtDbServer.  The cause of this is that the BizTalk Management Pack actually discovers the virtual SQL Server Cluster name.  As this is not a physical server name, it’s normal that the SCOM Agent can’t access the event log.

To overcome this issue, we could have created a new custom rule, which uses the discovered MgmtDbServer and performs a query on the [msdb].[dbo].[sysjobhistory] system table.  The sysjob tables contain all needed information to detect SQL Agent Job failures.  As this is too complex, we decided to have a look at the SQL Server Management Pack.

SQL Server Management Pack

As it is actually a SQL process that fails, the SQL Server Management Pack should be responsible for alerting us.  But why didn’t that happen?  There are actually two reasons for this:

-    The discovery of SQL Agent Jobs is disabled by default.
-    The alerting for SQL Agent Job Failures is disabled by default.

We enabled the discovery and alerting, tested the solution and everything went smooth.

Conclusion

SQL Agent Jobs should be monitored by the SQL Server Management Pack.  This Management Pack should always be installed in a BizTalk environment, as SQL Server is the core of BizTalk.  Please, keep in mind that the alerting of SQL Agent Jobs is disabled by default.

It’s actually a strange approach of Microsoft to try to include the monitoring of the SQL Agent Jobs in the BizTalk Server Management Pack.  Certainly because the default implementation only works for a single box installation.  On MSDN you can find a vague description on this subject.

Toon Vanhoutte

Lately, a lot of CTP’s came available on the Windows Azure platform.  My goal is to try them all out.  I blogged on two of these technologies already:

• Using Windows Azure VM Role
• Azure connect - VPN as a service

This time, we’re getting on the data side, with SQL Azure Data Sync.  This data synchronization service is built on the Microsoft Sync Framework technologies. It provides bi-directional data synchronization and data management capabilities allowing data to be easily shared across SQL Azure databases within multiple data centers.

A very good and detailed overview on Data Sync can be found on the TechNet Wiki: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-sync-overview.aspx

Scenarios

This SQL Azure Data Sync service is a very promising and interesting feature that opens a lot of great scenarios:

• For development/testing purposes: provide data in your local database (on development) and synchronize with the Azure database to feed the cloud instances.
• Synchronizing configuration or master data across databases in a very easy way.
• Use SQL Azure Reporting Services on ‘local’ data.

Registration for CTP

Since this feature is still in CTP, you need to register first to get an invitation code.  To do all this, just browse to https://datasync.azure.com and sign in with your live id.  After this, you can enter your e-mail and other details and you should receive an invitation code some time later.  With that registration code, you can log on to the full features.

Concepts

The following concepts are important in setting up Data Sync:

• Sync group: a sync group is a group of databases that can be synchronized together
• Database: a database that gets registered for SQL Azure Data Sync
• Agent: an agent is a Windows Service that performs and orchestrates the actual synchronization

Configuring data synchronization

Step 1: download the Sync Agent

A Sync agent is only required when synchronizing data from an on-premise database.  If the synchronization is set up from Cloud to Cloud, then it is not required to download the Agent, since the synchronization logic will be run in the Cloud.

For this example, I will synchronize a local database with a SQL Azure database.

On the Data Sync portal, click the Agents tab page and download the Agent installer at the bottom of the page.  After running the installer (and providing credentials for the Data Sync windows service), you can open a local configuration tool, through the Start menu: SQL Azure Data Sync Agent CTP2.

This tool allows you to configure local databases for synchronization.  The following screenshot shows the default configuration window, after installation.  To join this agent to a Windows Azure subscription, it is important to configure the Agent Key first.  This can be done by clicking the Edit Agent Key button and providing the key that can be copied from the Data Sync Portal. 

Once this is configured, it should be possible to Ping the Azure Sync Service, by clicking the Ping Sync Service button.

Step 2: Add your local database

Now we have the local agent installed, we will add a local (on premise) database to the configuration tool, so that it can be synchronized later.

In this example, I am using a custom database with 3 tables: Customer, OrderLines, OrderEvents.

• I will add this database in the SQL Azure Data Sync Agent tool, by clicking the ‘Add Member’ button at the left.  This pops up a configuration window where the server, database and the authentication method need to be selected. (1) 
• The Data Sync services have some limitations on supported data types, etc.  It is possible to check the database for issues, by clicking the ‘Check Member Schema’ button. (2).
• I added a field with an unsupported data type (geography) to the customer table and the Schema validation provided a warning that indicated that this column would be skipped. (3) This is because geography is not supported in Windows Azure at this point in time.
• It is very important to start the Windows Service of the Azure Data Sync Agent, in order to register the client database with the Windows Azure subscriptions.

Step 3: Add the SQL Azure database

Adding a SQL Server database to the Data Sync subscription is much easier and can be done on the Data Sync management portal, by clicking the Databases tab and clicking the Add button.  In the pop up, you just need to provide the server, database and credentials, before saving. 

In my example, I am just adding a new empty database, here.

Step 4: Set up the synchronization, through a Sync Group

• In the management portal, click the New Sync Group button in the Sync Groups tab. And add all the databases you want to synch together to the database list(1) and click Next.
• In the next screen, you can add the tables, per database that you want to synchronize.  For this demo, I want to synchronize everything, except the events table. (2)  You can also enable a schedule for the synchronization (expressed in minutes).
• Once the group is created, you can synchronize the group easily.

Testing the data synchronization

Now I have the databases configured for synchronization, it’s time to play around with it a bit.  To execute the synchronization, you can either rely on the synchronization schedule , when configured on the Sync group, or you can manually trigger the synchronization.

1st synchronization: Creating tables in the cloud.

The first synchronization I did created the two synchronized tables on my empty Cloud database and added the data there.  One thing to notice is that the Location column (with the spatial data type) was not created on the SQL Azure database. 

2nd synchronization: Adding data locally, synchronizing to the cloud.

In this test, I added two customers and some order lines to the local database and synchronized with the Cloud database, to find out that everything was copied without problems.

3rd synchronization: Adding data in the cloud, synchronizing locally.

In this test, I added a customer to the cloud database and synchronized with the on premise database, to find out that everything was copied without problems.

4th synchronization: Adding data on premise and in the cloud, synchronizing bi-directionally

In this test, I added customers in both databases before synchronization to find out that the data was synchronized correctly. 

Data conflicts

Now, I wanted to simulate some conflicts to find out how the Data Sync would handle them.

Adding or updating data in both databases, with the same primary key.

I added two different customers, but with the same primary key in both databases.  But, surprisingly, the synchronization happened without any issue, but my local customer was overridden by the cloud customer, resulting in lost data. 

• Both records (local + cloud) were normally marked as new, so it should be possible to detect this.
• I was looking to find out if the ‘most recent’ record won, but in all scenarios, the data from the cloud database survived the synchronization.  (probably, because this database was added first)

Maybe something for a next CTP?

Deleting data that is being referenced by a non-synchronized table

Another interesting test was to delete an order line on my cloud database.  But this order line was being referenced by a record in my local database (OrderEvent).  Knowing that the OrderEvent table is not being synchronized, this should result in a conflict.

Here I did not receive an exception, but I also noticed that my record on my local database still existed, where my cloud record was deleted.  So here my data was out of synch. 

Maybe something for a next CTP?

Adding a third database

The last test I did, was adding a new empty (cloud) database to the sync group and after the synchronization, everything seemed to work automagically.  Great!

Underlying design

• When configuring a table for synchronization, triggers are being created for the three actions: insert, update, delete.  They have the following name structure: [TableName]_dss_[action]_trigger.
• These triggers add the data in new tables that are being created during configuration of the sync group.  For every data table, a sync table is being added with the following name: DataSync.[TableName]_dss_tracking
• Next to that, we can also see that a lot of new stored procedures are getting created

Conclusion

This CTP looks already very stable and the sychronization (between multiple databases) is very smooth.  I am just hoping that there will be a better view or configuration for synchronization exceptions (like explained in the conflicts section).

Sam Vanhoutte, Codit