April 10, 2010 at 10:34 pm
I have a client that has a specific request for a solution.
They have a main server 'A' that holds all data and then multiple other servers around the U.S. that hold a local exact copy of the server 'A' tables, data, etc. at call center type environments. This is being done for failover sake but is currently a manual process if a failure does occur. They need very high availability as the database is used for a call center application as well as a 24x7 live transactional website.
So the question is: what is the best method? Is it transactional replication, merge replication, mirroring, clustering, ??? What is the best practice for this specific scenario that would keep each individual call center live and with the most up-to-date data if their connection to the main server went down? And would this solution be able to automatically failover and then self-correct once the problem was remedied?
Thank you!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 12, 2010 at 4:10 am
In replication we dont have a automatic failover.we have automatic failover in mirroring and clustering.
Clustering nedds more inputs and budjet will be more.so you can choose mirroring.
April 12, 2010 at 6:53 am
So I can use mirroring to multiple other servers at once? What about recovery, how would that be accomplished?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 12, 2010 at 10:37 am
how is the call center setup? Do they work from one location (and copy databases to different locations) OR do they work from each location where database gets copied?
April 12, 2010 at 10:42 am
There are 6 call centers and each of them has an identical server that houses their own data that is replicated to the main server each night currently. The key for them is that each center has its own server in case they get disconnected from the main server, they will have the ability to keep working.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 12, 2010 at 10:50 am
Jacob Golden (4/12/2010)
There are 6 call centers and each of them has an identical server that houses their own data that is replicated to the main server each night currently. The key for them is that each center has its own server in case they get disconnected from the main server, they will have the ability to keep working.
The setup looks good coz in the case any of the servers goes down, it'd affect only that particular site.
Do you intend to go for HA for all six centers or just the main server? coz all six sites are failure points.
If its just the main server, HA is provided by clustering wherein ur databases will reside on a shared SAN storage with two or more physical servers, one of which will own the shared storage at a given point. in case of failure, the other node(server) will take over. This requires more $$ since SAN is relatively costly.
However there is a catch: If ur storage goes down, there is not much that you can do. U'll have to rely on your native backup/restore method.
If you go for mirroring u'll have ur exact copies of databases on 2 locations and the database will be syncronized to the latest transaction(depends on how u configure it). When ur primary server fails, you can point ur application to the mirrored server and things can work normally.
April 13, 2010 at 8:11 am
Each of the six call centers and the main server all have mirroring enabled locally. I think it comes down to replication now. Would it be best to setup transactional or merge? In this scenario, which will keep the data most up to date between all servers and have the fastest speed and recovery time? And is there anything that I should be aware of?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 13, 2010 at 9:56 am
In your case, Merge would be better I think.
-Roy
April 15, 2010 at 1:52 am
As all location's data moved/ transferd during your night time , the Merge replication will be the most suitable for you.
April 15, 2010 at 2:26 pm
Actually, these are 24 hour uptime as there is a website that allows clients to create and update records at any time during the day. I was reading up on Peer-to-Peer and wonder if that might be the best idea?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 15, 2010 at 3:43 pm
I'm currently maintaining a setup similar to yours and I use push merge replication. I have replication run every 5 minutes throughout the day. If a site's line goes down, they are still able to work and when the line comes back up replication syncs everything back up again. This setup has been running for several years before I came along and there hasn't been any major issues.
HTH,
Ben
April 16, 2010 at 2:19 pm
Interesting. Do you find that the sites don't mind it taking 5 minutes to sync up the data once entered? So, if a rep at one site enters a record and then the customer calls in 2 minutes later and gets a different site, that site will not have access to that record for another 3 minutes? Or maybe more specifically, if a customer enters a record on the website and then calls into any center? Or is it just the case that your centers all work from the main database unless there is a failure and then they would fail over to their local copy which may be missing transactions that occured within the last 5 minutes if it was close to the next scheduled sync?
I appreciate your example.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 19, 2010 at 5:58 am
Take a look at http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx
This is a customer study of using P2P replication to provide both high availability and DR. It might cover all you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 19, 2010 at 8:01 am
My company doesn't really deal with customers calling to a different state. The customers call either the outside sales rep directly or the main branch for their area. There are cases where the 5 minute replication would affect the process. In these instances, we code the programs to open a connection to the server where the work will end up at and process the work on that server. I want to point out that since I have replication set to 5 minutes, it can take 10 minutes for the data to get from one subscriber to another. 5 from the subscriber to the publisher and another 5 from the publisher to the other subscriber. Setting replication to 5 minutes may not work for you, but you can set it to a smaller interval to suit your needs.
Ben
April 19, 2010 at 1:02 pm
Thank you Ed, this was a very helpful Case Study.
I think that I have decided to recommend upgrading to 2008 Enterprise and using P2P replication. Do you feel there are benefits to also using mirroring at each server or is that overkill? I think it might be a good choice if we were to partition the 'writes' by call center. Thoughts?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply