January 9, 2009 at 3:03 pm
I want to set up transactional replication on a database. My concerns are, if for some reason the subscribing database has replication issues could it cause the publisher database to slow down or lock up?
We are planning on replicating to another database located in the same instance of sql server to use for ad hoc reporting purposes. I suggested snapshot replication but the users want as close to current data as possible so transaction replication is the option.
I have already set it up and tested it but was wondering if there were any issues to look out for.
January 9, 2009 at 3:11 pm
However, the greatest caution here is that you are basically doubling your activity for that instance for anything that is being replicated. If you are talking about infrequently used objects that may be fine but I wouldn't typically recommend that architecture.
If the subscription database goes offline it should not affect the publishing database. Over time you will have built up repl commands at the distributor (hopefully another instance?).
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2009 at 7:29 am
if you have trouble with your subscriber or some other replication issues it may cause the transaction log on the publisher to grow until those commands are marked as replicated.
solution is to run sp_repldone or reinitialize that publication and run another snapshot
January 20, 2009 at 10:39 am
DavidB (1/9/2009)
However, the greatest caution here is that you are basically doubling your activity for that instance for anything that is being replicated. If you are talking about infrequently used objects that may be fine but I wouldn't typically recommend that architecture.
David,
So I'm curious, what kind of architecture would you recommend for supplying real time information?
The other question I had was, should the initial snapshot be the only one, or should it be renewed on a regular basis?
We are looking at transactional replication as a report solution as well. From what I've read and discussed so far, I should be using 3 servers (publisher, distributor, subscriber).
Alan
January 20, 2009 at 10:51 am
We are using two subscribers with real time publication. Snapshot should be done only once. There is no need to re snapshot it again and again. We use Push publication. Yes, three servers are better when you are setting up replication.
Setting up replication will not affect your Publisher much other than when you are setting it up intially. You could get Dead locks if your DB is really busy.
-Roy
January 20, 2009 at 10:56 am
So setting up the initial snapshot should be done during slow/down time?
After that the publishing database should be OK? Or will it continue to take a performance hit from the replication?
Alan
P.S. Hey Roy, take a look at my other post...
January 20, 2009 at 11:02 am
there will be a slight degrdation but it is very small. Publisher has to notify the Distributor that there is a data change. So couple of small steps has to be taken. But the replication engine is pretty well tuned that you wont noptice it much.
Please keep in mind that the transaction Log will grow when you have the Database set in FULL mode. So take regular transaction Log back ups. If you have a warm stand by with transaction log shipping you dont need to worry about it.
Yes, I will look at the other thread and answer it there
-Roy
January 20, 2009 at 11:03 am
Alan Vogan (1/20/2009)
DavidB (1/9/2009)
However, the greatest caution here is that you are basically doubling your activity for that instance for anything that is being replicated. If you are talking about infrequently used objects that may be fine but I wouldn't typically recommend that architecture.David,
So I'm curious, what kind of architecture would you recommend for supplying real time information?
The other question I had was, should the initial snapshot be the only one, or should it be renewed on a regular basis?
We are looking at transactional replication as a report solution as well. From what I've read and discussed so far, I should be using 3 servers (publisher, distributor, subscriber).
Alan
Alan - What you are suggesting as your solution is solid. The reason I made the comment to the original poster was strictly because he was going to host both the publisher and the subscriber on the same instance. What you are suggesting is exactly what we have in production now and it works great.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2009 at 11:14 am
Roy Ernest (1/20/2009)
...Please keep in mind that the transaction Log will grow when you have the Database set in FULL mode. So take regular transaction Log back ups. If you have a warm stand by with transaction log shipping you dont need to worry about it.
Log backups every hour, with differentials 4 times a day. I don't have a warm standby with log shipping. Should I? Hmmm, that's something to ponder on another day, another project...
Thanks Roy,
Alan
p.s. Thanks to David too...
January 20, 2009 at 11:49 am
Whoops! Back again. Good thing we're not brain surgeons :w00t:
Our administrator wants to know why I can't just do this on 2 servers, combining the publisher and distributor. I mentioned that 3 was the optimal way to set it up but he wants to know why. Anyone?
Alan...
p.s. I won't answer for about an hour, I have to put on a different hat and go fix a printer...:cool:
January 20, 2009 at 12:22 pm
You can. You can put use 1, 2 or 3 servers. You can put publisher, distributor and subscriber on 1 server, or you can put publisher, distributor on one and subscriber on another. You can put publisher on one, and distributor, and subscriber on another, you can even put the publiser and subscriber on one, and the distributer on another. But for optimum performance, with least contention use a seperate server for each.
Its one of those "you get what you pay for" type things.
January 20, 2009 at 12:25 pm
You can use the same server. You just have to understand that this one instance will now share the load. I will say that I have quite a bit of replicated articles with both merge and transaction replication and my distributors are not hit that hard.
As always though I would recommend testing with a similar load to what you are going to have in production. The last thing you want to do is slow down your production server so that you can offload the performance impact due to reporting. Kind of lose your benefit if you know what I mean.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2009 at 12:38 pm
OK, thanks for the additional input. I'll go ahead and test it both ways. I didn't notice a big difference when I did it the first time, so this time I'll document it... should have known better in the first place :ermm:
Thanks again,
Alan
January 20, 2009 at 1:33 pm
Sorry I was out for Lunch. The issue is that when you have the distributor in your main production box, You are going to get more IOs. This would make the queries run slower, Transaction slower. When queries take more time, chancs of getting Dead lock also rises.
Distributor clean up agent can use a bit of processing power. At that time , the prodcution DB is going to go slow as well. It all depends on how much transaction / how busy your production server is.
-Roy
January 23, 2009 at 3:35 am
Dear Alan,
Inform your Admin that We need to implement it so that the load will be less on the Server.
If the above does not work,
If I were I would reply, It's a Client Requirement and we need to implement it the way it best serves our Clients.
Then hopefully, things might change for the better.
Good Luck! 🙂
Best Regards
Anil Mahadev
Senior SQL Server DBA 😎
MISPL,
Bangalore
INDIA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply