locked
Question regarding the fesability of an idea! (will sync services fit in with our needs?) RRS feed

  • Question

  • Hi There,

    We have been tasked with building a windows mobile interface to our website, which is built using ASP.NET and MS SQL 2005 (it is hosted on a third party shared server and we dont have any control on the software installed on it).

    The basic premise is that we need the mobile devices to be able to grab data from the database, work with it offline, and then have the data merged back onto the main database (hosted remotly on a shared server).

    Is there anyway we can use sync services to accomplish this, bearing in mind we have no control over the database server. From what we have looked at so far it appears that we could run MS SQL Server Compact on the device, which would hold the relevant info. Basically, whats the way to move data back and forth?

    Before we discovered sync services we were consdering using a web service and working with XML on the device. 

    If sync services can be used are there any tutorials or guides that would be relevent?

    Thanks in advance!
    • Moved by Hengzhe Li Friday, April 22, 2011 5:19 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, March 21, 2009 12:11 AM

Answers

  • Since your main concern/issue seems to be the fact that you can only access the remote database with SELECT/UPDATE/INSERT/DELETE SQL statements (am I understanding correctlly?), then Sync Services will not work, as it requires schema (structure) changes on the source database. I suugest the following architecture:

    Use SQL Compact RDA for downloading the required tables to the device.  (Supports better device performance and compression compared to web service). This will require you to configure a web server with the SQL Compact merge agent, that has read access to the database and can be reached from the Mobile devices.

    On the device, create additional tables to hold information about any inserts, deletes and updates as required. (as a kind of change log)

    On your web server, create a web service to handle the device data manipulations and database updates and write these changes back to the source database, based on the device change log. This allows you to handle/log any conflicts and errors centrally.

    This architecture will not requires any changes on the hosted database.

    Hope this helps.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Sunday, March 22, 2009 11:10 AM
  • Please notice that the key differience between the design Erik described and the Ntier config of Sync service is "what privillege you have on the backend sql serer from the IIS box. Sync Service requires some level of tracking ( will need to modify the table schema, create trigger etc ). if you don't have ( or the third party who controls the Sql server is not able to create this for you ), then sync service won't work and what Eric propsed will be the choice.

    this would be a case for an envrioment where SQL server is control/maintained by the DBAs, and the sync app is controled/maintained by other folks ( like app dev, app users or other  Admins ). in order to make the sync service work, the DBAs and the Admis/appDev need to work together on the end to end sync solution.

    As Eric mentioned, currently there is a perf issue in the sync service on device and this is another consideration when you compare with RDA -- we are working on the perf improvement and will post some fixes once they are finalized

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 22, 2009 8:21 PM
    Moderator

All replies

  • This is something that i am curious about too, does anyone know?
    Saturday, March 21, 2009 9:34 PM
  • the Ntier configuration of the sync service should fit this case well. can you take a look at this sample in this post
    http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/b4e7f7af-dbb6-4db0-9250-66c4a359468b

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 22, 2009 4:49 AM
    Moderator
  • Since your main concern/issue seems to be the fact that you can only access the remote database with SELECT/UPDATE/INSERT/DELETE SQL statements (am I understanding correctlly?), then Sync Services will not work, as it requires schema (structure) changes on the source database. I suugest the following architecture:

    Use SQL Compact RDA for downloading the required tables to the device.  (Supports better device performance and compression compared to web service). This will require you to configure a web server with the SQL Compact merge agent, that has read access to the database and can be reached from the Mobile devices.

    On the device, create additional tables to hold information about any inserts, deletes and updates as required. (as a kind of change log)

    On your web server, create a web service to handle the device data manipulations and database updates and write these changes back to the source database, based on the device change log. This allows you to handle/log any conflicts and errors centrally.

    This architecture will not requires any changes on the hosted database.

    Hope this helps.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Sunday, March 22, 2009 11:10 AM
  • Thanks Guys,
    I am now in the process of checking out both suggestions and will get back to you soon.

    Regarding the database, we have control over the schema, but can't add any more software to the system, as its simply a public third party shared web server. I wasn't sure if Sync Services would need to install something on the SQL server (which in this case is SQL Server 2005).

    Thanks again guys!
    Sunday, March 22, 2009 12:41 PM
  • Please notice that the key differience between the design Erik described and the Ntier config of Sync service is "what privillege you have on the backend sql serer from the IIS box. Sync Service requires some level of tracking ( will need to modify the table schema, create trigger etc ). if you don't have ( or the third party who controls the Sql server is not able to create this for you ), then sync service won't work and what Eric propsed will be the choice.

    this would be a case for an envrioment where SQL server is control/maintained by the DBAs, and the sync app is controled/maintained by other folks ( like app dev, app users or other  Admins ). in order to make the sync service work, the DBAs and the Admis/appDev need to work together on the end to end sync solution.

    As Eric mentioned, currently there is a perf issue in the sync service on device and this is another consideration when you compare with RDA -- we are working on the perf improvement and will post some fixes once they are finalized

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 22, 2009 8:21 PM
    Moderator