BizTalk and SQL: Alternatives to the SQL receive adapter. Using Msmq to receive SQL data
- by Leonid Ganeline
If we have to get data from the SQL database, the standard way is to use a receive port with SQL adapter.
SQL receive adapter is a solicit-response adapter. It periodically polls the SQL database with queries. That’s only way it can work. Sometimes it is undesirable. With new WCF-SQL adapter we can use the lightweight approach but still with the same principle, the WCF-SQL adapter periodically solicits the database with queries to check for the new records.
Imagine the situation when the new records can appear in very broad time limits, some - in a second interval, others - in the several minutes interval. Our requirement is to process the new records ASAP. That means the polling interval should be near the shortest interval between the new records, a second interval. As a result the most of the poll queries would return nothing and would load the database without good reason. If the database is working under heavy payload, it is very undesirable.
Do we have other choices? Sure. We can change the polling to the “eventing”.
The good news is the SQL server could issue the event in case of new records with triggers. Got a new record –the trigger event is fired. No new records – no the trigger events – no excessive load to the database.
The bad news is the SQL Server doesn’t have intrinsic methods to send the event data outside. For example, we would rather use the adapters that do listen for the data and do not solicit. There are several such adapters-listeners as File, Ftp, SOAP, WCF, and Msmq. But the SQL Server doesn’t have methods to create and save files, to consume the Web-services, to create and send messages in the queue, does it?
Can we use the File, FTP, Msmq, WCF adapters to get data from SQL code?
Yes, we can. The SQL Server 2005 and 2008 have the possibility to use .NET code inside SQL code. See the SQL Integration.
How it works for the Msmq, for example:
· New record is created, trigger is fired
· Trigger calls the CLR stored procedure and passes the message parameters to it
· The CLR stored procedure creates message and sends it to the outgoing queue in the SQL Server computer.
· Msmq service transfers message to the queue in the BizTalk Server computer.
· WCF-NetMsmq adapter receives the message from this queue.
For the File adapter the idea is the same, the CLR stored procedure creates and stores the file with message, and then the File adapter picks up this file.
Using WCF-NetMsmq adapter to get data from SQL
I am describing the full set of the deployment and development steps for the case with the WCF-NetMsmq adapter.
Development:
1. Create the .NET code: project, class and method to create and send the message to the MSMQ queue.
2. Create the SQL code in triggers to call the .NET code.
Installation and Deployment:
1. SQL Server:
a. Register the CLR assembly with .NET (CLR) code
b. Install the MSMQ Services
2. BizTalk Server:
a. Install the MSMQ Services
b. Create the MSMQ queue
c. Create the WCF-NetMsmq receive port.
The detailed description is below.
Code
.NET code
…
using System.Xml;
using System.Xml.Linq;
using System.Xml.Serialization;
//namespace MyCompany.MySolution.MyProject – doesn’t work. The assembly name is MyCompany.MySolution.MyProject
// I gave up with the compound namespace. Seems the CLR Integration cannot work with it L. Maybe I’m wrong.
public class Event
{
static public XElement CreateMsg(int par1, int par2, int par3)
{
XNamespace ns = "http://schemas.microsoft.com/Sql/2008/05/TypedPolling/my_storedProc";
XElement xdoc =
new XElement(ns + "TypedPolling",
new XElement(ns + "TypedPollingResultSet0",
new XElement(ns + "TypedPollingResultSet0",
new XElement(ns + "par1", par1),
new XElement(ns + "par2", par2),
new XElement(ns + "par3", par3),
)
)
);
return xdoc;
}
}
////////////////////////////////////////////////////////////////////////
…
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.Transactions;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
public class MsmqHelper
{
[Microsoft.SqlServer.Server.SqlProcedure]
// msmqAddress as "net.msmq://localhost/private/myapp.myqueue";
public static void SendMsg(string msmqAddress, string action, int par1, int par2, int par3)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
NetMsmqBinding binding = new NetMsmqBinding(NetMsmqSecurityMode.None);
binding.ExactlyOnce = true;
EndpointAddress address = new EndpointAddress(msmqAddress);
using (ChannelFactory<IOutputChannel> factory = new ChannelFactory<IOutputChannel>(binding, address))
{
IOutputChannel channel = factory.CreateChannel();
try
{
XElement xe = Event.CreateMsg(par1, par2, par3);
XmlReader xr = xe.CreateReader();
Message msg = Message.CreateMessage(MessageVersion.Default, action, xr);
channel.Send(msg);
//SqlContext.Pipe.Send(…); // to test
}
catch (Exception ex)
{
…
}
}
scope.Complete();
}
}
SQL code in triggers
-- sp_SendMsg was registered as a name of the MsmqHelper.SendMsg()
EXEC sp_SendMsg'net.msmq://biztalk_server_name/private/myapp.myqueue', 'Create', @par1, @par2, @par3
Installation and Deployment
On the SQL Server
Registering the CLR assembly
1. Prerequisites: .NET 3.5 SP1 Framework. It could be the issue for the production SQL Server!
2. For more information, please, see the link http://nielsb.wordpress.com/sqlclrwcf/
3. Copy files:
>copy “\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll” “\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 \Microsoft.Transactions.Bridge.dll”
If your machine is a 64-bit, run two commands:
>copy “\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll” “\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0 \Microsoft.Transactions.Bridge.dll”
>copy “\Windows\Microsoft.net\Framework64\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll” “\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 \Microsoft.Transactions.Bridge.dll”
4. Execute the SQL code to register the .NET assemblies:
-- For x64 OS:
CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Xml.Linq] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll' WITH permission_set = unsafe
-- For x32 OS:
--CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set = unsafe
5. Register the assembly with the external stored procedure:
CREATE ASSEMBLY [HelperClass] AUTHORIZATION dbo FROM ’<FilePath>MyCompany.MySolution.MyProject.dll' WITH permission_set = unsafe
where the <FilePath> - the path of the file on this machine!
6. Create the external stored procedure
CREATE PROCEDURE sp_SendMsg
(
@msmqAddress nvarchar(100),
@Action NVARCHAR(50),
@par1 int,
@par2 int,
@par3 int
)
AS EXTERNAL NAME HelperClear.MsmqHelper.SendMsg
Installing the MSMQ Services
1. Check if the MSMQ service is NOT installed.
To check:
Start / Administrative Tools / Computer Management,
on the left pane open the “Services and Applications”,
search to the “Message Queuing”. If you cannot see it, follow next steps.
2. Start / Control Panel / Programs and Features
3. Click “Turn Windows Features on or off”
4. Click Features, click “Add Features”
5. Scroll down the feature list; open the “Message Queuing” / “Message Queuing Services”; and check the “Message Queuing Server” option
6. Click Next; Click Install; wait to the successful finish of the installation
Creating the MSMQ queue
We don’t need to create the queue on the “sender” side.
On the BizTalk Server
Installing the MSMQ Services
The same is as for the SQL Server.
Creating the MSMQ queue
1. Start / Administrative Tools / Computer Management,
on the left pane open the “Services and Applications”,
open the “Message Queuing”, and open the “Private Queues”.
2. Right-click the “Private Queues”; choose New; choose “Private Queue”.
3. Type the Queue name as ’myapp.myqueue'; check the “Transactional” option.
Creating the WCF-NetMsmq receive port
I will not go through this step in all details. It is straightforward.
URI for this receive location should be 'net.msmq://localhost/private/myapp.myqueue'.
Notes
· The biggest problem is usually on the step the “Registering the CLR assembly”. It is hard to predict where are the assemblies from the assembly list, what version should be used, x86 or x64. It is pity of such “rude” integration of the SQL with .NET.
· In couple cases the new WCF-NetMsmq port was not able to work with the queue. Try to replace the WCF- NetMsmq port with the WCF-Custom port with netMsmqBinding. It was working fine for me.
· To test how messages go through the queue you can turn on the Journal /Enabled option for the queue. I used the QueueExplorer utility to look to the messages in Journal. The Computer Management can also show the messages but it shows only small part of the message body and in the weird format. The QueueExplorer can do the better job; it shows the whole body and Xml messages are in good color format.
