Schema changes procedure on Transactional Replication using Backup

Question

Schema changes procedure on Transactional Replication using Backup

plague.y3k

SSC Veteran

Points: 215
More actions
July 2, 2017 at 9:58 pm

#337914

Hi,
I am looking to find out the procedures I must follow to do schema changes to a transactional replication database created using backup.
To explain better, our company database is about 8GB in size. So it has a hard time doing transactional replication using synchronization. So I had to setup a transactional replication using backup. It works fine as it should.
But the problem I have is when I do schema changes. It doesn't seem to affect as it should and then the replication fails. I have to delete all the replication and create a new one after doing a backup/restore in the subscribed server.
It would be great if someone can help me with the steps I can follow to make the schema changes replicated without having to recreate the replication and do the whole backup/restore process.
Kind Regards,
Plague

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Check if the publication has replication of schema changes enabled. In Replication Monitor, right click on the publication, select properties.
Go to the Subscription Options and check the Schema Replication category to make sure Replicate Schema Changes is true.

Sue

plague.y3k SSC Veteran Points: 215 More actions · Answer 2

Sue_H - Monday, July 3, 2017 8:11 AM
Check if the publication has replication of schema changes enabled. In Replication Monitor, right click on the publication, select properties.
Go to the Subscription Options and check the Schema Replication category to make sure Replicate Schema Changes is true.
Sue

It is set to True. But when I make schema change like adding a new column, the replication fails after that. I can't recover it back and only way to do it is by redoing the entire replication process which is a pain with a 8GB database.
Kind Regards,
Plague

Sue_H SSC Guru Points: 90860 More actions · Answer 3

plague.y3k - Monday, July 3, 2017 5:14 PM
It is set to True. But when I make schema change like adding a new column, the replication fails after that. I can't recover it back and only way to do it is by redoing the entire replication process which is a pain with a 8GB database.
Kind Regards,
Plague

Yup, that isn't fun. You'll need to investigate whatever the error is. There is a long list of requirements and such for schema changes on published databases. I'd start by going through that list:
Make Schema Changes on Publication Databases

Sue

plague.y3k SSC Veteran Points: 215 More actions · Answer 4

Sue_H - Monday, July 3, 2017 5:24 PM
plague.y3k - Monday, July 3, 2017 5:14 PM
It is set to True. But when I make schema change like adding a new column, the replication fails after that. I can't recover it back and only way to do it is by redoing the entire replication process which is a pain with a 8GB database.
Kind Regards,
Plague
Yup, that isn't fun. You'll need to investigate whatever the error is. There is a long list of requirements and such for schema changes on published databases. I'd start by going through that list:
Make Schema Changes on Publication Databases
Sue

Ah. Thank you very much for the link. That helps. It highlighted where I made the mistake on

Important

Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.

I did the schema changes using the Management Studio. Guess that's why the Replication fails after the schema change.
Thanks a lot. I will try Alter Table script and see if that makes the change and keep the replication going.
Kind Regards,
Plague

Sue_H SSC Guru Points: 90860 More actions · Answer 5

plague.y3k - Monday, July 3, 2017 5:48 PM
Ah. Thank you very much for the link. That helps. It highlighted where I made the mistake on
Important
Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.
I did the schema changes using the Management Studio. Guess that's why the Replication fails after the schema change.
Thanks a lot. I will try Alter Table script and see if that makes the change and keep the replication going.
Kind Regards,
Plague

Glad that helped. It's good to know about that list - I still get caught by those sometimes even though most of them make sense when you think about them.

Sue

plague.y3k SSC Veteran Points: 215 More actions · Answer 6

Sue_H - Monday, July 3, 2017 6:02 PM
plague.y3k - Monday, July 3, 2017 5:48 PM
Ah. Thank you very much for the link. That helps. It highlighted where I made the mistake on
Important
Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.
I did the schema changes using the Management Studio. Guess that's why the Replication fails after the schema change.
Thanks a lot. I will try Alter Table script and see if that makes the change and keep the replication going.
Kind Regards,
Plague
Glad that helped. It's good to know about that list - I still get caught by those sometimes even though most of them make sense when you think about them.
Sue

To confirm, I just did the sync after doing the schema changes using the script. It seems to sync fine and the schema changes were implemented successfully.
Thanks Sue. Appreciate the help.
Kind Regards,
Plague

Schema changes procedure on Transactional Replication using Backup

Cookies on SQLServerCentral