SQL server 2008 and SQL server 2014/ 2016 replication

Dear all,

We all are in trouble with SQL server 2008 SP3 replication, breaks a lot of time when table change.

can SP4 help on this ?

can SQL server 2014/2016 help on this in anyway.

any technology that can replace replication which serve the same purpose you guys are using?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
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?
lcohanDatabase AnalystCommented:
I believe more details related to this new question can be found at:

http://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.
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ZberteocCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
marrowyungSenior Technical architecture (Data)Author Commented:
ZberteocCommented:
"we already enlarge it and it still has a lot of problem. so in your case what should the range be ?"

I already answered that using an example. You can change the numbers but that is the idea.

"rom 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 point of view doesn't matter here but how it works. The sync can be done at predefined intervals, like 5min, 10min or 1hr... Between the sync the 2 servers generate identity columns and you have to use those ranges and filling percentage in order to make sure the same identity value is not generated on both servers.  Think of an empty table. If I insert a row in sever A the identity will be 1, similarly, if I insert a row on server B the identity will still be 1 but the content could be different. The identity values on the 2 servers have to be different. Server 1 will be 1 and server B will be 10001 and replication will sync these 2 rows to exist on both servers.  Another way of doing it is to generate odd and even values on the 2 servers.

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

A piece of  advise, do not expect to get full tutorial answers on EE. There are plenty of tutorials on the net and I gave you a link to look at in regards to the merge replication setup. You will have to LEARN how to use SQL features and then ask specific questions. You have a tendency to come over and over with more questions after your original one was answered. That is not how EE works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"
 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?
ZberteocCommented:
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.
ZberteocCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
" 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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't you have a similar question opened here in EE?
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
marrowyungSenior Technical architecture (Data)Author Commented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.