SQL Server Performance

Replication Question

Discussion in 'Performance Tuning for SQL Server Replication' started by larrykl, Jan 25, 2006.

  1. larrykl New Member

    I have an ASP.NET web app running on a remote server drawing data from a remote SQL Server 2000. I have a local server on which I want to intstall the web app and SQL Server 2000. I want to set up replication so that if the remote sql server goes down for any reason, the data can be drawn from the local sql server by the web app.
    The web app is allowing users to read and updte data so the replication type must be updating the local server as the remote server is in use. When the remote server goes down, I need to switch the web app to use the local server but once the remote server is up again, the new transactions on the local server need to be updated to the remote server.

    Which kind of replication do I need for this and is this doable? Right now, it seems that I'd have to do a one way type of replication and then DTS back to the remote server once it comes back up.

    Thanks in advance...
  2. ghemant Moderator

    Hi,<br />if your application need to update data from your remote sql to your local sql then you need to set up * Transactional Replication * ,<br /><br />Transactional replication<br /><br />In transactional replication, the Subscribers are first synchronized with the Publisher, typically using a snapshot, and then, as the publication data is modified, the transactions are captured and sent to the Subscribers. Transactional integrity is maintained across the Subscribers by having all modifications be made at the Publisher, and then replicated to the Subscribers. Transactional replication is used when data must be replicated as it is modified, you must preserve the transactions, and the Publishers and Subscribers are reliably and/or frequently connected through the network.<br /><br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  3. larrykl New Member

    I currently have the remote (master) server set up as the publisher and the local set up as a pull subscriber. Transactions will only be initiated on the Subscriber if the Publisher is down. What happens when the remote server comes back up? How can the transactions that occurred on the local server be merged or updated back to the remote server? Basically, I'm using the local server as a fail safe so it's only used when the remote is down. Will transactional replication take care of this? Does it preserver the key and identity fields appropriately?

    Thanks....
  4. ghemant Moderator

    Hi,<br />as if your remote server up again :<br />1). DTS <br />2). backup / restore<br />3). set the transaction replication again (this time your local server is a publisher and remote server will be subsciber ,and after the data are updated ,again set it to reverse)<br /><br />Or other option is you can set the * Log shipping *<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  5. larrykl New Member

    So, if I understand you correctly, you're suggesting to set transactional replication from remote server to local server. Can this be set up to have the transactions replicated in real time or near real time?

    Then, in the case of failure of the remote server, I switch to the local server's data. When the remote server comes back up, I then use DTS, back-up/restore, or snapshot replication to restore the data on the remote server from the local server. Is this what you're suggesting?

    I was hoping for a the most automated way to do this but it sounds like something like like I think you're suggesting is safest. There will be some down time associated with this but I guess it is minimal.
  6. ghemant Moderator

    Hi,<br />Yes, and synchionisation should be Continuously to keep it in real time.<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />

Share This Page