Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

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!!
0
jonmenefee
Asked:
jonmenefee
  • 4
  • 3
1 Solution
 
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.
0
 
jonmenefeeAuthor Commented:
Ah. Ok. Thanks that helps. :-)
0
 
Ryan McCauleyDatabase and Reporting ManagerCommented:
Are you using mirroring (the SQL Server feature)? If so, then you have to set the failover partner in the connection string:

http://www.connectionstrings.com/sqlconnection/database-mirroring/

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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jonmenefeeAuthor Commented:
Ryan
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.
0
 
Ryan McCauleyDatabase and Reporting 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!
0
 
jonmenefeeAuthor Commented:
Can Alwayson be used over a network or vpn?
0
 
Ryan McCauleyDatabase and Reporting 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:

http://blogs.msdn.com/b/sqlcat/archive/2014/02/03/alwayson-availability-groups-listener-named-instances-port-numbers-etc.aspx

For peer-to-peer replication, you just need SQL connectivity between the nodes to enable replication.
0
 
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.

Thanks!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now