2

We have a SQL Server 2008 R2 cluster with 2 node and total data is about 2 TB now that cluster is log ship to 4000 Miles another data center. I know about a 2 years back we have mirroring setup instead of log shipping, but issue happening at night when load jobs runs @ take 22 hours instead of 3 to 4 hours every night so we try to put mirroring in asynchronous mode but still it didn't make any difference so we configure log shipping for every 15 minutes.

But now it needs upgrade to SQL Server 2012 & we like to do always on but its mirroring behind always on so will it work or is there any other way?

  • Well frankly with 2TB database things would always be complex. Go for AOAG only if you clearly have defined your HADR topology. With such a huge database AOAG does not have very good response, is expensive and hard to maintain. This is my personal experience. But what you are asking requires a round of testing – Shanky May 13 '16 at 16:07
4

But now it needs upgrade to 2012

I would suggest you to go with SQL Server 2014, unless you have a compelling reason to stick with SQL Server 2012.

AlwaysON is getting a lot of fixes back ported from SQL Server 2016.

we like to do AlwaysON but its mirroring behind AlwaysON so will it work or is there any other way ?

This is not true. The mechanism is different.

Mirroring will flush the log block to disk and once completed locally, it sends the block to secondary.

AlwaysON flushes the log block(s) in parallel. A secondary could have hardened log block(s) before the primary I/O completes. This design increases performance and narrows the NOT IN SYNC window(s).

total data is about 2 TB now that cluster is log ship to 4000 Miles another data center.

The data size matter to a certain extent - when doing an initial sync. What matters is the number of transactions occuring - meaning how busy your database is and the network bandwidth between the 2 data centers. The way you do index maintenance needs to be revisited as well.

There are other stuff like - backups (Full and log backups), application connection string chages to use multisubnet failvoer and hostTTL settings, reading from secondary, etc play crucial role when embracing a new technology.

I would suggest you to start reading : Stairway to AlwaysOn as a good start and then AlwaysOn Professional for some advance stuff.

Read : How It Works: Always On–When Is My Secondary Failover Ready?

|improve this answer|||||
  • AlwaysON is getting a lot of fixes back ported from SQL Server 2016. Where, by chance, did you hear this? – Sean Gallardy May 14 '16 at 16:28

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.