woensdag 28 december 2011

Open Source BizTalk Projects in Codeplex

Open Source Projects

SDC Tasks Library
This is the latest version of the SDC Tasks for .NET 2.0. The SDC Tasks are a collection of MSBuild tasks designed to make your life easier. You can use these tasks in your own MSBuild projects. You can use them stand alone and, if all else fails, you can use them as sample.
Deployment Framework for BizTalk.
The Deployment Framework for BizTalk eliminates the pain associated with BizTalk application deployments, and goes far beyond BizTalk’s out-of-the-box deployment functionality. It also includes additional tools to enhance developer productivity, such as binding file management.
BizUnit - Framework for Automated Testing of Distributed Systems
BizUnit enables automated tests to be rapidly developed. BizUnit is a flexible and extensible declarative test framework targeted that rapidly enables the automated testing of distributed systems, for example it is widely used to test BizTalk solutions. BizUnit is fully extensible. Its approach is to enable test cases to be constructed from generic reusable test steps, test cases are defined in XML which allows them to be auto-generated and also enables the ‘fixing up’ of Url’s for different environments, e.g. test, staging and production environments. Defining test cases in XML enables test cases to be auto-generated.
BizUnitExtensions And Tools
This project aims to provide some more test step libraries, tools and utilities to enhance the reach of BizUnit. Here you can find some enhancements/extensions to the steps in the base libraries (the enhancements being packaged in separate assemblies) , new steps, support applications, tutorials and other documentation to help you understand and use it.
BizUnit Designer
BizUnit Designer is a GUI that allows rapid creation of BizUnit test cases which can be used for unit testing or system testing distributed applications. The easy to use drag and drop user interface has minimal learning curve which will get you up and running with test cases in a matter of minutes.
BizTalk Sftp Adapter
This adapter was developed for a customer who needed to transfer files independent from any choice of platform. Much concern for security, along with already invested infrastructure, where the main reasons for choosing Sftp before other protocols like Ftps.
BizTalk Server Pipeline Component Wizard
A project meant to ease to development of pipeline components used within a BizTalk Server environment. Supports both C# and VB.NET
BizTalk Scheduled Task Adapter
An Biztalk Server 2004/2006 in-process receive adapter that executes a prescribed task on a daily, weekly or monthly schedule. The adapter is configured entirely within Biztalk, all config is stored within the SSODB and can be exported and imported via binding files.
BizTalk Server 2006 Documenter
Creates compiled help files for a given BTS 2006 installation. This tool can be run on an ad-hoc basis using the UI or from the command line as a post build/deploy task to create a compiled help file describing a BTS 2006 installation.
BizTalk Server 2006 TCPIP Adapter
TCPIP Adapter for BizTalk Server 2006 which supports all BTS message exchange patters and also provides a Duplex Pattern which is not supported out of box with BizTalk.
BizTalk 2006 Message Archiving Pipeline Component
A BizTalk 2006 & BizTalk 2006 R2 pipeline component which allows messages to be written to the file system for archiving. The component can be executed in either the Decode (Receive) or Encode (Send) stages and uses message context-properties to define the archive path.
BizTalk Adapter Wizard
Visual Studio wizard for creating custom BizTalk adapters. This wizard uses your input to create all of the necessary adapter framework code for a BizTalk custom adapter.
BizTalk Server 2006 Orchestration Profiler.
Creates CHM report files illustrating the level of coverage for specified BizTalk orchestrations. This tool can be run to gain a consolidated view of orchestration tracking data for a specified period of time to help developers get an idea of how their orchestrations are being processed and how much coverage they are getting in their testing.
BizTalk MsBuild Generator
This tool can be used to generate the MsBuild scripts required to implement a build process for a development environment that will build and deploy your code then allow you to test it.
BizTalk Software Factory
The BizTalk Software Factory supplies guidance for developing BizTalk applications. It is build using the February 2008 release of the Guidance Automation Extensions and the Guidance Automation Toolkit.
A set of FxCop rules for enforcing coding standards on BizTalk projects. First release includes configurable naming rules based on Scott Colestock's published conventions.
ShoBiz - BizTalk Documentation Tool for Sandcastle
BizTalk tool that generates Sandcastle documentation from BizTalk artifacts deployed into the databases. Promotes documentation as part of the solution lifecycle. Documentation structure mimics the Admin Console and extracts documentation comments from all sources.
PowerShell Provider BizTalk
A Provider for powershell that allows to administrate your BizTalk Server.
The GenerateTypedBamApi command line tool enables you to take a BAM Observation model represented as a Excel Spreadsheet and generate a set of strongly typed C# classes which you can then use to create and populate BAM Activities.
BizTalk Host Configurator per Application.
This tool will help you create (mangage) seperate BizTalk Host for your choosen BizTalk application. It will understand the adapters used inside the application, and creates InProcess and Isolated Host (if required).

dinsdag 13 december 2011

Ports that DBAs Need to Know

Here's a list of essential TCP/UDP port numbers that an administrator running SQL Server requires to know when configuring the firewall or otherwise.





Default Instance



Official socket allocated by IANA to Ms for SQL Server, this can be changed to anything above 1024.

Named Instance



As & what you assign in the dynamic port range. There have been changes since Win 6, see below for the available range.

DAC Default Instance



Remote connections through DAC are disabled unless turned on manually. For named instance ports other than 1434 are used.

SQL Browser / SQL Server Resolution Protocol



Used by an application level protocol SSRP on top of which the browser service runs.

It helps when connecting to non-hidden instances named instances. In such cases TCP port is dynamic (unless specified) & determined when the Database Engine starts. It’s not needed if all connections contain the port#. When uninstalling SQL 9.0 from a machine running SQL 8.0 check the existence of registry key IsListenerActive, because if it exists SSRP will fail to listen.

Denali Browser does not support sending information about SQL 8.0 instances.

Refer à http://msdn.microsoft.com/en-us/library/cc219750(v=PROT.10).aspx



Be cautious a malformed request to port 3882/tcp can cause DOS.

When communicating with remote SSIS port 135 is used & if it’s a SSIS package is running against a database server you need 1433 or as specified. Uses msdts1 protocol for service type msdts1.



2393, 2394, 2725

OLAP Services 7.0 used TCP ports 2393 & 2394. Though Ms has reserved UDP ports 2393 & 2394 those are not used by OLAP Services. Analysis Services uses TCP port 2725. For backward compatibility, Analysis Services uses TCP ports 2393 & 2394 when connected with an OLAP Services 7.0 client.




Standard port for the default instance of Analysis Services. User configurable;

Browser SSAS



Client connection requests for a named instance of Analysis Services that do not specify a port number are directed to SQL Server Browser.




Providing the remote desktop to a client or VDI keep your eyes open because the default encryption certificate (RSA pk stored in mstlsapi.dll), is there with widows base install. A Man-in-the-Middle (MITM) attack can intercept the exchange of RDP encryption information. Check here for safety ribbons secure RDP using Transport Layer Security http://technet.microsoft.com/en-us/library/cc782610%28WS.10%29.aspx

For 6.0 Network Level Authentication offers much stronger protection http://blogs.technet.com/askperf/archive/2008/02/16/ws2008-network-level-authentication-and-encryption.aspx

Dynamic Port Range

To comply with Internet Assigned Numbers Authority recommendations, Ms has increased the dynamic client port range for outgoing connections. Since the release of Win 6.0 new default port range is 49152 – 65535 which was earlier 1025 through 5000.

Service Broker

User configurable; there is no default port. BOL conventional configuration uses TCP 4022.




When used with HTTP forming HTTPS, it provides an encrypted communication channel.

HTTP endpoint

Used when connecting through a url, this is user configurable; this can be customized while creating an endpoint. Port 80 for CLEAR_PORT traffic & 443 for SSL_PORT traffic.

HTTPS endpoint



Default instance running over an HTTPS endpoint, used for a connection through url which used SSL.


3260, 860

SQL Agent File Copy


Agent to copy backup files to the shared folder on the standby server.

137, 138, 139, 445

File copy on UNC shares.

SQL Debugger



Exception for IPsec traffic might also require you to set an inbound rule for 500 & 4500 if IPsec is used for network communication.

After opening port 135 include the applications Visual Studio à Devenv.exe / Management Studio à ssms.exe.

Database Mirroring

User configurable; there is no default port. While setting multiple instances be cautious to not to break the quorum. BOL conventional configuration uses TCP 7022.




For push transactional replication a working 1433 between distributor & subscriber is all you need, however in pull subscriptions few other ports are needed; when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives to copy down scripts.

FTP (21) can be used initially to transfer schema & data over the internet; it can also use HTTP (80) or File & Print Sharing (ports 137,138, 139).

You can put merge replication to use WEB synchronization using port 80 or encrypted 443. Replication uses the IIS endpoint, when syncing over HTTP (80 by default but configurable), however IIS process connects to SQL Server through standard ports.

Keep in mind when synchronizing over the Web using FTP, there is no transfer between subscriber & IIS, it’s all amid IIS & the publisher.

Cluster Service



Cluster services control & manage the cluster database. Like the Heartbeat process - Cluster network driver (Clusnet.sys) performs intra-node communication between each node of the cluster by periodically exchanging sequenced, unicast/multicast UDP datagrams in the cluster. This determines whether all nodes are running correctly & network links are healthy. Generally this does not happens over the public network.

There are cases when the range of random available IP ports that the cluster service uses to initiate communication through RPCs is less than 100 ports & connection to the Cluster Admin fails (refer to 154596(http://support.microsoft.com/kb/154596/ ) ).

RPC – 135 / Cluster Network Drv – 3343 / SMB – 445 / NetBIOS – 139 / RPC 5000-5099 / 8011-8031

  • 135 (RPC endpoint mapper/DCOM), (RPC endpoint mapper over UDP).
  • For nodes running multiple services, ports 5000-5099 (or more) may be needed for remote RPC connectivity. If closed, error 1721 might occur when you connect to a remote cluster.
                 Cluster service requires at least 100 ports for communication through RPC. Count of ports available may get too low when other services like DNS, WINS, Ms SQL Server service & others are using some of the necessary ports.
  • Ports 8011-8031 must be open for internode RPC connectivity or the cluster log will indicate that a SPONSOR is not available. Again these errors occur because there are not enough ports available for RPC communication between a node trying to join the cluster & a node that can sponsor the new node.

Cluster Admin



Random Ports


Check dynamic port range





139 & 445.








Used by SSCM, it runs over DCOM (aka Network OLE) when accessing remote data. After initial connection DCOM randomly assigns a port for further communication where some tools may require a TCP port > 1024 (aka TCP high port) opened on the remote host.

IPsec traffic


500 & 4500

Should be set to allow ISAKMP traffic to be forwarded for both inbound & outbound filters.



Since NT 4, MSDTC has been performing as the transaction coordinator for components with COM & .NET architectures. Connected resources can be databases, message queues or file systems which may be distributed. Messages are sent on TCP 135 while the responses are on a dynamically assigned port.

Source: http://www.sqlservercentral.com/articles/networking/75481/

vrijdag 9 december 2011

BizTalk Server 2010 R2 announced

According to Microsoft the new release wil focus on 3 main themes:

  • Platform Support – Supporting new and current server and development platforms
  • Improved B2B – Aligning with industry standards and continuing to invest in performance improvements
  • Ready for the Cloud – Enabling customers to take advantage of the benefits of cloud computing

BizTalk Server 2010 R2 will be delivered approximately 6 months following the release of Windows Server 8. Release timing for Windows Server 8 is yet to be announced.

More info: BizTalk Server 2010 R2 - BizTalk Server Team Blog

donderdag 8 december 2011

7 Preventable Backup Errors

Grant Fritchey wrote a blog on some common errors in SQL Backups.

the 7 errors he describes are:
  • File Backups:
    .MDF and .LDF files have been backed up
  • Drive Space:
    the error message “Insufficient disk space for backup” or some similar message occurs
  • Transaction Log is Full:
    alert that a transaction cannot complete because there is insufficient space in the log
  • Backing Up Across the Network:
    backups are running so slowly that sometimes you get timeout errors and the logs fail
  • Only Have Differential Backups Available:
    error: The log or differential backup cannot be restored because no files are ready to roll forward
  • Breaking the Backup Chain:
    error: This differential backup cannot be restored because the database has not been restored to the correct earlier state
  • Restoring to the Wrong Server:
    this isn’t a backup issue per se, but it is an extremely common event

Original blog post: 7 Preventable Backup Errors

vrijdag 25 november 2011

dinsdag 22 november 2011

Cumulative update package 4 for BizTalk Server 2009

Microsoft released a new update package for BTS 2009. Cumulative update package 4 is now available here.

  • BizTalk Server EDI support: FIX: A TA1 interchange acknowledgement is not returned when you use a two-way receive port to receive EDI interchanges in BizTalk Server
  • BizTalk Server Design Tools: FIX: Visual Studio crashes when you build a project in BizTalk Server 2009 if message types are classes from a referenced .NET project
  • Microsoft BizTalk Server Adapters: FIX: "Error encountered while querying virtual server state for URL" error when you configure WSS adapter in BizTalk Server 2009

!! Important Note:
After you apply this cumulative update, the settings for the DTA Purge and Archive Job roll back to default. This may cause backlog in the BizTalk Tracking (BizTalkDTADb) database. It is recommended to re-configure the job. For more information, visit the following Microsoft Web site: How to Configure the DTA Purge and Archive Job

Error 0xC0002A1F in BizTalk installation

During the BizTalk installation I got the error code '0xC0002A1F' when configuring the installation using the Microsoft BizTalk Server Configuration Wizard. There wasn't any error description given, only the code 0xC0002A1F which of course didn't was of much help.

Turned out this error code meant the following:

Error Code: 0xC0002A1F, Cannot perform encryption or decryption because the secret is not available from the master secret server. See the event log for related errors.

This of course was much clearer. It was clear I needed to restore the Master Secret needed of the SSO (Single Sign-on).

After restoring the master secret, all worked well.

maandag 21 november 2011

Error when configuring the BizTalk runtime

I was installing BizTalk 2010 at home and ran into an error when configuring my BizTalk Server installation with the Microsoft BizTalk Server Configuration wizard. Configuring the BizTalk Runtime gave me following error:

The Microsoft Distributed Transaction Coordinator (MSDTC) may not be configured correctly. Ensure that the MSDTC service is running and DTC network access is allowed on the BizTalk, SQL and SSO Master servers. For more information, see "MSDTC Configuration settings required for BizTalk Server" in the BizTalk Server Help.

To solve this issue, I did the following:

Go to Component Services (Click Start, click Run, and type dcomcnfg to launch the Component Services Management console).
Next, Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties, as shown below.

Now Click the Security tab of the Local DTC Properties dialog.

If either the Mutual Authentication Required or the Incoming Caller Authentication Required configuration options are enabled then the client(s) computer account must be granted the Access this computer from the network user right. If the computer account for a client computer is not granted the Access this computer from the network user right, or is included in the Deny access to this computer from the network user right, then DTC communication between the client and server computer will fail.

These changes caused to give me following error:

New transaction cannot enlist in the specified transaction coordinator

The solution for this is to change the setting under Transaction Manager Communication to “No Authentication Required".
So the MSDTC Security configuration now looks like this:

woensdag 9 november 2011

Cumulatieve updatepakket 2 for BizTalk Server 2010

Microsoft released the Cumulatieve updatepakket 2 for BizTalk 2010

This cumulative update package for Microsoft BizTalk Server 2010 contains hotfixes for the BizTalk Server 2010 issues that were resolved after the release of BizTalk Server 2010.

More info: Cumulatieve updatepakket 2 for BizTalk Server 2010

Some neat tools for Biztalk / .NET developers

I bundled some handy free tools to help you develop and / or debug your (BizTalk) applications.


soapUI is a free and open source cross-platform Functional Testing solution, a tool for SOA testing.
soapUI allows you to easily and rapidly create and execute automated functional, regression, compliance, and load tests. In a single test environment, soapUI provides complete test coverage and supports all the standard protocols and technologies.



LINQPad lets you interactively query databases in a modern query language: LINQ.

LINQPad supports everything in C# 4.0 and Framework 4.0:
  • LINQ to Objects
  • LINQ to SQL and Entity Framework
  • LINQ to XML
  • Parallel LINQ



TestDriven.Net makes it easy to run unit tests with a single click, anywhere in your Visual Studio solutions. It supports all versions of Microsoft Visual Studio and it integrates with the best .NET development tools.



SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio. It contains a few upgrades to the SSMS IDE


deployment framework for biztalk

The Deployment Framework for BizTalk eliminates the pain associated with BizTalk application deployments, and goes far beyond BizTalk’s out-of-the-box deployment functionality. It also includes additional tools to enhance developer productivity, such as binding file management.



A set of FxCop rules for enforcing coding standards on BizTalk projects. First release includes configurable naming rules based on Scott Colestock's published conventions.



SmartSniff is a network monitoring utility that allows you to capture TCP/IP packets that pass through your network adapter, and view the captured data as sequence of conversations between clients and servers.



BizUnit is a flexible and extensible declarative test framework targeted that rapidly enables the automated testing of distributed systems, for example it is widely used to test BizTalk solutions. BizUnit is fully extensible. Its approach is to enable test cases to be constructed from generic reusable test steps, test cases are defined in XML which allows them to be auto-generated and also enables the ‘fixing up’ of Url’s for different environments, e.g. test, staging and production environments. Defining test cases in XML enables test cases to be auto-generated.



ReSharper is a renowned productivity tool that makes Microsoft Visual Studio a much better IDE.


dansharp xmlviewer

DanSharp XmlViewer is a lightweight XPath generation and testing tool, making it easy to generate and test XPath queries. It also allows for validation of Xml documents against schemas, display of Xml documents in tree-view format, and generation of BizUnit test cases.


bts terminator

A tool which allows for common biztalk problems to be addressed by scripts provided by the biztalk team. Replaces terminate.vbs with more functionality including the ability to suspend, resume and terminate by date range. Powershell script functionality provided for performance tuning. Integration with mbvcleanupscripts.xml from the messagebox viewer utility

Guideline on biztalkadminsblogging.com


DebugView is an application that lets you monitor debug output on your local system, or any computer on the network that you can reach via TCP/IP. It is capable of displaying both kernel-mode and Win32 debug output.


msbuild explorer extension

The MSBuild Extension Pack provides a collection of over 400 MSBuild Tasks, MSBuild Loggers and MSBuild TaskFactories.



ILSpy is the open-source .NET assembly browser and decompiler.


biztalk map documenter

The BizTalk Map Documenter stylesheet will transform a a BizTalk map into an HTML view containing the following information:
  • The node links between source and destination schemas.
  • All functoids used in the map are displayed with their parameters.
  • Chained functoids are displayed as nested “function” calls.
  • Labels
  • Constants
  • For HL7 schemas where source and desitnation are the same message type, links between different nodes are highlighted.



The msxsl.exe command line utility enables you to perform command line Extensible Stylesheet Language (XSL) transformations using the Microsoft XSL processor.


Microsoft BizTalk LoadGen 2007

Microsoft LoadGen 2007 generates message transmission loads to run performance and stress tests for your Microsoft BizTalk Server applications, and provides performance counters to monitor the performance of the infrastructure running BizTalk Server.


BizTalk Server Best Practices Analyzer

he BizTalk Server Best Practices Analyzer V1.2 examines a BizTalk Server 2006, BizTalk Server 2006 R2, or BizTalk Server 2010 deployment and generates a list of issues pertaining to best practices standards for BizTalk Server deployments.


Performance Analysis of Logs (PAL)

The PAL (Performance Analysis of Logs) tool is a new and powerful tool that reads in a performance monitor counter log (any known format) and analyzes it using complex, but known thresholds (provided). The tool generates an HTML based report which graphically charts important performance counters and throws alerts when thresholds are exceeded.


Log Parser 2.2

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory.


Process Explorer

The unique capabilities of Process Explorer make it useful for tracking down DLL-version problems or handle leaks, and provide insight into the way Windows and applications work.


Message Box Viewer

The goal of MBV is to retrieve the maximum amount of information from a BizTalk System in one click in the minimum amount of time, identify all possible issues which could be critical or need attention and present them in a user friendly format.

More info: http://blogs.technet.com/b/jpierauc/archive/2008/07/22/msgboxviewer-mbv-q-a.aspx%22

TCP Trace

Used to monitor text based network traffic between client and server. Useful while using adapters like SOAP,HTTP,POP3 etc to see the message travelling via the wire.



This tool is designed to assist with troubleshooting Microsoft DTC Firewall Issues, which you'll often see in a multiserver BizTalk deployment.

BizTalk Message Decompressor

This tool enables you to decompress BizTalk messages/context from a screen that looks like SQL Query Analyzer.


maandag 7 november 2011

Using DocumentSpecName of the pipeline configuration

A schema can be defined in the 'DocumentSpecName' attribute of the pipeline configuration properties (in the dissamble stage).
When no schema is defined and there are multiple schema's with the same combination for 'Target Namespace' (optional) and 'Root Name', you 'll get following error in the receive pipeline:

There was a failure executing the receive pipeline: "<ReceivePipeline>" Source: "XML disassembler" Receive Port: "<ReceivePortName>" URI: "<URI>\*.xml" Reason: Cannot locate document specification because multiple schemas matched the message type "<TargetNameSpace><RootName>".

The use of "DocumentSpecName" attribute in the configuration section of the pipelines is there to direct the pipeline to validate the message against a specific schema in the given assembly. The attibute should be filled with the schema full name ('<schema type>, <schema assembly>') as shown in below figure. This should solve the above error.

The issue becomes more complex when using multi-part schemas. In htis situation you'll get following error:

There was a failure executing the receive pipeline: "<ReceivePipeline>" Source: "XML disassembler" Receive Port: "<ReceivePort>" URI: "<URI>\*.xml" Reason: No Disassemble stage components can recognize the data .

What BizTalk want to tell us is that at this stage, given the specific configuration of DocumentSpecName Attribute, in the well known syntax of "<schema type> ,<schema assembly full name>" is not enough for multipart schemas.

Turns out that there should be an additional value is needed in the DocumentSpecName-attribute to fully define the schema. So the above value syntax is not enough, it will direct the pipeline to the desired schema in the assembly, but it doesn't say which root it should validate against.

The correct syntax in this case is : "<schema type>+<root name> ,<schema assembly full name>"
So by adding the root name after the '+' -sign solves the issue for multipart-schemas (schemas with multiple root nodes).

woensdag 19 oktober 2011

Biztalk File name Macros

In a Biztalk Send port you can define the file name you are writing. Default Biztalk will use the message ID as filename (%MessageID%).
It is however possible to use a bunch of other parameters in a filename. Here 's a list of possible macros to use in the filename:

Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).

UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).

Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).

Name of the destination party. The value comes from message the context property BTS.DestinationParty.

Identifier of the destination party (GUID). The value comes from the message context property BTS.DestinationPartyID.

Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.

Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.

Name of the file from where the File adapter read the message. The file name includes extension and excludes the file path, for example, foo.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value, for example, if message was received on an adapter other than File adapter, then the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%).

Name of the source party from which the File adapter received the message.

Identifier of the source party (GUID). The value comes from the message context property BTS.SourcePartyID.

Qualifier of the source party from which the File adapter received the message.

UTC time in the format hhmmss.

Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

donderdag 13 oktober 2011

AlwaysOn - New in SQL Server 2012

What is SQL Server AlwaysOn?

SQL Server AlwaysOn is the new high availability and disaster recovery solution for SQL Server 2012. Using AlwaysOn businesses can achieve increased application availability for their mission critical applications and get higher returns on their high availability investments through better utilization of hardware resources. AlwaysOn also increases productivity and lowers TCO by greatly simplifying high availability deployment and management.

More Info: AlwaysOn - New in SQL Server Code Name "Denali" CTP3

SQL Server 2012

The next version of SQL Server will be released in the first half of 2012.
Microsoft has confirmed this release at the PASS Summit on October 12th.
Although Microsoft was yet to announce a specific availability deadline for SQL Server 2012.
Source: SQL Server 2012 Launches in H1 2012, Now in the Final Production Stages

Microsoft already released a Developer Training kit for SQL Server 2012.
This training kit is designed to help you learn how to develop SQL Server 2012 database and BI solutions.

SQL 2012 Training Kit: SQL Server 2012 Developer Training Kit

dinsdag 27 september 2011

BTS Project build error on string message

Today Is was making an orchestration that contains a Multi-Part message type, with a message part of type System.String. When building the BizTalk Project it returned the error "'System.String' must be Xml serializable to be a message part type". I've tried several things, but it kept giving this same error no matter what I tried. But I was certain I had used System.String parts in an orchestration before, without any problems. So after some debugging, I tried restarting Visual Studio... and this seemed to do the trick! Apparantly all ik took to succesfully build the biztalk project was restarting Visual Studio.

woensdag 14 september 2011

donderdag 8 september 2011

SQL Recovery Models explained

There are 3 different recovery models available in Microsoft SQL Server. However, the understanding of the differences between those recovery models doesn't seem to be well understood. That's why I decided to give a little summary of each of the recovery models.
To continue, I first need to explain the differences of logging levels

Logging Levels

Minimally logging

Minimal logging indicates it only logs the information that is required to recover the transaction without supporting point-in-time recovery. An operation is said to be minimally-logged if all it logs are the allocation changes. That means that there is not sufficient information in the log to replay the transaction.
So for example when doing a bulk insert only the allocations will be logged, not the inserted rows or the data on the pages. So this transaction can't be reproduced just by the information that was logged. SQL would be able to reallocate the pages, but it can't put the correct data in these pages.
A complete list of operations that will be minimally logged can be found here.

Fully logging

An operation is said to be fully logged if all information needed to reproduce the operation can be found in the logged information. This doesn't mean that for each operation every row will be individually logged in the transaction log. When doing a truncate on a table for example, it will only log the page de-allocations. But since this information is enough to reproduce the operation, it is still classified as fully logged.

In conclusion

Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.

Recovery Models

Simple Recovery Model

The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.
Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.
More information: Backup Under the Simple Recovery Model

Full Recovery Model

Provides the normal database maintenance model for databases where durability of transactions is necessary.
Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.
More information: Backup Under the Full Recovery Model

Bulk-Logged Recovery Model

This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery.
For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, if data loss is unacceptable, to prevent data loss, it isrecommended that you switch to the bulk-logged recovery model only under the following conditions:
  • Users are currently not allowed in the database.
  • No modifications are made during bulk processing that are not recoverable without depending on taking a log backup; for example, by re-running the bulk processes.
When switching to Bulk-Logged recovery model consider these recommendations:
  • Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.

More information: Backup Under the Bulk-Logged Recovery Model

Summary of recovery models

Recovery model


Work loss exposure

Recover to point in time?


No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Can recover only to the end of a backup.


Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, see Restoring a Database to a Point Within a Backup.

Bulk logged

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

maandag 29 augustus 2011

SQL Server educational links

Found a great blog post full of links for a better knowledge of SQL Server.

There are multiple links on each of these subjects:
  • Identifying SQL Server Bottlenecks and Performance Issues
  • Moving Data in SQL Server
  • Know your SQL Server Objects and Manipulate them

Source: Every .NET Developer Should Know About the Database they are working with

donderdag 25 augustus 2011

Visual Studio: VS templates missing

I was unable to add an aspx page to my web site solution.
The error I got from Visual Studio was:

"Could not find file 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\ItemTemplatesCahce\Web\Csharp\1033\WebForm.zip\WebForm_cb.aspx'."

This was the solution:
  1. Open "Visual Studio 2008 Command Prompt"
  2. Run the command: "devenv /installvstemplates"
And that was all to solve my problem!

dinsdag 23 augustus 2011

Biztalk Error: The system cannot find the file specified (Exception from HRESULT: 0x80070002)

Each time I tried to test my new mapping using BizTalk I got this error: The system cannot find the file specified (Exception from HRESULT: 0x80070002).

So the full error in the event viewer looked like this:

A message received by adapter "FILE" on receive location "RECEIVE ORDERS PCE FILE" with URI "C:\B2B.TRADCOM.COM\ORDERS\INBOUND\PCE\*.xml" is suspended.
Error details: The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
MessageId: {F83F1E4B-3B09-4F9B-A9D5-F6C1BD5C2698}
InstanceID: {E162E5AB-E621-4EA1-B643-FD843D7F8D44}

I was able to map the message using Visual Studio, so I was quite sure my BizTalk mapping was correct.

Now it turned out that I forgot to sign the assembly. So the system was unable to place the assembly in the GAC. Because I used a deploy script I didn't notice the resulting warning.

woensdag 17 augustus 2011

The Mystery of Query Timeouts by Kendra Little - YouTube

"People are complaining about query timeouts. I don't see anything happening in SQL Server, but they always say the database is the problem. How can I tell what's really going on?"

When your users keep hitting timeouts in their application, they naturally think the database is killing off helpless queries. In this webinar Kendra Little will tell you how to triage treacherous timeout situations and collect hard evidence about whether or not the problem is in the database. Do the right detective work and you can turn your frustrated users and irate developers into raving fans.

This session is be 200-level - you should have familiarity with OLTP concepts and understand what DMVs and Profiler are, but you don't have to be an expert with them.

SQL – Interview Questions and Answers

Pinalkumar Dave has published a neat series of FAQ on SQL Server.

The series of blogs provides a simple way to go back over the building blocks of SQL Server. It's way to remind yourself of what you already know.
A Question and Answer discussion can also be a way to go over old topics in a more interesting manner. Especially if you have been working in the industry, or taking lots of classes on the topic, everything you read can sound like a repeat of what you already know. Going over a topic in a new format can make the material seem fresh and interesting. And an interested mind will be more engaged and remember more in the end.

The list of interview Questions and Answers is helpfull as a refresher for SQL developers/administrators or as a way to learn about SQL (as a beginner or to learn new things).

here's an overview of the complete Questions and Answers Series blogs

dinsdag 9 augustus 2011

BizTalk Server 2009 CU package 3 available

The cumulative update package 3 for BizTalk Server 2009 is available.

included hotfixes:
  • BizTalk Server EDI support
    • The EDI batching orchestration does not release batches on time in BizTalk Server 2006 R2 and in BizTalk Server 2009
    • AS2 messages remain active after they are processed when the "Track Message Bodies - Request message after port processing" option is enabled on a send port in a BizTalk Server 2009 environment
  • BizTalk Server Administration Tools & Configuration Tools
    • "The group BizTalk Isolated Host Users doesn't exist" error message is logged when you configure the Exception Web Services by using the ESB Configuration Tool in a BizTalk Server 2009 environment
    • All the tracking options under "Track Events" are selected unexpectedly after a BizTalk orchestration is created or updated in BizTalk Server 2009
    • Applications stop responding or crash when System Center Operations Manager monitors BizTalk Server 2009 applications
  • BizTalk Server Message Box and Message Agent, Pipelines & Tracking
    • Performance decreases when a server that is running BizTalk Server 2009 processes a large batch of messages after you customize the settings of the "Large Message Threshold" option and of the "Large Message Fragment size" option
    • Error message when you receive a MIME or SMIME document in BizTalk Server: "Item has already been added"
  • BizTalk Server XLANG engine
    • White space is removed if a message is processed by a mapping operation that is included in a Construct Message shape in BizTalk Server
  • BizTalk Server Setup
    • Cumulative update package 2 for BizTalk Server 2009 fails if a BizTalk group uses a database that uses a case-sensitive collation

To apply this update, you must have BizTalk Server 2009 installed. For detailed information about how to install this update, see the Readme.txt file that is included in the update package.

  • You have to stop the following services before you apply this update:
    • ENTSSO
  • To install this update in a BizTalk group that includes multiple BizTalk servers, install this server update on each server individually instead of concurrently.

Source: http://support.microsoft.com/kb/2557149

BizTalk Webcast

The biztalk videos for BDD (Behaviour Driven Development) and acceptance testing are now available on cloudcast.

LINK: http://www.cloudcasts.net/Default.aspx?category=BizTalk

dinsdag 26 juli 2011

BizTalk Training Resources

Where to start learning Biztalk?
First of all here's an interesting post that explains the way Biztalk works by Steef-Jan Wiggers.

A more detailed picture of the BizTalk architecture:

Or if you're interested in some more detailed Biztalk architecture posters:

Here's another great reference, here you can find a list of tutorial video's, books, virtual labs, and more: http://sandroaspbiztalkblog.wordpress.com/2011/07/13/biztalk-training-resources-virtual-labs-tutorials-virtual-machine-and-more/

vrijdag 15 juli 2011

The future of BizTalk

Last couple of days the Microsoft Worldwide Partner Conference took place in Los Angeles. It appeared that there was some discussion on the future for Biztalk Server, but I couldn't figure out exactly what it was.

Today I found some Blogs which summarizes the presentation given by Microsoft’s Tony Meleg. It seems that BizTalk as product/brand name may go away after few years but the integration concepts are going to remain. As I understood, no new features would be released anymore, only some maintenance releases.

Presentation video: AP03 - The Future of Middleware and the BizTalk Roadmap
Presentation slides: Download Session Presentation

Summarizing blogs:

donderdag 14 juli 2011

Released: Microsoft SQL Server 2008 R2 SP1 and SQL Server Denali CTP3

This week is packed with big announcements: Microsoft released SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3) with breakthrough innovations that will help our customers to shape their Business Intelligence (BI) future; and Microsoft also released SQL Server 2008 R2 Service Pack 1 with important improvements of our existing technologies.

Also PowerPivot for Microsoft Excel 2010 was updated as a separate download.

Source: Released: Microsoft SQL Server 2008 R2 Service Pack 1 - Analysis Services and PowerPivot Team Blog - Site Home - MSDN Blogs

Dependency service or group failed to start

I got following error when I tried to start the SQL Server service:

My SQL service is running on my domain user account, and this is the reason I got the error. To be able to run any services using a domain account, the Netlogon service must be started.
For some reason (I blame the windows updates that were installed) this services startup type was changed to Disabled. Reconfiguring the Netlogon service to automatic and start the service solved the problem.

Netlogon: Maintains a secure channel between this computer and the domain controller for authenticating users

Microsoft Lightswitch coming on July 26

Microsoft’s LightSwitch to Launch on July 26. This program will give developers a simpler and faster way to create high-quality business applications for the desktop and the cloud. LightSwitch is a new addition to the Visual Studio family.

I've already been playing with Lightswitch some time ago + I've seen the presentation on Techdays 2011 in Belgium. The new Visual Studio addition definitely has some promising features, and is easy to use. It could get quite handy for some (smaller) projects.

Lightswitch makes sure you'll get a working program fast with pre-configured screen templates, prewritten code and other reusable components to handle routine application tasks, along with helpful step-by-step guidance.

And afterwards you can of course customize it all in Visual Basic .NET or C#. To finalize, you can deploy to the desktop, browser, or cloud to share your application with others more easily without the cumbersome installation processes.

More info: Lightswitch, a developers tool, will launch on July 26
Videos and tutorials: http://www.microsoft.com/visualstudio/en-us/lightswitch

maandag 11 juli 2011

Free E-book: SQL Server Execution Plans

Execution plans show you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including:

Which indexes are getting used and where no indexes are being used at all.
How the data is being retrieved, and joined, from the tables defined in your query.
How aggregations in GROUP BY queries are put together.
The anticipated load, and the estimated cost, that all these operations place upon the system.
All this information makes the execution plan a fairly important tool in the tool belt of database administrator, database developers, report writers, developers, and pretty much anyone who writes TSQL to access data in a SQL Server database.

Download link: http://downloads.sqlservercentral.com/Books/dissecting_complete.pdf?utm_source=ssc&utm_medium=weblink&utm_content=Grant&utm_campaign=sqltoolbelt
Source: SQL Server Execution Plans - SQLServerCentral

woensdag 29 juni 2011

donderdag 16 juni 2011

CU 1 voor de BizTalk-Adapter Pack 2010

This cumulative update for Microsoft BizTalk Adapter Pack 2010 contains hotfixes for Microsoft BizTalk Adapter Pack 2010 issues that were resolved after the release of BizTalk Adapter Pack 2010.

It contains following hot fixes:
  • FIX: The WCF-SQL adapter from BizTalk Adapter Pack 2.0 or from BizTalk Adapter Pack 2010 does not write enough useful error information
  • FIX: A WCF-based SQL adapter may lock SQL Server resources when the PolledDataAvailableStatement binding property returns a positive result but the actual polling does not return any data
  • FIX: Statement in the PollStatement property incorrectly runs if a receive location uses WCF-based BizTalk Adapter for SQL Server
  • FIX: An update is available that adds the ClearRfcContext binding property to the WCF-based SAP adapter from BizTalk Adapter Pack 2.0 or BizTalk Adapter Pack 2010
  • FIX: Multiple grouped IDOCs are processed incorrectly if the WCF-based SAP adapter in BizTalk Adapter Pack 2.0 or BizTalk Adapter Pack 2010 uses the String setting to connect to an SAP system
  • Hotfix to add the "ReceivedIdocRelease" binding property for the WCF-SAP adapter to BizTalk Adapter Pack 2.0 and Microsoft BizTalk Adapter Pack 2010
SOURCE: http://support.microsoft.com/kb/2539794/en-us

BizTalk Server 2010 Developer Training Kit

Also a developer kit for the biztalk 2010 is avaible!

This training kit contains a complete set of materials that will enable you to learn the core developer capabilities in BizTalk Server 2010. This kit includes lab manuals, PowerPoint presentations and videos, all designed to help you learn about BizTalk Server 2010. There is also an option below to download a Virtual Machine that is ready for you to use with the training kit.

After completing this course, students will be able to:

  • Work with schemas, maps, and pipelines, and create flat file schemas.
  • Configure a new FTP, HTTP and Windows SharePoint Services (WSS) adapter for BizTalk Server
  • Use BizTalk Orchestration Designer to create and test a simple orchestration.
  • Configure orchestration properties and variables, deploy an orchestration, and create and deploy a rule set and execute those rules from within an orchestration.
  • Define, deploy, and map a BAM observation model.
  • Use the deployment and management features in BizTalk Server
  • Build a simple Windows Communication Foundation (WCF) service and client and configure BizTalk Server to use a WCF Adapter.
  • Create a Microsoft .NET class library project that will contain a WCF Adapter.
  • Deploy artifacts needed for processing certain EDI documents and for turning XML messages into EDI.

DOWNLOAD: http://www.microsoft.com/download/en/details.aspx?id=14865

dinsdag 7 juni 2011

VS2008: web services enumeration components not available

Recently I got following error when trying to add a Web Reference to VS2008:
The web services enumeration components are not available. You need to reinstall Visual Studio to add web references to your application.

I have no idea why this error suddenly appeared.
However, I did find a solution to this issue.

I executed 'devenv /ResetSkipPkgs' from the command line in a VS2008 command prompt.
This did the trick for me.

vrijdag 27 mei 2011

SQL: Index Scan Vs. Index seek

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192 for an idea on how this works) - time taken is only proportional to the number of matching records.

In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
Note however that in certain situations an index scan can actually be faster than an index seek - usually when the table is very small, or when a large percentage of the records match the predicate.

woensdag 18 mei 2011

Microsoft SQL Server 2008 R2 Service Pack 1

Service Pack 1 is available for SQL Server 2008 R2.

What’s New in SQL Server 2008 R2 Service Pack 1
  • Dynamic Management Views for increased supportability:
    sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.
  • ForceSeek for improved querying performance :
    Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.
  • Data-tier Application Component Framework (DAC Fx) for improved database upgrades:
    The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
  • Disk space control for PowerPivot:
    This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

    Source:Download details: Microsoft® SQL Server® 2008 R2 Service Pack 1 Community Technology Preview
  • SQL Server 2008 Developer Training Kit

    Learning how to develop with the rich data types, programming models and new development paradigms in SQL Server 2008?
    Microsoft has a training kit available to do this with slides, demos and hands-on labs.

    DOWNLOAD: SQL Server 2008 Developer Training Kit

    Since Januari 2011 there is also an update available.

    New Content: Build Your First Microsoft BI Solution with SQL Server 2008 R2
    This update includes a rich new set of content designed for developers who want to learn how to build their first Microsoft Business Intelligence solution with SQL Server 2008 R2. Microsoft BI solutions leverage your existing technology investments in .NET, SQL Server and Office to develop rich integrated reporting and analytics experiences that empower users to gain access to accurate, up-to-date information for better, more relevant decision making. This content is a complete training course that contains eleven units covering all the key aspects of Microsoft BI solution development. You can find it by navigating to the Getting Started - BI Developers page. Here is a summary of what’s included:
    31 presentations (PowerPoint slides, videos and transcripts included)
    27 demos (installer scripts, videos and transcripts included)
    12 hands on labs (installer scripts included)

    More Info + Download: SQL Server 2008 R2 Update for Developers Training Kit

    dinsdag 10 mei 2011

    interesting vids

    I've been to the Microsoft Techdays a few weeks ago.It's been an amazing experience and learned some new things.

    Too bad though that some interesting sessions took place on the exact same moment. So I could only follow one of them.
    Luckily however, they announced that all sessions would be recorded and placed on the channel 9 web site to view later.

    Well, I'm still waiting for those sessions to appear on the website...
    I've found some other Microsoft technology event sessions of this year that were on channel 9 already. So for now, I won't have any evenings not knowing what to do :).

  • Devdays 2011 Netherlands
  • MIX 2011 Las Vegas
  • techdays 2011 Belgium (only developer keynote on line yet)
  • woensdag 4 mei 2011

    Promoted property vs distinguished field - tutorial

    My latest post has been quite some time ago now.
    This is because of a holiday, a course on SQL 2008 and off course attending the techdays 2011 Belgium.
    I recently found an interesting blog on the difference between promoted properties and distinguished fields in BizTalk. It explains the differences between them both and shows you how to add both of them to your schema.
    BLOG: Francois Malgreve - Bangkok: Promoted property vs distinguished field - tutorial

    donderdag 24 maart 2011

    Cumulative update package 2 for BizTalk Server 2009

    Microsoft has released CU2 for BizTalk Server 2009 and details can be found here:
    Cumulative update package 2 for BizTalk Server 2009

    BELUX MSDN and TechNet Desktop Gadget

    Stay up to date with the latest news from MSDN and TechNet in Belgium and Luxembourg via the new desktop gadget for Windows Vista and Windows 7. It’s free, easy to install and very practical: you won’t miss anything of the latest tech news, blogs, videos and event registrations, as they are all fed right into your desktop!

    Downoad: http://gallery.live.com/LiveItemDetail.aspx?li=1f7425ec-9b30-4d71-ac92-242837a10c41
    Blog: Install the BELUX MSDN and TechNet Desktop Gadget to stay up to date - Katrien DG's MSDN Blog

    woensdag 23 maart 2011

    BizTalk monitoring, PART 2: BTS suspended messages

    We've written some code to receive an E-mail when a message is suspended in Biztalk. For this code we've created a scheduled task that is triggered when an error is raised by Biztalk.

    First of all I'll show how to setup such a triggered event. Just make a new scheduled task in your windows task scheduler. To start the task you add a new trigger, here's a screenshot from how to set up this trigger:

    Then click on the 'Edit event filter' button.
    Following screenshot shows you how to configure the trigger to the Biztalk 2009 Errors:

    Now that the trigger is set, just configure the scheduled task to run the programming code. Just like the BizTalk monitoring tool of my previous post, the configuration is done in an external XML File.

    So here is the used code the send us an E-mail with an overview of suspended messages, including the error details and the receiving port. The method will return a boolean to specify if there are no suspended messages.

       1:  public Boolean CheckBTSQueue()
       2:          {
       3:              String tab = "&nbsp;&nbsp;&nbsp;&nbsp;";
       4:              //Constant Values
       5:              String ErrorMessage = tab + "<b><i>Suspended message with error at {0}:</i></b><BR>" +
       6:                  tab + tab + "Receive Port: {1}<BR>" +
       7:                  tab + tab + "Error Code: {2}<BR>" + 
       8:                  tab + tab + "Error Description: {3}<BR><BR>";
       9:                  //tab + tab + "Inner Exception: {3}";
      10:              String ErrorSuspendedMsgHeader = "<strong>WARNING: Suspended Message(s) in Biztalk</strong><BR><BR>";
      11:              String MailSubject = "Messages stuck on " + Dns.GetHostName();
      13:              StringBuilder suspendedMessages = new StringBuilder();
      15:              Boolean allRunning = true;
      17:              BizTalkOperations operation = new BizTalkOperations(dBServer, dB);
      19:              int count = 10;
      20:              foreach (BizTalkMessage message in operation.GetMessages())
      21:              {
      22:                  if (message.MessageStatus == MessageStatus.Suspended)
      23:                  {
      24:                      suspendedMessages.AppendLine(String.Format(ErrorMessage, message.CreationTime.ToString(),
      25:                          message.Context.Read("PortName", "http://schemas.microsoft.com/BizTalk/2003/messagetracking-properties"), message.ErrorCode, message.ErrorDescription));
      26:                      //message.Context.Read("Name", "NameSpace");
      28:                      count--;
      30:                      allRunning = false;
      31:                  }
      32:                  if (count <= 0)
      33:                  {
      34:                      count--;
      35:                      break;
      36:                  }
      37:              }
      39:              if (!allRunning)
      40:              {
      41:                  if (!String.IsNullOrEmpty(suspendedMessages.ToString()))
      42:                      suspendedMessages.Insert(0, ErrorSuspendedMsgHeader);
      44:                  String body = "";
      46:                  if (count < 0)
      47:                      body = "Only last 10 messages are given<BR><BR>";
      49:                  body += suspendedMessages.ToString();
      51:                  Helper.SendMail(body, MailSubject, sendMailAddress, receiveMailAddress, SMTP);
      52:              }
      54:              return false;
      55:          }

    the method to send an email, the method to read the configuration from XML and the XML itself are the same as in my previous Blog. Here they are again:

    the method to send an email is the following:

       1:  public static void SendMail(String body, String subject, String fromAddress, String toAddress, String SMTPAddress)
       2:          {
       3:              MailMessage msg = new MailMessage(fromAddress, toAddress);
       4:              msg.IsBodyHtml = true;
       5:              msg.Body = body;
       6:              //msg.From = new MailAddress(fromAddress);
       7:              msg.Subject = subject;
       9:              System.Net.Mail.SmtpClient smtp = new SmtpClient(SMTPAddress);
      10:              smtp.Send(msg);
      11:              //SmtpMail.SmtpServer = SMTPAddress;
      12:              //SmtpMail.Send(msg);
      13:          }

    to read the configuration out of the XML file, we've programmed following in the constructor (all is read to private class variables:

       1:  private string dBServer;
       2:          private string dB;
       3:          private string userName;
       4:          private string password;
       6:          private List<string> BTSService;
       8:          private string sendMailAddress;
       9:          private string receiveMailAddress;
      10:          private string SMTP;
      12:          public Monitor()
      13:          {
      14:              XmlDocument config = new XmlDocument();
      15:              config.Load(System.IO.Directory.GetCurrentDirectory() + "/BTSMonitorConfiguration.xml");
      17:              try
      18:              {
      19:                  dBServer = config.SelectSingleNode("CONFIGURATION/DBSETTINGS/Server").InnerText;
      20:                  dB = config.SelectSingleNode("CONFIGURATION/DBSETTINGS/DB").InnerText;
      21:                  userName = config.SelectSingleNode("CONFIGURATION/DBSETTINGS/UserName") == null ?
      22:                      "" : config.SelectSingleNode("CONFIGURATION/DBSETTINGS/UserName").InnerText;
      23:                  password = config.SelectSingleNode("CONFIGURATION/DBSETTINGS/Password") == null ?
      24:                      "" : config.SelectSingleNode("CONFIGURATION/DBSETTINGS/Password").InnerText;
      26:                  BTSService = new List<string>();
      28:                  foreach(XmlNode service in config.SelectNodes("CONFIGURATION/BTSSETTINGS/BTSSERVICES/BTSServiceName"))
      29:                  {
      30:                      BTSService.Add(service.InnerText);
      31:                  }
      33:                  sendMailAddress = config.SelectSingleNode("CONFIGURATION/MAILSETTINGS/From").InnerText;
      34:                  receiveMailAddress = config.SelectSingleNode("CONFIGURATION/MAILSETTINGS/To").InnerText;
      35:                  SMTP = config.SelectSingleNode("CONFIGURATION/MAILSETTINGS/SMTP").InnerText;
      36:              }
      37:              catch (Exception e)
      38:              {
      39:                  StringBuilder error = new StringBuilder();
      40:                  error.AppendLine("not all necessary data to perform the action is provided.");
      41:                  error.AppendLine("Make sure these fields are correctly filled in in the XML File:");
      42:                  error.AppendLine("\tDBServer (server name)");
      43:                  error.AppendLine("\tDB (Database)");
      44:                  error.AppendLine("\tBTSServiceName (BTS Service Name)");
      45:                  error.AppendLine("\tFrom (sending mail address)");
      46:                  error.AppendLine("\tTo (receiveing mail address)");
      47:                  error.AppendLine("\tSMTP (SMTP host address)");
      48:                  throw (new Exception(error.ToString()));
      49:              }
      50:          }

    and finally the created XML file:

    <?xml version="1.0" encoding="utf-8" ?>
        <Server>.</Server>    <!-- Server Name -->
        <DB>BizTalkMgmtDb</DB>    <!-- DataBase Name -->
        <!--<UserName>user</UserName>-->    <!-- optional -->
        <!--<Password>password</Password>-->    <!-- optional -->
          <BTSServiceName>BizTalk Service BizTalk Group : BizTalkServerApplication</BTSServiceName>      <!-- BTS Service Name -->
          <BTSServiceName>BizTalk Service BizTalk Group : BizTalkServerApplication2</BTSServiceName>
          <!-- BTS Service Name -->

    In this example, only the last 10 suspended messages are mailed to us.

    These monitoring mails have proven to be very valuable to us.
    Hope you enjoyed my post.