September 12, 2016 at 4:01 am
Hi all
We've recently had an issue with our data repository (from a 3rd-party application).
The vendor wanted to rename a table/column in a replicated table and tried to use sp_rename to do it.
They've said that sp_rename returned an error because teh table was being replicated.
It was my understanding that, because sp_rename is a standard command, that the change would just be replicated through the publisher, etc.
Can anyone shed any light on this please?
This will happen every 6 months or so and I don't want to have to wipe out replication (or any part of it) just to have to redo it when they've made any changes.
September 12, 2016 at 4:53 am
It depends on whether your publication is set up to replicate schema changes.
USE PublicationDatabase;
-- Execute one of these depending on whether your replication is transactional or merge
SELECT replicate_ddl FROM sysmergepublications;
SELECT replicate_ddl FROM syspublications;
John
September 12, 2016 at 5:52 am
Thanks John
It should have been (I don't remember specifying not to anywhere).
I'll double-check the scripts I've got and see if I can find anything obvious.
::edit::
I've just checked the scripts that were generated when I set up replication and found the following:-
For database 1:-
exec sp_addpublication @publication = N'XXXX_Publication', @description = N'Transactional publication of database ''XXXX'' from Publisher ''XXXXXX''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
For database 2:-
exec sp_addpublication @publication = N'YYYY_Publication', @description = N'Transactional publication of database ''YYYY'' from Publisher ''YYYYYY''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
Both of these lines have:-
@replicate_ddl = 1 (highlighted above)
which to me says that schema changes are allowed.
Can someone please confirm?
September 12, 2016 at 6:59 am
Is this transactional or merge replication? Are you running sp_rename on the publisher or subscriber?
John
September 12, 2016 at 7:56 am
Hi John
It's set up for transaction replication and the command was run on the publisher.
September 12, 2016 at 8:05 am
It sounds as if it should work. We're reaching the edge of my knowledge of this, I'm afraid. Did you run the command I posted earlier just to check that the publication is configured like it is in your script? What is the exact error message you get? Is there anything in the errorlog on either of the servers? Do you get the same error if you use ALT ER TABLE instead of sp_rename?
John
September 12, 2016 at 8:13 am
Hi John
I didn't have a chance as we had to remove replication last week as the 3rd-party couldn't make their changes. The supplier hasn't supplied the error message and I can't see anything obvious in the logs (I'll have another look over the next few days).
The main issue, I think, is that the updates/changes are being done by the supplier and we can't really see what they're doing.
We're also reliant on them giving us the correct error messages (if they give us any at all).
I'll see if they can give us the text of the error they were getting.
September 13, 2016 at 1:20 pm
Is the replication still working otherwise? Are the two servers on the same domain?
----------------------------------------------------
September 13, 2016 at 1:31 pm
I just did a quick refresher on replication and did not see anything about renaming tables. I got the impression you have to drop and recreate the article and then likely the subscription. Has anyone done this before and know for a fact that renaming a replicating table is possible?
----------------------------------------------------
September 14, 2016 at 1:36 am
I had to remove replication completely from the publisher side and I haven't had a chance to put it back.
Apparently, according to the 3rd-party supplier, if their software sees replication is active it just stops.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply