SQL 2012 Peer to Peer Database Replication

Hey guys

I am helping a customer setup his SQL 2012 server with Peer to Peer replication and just for my own sake I was wondering how a website would connect to it.  I know that nearly all websites that are configured thru .Net or something similar can connect to a SQL database using one of two ways... if the SQL database is local they can use Localhost in the name of the server field or they can use an IP address or computer name, but if a database is replicating via Peer to Peer how would the connection be made?  Just something for my own peace of mind so that if they do ask me this, I can say how its supposed to work.  They are already connecting to a database that is Mirrored to another location but the SQL database is on the same server as the website, so if the SQL database is down, so is the website and its an easy matter to use DNS Failover to reroute customers till the primary site is back online.  This setup is different though in that the SQL database is on a different server than the website so if the SQL server goes down, the website might not go down and so now the website has to go to get its information from another SQL server.

Am I making much sense?  Thanks for any help in this matter!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

x-menIT super heroCommented:
if is replica,  you'll have to code the reroute. If you choose the Always-On feature, SQL Server will take care of everything.
jonmenefeeAuthor Commented:
Ah. Ok. Thanks that helps. :-)
Ryan McCauleyData and Analytics ManagerCommented:
Are you using mirroring (the SQL Server feature)? If so, then you have to set the failover partner in the connection string:


If you're using peer-to-peer two-way replication (which is different from mirroring), then your website will have the name of a server it's connecting to. If you need to fail over to the other server for some reason, you have a couple of choices:

1. In case of a failure, manually change your application configuration file to use the other SQL Server. This should take effect immediately, but you have to change it each place it's specified - if you have multiple web apps, that's a problem.
2. Set up a DNS alias that points to your existing SQL server and put that in your application configuration file. It will use that name to connect to the SQL Server - if there's a failure, you can do a DNS redirect to your new server, just as you're descripting in your question. To force the change, you'll have to do a "ipconfig /flushdns", which takes a few moments, and can slow down internet traffic temporarily while it re-resolves DNS.
3. Set up a SQL Client Alias on the application server for your database connection. If there's a failure, you can change the alias to point to the new server and it will take effect immediately. This also creates a single place to make the change, so it takes the best parts of option 1 and 2.

Mirroring (or AlwaysOn) are the preferred methods for handle a database service failure as they're automatic (don't require any manual intervention), but if you can't do either of them for some reason, I'd use a SQL Client Alias as it's easy and is a single setting to change when it's needed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jonmenefeeAuthor Commented:
That is the best information I have seen on the topic.  Thank you very much!!   I will send this to the developer and let him choose but at least I know what to expect.

Yes, he was using mirroring but he wanted to expand to more than just two SQL servers so we saw that Peer to Peer would do it.

So I am guessing from this that there is no automatic failover method for changing where the website gets its data when using a Peer to Peer configuration.  It appears that everything is done manually.
Ryan McCauleyData and Analytics ManagerCommented:
Correct - automatic failover is built into Mirroring and AlwaysOn, but peer-to-peer replication would require some kind of manual intervention to redirect the website connections.

Glad it was helpful - please let me know if you have any additional questions or information I can provide!
jonmenefeeAuthor Commented:
Can Alwayson be used over a network or vpn?
Ryan McCauleyData and Analytics ManagerCommented:
It can be used over a VPN, but keep in mind that AlwaysOn requires Windows Clustering, so there are a number of ports that have to be open between the two (or more) SQL Servers for it to work properly:


For peer-to-peer replication, you just need SQL connectivity between the nodes to enable replication.
jonmenefeeAuthor Commented:
Ah.  Ok, I think we will stick with option 3.  We would have to intervene if there is a SQL failure but at least we know when it happens and can start taking corrective actions.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.