SQL server 2008 and SQL server 2014/ 2016 replication

Some details could help. What do you mean it breaks? What kind of replication do you use? From what server to what server and what distance(locations)? Is the connection reliable? What are the settings? What are the errors?

I believe more details related to this new question can be found at:

https://www.experts-exchange.com/questions/28743303/script-out-the-difference-between-TEST-platform-and-production-platform.html

and as far as I'm aware there's nothing out of the box to replace SQL "merge" replication and work without ANY issues after schema changes due to SQL database updates.

Zberteoc,

"Some details could help. What do you mean it breaks? What kind of replication do you use? "

merge  replication, breaks means if our table has identity column it will breaks, if a new table has a foreign key point to diff table in diff  publication, it will failes.

deployment from test to production server in a replication environment is quite challenge because of this, so usually we need to stop the replication, deploy and enable the replication again.

heard that SQL 2008 SP4 can solve some of the problem and what is your option on this ?

if our table has identity column it will breaks

That is because you haven't set up properly the ranges for the identity columns in the replication objects properties. The range means for instance an interval of values like 10000. The way it works is that on publisher it will generate IDs between 1 and 10000 and on subscriber between 10001 and 20000, then back to publisher from 20001 and 30000 and so forth. You will also have to specify a percentage of filling that interval which should be less then 100% usually, let's say 80%. This means that when publisher reaches ID 8000 it will skip to the next interval for it and that is 20001-30000, respectively when subscriber reaches 18000 it will skip to 30001. This is to ensure that there is room enough to avoid conflicts.

if a new table has a foreign key point to diff table in diff  publication, it will failes.

You will have to specify the existence of FKs in the replication objects properties as well and if you do any schema modifications, that is adding new objects or modifying existing one that can happen ONLY ON PUBLISHER!

Check this training video and pay attention to property details:
https://www.youtube.com/watch?v=FvcTVlmBxFg

"You will also have to specify a percentage of filling that interval which should be less then 100% usually, let's say 80%. This means that when publisher reaches ID 8000 it will skip to the next interval for it and that is 20001-30000, respectively when subscriber reaches 18000 it will skip to 30001. This is to ensure that there is room enough to avoid conflicts.
"

we already enlarge it and it still has a lot of problem. so in your case what should the range be ?

"The way it works is that on publisher it will generate IDs between 1 and 10000 and on subscriber between 10001 and 20000, then back to publisher from 20001 and 30000 and so forth"

why the range has to be use in this way, I just know it is for identity column (random sequence number) so that the same column in publisher is not going to conflict with the one in subscriber, what is that at all?

"This is to ensure that there is room enough to avoid conflicts.
"

from my point of view the row just need to replication and should be the same on both publisher and subscriber, I can't see why they HAVE to be diff. we replication the same thing to subscriber, right?

"You will have to specify the existence of FKs in the replication objects properties as well and if you do any schema modifications, that is adding new objects or modifying existing one that can happen ONLY ON PUBLISHER!"

what I heard is, we can't change replication table name if it is replicating already but add a new table on it and setup publication again. so this is not true ?

where to set the replication object properties ? so this works for any newly add table modify the existing table ?

so we can only deploy the change on publisher only but not the source database?

please help on this if you can.

https://www.experts-exchange.com/questions/28745524/SQL-server-2014-2016-upgrade.html?anchorAnswerId=41044229#a41044229

"
 When you add a new table you will have to add it to the publication and then reinitialize the replication."

but we should stop the replication, add table and  then start the replication again, right?

reinitialize the replication will kill everything and the data replicated to the subscriber can be wrong?

I think starting with 2008 or you don't have to stop replication to add an article(table). But initializing means to actually start over the replication, which will drop and recreate and populate the tables on subscriber.

Correction, to add an article(table) you don't need re-initialization but you need a new snapshot to apply on subscriber, which does the same, drops/recreates tables.

I recommend you to do a search on this subject because it's been a while since I've done this. Here:

https://msdn.microsoft.com/en-us/library/ms152493.aspx

" But initializing means to actually start over the replication, which will drop and recreate and populate the tables on subscriber. "

my existing DBA in US say reinitiazation will mess up the data and make data wrong on the subscriber side, is that right?

"I recommend you to do a search on this subject because it's been a while since I've done this. Here:

https://msdn.microsoft.com/en-us/library/ms152493.aspx "

ok, let me take al look.

Don't you have a similar question opened here in EE?

Zberteoc,

"Correction, to add an article(table) you don't need re-initialization but you need a new snapshot to apply on subscriber, which does the same, drops/recreates tables."

so on subscriber, everything will be gone ?

Zberteoc,

that link : https://msdn.microsoft.com/en-us/library/ms152493.aspx , is the one which add/remove article without destroying any existing configuration of replication, right?

as we should assume that the replication is running and by this, no need to rebuild the replication, which takes a long long time? and SQL server just push/pull the change (article we are going to add) to subscriber, right?