BizTalk 2013 R2 database migration from SQL 2012 to 2014

On my Virtual PC which had BizTalk 2013 R2 configured with SQL 2012 as database server I thought of installing SQL 2014 and try using the new features of Column Store and see how it can help run the Integrations faster.
In this post I will show you how I migrated my existing BizTalk databases from SQL 2012 to 2014 which is installed on the machine.

•  Take Backup of all BizTalk databases from SQL 2012 and Restore it on to SQL 2014.
  

•  Create Scripts of all BizTalk SQL Jobs from SQL 2012 and execute it on to SQL 2014.
  

•  Create Scripts of all BizTalk SQL Logins from SQL 2012 and execute it on to SQL 2014.
  

•  Update the SampleUpdateInfo.xml file found in the following location with info of the current and the new SQL Server. For me current SQL 2012 was on KIWI server and the New SQL 2014 Server is on KIWI\SQL14.
  C:\Program Files (x86)\Microsoft BizTalk Server 2010\Bins32\Schema\Restore
  

•  Run the UpdateDatabase.vbs file which is found in the same folder which updates the Server references in the tables on the new SQL Server.
  

•  Run the UpdateRegistry.vbs file which is found in the same folder which updates the System Registry key references on the Server.
  

•  Finally I went into BizTalk Admin Console to check if the references were updated successfully and yes it did update everything.
  

Finally I would give credit to the following old Microsoft document which still holds true for  the new versions of BizTalk\SQL Server ;).
How to Restore Your Databases

BAM configuration failure in BizTalk 2013

HI,
Seems the BizTalk 2013 Installation software has not been tested properly.
I got many errors which I could google and fix it but this one was odd about BAM configuration failed with following error.

ERROR: Encountered error while executing command on SQL Server "".A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)The system cannot find the file specified (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
[ Error BAMConfigWizExt] 

Solution was to install the latest Cumulative update and try again without Server restart and it worked fine and BAM was configured correctly.
BizTalk Cumulative Update Link


Till now Core Product configuration(SSO,Group,Runtime) was not messed up by Microsoft in all versions (2004 till 2010) I installed/configured. I normally used to install Cumulative update after I am done configuring default BizTalk features or maybe it was a wrong way of doing it  :) .

Database Lookup Functoid : Multiple Where Clause : CRM PickList field

When integrating CRM I came across a Scenario while mapping a PickList field in CRM using BizTalk. Since we need to send the Index (AttributeValue) of the Value in the Xml to CRM for the PickList field I had to do a look up first to get the Picklist value Index.
Assume my Query is
Select top 1 from StringMap where Value='Staff' and AttributeName='lucky_title'
Staff comes from the Title field in the Input Xml.

Now in traditional Database Lookup Functoid we know about single where clause parameters but what if we have a multiple where clause?
Here is the StringMap table in CRM 2011


In that case what we have to do is add a String Concatenate Functoid or a Scripting Functoid for the Multiple Column and Multiple Value parameter with a separator.
I have used a Scripting Functoid.
select top 1 * from StringMap where Value + '|' +  AttributeName = Title|lucky_title

SQL Receive Adapter: Multiple tables in different Database

Last week I had a simple scenario where BizTalk Server will use a SQL Receive Adapter and poll a SQL Stored Procedure(SP) which will return the rows from multiple tables spread across 2 different database.
Initially I thought the only catch out here will be to grant rights across all tables for the same User Account with which Biztalk polls the SP. Following were the last 2 lines of my proc.
SET @ReturnXML=SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS
Select @ReturnXML
The SP worked perfect from SQL Query window but I started getting the following error when called by BizTalk SQL Receive Adapter.
Event ID: 5740
Description:The adapter "SQL" raised an error message. Details "HRESULT="0x      7a" Description="Description not provided"
I thought I am missing some access rights on the 'XYZ' table, I granted all possible rights for the user on the table but still the same issue. After struggling for 2 days I thought to concentrate only on what the SP returned in the last 2 lines shown above.I felt that BizTalk is having an issue with the data returned from SP as it wont care what you do in the SP.BizTalk is only interested in the results(rows) returned,So I removed the variable(@ReturnXML) and it worked. So SP now returns the SQL Query directly.
SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS

Hope it helps you.