Stairway to Always On Level 1: What is "SQL Server Always On"?

Question

Stairway to Always On Level 1: What is "SQL Server Always On"?

Perry Whittle

SSC Guru

Points: 234013
More actions
September 24, 2014 at 12:19 am

#293532

Comments posted to this topic are about the item Stairway to Always On Level 1: What is "SQL Server Always On"?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ian Massi SSCertifiable Points: 5931 More actions · Answer 1

The timing of this stairway is excellent. I'm looking at setting up AlwaysOn in my environment and this first article is excellent to explain the different components to my manager and the server/network guys.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Hi

Thanks for the comments please don't forget to rate the article if you found it useful

Regards Perry

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

Thanks for this timely introductory article, as I am doing research on AlwaysOn this week 🙂

Some requirements though

AG Group is only available on Enterprise edition @@ = costly. I believe Replicas can be on Standard edition?

AG has to be on a Cluster node = again, costly vs. Log Shipping/Mirroring (deprecated) where one can do it on standalone to standalone

Features Supported by the Editions of SQL Server 2014

http://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Jerry Hung (9/24/2014)
Thanks for this timely introductory article, as I am doing research on AlwaysOn this week 🙂

Thank you, it's a 9 level stairway, there are 8 more levels to come yet 😉

Please rate the article if you found it useful

Jerry Hung (9/24/2014)
AG Group is only available on Enterprise edition @@ = costly.

Hey, there's no such thing as a free lunch 😉

Jerry Hung (9/24/2014)
I believe Replicas can be on Standard edition?

No, this link details the following

SQL Server AlwaysOn Pre Reqs and Restrictions
Each server instance must be running the Enterprise Edition of SQL Server 2012\2014

Jerry Hung (9/24/2014)
AG has to be on a Cluster node = again, costly vs. Log Shipping/Mirroring (deprecated) where one can do it on standalone to standalone

Yes a cluster node is required but where do you get that the cost is higher? Windows 2012 Standard supports clustering and if you're not using shared storage to support an FCI as a replica then cost shouldn't be an issue. All of your servers will likely have at least Windows 2012 Standard edition. Also don't forget that to get full mirroring features this required Enterprise edition of SQL server 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jerry Hung SSChampion Points: 12968 More actions · Answer 5

Perry Whittle (9/24/2014)
Jerry Hung (9/24/2014)
Thanks for this timely introductory article, as I am doing research on AlwaysOn this week 🙂
Thank you, please rate the article if you found it useful

Rated Excellent!

Jerry Hung (9/24/2014)
I believe Replicas can be on Standard edition?

No, this link details the following

SQL Server AlwaysOn Pre Reqs and Restrictions
Each server instance must be running the Enterprise Edition of SQL Server 2012\2014

Well that sucks

Perry Whittle (9/24/2014)

Jerry Hung (9/24/2014)
Jerry Hung (9/24/2014)
AG has to be on a Cluster node = again, costly vs. Log Shipping/Mirroring (deprecated) where one can do it on standalone to standalone
Yes a cluster node is required but where do you get that the cost is higher? Windows 2012 Standard supports clustering and if you're not using shared storage to support an FCI as a replica then cost shouldn't be an issue. All of your servers will likely have at least Windows 2012 Standard edition. Also don't forget that to get full mirroring features this required Enterprise edition of SQL server 😉

Sadly, no Windows 2012 for OS at work yet @@ so I am testing SQL 2014 on Windows 2008 R2 (which is probably not ideal AG setup anyway)

I meant "higher cost" as in Clustering usually needs 2 nodes vs. 1 (unless we set up a 1-node Cluster, ha, I know the Pros on this)

If I understand correctly,

AG Primary (2-node cluster) -> AG Replica (2-node cluster) = 4 nodes minimum usually, SQL Enterprise = $$$$$

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Jerry Hung (9/24/2014)
Rated Excellent!

keep an eye out for the remaining 8 levels of the stairway 😉

Jerry Hung (9/24/2014)
Sadly, no Windows 2012 for OS at work yet @@ so I am testing SQL 2014 on Windows 2008 R2.

Hmm, check out the price of Windows 2008 R2 Enterprise edition as Windows 2012 Standard has re aligned.

Jerry Hung (9/24/2014)
I meant "higher cost" as in Clustering usually needs 2 nodes vs. 1 (unless we set up a 1-node Cluster, ha, I know the Pros on this)

True, clustering needs at least 2 nodes to fully function, but then, an AlwaysOn group requires as many nodes as you'll have replicas.

If you plan an AlwaysOn group with 4 replicas you'll need 4 nodes. An AlwaysOn replica must reside on a separate node to all other replicas in the group, this is a pre req which is checked when enabling the AlwaysOn group configuration.

Jerry Hung (9/24/2014)
If I understand correctly,
AG Primary (2-node cluster) -> AG Replica (2-node cluster) = 4 nodes minimum usually, SQL Enterprise = $$$$$

What you're describing here is an AlwaysOn group configuration where both replicas are FCIs, the restrictions doing this are huge and also re introduce the storage single point of failure since you'll need shared storage for each of the FCIs.

AlwaysOn groups ideally use standalone instances of SQL server, it's just that all the nodes are joined to the same Windows cluster.

Just because your nodes are in a Windows cluster doesn't mean you automatically have to deploy FCIs, in fact don't, otherwise you cannot utilise automatic failover in the AlwaysOn group 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Miles Neale SSChampion Points: 13147 More actions · Answer 7

Thanks excellent information and very readable.

(Already rated)

Not all gray hairs are Dinosaurs!

quackhandle1975 SSChampion Points: 11055 More actions · Answer 8

Nice article, looking forward to the next in the stairway.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

quackhandle1975 (10/27/2014)
Nice article, looking forward to the next in the stairway.
qh

Here you go

http://www.sqlservercentral.com/articles/AlwaysOn/107537/[/url]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

March 9, 2016 at 4:47 pm

#1864146

Thanks for the article.