SQL Server Compact

Now that SQL Server 2012 has been released, some of you might be wondering if SQL Server Compact is capable of synchronizing with it.  With the release of Cumulative Update Package 6 for SQL Server Compact 3.5 Service Pack 2, the answer is a resounding yes!

Build number 3.5.8088.00 adds support for replication with SQL ServerDenali” which is pretty awesome in my book.  For those of you keeping score at home, check out Erik’s Everything SQL Server Compact blog to see a running total of improvements to SQLCE via Cumulative Updates.  After that, click on the Cumulative Update link at the beginning of this article and head on over to the Microsoft Support page to get started.

At the top of the page it says Hotfix Download Available and beneath that says View and request hotfix downloads so click on that.  Keep in mind that my link was defined by my U.S. English IE9 browser so the page I navigated to shows me checkboxes to download x64 Server Tools for IIS and both x86 and x64 versions for Windows.  Your experience may be different depending on where you live.  Luckily, there’s a link that says Show hotfixes for all platforms and languages.  Check the checkboxes to select your language, the Server Tools, Windows, and Windows Mobile/Embedded platforms that you’re looking for.  Afterward, type in your email address and play the Captcha game in order to have links to the bits you need sent to you.

The next thing you’re going to need is a copy of SQL Server 2012.  To keep things simple with this series of articles, I won’t dive into security and I decided that I’ll use my x64 Windows 7 laptop as the database server, middleware, mobile platform and use Remote Data Access (RDA) as the sync transport.  I think you’ve probably already heard enough about Merge Replication from me so I decided to mix it up a bit with my old friend RDA.  Remember, RDA does not require any configuration on SQL Server, it is not invasive to the schema of the server database, and it’s amazingly fast and scalable.  With over 650 million copies of Windows 7 deployed, this is by far the most widely used, occasionally-connected mobile platform in the world, so I don’t feel bad about not writing another Windows Phone article.  Navigate your browser to download the free SQL Server 2012 Express with Advanced Services since we won’t be needing support for Replication.  You might need to navigate elsewhere if you’re not targetting U.S. English.  Sorry about that.  Once you’ve downloaded the exe, install the product and make sure you can login via SQL Server Management Studio.

At this point, go ahead and install the 32 and 64-bit versions of the SQL Server Compact 3.5 SP2 runtimes that you downloaded as appropriate.  Remember, on a 64-bit OS, you must install both the x86 and x64 versions in order to have smooth sailing with out favorite embedded database.

I already have IIS installed on my x64 Windows 7 laptop so I’m in good shape to install the x64 Server Tools.  Unzip your CU 6 update and click SSCEServerTools-ENU.msi to begin the Server Tools installation.  As a refresher, you must have IIS 6 Management Compatibility enabled to make things work with IIS 7.5.  During the install make sure all the System Configuration Checks are successful, and that you select SQL Server “Denali” to synchronize with. With the Server Tools installed, I want you to create a local folder on your computer and call it SnapshotShare and Share it with Everyone to keep things simple.  It’s a little silly since RDA doesn’t use a Snaphot Share, but the installation Wizard may no let you proceed without it.

I know many of you have followed the screenshot-filled installation routines in my books so I’ll keep the pictures microscopic this time around.  Click Windows Start, navigate to Microsoft SQL Server Compact 3.5, and select Configure Web Synchronization Wizard.

Welcome to the Configure Web Synchronization Wizard | Click Next





Subscriber Type | Select SQL Server Compact | Click Next





Web Server | Create a new virtual directory | Click Next





Virtual Directory Information | Type RDA in the Alias textbox | Click Next | Click Yes to create a folder | Click Yes again





Secure Communications | Select Do not require secure channel (SSL) | Click Next





Client Authentication | Select Clients will connect anonymously | Click Next





Anonymous Access | Default IUSR account of IIS will be used | Click Next





Snapshot Share Access | Enter path to the shared folder I told you to create | Click Next | Click Yes





Complete the Wizard | Verify the choices you made | Click Finish





Configure Web Synchronization | You should have 9 successes | Click Close





Congratulations!  You’re done.

Test your Server Tools installation using Internet Explorer and navigate to this address: http://localhost/rda/sqlcesa35.dll.  Your browser should display “Microsoft SQL Server Compact Server Agent” if all went well.  Your configuration tasks are almost complete, but I need you to bring up SQL Server Management Studio to do one more thing for me.  In SQL Server, create a new login called NT AUTHORITY\IUSR  with ContosoBottling as the default database so devices can anonymously connect to IIS and SQL Server to sync.  I apologize for not having you build out a network full of servers and for not having you use Windows auth against Active Directory.  Remember, when it’s time to go to production, you’ll do this the secure way.

You’ve accomplished a lot by following along through this article and all the pieces are in place to create an occasionally-connected solution for yourself, your company, or your customers.  In the next article, we’ll build a sample database and start writing some code in Visual Studio 2010.

If you’re ready to go deep on this sync technology to build enterprise apps that run on Windows tablets and laptops, click this link to check out and purchase my book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012.”

Stay in sync,


Sharing my knowledge and helping others never stops, so connect with me on my blog at https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode I
Tagged on:                 

Rob Tiffany

Rob is a writer, teacher, speaker, world traveller and undersea explorer. He's also a thought leader in the areas of enterprise mobility and the Internet of Things.

6 thoughts on “Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode I

  • May 5, 2015 at 11:04 am

    Any idea of RDA works with SQL Server 2014 ? I need to migrate a 2000 box to a newer server and need to support RDA. Just looking to see what is the newest version I can go to and still support it.

    • May 9, 2015 at 5:27 pm

      Hi Rich. Two things here. Does it work and is it supported. Since RDA doesn’t require any kind of special integration with SQL Server the way Merge Replication does, it should probably work. I haven’t tested it yet, though. Regarding support, we’re all on our own when it comes to SQLCE and associated technologies. Best of luck to you.


  • May 18, 2015 at 7:34 am

    Previously I used SQL Server 2012 to sync with SQL Compact Edition 3.5 after installing the mentioned hotfix. But now I want to setup my application in a server where only SQL Server 2014 was setup. Is there any hotfix available so that I can install that to connect SQL Server 2014? Or do you know that your mentioned hotfix will also work for SQL Server 2014 also and I will get the Denali option to sync with 2014? If you know that SQL Server 2014 is compatible with SQL Compact Edition 3.5 or not please let me know.

    • November 25, 2015 at 10:15 pm

      Unfortunately, sync between SQL Server 2014 and SQL Server Compact is not supported. You might check out Zumero.

  • June 23, 2015 at 2:05 am

    Hi Rob! It is also enough to install the following SQL Server 2012 Components:
    1. Client Tools Connectivity
    2. Client Tools Backwards Compatibality

    And after that you can run the SSCEServerTools Setup in the following way:
    msiexec /i SSCEServerTools-ENU.msi SQLSERVERDENALI=”1″

    So no Database Installation is needed 😉


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.