none
Using Sync Service for n-tier Applications RRS feed

  • Question

  • We're a software firm developing a desktop application for the education arena.  This application will require control tables that users at each location will need to share.  These control tables will be stored in a local SQL Express database on each workstation.  For example a location may have 3 - 5 installs of the application and all users within that location will need to share the same control tables.  We expect to have this application installed at 30 - 50 locations around the country.   It's very critical that locations cannot be exposed to other locations control table data.  Each location will have a unique identifier so I'm hoping that data between the user and the datastore could be filtered based on their unique identifier.  We would like to store all this control data in a centerally hosted SQL database.  Users would need to be able to work off-line in the event their connection to the centeral data store is unavailable.  Any changes they may have made would refresh once back online.
    I'm looking for suggestions on an approach to use Sync services to accomplish this task.  We had considered writing our own processes that accomplish this task using webservices.  In dealing with education customers, security is a high concern, and connections to services outside their domains is usually limited.  For example ports are normally locked down, so communicating via port 80 using web services has normally been our method of choice in the past.
    Any assistance would be greatly appreciated.
    Wednesday, March 17, 2010 1:24 PM

Answers

All replies

  • You can pretty much use Sync Framework for your application.

    There are two scenario's you can choose from:

    1. Offline scenario or hub-spoke - you have clients that syncs to a central server. However, there is no out-of-the box SQL Express client provider available. The SQL Express client provider available is just a sample provider. The offline client sync providers also has limited support for provisioning or creating the database objects needed for change tracking and sync. see http://msdn.microsoft.com/en-us/library/bb902819(SQL.105).aspx

    2. Collaboration or peer-to-peer - you can have peer-to-peer syncs between clients and between client and server. You can however configure your sync for hub-spoke by simply making sure that your clients only syncs with the server. There is an out-of-the-box client sync provider for SQLExpress and there's also provisioning APIs available. see http://msdn.microsoft.com/en-us/library/bb902853(SQL.105).aspx

    They both support filtering as well, with the latter supporting it via Scope filters.

    Both scenarios above can be enabled for n-tier setup using WCF, so that should take care of your requirements to sync via port 80. (see http://msdn.microsoft.com/en-us/library/bb902831(SQL.105).aspx and http://msdn.microsoft.com/en-us/library/dd918908(SQL.105).aspx)

    cheers,
    • Marked as answer by Curtis Broyles Wednesday, March 17, 2010 5:51 PM
    Wednesday, March 17, 2010 2:15 PM
    Moderator
  • Thank you so much June for your through response.
    Wednesday, March 17, 2010 5:52 PM
  • Hi Curtis Broyles.

    Coincidently, We are also stuck with a similar situation, we have around 1200 schools in state, the school are located in hill regions and connectivity is available to only 40% of the schools. Now we want to develop an application so that we may get student details, teachers attendance to calculate salary and various other information on Centralized Server and schools remain functional even if there is no connectivity for a month(It happens in our case.). 

    We have another situation, there are schools with no connectivity at all, so we need to dump the database in CD bring it to another connected school and then merge the data bidirectional. 

    We want to use SQL Express at all the clients and Sql Enterprise 2008 at Server(Hub & Spoke). 

    We would like to go for a no touch deployment from Web(Schools are located in remote locations and deployment, maintainability and support is a big concerns as because of landslide and extreme whether conditions travelling all round the year to the location is not possible) . Our client will need to connect using http.

    How to go about it?

    Thanks in advance.

     

     

     

     

    Thursday, September 2, 2010 8:59 AM
  • for the clients that has no connectivity at all where you actually dump the database to a CD and transport them onsite, you'll be better off using the collaboration providers (SqlSyncProvider). The provider allows you to send a copy of a fully initialized database to initialize other databases thru snapshot initialization. Likewise, it allows peer-to-peer synchronization either via direct connection and n-tier setups using WCF. Since the replicas can take some time before synchronizing again, you should consider increasing the retention period of the metadata.

    you will find working samples at: http://code.msdn.microsoft.com/sync

    Thursday, September 2, 2010 1:50 PM
    Moderator
  • Thanks JuneT!

    I appreciate your prompt response.

    I explored the resources as instructed. I tried my best to get hold of the things. As you know, knowledge facilitates more questions. I also studied the retention property.(Some of our client will never connect directly, we need to dump database and synchronize them from any connected point).

    In our case, we need to use n-tier(Hub and Spoke), we just need to take database to any connected node and need to write a utility so that the database can synchronize with Central Server Only(data security policy).

    I feel our application is a more of desktop base application with a facility to synchronize the database to server after certain interval, (Attendance daily, salary sheet monthly likewise). Only certain data is required to to be sent to the server on daily or immediate basis(very limited).

    I apologize for taking liberty, but I have a few more questions now.

    1. Handling Schema Changes.

    Our application is constantly evolving. We add more tables, add columns to existing tables.

    We can write a utility to propagate the schema changes to client.

    but

    i) When we change schema, the sync scope does not reflect the new added columns

    How to handle that?(Scope re-provisioning)

    ii) When application run only in disconnected mode(We plan to send update patches to reflect schema), the update patch will change the schema structure and UI, but after changing the schema, if we try to synchronize(take database to nearest connected node), will it work? Are we not going to lose earlier changes(after schema change and Scope re-provisioning)?

    Waiting in anticipation!

    Sen Gupta

     

     

     

     

     

    Saturday, September 4, 2010 4:44 AM
  • Sync Fx does not handle schema changes and neither can you modify an existing scope definition. afaik, sync knowledge is maintained per scope. So when you provision a new scope that includes you're new schema, that's going to be a totally new scope and will have no idea about the other scope' sync knowledge, so when you synchronize, it's going to behave like a first synchronization or newly initialized sync.

    if you're just adding new columns to the schema of the existing tables that are part of the scope, you're sync should still be work. It's just that only columns included in the current scope definition will be included in the sync and provided that the newly added columns has not constraints which would prevent the changes from being applied (e.g., you added a new column that doesnt allow nulls, if you sync, the new columns will not be included in the sync and thus no value will be passed when inserting new rows on the target).

    i think you can getaway with directly modifying the stored procedures and config_data column for the scope to add a new column or remove (provided it's not the pk or filter column which requires more changes).

    Sunday, September 5, 2010 3:05 PM
    Moderator
  • Thanks Again JuneT,

    Will SQL 2008 Change Tracking feature make things easier, we will have around 1200 Clients, performance wise?

    Sen Gupta 

     

    Sunday, September 5, 2010 4:47 PM
  • unfortunately, the collaboration providers/peer-to-peer providers (SqlSyncProvider/SqlCeSyncProvider) doesnt support SQL Change tracking.

    It's the offline providers (SqlCeClientSyncProvider/DbServerSyncProvider) that supports it (these are the providers used by the Local Database Cache project item wizard in Visual Studio). But then there is no out of the box offline provider for SQL Express other than a sample provider.

    Personally, changing schemas and adding tables is easier to implement with offline providers since the only metadata kept is on what was sent/received and is maintained per table and there is no metadata kept on what columns/tables are being synched (unlike the collaboration providers where the scope definition is persisted on the database).

    You can add/remove columns for as long you update your table adapters (and your adapters can be built dynamically). You can also add/remove tables from the SyncGroup dynamically. 

    Monday, September 6, 2010 1:23 AM
    Moderator