RSS
 

Posts Tagged ‘ADO.NET’

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge Replication

23 Oct

I’m happy to say that my latest book is now available on Amazon.

With the world’s largest organizations rolling out tens of thousands of Windows® phones, laptops, tablets and Netbooks to empower their respective mobile workforces, the ability to create mobile line of business solutions that support large numbers of users is absolutely critical. In my fourth book on mobile infrastructure and development, I show you how to take the SQL Server data you use to run your organization and make it available to all of your mobile employees.

Step-by-step, I’ll walk you through the process of building a secure, performant, n-tier, mobile enterprise application platform architecture designed to scale to thousands of users. You’ll also learn how to create occasionally-connected .NET applications designed to thrive in unreliable wireless conditions.

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact=

  • Learn how to “Mobilize” your organization by making your enterprise data available to employees carrying Windows® phones, laptops, Netbooks and tablets in the field.
  • Learn how to build an N-Tier Mobile Sync infrastructure that will scale to thousands of users.
  • Learn how to create occasionally-connected .NET applications designed to thrive in unreliable wireless conditions.
  • Learn best practices in security, reliability, performance, load-balancing, reverse proxy and hardware configuration.
  • Learn how to implement this technology in real world scenarios like supply chain management, retail, sales force automation, healthcare and emergency management.

 

Keep in mind that the knowledge you gain from this book didn’t come from me dreaming this stuff up in an Ivory Tower.  It came from building some of the worlds largest and most complex data synchronization systems for the world’s largest companies.  In addition to the hands-on experience that went into this book, I’d also like to thank some of my colleagues for their invaluable contributions:

  • Liam Cavanagh is a Senior Lead Program Manager for Microsoft’s Sync Framework and Cloud Data Services and he wrote the forward.
  • Catherine Wyatt is the Managing Editor for Hood Canal Press who made the publishing of the book possible.
  • Darren Shaffer is the CEO of Handheld Logic and he wrote the Chapter on building the Mobile Subscriber. 
  • Michael Jimenez is a Mobility Architect at Microsoft and he wrote the Appendix that shows you how to create an ISA Server 2006 Reverse Proxy to publish your sync infrastructure to the Internet.

 

It’s my sincere hope that this book will encourage you to un-tether your workforce from their desktop computers and boost your organization’s agility by pushing out critical business functions to the point of activity where employees are empowered to make timely decisions and perform tasks that best serve the interests of their customers and their company.

This repudiation of the traditional “connected” software application model increases customer satisfaction, boosts worker efficiency, reduces “missed opportunities” and results in cost savings as “un-wired” employees get their jobs done wherever they happen to be.

Best Regards,

Rob

 
6 Comments

Posted in Featured, Mobile Enterprise Application Platform

 

The Hidden Message Queue on your Windows phone

02 Oct

The ability to be “offline” and “occasionally-connected” is a critical component of successful mobile apps.  Wireless data networks lack complete coverage and exhibit a level of unreliability that immediately disqualify permanently-connected apps like you might see on a corporate LAN.  For a mobile app to be successful, it must allow the user to keep working in the absence of a data network.  It must also be able to transparently sync data changes from the mobile client to the server whenever a wireless data network is detected.  The primary means of accomplishing that today is via one of Microsoft’s sync technologies that allows SQL Server Compact on the mobile client to replicate data to and from SQL Server in the data center or the cloud.  Since SQL Server Compact runs almost anywhere, your mobile client could be a Windows phone, a laptop, a desktop or even a Netbook.

Besides synchronizing the tables, rows and columns of a complete database between mobile clients and servers, the use of message queuing should be considered for many scenarios due to its high-reliability by ensuring that a critical message arrives at its destination.  Products like MSMQ, MQ Series, Tibco and JMS are used all over the world in the most mission-critical environments to ensure a high level of availability and reliability.  They’re asynchronous by nature and use store and forward mechanisms so that messages get from point A to B to C.  A typical queue message includes the Destination which tells the messge where to go, a Label which describes the message, a Body which contains the message, and a Body Length so the receiver can verify that it received everything.

So how does any of this relate to Windows phones?  A number of years ago, an MSMQ client was made available for download and installation on Windows phones.  Additionally, the .NET Compact Framework 2.0 included classes to work with MSMQ.  Unfortunately, the installation of MSMQ was far from seamless which inhibited its adoption by customers.  More recently, functionality was included in the .NET Compact Framework 3.5 that facilitated store and forward messaging using Exchange 2007 as a transport.  This is a good solution for customers running the newest version of Exchange, have an unlimited data plan for their phones, and don’t mind running line of business applications over their email infrastructure.

So what do we do for customers that have found the Windows Mobile MSMQ client too much of a hassle, don’t have Exchange 2007 or don’t want to use it as a mobile message queue server?  I think the answer has been under our noses all along.  Burned in the ROM of every Windows Mobile 6.x device is SQL Server Compact + a lightweight data sync solution called Remote Data Access (RDA).  For those of you running Windows Mobile 5, XP, Vista or 7, you can easily download these bits to your mobile client.  SQL Server Compact is you local queue, RDA is your transport and SQL Server in the cloud or data center is your message queue server.  So let’s break this down and see how it will work.

A mobile application that captures data in the field would want to drop that info in a local queue.  SQL Server Compact becomes that local queue and the message format is actually a table with the following structure:

Table name Message
MessageId Uniqueidentifier
Destination NVarchar(whatever)
Label NVarchar(whatever)
Body NVarchar(4000)
BodyLength int

This table would be created inside a MessgeQueue database in SQL Server and RDA would pull it down to SQL Server Compact.  In the Pull method you call from .NET on the client, you would add “WHERE 1=0” to the SQL statement.  This filter has the effect of pulling down an empty shell of the table without retrieving any data to the client since that’s all you want.  It also means that when you insert local data into the table and call the Push method, the data will be removed from the client at the completion of a successful sync.

So you’re probably wondering, what makes this so special and message queue-like vs. anything else?  The secret is that unlike other sync technologies, RDA can wrap the upload of data into a transaction.  As the data is being uploaded over wireless, if any of the INSERTs into SQL Server fail for whatever reason, everything gets rolled-back and the original data remains in the local SQL Server Compact queue.  This is the kind of guaranteed commit that you expect from a message queuing system.  It’s an “all or nothing” success or rollback. 

It actually gets better.  A property of both RDA and Merge Replication is called ConnectionRetryTimeout.  This feature is designed to help you with unreliable wireless coverage where you have signal one minute and then lose it the next.  Let’s say you have this timeout value set to 2 minutes and you begin your Push upload of queued data.  Everything is working fine for the first few seconds but then you lose wireless coverage from your mobile operator.  If you regain coverage before the 2 minute time-out, the upload will resume where it left off.  Since both RDA and Merge send and receive data between the SQL Server Compact and IIS in tiny blocks, you never have to worry about running out of memory and you can pick up where you left off in case of a network dropout.

So the big takeaway here is that we do in fact have a Mobile Message Queue solution hidden on our Windows phones.  We have a message format that lets us drop text/xml/whatever data into the body, a label that a server process or SQL trigger can key off of to perform an action, and a transactional upload mechanism that ensures your critical data will cross the wireless chasm and make it to the other side intact.

So what’s next?  Now that you can capture data in a local queue and safely upload it, you might be wondering how queued messages from someone else can be pushed to your device.  Don’t worry, that will be in my next post.  Also, this isn’t just an article on how to solve a big problem in the mobile space, I’m actually building the necessary client and server pieces as well.  We’re all looking for a reliable and unified way to connect mobile devices to corporate assets and this just might be the simple answer we’re looking for.

- Rob

 
2 Comments

Posted in Message Queuing

 

What ever happened to RDA?

22 Sep

Who remembers using Remote Data Access to synchronize data between SQL Server and SQL Server Compact?  I certainly do!

Before I dove head first into the world of Merge Replication, I always used RDA to get my customers up and running quickly.  Mobilizing an organization’s workforce quickly and easily is what it’s all about so they can start reaping the benefits.  In addition to a speedy time to market, there’s no faster or more scalable mobile sync technology on the market anywhere. 

So why wouldn’t I always use RDA?  Here’s a quick list:

  1. You’re using Identity columns.
  2. You want to replicate schema changes to the client.
  3. You want change tracking on both the client and server to perform diffs of each of the tables during a sync instead of re-downloading the entire table.
  4. You want to automatically resolve conflicts that arise when 2 people update the same data.
  5. You want referential integrity constraints to be pushed down to the client database from SQL Server.
  6. You don’t want to write code to perform synchronization or filter data.

If anything on the above list applied to you, you would shift to Merge Replication because it could manage ranges of Identity columns, push down schema changes, only sync data differences, resolve conflicts and push down a database’s referential integrity constraints.  Merge requires almost no code to get started and tables and columns are filtered visually via a wizard.

So why might you choose to use RDA?  Here’s another list:

  1. Your Primary Keys use GUIDs instead of Identity columns.
  2. Users don’t overwrite each other’s data so you don’t need conflict resolution.  The rule of “Last in Wins” works for you.
  3. While you want indexes to be pushed down, you don’t care if your local SSCE database has referential integrity constraints applied.
  4. You want to wrap the changes you upload to SQL Server in a transaction so that all changes are applied or none of them are.
  5. Change tracking on the client is good enough and re-downloading updated server tables doesn’t take too long.
  6. You developers don’t mind writing some sync code.
  7. Be able to execute SQL and Stored Procedures directly against SQL Server via IIS.
  8. You’re downloading read-only data.

If your solution meets the criteria in the list above, you’re probably a good candidate for using RDA instead of Merge.  Are there any other choices out there?

Back at MEDC 2007, we announced a new data replication technology for devices called Occasionally Connected Sync that would sit somewhere between RDA and Merge.  OCS as it used to be called was renamed Sync Services for ADO.NET and then was eventually merged into the Sync Framework. 

The Sync Framework is a developer-focused technology:

  1. Supports conflict resolution.
  2. Change tracking on the server as well as the client so that only data differences are exchanged.
  3. Peer to Peer sync in the forthcoming v2 of Sync Framework.
  4. Sync with databases other than SQL Server.
  5. Best suited for SSCE running on a desktop or laptop.

The clearest differentiation that the Sync Framework has over Merge is its provider model which allows it to sync with other ADO.NET databases like Oracle or DB2.  SQL Server supports built-in P2P Transactional replication and v2 of the Sync Framework will allow you to do this via WCF.  If you development team doesn’t mind writing lots of sync code and needs to support scenarios like synchronizing with other databases from SSCE on the desktop, then the Sync Framework might be the way to go for you.  I wouldn’t yet recommend the Sync Framework for device sync since its wire protocol is currently based on the DataSet which may cause out of memory errors on Windows phones with limited working sets.

So where does this leave RDA?

The reason I’m writing this blog post is because time and time again I run into customer sync scenarios that don’t always need the power of Merge or the extra flexibility of the Sync Framework.  Most field service applications follow the same kind of pattern:

  1. Lots of download-only lookup/reference tables that aren’t changed by the user.
  2. Tables that are pushed down to the device that tell a user where to go and what to do.
  3. Tables (sometimes empty) that are used to capture data from the user in the field that are upload-only.

These kinds of schemas don’t require conflict resolvers or server change tracking and are therefore well suited for RDA. 

What’s the big benefit of using RDA if a sync scenario meets its criteria?

  1. You won’t modify SQL Server’s schema with GUIDs and Triggers.
  2. You won’t degrade the performance of SQL Server by having it track changes and maintain extra metadata.
  3. You will have the fastest and most scalable sync solution with least amount of hardware.
  4. Time to market is shorter.

The big takeaway here is that I want you to consider your sync solution carefully before choosing a technology.  If your customer’s needs are met by RDA, then you should use it and reap the benefits of developing and deploying a simpler solution with fewer moving parts.

Remember Occam’s Razor.

-Rob

 
1 Comment

Posted in Sync