We have been using Azure DB Sync for some time now, however today the dss triggers have mysteriously been dropped from some of the tables.

We'll address the reason for the drop later, but for now is there a simple way to rebuild the triggers, without removing the database and attempting to add it back?

For now, I will script out the trigger creation scripts from another connected database in the same sync group, however I'm not sure if having the triggers is enough, I probably need to manually add the lines to the tracking tables as well, but a lot of data has changed over the last few days so I hope I don't miss anything.

Any one else dealt with this before? Does the sync agent have a repair option? If it does I can't find it.

remove the database and add it back might be the best option.

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller Friday, February 27, 2015 3:10 AM

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller Friday, February 27, 2015 3:10 AM

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller Friday, February 27, 2015 3:10 AM

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller Friday, February 27, 2015 3:10 AM

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller Friday, February 27, 2015 3:10 AM

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller 8 hours 20 minutes ago

remove the database and add it back might be the best option.

• Marked as answer by Chris.Schaller 8 hours 31 minutes ago

Hi Chris,

I agree with HoroChan, that please try to remove the database and add it back. Or could you please let us know that are there any blocker for doing it?

Regards,
Bowen

Yeah, um, no 5gb drop and re add... I simply don't have the bandwidth available to re-sync from the client.

The biggest issue for me is that this legacy app does a lot of DB structure maintenance, so the DB structure is likely to change more frequently than I would like to go back and re-configure the database.

I've nailed down the causes and built a script that will put the "instead of" triggers back.

For me it's about finding a repeatable solution that a monkey who doesn't know any better can perform because I don't have enough allocated capacity to be available personally every time a developer at their end changes the DB structure.

Before this feature goes off preview as a consumer I expect the default functionality to be to select the tables to sync and the framework should be able to dynamically adapt to DB structure changes, I expected as a consumer to use this DB sync feature to hybridise my legacy systems with cloud functionality. So far the management of the sync between evolving systems has added such a large overhead that its quickly becoming not cost effective when compared to rolling our own... which is simply disappointing.

Just further, what I was expecting was in the Sync Agent, a button to detect and repair sync configuration

- eg, rebuild the stored procs, tracking tables and triggers. There's only so much data structure manipulation that I want going on in a production DB. To properly remove the DB from the sync is messy enough.

the Data Sync service doesn't do schema syncs. If you frequently change your database schema, this may not be the right service for you. Likewise, please note that it is a Preview service as well which means it's direction can change.

you can hack the data sync service objects to reflect your schema changes, but those sync group definition changes are not known to the service as well. so the next time you add a new member database to the sync group, it will be provisioned with what Data Sync Service knows when you defined the sync group.

Exactly, because this is in preview, we need to have a discussion about this kind of functionality. I don't have any clients where their entire DB schema stays static for very long, I can (to a degree) accept that the Data Sync Service is not responsible for propagating schema changes through the sync group, but on that, it wouldn't be hard to allow additional columns to be automatically synced.

What I cannot accept is that there is no "Repair" or at least a "Validate" button or procedure that I could run from powershell to verify the health of the sync configuration.

Real world example: Client comes to me and says "...I can see the invoices on the web, but when I try to print all the invoices created this week they have no line items..."

So I go and look in Azure DB Sync logs, nothing out of place, everything reports as healthy. I then inspect my invoice item table on the Hub and can clearly see that all of the recently created invoices have zero line items (something LOB App would prevent).

I goto the client where the sync agent is running and check windows event logs, nothing interesting there, the Sync agent itself says it can connect to the sync hub and everything is good.

Next I get the client to run their LOB app and print me one of these invoices locally, sure enough it prints fine. Now check the DB tables, data looks good. Now I check the tracking table for invoice item, BINGO, no changes tracked this past week.

Turns out the "instead of" triggers for the invoice item table are gone, and for a few other tables.

What has happened is that the LOB app had an update which modified the invoice item table by adding additional tracking fields. to add some additional tracking information for their online payments system. This modification was implemented the "Management Studio Way" where the old table is renamed, and the data migrated to the new table created in its place.

For my solution, the net affect of these changes is zero, there is no change in the schema that I want to use, I don't need these additional fields to be synced at all, but I sure as hell expect the data to be.

It cost me 2 hours of my time to make my own windows dashboard that has a "Validate Sync" process and a "Fix Schema" process.

Where do I go, to talk to MS about having such a feature in their Sync service? You would expect it from any other enterprise application wouldn't you? Why don't we expect the same from MS, to remove a DB and re-add it under these circumstances is only serving the purpose of keeping a DBA in a full time role.

the feature request page is here: http://feedback.azure.com/forums/217535-sql-data-sync

from your post, it looks like you're actually using it in Production. this link explicitly states its not for production use. it looks like you didn't consider the feature limitations either.  if it's not acceptable to you not having a 'Repair' or 'Validate' feature, perhaps, you should not have used the service at all. it would have cost you less than 2 hours to go understand the limitations of the service before you use it.

Thankyou for the links, I will submit a feature request, initially I was looking for guidance before I got bored and figred it out anyway. That said, I'm really beyond the actual issue but I would like to continue the discussion in the hope that someone out there has come across a similar situation or requirement and has some useful outcomes to share with the rest of the community.

I'm sorry that I didn't open with a disclaimer that I have read the documentation, and am part of an agile development and release team, and that I make my clients sign all sorts of waivers before trail any of MS new software in their environments.

By definition, a "Preview Release", even according to your link states that it is "meant only for product feedback", well as far as anyone needs to know, I have done some trials and now have some product feedback, so the other initial question of mine was where to post said feedback, the feature request seems like a good place, now that I realise the feature is not already there, which was the other half of my initial request.

So thankyou, that was actually helpful.

If we filtered out all the comments in msdn forums that say things like "read the documentation" or "don't do that its in preview" there would likely be a lot less crap to sift through, sure there are a few noobs out there but can we please on some level assume that as a member of this technical community one in general would not post said questions without doing their due diligence first.

Sounds like you would be shocked if I listed the number of preview features that some of my clients are using in their "Production Environments". The nature of my work is to offer new ways for my clients to do things, we take them on as "Pilot Clients", many of our solutions have been used by MS reps at Tech-Ed and Build as case studies on how to use these new features, I even have one client in particular where every aspect of their environment is running in Preview features, even down to windows 10 and Visual Studio 2015. We acknowledge the risk and take that on under our own belt.

Sometimes I feel that if we don't push these boundaries before the product goes to general release who will?

If I choose to eat dog food that clearly states not for human consumption I take that risk. If I ask a question about the flavour of said dog food, I'm really only interested in hearing responses from others who have also tasted this brand, it's pretty unlikely that I would be surprised to find that dog food was not meant for me, but more importantly it doesn't help at all with the issue at hand.

Thanks JuneT, I have lodged a feature request for:

Add a "Validate" or "Repair" function

in the Azure feedback portal.