none
merge replication without initialization/snapshot being sent

    Question

  • I have two sql server 2005 workgroup editions. They have a large database that is being replicated between them using merge replication.

    When the database is being updated, I would like to do the following:
    1. Turn off the replication
    2. Apply the change script to both the publisher and subscriber database
    3. Turn on the replication

    I do not want to send the snapshot to the subscriber again. Is this possible?
    Thursday, June 25, 2009 1:39 PM

All replies

  • No. You'll need to send it again.

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, June 25, 2009 1:44 PM
    Moderator
  • Is it possible to do this with transactional replication?
    Thursday, June 25, 2009 1:50 PM
  • Let's back up a minute and discuss what you mean by "turn off the replication".

    If you mean stop the agents, yes this will work for both transactional and merge replication as long as you have enabled your publication to replicate ddl commands. This is the default.

    If you mean drop the publications, run the changes, then recreate your publicaiton, you can do it without a reinitialization for transactional as long as you update the replication stored procedures. For merge it will require an reinitialization.

     

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, June 25, 2009 2:21 PM
    Moderator
  • Hi hilary,

    thank you for the help.

    So, if I stop the merge replication agent, I can do things like rename a column (on both the publisher and subscriber). Then I can start the agent again with no issues?

    How do I stop the merge replication agent? I can see how I can stop the job, but cannot find the agent.
    Thursday, June 25, 2009 3:19 PM
  • you don't need to stop the merge agents to make the change. Keep in mind that some changes will break your topology, like replicating fks to non existent tables, et.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, June 25, 2009 3:20 PM
    Moderator
  • Hi Jagdip,

    I don't believe you'll be able to do just a straight name change for a column without re-initializing from a new snapshot.  But, you should be able to do a series of ddl commands to accomplish the same thing.

    For example, for a column rename you can do this on the publisher
    ALTER TABLE [Table1] ADD [Col2] VARCHAR(MAX) NULL

    Then when you synchronize next, the subscriber will also have [Col2], which will be your new name.  Then you can UPDATE [Table1] SET [Col2] = [Col1] to copy all the data from the old column to the new one.  Then you can do ALTER TABLE [Table1] DROP COLUMN [Col1] will remove the old column.

    All of this can be done at the publisher, and it will automatically be pushed out to the subscriber when you synchronize.

    Some other changes are easier to do, like adding and removing constraints, changing triggers, etc...  Any changes like those made at the publisher are automatically replicated at the subscriber.

    HTH

    Kevin
    Kevin
    Thursday, June 25, 2009 6:32 PM
  • The easiest way is to simply replicate the DDL changes, which is the default option.

    If you don't want to do that, then, yes, you can accomplish this without needing to send a new snapshot.

    What you do is the following:
    1. Lock out all applications so that data changes outside of your control are not occuring
    2. Drop the subscriptions
    3. Apply the schema changes at the publisher and all subsscribers - if this involves data changes those are also applied everywhere
    4. Modify the publication definition to incorporate any schema changes
    5. Recreate the subscriptions and specify not to initialize (NOSYNCH)
    6. Send a test transaction through to make sure everything is working properly
    7. Open the database back up to your applicatons.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Friday, June 26, 2009 2:33 AM
    Moderator

  • The reason I want to do this like this is so that I do not have to tell my developers that you cannot do this or that. I would like them to develop the database the way they like, and not worry about restrictions that the replication impose. However, it looks more and more likely that I will have to do this. I am now creating a set of rules for editting existing database that they must follow, and I will have to test any change scripts on a test database that is replicated to ensure no errors occur.

    RE : Kevin:
    Changing the name of a column is okay. However, lets take an example of deleting a stored procedure. The way we work is that, instead of deleting the sp, we rename it (incase we still need it). We delete the sp's that have been sat in the system for more that 2 version releases.
    Merge replication will not let me rename the sp.

    RE : Michael
    I have tried what you suggest, but have not had any success. The issues I have are:
    1. When I drop the subscriptions (and not the publication), I still cannot rename any objects. SQL gives me the error stating it cannot be done as the object is an article for a publication.
    2. If I drop the publication, run the scripts on both sides, change the settings for all articles to say that if the object already exists, then do not drop it and instead leave it as it is, I get the error message similar to this page: http://www.mydatabasesupport.com/forums/ms-sqlserver/415833-merge-rep-how-setup-without-using-snapshot.html
    3. I believe that when merge replication is setup, it creates a rowguid for every row. When I drop the publication, this rowguid is deleted from the tables. This means that, when I setup the replication again, the new rowguid's will not match and the data will be set again anyway.

    I would love to get an answer to this problem. Thank you for your contributions.
    Friday, June 26, 2009 9:43 AM
  • a couple of points here

    1) you can't do a nosync with merge replication, there is a bug associated with the building of the conflict tables
    2) Kevin is correct, you can't rename columns involved in replication.

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, June 26, 2009 10:08 AM
    Moderator
  • That's interesting.  I deploy using nosynch all the time, haven't had any issues.  I just got done deploying a couple hundred new subscribers starting last night, just finished the validation on them about an hour ago.  No errors, no issues, everything is replicating perfectly.

    As far as being able to just do whatever you want in development, deploy it, and not have to worry about replication, that isn't going to happen.  You always have to build this with replication in mind and your change scripts should always be tested against a system that looks like production.  Otherwise, you are just asking from trouble.  Replication works when your applications are designed to work with the replication engine.  If you don't pay attention to the constraints that replicating data is going to place on your environment, then you are going to wind up with a non-functioning environment.

    I don't know where you got that dropping a publication removes the rowguid column.  That doesn't happen.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Friday, June 26, 2009 10:32 AM
    Moderator
  • I don't believe I ever said anything  about dropping replication removing a rowguid. Please correct me if I am wrong.

    Regarding the problem with merge an no-syncs. Create a normal merge publication, do a no sync subscription (making sure all tables are in place on the subscriber).

    It will fail with the below message:

    Source:  Failed Command
    Number:  0
    Message: {call sp_MSsetconflicttable (N'mytable', N'MSmerge_conflict_test_mytable', N'SQL001', N'test', N'test')}
    2009-06-22 19:07:22.328 Percent Complete: 0
    2009-06-22 19:07:22.328 Category:SQLSERVER
    Source:  SQL001
    Number:  102
    Message: Incorrect syntax near 'MyColumnName'.
    2009-06-22 19:07:22.359 Percent Complete: 0


    Please refer to this link for more info:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/f1e8976b-1976-4650-94af-6126741d38c6

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, June 26, 2009 10:39 AM
    Moderator
  • Hi Jagdip,

    Michael's right when he says you can't just tell the developers that they have total control over the databases in a replication environment.  Not unless you want to spend countless hours making sure their database changes required get made at all subscriptions (which depending on the change you want, sometimes the only solution is a new snapshot and re-initialization).

    If you have some specific examples of changes that you would like to make, we can help identify what can be done and how:

    My answer above should work for renaming a column in a database table without re-initializing from a new snapshot (although there are a couple steps involved, but you can't just do a 'rename' without redoing the publication).

    When it comes to the stored procedures, I believe you may want to re-evaluate your process of renaming them when they aren't used any more.  What you can do is ALTER PROCEDURE to change the code in it, and perhaps insert a new line at the very top, like:
    RAISERROR('This procedure has been deprecated',16,-1)
    rather than renaming it so that it will cause an exception if it is used by the front-end application.
    Kevin
    Friday, June 26, 2009 5:03 PM
  • this is the solution for SQL Server 2005 and 2008 http://msdn.microsoft.com/en-us/library/ms152488(SQL.90).aspx

    Regards, Ahmad Elayyan

    Sunday, March 04, 2012 2:57 PM