SQL server replication

Dear all,

right now face slowness on SQL server replication infrastructure.

what is the common problem of replication is slow and usually how can we solve it?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
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.

arnoldCommented:
Available bandwidth?
Amount of data?
Frequency of replication?

What type of replication is in place?
I.O. storage?
0
marrowyungSenior Technical architecture (Data)Author Commented:
no. SQL server replication, SW side !
0
arnoldCommented:
what type of replication do you have setup, transactional, merge, log shipping...
how frequently


You have to provide details on your setup.

your issue is either limitation on bandwidth transmitting the data
The amount of data flowing.
The ......
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
"your issue is either limitation on bandwidth transmitting the data
The amount of data flowing.
The ......"

I guess MS has suggestion on how to turn the speed of replication, right? what is it ?

any suggestion/requirement of the bandwidth of transmitting the data?

any way to limit the amount of data flow? merge replication with updates only ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I guess MS has suggestion on how to turn the speed of replication, right? what is it ?

 any suggestion/requirement of the bandwidth of transmitting the data?

 any way to limit the amount of data flow? merge replication with updates only ?
Instead of making more questions it will help if you answer first the arnold's questions.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ur ... ! good ! ok let come back later .

so this does very matter and no common experienced replication problem ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You only said that you're facing slowness and that is very generic. Arnold's questions should be answered to narrow the solutions for you.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
let's come back later as I need time to check that back from Friend's server !
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks for telling me.
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

I heard that for partitioned table in SQL 2008, it has problem for replication and can't replicate, we are running SP3, any solution for that?

upgrade to SP4 ?
0
arnoldCommented:
partitioning I think is only available with enterprise version of SQL server.

https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.105%29.aspx
or
https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.100%29.aspx

depending on 2008R2 or 2008.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Arnold,

No. what I mean is we can't implement the table partitioning as we have replication, replication will fail on partitioned table, agree?

or your have other through ?
0
arnoldCommented:
It requires its own setup/complications/etc.
The below discussion might help make things more complicated that they are.
Summary of partitioning/replication/complication there of.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73935d26-7ac7-4bf9-97a1-2a742a686baa/replication-partitioning-and-sql-server-2008?forum=sqlreplication

Before trying to find a solution, you have to define the problem that the solution will be solving.

In a working environment you must thread slowly, no major changes, no more than a single change per attempt. But those MUST ONLY BE DONE AFTER YOU IDENTIFY AND DEFINE the problem/cause.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"In a working environment you must thread slowly, no major changes, no more than a single change per attempt. But those MUST ONLY BE DONE AFTER YOU IDENTIFY AND DEFINE the problem/cause. "

I knew, I am new on replication and that's why I am keep asking.
0
marrowyungSenior Technical architecture (Data)Author Commented:
do you have the table partitioning on replication before ?
0
arnoldCommented:
You are going through training.
The setup has aged, meaning he possibility exists that the larger portion of data in the database at all locations is not being actively accessed.

The scope of what the environment is will deal whether the partitioning/replication setup would/should even be a consideration and the complexity it includes.
0
marrowyungSenior Technical architecture (Data)Author Commented:
if we receive alert in the application log said this:

Replication-Replication Merge Subsystem: agent yyyy-xxxx-Backend_Articles-yyy failed. The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does no

what should we do for example ? only enlarge the range ? any suggestion range you all use which don't create problem ? we can receive a lot number of alert based on this!
0
marrowyungSenior Technical architecture (Data)Author Commented:
we also see this in the application log for another publication on the publisher:

"The schema script 'comp_cd_347.sch' could not be propagated to the subscriber."

what should we do ?
0
arnoldCommented:
You could use automatic range management.
Ref ms https://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx

What is the current column definition?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"What is the current column definition? "

I am sorry, how to check it?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"https://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx"

link above is more on Replication Transact-SQL Programming side, how can I do the same thing from UI console side? doable ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"What is the current column definition? "

there are too much articles on that publication and how can I know which table cause that and then take a look on the column definition ?
0
arnoldCommented:
If this is a known issue, suggesting that the ranfe is manually managed i.e. Extended when needed until the next time.

Increase the range by a significant amount
Check with others to determine whether the automated is a consideration.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Check with others to determine whether the automated is a consideration. "

others?

you mean workmates ?

"If this is a known issue"

I am not sure if replication has this known issue. just want to come here and see any advice can I seek,
0
arnoldCommented:
Yes, co-workers. Yes if the running out of range is a known issue with the existing setup. The duration/transfer of data and timely updates.
0
marrowyungSenior Technical architecture (Data)Author Commented:
already checking with them, it seems they don't want to let me know what their founding is.
0
arnoldCommented:
IMHO, when something is being designed and implemented, you can use your experience expertise to design/implement the best way you know how. However, when you are coming in with an existing setup, you have to, like a doctor, first do no harm.
In this type of an environment you have to have all the information, interactions, inter and intra dependencies.
Make sure to export the publisher, distributor, and subscriber scripts, jobs, etc. nothing is worse that a hardware failure leads to a loss of publisher, distributor, subscribers because the outage duration exceeded the threshold after which those jobs self deleted.

Your current goal should be to learn and understand to be in a position to suggest recommend a possible improvement, not to do something that might or in a missed detail make things worse.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"IMHO, when something is being designed and implemented, you can use your experience expertise to design/implement the best way you know how. However, when you are coming in with an existing setup, you have to, like a doctor, first do no harm"

IMHO ? what is that ?

"Make sure to export the publisher, distributor, and subscriber scripts, jobs, etc. nothing is worse that a hardware failure leads to a loss of publisher, distributor, subscribers because the outage duration exceeded the threshold after which those jobs self deleted."

this is good! how to script that out? should be relationship in the script about which one is distributors, right? we seems do not user distributor,

this is our replication diagram, anything wrong you can see ?

repli

one thing is, replication is slow and we are focusing on network speed as all subscriber now connect to publisher using slow network. we tried to transfer 1GB file from between them and it seems it takes forever.

But the data between AZ and the other one next to publisher, the one run the ETL and reporting, from time to time, do not have the same data, we are building more script to sync back the data so make both side the same.

should we use distributor ?  wait for the network to be fix first ? yeah, that can solve the latency problem but we keep seeing data conflict as I heard that developer/users can update the data on both side.  but when we take a look on the replication monitor, it is bad that SQL server record some conflict that has data insert in months diff, which doesn't make sense and we expected to choose the winner.

"Your current goal should be to learn and understand to be in a position to suggest recommend a possible improvement, not to do something that might or in a missed detail make things worse. "

yes, that's why I come and ask.
0
arnoldCommented:
Right click on the publisher/subscriber I ssms,I think it provides an option to script it out.(create ...)

IMHO - In My Humble Opinion.

Your publisher might be serving both roles.publisher/distributor
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Right click on the publisher/subscriber I ssms,I think it provides an option to script it out.(create ...)"

yeah that one ! so the stupid question is, this action should not kill the replication ,right?

"Your publisher might be serving both roles.publisher/distributor "

you means to me that replication MUST have distributor and combining publisher and distributor in the single box is not good ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

will the design of the tempDB file, i.e., where the files location, how many tempdb files, how many tempdb disk for each file, will have any IMPACT on replication performance and replication issue?

any URL from MS show this ?
0
arnoldCommented:
Right, you do not execute the output create script; you save it to a file in the event your replication fails and the item is auto removed, you'll have the script to recreate it.

I am not sure what you mean by the tempdb.

The amount of memory, resources always affects the pergormance if SQL that usually want ever increasing amounts if memory in a growing DB environment.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I am not sure what you mean by the tempdb."

when I google from internet, someone thinking about the issue on whether change/optimizing the tempdb will help on the replication issue:  

http://www.databaseteam.org/1-ms-sql-server/b2e964dfc85aead7.htm, do you think enhancing Tempdb on the publisher/distribution will help on merge replication ?

"The amount of memory, resources always affects the pergormance if SQL that usually want ever increasing amounts if memory in a growing DB environment."

 I don't think the amount of memory will be as important as tempdb in SQL server, right?
0
arnoldCommented:
Tempdb is available on every SQL server the amount of space available to it is a separate matter.

The create publisher/distributor/subscribers might point to which resources are used/needed.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Tempdb is available on every SQL server the amount of space available to it is a separate matter."

nono . what I mean is, can the optimization of tempdb configuration (e.g. disk speed) can improve the speed of replication?

"The create publisher/distributor/subscribers might point to which resources are used/needed."
what is that mean ? which need to take a look on the creation script?
0
marrowyungSenior Technical architecture (Data)Author Commented:
please see attached replication script, any potential problem you can see from that.?
upload-to-EE.sql
0
arnoldCommented:
The speed of disks in which the SQL databases, transaction log files are always help in pergormance. Enough ram ....

I do not currently have a way to look at the create script you uploaded at the moment.

If you can not currently determine the amount of data being transferred. Trying to determine transfer plus processing through the SQL logs records using the start time when the publisher started to the tine the subscriber completes its task.
Averaging those times might allow you to approximate the range of data being transferred.......
0
marrowyungSenior Technical architecture (Data)Author Commented:
"If you can not currently determine the amount of data being transferred"

from replication monitor, it can only show how many row/sec, right?

"determine transfer plus processing through the SQL logs records using the start time when the publisher started to the tine the subscriber completes its task."

sorry, from SQL server log or SQL agent log? I am not sure how to get this information.
0
arnoldCommented:
The SQL job log (SQL agent logs)
0
marrowyungSenior Technical architecture (Data)Author Commented:
wait, the SQL agent log don't show this . what keyword should I search for ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
from replication monitor, I can only see row /sec. a rows should be very small in size, right?
0
arnoldCommented:
A row, is the sum of the size of each column's data type as the row data size. Using the max column content size will give you the upper limit of the ........

Ref data types
https://technet.microsoft.com/en-us/library/ms172424(v=sql.110).aspx
0
marrowyungSenior Technical architecture (Data)Author Commented:
so I have to sum up all datatype size of tables involve in the articles, that's it ?
0
arnoldCommented:
Summing the data types in a row is a way to determine the Max amount per row.

You would do that on the columns being reported out in the publisher/subscriber model.
A row is made up of the data in the publisher, not all the columns in all the tables that are used to assemble the data.
0
marrowyungSenior Technical architecture (Data)Author Commented:
starting from SQL 2012 we have one more column in the replication monitor which called event tracker to see what make the replication pending here and wait for something?

any similar thing for SQL server 2008 to check what is waiting/pending for replication to beginning ? it seems that an uncommit transaction can hold the data replication ?
0
arnoldCommented:
I am not aware of such a mechanism in sql 2008.
0
marrowyungSenior Technical architecture (Data)Author Commented:
then is there any way to know what make it hold there and not replicating in SQL 2008 ?

what will you do to check ?
0
arnoldCommented:
I am uncertain whether an open transaction will prevent replication in 2005,2008, or the 2012 and newer includes information on the but for X additional data would be replicated.'
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, I see

one thing:

"You could use automatic range management.
 Ref ms https://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx

 What is the current column definition? "

I found out that not all article has this and it means that someone tried to use this:

auto
is it possible to change/transform the existing article to use automatic range management without impacting the existing merge replication?
0
arnoldCommented:
The article covers that it could, though I would suggest you check first as there might be some reason this was setup for manual, perhaps to keep track of how fast it is being consumed .....
0
marrowyungSenior Technical architecture (Data)Author Commented:
ops, recheck and see this:

To change automatic identity range management settings for an existing article in a merge publication


1.At the Publisher on the publication database, execute sp_helpmergearticle and note the value of identity_support in the result set. If this value is 0, automatic identity range management is not enabled.


2.If the value of identity_support in the result set is 1, change the settings as follows:

◦To change the assigned identity ranges, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identity_range or pub_identity_range for @property and the new range value for @value.


◦To change the threshold at which new ranges are assigned, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of threshold for @property and the new threshold value for @value. For more information on when new identity ranges are assigned, see Assigning Identity Ranges in the topic Replicating Identity Columns.


◦To disable automatic identity range management, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identityrangemanagementoption for @property and either manual or none for @value.


so this help us to convert without impacting the existing merge replication ?

from the screenshot above, also can't see why that article is auto identity range managed but still has range there, any idea ?
0
arnoldCommented:
As your prior error pointed out, you can manually extend the range.
The range is either automatically managed or it is manually adjusted.
The automatic if not mistaken can be set to not expand beyond a certain range....
0
marrowyungSenior Technical architecture (Data)Author Commented:
"As your prior error pointed out, you can manually extend the range. "

during replication is on going ? once click OK the replication is not going to die?

article-properties.jpg
and we only need to adjust it on publisher but not subscriber ?

"The automatic if not mistaken can be set to not expand beyond a certain range.... "

executes me, what is that means ?

I am not sure the picture above don't have the range for me to adjust at all, any idea? where is the option goes ?
0
arnoldCommented:
The link previously posted included the command/sp that has to be run to run to increase the range.

Pressing ok o the properties display should be fine, what changes if any did you make to the settings reflected there?.

I think the same way you can set log files to expand until a max size, you can set the range max while it will extend auto.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Pressing ok o the properties display should be fine, what changes if any did you make to the settings reflected there?.
"
1) change to automate range management if it is not yet.
2) increase the range AGAIN when we see error out conflict on identity range. I saw that 2 times in 2 months here.

"I think the same way you can set log files to expand until a max size, you can set the range max while it will extend auto. "
I don't understand this, please clarify.
0
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.