Thursday, June 17, 2010

Walkthrough: Configuring the Merge Replication Agent (Web Synchronization) sqlcesa35.dll (SP2) on Windows 7 x64 and x86 with IIS 7.5

With the recent release of SQL Server Compact 3.5 SP2, installing the Web Synchronization Agent on Windows 7 and Server 2008 R2 has become a lot easier, as I demonstrate below.

Download the latest Server Tools

First, download the latest version of the SQL Compact Server tools for 3.5 SP2.

For x64 systems, the x64 version of the tools is:  SSCEServerTools_x64-ENU.msi

Notice the following on the download page:

  • Prior to installing SQL Server Compact 3.5 SP2 Server Tools, uninstall all earlier versions of SQL Server Compact Server Tools, including, SQL Server Compact 3.5 SP2 Server Tools Beta, SQL Server Compact 3.5 SP1 Server Tools, SQL Server Compact 3.5 Server Tools, SQL Server 2005 Compact Edition Server Tools or SQL Server 2005 Mobile Edition Server Tools by using the Add/Remove Programs in the Control Panel.
  • On Windows Vista SP1, Windows Server 2008, Windows 7 or Windows Server 2008 R2 with IIS 7.0, the IIS 6.0 Compatibility Components need to be installed prior to installing SQL Server Compact 3.5 SP2 Server Tools. For more information about installing the IIS 6.0 Compatibility Components, see IIS 6 Compatibility Components.

    Configure IIS 7.5

    Enable IIS on Windows 7:

    Click Start, click Control Panel, click Programs and Features, and then click Turn Windows features on or off.

    Select the Internet Information Services check box.

    Open Internet Information Services.

    Open World Wide Web Services.

    Open Application Development Features

    Select the ISAPI Extensions check box.

    Open Web Management Tools.

    Open IIS 6.0 Management Compatibility.

    Select the check boxes for IIS 6 Metabase and IIS 6 configuration compatibility and IIS 6 Management Console

    Click OK.

    image

     

    Install the Server Tools

    Install SSCEServerTools_x64-ENU.msi or SSCEServerTools_x86-ENU.msi

    Important steps:

    image

    The checkmarks in this steps reflects the version of the SQL Server Replication components installed on the system, I my case only SQL Server 2008 replication components are installed.

    Otherwise, just click Next, Next…

    Create the publication and configure for SQL Compact subscribers

    Go ahead and do this. See http://msdn.microsoft.com/en-us/library/ms171780(v=SQL.100).aspx

    Configure the agent - run Configure Web Syncronization Wizard

    Run "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\sync\SQL\ConnWiz.exe" (you can use the shortcut in Start, All Programs, Microsoft SQL Server Compact 3.5).

    Complete the Wizard.

    Test the agent

    Run: http://mypc/ssce35/sscesa35.dll in your browser.

    You may encounter this error:

    image

    If this happens, try to remove the web.config file from your root web folder (c:\inetpub\wwwroot). This worked for me.

    If the test succeeds, you are good to go:

    image

    You can now use SQL Server Management Studio to test your publication, before testing on other machines and devices.

    Create a new SQL Server Compact Database, and run the New Subscription Wizard to test it.

  • 12 comments:

    Hugo Simões said...

    Hello Erik,

    I followed your post carefully. After the uninstall of sqlce 3.1 and install of sqlce 3.5 sp2, I lost the hability of connect to a sqlce database from Studio Management (2008).
    I've search but I can't figure out how I correct this problem. Any ideas?

    ErikEJ said...

    You uninstalled too much! - See http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/31ddcfa8-c566-49c7-ad19-74b1a651e1af/

    Unknown said...

    Erik,

    In my search for a solution to a connection problem with SQL CE 3.5 sp2 server tools, I found this blog entry. I have a successful connection to the dll with ?Diag option in URL, and I can connect to the database remotely using credentials. However, the error log, set at level 3, provides only generic error messages ( Hr=80070585 ERR:Failure reading Transport version header 0
    Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1)

    Can you recommend any diagnostic tools or reference sources for tracing what happens between the dll under IIS 7 and sql server 2008 r2?

    Than you.

    Rick

    ErikEJ said...

    Suggest you ask in the MSDN forum - firewall/proxy??

    Ahmet said...

    Hi Erik,

    I am following these instructions (which are extremely helpful, btw) but I am stuck at the part where it says to run the connection wizard (\sync\SQL\ConnWiz.exe). After installing all the components, I don't have the \sync\... folder, which means I also don't have the connection wizard. Any idea what i'm doing wrong? thanks!!

    ErikEJ said...

    Have you installed the x86 tools, then maybe look in program files (x86), or do a file search...

    Kenji said...

    Hello Erik,
    I have Window Server 2012 and Sql Server 2014 Express.
    So I can setup Sql Server Compact 3.5 like you in my system?
    Pls help me!
    Thank you.

    ErikEJ said...

    Kenji: Merge replication with sqlce is not supported with SQL Server 2014 nor with any Express edition, so you need SQL Server 2012 Standard or Enterprise

    Unknown said...

    Erik - I have followed this process step by step as well as looked all over and still cannot get merge replication to work on my new servers I am migrating to. I have SQL 2012 and IIS 8.5 and continue to get failed getting pub version 28627. I have tried everything to no avail. What am I missing as this works fine in current production SQL 05 and IIS 6.

    ErikEJ said...

    chip: did you follow the tips here? http://erikej.blogspot.dk/2014/04/merge-replication-with-sql-server.html

    Unknown said...

    This is resolved - The client machine that my colleague was using for the client had not bee rebooted after updating the SQLCE to the proper version. After looking into his machine last night I rebooted it and all synched fine. Thanks for the great post. You may want to update the info to reflect the reboot requirement for the client machine if they are upgrading from an older version of SQLCE.

    ErikEJ said...

    Chop: An IIS or app restart should be enough, a reboot should not be required