我的订户数据库失去了与发布者的连接，并且过期了.可以保存我的数据吗? [英] My subscriber database lost connection to the publisher and expired. Can my data be saved?

问题描述

我有一个发布者数据库A，并且有两个订阅者A的订户数据库B和C.我的应用程序本地驻留在站点B和C上，并且通过复制，B和/或C处的更改可以彼此复制./p>

问题是自2019年1月31日起，C停止订阅A，并且站点C的IT人员对此一无所知(没有警报).

更大的问题是，在此期间，使用B处的应用程序的人一直在输入要复制回A的数据.同时，站点C的人一直在将数据添加到数据库C中，而该数据库没有被复制回

如果我恢复订阅，它将在A处获取数据并覆盖C，这是一个主要问题，因为在此期间我将丢失在C处添加的数据.由于这是运行状况数据，因此所有数据都已加密并存储在xml中格式，它不只是更新丢失的数据那样简单，因为某些文件在站点B和C之间共享，这意味着它们会将数据添加到保存的xml树中.

例如，如果某人在站点B看到患者并输入便笺，它将更新一个文件.但是，如果下周下同一个病人去站点C，那里的护士将更新在站点B上更新过的文件.

我不知道如何在恢复新订阅之前先同步回C处所做的更新.

如果有人有任何建议，将不胜感激！

解决方案

多年来，我们一直在使用合并复制，有时(例如在更新数据库之后)它确实会出现奇怪的错误，无论我们尝试什么，都无法修复.在所有这些情况下，唯一有效的解决方案是重新初始化订阅，甚至删除并重新创建具有所有订阅的发布.

但是由于这是合并复制的情况，并且在未发生同步时在所有末端(即发布者和所有订阅者)都添加了新数据，所以我们不能简单地重新初始化订阅，因为就像您指出的那样，在失败的订户方将丢失.您的案例更加困难，因为您正在使用加密和XML文件来存储数据.您是否将加密的XML作为BLOB/CLOB存储在数据库中?

但是，我将提供对我们有用很多次的解决方案.因此，如果合并复制的一个或多个端点失败，则必须采取以下步骤:

1. 使用诸如 Red Gate SQL数据比较(可免费试用14天)或免费 解决方案

We have been using merge replication for years and sometimes (after DB updates for example) it does break with weird errors which are impossible to fix no matter what we try. In all those cases the ONLY solution that worked was to reinitialize subscriptions or even remove and re-create publications with all subscriptions.

But since it is the merge replication case and new data is being added at all ends (i.e. publisher and all subscribers) when syncs are not happening we cannot simply re-init subscriptions because like you have pointed out not synchronized data generated at failed subscriber's side will be lost. Your case is even more difficult because you are using encryption and XML files to store data. Do you store encrypted XML as BLOBs/CLOBs in DB?

However, I will provide the solution which has worked for us many times. So, if one or more endpoints of merge replication fails the following steps must be taken:

1. Manually add non-synchronized data to all endpoints using tools like Red Gate SQL Data Compare (commercial with free 14-day trial) or free SQL Server Data Tools (SSDT) from Microsoft. The Red Gate's tool is very powerful. You can define custom mappings of tables/columns between databases for comparison. While SSDT from MS at this time can only compare tables having equal names and same schema name. Both tools can generate insert/update/delete scripts to get your data in sync. They even automatically disable and then restore foreign key constraints checks.

2. Change your publication and set Action if name is in use property to Keep existing object unchanged for all articles.

3. Reinitialize subscriptions.

After you perform step #2 and re-init subscription replication agent will still take time to process existing records (and depending on data set size it may take quite some time so think about adding Date filters to articles) but it will not change any data, no deletes or inserts will happen if both publisher and subscriber databases are fully synchronized after step #1. The agent will only mark those existing records as processed to skip them during future runs, so you will only see Update commands counts increased at Replication Monitor. Once subscriptions are reinitialized you should be fine and new data generated at any side will get synchronized.

Your case is difficult because you can't easily perform manual data sync from step #1. But this is a pre-requisite and you will need to think how to do that. You didn't provide details of how you actually store the encrypted data so I can't suggest the detailed plan but somehow you need to manually decrypt existing XML records, insert missing parts then encrypt again and make sure the encrypted columns are equal at all sides.

Hope this helps you to resolve your problem.

PS. I'm not affiliated anyhow neither to Red Gate nor to Microsoft.

这篇关于我的订户数据库失去了与发布者的连接，并且过期了.可以保存我的数据吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！