Move BizTalk Server Databases from one SQL server to another

In this article we will explain how to migrate or move BizTalk databases from one server to another server. Let’s assume we want to move our BizTalk primary databases from local server to remote server or any other server on the network. Same method can also be used to move databases to SQL server cluster.

Assumptions:

· You should have admin user account access to both server, source and destination server.

· You should have basic knowledge of SQL Server.

· You should have basic knowledge of configuring BizTalk Server.

Step by step procedure

There are few major steps to follow which are described below. We will also explain these steps in detail in later.

1. Stop all BizTalk server services.

a. Perform a partial stop of all BizTalk services, do not perform a full stop!

b. Stop all host instances.

c. Stop BizTalk server services.

i. BizTalkServerApplication

ii. Enterprise Single Sign-On Service

iii. Rule Engine Update Service

iv. Any other BizTalk service that is running

clip_image001

clip_image002

2. Stop the SQL Server Agent service.

clip_image003

3. Stop IIS.

4. Backup following BizTalk databases using Backup BizTalk Server job.

a. BAMPrimaryImport

b. BAMAlertsApplication

c. BAMAlertsNSMain

d. BizTalkDTADb

e. BizTalkMgmtDb

f. BizTalkMsgBoxDb

g. BizTalkRuleEngineDb

h. SSODB

For configuring Backup BizTalk Server job please follow instructions mentioned at https://msdn.microsoft.com/en-us/library/aa546765.aspx

you may need to run sp_ForceFullBackup before running the job.

5. Restore above backup databases on the remote server. – NOTE: do not use simple restore task, use cs script method instead.

Open SQL server management studio and restore all the backed up databases. In the SampleUpdateInfo.xml file, comment out all of the databases except for those you have moved to the new server. Edit C:\Program Files\Microsoft BizTalk Server 2006\Schema\Restore\SampleUpdateInfo.xml

a. Do a “Replace All” and change SourceServer to ….
b. Do a “Replace All” and change DestinationServer to ….

Copy the edited SampleUpdateInfo.xml file to the following folder, open a command prompt, and go to computer: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore and run

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

Copy the edited SampleUpdateInfo.xml file to the following folder computer: Copy to %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore open a command prompt, and go to computer: %SystemDrive%Program Files (x86)Microsoft BizTalk Server <version>Bins32SchemaRestore and run

cscript UpdateRegistry.vbs SampleUpdateInfo.xml

6. Script all SQL server users from source server and run those script on destination server.

7. Script all SQL server agent jobs from source server and run those script on destination server.

8. Restart Windows Management Instrumentation service under services from services.msc

9. Re configure BizTalk server administrator

a. Remove current BizTalk group

b. Connect to existing group

c. Configure destination SQL server by giving server name

d. Configuring databases

Your BizTalk Server databases is now moved or migrated to another database server and BizTalk should be running properly.

Feel free to contact in case of any query

References:

https://msdn.microsoft.com/en-us/library/ee378546.aspx

Author: Jahanzaib.khan@alliedc.com

Posted in BizTalk, SQL. 1 Comment »

Azure BizTalk Rules Alternatives

 

All Preview API Apps including BizTalk Rules V1 API App was deprecated from Azure since Jan 18th, 2017. The migration path is to redeploy the V1 API App as App Service but this is not possible for built in BizTalk Rues API App.

Here are few alternatives of BizTalk Rules V1 API App:

Option 1: On-Premises BizTalk Rules Engine

we can use BizTalk BRE to define Rules in Rules Editor, deploy them on a BizTalk Server installation and write a Web API App to execute the policy through code, and we access the Web API App from Azure through a Hybrid Connection. The Logic Apps does not support Hybrid Connections yet, therefore we have to create a Relay Web App in Azure which take HTTP requests from the Logic App and pass it on to On-premises Rules Web API App.

 

Logic App –> App Service | Azure Hosted Relay Web API [Relay request and Cache Rules Result] –> Hybrid Connection –> On-Premises Web API –> BizTalk BRE Call

 

With this approach, we have access to full capabilities of BizTalk Rules Engine to create and execute complex Rules, and store and retrieve facts from on-premises SQL Server database. However, we need to have a BizTalk 2010+ installation on any on-premises server or on an Azure VM.

 

Option 2: CodeEffects Rules Engine

CodeEffects Rules Engine provide a web based Rules editing and testing platform, and we can choose this option if we don’t have a BizTalk Server installation, yet we want a UI based Rule editor and complex rules processing.

Logic App –> App Service | Azure Hosted Relay Web API [Relay request and Cache Rules Result] –> Hybrid Connection –> Code Effects Rule Engine

Option 3: Azure Functions as Rules Engine

 

Azure Functions is a solution for easily running small pieces of code in the cloud. The Code can be edited and deployed within Azure Portal. The idea is to use Azure Functions to defined and execute the Rules Logic and the Functions are invoked over an HTTP call from Logic Apps or Web Apps.

 

Logic App | Web App –> HTTP call to Azure Function –> Azure Function implementation

 

Option/Feature

Rules Authoring

Rules Complexity

Dependency

1: BizTalk Rules Engine

UI

Complex

BizTalk installation

2: CodeEffects

UI

Complex

CodeEffects Rules License/Free version

3: Azure Functions

Code

Custom Code

No

Posted in Azure, BizTalk, C#. Tags: . Leave a Comment »

Secure Azure Service Bus Relays with SAS Token

This article explains how to secure Service Bus Relays using Shared Access Signature (SAS) to prevent unauthorized/Anonymous access.

Shared Access Signature

Shared Access Signatures are based on SHA-256 secure hashes or URIs. In Azure all Service Bus services provide this authentication mechanism to control access to the resource that may be a service bus relay, a service bus messaging queue or a topic. The SAS is composed of two components:

1. A Shared Access Policy.

2. A Shared Access Signature which is also called a token.

To secure and access a Service Bus Relay endpoint first we need to create a Service Bus Relay Namespace in the Azure portal. After the namespace has been created, create a new policy under Service Bus Relay namespace. We created a new Service Bus Namespace and a new policy as RelayPolicy as shown in the picture below.

clip_image002

Note: we will use Policy name and the Primary key to generate a SAS token or Shared Access Signature in the console application which we are going to create shortly.

Create a console application in C# to generate SAS token

Now we will create a C# script to generate a SAS token.

Create a console application in Visual Studio and name it whatever you like.

Replace the code in the Program.cs class with the following code. Note that the Primary Key and the Policy name may vary and you need to put your own Policy name and Primary Key here.

static void Main(string[] args)

{

var strAuthorizaitionHeader = GenerateToken(“https:// Dev-Relays.servicebus.windows.net/”,

“RelayPolicy”, “*********************=”);

}

public static string GenerateToken(string resourceUri, string sasKeyName, string sasKey)

{

//set the token lifespan

TimeSpan sinceEpoch = DateTime.UtcNow – new DateTime(1970, 1, 1);

var expiry = Convert.ToString((int)sinceEpoch.TotalSeconds + 3600); //1hour

string stringToSign = HttpUtility.UrlEncode(resourceUri) + “\n” + expiry;

HMACSHA256 hmac = new HMACSHA256(Encoding.UTF8.GetBytes(sasKey));

var signature = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign)));

//format the sas token

var sasToken = String.Format(CultureInfo.InvariantCulture, “SharedAccessSignature sr={0}&sig={1}&se={2}&skn={3}”,

HttpUtility.UrlEncode(resourceUri), HttpUtility.UrlEncode(signature), expiry, sasKeyName);

return sasToken;

}

 

We will use this token in the logic app to send HTTP request to the Relay Service endpoint.

Now Open the logic app and go to the HTTP Post action and paste the SAS token string as value for Authorization HTTP header

clip_image004

Configurations for BasicHttp Relay transport properties

In Biztalk BasicHttp Relay set the transport properties as following.

1. Set the Security mode to Transport.

2. Set Relay Client Authentication Type as Relay Access Token.

The following picture shows the configurations settings of BasicHttp Relay transport properties

clip_image006

 

 

Could not load file or assembly ‘Microsoft.BizTalk.Interop.SSOClient, Version=7.0.2300.0

SSO Error

Unexpected exception occurred while configuring [BizTalk EDI/AS2 Runtime].

——————————
ADDITIONAL INFORMATION:

Could not load file or assembly ‘Microsoft.BizTalk.Interop.SSOClient, Version=7.0.2300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified. (EDIAS2Config)

FIX

Install SSOClient from [BizTalkServer2013 installation media]\BT Server\Platform\SSO\Client

Posted in BizTalk. Tags: . 1 Comment »

BizTalk EDI: Creating batching configuration in code

For outbound EDI batching, we have to create Batch Configuration in Party Configuration and set Filter Criteria about what messages should constitute one batch. But what if Filter Criteria is dynamic or we have too many batches to configure

here is code snippet to create batch configuration through API

 var builder = new SqlConnectionStringBuilder("DATA SOURCE="BTSSQLServer;Initial Catalog=BizTalkMgmtDb;
Integrated Security=SSPI;");
            using (var tmpCtx = TpmContext.Create(builder))
            {
                var agreement = (from p in tmpCtx.Agreements where p.Name == AgreementName select p).FirstOrDefault();
                if (agreement == null)//create agreement if does not exist.
                {
                    string Sender = ConfigurationManager.AppSettings["Sender"] ?? "SenderPartyName";
                    string Receiver = ConfigurationManager.AppSettings["Receiver"] ?? "ReceiverPartyName";
                    var OneWayAgreement = agreement.GetOnewayAgreement(Sender, Receiver); 
                    var firstBatch = OneWayAgreement.GetBatches()[0]; // first batch config is used as template
                    var newBatch = OneWayAgreement.CreateBatch(BatchName);
                    FilterPredicate predicate = firstBatch.GetFilterPredicate();
                    predicate.Groups[0].Statements[0].Value = "FilterRHS";
                    newBatch.SetFilterPredicate(predicate);
                    newBatch.SetReleaseCriteria(firstBatch.GetReleaseCriteria());
                    tmpCtx.AddToBatchDescriptions(newBatch);
                    tmpCtx.SaveChanges();
// this SQL insert is required to Activate the batch config
                    using (var cmd = new SqlCommand(@" INSERT INTO [dbo].[PAM_Control]
                                                                       ([EdiMessageType]
                                                                       ,[ActionType]
                                                                       ,[ActionDateTime]
                                                                       ,[UsedOnce]
                                                                       ,[BatchId]
                                                                       ,[BatchName]
                                                                       ,[SenderPartyName], ReceiverPartyName, AgreementName)
                                                                    SELECT 0 as EDIMessageType
                                                                  ,'EdiBatchActivate' as 'ActionType'
                                                                  ,GetDate() as 'ActionDateTime'
                                                                  ,0 as 'UsedOnce' 
                                                                  ," + newBatch.Id + @" as [BatchId]
                                                                  ,'" + BatchName + @"' as [BatchName], '"+ Sender +@"', '"+
 Receiver +@"', '" + AgreementName + "'", new SqlConnection(builder.ConnectionString)))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
                tmpCtx.Dispose();
Posted in BizTalk, C#. Tags: , . Leave a Comment »

BizTalk EDI: AK2 missing from 997 ACK

 

We can correlate a 997 with corresponding EDI request message by looking at AK2 segment (AK2:02 = ST02 in request), but by default BizTalk generates AK2 only for failed transactions, thus makes it difficult to correlate 997 with corresponding request in Async setup. To bring AK2 in 997, enable from Parties configuration

SNAGHTML16ab7ff

Posted in BizTalk. 1 Comment »

BizTalk: ‘Unspecified error’ when starting Host Instance

 

I was stuck with this error when starting BizTalk host instances after database connectivity was lost and resumed,

SNAGHTML4aab330

event log:

A failure occurred when executing a Windows service request.
 
Service request: Start
 
BizTalk host name: BizTalkServerApplication
Windows service name: BTSSvc$BizTalkServerApplication
 
Additional error information:
Error code: 0x80131604
Error source: mscorlib
Error description: Exception has been thrown by the target of an invocation.

 

one of possible reasons (among other is a reported issue in BizTalk – and MS provides CU for it) is a new section in BizTalk config file that is not declared in machine.config. In my case, I added a new config section to define appSettings configuration keys  – but I forgot to update machine.config first

<system.configuration> 

</system.configuration>