Share via

BizTalk Server: Migrating Business Rules Engine to Azure

  • Article

 

1  Introduction

At the time of writing this article there is no equivalent service in Azure similar to the Microsoft Business Rules Engine (BRE) that ships with BizTalk Server. However, when Logic Apps was released to public preview (back in 2014) there was: the BizTalk Rules API App (amongst other BizTalk services). This was not simply a repackaged BizTalk Server BRE for the cloud but a redevelopment. It lacked database and .NET facts (though these were slated for future implementation), which made some ruleset migrations from the BizTalk Server BRE difficult. In 2016 Logic Apps moved away from using the Public Preview API Apps to using API Apps v2 and in January 2017 the public preview API Apps were deprecated, which unfortunately included the BizTalk Rules API App.

Even though the #1 feedback from users to the Logic App Team is for a Business Rules Engine, the request has remained under review since 2016. But the community has not stood idly by waiting to see what transpires and have, instead, come up with several innovate solutions to address this need. This article first explores the current alternatives available to the developer in migrating business rules from BizTalk Server to Azure Logic Apps. Some of these have been suggested and developed by the community in blog posts and articles, which have all been brought together here as a single reference. After looking at the alternative solutions this article goes on to explain in detail the steps required in moving the BRE to Azure.

This article assumes the reader is familiar with BizTalk Server, the BizTalk Server BRE and Azure Functions, Hybrid Connections, SQL Server and Logic Apps.

 

2  Overview

While the BRE is shipped with BizTalk Server (all versions including the Branch edition) it is a separate product, even though it is only ever distributed with BizTalk Server, it is possible to install the BRE without installing BizTalk Server itself.

Over the years BizTalk Server developers have found many uses for the BRE, from using it as a simple configuration store to a sophisticated inferencing and reasoning engine as at was designed (whether this demonstrates the versatility of the tool, or merely invites a debate on the use of the correct tool to solve a problem, remains an open question).

Consequently, given the variety of uses of the BRE, there are several different approaches that can be taken when migrating BRE rules. The three main considerations are:

  1. Is rule externalisation the only requirement or is a more complex rule engine required (inferencing etc.);
  2. What effort, if any, is required for the refactoring of a ruleset;
  3. What volume of rulesets is to be migrated;

There are, of course, other considerations such as the authoring of new rules, rule versioning & deployment and any new licensing costs. But in the main the above three will be the key drivers for determining a migration strategy.

 

3  Alternatives for Migrating Business Rules to Azure

This section considers the current options available for migrating BizTalk Server business rules to Azure.

Business Rules Using Integration Account Maps

Liquid Templates

One way in which business rules can be externalised in Logic Apps is to Liquid templates. An in-depth discussion, with examples, is given here. Additionally, the article presents a generic BRE to dynamically invoke a particular Liquid template. The process flow is:

Logic App >>> Liquid Transform Action >>> Integration Account >>> Liquid Map

Things to consider in this approach:

  • The DotLiquid implementation of Liquid Templates may not have all the necessary Control Flow tags and Filters required for your business rules;
  • Use of the Liquid Templates requires a Logic App Integration Account;
  • While useful for externalizing business rules this approach is not suitable more complex rules;
  • Migrating BRE policies will require refactoring to use this approach.

XSL Transforms

In a similar manner to a Liquid Template an XSLT map can also be used to externalize business rules. The process flow is:

Logic App >>> XML Transform Action >>> Integration Account >>> XSLT Map [ >>>  .NET Script (XSLT 1.0 only)]

Things to consider in this approach:

  • XSLT has more logical functions than Liquid Templates and can accept parameters;
  • XSLT 1.0 also supports embedded scripting (JavaScript, VisualBasic, or CSharp), and can call external assemblies uploaded to the Integration Account;
  • Use of the XSL Transform requires a Logic App Integration Account;
  • While useful for externalizing business rules this approach is not suitable more complex rules;
  • Migrating BRE policies will require refactoring to use this approach.

 

Business Rules as an Azure Function

Azure Functions Only

Azure Functions can be used to define and execute the Rules Logic. and the Functions are invoked over an HTTP. The process flow is:

Logic App >>> Azure Functions Action >>> Azure Function

Things to consider in this approach:

  • Can call external assemblies, connect to databases etc.;
  • Can write more complex rules than liquid & XSLT maps but not complex rules that use forward-chaining etc.;
  • Migrating BRE policies will require refactoring to use this approach.

Azure Functions with NRules

NRules is an open source production rules engine for .NET, based on the Rete matching algorithm. Rules are authored in C# using an internal domain-specific language (DSL). The process flow is:

Process flow: Logic App >>> Azure Functions Action >>> Azure Function >>> NRules rules engine

Things to consider in this approach:

  • Can call external assemblies, connect to databases etc.;
  • Can write complex rules that use forward-chaining etc.;
  • Migrating BRE policies will require refactoring to use this approach.

 

Business Rules with an On-premise Data Gateway

Logic App BizTalk Connector

The BizTalk Server BRE can be used to define rules in Rules Editor and deploy them to a BizTalk Server installation. Using the BizTalk Server Logic App adapter and Logic App BizTalk Connector we can connect to BizTalk Server through a Hybrid Connection. An orchestration can receive the message, use a Call Rule shape to execute a Policy and return the result to the Logic App. The process flow is:

Logic App >>> BizTalk Connector >>> Hybrid Connection >>> On-Premise BizTalk Server 2016 End Point >>> BizTalk Application >>> Orchestration Call Rule shape

Things to consider in this approach:

  • Rule refactoring is not required;
  • There is still a dependency on an on-premise BizTalk Server installation;

Logic App HTTP+Swagger Connector

As with the Logic App BizTalk Connector, BizTalk server BRE can be used to define rules in Rules Editor and deploy them to a BizTalk Server installation. Using the HTTP+Swagger Connector, a Web API (e.g. BRE Data Services) can be accessed through a Hybrid Connection. There exists an open source Rest API that can be utilized. The process flow is:

Logic App >>> HTTP+Swagger Connector >>> Hybrid Connection >>> On-Premises Web API (BRE Data Services) >>> BRE .NET Call

Things to consider in this approach:

  • Rule refactoring is not required;
  • There is still a dependency on an on-premise BizTalk Server BRE only installation;

 

Business Rules with an Azure VM

This option is like that of the two provided in Business Rules with an On-premise Data Gateway above. A BizTalk Server VM instance can be configured in Azure. The process flow is the same in both cases.

 

Things to consider in this approach:

  • There is still a dependency on BizTalk Server and/or the BizTalk Server BRE;
  • Allows a move from an on-premise installation to a Cloud installation;
  • Rule refactoring is not required;
  • Has the option of a Pay as you go pricing model.

 

Business Rules with a Third-Party Rules Engine

On-premise Business Rules Engine

There are many third-party rules engine that can be deployed on-premise. So long as the engine exposes a callable interface, or one can be developed for the engine then, with a Hybrid Connection, rules can be executed. The process flow is:

Logic App >>> HTTP+Swagger Connector >>> Hybrid Connection >>> On-Premises Web API >>> Third-party Rules Engine

Things to consider in this approach:

  • Able to migrate complete off BizTalk Server;
  • Does not have to be a Windows compatible rules engine;
  • Licensing costs of the third-party rule engine could be close to those of BizTalk Server;
  • Migrating BRE policies will require refactoring to use this approach.

 

Cloud Business Rules Engine

Just as with on-premise there are many third-party rules engine that can be deployed in the cloud where rules can be authored and executed. The process flow is: 

Logic App >>> HTTP+Swagger Connector >>> Secure Connection >>> Third-party Rules Engine Web API >>> Third-party Rules Engine

Things to consider in this approach:

  • Able to migrate complete off BizTalk Server;
  • Licensing costs of the third-party rule engine could be close to those of BizTalk Server;
  • Depending where the rules engine is hosted could introduce latency;
  • Migrating BRE policies will require refactoring to use this approach.

 

4  Migrating the Business Rules Engine to Azure

In this section we consider the migration of the Business Rules Engine (BRE), included with BizTalk Server, to Azure. The BRE is built upon a common rule processing foundation called the Business Rules Framework (BRF), which consists of five parts: storage, modelling, deployment, administration and execution. We’ll look at how each of these five parts can be migrated to Azure and, in the process, remove any dependencies on BizTalk Server components.

Rule Storage

BRE rules are store centrally in a SQL Server database (by default named BizTalkRuleEngineDb). The first step, then, is to migrate this database to Azure SQL Server. There are two ways to do this:

Create an empty rules database

An empty rules database can be created in Azure manually with the following steps.

  1. Create the BizTalkRuleEngineDb database in the Azure Portal and then connect to Azure SQL Server database using SQL Server Management Studio (SSMS);
  2. Locate and execute the script BTS_RulesEngine_Schema.sql (C:\Program Files (x86)\Common Files\Microsoft BizTalk\Schema) in SSMS;
  3. Locate the script BTS_RulesEngine_Logic.sql (in the same folder as step 2). Edit the script and remove the creation of the SQL job Rules_Database_Cleanup_BizTalkRuleEngineDb (at the end of the file). Save and execute the script in SSMS;

Migrate an existing rules database

Alternatively, the database can be migrated using the Data Migration Assistant. The migration tool flags up three ‘Migration Blockers’:

  1. “Database users mapped with Windows authentication (integrated security) not supported in Azure SQL Database.” This is a reference to the five BizTalk groups: BizTalk Application Users, BizTalk Isolated Host Users, BizTalk Server Administrators, BizTalk Server B2B Operators and BizTalk Server Operators;
  2. "References found to system stored procedures that are not available in Azure SQL Database". These system stored procedures (sp_grantlogin, sp_helprotect, sp_helprolemember, sp_revokedbaccess & sp_helprolemember) are used by stored procedures: adm_AddLoginUser, adm_ChangeRolePrivForUser, adm_IsMemberOfRole, adm_RemoveLoginUser, adm_RemoveRole;
  3. “Cross database queries using three- or four-part names not supported in Azure SQL Database.” Used in stored procedure adm_AddLoginUser.

All three of these errors relate to stored procedures called during the BizTalk Server Configuration process to configure AD group and user access to the database. They are not called anytime after configuration (except when unconfiguring).

BRE rules also get cached by a Windows Service called the Rule Engine Update (REU) service, which acts as a local store of deployed rules. This service is not a necessity for rule execution, though without it the RuleEngine Policy class cannot be used and hence some code refactoring may be required as we’ll see in the next section.

Rule Modeling

The BRF defines several object models (vocabulary, rules store and rule set) to support the creation and manipulation of rules through custom code or though the Business Rule Composer (BRC) authoring tool. In this step all dependencies on any BizTalk Server assemblies need to be removed.

Using the Business Rule Composer

The BRC is installed during the BizTalk Server installation process if the Business Rules Components (in Additional Software) is selected. It can also be distributed to non-BizTalk clients for the purposes of development and testing. The BRC can still be used to connect to the Azure SQL rules database by connecting using SQL Server authentication. Rules can be created, saved and published. However, they cannot be deployed because the Deploy class uses SSPI authentication and any attempt to deploy through the BRC results in the error “Error encountered while connecting to database: "Initial Catalog=BizTalkRuleEngineDb;Data Source={AzureSQLServer}.database.windows.net;Integrated Security=SSPI;".”. In addition, the BRC will incorrectly show a policy version as still published even if it has been deployed. This is due a failure of the method IsRuleSetDeployed (RuleSetDeploymentDriver class) which tries to query the rules database using SSPI authentication and subsequently returns false.

Programmatic rules with .NET assemblies

Two .NET assemblies are provided for executing policies programmatically: Microsoft.RuleEngine.dll and Microsoft.BizTalk.RuleEngineExtensions.dll. The latter assembly provides additional components for use within BizTalk Server and the BRC. As the intention is to migrate from BizTalk Server there is a need to remove references to BizTalk Server assemblies. This requires two types of changes:

  1. Code needs to be refactored to solely reference the Microsoft.RuleEngine.dll assembly. Generally, this will involve replacing the RuleSetDeploymentDriver references;
  2. Any code that creates a Policy object also needs to be refactored since, as previously mentioned, this class utilizes the REU service to retrieve the rule sets. An example of how to do this is given here, but in our case a slight change is needed (to remove the Microsoft.BizTalk.RuleEngineExtensions dependency): 

//Get access to the SQL rule store 

Microsoft.BizTalk.RuleEngineExtensions.RuleSetDeploymentDriver dd; 

dd = new Microsoft.BizTalk.RuleEngineExtensions.RuleSetDeploymentDriver();

SqlRuleStore sqlRuleStore = (SqlRuleStore)dd.GetRuleStore(); 

 

>>>

//Get access to the SQL rule store 

SqlRuleStore sqlRuleStore = new Microsoft.RuleEngine.SqlRuleStore("tcp:{AzureSqlServer}.database.windows.net,1433; Initial Catalog=BizTalkRuleEngineDb;Persist Security Info=False;User ID={user};Password={password};Encrypt=True;");

At the end of these steps there should be no code dependencies on the Microsoft.BizTalk.RuleEngineExtensions namespace (or indeed any Microsoft.BizTalk.xxxxxxx namespace).

Rule Deployment

The Business Rules Engine Deployment Wizard cannot be used for deployment as it uses SSPI authentication. The solution to deploying (and undeploying) is to use a modified version of the rules deployment tool that comes with the BizTalk Deployment Framework (BTDF). Unfortunately, this tool (DeployBTRules) references the assembly Microsoft.BizTalk.RuleEngineExtensions.dll and since we want to remove all BizTalk Server references, we need to refactor several methods (and an enumeration) from this assembly, incorporating them into DeployBTRules. The methods to copy are:

  • AddAuditParameters(SqlCommand cmd)
  • Deploy(RuleSetInfo ruleSetInfo)
  • Deploy(SqlConnection rsConnection, SqlTransaction rsTransaction, RuleSetInfo ruleSetInfo, RuleSet ruleSet)
  • ImportAndPublishFileRuleStore(string fullFileName)
  • IsRuleSetDeployed(RuleSetInfo ruleSetInfo)
  • Undeploy(RuleSetInfo ruleSetInfo)
  • Undeploy(SqlConnection rsConnection, SqlTransaction rsTransaction, RuleSetInfo ruleSetInfo)

A new connection string element needs adding to the app.config file of DeployBTRules which will allow us to specify a SQL Server connection (to the Azure SQA Server database). Methods that connect to the rule store will need updating to use this connection string, in most cases this will be by passing the connection to a SqlRuleStore()constructor rather than calling the RuleSetDeploymentDriver GetRuleStore()method.

The source code of an updated tool is available here. There have been no changed to the interface:

 

Rule Administration

 

Management

Policies are commonly managed through the BizTalk Server Administration Console (BSAC), though there are alternatives. Putting aside the BSAC functions to deploy/undeploy, which were dealt with in Rule Deployment, there will still be a need to perform other management functions such as checking the status of a rule, listing deployed rules and vocabularies, setting rule tracking options, exporting rules and vocabularies etc. Some of the required functions can be obtained through SQL queries but, in this instance, the easiest solution is to write a tool (ManageBTRules), similar to DeployBTRules, that utilises the Microsoft.RuleEngine namespace to perform the required functions. The tool can be downloaded here. The functionality available is displayed below:

Security

There are no Windows user groups for the Business Rule Engine, only individual accounts. The Rule Engine database has been configured with three database roles. Their names and authorization permissions are defined in the table below:

Role

Permissions

RE_OPERATORS

Can access the rules store in read-only mode for information.

RE_HOST_USERS

Same as RE_OPERATORS but can also save, publish, update and delete policies.

RE_ADMIN_USERS

Same as RE_HOST_USER but can also amend database authorization, rename artefacts, update tracking configuration.

Users can obviously be added to these database groups. The Rule Engine database also provides a fine-grained authorisation on individual artefacts. This is disabled by default, but members of RE_ADMIN_USERS can enable this by executing a stored procedure as  follows:

EXEC re_setconfig 'authorization', 1 -- 0=disabled, 1=enabled

To manage artefact authorisation administrators create authorisation groups (stored proc.: re_saveauthgroup) and then associates these groups with specific artefacts (stored proc.:  re_addauthgroupforartifact). For a detailed description see Micrsoft BizTalk Server 2010 Unleashed p.501.

 

Maintenance

When the rules engine is configured with the BizTalk Server Configuration application a SQL Server maintenance job (Rules_Database_Cleanup_BizTalkRuleEngineDb) is created. This job removes deployment and audit history and is configured, by default, to run every hour. Its only step is to execute the stored procedure re_cleanup_database. With Azure SQL jobs are only available in a Manage Instance. However, there are alternative schedulers in Azure, Elastic Jobs and Automation (note that the Scheduler is being deprecated in September 2019). A good walkthrough on how to execute an Azure SQL stored procedure using Automation is given here and the Power Shell script to run is given in Appendix A.

 

Rule Execution

Rule execution can be performed in two ways: calling an Azure Function or an XSLT 1.0 map.

Azure Function

The Microsoft.RuleEngine.dll assembly needs uploading to the function bin folder and referenced. If we use the previous example, the C# code will look like this (assuming the XML document is passed in the body and the policy and xml document type are passed in the headers):

#r "D:\home\site\wwwroot\CallRuleEngine\bin\Microsoft.RuleEngine.dll"
using System.Configuration;
using System.Net;
using System.Xml;
using Microsoft.RuleEngine;
 
public static  async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
  string  body = await req.Content.ReadAsStringAsync();
   string policy = req.Headers.GetValues("policy").First();
  string  typedXmlDoc = req.Headers.GetValues("typedXmlDoc").First();
  string  rulesDBConn = ConfigurationManager.ConnectionStrings["RulesEngineDBConnection"].ConnectionString;
 
  XmlDocument xd = new  XmlDocument();  
  xd.LoadXml(body);
  TypedXmlDocument txd = new  TypedXmlDocument(typedXmlDoc, xd);
  SqlRuleStore srs = new  SqlRuleStore(rulesDBConn);
  RuleSetInfoCollection rsic = srs.GetRuleSets(policy, RuleStore.Filter.All);
  RuleSet rs = srs.GetRuleSet(rsic[0]);
  RuleEngine re = new  RuleEngine(rs);
  re.Assert(txd);
  re.Execute();
 
  return  req.CreateResponse(HttpStatusCode.OK, txd.Document.OuterXml);
}

 

XSLT 1.0 Map

The Microsoft.RuleEngine.dll assembly first needs uploading to the Integration Account Assemblies blade. Using the same example, the XSLT code will look like this:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl userCSharp" version="1.0" xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">
  <xsl:output omit-xml-declaration="yes" media-type="application/text" method="text" version="1.0" />
  <xsl:param name ="RulesEngineDBConnection" />
  <xsl:param name ="Policy" />
  <xsl:param name ="TypedXmlDoc" />
  <xsl:template match="/">
  <xsl:value-of select ="userCSharp:Execute(., $RulesEngineDBConnection, $Policy, $TypedXmlDoc)" />
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="userCSharp">
  <msxsl:assembly name="Microsoft.RuleEngine, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  <msxsl:using namespace="System.Net" />
  <msxsl:using namespace="System.Xml" />
  <msxsl:using namespace="Microsoft.RuleEngine" />
  <![CDATA[
  public string Execute(XPathNodeIterator nodes, string rulesEngineDBConnection, string policy, string typedXmlDoc)
  {
    nodes.MoveNext();
  XmlDocument xd = new XmlDocument();  
  xd.LoadXml(nodes.Current.OuterXml);
  TypedXmlDocument txd = new TypedXmlDocument(typedXmlDoc, xd);
  SqlRuleStore srs = new SqlRuleStore(rulesEngineDBConnection);
   RuleSetInfoCollection rsic = srs.GetRuleSets(policy, RuleStore.Filter.All);
  RuleSet rs = srs.GetRuleSet(rsic[0]);
  RuleEngine re = new RuleEngine(rs);
  re.Assert(txd);
  re.Execute();
  return txd.Document.OuterXml;
  }   
  ]]>
  </msxsl:script>
</xsl:stylesheet>

 

 

5  Further Considerations

There are few points to consider when migrating the BizTalk Server BRE to Azure using the technique described in the previous section:

 

Static Support

It is not possible to set the StaticSupport registry key to invoke static member of a class as writing to the registry is not permitted in the Azure Function sandbox environments (see here). A workaround to this is to create an instance of the class and assert it to the rule engine. For example, in this walkthrough the source code can be modified to:

SqlConnection cn = new  SqlConnection("Data Source=(local);Initial Catalog=TestDB;Integrated Security=SSPI");
cn.Open();
DataConnection dc = new  DataConnection("TestDB", "PO", cn);
 
POUtility pou = new  POUtility();
 
SqlRuleStore srs = new  SqlRuleStore("Data Source=(local);Initial Catalog=BizTalkRuleEngineDb;Integrated Security=SSPI");
RuleSetInfoCollection rsic = srs.GetRuleSets("ProcessPurchaseOrderDbNet", RuleStore.Filter.All);
RuleSet rs = srs.GetRuleSet(rsic[0]);
RuleEngine re = new  RuleEngine(rs);
 
re.Assert(new object[] { dc, pou });
re.Execute();
 
dc.Update();
cn.Close();

 

Tuning Parameters

There are several tuning parameters (e.g. TranslationTimeout) that can be configured in the registry, but, as mentioned above, editing registry keys is not permitted and so these are unavailable for use.

No Long-Term Facts

The Rule Engine Update (REU) service cannot be migrated and consequently the Policy class, which is depended on this service, cannot be used. This means that there is no longer any notion of long-term facts; there are only short-term facts that are retracted from the rule engine’s working memory after the policy executes.

Custom Rules Store

It is possible to implement a custom rule store rather than retrieving them from a SQL Server rule store (see Microsoft BizTalk Server 2010 Unleashed: Implementing Custom Rule Store Components, p.628-633).

Licencing Requirements

While the BRE is a separate product, in a sense nothing whatever to do with BizTalk Server, it is not a separate SKU. It is only distributed with BizTalk Server and, as such, licensing requirements for BizTalk Server must be met even if only the BRE is used. While this may sound prohibitive, a cursory piece of market research soon reveals such a cost is comparable with alternative rule engine license costs.

 

6  Summary

This article has provided an overview of the different migration strategies available for migrating business rules from the BizTalk Server BRE to Azure. It has also given a step by step guide on how to migrate the core components of the BRE to Azure as another strategy.

Because of the many uses of the Rules Engine there are several choices available depending what functionality is most required. The table below gives a summary of each:

Migration Option

Rule Authoring

Rule Complexity

Rule Versioning

 Fact Types Supported

BRE Policy Refactoring Required?

Dependencies

XML

Database

.NET

Liquid Templates

Code

Simple

No

Yes

No

No

Yes

Azure Logic App Integration Account

XSLT Transformation

Code

Simple

No

Yes

No1

No

Yes

Azure Logic App Integration Account

Azure Functions

Code

medium
complexity

No

Yes

Yes

No

Yes

Azure Functions

Azure Functions with NRules

Code

Complex

No

Yes

Yes

No

Yes

Azure Functions, NRules

Web based third-party Rules Engine

GUI

Complex

Yes/No2

Yes

No

No

Yes

Third-party software

 

On-premise third-party Rules Engine

GUI/Code2

Complex

Yes/No2

Yes

Yes/No2

No

Yes

Third-party software, Azure Data Gateway

On-premise BizTalk Server 2016

GUI, Code

Complex

Yes

Yes

Yes

Yes

No

BizTalk Server, Azure Data Gateway

On-premise BizTalk Server BRE only

GUI, Code

Complex

Yes

Yes

Yes

Yes

No

BizTalk Server BRE only, Azure Data Gateway, BRE Data Services

BizTalk Server Azure VM

GUI, Code

Complex

Yes

Yes

Yes

Yes

No

Azure VM, BizTalk Server, Azure Data Gateway4

BRE Migration to Azure

GUI, Code

Complex

Yes

Yes

Yes

Yes

No3

Azure SQL Server, Azure Functions, Azure Automation5

1 It is possible in XSLT 1.0 (but not in XSLT 2.0/3.0) by utilizing the <msxsl:script> element.
2 Dependent on the third-party Rule Engine chosen.
3 Custom code executing a policy using the Policy.Execute() method will need modification.
4 Requires BizTalk Server 2016 if the Logic App BizTalk Connector is used.
5 If not using Managed Instance.

 
 

7  References

BizTalk Server BRE Overview

 

Programming with the BRE

 

Azure

 

BRE Alternatives

8  Appendix A

Azure Automation Power Shell script to run an Azure SQL Server stored procedure:

workflow Rules_Database_Cleanup

{
  [cmdletbinding()]
  param
  (
   # Fully-qualified name of the Azure DB server
  [parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string] $SqlServerName = "<DBSERVER>.database.windows.net",
 
  # Name of database to connect and execute against
   [parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string] $DBName = "<DBNAME>",
 
  # Name of stored procedure to be executed
  [parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string] $StoredProcName = "re_cleanup_database",
 
  # Credentials for $SqlServerName stored as an Azure Automation credential asset
  [parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [PSCredential] $Credential
  )
  inlinescript
  {
  Write-Output "========================================================================"
  Write-Output “JOB STARTING”
  Write-Output "========================================================================"
 
  # Setup variables
   $ServerName = $Using:SqlServerName
  $UserId = $Using:Credential.UserName
  $Password = ($Using:Credential).GetNetworkCredential().Password
  $DB = $Using:DBName
  $SP = $Using:StoredProcName
  
  Try 
  {
   # Create and Open the PowerShell Connection to the Database
  $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
  $DatabaseConnection.ConnectionString = “Data Source = $ServerName; Initial Catalog = $DB; User ID = $UserId; Password = $Password;”
  $DatabaseConnection.Open();  
   Write-Output “Connection to the Database is open.”
 
  # Create & Define command and query text
  $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
  $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
  $DatabaseCommand.Connection = $DatabaseConnection
  $DatabaseCommand.CommandText = $SP
  
  #Set up for return value that will drive the success or failure from the stored Procedure.
  $DatabaseCommand.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int") 
  $DatabaseCommand.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"
 
  Write-Output “Executing the Query: $SP”
  
  # Execute the query
  $OUT = $DatabaseCommand.ExecuteNonQuery()  | out-null
 
  $returnValue = $DatabaseCommand.Parameters["@ReturnValue"].Value
  Write-Output "Return Value: $ReturnValue"
  }
  Catch 
  {
  Write-Error -Message $_.Exception
  }
  Finally
  {
  # Close connection to DB
  $DatabaseConnection.Close()
  Write-Output "========================================================================"
  Write-Output “CONNECTION CLOSED”
  Write-Output “JOB COMPLETED”
  Write-Output "========================================================================"
  }
  }
}