Replicate Passive Node in Active/Passive cluster

  • 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

  • I'm pretty sure you can't replicate from a passive node because it's not accessible until a failover makes it active.

    Greg

  • 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!

  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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" 😉

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

  • 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!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply