DB won't reinitialize to local subscribers after Schema changes

Question

First, I will outline my issue in case someone has an alternate fix.

The Problem:

I have a winform app that uses MergeReplication. This is working great except I needed to make changes to the columns and Primary Key on 5 Tables. I dropped them from the Articles and then made my changes. I then re-added them to the Articles and set the Publication to Reintilialize All.

Unfortunately, this does not work. When I go to run the Subscription Program it tells me that the Subscription is InValid.

EDIT 1

I have a correction/addition here. The actual errors I am getting in the Replication Monitor are such -->

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

This seems important because it means that my MergeRepl sync process is trying to ReInitialize but cannot because of the below issue.

The way I was able to fix it on my machine was to use MSSSMS to DELETE the DB and then run my program which creates a db and syncs it. Unfortunately I do not have MSSSMS access to all the remote user SQL Express installs as for security reasons remote connections are off.

My Idea:

Create a small program that runs a .sql script to DELETE the DB on local machine. A la; DROP DATABASE MyDB This is only the test stage so no data preservation is needed.

Unfortunately I haven't the faintest idea how to have a program do that.

The Code:

This is the code that runs as my program loads. It takes care of creating the local db's and subscription if they aren't already there. It then checks to see if they need to be syncronized and kicks off a Pull Sync if needed. I include it because of the possibility that my solution is a change to this code.

I call this code like this -->

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

MergeRepl is below -->

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()
}

The Guerdon (Reward):

This is extremely important to me so even if I am a flaming idiot and there is a super simple solution I will be adding a bounty to the correct answer.

EDIT 2

I created this to try and remove the Subscription first....which it does but still errors out on the DROP DB portion saying it is in use...

    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

If I have understood your 'original' issue correctly, then you need to create a new Snapshot of the publication before it can be reinitialized. This is so that any structural changes you have made are applied to the subscribers.

See Adding Articles to and Dropping Articles from Existing Publications for more information and follow the specific steps for Merge replication.

Answer 2

If you're in testing phase (and I certainly don't recommend significant schema changes on a production system), then just drop the subscription and database on the subscriber machines and start over. If you can connect to them through SSMS then you can do it from there; or if you have physical access to them you can do it with SQLCMD.

I have code for dropping subscriptions and databases using SMO but it has to be run on the subscriber. Let me know if you think it would be helpful and I'll post it.

Edited to add: OK, the code is below. I don't have time right now to clean it up so it's raw. RaiseSyncManagerStatus is a method to display the status back to the UI because these methods are invoked asynchronously. Hope this helps -- bring on the 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;
        }
    }