SQL Server and BizTalk Server 2002 Database Interactions

To avoid unnecessary disk space allocation, use the Truncate log on checkpoint and Auto shrink features of Microsoft SQL Server. Otherwise, the SQL Server logs can consume large amounts of disk space. However, you need to install BizTalk Server 2002 before you can complete this procedure. For more information, see Avoid unnecessary disk space allocation. For more information about installing BizTalk Server 2002, see Installation Instructions.

BizTalk Server 2002 uses Windows Authentication to access the BizTalk Messaging Management, Shared Queue, and Tracking databases and the BizTalk Document Tracking Web application. If you plan to install BizTalk Server and SQL Server on two different computers, the account you are using must have Administrative permissions on the computer running SQL Server and the computer running BizTalk Server.

Ee251049.international_issues(en-US,BTS.10).gif International Issue

  • BizTalk Server cannot access databases created by a different language version of BizTalk Server.

The following procedures are necessary to establish permissions for remote SQL Server installations and to establish permissions on the remote server to the appropriate BizTalk Server databases.

Remote SQL Server login account

Ee251049.note(en-US,BTS.10).gif Note

  • By default, if SQL Server and BizTalk Server 2002 are both installed on the local computer, and the BizTalk Server Messaging service account has been added to the Administrators group on the local computer, then it already has System Administrator permissions in SQL Server and the following procedure is not necessary. If, however, you are installing BizTalk Server and SQL Server on separate computers, you must perform the following procedure in order for BizTalk Server 2002 to be able to access SQL Server.
  1. On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the server to which you want to add a SQL Server login account, and then expand Security.

  3. Right-click Logins and click New Login.

    The SQL Server Login Properties - New Login dialog box appears.

  4. On the General tab, in the Name box, specify a domain and Windows account name.

    For example: <DOMAIN>\<service account>, specifying the name of the account under which BizTalk Messaging Services will run.

  5. On the Server Roles tab, in the Server Roles list, select the System Administrators check box.

Ee251049.note(en-US,BTS.10).gif Note

  • Administrative privileges are not required for the service account for the BizTalk Messaging Service to run properly.
  1. Click OK to exit the SQL Server Login Properties page.

Establish remote database permissions

After BizTalk Server 2002 is installed, you will need to specify the databases that can be accessed by the SQL Server login account. Complete the following steps to give the SQL Server login account permissions to the BizTalk Messaging Management, Shared Queue, and Tracking databases.

Ee251049.note(en-US,BTS.10).gif Note

  • This procedure cannot be completed until BizTalk Server has been installed.
  1. On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the server that contains the BizTalk Server databases, expand Security, and then click Logins.

  3. In the details pane, double-click the SQL Server login account that you created for the BizTalk Server Messaging service account.

    The SQL Server Login Properties page appears.

  4. On the Database Access tab, select the InterchangeBTM check box.

    The account name you specified appears in the User box. The Permit in Database Role box automatically appears at the bottom of this page.

  5. In the Permit in Database Role box, select the db_owner and dta_ui_role check boxes.

  6. Repeat steps 4 and 5 for the InterchangeDTA and InterchangeSQ databases, selecting only db_owner in the Permit in Database Role box.

    Ee251049.note(en-US,BTS.10).gif Note

    • During setup, the BizTalk Messaging Management database object name defaults to InterchangeBTM; the Tracking database object name defaults to InterchangeDTA; and the Shared Queue database object name defaults to InterchangeSQ. You might have renamed these default database object names during setup.

Did you find this information useful? Please send your suggestions and comments about the documentation to BizTalk Server Documentation Feedback@microsoft.com



Show: