This is one in a series of posts on when and where to use a distributed architecture design in your organization's computing needs. You can find the main post here: http://blogs.msdn.com/b/buckwoody/archive/2011/01/18/windows-azure-and-sql-azure-use-cases.aspx

Description:

Organizations often need to share all or part of a data set, which is consumed by other systems. These systems can be on-premise or at another location, or even at a different organization.

Many times these systems use a well-defined data interchange system, such as EDI or other standards. In the case of a trusted system, simply using a direct connection into another database is the process used to transfer data. This process might be one-way or bi-directional.

But there are systems that transfer data back and forth in stages using intermediate systems. A typical data flow in this case looks similar to the following:

SADH-1

In this example, the owning system contains data set A. This is set to a staging system or server, where the receiving system collects it. The receiving system contains data set B and works with data set A to create a new data set, C. This new data is consumed by the original system to complete the cycle. A concrete example is an inventory control system. Data set A is the original inventory list, shipped to a manufacturer. The manufacturer consumes the inventory available, orders and components, and returns the ordering bid with any changes to the staging server as data set C. The data is consumed by the originating system and components are noted in the overall flow of data set A.

Note: Normally this is solved with a full EDI implementation, but this process is still a common practice.

There are other examples, but the general concept is one where the need is for two, possibly untrusted systems to share a common source of data.

Implementation:

One possible solution is to segregate the data that is being transferred into an agree-upon set of entities that can be added or edited real-time, where both systems (or many) feed from the same data set instead of shipping the data. This removes latency, improves data quality, and shares the cost of the data. Also, security is increased because there are no shared logins - each firm gets its own.

SADH-2

One consideration with this layout is that the source systems must be altered to use a shared data set. If this is not possible, this is still a possibility as the system can be used as it was before - a data transfer - but the data can be cleansed real-time by both systems. It’s also a more secure and shared-cost system even if used in the original manner.

Resources:

Security is a concern in this arrangement, so it’s best to understand exactly how the security works in SQL Azure: http://msdn.microsoft.com/en-us/library/ff394108.aspx 

Another possibility to solve this pattern is to use Data Sync, in many different arrangements that involve SQL Azure. You can learn more about it here: http://blogs.msdn.com/b/sync/archive/2010/10/07/windows-azure-sync-service-demo-available-for-download.aspx