locked
SQL Express 2008 client with change tracking RRS feed

  • Question

  • What is the current status regarding SQL Express 2008? I know there's a sample available for implementing your own ClientSyncProvider to work with SQL Express, but it doesn't seem to use change tracking, and it seems to target earlier versions of SQL Express.

    Is Express 2008 with change tracking not currently supported out of the box? And are there plans to add support for it?
    And if so, how long can we expect to wait?

    At the moment, we are using a SQL CE database, but we'd like to switch to an Express DB as soon as possible, but if it requires a lot of extra work (implementing and maintaining our own ClientSyncProvider, which may or may not support change tracking) we might just be better off sticking with SQL CE a few more months.
    • Moved by Hengzhe Li Friday, April 22, 2011 3:26 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, October 20, 2008 7:21 PM

Answers

  • Hi Guys,

     

    Here’s a post on the info from the PDC session.  PDC 2008 - Embedding SQL Server Compact In Desktop And Device Applications  It includes a screencast of the session.  As Sean and Abid mentioned you can actually get better performance for local data scenarios with Compact as it’s optimized for in-proc access.  Express, being an entry point to our server platform is optimized for network and concurrent access from thousands of clients.  However, as the queries do get more complex, >5 joins and other complex operations, you will see SQL Server curve back up.  The question is which tool has a better balance to the overall needs.  An SUV can suit most, but some must have a Box truck or tractor trailer.  Compact and Express have tradeoffs related to deployment and performance.  In the PDC session I focused on the simplicity of deployment, and the power that can be surfaced from Compact with just a few extra lines of code to fill the gaps for what SQL Server provides with connection pooling and query plan caching.

     

    Ultimately, we’re not here to convince you of one Microsoft product over another.  Both are great products.  Just looking to make sure you have enough info to understand the choices.

     

    Steve

    http://blogs.msdn.com/SteveLasker

    Monday, November 3, 2008 4:19 PM

All replies

  • Jalf,

     

    What is compelling you to make the switch to SQL Express?  We have had quite a few requests to update the sample to support change tracking but do not have any firm plans on when this will occur.  The nice thing about Compact is that it is what we call a "lights out" provider.  Specifically, the only work required on your end to enable Compact for synchronization is to specify the tables you want to sync.  SQL Compact has a built in change tracking layer that the Sync Framework leverages, negating the need for you t define the commands used to get and apply changes.  As you pointed out, the SQL Express sample is significantly more complex.  Steve Lasker has a great blog that talks to addressing some of the requirements that frequently come up when evaluating whether to use SQL Express or Compact here:

     

    http://blogs.msdn.com/stevelasker/

     

    Let me know if this helps.

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Tuesday, October 21, 2008 2:56 AM
    Moderator
  • Our problem is mainly that our application does a lot more than just sync'ing, and a more powerful database would be nice (Stored procedures would be nice, for example). We're also experiencing some performance issues with some of our queries, which we hope would perform better with SQL Express (I haven't tested that yet, however). So mostly, we want to switch to Express for the sake of the "non-sync" part of the application.

    I agree with you that if you're just syncing, the argument for using SQLCE is pretty compelling.

    The problem is that sync is just a small part of our application, and the rest of it would like a bigger database. Smile

    So what you're saying is that there are no concrete plans for when Express clients will be supported out of the box?
    Tuesday, October 21, 2008 7:43 PM
  • My two bits in re why we want SQL Express instead of CE:

     

    With SQL CE, the initial schema sync does not bring down FK relationships - they have to be added manually. This is hugely important, especially when using LINQ to SQL to generate client code.

    Monday, October 27, 2008 4:07 PM
  • I am also trying to sych two sql server express 2008 dbs between two computers. The problem with current sample is too much work need to be done to make it work. I assume with change tracking enabled, it will be much simpler. If somebody can give an example with change tracking enabled, it would be of great help.
    Tuesday, October 28, 2008 7:58 PM
  • Regarding performance, because sql compact runs in proc in many cases it outperforms sql express significantly.  Steve Lasker gave a great presentation on this topic at PDC last week and I suspect at some point he will post the details to his blog soon.

     

    I want to drill into your need for a "powerful" database. Some of the advantages that SQL Compact has over express are its extremely small footprint (<1MB vs 150 MB), ease of deployment and a great suite of development tools that ship with Visual Studio.  It sounds like size might also be be an issue.  Specifically, are you referring to the 4 GB limit for Compact?  I find that client side databases > 4 GB oftentimes are not taking advantage of some of the filtering capabilities that ship with Sync Services for ADO.NET so you may want to consider that as well as IT may improve the latency of your sync operations significantly. 

     

    I do not think that SQL Compact is good for sync only.  Sync is a means by which to get data down to the client which presumably has an application built on top.  The Compact team has made a significant amount of progress regarding integration with LINQ and the Entity Framework which makes application development significantly more productive.  Note that Express has the same integration so this is not meant to be an advantage but rather just pointing out something that is oftentimes overlooked.  SQL Compact does light up around sync scenarios insomuch that we ship a significant number of features in the box to decrease the amount of work around tasks such as schema creation, conflict detection, conflict resolution, filtering, change tracking etc.  Note that none of these features ship for SQL Express today requiring much more work on your end in order to achieve the same level of functionality.

     

    Stored procs are a common request but I believe Steve has some guidance around creating managed procs to mimic the behavior.  Not a perfect answer I realize but something to consider.

     

    No firm dates yet for an Express provider. Sorry guys.  Is this an adoption blocker for you and if so, which features would you want added to SQL Compact in order to eliminate this barrier to entry?

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Saturday, November 1, 2008 10:13 PM
    Moderator
  • Hi Jalf,

     

    I would be happy to follow-up more on this thread to better understand how Compact can be more suitable to your requirements.

     

    Please feel free to ping me via http://blogs.msdn.com/sqlservercompact/contact.aspx

     

    Regards

    Abid

     

    SQL Compact - Program Manager

     

    Monday, November 3, 2008 9:53 AM
  • Hi Guys,

     

    Here’s a post on the info from the PDC session.  PDC 2008 - Embedding SQL Server Compact In Desktop And Device Applications  It includes a screencast of the session.  As Sean and Abid mentioned you can actually get better performance for local data scenarios with Compact as it’s optimized for in-proc access.  Express, being an entry point to our server platform is optimized for network and concurrent access from thousands of clients.  However, as the queries do get more complex, >5 joins and other complex operations, you will see SQL Server curve back up.  The question is which tool has a better balance to the overall needs.  An SUV can suit most, but some must have a Box truck or tractor trailer.  Compact and Express have tradeoffs related to deployment and performance.  In the PDC session I focused on the simplicity of deployment, and the power that can be surfaced from Compact with just a few extra lines of code to fill the gaps for what SQL Server provides with connection pooling and query plan caching.

     

    Ultimately, we’re not here to convince you of one Microsoft product over another.  Both are great products.  Just looking to make sure you have enough info to understand the choices.

     

    Steve

    http://blogs.msdn.com/SteveLasker

    Monday, November 3, 2008 4:19 PM
  • express provider would be a huge plus for us. our situation is we have a huge horrible database built for web services (full of sprocs, triggers, arrgggghhh) that we have to somehow cram into an "outlook" offline model on UMPCs, using sync framework to sync with the master database on the server.

    we dont have time to re-engineer the whole thing for sql compact since we only have a few days to finish this so using the database as it is attached via sqwl express is the only option.

    an express provider would SAVE OUR SKIN right about now!!!
    Friday, November 7, 2008 9:33 AM
  • It has been a few months since the original post, so can someone in the know please provide us with an update on the current status regarding the SQL Express ClientSyncProvider?
    Saturday, March 14, 2009 7:09 AM
  •  Not having an sql express client provider is a killer for me too.  As in my case, the "client" is really a small (3-4) group of users that will share the same DB. This DB then would sync up with a master database. Both running sql 2008. If i were to use compact, each client would have its own db, which i guess i could do, but my ORM also doesnt work as well with compact so i would much prefer the client to be sql express.

    Friday, March 20, 2009 7:47 PM
  • SQL Express is also the only option for us, as our app is 100% dependent on stored procedures and we have no plans to change that. The synchronization functionality is only a secondary concern but the lack of a SQL Express provider is very disappointing.
    Wednesday, April 22, 2009 1:07 PM