---

This guide describes the process for installing and configuring Microsoft BizTalk Server 2010 on a single computer (standalone machine) running Windows Server 2008 R2. This information will help you plan the installation and configuration of BizTalk Server 2010, applications and components on which it depends.

Before installing BizTalk Server or its prerequisites, make sure you have installed the latest critical Windows updates from Microsoft. We assume that the machine is already installed with the operating system.

Enable Internet Information Services

Microsoft Internet Information Services (IIS) provides a Web application infrastructure for many BizTalk Server features. BizTalk Server requires IIS for the following features:

• HTTP adapter
• SOAP adapter
• Windows SharePoint Services adapter
• Secure Sockets Layer (SSL) encryption
• BAM Portal

To enable Internet Information Services 7.5:

• Click Start, point to Administrative Tools and then click Server Manager.
• In Roles Summary, click Add Roles.

• On the Before You Begin screen, click Next.
• On the Select Server Roles screen, click Web Server (IIS).
  

  

• If prompted, click Add Required Features, and then click Next.
• Review the information on the Web Server (IIS) screen, and then click Next.
• On the Select Roles Services screen, in addition to the default values, select Application Development, and then click Add Required Features.
• Under Security, select Basic Authentication and Windows Authentication.

• Under Management Tools, select IIS 6 Management Compatibility, and then click Next.

• Review the information on the Confirm Installation Selections screen, and then click Install.
• On the Installation Results screen, click Close.

Note: BAM Portal runs only runs on a 32-bit mode. If you are installing IIS on a 64-bit machine then you must ensure that ASP.NET 2.0 is enabled on 32-bit mode. To do this, follow these steps:

• On the taskbar, click Start, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
• In the Connections pane, expand the server name, and then click Application Pools.
• In the Actions pane, click Set Application Pool Defaults…

• On the Application Pool Defaults dialog box, in Enable 32-bit applications, select True.

To enable it from the command prompt:

• Click Start, right-click Command Prompt, and then click Run as administrator.
• At the command prompt, type cd %windir%\system32\inetsrv, and then press Enter.
• Type appcmd set config -section:system.applicationHost/applicationPools /applicationPoolDefaults.enable32BitAppOnWin64:”True” /commit:apphost, and then press Enter.

Microsoft Office Excel is required for using the Business Activity Monitoring (BAM) feature in BizTalk Server. You use the BAM Office Excel Workbook to define the business processes you want to monitor. You also use the BAM Excel Workbook to define the way in which business users see the data collected by BAM.

Note: BizTalk Server 2010 supports only 32-bit version of Microsoft Office 2010.

The following procedure installs only Office Excel. You can install additional Microsoft Office system applications and tools if you wish, but they are not necessary to complete the installation of BizTalk Server.

To install Microsoft Office Excel:

Note: After installing the office Excel and tools I like to do a Windows update, this step is not necessary.

Install Visual Studio 2010

The BizTalk Server development tools are based on Visual Studio 2010. Therefore, at a minimum, you must have the Microsoft Visual C#® .NET portion of Visual Studio 2010 installed on your computer before installing the BizTalk Server Developer Tools and SDK component.

Visual Studio 2010 is not required if you are installing BizTalk Server on a production computer (runtime only), on which no application development or debugging will occur.

The BizTalk Server runtime components require .Net Framework 4 and .NET Framework 3.5 SP1. In addition, the .NET Framework 3.0 is required if the Windows Communication Foundation (WCF) adapter or WCF Interceptor is installed.

To install Visual Studio 2010:

• Insert the Visual Studio 2010 installation disk into the DVD-ROM drive.
• Click Install Visual Studio 2010.
• After the installation loads, click Next.
• On the Start page, accept the license agreement, type your Product Key and any other necessary information, and then click Next.
• On the Options page, select Custom, and then click Next.

• Under Select features to install, clear all features except Visual C#, and then click Install. (I chose to install other features as well, but not necessary)

Important: Your version of Visual Studio may include Microsoft SQL Server Express as an optional feature. Do not install this feature, as it will cause BizTalk Server setup to fail.
Note: Is recommend to install VC++ because this is required in order to build the ESB toolkit samples. Also, VB.NET must be installed in order to work with WF 4 in VS 2010.

• On the Finish page, click Finish.
• On the Visual Studio Setup screen, click Exit.

Note: Once again, after this installation, I like to do a Windows update, this step is not necessary.

Install SQL Server 2008 R2

BizTalk Server provides the capability to specify a business process and also a mechanism by which the applications used in that business process can communicate with each other. SQL Server is the main repository for this communication mechanism. You must install SQL Server 2008 R2/SP1 (Enterprise Edition is recommended).

Important: BAM real-time aggregation (RTA) is not supported in SQL Server 2008 R2/SP1 Standard Edition. In order to use BAM RTA, you must install SQL Server 2008 R2/SP1 Enterprise Edition.

To install SQL Server 2008 R2:

• Insert the SQL Server 2008 R2 installation disk into the DVD-ROM drive. The setup program will begin automatically. If prompted for automatic installation of prerequisite software, click OK.
• On the SQL Server Installation Center, click Installation.
• Click New Installation or Add Features to an Existing Installation.

• Review the information on the Setup Support Rules screen, and then click OK.

• On the Product Key screen, enter your product key and click Next.
• On the License Terms screen, select I accept the license terms, and then click Next.
• On the Setup Support Files screen, click Install.
  • You will probably get a warning for the Windows Firewall. That is because some ports needs to be opened if you plan to access your SQL Server installation from outside. Read more at http://go.microsoft.com/fwlink/?LinkId=94001

• On the Feature Selection screen, select the following features, and then click Next.
  • Database Engine Services
    • SQL Server Replication
    • Full-Text Search
  • Analysis Services
  • Reporting Services
  • Shared Features
    • Business Intelligence Development Studio
    • Client Tools Connectivity
    • Integration Services
    • Management Tools – Basic
      • Management Tools – Complete

• On the Instance Configuration screen, select Default instance, and then click Next.

• Review the information on the Disk Space Requirements screen, and then click Next.
• On the Server Configuration screen, click Use the same account for all SQL Server instances, enter a local account or domain account and password, and then click Next.

• On the Database Engine Configuration screen, select Windows authentication mode, click Add Current User, and then click Next.

• On the Analysis Services Configuration screen, click Add Current User, and then click Next.

• On the Reporting Services Configuration screen, select Install the native mode default configuration, and then click Next.

• On the Error and Usage Reporting screen, leave the default values, and then click Next.
• On the Installation Rules screen, review the information and resolve any issues, and then click Next.
• On the Ready to Install screen, review the information, and then click Install.

• On the Installation Progress screen, after setup has finished, click Next.
• On the Complete screen, click Close.

Note: Once again, after this installation, I like to do a Windows update, this step is not necessary.

Install SQL Server 2005 Notification Services on top of SQL Server 2008 R2

If you plan to use BAM Alerts in your BizTalk Server project, you must install SQL Notification Services and its prerequisites on your BizTalk Server computer. This SQL Server 2005 feature is not included in SQL Server 2008 R2/SP1, but you can install it from the Microsoft Download Center.

To install SQL Server 2005 Notification Services

• Click or navigate to the Feature Pack for Microsoft SQL Server 2005 – December 2008 (http://go.microsoft.com/fwlink/?LinkId=154501).
• Depending on your platform, download the appropriate package from the following three components :
  • Microsoft SQL Server Native Client
    • X86 Package (sqlncli.msi)
    • X64 Package (sqlncli_x64.msi)
  • Microsoft SQL Server 2005 Management Objects Collection
    • X86 Package (SQLServer2005_XMO.msi)
    • X64 Package (SQLServer2005_XMO_x64.msi)
  • Microsoft SQL Server 2005 Notification Services Client Components
    • X86 Package (SQLServer2005_NS.msi)
    • X64 Package (SQLServer2005_NS_x64.msi)

In my case I used x64 components.

To install Microsoft SQL Server Native Client

You need to execute the “sqlncli_x64.msi” file:

• On the Welcome Screen click Next.
• On the License Terms screen, select I accept the license terms, and then click Next.
• On the Registration Information screen, enter your name and company in their respective fields, and then click Next
• On the Feature Selection screen, select the following features, and then click Next.
  • Client Components

• On the Ready to Install screen, click Install.

To install Microsoft SQL Server 2005 Management Objects Collection

You need to execute the execute “SQLServer2005_XMO_x64.msi” file:

• On the Welcome Screen click Next.
• On the License Terms screen, select I accept the license terms, and then click Next.
• On the Registration Information screen, enter your name and company in their respective fields, and then click Next
• On the Ready to Install screen, click Install.

To install Microsoft SQL Server 2005 Notification Services Client Components

You need to execute “SQLServer2005_NS_x64.msi” file:

• On the Welcome Screen click Next.
• On the License Terms screen, select I accept the license terms, and then click Next.
• On the Registration Information screen, enter your name and company in their respective fields, and then click Next
• On the Ready to Install screen, click Install.

Install and Configure Windows SharePoint Services

Windows SharePoint Services is a collection of services for Windows Server which enable you to share information, collaborate with other users on documents, and create lists and Web part pages. For information and procedures on installing Windows SharePoint Services, see Appendix A.

Note: You do not need to install WSS if you do not also plan to install the WSS adapter. However, if you are installing WSS, you must do it before continuing with the rest of the BizTalk Server prerequisites.

BizTalk Server 2010 supports these two versions of WSS:

• SharePoint Foundation 2010
• Windows SharePoint Services 3.0 with SP2

Installing and configuring Windows SharePoint Services consists of the following procedures:

• Install Windows SharePoint Services
• Configure Windows SharePoint Services
• Extend the Default Web Site as a virtual server

In this tutorial I chose to install WSS 3.0 with SP2 for no particular reason. However, if you wish to use SharePoint Foundation 2010, I suggest reading Steef-Jan Wiggers post:

• Installing Windows SharePoint Services Web Service Adapter BizTalk Server 2010/SharePoint Foundation 2010

where is explained in detail all the steps required to install and configure SharePoint Foundation 2010 and SharePoint Services Web Service Adapter for BizTalk Server 2010

Install Windows SharePoint Services 3.0 with SP2

In the following procedure you install Windows SharePoint Services 3.0:

• Windows SharePoint Services 3.0 requires Microsoft .NET Framework 3.0, which must be installed before beginning installation of Windows SharePoint Services 3.0. The .NET Framework is available for download at http://go.microsoft.com/fwlink/?linkid=75053.
• Browse to http://www.microsoft.com/downloads/en/details.aspx?FamilyId=EF93E453-75F1-45DF-8C6F-4565E8549C2A&displaylang=en. Download the installation package to a location on your hard disk.
• Locate the package you just downloaded (SharePoint.exe), and then double-click the package to start the setup.
• On the End User License Agreement screen, select I accept the terms in the License Agreement. On this screen and several others that follow, the setup wizard will automatically move to the next screen when you have made your selection.

• Under Type of installation, choose the Advanced button.

• On the Server Type tab, select Web Front End, then click Install Now.

• Ensure the configuration checkbox is clicked and click the “Close” button.

Configure Windows SharePoint Services

• On the Welcome screen, click “Next”

• In the Warning screen, Click “Yes”

• On the Server Farm screen, select the option “No, I want to create a new server farm” and click “Next”

• On the Configuration Database screen, provide a Database server name and credentials and click “Next”.

• On the Configure SharePoint Central Administration WEB Application screen, use the Default values and click “Next”.

• On the Completing the SharePoint Products and Technologies Configuration Wizard screen, leave the default values and click “Next”

• On the Configuration Successful screen, click “Finish”.

Extend the Default Web Site as a virtual server

• Open SharePoint Central Administration, and click “Application Management”

• On the Application Management Tab, click on “Create or extend Web application”

• At this point, normally I extend an existing Web application, this time I chose to follow Kent Weare suggestion and create a new one using the port 90, so click on “Create a new Web application”

• I have decided to create my Web Application in a new IIS Web site called “SharePoint – 90”. I have done this so that I don’t overwrite or break anything that may be living in my Default Web Site. I also have modified the port to be “90” which means any requests to my WSS Site will need to have “:90” in the url. If you do this then when you post or retrieve documents to/from SharePoint from BizTalk that you will need to include this port number in your Receive Location/Send Port configuration.
  • On IIS Web Site options, select “Create a new IIS web Site”, set a description and change Port to 90
  • On Security Configuration options, set Authentication provider as NTLM

• On Load Balanced URL options, check if the URL as “:90”
• On Application Pool options, create a new application pool “Sharepoint – 90”

• On Database Name and Authentication options, appended “99” onto the Database Name that will be used for storing Content and chose “Windows authentication” option
• Click “Ok”
• You have now created the “shell” for this Web Application, but you still don’t have any content in this Web Application. Click on the “Create Site Collection” link. Remember to issue an “iisreset /noforce” command to complete the Web Application creation process.
  • Open cmd and execute “iisreset/noforce”
  • Click “Create Site Collection”

• On Title and Description option, give your Site a Title, Description(if so desired), on the Web Site Address option create a URL, and in the Template Selection option select “Team Site” (Team Sites are generally a place where team members can collaborate by publishing documents, but other features exist such as Team Calendars, surveys, links etc).

• On Primary and Secondary Site Collection Administrator option set an user. And click “Ok” to finish.

• Team site has now been created.

Disable the Shared Memory Protocol

Under certain stress conditions (such as clients accessing SQL Server from the same computer), the SQL Server Shared Memory protocol may lower BizTalk Server performance. You can resolve this problem by disabling the use of the Shared Memory network protocol in the SQL Server Client Network Utility.

To disable Shared Memory protocol for SQL Server:

• Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.
• In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for MSSQLSERVER.

• Right-click Shared Memory, and then click Disable.
• Click SQL Server Services, right-click SQL Server (MSSQLSERVER), and then click Stop. After the service has stopped, right-click SQL Server (MSSQLSERVER) again, and then click Start.

• Close SQL Server Configuration Manager.

Validate and configure the Application Event Log

This is an important because:

• BizTalk Server setup keeps a record of events in the Application Event Log. Depending on the BizTalk Server features installed, the amount of space required in the log may exceed its limit. If the application event log runs out of space during BizTalk Server setup, the installation will fail.
• Also in production you always want a rolling list of the latest events (information, warnings or failures) and you don’t want that your Application Event Log becomes massive in size.

However by default the Application Event Log is already correctly configured:

• Maximum log size (Kb): 20489 – that is 20 MB, which gives us a comfortable historic of events
• And the option “Overwrite events as needed (oldest events first)” is select, with this option we prevent that the log to exceed its limits, avoiding installation failures:
  • Typical error: Inner exception: The event log file is full

To change settings in the Application Event Log:

• Click Start, click Control Panel and then double-click Administrative Tools.
• Click Event Viewer.
• In the Event Viewer, expand Windows Logs.
• Right-click Application and then click Properties. This opens the Log Properties dialog box.
• To see how much available space your log has, compare the Log Size field with the Maximum log size field.
• To provide more space in the event log, enter a higher number in the Maximum log size field.
• To enable overwriting of old events when the log becomes full, select Overwrite events as needed.
• To clear the log events, click Clear log.
• Click OK, to close the Event Viewer

Samples of BizTalk events in the Application Event Log
Information:

The following BizTalk host instance has initialized successfully. 

BizTalk host name: BizTalkServerApplication 

Windows service name: BTSSvc$BizTalkServerApplication

Error:

A message sent to adapter "SMTP" on send port " EmailNotificationPort" with URI "mailto:myemai@email.pt" is suspended.

Error details: There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: " EmailNotificationPort" URI: "mailto: myemai@email.pt" Reason: Name cannot begin with the ‘<’ character, hexadecimal value 0x3C. Line 2, position 1.

A request-response for the "SOAP" adapter at receive location "/WebServiceasmx" has timed out before a response could be delivered.

Warning:

The cube "MyView" was not processed. Please run the cube DTS to process the cube.

The adapter failed to transmit message going to send port "DynamicSendPort" with URL "http://Service.asmx". It will be retransmitted after the retry interval specified for this Send Port. Details:"Server was unable to process request. —> Object reference not set to an instance of an object.".

Installing and Configuring BizTalk Server 

When you installed SQL Server, setup granted your account Database Administrator rights. Since these rights are also required for installing BizTalk Server, you must do one of the following:

• Use the same account you used when you installed SQL Server.
• Make sure the account you’re using now also has Database Administrator rights.

To install BizTalk Server 2010

• Close any programs you have open.
• Insert the BizTalk Server 2010 installation disk into the DVD-ROM drive.
• On the Start screen, click Install Microsoft BizTalk Server 2010. 

• On the Customer Information screen, type your user name and organization, enter your product key, and then click Next. 

• On the License Agreement screen, click Yes, I accept the terms of the license agreement, and then click Next. 

• Use the Customer Experience Improvement Program page of the BizTalk Server 2010 Installation Wizard to enable feature usage reporting functionality for BizTalk Server. 

• On the Component Installation screen, review the available components and select the ones you want to install. 

• Accept the default installation location or click Browse to move to the location where you want to install BizTalk Server 2010, and then click Next.
• If your computer is missing a prerequisite component such as ADOMD.NET, Setup can install the redistributable prerequisites. You can either:
  • Select Automatically install the redistributable prerequisites from the web
  • OR Select Automatically install the redistributable prerequisites from a CAB file if you have downloaded the CAB file. If you select this, you can then browse to the location of the CAB file and select it. 

• On the Summary screen, verify that the components that you select to install are correct. 

• To enable auto-logon after a system reboot, click Set and provide your logon information. Auto-logon is enabled only for reboots during setup, and is disabled when setup is complete.
• Click Install to start the installation process.
• On the Microsoft Update Setup page, click Use the Microsoft Update when I check for updates (recommended).
• On the Installation Completed screen, clear the Launch BizTalk Server Configuration check box, and then click Finish. 

Verify Your Installation

You can check to see whether your installation was successful by using either Programs and Features or the Registry.

To verify installation using Programs and Features

• Click Start, click Control Panel (View by: Large/Small icons), and double-click Programs and Features.
• When the list is fully populated, look for BizTalk Server 2010. If it appears in the list, setup succeeded.

To verify installation using the Registry

• Open a command prompt.
• Type regedit and then press Enter.
• When the Registry opens, browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0
• If files exist in the 3.0 folder, setup succeeded.
• Close the Registry, and then close the command prompt.

Configure BizTalk Server

Use the following procedure to complete a basic configuration of BizTalk Server.

To configure BizTalk Server 2010 using Basic Configuration:

• Click Start, click All Programs, click Microsoft BizTalk Server 2010, right-click BizTalk Server Configuration, and then click Run as Administrator. 

• In the Database field, enter the name of the local computer.
• In the Service Credential field, type the User name and Password for the account that the BizTalk services will run under. 

• Click Configure.
• Note: You may receive a warning if you enter a user name with administrative credentials on this computer. Click OK to continue. 

• On the Summary screen, review the configuration about to be performed, and then click Next. 
• Don’t worry about SharePoint Adapter, we deal with this later! 

• On the Completion screen, click Finish. 

Note: Once again don´t worry about SharePoint Adapter, we deal with this later.

Setting up BizTalk – SharePoint Adapter

We now have the ability to configure the “SharePoint Adapter”. For that you must:

• Run BizTalk Server Configuration 

• On the Configuration Screen, select the option “SharePoint Adapter” and use the drop down list to select the WSS Site where you would like the adapter to be installed, in this case “SharePoint – 90”. 

• Assuming everything is aligned correctly, you should now be able to click the “Apply Configuration” button.
• On the Summary screen, click “Next” 

• On the Completion screen, click Finish. 

Congratulations you have now BizTalk Server 2010 fully installed and working for the first time! Only missing some configurations.

Installing BizTalk Adapter Pack 2010

The BizTalk Adapter Pack 2010 provides connectivity to SAP, Oracle E-Business Suite, Siebel and SQL and Oracle Databases. The BizTalk Adapter Pack can be installed in conjunction with, or separately from, BizTalk Server.

The BizTalk Adapter Pack 2010 includes support for new versions of the following line-of-business applications and databases: SAP 7, Oracle EBS 12.1 and SQL Server 2008 R2. The Adapter Pack also provides support for Visual Studio 2010 and .NET Framework 4.

It is included in both the Enterprise and Standard Editions of BizTalk. When you install the adapter pack on x64 environment you have to perform a few steps. These steps were described in earlier versions through installation guides. There is documentation to install adapters pack, but now during installation one is guided through the necessary steps.

This is a definite improvement towards installing Adapter Pack on for instance x64 environment as shown on the screenshot above. So first step is installing WCF LOB Adapter SDK and by clicking on the first step an installer of SDK is launched.


By clicking next and selecting one of installation options the SDK is installed. After installation you will see that first step is completed in the overall process. Second step is installation of the Adapter Pack (x86). By clicking Microsoft BizTalk Adapter Pack needs to be installed an installer is launched again.

After installation of this pack the screen is updated again stating second step is completed successfully.

You will sometime see windows update screen or screen with question if you want to join the improvement program. Next step is installing the Microsoft BizTalk Adapter Pack(x64). Before installing this pack you have to install x86 first, which is overlooked a lot of times in past by people. Can’t happen having guidance.

Having installed the x64 Adapter Pack the final step can be performed.

By clicking on Install Microsoft BizTalk Adapters for Enterprise Applications an installer is launched.

As this installation has been successfully completed the process of installing the LOB Adapters is complete.

I recommend to always choose complete installation of SDK, Adapter Packs and Microsoft BizTalk Adapters for Enterprise Applications.You the have documentation at you disposal. Going through this process you now have full set of adapters ranging from new WCF Adapters to old (non-WCF) Adapters. Next step is adding the appropriate adapters in BizTalk Administration Console.

You have to go navigate to Platform Settings –> Adapters and then right click New –> Adapter.

Note: When installing the adapter pack you do not see a new adapter show up under the Adapters folder or in the drop down for Add new Adapter. The adapter pack installs new custom bindings.The BizTalk "adapter" for instance for the Oracle database shows up in BizTalk as the OracleDBBinding. The standard bindings are wsHttpBinding, netTcpBinding, etc. OracleDbBinding and SqlBinding are themselves custom bindings.

You can also download the interactive or normal poster of BizTalk Adapter 2010. The BizTalk Adapter Pack is included with BizTalk Server 2010 and you can download the BizTalk Server 2010 Evaluation if you want to try it out.

When completing this process you also have installed all the prerequisites for AppFabric Connect. When starting a WCF Workflow Service you will see BizTalk Mapper.

On AppFabric connect see also Thiago post: Microsoft AppFabric Connect in BizTalk 2010 – licensing and installation, what’s the deal?

Configure BizTalk Server SQL Jobs

By default, the following BizTalk jobs aren’t configured and enabled upon installation:

• Backup BizTalk Server (BizTalkMgmtDb)
• DTA Purge and Archive (BizTalkDTADb)
• MessageBox_Message_Cleanup_BizTalkMsgBoxDb

If you want these functionalities you must configure and enabled them.

How to configure Backup BizTalk Server (BizTalkMgmtDb)

This Job consists of four steps:

• Step 1 – Set Compression Option – Enable or disable compression during backup.
• Step 2 – BackupFull – Performs full database backups of the BizTalk Server databases.
• Step 3 – MarkAndBackUpLog – Backs up the BizTalk Server database logs.
• Step 4 – Clear Backup History – Specifies for how long the backup history is kept.

Step 1 – Set Compression Option

This job step calls a stored procedure named sp_SetBackupCompression on the BizTalk management database (BizTalkMgmtDb by default) to set the value on the adm_BackupSettings table.

The original script is:

• exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */

The stored procedure has only one parameter:

• @bCompression: By default, Set Compression Option is set to 0, which makes backup compression off by default. To change the default, change Set Compression Option to 1.

Change the script to:

• exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /*0 - Do not use Compression, 1 - Use Compression */

For more information see Tiago Almeida article: BizTalk Server 2010 Backup Compression

Step 2 – BackupFull

The original script is:

• exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */

Where:

• Frequency (@Frequency): The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
• Name (@MarkName): The default is BTS. The name is used as part of the backup file name.
• Location of backup files (@BackupPath): Replace ‘<destination path>’ with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases.

There are also three optional parameters:

• Force full backup after partial backup failures (@ForceFullBackupAfterPartialSetFailure): The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached. Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
• Local time hour for the backup process to run (@BackupHour): The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server computer and will run at midnight UTC time (0000). If you want to backup to run at a particular hour in the time zone of the BizTalk Server computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.
• Use local time (@UseLocalTime): This is an extra parameter that you can also add that tells the procedure to use local time
• The default value is 0
  • If set to 0, then it uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933
  • If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933

Change the script to:

• exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<your_destination_path>\BizTalk Database\Full' /* location of backup files */

Step 3 – MarkAndBackUpLog

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up.

The original script is:

• exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '<destination path>' /* location of backup files */

Where:

• @MarkName: Log mark name is part of the naming convention for backup files:
  • <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
• @BackupPath:. You must change the destination path this to a valid one. It may be local or a UNC path to another server.

There is also one optional parameter:

• @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time
  • exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */,'<destination path>'  /*location of backup files */ , 1

Change the script to:

• exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '< your_destination_path>\BizTalk Database\Logs' /* location of backup files */

Step 4 – Clear Backup History

The original script clear out the instances in the MarkLog table older than 14 days:

• exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14

Where:

• @DaysToKeep: specifies how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps to maintain the Adm_BackupHistory table at an appropriate size. The default value for the DaysToKeep parameter is 14 days.

There is also one optional parameter:

• @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time
  • exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 , @UseLocalTime =1

In this particular case I like to leave the default settings.

Note: This job step does not provide functionality for deleting backup files that have accumulated over time.

You can solve this problem by implementing you custom sp_DeleteBackupHistory:

• Start SQL Server Management Studio, and then connect to the BizTalk Management Database. By default, this database is named BizTalkMgmtDb.
• Click New Query to open a Query Editor window.
• Run the following Transact-SQL script to create the sp_DeleteBackupHistoryAndFiles stored procedure - see more information here.
• Change the “Clear Backup History” step of the Backup BizTalk Server job so that it calls the sp_DeleteBackupHistoryAndFiles stored procedure instead of the sp_DeleteBackupHistory stored procedure.

How to configure DTA Purge and Archive (BizTalkDTADb)

This job automates the archiving of tracked messages and the purging of the BizTalk Tracking database to maintain a healthy system and to keep the tracking data archived for future use.

And it’s configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job:

• @nLiveHours: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default is 0 hours.
• @nLiveDays: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default interval is 1 day.
• @nHardDeleteDays: All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged. Default is 30 days.
• @nvcFolder: Folder in which to put the backup files.
• @nvcValidatingServer: Server on which validation will be done. NULL value indicates no validation is being done. Default is NULL.
• @fForceBackup: Default is 0. This is reserved for future use.
• For more information: How to Purge Data from the BizTalk Tracking Database

The original script after installing BizTalk looks like this:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

This means that:

• Any completed instance that is older than the live days plus live hours will be deleted, as will any associated data.
• Any data older than the HardDeleteDays will be deleted.

Normally I use these configurations for production environments:

• exec dtasp_BackupAndPurgeTrackingDatabase 0, 10, 20, '<destination path>', null, 0

For more information: How to Configure the DTA Purge and Archive Job

However in a development machine we don’t need to maintain the archived tracking data, so I just purge it periodically. BizTalk gives you the option to Archive and Purge the tracking data or just simple purge the data without archiving:

• Change the SQL Statement inside “DTA Purge and Achieve” SQL Job to
  • declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

MessageBox_Message_Cleanup_BizTalkMsgBoxDb

Removes all messages that are not referenced by any subscribers in the BizTalkMsgBoxDb database tables.

Important: This job is also started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Therefore, we recommend that you disable this job.

Enable TCP/IP, Named Pipes protocols and DTC on the Local Host Server

This are two key steps to facilitate transactions and improve performance between SQL Server and BizTalk Server.

Enable TCP/IP and Named Pipes

To facilitate transactions between SQL Server and BizTalk Server, you must enable TCP/IP and Named Pipes in SQL Server.

To enable TCP/IP and named Pipes

• Click Start, click All Programs, click Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.
• In the left-hand pane, expand SQL Server Network Configuration.
• Click Protocols for MSSQLSERVER.
• Verify that both TCP/IP and Named Pipes are enabled.
  • If either is not, follow these steps:
  • Right-click the protocol, and then click Enable.
  • Note: the ‘Shared Memory’ protocol should be disabled

• In the left-hand pane, click SQL Server Services.
• In the right-hand pane, right-click SQL Server (MSSQLSERVER), and click Restart.

• You must restart also the NS$BAMAlerts service.
• Close the Configuration Manager.

Enable DTC on the Local Host Server

To facilitate transactions between SQL Server and BizTalk Server, you must enable Microsoft Distributed Transaction Coordinator (MS DTC) on the Local Host Server (LHS).

To enable Distributed Transaction Coordinator (DTC) on the LHS:

• Click Start, click Run, type dcomcnfg and then click OK to open Component Services.

• In the console tree, expand Component Services, expand Computers, expand My Computer, expand Distributed Transaction Coordinator, and then click Local DTC.
• Right-click Local DTC and click Properties to display the Local DTC Properties dialog box.

• Click the Security tab.
• Ensure that each of the following four options is selected, and all others are cleared:
  • Network DTC Access
  • Allow Inbound
  • Allow Outbound
  • No Authentication Required

• Click OK to close the Local DTC Properties dialog box. If prompted to restart the MSDTC service, click Yes.
• Close Component Services.
• Click Start, point to Administrative Tools, and then click Windows Firewall with Advanced Security.

• In Windows Firewall with Advanced Security, click Inbound Rules.
• In the Inbound Rules pane, right-click in all Distributed Transaction Coordinator rules and then click Enable Rule.

• In Windows Firewall with Advanced Security, click Outbound Rules.
• In the Outbound Rules pane, right-click Distributed Transaction Coordinator and then click Enable Rule.

• Close Windows Firewall.
• Click Start, point to Administrative Tools, and then click Services
• And restart the following services:
  • Right-click COM+ System Application, click Restart, and wait for the service to restart.
  • Right-click and restart the Distributed Transaction Coordinator service.
  • Right-click and restart the SQL Server (MSSQLSERVER) service.
• Close Services window.

Sources

Sandro Pereira
DevScope | MVP & MCTS BizTalk Server 2010
http://sandroaspbiztalkblog.wordpress.com/ | @sandro_asp

---

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.

Read suggested related topics:

• How to Install BizTalk Server 2010 in a Basic Multi-Computer Environment
• Recommendations for Installing, Sizing, Deploying, and Maintaining a BizTalk Server Solution
• Install and Configure BAM (Business Activity Monitoring) in a Multi-Computer Environment

---