January 30, 2009 at 8:28 am
hi. we have a 2 node cluster running Active/Passive and would lke to replicate (snapshot). We have set up replication on the active node, but it for some reason slows down the database to a crawl (we have lots of room and memory). Is it possible to set up replication on the passive node instead? If so, what are the drawbacks?
Thank you!
E
January 30, 2009 at 10:46 am
I'm pretty sure you can't replicate from a passive node because it's not accessible until a failover makes it active.
Greg
January 30, 2009 at 1:11 pm
thank you for your reply..i suspected, but was wishing. Is there anyway (i am not a dba, nor do i know much about replication), to find out why the snapshot replication is slowing down the database so much?
thanks!
January 31, 2009 at 4:57 am
As Greg said, in an A-P cluster, the SQL Server service does not run on the P node unless the cluster group is failed over.
The only way you could replicate to the other node would be to install another instance of SQL Server on the P node to make it an A-A cluster.
Why do you want to replicate your database to the other node though, is this for reporting?
January 31, 2009 at 10:12 am
myst.black (1/30/2009)
Is it possible to set up replication on the passive node instead? If so, what are the drawbacks?
Bear in mind that, with clustering there's only a single copy of the database files. They're shared between the two servers and whichever one is active owns the files. So, not only is the passive node not running the SQL service, it doesn't have access to the data files at all.
As for your snapshot replication, how often are you generating a snapshot? Generating snapshots puts locks on the tables (shared) and can have impact on IO and possibly network, as the snapshot agent is copying the entire of the selected tables to a file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2009 at 3:54 pm
as Gail pointed out remember that to make the passive node active would require a new set of disk resources for the 2nd active instance. Is it worth the trouble, what do you hope to gain?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2009 at 5:14 am
Perry Whittle (1/31/2009)
as Gail pointed out remember that to make the passive node active would require a new set of disk resources for the 2nd active instance.
And even if you do that, the 2nd active instance won't have the same database as the first. A database can not be shared between two instances.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 7:01 am
I'd like to just add that only changed pages get read from the Snapshot. Non-Changed pages still get read from the original database.
Also, if you make the cluster active-active with a new instance (even if it's just for a Snapshot DB) you will have to pay for SQL Server licensing on the new active node.
Tim White
February 2, 2009 at 7:12 am
2 Tim 3:16 (2/2/2009)
I'd like to just add that only changed pages get read from the Snapshot. Non-Changed pages still get read from the original database.
Unless I'm badly misreading, he's talking about snapshot replication, not database snapshots.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 7:30 am
It might be a good idea to clear up the difference here and I will defer to Gail's expertise.
However, this is what I was referring to:
http://msdn.microsoft.com/en-us/library/ms187054.aspx
"If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database".
Where Snapshot Replication (as I understand it) is the original (real) load of the replicated data to begin a replication process (transactional, merge, etc..)
Tim White
February 2, 2009 at 7:49 am
I might be mis-interpreting this, but it sounds like the purpose of replication here is for disaster recovery. Is this correct?
If it is meant to be used for DR, why not try log shipping instead?
Regards, Jim C
February 2, 2009 at 7:52 am
thank you everyone for your replies!! we are using the snapshot replication that is then distributed (is that the correct word?) to another database on another server. We have a custom application being developed. We were assured that snapshot replication does not cause any performance issues, so we installed the replication on production to our detriment. It has since been disabled. We were planning on taking snapshots every 3 hours, but were getting thousands of timeout errors; therefore, disabled.
So that is what we need, a "snapshot" (i'm using snapshot for lack of a better word) that can then be taken in by the database on the other server on a fairly regular basis.. I am open to any suggestions, and or ideas.
thank you all!
February 2, 2009 at 7:53 am
2 Tim 3:16 (2/2/2009)
"If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database".
Yup. That's database snapshots, a point-in-time 'copy' of a database on the same instance as the source database.
Where Snapshot Replication (as I understand it) is the original (real) load of the replicated data to begin a replication process (transactional, merge, etc..)
It's the reading of the publisher the creation of the bcp files and the load of that into the subscriber database. While it's often the beginning of transactional or merge replication, it can be done by itself as snapshot replication.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 8:02 am
myst.black (2/2/2009)
So that is what we need, a "snapshot" (i'm using snapshot for lack of a better word) that can then be taken in by the database on the other server on a fairly regular basis.. I am open to any suggestions, and or ideas.
Why? What is it that you're trying to do here? Disaster recovery? Separate reporting database? Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply