c# - 架构更改后，数据库不会重新初始化给本地订阅者

Question

首先，我将概述我的问题，以防有人有替代解决方案。

问题:

我有一个使用 MergeReplication 的 winform 应用程序。这非常有效，除了我需要更改 5 个表 上的列 和主键。我将它们从文章 中删除，然后进行了更改。然后，我将它们重新添加到文章 并将发布 设置为Reintilialize All。

不幸的是，这不起作用。 当我运行 订阅程序时，它告诉我 订阅 无效。

编辑 1

我这里有更正/补充。我在复制监视器中遇到的实际错误是这样的 -->

Error messages:
The schema script 'tblCaseNotes_4.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Could not drop object 'dbo.tblCaseNotes' because it is referenced by a FOREIGN KEY constraint. (Source: MSSQLServer, Error number: 3726)
Get help: http://help/3726

这似乎很重要，因为这意味着我的 MergeRepl 同步进程正在尝试重新初始化，但由于以下问题而无法进行。

我能够在我的机器上修复它的方法是使用 MSSSMS 删除数据库，然后运行我的程序创建一个数据库并同步它。不幸的是，出于安全原因，远程连接已关闭，我无法访问 SQL Express 安装的所有远程用户的 MSSSMS。

我的想法:

创建一个运行 .sql 脚本的小程序来删除本地机器上的数据库。啦啦； DROP DATABASE MyDB 这只是测试阶段，所以不需要保存数据。

不幸的是，我完全不知道如何让程序做到这一点。

代码:

这是在我的程序加载时运行的代码。如果本地数据库和订阅不存在，它会负责创建它们。然后它会检查它们是否需要同步并在需要时启动 Pull Sync。我包含它是因为我的解决方案可能是对此代码的更改。

我这样调用这段代码 -->

MergeRepl matrixMergeRepl = new MergeRepl(SystemInformation.ComputerName + "\\SQLEXPRESS","WWCSTAGE","MATRIX","MATRIX","MATRIX");
matrixMergeRepl.RunDataSync();

MergeRepl 在下面 -->

public class MergeRepl
{
    // Declare nessesary variables
    private string subscriberName;
    private string publisherName;
    private string publicationName;
    private string subscriptionDbName;
    private string publicationDbName;
    private MergePullSubscription mergeSubscription;
    private MergePublication mergePublication;
    private ServerConnection subscriberConn;
    private ServerConnection publisherConn;
    private Server theLocalSQLServer;
    private ReplicationDatabase localRepDB;

    public MergeRepl(string subscriber, string publisher, string publication, string subscriptionDB, string publicationDB)
    {
        subscriberName = subscriber;
        publisherName = publisher;
        publicationName = publication;
        subscriptionDbName = subscriptionDB;
        publicationDbName = publicationDB;

        //Create connections to the Publisher and Subscriber.
        subscriberConn = new ServerConnection(subscriberName);
        publisherConn = new ServerConnection(publisherName);


        // Define the pull mergeSubscription
        mergeSubscription = new MergePullSubscription
                                {
                                    ConnectionContext = subscriberConn,
                                    DatabaseName = subscriptionDbName,
                                    PublisherName = publisherName,
                                    PublicationDBName = publicationDbName,
                                    PublicationName = publicationName
                                };

        // Ensure that the publication exists and that it supports pull subscriptions.
        mergePublication = new MergePublication
                               {
                                   Name = publicationName,
                                   DatabaseName = publicationDbName,
                                   ConnectionContext = publisherConn
                               };

        // Create the local SQL Server instance
        theLocalSQLServer = new Server(subscriberConn);
        // Create a Replication DB Object to initiate Replication settings on local DB
        localRepDB = new ReplicationDatabase(subscriptionDbName, subscriberConn);

        // Check that the database exists locally
        CreateDatabase(subscriptionDbName);
    }

    /// <exception cref="ApplicationException">There is insufficient metadata to synchronize the subscription.Recreate the subscription with the agent job or supply the required agent properties at run time.</exception>
    public void RunDataSync()
    {
        // Keep program from appearing 'Not Responding'
        ///// Application.DoEvents();

        // Does the needed Databases exist on local SQLExpress Install
        /////CreateDatabase("ContactDB");

        try
        {
            //  Connect to the Subscriber
            subscriberConn.Connect();

            // if the Subscription exists, then start the sync
             if (mergeSubscription.LoadProperties())
             {
                 // Check that we have enough metadata to start the agent
                 if (mergeSubscription.PublisherSecurity != null || mergeSubscription.DistributorSecurity != null)
                 {
                     //  Synchronously start the merge Agent for the mergeSubscription
                     //  lblStatus.Text = "Data Sync Started - Please Be Patient!";
                     mergeSubscription.SynchronizationAgent.Synchronize();
                 }
                 else
                 {
                     throw new ApplicationException("There is insufficient metadata to synchronize the subscription." +
                         "Recreate the subscription with the agent job or supply the required agent properties at run time.");
                 }
             }
             else
             {
                 // do something here if the pull mergeSubscription does not exist
                 // throw new ApplicationException(String.Format("A mergeSubscription to '{0}' does not exist on {1}", publicationName, subscriberName));
                 CreateMergeSubscription();
             }
        }
        catch (Exception ex)
        {
            // Implement appropriaate error handling here
            throw new ApplicationException("The subscription could not be synchronized.  Verify that the subscription has been defined correctly.", ex);
            //CreateMergeSubscription();
        }
        finally
        {
            subscriberConn.Disconnect();
        }
    }

    /// <exception cref="ApplicationException"><c>ApplicationException</c>.</exception>
    public void CreateMergeSubscription()
    {
        // Keep program from appearing 'Not Responding'
        // Application.DoEvents();

        try
        {

            if (mergePublication.LoadProperties())
            {
                if ((mergePublication.Attributes & PublicationAttributes.AllowPull) == 0)
                {
                    mergePublication.Attributes |= PublicationAttributes.AllowPull;
                }

                // Make sure that the agent job for the mergeSubscription is created.
                mergeSubscription.CreateSyncAgentByDefault = true;

                // Create the pull mergeSubscription at the Subscriber.
                mergeSubscription.Create();

                Boolean registered = false;

                // Verify that the mergeSubscription is not already registered.
                foreach (MergeSubscription existing in mergePublication.EnumSubscriptions())
                {
                    if (existing.SubscriberName == subscriberName
                        && existing.SubscriptionDBName == subscriptionDbName
                        && existing.SubscriptionType == SubscriptionOption.Pull)
                    {
                        registered = true;
                    }
                }
                if (!registered)
                {
                    // Register the local mergeSubscription with the Publisher.
                    mergePublication.MakePullSubscriptionWellKnown(
                        subscriberName, subscriptionDbName,
                        SubscriptionSyncType.Automatic,
                        MergeSubscriberType.Local, 0);
                }
            }
            else
            {
                // Do something here if the publication does not exist.
                throw new ApplicationException(String.Format(
                    "The publication '{0}' does not exist on {1}.",
                    publicationName, publisherName));
            }
        }
        catch (Exception ex)
        {
            // Implement the appropriate error handling here.
            throw new ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex);

        }
        finally
        {
            publisherConn.Disconnect();
        }
    }

    /// <summary>
    /// This will make sure the needed DataBase exists locally before allowing any interaction with it.
    /// </summary>
    /// <param name="whichDataBase">The name of the DataBase to check for.</param>
    /// <returns>True if the specified DataBase exists, False if it doesn't.</returns>
     public void CreateDatabase(string whichDataBase)
    {
        Database db = LocalDBConn(whichDataBase, theLocalSQLServer, localRepDB);

        if (!theLocalSQLServer.Databases.Contains(whichDataBase))
        {
            //Application.DoEvents();
            // Create the database on the instance of SQL Server.
            db = new Database(theLocalSQLServer, whichDataBase);
            db.Create();

        }

        localRepDB.Load();
        localRepDB.EnabledMergePublishing = false;
        localRepDB.CommitPropertyChanges();

        if (!mergeSubscription.LoadProperties())
        {
            CreateMergeSubscription();
        }

    }

    private Database LocalDBConn(string databaseName, Server server, ReplicationDatabase replicationDatabase)
    {
        return server.Databases[replicationDatabase.Name];
    }

    /// <summary>
    /// Checks for the existence of the Publication.  If there is one it verifies Allow Pull is set
    /// </summary>
    /// <returns>True if Publication is present. False if not.</returns>
    public bool CheckForPublication()
    {
        // If LoadProperties() returns TRUE then the Publication exists and is reachable
        if (mergePublication.LoadProperties())
            return true;

        if ((mergePublication.Attributes & PublicationAttributes.AllowPull) == 0)
        {
            mergePublication.Attributes |= PublicationAttributes.AllowPull;
        }

        return false;
    } // end CheckForPublication()

    /// <summary>
    /// Checks for the existence of a Subscription.
    /// </summary>
    /// <returns>True if a Subscription is present.  False if not</returns>
    public bool CheckForSubscription()
    {
        // Check for the existence of the Subscription
        return mergeSubscription.IsExistingObject;
    } // end CheckForSubscription()
}

Guerdon(奖励):

这对我来说非常重要，所以即使我是一个火爆的白痴并且有一个 super 简单的解决方案，我也会为正确答案添加赏金。

编辑 2

我创建这个是为了首先尝试删除订阅....它确实这样做了，但仍然在 DROP DB 部分出错，说它正在使用中...

    class Program
{
    static void Main(string[] args)
    {
        DropSubscription();
        DropDB();
    }

    private static void DropSubscription()
    {
        ServerConnection subscriberConn = new ServerConnection(".\\SQLEXPRESS");
        MergePullSubscription mergePullSubscription = new MergePullSubscription("MATRIX","WWCSTAGE","MATRIX","MATRIX",subscriberConn);

        mergePullSubscription.Remove();
    }

    private static void DropDB()
    {
        SqlCommand cmd;
        string sql;

        string dbName = "MATRIX";

        SqlConnection sqlConnection = new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog="+ dbName + ";Integrated Security=True;User Instance=False");
        sqlConnection.Open();
        sql = "DROP DATABASE " + dbName;
        cmd = new SqlCommand(sql,sqlConnection);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }
}

Answer 1

如果您处于测试阶段(我当然不建议在生产系统上进行重大架构更改)，那么只需将订阅和数据库删除到订阅者机器上并重新开始。如果您可以通过 SSMS 连接到它们，那么您可以从那里进行连接；或者，如果您可以物理访问它们，则可以使用 SQLCMD 来完成。

我有使用 SMO 删除订阅和数据库的代码，但它必须在订阅者上运行。如果您认为它有帮助，请告诉我，我会发布它。

编辑添加:好的，代码如下。我现在没有时间清理它，所以它是生的。 RaiseSyncManagerStatus 是一种将状态显示回 UI 的方法，因为这些方法是异步调用的。希望这会有所帮助 - 带来 guerdon。 :-)

    public void DropSubscription()
    {
        try
        {
            RaiseSyncManagerStatus(string.Format("Dropping subscription '{0}'.", _publicationName));
            Server srv = new Server(_subscriberName);
            MergePullSubscription sub = GetSubscription(srv.ConnectionContext);
            // Remove if it exists
            // Cannot remove from publisher because sysadmin or dbo roles are required
            if (sub.LoadProperties() == true)
            {
                sub.Remove();
                RaiseSyncManagerStatus("Subscription dropped.");

                RaiseSyncManagerStatus("Removing subscription registration from the publisher.");
                Server srvPub = new Server(_publisherName);
                MergePublication pub = GetPublication(srvPub.ConnectionContext);
                // Remove the subscription registration
                pub.RemovePullSubscription(srv.Name, _subscriberDbName);
            }
            else
            {
                RaiseSyncManagerStatus("Failed to drop subscription; LoadProperties failed.");
            }
        }
        catch (Exception ex)
        {
            RaiseSyncManagerStatus(ex);
            throw;
        }
    }


    public void DropSubscriberDb()
    {
        try
        {
            RaiseSyncManagerStatus(string.Format("Dropping subscriber database '{0}'.", _subscriberDbName));

            if (SubscriptionValid())
            {
                throw new Exception("Subscription exists; cannot drop local database.");
            }

            Server srv = new Server(_subscriberName);
            Database db = srv.Databases[_subscriberDbName];

            if (db == null)
            {
                RaiseSyncManagerStatus("Subscriber database not found.");
            }
            else
            {
                RaiseSyncManagerStatus(string.Format("Subscriber database state: '{0}'.", db.State));
                srv.KillDatabase(_subscriberDbName);
                RaiseSyncManagerStatus("Subscriber database dropped.");
            }
        }
        catch (Exception ex)
        {
            RaiseSyncManagerStatus(ex);
            throw;
        }
    }