August 16, 2015 at 6:05 pm
We are planning to setup multi site data centers. We want to have read-write nodes across all data centers with high availability. I am having hard time to decide on how to organize the current data. We can setup P2P replication but currently we have identity columns on all tables making it complex to setup the new environment.We want to make sure the user is routed to closest data center. Can someone guide me on how to setup this new environment?
August 17, 2015 at 8:52 am
For something like this I would recommend you hire a consulting company.
BrentOzar
PragmaticWorks
SQLSkills
I am sure other posters could offer many other options as well.
August 17, 2015 at 9:00 am
What you're asking there is seriously, seriously complex. If you really want read-write on all nodes, then peer-to-peer replication (or maybe merge replication) is the only option, but to work well you need to define what set of data gets changed on which node. Allowing free for all changes to anything, anywhere is asking for conflicts and chaos (replication has a latency).
Alternately you could look at some form of service bus architecture, where only one node is read-write, but changes can be put onto the service bus from any location, to be processed from the central node.
You probably want to get someone in to help you design it (and possibly help you simplify what you're asking for)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2015 at 4:58 pm
GilaMonster (8/17/2015)
What you're asking there is seriously, seriously complex. If you really want read-write on all nodes, then peer-to-peer replication (or maybe merge replication) is the only option, but to work well you need to define what set of data gets changed on which node. Allowing free for all changes to anything, anywhere is asking for conflicts and chaos (replication has a latency).Alternately you could look at some form of service bus architecture, where only one node is read-write, but changes can be put onto the service bus from any location, to be processed from the central node.
You probably want to get someone in to help you design it (and possibly help you simplify what you're asking for)
Thanks Gila. One of our team member suggested to shred the data based on users and divide it across different data centers. So lets suppose we have 500k users. Divide the users such that 1-100k goes and hit one data center and 100k to 200k goes and hit other data center. Make sure they never get mixed up. If one data center goes down, just update the main table so that all users come and hit one data center. Not sure if this is possible or not.
August 18, 2015 at 3:14 am
I agree with the others; you need to hire a proper consultancy to help you design all that.
August 18, 2015 at 4:21 am
muthyala_51 (8/17/2015)
Not sure if this is possible or not.
Possible, yes. However unless those two sets of users make changes to completely different sets of tables or subsets of the data, probably a bad idea.
SQL does not scale out easily, and implementing a scale-out, without causing yourself endless problems, is a really complex problem. To be honest, if you insist on designing and implementing it yourself, you're far, far better off using something like availability groups with one read-write database, one synchronous replica for fail over and additional read-only replicas. All writes to the central, reads (for reports or other large data access) can be done from the read only replicas.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2015 at 12:40 pm
Thanks Gail. I agree with you it's very complex. We are just in the initial phase of reviewing this new architecture. At the end might settle down with what you recommended of using the AG and use of secondary replicas for read only.
August 18, 2015 at 1:30 pm
If you go that way, keep in mind that you don't connect directly to the async replica, you connect to the AG listener with the Read Only Intent property added to the connection string and the listener directs the connection to an async replica
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2015 at 2:05 pm
got it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply