Quiesce requirements for adding new table to publication/subscription?

[Former Member]

If I want to add a new table to an existing subscription, I use the commands:

• create the repdef for the new table
• mark the table for replication using sp_setreptable
• create article for the new table specifying an existing publication
• validate publication
• create subscription with the "for new articles" option (really should be called "alter subscription", whatever).  Also, I'll be using the "without materialization" option here.
• run "check subscription"

My question is, is it ok to run all these commands on a live system where other tables in the publication/subscription have data changes replicating?  Or should I quiesce data changes on the other tables first?

When running sp_setreptable, do I need to stop the rep_agent before running this command?  (it doesn't say to do this in the manuals, but historically, we've been doing it at my site)

Thanks in advance

Ben

[edit: this is for repserver version 15.7.1]

[terry_penna]

Yes you should always quiesce the replication path before making changes to subscriptions.  You would not have to stop the rep agent when marking the table.

When changes are made to subscriptions it will place a marker in the primary database that will replicate through and validate the subscription.  If the system is busy it may take some time before the marker is processed. During this time you might think that there is something wrong with the subscription and try to drop it and re create it causing more problems, or you may have an error and in trying to correct the error loose the marker, causing the subscription to never validate.  If there is an issue that causes you to manually clean up the subscription it can cause you to loose time due to resolving the error and having to resynch.

I am curious as to why you are not using a database repdef and database subscription for replication.  Even if you are not replicating the whole database it is still more convenient to add or remove tables in the replication environment using a file that contains all tables you wish to replicate (or not to replicate).  If you want to replicate a new table in the primary database you would add it to the file and then just run an alter repedef command to re read the file and add the table to replication.

It is not necessary to do anything with the subscription when adding or removing tables from replication.  You also would not need to mark the table on the primary if sp_reptostandby is used to mark the database for replication.

You also have the ability to replicate DDL changes to tables even if they are not going to be replicated at the time you add them.  So for example you add a new table to the primary database the create table DDL is replicated to the target database and the table gets created.  However since the table is not in your file, replication will not occur until you are ready.   When you decide you want to replicate this table all you have to do is edit the file and add this table, next alter the database repdef to reread the file and replication will start for that table.

The same process works for tables you whish to exclude from replication that you were already replicating.  You would go to the file comment out the table you wish not to replicate anymore and alter the repdef it will re read the file and update the table list and stop replicating that table.

Articles and publications is an old replication method that can be cumbersome and can have problems when making changes to them.

[Former Member]

Thanks for your detailed reply.

Re: Yes you should always quiesce the replication path before making changes to subscriptions. 

Good to know, but I couldn't find this anywhere in the documentation when adding a new table to an existing publication/subscription.   If this is a requirement, it really needs to be written down somewhere.

Re: You would not have to stop the rep agent when marking the table (if the "replication path" is quiesced)

So "quiescing the replication path" here includes a quiescing primary (active) database.  Ok.

Again, the sp_setreptable man pages say nothing about stopping the rep_agent.   The doc's do say to make sure the repdef exists first.

A repserver 15.5 tech overview doc I have (dated 5/4/2010) talks about being able do alter repdef on the fly and the repserver will be able to keep track of which data is using which repdef.

Re: When changes are made to subscriptions it will place a marker in the primary database

Really?  A subscription change in a downstream repserver will execute an rs_marker command in primary (er, "active") ASE server that's not even connected to the downstream repserver?   Are you talking about alter repdef?

Re: why you are not using a database repdef and database subscription for replication.

We've thought about this and it's a possibility someday.

One minor problem, for the rep_maint user that the DSI uses to run the actual insert/delete/update statements (non schema change commands), we alias rep_maint to dbo so we don't have to do lots of grants.

But we have to drop the rep_maint alias to db and add a real rep_maint user when running rs_init (it needs a real rep_maint user).

When we drop the rep_maint alias for a primary (active) db, it causes errors for db subscriptions (aka MSA replication).

So I guess we have to figure out how to setup a rep_maint user in a db to have dbo privileges in that db without aliasing rep_maint to dbo.

Ben

[terry_penna]

Gald to help...  Please see my replys below.

Good to know, but I couldn't find this anywhere in the documentation when adding a new table to an existing publication/subscription.   If this is a requirement, it really needs to be written down somewhere.

Re: This depends on the materialization method you are using to materialize data to the replicate database.  If you review the SRS Administration Guide Vol 1 'Managing Subscriptions' section
They give a task list for each materialization method and step 2 and 3 are the important ones:

2. Suspend updates to the data in the primary database by stopping client applications that
generate transactions against the primary data directly or indirectly through Replication
Servers.
3. Quiesce the replication system components involved with replicating data from the
primary Replication Server to the replicate Replication Server.
Use admin quiesce_for_rsi at the primary and replicate Replication Servers and any
intermediate Replication Servers.

So "quiescing the replication path" here includes a quiescing primary (active) database.  Ok.

Re: Yes

Again, the sp_setreptable man pages say nothing about stopping the rep_agent.   The doc's do say to make sure the repdef exists first.

Re: Yes that is true however if you do mark the table first you will just get warnings that no repdef has been defined for the table.

A repserver 15.5 tech overview doc I have (dated 5/4/2010) talks about being able do alter repdef on the fly and the repserver will be able to keep track of which data is using which repdef.

Re: If you have this document I would need to see the document to review it to see what it is talking about.

Really?  A subscription change in a downstream repserver will execute an rs_marker command in primary (er, "active") ASE server that's not even connected to the downstream repserver?   Are you talking about alter repdef?

Re:  Yes the marker helps check the complete path of replication from primary to replicate to validate the subscription.

We've thought about this and it's a possibility someday.

Re: Highly recommend looking into it and testing it.

One minor problem, for the rep_maint user that the DSI uses to run the actual insert/delete/update statements (non schema change commands), we alias rep_maint to dbo so we don't have to do lots of grants.
But we have to drop the rep_maint alias to db and add a real rep_maint user when running rs_init (it needs a real rep_maint user).

Re: How oftent do you run rs_init and for what purpose?  Once the connection is created you should not have to run rs_init again?

When we drop the rep_maint alias for a primary (active) db, it causes errors for db subscriptions (aka MSA replication).

Re: What types of errors are you getting?

So I guess we have to figure out how to setup a rep_maint user in a db to have dbo privileges in that db without aliasing rep_maint to dbo.