Installing BizTalk Server 2010 in a Basic Multi-Computer Environment: Preparing and Install SQL Server 2008 R2 machine (Part 4)

Posted: January 5, 2012 in BizTalk
Tags: , ,

This part of the article will focus on installing the SQL Server machine and all necessary requirements and components

Important considerations before set up the servers

Check if all the considerations described above are implemented:

  • Join the BizTalk Administrator Group to Local Administrators Group
  • Install Critical Windows Updates
  • Disable IPv6
  • Turn off Internet Explorer Enhanced Security Configuration
  • Disable User Account Control
  • Install .NET Framework 3.5 SP1
  • Configure Microsoft Distributed Transaction Coordinator (MS DTC)
  • Enable Network COM+ access
Install SQL Server 2008 R2

In this step, we will install SQL Server 2008 R2 on the machine that will host the BizTalk databases

The steps are described in my blog:

The only recommendation that I made is to change the user running the SQL services to a domain account in the step 11, Microsoft recommends that you use a separate account for each SQL Server service (see SQL Server Service Accounts topic described earlier).

Note: Using SQL Server Express Edition in a production environment is not recommended. The Express edition does not include certain features needed by BizTalk Server.

Install Service Pack 1 for Microsoft SQL Server 2008 R2

Service Pack 1 for Microsoft SQL Server 2008 R2 can be found here.

SQL Server 2008 R2 service packs are cumulative updates and upgrade all service levels of SQL Server 2008 R2 to SP1. This service pack contains the cumulative updates of SQL Server 2008 R2 cumulative update packages 1 thru 6. You can use these packages to upgrade any of the following SQL Server 2008 R2 editions:

  • SQL Server 2008 R2 Datacenter Edition
  • SQL Server 2008 R2 Enterprise Edition and Developer Edition
  • SQL Server 2008 R2 Standard Edition
  • SQL Server 2008 R2 Web Edition
  • SQL Server 2008 R2 Workgroup Edition
  • SQL Server 2008 R2 Express Edition

You should install this Service Pack.

SQL Reporting Services Note

One of the changes in the SQL Server Reporting 2008 Reporting Services is the removal of the Internet Information Services (IIS) dependency.

The first reason Microsoft moved away from IIS is for better configuration. IIS was built several years ago to do lots of things, including serving up web pages from the file system, host ISAPIs, Internet printing, etc. SSRS 2005 uses very little of IIS, mainly as a port 80 listener, client authentication, and process hosting. But when you install IIS, you got a bunch of stuff that RS really doesn’t need at all. It turns out that all of the parts of IIS that we didn’t use got configured in very strange ways and made RS not work.

This is especially true in the case where there were other IIS applications on the same server as RS. Not only did other application settings conflict with RS, any application running under the same Application Pool identity is granted full access to the RS catalog. By moving out of IIS, so Microsoft have actually significantly reduced the surface area that you to configure and secure.

The second reason for the change is a need for better resource management. IIS has a pretty simple model for thread, memory, and state management. This works well for most applications that are serving up static or dynamic HTML pages. However, it wasn’t really built to host an enterprise server responsible for serving up reports with gigabytes of data in multiple formats to thousands of users simultaneously. By hosting the service engine ourselves, Microsoft gain much more control over resource management. For example, RS 2008 has a new on-demand processing model to ensure that no report executions will run out of memory. It does this by paging and releasing allocated memory to the disk when it gets memory pressure notifications. This would have been very difficult to implement using the IIS hosting model.

Reference:

Configure SQL Server for Remote Connections

In order for the BizTalk servers to connect to SQL Server, remote connections need to be enabled.

To do so you need to:

  • Run Microsoft SQL Server Management Studio and connect to the right instance.
  • Once the tool opens, right click over instance name and select Properties
  • Go to Connections page and select the option “Allow remote connections to this server”

Configure-SQL-Server-for-Remote-Connections

Configured SQL Server protocols – Disable the Shared Memory Protocol, Enable TCP/IP and Named Pipes

The steps are described in my blog:

Click ‘Start’, ‘All Programs’, click ‘Microsoft SQL Server 2008 R2’, click ‘Configuration Tools’, then click ‘SQL Server Configuration Manager’

In the SQL Server Configuration Manager, click on the ‘SQL Server Network Configuration’ node and expand it, under ‘Protocols for MSSQLSERVER’ right-click the ‘Shared Memory’ protocol and disable it. Right-click the TCP/IP protocol and enable it and repeat the process for Named Pipes.

Configure-SQL-Network-protocols

Configure a Server to Listen on a Specific TCP Port

This is one request of my client.

If enabled, the default instance of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact 3.5 SP2 are configured for dynamic ports. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

To assign a TCP/IP port number to the SQL Server Database Engine

  • In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
  • In the TCP/IP Properties dialog box, on the Protocol tab, change the property “Listen All” to “No”(This is one request of my client – just want to listen in one IP set in the machine)

Configure-SQL-Server-Network-Configuration-TCP-IP-Properties

  • In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.
  • If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  • In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

Configure-SQL-Server-Network-Configuration-TCP-IP-Properties-IPAddress

  • In the console pane, click SQL Server Services.
  • In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

  • Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
  • Create an alias on the client, specifying the port number.
  • Program the client to connect using a custom connection string.

Reference: How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Configure SQL Analysis Server to Listen on a Specific TCP Port

This is one request of my client.

You will be able to configure the port that SQL Analysis Server is listening by:

  • Opening the SQL Server Management Console
  • Connect to Analysis Server

Connect-to-Analysis-Server

  • Right Click on SQL instance and select “Properties” option
  • On “General” tab, go to “Port” and set the port that you want to configure, in my case “2382”.

Analysis-Server-General-Port-propertie

  • Click “Ok” and restart the service.
Configuring Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall or network address translation firewalls

DTC uses Remote Procedure Call (RPC) dynamic port allocation. By default, RPC dynamic port allocation randomly selects port numbers above 1024.

You can configure DTC to communicate through firewalls, including network address translation firewalls, just follow these steps:

  • Go to Component Services
  • On Component Services console, expand “Component Services
  • Right click over “My Computer” and select Properties option
  • Go to “Default Protocols” tab select “Connection-oriented TCP/IP” option in DCOM protocols

My-Computer-Default-Protocols

  • Click “Properties” button
  • On “Properties for COM Internet Services” windows, click “Add” button to set a port range
  • Specify the range of ports you want to use, I set the following range: “5000-8976”

Properties-for-COM-Internet-Services

  • Click “Ok”

Properties-for-COM-Internet-Services-conf

  • Ensure that “Internet range” is check in both options: “Port range assignment” and “Default dynamic port allocation”
List of ports between BizTalk Server and SQL Server

The following table lists the ports that we need to configured in firewall or request ACLs to network IT.

Direction:

  • Source Host: BizTalk Server
  • Destination Host: SQL Server
Port No. Protocol /Service Description of Service
135 TCP RPC: Transacted connection to SQL ServerTransacted connection to SQL Server for SQL adapter
5000-8976(you may extend more ports for performance) TCP Secondary RPC portsSecondary RPC ports for SQL adapter
1433 TCP Microsoft SQL Server port used typically for remote connections to the database.For the Enterprise Single Sign-On service to connect to the SSO databaseTo create and configure the MessageBox database

To create and configure the Tracking database

1434 UDP Microsoft SQL Monitor use in monitoring Microsoft SQL Databases.
2382 TCP To update and retrieve information from the BAM Analysis database
445 TCP To create the OLAP data file (.mdb) on the remote computer
2725 TCP For data retrieval for analysis (PivotTable® reports)
80 TCP Reporting Services

Reference:

Configure Firewall on SQL Server machine
Inbound Rules

In order to make DCOM applications work in Windows Server 2008 R2 you need to add the COM+ network access as show below:

  • Open “Server manager” console and expand “Configuration” option in the left tree.
  • Expand “Windows Firewall with Advanced Security” and select “Inbound Rules” option.
  • Enable the following inbound rules by select them and right click and select the option “Enable Rule” option
    • COM+ Network Access (DCOM-in)
    • COM+ Remote Administrator (DCOM-In)

Firewall-Inbound-Rules-Enable-Rule

Firewall-Inbound-Rules-Enable-Rule-result

Also we enable Microsoft Distributed Transaction Coordinator (MS DTC) to communicate through a firewall with another MS DTC.

Repeat the previous steps and Enable the followings rules:

  • Distributed Transaction Coordinator (RPC)
  • Distributed Transaction Coordinator (RPC-EPMAP)
  • Distributed Transaction Coordinator (TCP-In)

Firewall-Inbound-Rules-Enable-Rule-DTC-result

Now we need to create new rules for each of port described in topic “List of ports between BizTalk Server and SQL Server” to allow BizTalk Server to communicate with SQL Server machine. To create a new Inbound Rule, follow these steps:

  • Right click on “Inbound Rules” on the left tree and select the option “New Rule”

Firewall-Inbound-Rules-New-Rule

  • On “New Inbounded Rule Wizard” windows in step “Rule Type” select "Port" option
  • In step "Protocol and Ports" select the protocol requested and specify the port or list of ports
  • In step "Action" select "Allow the connection" option
  • In step "Profile" leave the default options
  • In step "name" set the name and description of the rule
Outbound Rules

First thing is to enable the following outbound rules:

  • Distributed Transaction Coordinator (TCP-Out) – this will allow Outbound traffic for the Distributed Transaction Coordinator.

Firewall-outbound-Rules-Enable-Rule-DTC

To do this follow the steps described in Inbound Rules, the steps are the same the only difference is that we are dealing with Outbound Rules

The second is to create a new outbound rule for RPC ports:

  • Right click on “Outbound Rules” on the left tree and select the option “New Rule”
  • On “New Outbounded Rule Wizard” windows in step “Rule Type” select "Port" option
  • In step "Protocol and Ports"
    • Select the protocol: TCP
    • Specify the range of ports: 5000-8976
  • In step "Action" select "Allow the connection" option
  • In step "Profile" leave the default options
  • In step "name" set the name and description of the rule

Firewall-outbound-Rules-New-Rule-RPC

Related Links

Tags: BizTalk 2010 | Installation | Configuration

About these ads
Comments
  1. Great post! But just as a remark, there is a typo, the default port where SSAS is running is 2383, not 2382.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s