Manual sql failover machine with replication 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.


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:


Can I do what I have described using the standard sql server methods and technologies which I have mentioned.
Is there maybe a better way to achieve what I am looking for? Am I over-complicating it?
What technology should I use for the purple link (replication / live mirror etc)
After my primary is back online, how would I swap back over to it without loosing the changes in data?
Yes, if you are using Peer2Peer replication between the primary and secondary servers which is an enterprise only feature
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 
Mirroring or AlwaysOn, depending on your SQL Server version - or Peer2Peer replication.
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.