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

| March 27, 2012 | 1 Comment

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,

Rob

Tags: , , , ,

Category: Sync

About the Author ()

A mobile strategist and cloud architect at Microsoft, Rob has spent his career as an entrepreneur, advisor, teacher, developer, speaker, and author of bestselling books on mobile and wireless technologies. A pioneer of the smartphone revolution, he drove the development of the mobile app ecosystem from its earliest days and co-founded the world’s first cloud-based mobile device management company.

Leave a Reply