SQL Server Compact

Back in my first article, I showed you where to find Microsoft’s latest updates to the SQLCE and RDA technologies so you can begin synchronizing data with the new SQL Server 2012 (Denali) database.

Just imagine, you now have mobile sync components that give you the flexibility to to work with SQL Server 7, 2000, 2005, 2008, and 2012 from your devices.  I’d say you have both your legacy and state-of-the-art bases covered.  In the second article you built both the server and client databases so now you’re ready to sync some data.

As I may have mentioned before, Remote Data Access (RDA) is the fastest and easiest way for your mobile devices to synchronize data with SQL Server – and then take it offline in SQL Server Compact.  It works on the simple premise of pulling and pushing data to and from SQL Server via the Server Agent which is running on the middle-tier IIS application server.  The Server Agent is able to communicate with SQL Server via an OLEDB connection string which is passed to it from your device application code:

string rdaConnection = @”Provider=SQLOLEDB;” +

“Data Source=Machinename\\SQLExpress;” +

“Initial Catalog = ContosoBottling;” +

“User Id = sa;” +

“Password = P@ssw0rd;”;

You’ll use this connection string over and over whether your pulling or pushing data so keep it handy.  Data is retrieved on a table-by-table basis using the Pull method of the SqlCeRemoteDataAccess object.  You would put the example code below in a method to retrieve a list of Distribution Centers from SQL Server:

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess())


rda.InternetUrl = “http://localhost/rda/sqlcesa35.dll”;

rda.LocalConnectionString = “Data Source=ContosoBottling.sdf”;

//Drop Table

DropTable(“DistributionCenters”, rda.LocalConnectionString);

//Pull Table


“SELECT DistributionCenterId, Name FROM DistributionCenters”,





Notice that the mobile device connects to the Server Agent on IIS by pointing to it via a URL.  After that, you assign a connection string that points to the local path of your SQLCE database.  For now, I want you to ignore the DropTable method, because I’ll cover it in a sec.  The Pull method is where the magic happens.  In the first parameter, you pass in the name of the local table you want to create as an argument.  This typically matches the name of the table you’re retrieving from SQL Server.  In the second parameter, you pass a standard SQL statement or call to stored procedure.  This is how you filter the data you want to download to the device.  I don’t want to see any SELECT *’s and I do expect to see appropriate use of the WHERE clause to reduce the amount of data downloaded.  Remember, this filtering allows you download lookup tables that apply to everyone, as well as tables with data that uniquely pertain to a specific user.  In the next parameter you pass in the OLEDB connection string I displayed at the beginning of the article.  The following parameter is where you decide if you want SQLCE to track changes or not, as well as whether to create the same indexes found on the server.  Indexes are typically always a good thing except for very small tables.  Download-only data won’t need change-tracking but your transactional stuff will.  This amazing feature allows offline users of your app to keep working in the absence of a network connection.  In the last parameter you specify the name of a table to auto-create to track any sync errors that may arise.

Server Explorer







After executing this code, I connected to the new SQLCE ContosoBottling bottling database on my Windows laptop using the Server Explorer in Visual Studio as shown above.  You can see that the ErrorTable and DistributionCenters tables were created locally.

So now let’s talk about that DropTable method.  RDA works on the premise of downloading complete table snapshots.  Unlike Merge Replication that downloads incremental changes from SQL Server, RDA re-downloads the entire table in order to make SQLCE aware of any server changes.  The catch is that you have to drop an existing local table before downloading an updated version from SQL Server.  Here’s how you do it:

private void DropTable(string tableName, string connectionString)


using (SqlCeConnection cn = new SqlCeConnection(connectionString))


SqlCeCommand cmd = cn.CreateCommand();

cmd.CommandText = String.Format(“SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘{0}'”, tableName);


if((int)cmd.ExecuteScalar() == 1)


cmd.CommandText = String.Format(“DROP TABLE {0}”, tableName);





You can see that I use SqlCeConnection and SqlCeCommand objects in order to query the INFORMATION_SCHEMA.TABLES database object.  If the return value of the query is 1, then you know that a table already exists.  This result leads you to execute a DROP TABLE statement so that the existing table is gone before the new one is downloaded.

Local Query


Right-clicking on DistributionCenters and selecting Show Table Data reveals that the Seattle and Redmond distribution centers and their associated uniqueidentifiers were downloaded to SQLCE from SQL Server 2012.

Right about now, I know you’re thinking that this whole process of dropping a table and re-downloading a new one in order to keep a mobile database up to date sounds wasteful.  I get it.  I also get all the heavyweight processes that are required by Merge Replication to figure out server changes for each device that synchronizes with SQL Server.  You have to weigh your options.  For instance, in boosting Merge Replication performance and scalability, one of the keys to success is maintaining a low Subscription Expiration value.  This value determines how long a mobile user can go without synchronizing her data before her subscription expires, which requires her to re-download an entire database from scratch.  Keeping a low value ensures that SQL Server doesn’t track too much performance-degrading metadata.  It also means that users might have to synchronize more frequently than business rules dictate.  The great thing about RDA is that the notion of a subscription doesn’t exist since it downloads table snapshots to keep mobile clients up to date.  This means users can download data to their devices and remain disconnected for an indefinite amount of time while capturing new data out in the field.  No expiration or degraded performance on SQL Server 2012.  This leads to infinitely greater scalability for your system.

In the most common mobile scenarios I see in business, laptops/devices download the data needed to perform work for a given day via Wi-Fi or cradled Ethernet.  Unless each of your downloaded tables are 100+ MB a piece, this shouldn’t be a big deal at these types of network speeds.  Most organizations roll their own web services to do the same thing and they don’t bat an eye at the amount of data they have to re-download with each web method call.  Heck, most companies I work with allow their devices to take all night to download the data needed for the next morning.

So after a user has spent some time in the field capturing new data or changing/deleting existing data, it’s time to push those tracked changes back up to SQL Server 2012.  This is the simplest code of all:

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();

rda.InternetUrl = “http://localhost/rda/sqlcesa35.dll”;

rda.LocalConnectionString = “Data Source=ContosoBottling.sdf”;

rda.Push(“DistributionCenters”, rdaConnection, RdaBatchOption.BatchingOn);

For each table that you tracked changes for, you need to use the SqlCeRemoteDataAccess object and the Push method.  The first parameter should look familiar since it’s the name of the tracked table that you had previously Pulled.  The second parameter is the same OLEDB connection string we used in the Pull method.  The last one allows you to specify batching of uploads.  This feature gives you the transactional, all-or-nothing functionality of a message queue.  If any of the table data uploads fail, the whole transaction is rolled back.  This is a great feature to ensure data integrity.

Before you run this code, I want you to go back to the local SQLCE query result from the Server Explorer in Visual Studio and change the Distribution Center Name column from Redmond to Bellevue.  I have to prove that this great change tracking feature actually works after all.  Once you’ve made the change and hit the tab key to save it, go ahead and run your Push code.  If everything works as expected, the local change you made should be pushed up to SQL Server.  We need some proof, so open up SQL Server Managment Studio:

Object Explorer


Right-clicking on dbo.DistributionCenters and clicking Select Top 1000 Rows should return the result you see in the figure above.  Happily, the local SQLCE change from Redmond to Bellevue is reflected in the result on SQL Server 2012.

The circle is complete.

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.”


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 III
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.

7 thoughts on “Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode III

  • May 23, 2012 at 5:11 am

    p { margin-bottom: 0.21cm; }

    Thanks i like your blog very much , i
    come back most days to find new posts like this!Good effort.I learnt

    Andrew Struss

    Mail id:
    godbtechnologies@gmail:disqus .com



  • July 14, 2012 at 1:34 am

    Sir i m trying to use ur code in my program but error is “Type ‘SqlCeRemoteDataAccess’ is not defined.” please guide me what should i do ?????
    thanx And regards

    • July 18, 2012 at 10:38 am

      Hi Abid,
      To get things working with your Windows app, go to the Solution Explorer, right-click on References, and add a reference to System.Data.SqlServerCe. To make sure you’re working with the newest bits based on Cumulative Update package 6, in the Add Reference dialog, click the Browse tab and navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll.


  • August 9, 2012 at 5:35 pm

    Hi Rob,

    How critical d you think it is to move to Microsoft Synchronization Services for ADO.NET?

    Because of design limitations, remote data access (RDA) will be removed in a future release. If you are currently using RDA, you should consider transitioning to Microsoft Synchronization Services for ADO.NET. If you were planning to use RDA in a new application, you should instead consider merge replication or Sync Services. Note that Sync Services is available for both desktop and mobile devices.

  • March 22, 2016 at 2:20 am

    Hi Rob
    I have to replace an application which I originally wrote in vs2005 vb.net and synched to a sql Ce database.
    The desktop was used as a server using sql 2000 and hand held devices running CE 4.2, using IIS and sqlcesa20 agent. I have now got to update this application using hand held devices running CE7.00 and Sql 2008 r2 express or Sql 2012 express as sql server on a windows 7 Desktop. I have no control over hand held device Operating System. Would I be correct in thinking I have to go down the sync service route or could you suggest any other ideas. If you have any articles with this scenario i would be grateful with a steer towards them.
    Kind Regards

  • July 19, 2016 at 6:34 am


    I’m working with an RDA system which has working well, until now.
    The system seems to get an issue if we are pushing over 100,000 records as insert\updates and the server side has about 3M records.

    The issue is that the DB deadloack and the RDA seems to freeze.

    I’ve found, if I switch of the batch option, the deadlock does not happen. But there is still speed issues.

    Have you had this issue and if so, how have you resolved it?


Leave a Reply

Your email address will not be published.

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