Thursday, November 12, 2009

Use an existing SQL Server for your BizTalk environment

Recently i had the ability to use 2 dedicated development machines, one for setting up BizTalk and i figured to use the other for SQL Server.

I sounds easy enough, be it that there are some steps that you should not forget.

Possible error to look for: “Cannot open database BizTalkMgmtDb on server <dummy>.Verify that you have the required security permissions and that communication between Distributed Transaction Coordinator services on the machines involved is not prevented by current DTC security,   firewall or authentication settings.”

Setting up the machines

Preconditions:

o SQL Server is installed

o BizTalk is installed but not configured

Setting up the SQL Server machine

1) Start ‘SQL Server Surface Area Configuration’

o Enable remote connections

(Choose: TCP/IP or TCP/IP and named pipes, disable shared memory)

o Start the SQL Agent

o Start the SQL Browser

2) Start ‘SQL Server Configuration Management’

o Goto ‘Protocols for MSSQL Server’ to enable TCP/IP

· Set Active to ‘Yes’

· Set Enabled to ‘Yes’

· Enable named pipes

3) Configure DTC

a) Start ‘Component Services’

In the control panel, browse to ‘Administrative Tools’\’Component Services’

Right click on properties on the context menu under:

\Component Services\Computers\My Computer\Properties

b) Configure MSDTC

Check the following settings:

· Allow remote connections (remote administration is not required)

· No Authentication required

· Enable XA Transactions

· Enable TIP (not sure is this is required)

for more info see: http://msdn.microsoft.com/en-us/library/aa544733.aspx

Setting up the BizTalk machine

o Manually create the required SSO groups

o For SSO: add user to the SSO Administrators group

o Add authenticated database users to the BizTalk Administrators group

o Repeat steps 3a-b for setting up DTC

No comments: