May 31, 2013 at 7:21 am
I've recently set up Transactional Replication with Push subscriptions. At the moment the distributor is running on the publisher server. I've been getting vague reports of "it's going slow since we put replication on".
I'm aware that running the distributor on the publisher server has an overhead, but I can't find any specific information on what that overhead is. I'd imagine that it's an amount of I/O as it writes the transactions into the distribution database, plus a bit of CPU? Weirdly, the reports are that the published database is running slowly, but other databases on the server (and on the same disk) are running fine. I'm a little skeptical to honest as I'd have thought extra I/O or CPU load would affect the whole server, rather than one specific database.
If it turns out that it is causing issues, what are my options?
- I've ruled out Pull subscriptions because we're replicating to SQL Server Express, which doesn't have SQL Server Agent to run the jobs.
- It would be a very hard sell to convince the customer to buy another server to run the distributor on.
- Would it be worth adding a new disk to put the distribution database on, but still running the distributor on the publisher?
I realise that this post is lacking specific information, but at this stage I'm just looking at what my options might be. I'm a developer who does a bit of DBA-ing and I'm bit new to replication, so I understand the basics but the minutiae are a bit of a mystery to me! Any help you could provide would be most appreciated.
Thanks in advance
May 31, 2013 at 11:49 am
Run performance monitor for a few days and get some facts. Collect CPU, RAM, latency, page life expectancy and other counters that will give you a clue of what's happening.
You can run some DMVs and find latency on those databases.
Usually, the major overhead on replication is network and writing or moving those transactions to the other one; CPU too but if you have a good server that may not be your problem.
If the disk or LUN where you have your Tlog is not fast enough, it may take a while to write those transactions to the disk and that may create some issues.
But like I said, it is better to run metrics than just accept for true what the end user is telling you now.
June 3, 2013 at 2:54 pm
There is a way to run pull subscription with SQL Server express.
Here it is.
But there may be other reasons for the slowness. try to find out what else is causing the issue. Use Adam Machanic -sp_whoisactive to start with.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply