Software Prerequisites

Software Prerequisites When Using the SQL Adapter Through a .NET Application

Software Prerequisites When Using the SQL Adapter with BizTalk Server

Supported SQL Server Versions and Client Libraries

Compatibility Matrix Between 32-bit and 64-bit Versions of the Adapter, BizTalk Server, and Operating System

Installing the SQL Adapter

Scenarios for Installing the SQL Adapter on 32-bit and 64-bit Platforms

Installing the SQL Adapter in Interactive Mode

Installing the SQL Adapter in Silent Mode

After Installing the SQL Adapter

Modifying the SQL Adapter Installation

Removing the SQL Adapter

After Removing the SQL Adapter

Accessing the Adapter Forum

Copyright

Welcome to the Microsoft BizTalk Adapter for SQL Server Installation Guide. This document provides software requirements and detailed instructions for installing the SQL adapter.

For the latest version of this installation guide, go to http://go.microsoft.com/fwlink/?LinkID=188970.

Software Prerequisites

The SQL adapter can be used with:

  • A .NET application

  • Microsoft BizTalk Server

Based on how you use the adapters, the required software will vary.

Software Prerequisites When Using the SQL Adapter Through a .NET Application

You must ensure the following software is installed on the computer where you will be writing .NET applications on top of the SQL adapter. We recommend installing the software in the same order as listed here.

  • Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4

    Important

    .NET Framework 4 is essential to use BizTalk Adapter Pack with BizTalk Server 2010. However, you can use .NET Framework 3.5 SP1 and .NET Framework 4 to write.NET applications (channel/proxy) for connecting using adapters.

  • Microsoft Visual Studio 2010

  • The Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK (WCF LOB Adapter SDK) 2010. You can download WCF LOB Adapter SDK 2010 from http://go.microsoft.com/fwlink/?LinkID=189401.

  • SQL Server client libraries. For the list of SQL Server client libraries you must install on the same computer where you install the SQL adapter, see Supported SQL Server Versions and Client Libraries.

Software Prerequisites When Using the SQL Adapter with BizTalk Server

You must ensure the following software is installed on the computer where you will be using the SQL adapter with BizTalk Server. We recommend installing the software in the same order as listed here.

  • Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4

    Important

    .NET Framework 4 is essential to use BizTalk Adapter Pack with BizTalk Server 2010. However, you can use .NET Framework 3.5 SP1 and .NET Framework 4 to write.NET applications (channel/proxy) for connecting using adapters.

  • Microsoft Visual Studio 2010

  • The Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK (WCF LOB Adapter SDK) 2010. You can download WCF LOB Adapter SDK 2010 from http://go.microsoft.com/fwlink/?LinkID=147367.

    Important
    • You must also install a BizTalk add-in (Consume Adapter Service BizTalk Project Add-in) for Visual Studio that is shipped with the WCF LOB Adapter SDK. To install this add-in, you must perform a Custom or Complete installation of the WCF LOB Adapter SDK. Under the Custom option, select BizTalk Server Addin.
    • Also, to install the add-in, you must install Visual Studio before installing the WCF LOB Adapter SDK.
  • Microsoft BizTalk Server 2010

  • SQL Server client libraries. For the list of SQL Server client libraries you must install on the same computer where you install the SQL adapter, see Supported SQL Server Versions and Client Libraries.

Top of page

Supported SQL Server Versions and Client Libraries

The following section presents the supported SQL Server versions and the client libraries required by the SQL adapter.

  • Supported server versions: SQL Server 2000, SQL Server 2005, SQL Server 2008 SP1, SQL Server 2008 R2

  • Supported client versions: NET Framework 3.5 SP1 and .NET Framework 4 bundle the client DLLs required to connect to SQL Server. You do not need to explicitly install any client DLLs on your computer.

  • Required drivers:

    • For SQL Server 2005: If you create User Defined Types (UDTs) in SQL Server, make sure the respective assemblies for the UDTs are added to the GAC.

    • For SQL Server 2008 SP1 or SQL Server 2008 R2:

      • If you use the UDTs shipped with SQL Server 2008 SP1 or SQL Server 2008 R2 (for example, Geography), make sure the following DLLs are present on the computer where you use the adapter to perform operations on SQL Server. For example, if you create BizTalk projects to perform operations on SQL Server, these DLLs must be present on the computer where BizTalk Server is running.

        • Make sure Microsoft.SqlServer.Types.dll is added to the GAC.

        • Make sure SqlServerSpatial.dll is available in the System32 folder.

        You can install these DLLs on the computer by running the SQL Server 2008 SP1 or SQL Server 2008 R2 setup and selecting Management Tools – Basic or Management Tools – Complete in the Feature Selection page of the wizard.

      • If you use the adapter to perform operations on columns of FILESTREAM data types, make sure you have SQL Client Connectivity SDK installed. You can install the SQL Client Connectivity SDK by running the SQL Server 2008 SP1 or SQL Server 2008 R2 setup and selecting SQL Client Connectivity SDK in the Feature Selection page of the wizard. The adapter uses the sqlncli10.dll, installed with the SQL Client Connectivity SDK, to perform FILESTREAM operations.

      • If you create your own UDTs in SQL Server, make sure the respective assemblies for the UDTs are added to the GAC.

Top of page

Compatibility Matrix Between 32-bit and 64-bit Versions of the Adapter, BizTalk Server, and Operating System

The following table presents a compatibility matrix between the 32-bit and 64-bit versions of the operating system, SQL adapter, and BizTalk Server.

SQL Adapter + BizTalk Server

Operating System

32-bit

64-bit

32-bit 64-bit

Yes

N/A

Yes

Yes

Legend

  • Yes = Supported

  • No = Not supported

  • N/A = Not Applicable

For more information about the supported installation scenarios for the 32-bit and 64-bit SQL adapter, see Scenarios for Installing the SQL Adapter on 32-bit and 64-bit Platforms.

Top of page

Installing the SQL Adapter

Make sure you have the prerequisites installed before installing the SQL adapter. For more information, see Software Prerequisites.

You can install the SQL adapter in the following two ways:

  • In interactive mode by running the setup wizard.

  • In silent mode by using the command line.

    Important

    You must have administrative privileges on the computer where you will install the SQL adapter, irrespective of whether you are installing by using the wizard or the command line.

Scenarios for Installing the SQL Adapter on 32-bit and 64-bit Platforms

With BizTalk Server, the SQL adapter can be used for:

  • Visual Studio design time (when generating metadata for operations).

  • BizTalk Server Administration console design time (for creating physical ports).

    Note

    BizTalk Server Administration console runs as a 32-bit Microsoft Management Console (MMC) application.

  • BizTalk run time (when sending and receiving messages from LOB applications).

You can have an installation where you perform all these tasks on the same computer or different computers. Because both Visual Studio and BizTalk MMC are 32-bit processes, you must install the 32-bit SQL adapter on the computer where you want to perform the design-time tasks. The following scenarios are supported for installing the SQL adapter on 32-bit and 64-bit platforms.

Scenarios for Installing the SQL Adapter on a 32-bit Platform

The following scenarios are supported when installing the SQL adapter on a 32-bit platform.

Visual Studio design time BizTalk MMC design time BizTalk run time Visual Studio design time
and/or
BizTalk MMC design time + BizTalk run time
  • Install 32-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

  • Install 32-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

  • Install 32-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

  • Install 32-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

Scenarios for Installing the SQL Adapter on a 64-bit Platform

The following scenarios are supported when installing the SQL adapter on a 64-bit platform.

Note

On any computer where you want to perform design-time tasks using either Visual Studio or BizTalk MMC, you must install the 32-bit SQL adapter.

Visual Studio design time BizTalk MMC design time BizTalk run time Visual Studio design time
and/or
BizTalk MMC design time + BizTalk run time
  • Install 64-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

  • Install 64-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

For a 32-bit BizTalk process:

  • Install 64-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

For a 64-bit BizTalk process:

  • Install 64-bit WCF LOB Adapter SDK.

  • Install 64-bit SQL adapter.

  • Install 64-bit client and other required DLLs.

For a 32-bit BizTalk process:

  • Install 64-bit WCF LOB Adapter SDK.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

For a 64-bit BizTalk process:

  • Install 64-bit WCF LOB Adapter SDK.

  • Install 64-bit SQL adapter.

  • Install 64-bit client and other required DLLs.

  • Install 32-bit SQL adapter.

  • Install 32-bit client and other required DLLs.

Installing the SQL Adapter in Interactive Mode

Perform the following steps to install the SQL adapter using the setup wizard.

To install the SQL Adapter in interactive mode
  1. Download the Microsoft BizTalk Adapter for SQL Server self-extracting EXE file to your computer. You can download this from http://go.microsoft.com/fwlink/?LinkID=188970.

  2. Run the EXE file to extract the SQLAdapter folder.

  3. In the SQLAdapter folder, double-click SqlAdapterSetup.msi (for an x86-based platform) or SqlAdapterSetup64.msi (for an x64-based platform).

    Note

    If you are installing the SQL adapter on a virtual machine, the setup wizard might not proceed beyond a dialog box informing that the setup is checking for available disk space. In such cases, we recommend that you use the silent installation option. For more information, see Installing the SQL Adapter in Silent Mode.

  4. Read the information on the Welcome screen, and then click Next.

  5. Read and accept the end-user license agreement (EULA), and then click Next.

  6. In the Destination Folder dialog box, specify the location where you want to install the SQL adapter, click Next, and then click Install.

  7. In the Customer Experience Improvement Program dialog box, specify whether you would like to enroll for the Customer Experience Improvement Program (CEIP). As part of CEIP for SQL adapter, you will share the following data with Microsoft:

    • Data related to the computer hardware on which you are installing the SQL adapter.

    • Data related to the SQL Server versions you are using to connect using the SQL adapter.

    Specify your choice and click OK. For more information about CEIP, see http://go.microsoft.com/fwlink/?LinkID=144699.

    Note

    You can always change your preference regarding enrolling for CEIP by running the Setup in Repair mode from the Control Panel.

  8. Click Finish.

Installing the SQL Adapter in Silent Mode

The following procedure demonstrates how to perform a silent installation of SQL adapter using the msiexec command.

To install the SQL Adapter in silent mode
  1. Download the Microsoft BizTalk Adapter for SQL Server self-extracting EXE file to your computer. You can download this from http://go.microsoft.com/fwlink/?LinkID=188970.

  2. Run the EXE file to extract the SQLAdapter folder.

  3. Open a command prompt, and navigate to the SQLAdapter folder.

  4. Run the following command to install the SQL adapter:

    Note

    To perform silent installation on an x64-based platform, in the following commands, replace SqlAdapterSetup.msi with SqlAdapterSetup64.msi.

    msiexec /i SqlAdapterSetup.msi /qn

    You can also choose to enroll for CEIP as part of the silent installation. As part of CEIP for SQL adapter, you will share the following data with Microsoft:

    • The computer hardware on which you are installing the SQL adapter.

    • The SQL Server versions you are using to connect using the SQL adapter.

    To enroll for CEIP, run the following command:

    msiexec /i SqlAdapterSetup.msi /qn CEIP_OPTIN=true

    By default, the option is false. For more information about CEIP, see http://go.microsoft.com/fwlink/?LinkID=144699.

    For more information about the msiexec command, type msiexec on the command line and press ENTER or see http://go.microsoft.com/fwlink/?LinkID=103199.

After Installing the SQL Adapter

If the SQL adapter setup fails to register the bindings during installation, you must register the bindings manually using the following procedure.

Registering the Bindings

Perform these steps only if the setup wizard fails to register the adapter bindings in the machine.config file.

To register the bindings
  1. Navigate to the machine.config file on the computer. For example, on a 32-bit platform, the machine.config is available under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG.

    • For Microsoft .NET Framework 3.5 SP1, <version> is the version v2.0.50727 of the .NET Framework.

    • For Microsoft .NET Framework 4, <version> is the version v4.0.30319 of the .NET Framework.

  2. Open the file using a text editor.

  3. To register the adapter bindings:

    1. Search for the element "system.serviceModel" and add the following under it:

      <client>
        <endpoint binding="sqlBinding" contract="IMetadataExchange" name="mssql" />
      </client>
    2. Search for the element "bindingElementExtensions" under system.serviceModel\extensions.

    3. Add the following section under the "bindingElementExtensions" node.

      For the SQL adapter, add:

      <add name="sqlAdapter" type="Microsoft.Adapters.Sql.SqlAdapterBindingElementExtensionElement,Microsoft.Adapters.Sql, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
    4. Search for the element "bindingExtensions" under system.serviceModel\extensions.

    5. Add the following section under the "bindingExtensions" node.

      For SQL adapter, add:

      <add name="sqlBinding" type="Microsoft.Adapters.Sql.SqlAdapterBindingCollectionElement,Microsoft.Adapters.Sql, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
    Note

    For information about how to determine the public key, see Determining the Public Key and Version.

  4. Save and close the machine.config file.

Determining the Public Key and Version

Perform the following steps to determine the public key and version for the SQL adapter.

To determine the public key
  1. Navigate to the Windows directory, typically C:\WINDOWS\assembly.

  2. Right-click the DLL for which you want the public key, and then select Properties. The following table lists the name of the DLLs for the SQL adapter.

    Adapter Name of the DLL

    SQL adapter

    Microsoft.Adapters.Sql.dll

  3. On the General tab, the value against the Public Key Token label specifies the public key for the DLL. Similarly, the value against the Version label specifies the version number for the DLL.

  4. Copy the public key, and then click Cancel.

Top of page

Modifying the SQL Adapter Installation

Perform the following steps to modify the SQL adapter installation. Make sure you have the SQL adapter installed before you run the setup wizard to modify the SQL adapter installation.

You can modify the SQL adapter installation in the following two ways:

  • In interactive mode by running the setup wizard.

  • In silent mode by using the command line.

To modify the SQL Adapter installation in interactive mode
  1. Click Start, and then click Control Panel.

  2. In Control Panel, double-click Programs and Features.

  3. In the Programs and Features window, select Microsoft BizTalk Adapter for SQL Server, and then click Change.

  4. Read the information on the Welcome screen, and then click Next.

  5. In the Change, repair, or remove installation dialog box, click Repair.

  6. In the Ready to repair Microsoft BizTalk Adapter for SQL Server dialog box, click Repair.

  7. If required, change your preferences regarding opting for CEIP, and then click OK. For more information about CEIP, see http://go.microsoft.com/fwlink/?LinkID=144699.

  8. Click Finish.

To modify the SQL Adapter installation in silent mode
  1. Open a command prompt, and navigate to the root directory of the SQL adapter installer.

  2. Run the following command:

    Note

    To modify the SQL adapter installation in silent mode on an x64-based platform, in the following commands, replace SqlAdapterSetup.msi with SqlAdapterSetup64.msi.

    msiexec /i SqlAdapterSetup.msi /qn /f
    Important

    While modifying the SQL adapter installation in the silent mode, you cannot change your preferences for opting in or out of CEIP. The preferences will remain the same as you specified during the installation, even if you explicitly set the CEIP_OPTIN to true or false.

    For more information about the msiexec command type msiexec on the command line and press ENTER or see http://go.microsoft.com/fwlink/?LinkID=103199.

Top of page

Removing the SQL Adapter

Perform the following steps to remove the SQL adapter from your computer. Make sure you have the WCF LOB Adapter SDK installed before you run the setup wizard to remove the SQL adapter.

You can remove the SQL adapter in the following two ways:

  • In interactive mode by running the setup wizard.

  • In silent mode by using the command line.

To remove the SQL Adapter in interactive mode
  1. Click Start, and then click Control Panel.

  2. In Control Panel, double-click Programs and Features.

  3. In the Add or Remove Programs window, select Microsoft BizTalk Adapter for SQL Server, and then click Remove.

  4. In the dialog box, click Yes.

To remove the SQL Adapter in silent mode
  1. Open a command prompt, and navigate to the root directory of the SQL adapter installer.

  2. Run the following command:

    Note

    To remove the SQL adapter in silent mode on an x64-based platform, in the following commands, replace SqlAdapterSetup.msi with SqlAdapterSetup64.msi.

    msiexec /x SqlAdapterSetup.msi /qn

    This command removes the SQL adapter from the computer.

    For more information about the msiexec command, type msiexec on the command line and press ENTER or see http://go.microsoft.com/fwlink/?LinkID=103199.

After Removing the SQL Adapter

If the SQL adapter setup fails to remove the adapter bindings while removing the SQL adapter, you must remove them manually. The following section describes how to manually remove the bindings for the SQL adapter.

Removing the Bindings

Perform these steps only if the setup wizard fails to remove the adapter bindings from the machine.config file.

To remove the adapter bindings
  1. Navigate to the machine.config file on the computer. For example, on a 32-bit platform, the machine.config is available under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG.

    • For Microsoft .NET Framework 3.5 SP1, <version> is the version v2.0.50727 of the .NET Framework.

    • For Microsoft .NET Framework 4, <version> is the version v4.0.30319 of the .NET Framework.

  2. Open the file using a text editor.

  3. To remove the adapter binding registration:

    1. Search for the element "system.serviceModel" and remove the following from under the element:

      <client>
        <endpoint binding="sqlBinding" contract="IMetadataExchange" name="mssql" />
      </client>
      
    2. Search for the element "bindingElementExtensions" under system.serviceModel\extensions.

    3. Remove the following section under the "bindingElementExtensions" node.

      For SQL adapter, remove:

      <add name="sqlAdapter" type="Microsoft.Adapters.Sql.SqlAdapterBindingElementExtensionElement,Microsoft.Adapters.Sql, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
    4. Search for the element "bindingExtensions" under system.serviceModel\extensions.

    5. Remove the following section under the "bindingExtensions" node.

      For SQL adapter, remove:

      <add name="sqlBinding" type="Microsoft.Adapters.Sql.SqlAdapterBindingCollectionElement,Microsoft.Adapters.Sql, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
  4. Save and close the machine.config file.

Top of page

Accessing the Adapter Forum

You can use the adapter forum to discuss any issues you encounter with SQL adapter. The SQL adapter forum is available at http://go.microsoft.com/fwlink/?LinkId=87695.

Top of page

Copyright

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted in examples herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2010 Microsoft Corporation. All rights reserved.

Microsoft, MS-DOS, SQL Server, Visual Studio, Windows, Windows Server, Windows Vista, and BizTalk are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

All other trademarks are property of their respective owners.

Top of page