December 9, 2015 at 12:11 pm
We are looking into the possibility of using AlwaysOn Availability Groups in SQL 2014, if it works like we are hoping. What we would like to do is the following:
SQL Server A: Has Primary Availability Group A with Database 1, Database 2, and Database 3. Has Secondary Availability Group B with Database 4, Database 5, and Database 6.
SQL Server B. Has Primary Availability Group B with Database 4, Database 5, and Database 6. Has Secondary Availability Group A with Database 1, Database2, and Database 3.
That way we could have some applications always primarily running off of SQL A and some always running off of SQL B. Our thinking is this will spread the load of the database to different servers.
Questions would be"
1. Can 1 server work as a Primary for one AG and as a Secondary for another AG?
2. Will background transaction trying to keep everything in sync nullify what we are trying to do?
3. Is there a better way to do this?
I did not see any post out there actually addressing if this was possible, so sorry if this has been answered before.
Cheers
Dave
December 9, 2015 at 7:01 pm
SQL Server A: Has Primary Availability Group A with Database 1, Database 2, and Database 3. Has Secondary Availability Group B with Database 4, Database 5, and Database 6.
SQL Server B. Has Primary Availability Group B with Database 4, Database 5, and Database 6. Has Secondary Availability Group A with Database 1, Database2, and Database 3.
1) Database cannot exist in more than one availability group.
SQL SERVER A (Primary)
AG1
-Database1
-Database2
-Database3
AG2
-Database4
-Database5
-Database6
SQL SERVER B (Secondary Replica)
AG1
-Database1
-Database2
-Database3
AG2
-Database4
-Database5
-Database6
The documentation for Availability Modes pretty much explains how the replicas work based on each mode. You can have a synchronous-commit replica that is readable so your application can read against SERVER B. Your limitation in what you want to do is only the primary replica (SERVER A) will be the only server where modification can be done.
What you described is database-sharding where you are spreading your reads/writes across multiple database servers. This can be very difficult to implement and support from what I understand.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 10, 2015 at 4:10 am
David Graham (12/9/2015)
SQL Server A: Has Primary Availability Group A with Database 1, Database 2, and Database 3. Has Secondary Availability Group B with Database 4, Database 5, and Database 6.
SQL Server B. Has Primary Availability Group B with Database 4, Database 5, and Database 6. Has Secondary Availability Group A with Database 1, Database2, and Database 3.
Yes, this is fine, although bear in mind that either AG may fail over to the other server occasionally. It may not be all that helpful in spreading the load but I imagine it's quite a normal setup.
As already said, each database can only be in 1 AG but that's what you're planning anyway.
December 10, 2015 at 10:07 am
Great information. Thanks much to both of you for answering. Now to move forward with implementing and see how it goes.
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply