0

I'm surely using the wrong terminology, but I have the following:

SQL Server A (SAN1) - DB1, DB2, DB3

SQL Server B (SAN2) - DB4, DB5, DB6

It's currently SQL 2008 and we're looking to move to SQL 2014. I'd like to set up High Availability, and I'm wondering if I can configure it for the following scenario.

Have DBs 123 be (replicated?) to B and 456 replicated to A. Then, if SQL Server A fails, DBS 1,2, and 3 will be live on Server B (along with 4,5, and 6).

Likewise, if Server B fails, have 4,5, and 6 be live on A.

Hopefully this makes sense.

Thanks so much

2
0

I'm gathering you're looking to roll both high availability and some kind of load-balancing into one system. There is only really one way you can kind of achieve something like this with a SQL Server 2014 Availability Group and it isn't exactly what you noted:

Readable Secondary Replica

https://msdn.microsoft.com/en-AU/library/ff878253.aspx

The AlwaysOn Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. Furthermore, full-text indexes are synchronized with the secondary databases. In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

Security settings that occur in the primary databases are persisted to the secondary databases. This includes users, database roles, and applications roles together with their respective permissions and transparent data encryption (TDE), if enabled on the primary database.

Essentially, reads will balance across both servers but writes will only occur to the current active server. Note that in SQL Server 2014, the readable secondary replica process will only target the first secondary server, not any subsequent servers, so having more than two does not improve performance for this feature.

Always On Availability Groups were and are intended entirely as an HA/DR solution. They aren't intended to do load balancing. Indeed, there is no out-of-the-box way for SQL Server to load balance. There are third party tools that can achieve something similar but you're looking at database-level changes and potential application re-architecture which is probably not in the scope of this answer.

1
  • That's what I was gathering. Ok... another question to follow.
    – Rob
    Jan 21 '16 at 14:32

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.