Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Has anyone implemented SQL Server 2012 Always On for disaster recovery


Has anyone implemented SQL Server 2012 Always On for disaster recovery

Author
Message
Mike Byrd
Mike Byrd
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 389
Am helping a client upgrade his servers (currently SS 2005 with Windows 2003 cluster). Talked to Dell sales rep this afternoon and he had not heard of anyone implementing Always On for DR. He said all his sales for SS2012 have been for active/passive cluster.

I've attended several SQL Saturday sessions and webinars on Always On, but now are worried that nobody has really implemented it in production. I don't want the client to be on the technology tip by himself, but would like to take advantage of Always On if it really works.

I would appreciate it if anybody could tell me if they have implemented Always On and what pros and cons they have found.

Thanks in advance,
Mike Byrd

Mike Byrd
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6012 Visits: 8314
Mike Byrd (4/12/2013)
Am helping a client upgrade his servers (currently SS 2005 with Windows 2003 cluster). Talked to Dell sales rep this afternoon and he had not heard of anyone implementing Always On for DR. He said all his sales for SS2012 have been for active/passive cluster.

I've attended several SQL Saturday sessions and webinars on Always On, but now are worried that nobody has really implemented it in production. I don't want the client to be on the technology tip by himself, but would like to take advantage of Always On if it really works.

I would appreciate it if anybody could tell me if they have implemented Always On and what pros and cons they have found.

Thanks in advance,
Mike Byrd


Your sales person is either misinformed, speaking only based on knowledge that if people are making SQL Server Cluster they state so to ensure they get "certified" hardware or is straight-up lying to you to get you to buy more expensive "cluster certified" hardware. I have 4 clients that have or are in the process of setting up some form of 2012 "enhanced mirroring" (e.g. Always On) for HA/DR/read-only scale-out (or any combination of those 3). There are some amazingly compelling reasons and benefits, and given that it's foundational underpinnings is database mirroring which has been around since SQL 2005 SP1, it is pretty solid given that technically Always On is a v1+SP1 feature! The one con that I know of is a limitation of single-threading the stuff across and hardening it on secondary - which I have heard means a real-world 50MB/second throughput give or take. I note that that is a LOT of data/transactions and that 99.x% of the installed base will never get even close to that. Well, there is also the cost of SQL 2012 licensing as well as the requirement for a Windows Failover Cluster of all machines involved (including any on a different subnet).

If you would like more information feel free to Private Message me.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
JayK
JayK
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 1133
We just rolled out AlwaysOn in our reporting environment last week to replicate 2 databases from our data warehouse to allow our data analysts query the report databases without effecting the ETL processes during the day on the primary.

The only issue we found when rolling out one of the databases was that we had to turn off transaction log backups on the primary while the replica ws being made on the secondary. Its very early days but so far all is good - the setup was very straighforward and we are not having any issues with latency - we set it up as Asynchronous-commit mode as our primary is in Melbourne and Secondary in Darwin.

As I said its early days but so far so good!
fb.cs99
fb.cs99
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
Did you need to upgrade all of your SQL instances to 2012 in order to enable AlwaysOn?
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1962 Visits: 3741
We have implemented AlwaysOn Availability Groups (AG) on a 3-node cluster, consisting of 2 subnets: primary and DR.

2 nodes on the primary site host a failover-cluster instance (FCI) in an active-passive configuration.

The 3rd node on the DR hosts a standalone SQL instance, serving as a secondary AG replica in async-commit mode.

No issues so far with this configuration, although we have observed transaction-log latencies of up to 20 minutes between the primary and secondary replicas.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
fb.cs99
fb.cs99
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
@SSCommitted Did all of your database servers have to be upgraded to 2012 in order for your configuration to work?
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1962 Visits: 3741
fb.cs99 (5/8/2013)
@SSCommitted Did all of your database servers have to be upgraded to 2012 in order for your configuration to work?


Yes.
Also all database servers need to be in a Windows Server 2008 R2 SP1 (or later) Failover Cluster.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
goher2000
goher2000
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 345
I have implemented Always ON, here is the configuration

I have 3 node cluster , 1 with stand alone sql server and 2 nodes are HA active/passive sql server cluster. all three nodes are in same subnet

now i want to add another node for DR which is at some other location with different subnet. (Stand alone sql only)

i have the following question

will adding new node to the existing WINDOWS cluster bring down services in the cluster?



Minaz Amin
Minaz Amin
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1750
I have recently did setup 2012 always on with 2 active/passive node with different NW subnet adding another instance in the AAG to be the mirroing. This is working fine.

"More Green More Oxygen !! Plant a tree today"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search