locked
Data Sync RRS feed

  • Question

  • hey...can you please tell me how to sync sql azure with sql express. i want to sync the Contents with Stored Procedures which would filter the data and give it to sync framework and then sync framework will save that filtered data into clients DB.

    Is it possible? can you please give me some sample code as soon as possible.

    Thursday, February 11, 2010 12:19 PM

Answers

  • thnx Liam for the help, links and the information.
    But still i am unable to get the desired result.
    i just want my Sql DB to be synchronized with Sql Azure DB. But I dont want to sync my whole db just Specific Record should be Synchronized rather than the Whole DB.
    For example i have some data of 100 users. and i only want to synchrnoize the data of client no 45 on that clients machine and Client no 55 should be synchronized by his/her data only.
    Please tell me how to do this as i didnt find any example doing this on Google and i need it.
    reply ASAP.

    Regards
    Samee Javaid

    Hi Samee,
    It's possible that SQL Data Compare could satisfy your use case. We've release an early beta that has rudimentary SQL Azure compatibility. Feel free to download it as it will be free to use until it is officially integrated into the product. In exchange we'd welcome your feedback on how well it works for you. Download the build by following this link:

    http://www.red-gate.com/Azure

    Kind regards,
    David Atkinson
    Product Manager
    Red Gate Software
    Wednesday, February 24, 2010 2:40 PM

All replies

  • Hi,

    Please use:

    http://sqlazuremw.codeplex.com/releases/view/32334
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, February 12, 2010 3:05 AM
  • Hi Langrial,

    The answer to your question is somewhat of a long answer because for the CTP we chose not to officially support SQL Express, howerver based on customer feedback this is something we are looking at for the next release.  Ultimately the issue is not SQL Express support but rather the lack of SQL Management Studio & SQL Agent which the tool relies on for initiating sync.  However, let me see if I can help you get around this...

    The first thing you need to do to (just to get started) is to use a version of SQL Server that includes SQL Server Management Studio (SSMS) with SQL Agent enabled.  Once you have this you should be able to take your SQL Server database and enable sync to SQL Azure in the normal way.

    From this, you would likely want to use a SQL Express databases that does not have SQL Agent (or SSMS).  If you want to have multiple SQL Express databases you can follow the information here on how to get another database to participate in sync:
    http://blogs.msdn.com/sync/archive/2009/12/14/how-to-synchronize-multiple-geographically-distributed-sql-server-databases-using-sql-azure-data-sync.aspx

    The biggest thing workaround you will need is to figure out how to initiate sync by not relying on the SQL Agent process or the sync exe we include in the tool.  To accomplish this all you need to do is write a simple ~10 line application.  Luckily there is a really good example on how to do this here:
    http://msdn.microsoft.com/en-us/azure/ee960244.aspx

    This application will allow you to tie a SQL Express database to SQL Azure without requiring the use of SQL Agent.

    I hope this helps and please let me know how it goes...

    Liam Cavanagh


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Monday, February 15, 2010 6:13 PM
  • thnx Liam for the help, links and the information.
    But still i am unable to get the desired result.
    i just want my Sql DB to be synchronized with Sql Azure DB. But I dont want to sync my whole db just Specific Record should be Synchronized rather than the Whole DB.
    For example i have some data of 100 users. and i only want to synchrnoize the data of client no 45 on that clients machine and Client no 55 should be synchronized by his/her data only.
    Please tell me how to do this as i didnt find any example doing this on Google and i need it.
    reply ASAP.

    Regards
    Samee Javaid
    Tuesday, February 16, 2010 12:42 PM
  • Hi Samee,

    The only filtering that this version of the tool provides is the ability to filter by table (meaning it does not have the ability to filter by row or column).  Although since this tool is ultimately based on the Microsoft Sync Framework that does support filtering, with some work you should be able to accomplish this.  I should also note that this type of filtering is very high on the request list for future versions of this tool.

    To get started on this you will need to understand how the Sync Framework uses the concept of "scopes" to define data that is to be synchronized.  This tool simply creates an un-filtered scope and what you would need to do is manually create a filtered scope.  There is a lot more information on this here:
    http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx

    I apologize that we do not yet have a wizard based solution to help you configure this right now, but hopefully this will get you started down the right track.

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Tuesday, February 16, 2010 5:47 PM
  • Hi Liam,

    Thanks again for the Help. Please tell me which tool you  are exactly talking about in your above reply. are  you talking about Sql Express or some other tool. Secondly Check out the link

    http://msdn.microsoft.com/en-us/library/bb726003(SQL.105).aspx
      this link is explaining the rows and columns Filteration and you said that the tool doesnt provide the facility to filter rows and columns.
    And let me clear you my requirement once again.
    "I want to synchronize our Database with Sql Azure filteration(rows and columns) using Sync Framework".

    Best Regards,
    Samee Javaid
    Wednesday, February 17, 2010 6:50 AM
  • Cannot initialize the client database because the schema for table 'Customer' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Ensure that you can establish a connection to the client database, and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly.

    Can you also guide me about this exception  i have tried a code according to my requirements and I am getting above exception.
    You can find the Code here

    Best Regards,
    Samee Javaid
    Wednesday, February 17, 2010 1:37 PM
  • Hi Samee,

    The tool I was referring to is the "SQL Azure Data Sync Tool for SQL Server" that is part of the Power Pack for SQL Azure (found under Programs | Sync Framework | SQL Azure Data Sync Tool for SQL Server). 

    I also want to be clear about the capabilities for doing filtering. 

    You can really think of this SQL Azure Data Sync Tool for SQL Server as a wizard that was built completely on top of the Microsoft Sync Framework.  Ultimately everything you can do with the wizard, you could have done manually using just the Microsoft Sync Framework.  The Microsoft Sync Framework itself has a LOT of capabilities in that it can sync any type of data content, with any type of store (database, filesystem, web services, outlook, etc) over any type of network over any type of architecture (i.e., Peer-to-peer, hub-and-spoke, multi-master, etc).  As you can imagine with all these capabilities it can take some time for a developer (or DBA) to figure out how to get started.  As such, over time we have seen a number of very common scenarios that use Sync Framework and this SQL Server to SQL Azure sync is one of them.  Because of this we felt it made sense to give a tool like this one that allows people to get up and running quickly.  Ultimately, this is really just a method for saving you time in getting started and like I mentioned before is really something you could have done yourself manually with the Sync Framework. 

    The great part about this is that everything we configured in this tool is something that is 100% compatible with what you would have done manually with the Sync Framework.  Even the Synchronization EXE that the SQL Server Agent calls is merely a Sync Framework application that syncs a SQL Server provider with a SQL Azure provider.

    So even though we have not built all of the capabilities of the Sync Framework into this tool (like filtering), there is no reason that you can not extend this yourself by customizing it using the existing capabilities of the Sync Framework. 

    Once again, I 100% agree that filtering is really something that is very common for most users and is really something that should be in this tool, and it is is very unfortunate that you have to do this manual work to implement it, however the good news is that we have heard this feedback loud and clear and will be working to fix this in the future.

    I hope that helps clarify things.

    Liam Cavanagh
    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Wednesday, February 17, 2010 5:01 PM
  • Hi Samee,

    We are pretty sure this error: "Cannot initialize the client database" is from an older version of the Sync Framework database provider.  Can you confirm that you in fact got this error from using the "Power Pack for SQL Azure" or "Sync Framework 2.0"?

    Liam
    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Wednesday, February 17, 2010 5:49 PM
  • Hi Liam,

    Thanks for the help. Yes you are right you people should consider this for your next release it would be really very benificial for every one who is trying to use the sql azure as in my scenario and  I have totaly understood what you said. Thanks again for this detailed Help.

    Samee Javaid

    Thursday, February 18, 2010 5:39 AM
  • Hi Liam,
    I m using Sync Framework 2.0 and i am getting this error when i try to call the synchrnonize method. and secondly can you please provide some code which is doing the filteration even on just one table. This would be really helpful and can you please give me your live id where we can chat?



    Samee Javaid
    Thursday, February 18, 2010 5:43 AM
  • thnx Liam for the help, links and the information.
    But still i am unable to get the desired result.
    i just want my Sql DB to be synchronized with Sql Azure DB. But I dont want to sync my whole db just Specific Record should be Synchronized rather than the Whole DB.
    For example i have some data of 100 users. and i only want to synchrnoize the data of client no 45 on that clients machine and Client no 55 should be synchronized by his/her data only.
    Please tell me how to do this as i didnt find any example doing this on Google and i need it.
    reply ASAP.

    Regards
    Samee Javaid

    Hi Samee,
    It's possible that SQL Data Compare could satisfy your use case. We've release an early beta that has rudimentary SQL Azure compatibility. Feel free to download it as it will be free to use until it is officially integrated into the product. In exchange we'd welcome your feedback on how well it works for you. Download the build by following this link:

    http://www.red-gate.com/Azure

    Kind regards,
    David Atkinson
    Product Manager
    Red Gate Software
    Wednesday, February 24, 2010 2:40 PM
  • Liam,

    Hopefully you're still watching this thread.  First of all, thanks for all of the great information.  As I've been researching SQL Azure and MS Sync in the last couple of days, I've seen your name pop up several times.  Thanks for your time and effort.

    I was reading the article you pointed to (http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx) about creating filtered scopes.  I'm trying to figure out whether they are really a workable solution for the scenario that Langrial mentioned, where you want to filter data by user.

    My impression from reading the article is that filter scopes seem to be geared towards having a few broad filter scopes in the database.  For example, "sales orders in Washington".  If I'm understanding this correctly, filter scopes are registered on the server and require some sort of metadata for each one in the server data store.  It seems like that could get out of hand very quickly when you're creating one scope per user.

    For example, what if you were creating something like Exchange Server, where you are storing email for thousands of users, all of whom are using Outlook as their offline-enabled synchronization application?  It sounds like to enable that scenario, where a given client should only be receiving their own email from the server, you would be creating a filter scope for every single user.  Is that really an expected use case for filter scopes and the Microsoft Sync Framework?

    I am building a mobile application where each user will have only their data on their device, and hoping to store the user data in the cloud.  The mobile clients will be occasionally connected, periodically synchronizing to get new server-side data from the cloud and submit data created on the mobile device back into the cloud.  The marketing information for Sync Framework makes it sound like it's tailor-made for that situation.  But is it?  Am I going down the right path by considering MS Sync Framework, or is this sort of user-based filtering/synchronization use case not something it's designed to support?

    Thanks,

    David Cater

    Sunday, April 4, 2010 10:53 PM
  • Hi David,

    Yes, I believe you are correct in your assumptions.  With the current version of Sync Framework that you are using we would require a new scope for each different filter value.  Obviously that is pretty tough to support of 100's of users each have their own filter value.  One of the things that we are hoping to get into the next release is the concept of filtered scopes.  Basically what this would allow you to do is define a single working "Sync Group" and then within this you define the tables you want to sync along with a parameter to be used for the filter values.  This would allows us to provide a much simpler way to support the case where you have 100's of clients with unique filtered values.  Basically you could dynamically generate a scope for the new client base on their new filter value.

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Wednesday, April 7, 2010 11:59 PM
  • Thanks for your reply, Liam.  My situation is potentially complicated even more by the fact that there can be multiple filters on a given device running off the same local cache.  For example, a parent and their children could all have registered accounts on the device, and my synchronization would need to take that into account.  That becomes an issue when you add accounts to the device.  For example, a parent has logged into the device themselvs, and has also added an account for one of their children.  After a sync has been performed, at some point they hand the device to their second child and have them login to the device. 

    At that point I may have up-to-date information for the parent and the first child, but the Sync Framework presumably wouldn't know what to do with a newly added child that needed to be added to the filter.  The last time I synced the child wasn't in the filter, so this time I'm not sure how it would know how much the local cache "doesn't know" about the new child.

    If you're interested, I have a separate thread on this topic started in the Sql Azure forum.  Initially the question was about using Sql Azure Data Sync for my project, but it veered pretty quickly into a general discussion about whether Sync Framework was going to work for me:

    http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/ae340223-18b5-4f9f-a857-3c80f8056a43

    Thanks again,

    David

    Thursday, April 8, 2010 2:27 AM
  • For the people that are looking to Sync with SQL Express.  This webcast I did recently might also be of use:

    http://blogs.msdn.com/b/sync/archive/2010/08/31/sql-server-to-sql-azure-synchronization-using-sync-framework-2-1.aspx

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Friday, September 3, 2010 4:59 PM
  • Liam, Jocelyn is a spammer who add a hidden seo link to each of the reply, you can look at the HTML source and find the seo link. The words used in the replies are very, very vague and do not add value to the discussion so the reply can be acceptable to the wide scope of forums being posted. It is not a good reply to be suggested as an answer.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Monday, January 31, 2011 4:17 AM