2

I have 2 Servers under SQL Server Cluster (Server 1, Server 2)

2 Databases configured with Availability Group (Database 1, Database 2)

Server 1:
Database 1 PRIMARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1: SECONDARY REPLICA
Database 2: SECONDARY REPLICA

If Database 1 is starting to consume a lot of resources and I want to add a 3rd server (Server 3) to lower the stress on Server 1 is this given example possible?

Server 1:
Database 1 SECONDARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1 SECONDARY REPLICA
Database 2 SECONCARY REPLICA

Server 3:
Database 1 PRIMARY REPLICA
Database 2 SECONDARY REPLICA

Is this possible to configure with minimum impact on users? I just want to know the possibilities of scaling horizontal with Availability groups as we have not the possibility on scaling vertically by adding resources to the server, the backups can be taken on a secondary replica right?

  • Are you aware that secondary replicas are read-only? Native load-balancing to read-only secondaries is introduced in SQL Server 2016. – Dan Guzman Jun 30 '16 at 11:14
  • Yes, Im aware that secondaries are read-only. But this a way to basic load balancing reporting workloads or even Backups. – J1mmy Jun 30 '16 at 11:50
  • Do you know if my "Infrastructure" could be setup? If I add one more node I can transfer one primary Database to it and change the rest to Secondary? Thanks! – J1mmy Jun 30 '16 at 11:56
2

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ?

IMHO, NO - since you are on sql server 2012.

Why ?

SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is accessible else it will pick up the next available replica.

So in sql server 2012 and 2014, even if you have multiple secondaries available for honoring read requests, its not possible to spread the read load across the available secondaries.

SQL server 2016 allows you to configure load-balancing across a set of read-only replicas.

Is this possible to configure with minimum impact on users?

It is possible with minimum impact, but as I said above it would not be worth doing it.

How to do it ?

  • Add server 3 to windows failover cluster.
  • Take full backup from server 1 (since it is primary) and restore it with no recovery on server 3.
  • Take a log backup from server 1 and restore it with norecovery on server 3.
  • Join the database to the availability group.
  • Change the sync mode to Synchronous and then Failover the server1.db1 to server3.db1 and now server3.db1 will be primary.
  • Resume the data movement on all the secondary replicas. In my testing, the data movement gets suspended and you have to resume it.

Below is how it will look (the drawing is not pretty, but it explains what I am telling :-) ):

enter image description here

the backups can be taken on a secondary replica right?

Yes, you can take copy_only full & regular log backups on secondary replica. A full backup must be performed on primary database.

  • Wow, thanks a lot taking your time to answer it, very well explained to be honest. The point of adding a third server is by the moment we need it because the current 2 nodes cannot hold all the workload anymore. Then, the new database will be Primary on the server 3 and secondary on servers 1 and 2. I know having databases replicated on more than 1 server until we dont go for SQL2016 its more or less useless but it gives us extra redundancy. I think Database 3 should be on a new Availability Group also. – J1mmy Jun 30 '16 at 17:19
  • Well my point was that if you are just adding the server for readonly routing, then its not worth. I dont know your workload, but if you feel that you need a 3rd node, go for it. Please upvote/accept as answer so it does not flag as unanswered ! – Kin Shah Jun 30 '16 at 17:25
  • Thanks a lot, One last question, Can I have SQL AG with Primary Databases on different servers Right? So Database 1 on server 1 and Database 2 on Server 2 with secondaries in the oposite, this makes sense? I think will look like a Multi Instance ACTIVE-ACTIVE FCI. – J1mmy Jun 30 '16 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.