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

rda.Pull(“DistributionCenters”,

“SELECT DistributionCenterId, Name FROM DistributionCenters”,

rdaConnection,

RdaTrackOption.TrackingOnWithIndexes,

“ErrorTable”);

}

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.

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);

cn.Open();

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

{

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

cmd.ExecuteNonQuery();

}

}

}

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.

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:

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.

-Rob

In my last article, I walked you through finding, downloading, installing, and configuring SQL Server 2012 Express, SQL Server Compact 3.5 SP2 CU6, and the Sync Server Tools.  With that series of tasks completed, you’re now capable of performing data synchronization with a mobile Windows client.

Open SQL Server 2012 Management Studio and connect to the local SQL Express instance.  You’ll quickly notice the new Visual Studio 2010 IDE look and feel.  Since you’re going to need a database to sync with, right-click on the Databases folder in the Object Explorer and select New Database.  Type ContosoBottling in the Database name text box and click OK.  I want you to create three simple tables for the purposes of this article:

If you read my last book on Enterprise Data Replication, these tables that support the operations of a delivery driver should look familiar to you.  Since RDA doesn’t support the Identity Range feature of Merge Replication, you’ll be using GUIDs for your primary keys to ensure uniqueness.  Since the offline data capabilities of sync technologies from all vendors are based on the notion of optimistic concurrency, having a globally unique primary key that won’t collide with inserts and updates made by one or more devices is critical to success.  Now it’s time to fill these tables with some sample data to get started:

Right-click on DistributionCenters and select Edit Top 200 Rows.  Type Seattle for the Name in the first row and Redmond for the Name in the second row.  Allow the DistributionCenterId uniqueidentifier values to be automatically created.  It should look something like this:

Now it’s time to tackle the Routes table.  Each Distribution Center will have multiple routes that it supplies products to.  Right-click on Routes and select Edit Top 200 Rows.  Like before, allow the RouteId uniqueidentifier values to be automatically created.  The eight rows of data I want you to enter should be as follows:

1. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Magnolia.
2. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Ballard.
3. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Fremont.
4. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Wallingford.
5. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Kirkland.
6. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Bellevue.
7. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Issaquah.
8. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Sammamish.

It should look something like this:

Last but not least, we have the Drivers.  Each of these folks will be assigned to a particular route on any given day.  Right-click on Drivers and select Edit Top 200 Rows.  For each row, allow the DriverId uniqueidentifier values to be automatically created.  I’ll just have you enter a couple of drivers for this table:

1. The RouteId should equal the related Magnolia value from the Routes table and the FirstName should equal Dave and the LastName should equal Bottomley.
2. The RouteId should equal the related Kirkland value from the Routes table and the FirstName should equal Khalid and the LastName should equal Siddiqui.

It should look something like this:

Now that your sample database has some data inside, it’s time to build a sample Windows app so fire up Visual Studio 2010.  Create a Windows Forms or WPF application and call the Solution SimpleSync.  The first thing I want you to do is 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.

With those pieces in place, it’s time to write some code.

Unlike Merge Replication that automatically creates a SQL Server Compact database for you during the initial sync, with RDA you’ll need to create it in code with the SqlCeEngine object.  So before your app can start synchronizing data, you’ll need to first create a database with the following code:

if (!File.Exists(“ContosoBottling.sdf”))

{

using (SqlCeEngine sqlEngine = new SqlCeEngine())

{

sqlEngine.LocalConnectionString = ”Data Source=ContosoBottling.sdf”;

sqlEngine.CreateDatabase();

}

}

The first thing the above code does is to check and see if a SQL Server Compact database already exists.  If not, then the SqlCeEngine object is instantiated.  The LocalConnectionString property is set to the path of where you want the database to reside.  In this case, I didn’t enter a path so the database will be created in the same folder as the app’s exe.  Keep in mind that a number of other parameters can be used for this property to support password protection and encryption among others.  Next, you just call the CreateDatabase() method and that’s all there is to it.  You will now have an empty shell of a database that typically weighs in at 20 KB.

With a local database created, you can begin retrieving data.  In my next article I’ll discuss how to RDA filters and pulls both data and indexes, enables local change-tracking, and pushes new data and updates back to SQL Server.

-Rob

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 Server “Denali” 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.

Stay in sync,

Rob