I hope this is the correct place to ask this question. We recently experienced one of our virtual machines being offline for 3 days due to issues in the data center hosting it. This quickly brought us to realize that we where not well prepared for failures in our system. Since then we have moved data centers and as a precaution I have applied for a separate smaller virtual machine hosted in a different location for a backup machine.

I have drawn a very rough image of what I want to try and achieve. Diagram

So to explain what is happening here.

He have a primary sql server which websites and services access. via a dyndns name, for ex. sql1.dyndns.org

certain tables and columns are replicated off to a group of subscribers (they are read only) and I do live transaction replication. Other services run on the subscriber machines which access the local db on that subscriber.

Now my issue comes in, say my primary sql server goes offline. All of my services depending on it stop working. hence why I want to add the backup machine. Say primary goes down. I simply go and change the IP address associated with sql1.dyndns.org and then all services should be able to continue running. However I then still need my replication to the subscribers from my secondary machine. As data which is inserted into that database needs to be sent off to the subscribers immediately.

So my questions:

  1. Can I do what I have described using the standard sql server methods and technologies which I have mentioned.
  2. Is there maybe a better way to achieve what I am looking for? Am I over-complicating it?
  3. What technology should I use for the purple link (replication / live mirror etc)
  4. After my primary is back online, how would I swap back over to it without loosing the changes in data?

1 Answer 1

  1. Yes, if you are using Peer2Peer replication between the primary and secondary servers which is an enterprise only feature
  2. I would personally use Mirroring/AlwaysOn for redundancy between primary and secondary sites as this includes ways to failover without changing the DNS records. Either by adding ';Failover Partner=PartnerServerName;' to the connection string or by using Availability groups with multisite failover
  3. Mirroring or AlwaysOn, depending on your SQL Server version - or Peer2Peer replication.
  4. Using Mirroring or Availability groups this will in most cases simply by achieved by bringing the server online and the service will automatically send all the changes over and then you can manually failover.
  • Ok great news, I was worried about recovering them back to the default state. With mirroring. When the principal goes down, the mirror becomes the principal. When the original principal comes back online, do they swap back to the original stage? Commented Jul 28, 2015 at 12:03
  • 1
    They will only swap roles automatically if you are using a witness server. You would have to manually failback when the old principal comes back online
    – Spörri
    Commented Jul 28, 2015 at 12:38
  • That is fine, Will they then sync the databases? Ie, the mirror will update the principle of all the logs which it missed while it was down. Commented Jul 28, 2015 at 12:39
  • And then regards to my further replication, How would I setup the replication on the mirror, to the subscribers? Commented Jul 28, 2015 at 16:41
  • 1
    You would need a shared distributor. It's all explained here: msdn.microsoft.com/en-us/library/ms151799.aspx
    – Spörri
    Commented Jul 29, 2015 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.