locked
How should I architect this small application RRS feed

  • Question

  • I am writing a desktop application that will need the Sync Framework to connect to a web service to update its data. I would like to use a SQL Server Express database on the desktop application so I can avoid the limitations of using SQL Server Server Compact edition (no stored procedures, file size limitation). The application will be an "occasionally connected" application, and it sounds like the Sync framework is a perfect solution but, I am having trouble getting started. I have a couple of questions...

    1) Can I use SQL Server express edition on the client machines without installing SQL Server? In other words, can I just create the local database in my Visual Studio project and deploy it with the project to the client machines?
    2) Will the sync code work with SQL Server Express, and call out to the web service that is already in use by other related applications that use the same source database that this new occasionally connected app will use.
    3) How can I create the supporting tables, etc. (tombstone tables, etc), on the source server? The Local Data Cache wizard that would normally take care of this in Visual Studio will create a SQL Server Compact Edition database as the local DB, and I can't use SQL Server CE (it doesn't provide Stored Procedures functionality). So, I can't use the wizard to do this. So, I can I get the appropriate supporting objects on the server DB?

    I have seen one example application here: http://blogs.msdn.com/b/sync/archive/2008/06/24/sample-sql-express-client-synchronization-using-sync-services-for-ado-net.aspx but, I can't get that app to work on my machine because of many, many compatibility issues.

    I have read a ton of documentation on this but, it seems the more I read, the more confused I become.

    Any advice about how to architect this solution, or how to get started would really be appreciated.

    Thanks. in advance.

    Monday, June 6, 2011 8:03 PM

Answers

  • to answer your questions:

    1. since you chose Sql Express, you need to install Sql Express on each client.

    2. If you want to re-use your existing web services, you will have to write a custom provider to interface with it (not an easy one). Sync Fx works on the concept of having providers at each end of the sync topology (think of them as drivers for each side). There is no out of the box Web service provider althought the existing SQL providers can be configured to work with WCF.

    3. The Local Database Cache wizard uses an older set of providers and doesnt come with a provider for SQL Express. The sample you mentioned above is just a sample of how to write a custom provider and doesnt work with the wizard either.

    I suggest you use the Sync Framework 2.1's SqlSyncProvider instead whichs works with Sql Express, Sql Server and Sql Azure out of the box. Once installed you can find sample walkthroughs in the documentation or you may check out: http://msdn.microsoft.com/en-us/library/bb902853(v=SQL.110).aspx

    you can find other sample apps at: http://code.msdn.microsoft.com/sync

    • Marked as answer by rknowles Tuesday, June 7, 2011 12:03 PM
    Tuesday, June 7, 2011 12:45 AM

All replies

  • to answer your questions:

    1. since you chose Sql Express, you need to install Sql Express on each client.

    2. If you want to re-use your existing web services, you will have to write a custom provider to interface with it (not an easy one). Sync Fx works on the concept of having providers at each end of the sync topology (think of them as drivers for each side). There is no out of the box Web service provider althought the existing SQL providers can be configured to work with WCF.

    3. The Local Database Cache wizard uses an older set of providers and doesnt come with a provider for SQL Express. The sample you mentioned above is just a sample of how to write a custom provider and doesnt work with the wizard either.

    I suggest you use the Sync Framework 2.1's SqlSyncProvider instead whichs works with Sql Express, Sql Server and Sql Azure out of the box. Once installed you can find sample walkthroughs in the documentation or you may check out: http://msdn.microsoft.com/en-us/library/bb902853(v=SQL.110).aspx

    you can find other sample apps at: http://code.msdn.microsoft.com/sync

    • Marked as answer by rknowles Tuesday, June 7, 2011 12:03 PM
    Tuesday, June 7, 2011 12:45 AM
  • Thank you so much for your answers. 

    I want to avoid installing SQL Express on the client machines, if possible. So I think I should investigate the use of either SQL CE or SQLLite.

    The only reason I wanted to use SQL Server Express is because it supports stored procedures, and my previous applications that use my source database have all relied on stored procedures. If I can find an effective way to replicate/duplicate the stored procedure functionality of the server database in the client database then, I would be happy to use SQL Server Compact. 

    I have found this video (login required to view) http://www.sqlshare.com/media.aspx?vid=483& that seems to lay out a pretty simple way to replicate stored procedures when using SQL CE...store a .sql file for each stored procedure in your project and point to them using a resource file, then, when executing a command on the SQL CE database, load the .sql file from resources and execute it. Looks pretty simple. I will dive into it and see how far I can go. I will post back to this thread if I discover any show stoppers. 

    I think I will try to do this using SQL CE, if possible. It seems to be the "native" database for the Sync functionality, and it won't require me to install db software.

    Thanks again for the great information and advise. I was really stumped there for a while.

    best regards.
    Roger 

     

    Tuesday, June 7, 2011 1:28 PM