| Author |
Topic |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2008-08-19 : 13:12:45
|
| What happens in with replication if the schema of a table changes at the subscriber? Does it not replicate the column additions? What about if it changes at the publisher? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-19 : 13:19:13
|
quote:
Originally posted by Ken Blum
What happens in with replication if the schema of a table changes at the subscriber? Does it not replicate the column additions? What about if it changes at the publisher?
Depends on what type of replication. It will replicate if you do Merge replication or Transactional replication with updatable subs. |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-08-19 : 14:21:51
|
| It's merge replication with updateable subs. What happens if the schema is changed at the subscriber, like adding a column to a table? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-19 : 14:47:16
|
| Why are you changing schema on subscribers? Explain you whole environment how merge replication is setup . Schema change like adding column is done by default.Only issue is when change datatype length like from char(6) to char(10). |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-08-20 : 07:35:46
|
| I'm not changing schema on subscrbers now, but it may happen in the future and was wondering what would happen if it does change. Just trying to be proactive/protective.So adding a column at the subscriber won't break replication? I assume dropping a column or changing the data type/length at the subscriber would break it. Would replication fail for the whole database or just the changed table?TIA! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-20 : 09:09:34
|
quote:
Originally posted by Ken Blum
I'm not changing schema on subscrbers now, but it may happen in the future and was wondering what would happen if it does change. Just trying to be proactive/protective.So adding a column at the subscriber won't break replication? I assume dropping a column or changing the data type/length at the subscriber would break it. Would replication fail for the whole database or just the changed table?TIA!
Whole database won't fail. Only the table will be unavailable and you have to reinitialize subscriber after doing that.Better change schema in Central Publisher. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-08-20 : 09:20:52
|
| That's the plan.Thanks. |
|
|
|
dkimbrell
Starting Member
9 Posts |
Posted - 2010-08-20 : 08:16:37
|
| I don't think SQL Server will even let you make a change at the subscriber level. I've tried adding columns to tables on subscriber machines in SMSS and while I was allowed to MAKE the change, I got an error when I tried to save the change that "Changes can only be made at the publisher" and it discarded the change.Also, I've just been warned in one of my own threads that schema changes don't always replicate well, and you should only make them when you know you can redistribute snapshots. This is horrible news to me, and I hope to hear otherwise at some point. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2010-08-23 : 11:03:45
|
| Schema changes can only be performed on the published database, and do replicate just fine, as long as the objects are already in the articles of publication. The key to remember is that if you want to add a new object like a table to a database and include that in replication, you will have to change your articles of publication and then distribute a new snapshot. The size of a snapshot can cause web synchronization to fail, so we usually have to manually transfer the snapshot. It's a complicated process. Not intended to be used with multiple subscriptions. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-23 : 13:54:13
|
quote:
Originally posted by Ken Blum
Schema changes can only be performed on the published database, and do replicate just fine, as long as the objects are already in the articles of publication. The key to remember is that if you want to add a new object like a table to a database and include that in replication, you will have to change your articles of publication and then distribute a new snapshot. The size of a snapshot can cause web synchronization to fail, so we usually have to manually transfer the snapshot. It's a complicated process. Not intended to be used with multiple subscriptions.
When you add a new table to the database that you want to include in your publication or even make changes to existing tables that are being published, you dont need to reinitialize the entire snapshot. You just need to snapshot only the new tables or the changed tables.Check out sp_Addsubscription stored procedure in books online for various parameters. For changing tables on subscriber end, I dont think you can make DDL changes but you an make DML changes/modify indexes etc. Just be aware of the implications your changes may have on replication.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2010-08-24 : 08:21:26
|
| How do you "snapshot only the new tables or the changed tables"? I have never seen any capability to limit a snapshot to specific tables/objects, without removing them from the articles of publication.I use merge publications with web synchronization (SQL 2005). Currently whenever I make a change to the publication I have to create a new snapshot, which includes ALL tables defined in the articles and ALL data, which can be quite large (200MB+). I manually transfer the snapshot (zipped) to the subscriber machine to ensure it is transferred properly and then synchronize the subscriber database from the local snapshot. Web Synchronization of a large snapshot is unreliable, and rarely works. Furthermore, in my testing it would download the entire snapshot to the subscriber regardless of whether the object is needed or not, and then perform synchronization. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-25 : 12:16:40
|
| yes you can drop/add specific tables to a publication. You need to use sp_Dropsubscription first for the article followed by sp_Droparticle. Similarly when you are adding, use sp_addarticle and sp_Addsubscription.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2010-08-25 : 12:42:31
|
| If you drop a table from the Articles of Publication then it is no longer a part of the replication synchronization, and of course would not be a part of the snapshot. I thought you stated that you could create a snapshot of only certain articles. Evidently that is not the case? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-25 : 21:26:58
|
| not sure where you are getting confused.. if you have a publication of, lets say 100 tables, and you need to add a new table to this publication you can just add this table to the publication without having to reinitialize entire snapshot of other 100 tables. Similarly, if you need to drop a table you can do so without reinitializing entire snapshot.. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2010-08-26 : 08:10:22
|
| That's where I am confused. Whenever I create a snapshot it always creates the snapshot for the entire database (101 tables in your example). How do you tell it to only create a snapshot for the one table that was added? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-26 : 12:02:18
|
| http://support.microsoft.com/kb/830210The key is the parameter @force_Invalidate_snapshot.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Schema changes with replication