Solved

SQL Server 2012 High Availability options

Posted on 2014-03-28
3
330 Views
Last Modified: 2014-04-14
I have a web-based application based on SQL Server 2012 which we want to have highly available, meaning 2 separate SQL servers in the same datacenter 'in sync' with each other, and a process whereby if the main SQL server fails, within a minute or so the application servers are talking to the backup SQL.

We also have a 2nd datacenter with 1 SQL server which we'd also like in the pool if possible.

I know SQL Standard and Enterprise 2012 offer AlwaysOn, but that is the only feature that matters to me and differentiates them from SQL Web, and the price difference is huge.

Can anyone recommend a 3rd party product to do this? Perhaps Doubletake or something similar?

Thanks
0
Comment
Question by:tabush
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39965753
I'd encourage you to look at Stardard edition - it runs about $1700/core retail and it supports a two-node failover cluster instance (FCI), which addresses your HA within the same data center (Web edition doesn't support failover clustering). An FCI includes two physical servers, with shared storage between them (your SAN, presumably), and one server is ready to take over for the other in case of a failure, usually with around 30 seconds of downtime.

You're correct that AlwaysOn would work well in the situation you're describing with DR at a separate data center, but since it's an Enterprise feature, you're looking at going from $1700/core to $7800, and likely isn't worth it for you. That said, standard edition still supports all forms of replication, so you can set up either log shipping or maybe transactional replication to your DR site from your primary servers. That way, your remote server is ready to take over once you redirect your application tier.

From a licensing perspective, you may not even need to license the remote site - as long as you're not running reporting from the DR server and no clients are connecting, I don't believe it has to be licensed. However, you'll want to confirm that with a license specialist (I'm not one), as the licensing for HA, DR, and warm failovers is very nuanced, where some implementations require licensing and some do not. If we were to license the second site, you now have the option of running reporting from the replicated server, which would alleviate some load on your primary servers (if that's an issue).

I hope that answers your questions, and if you need some more detail, please elaborate and I'll fill in where I can.
0
 
LVL 2

Author Comment

by:tabush
ID: 39966888
thanks @ryanmccauley, i'm actually looking to avoid shared storage, which i believe always on solves, but it's damn expensive... we are looking at SPLA licensing and it's literally around 10x the price of web edition.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 39966960
Ah - that makes sense. Unfortunately, you're limited on replication options as well - Web edition can be the recipient of a replication stream, but it can't be the originator. However, Web Edition does support log shipping, so you may want to look into that:

http://technet.microsoft.com/en-us/library/ms187103.aspx

Alternatively, you could have a bit more control if you wanted by taking backups and manually replicating them to your secondary site. I've set up something like this before (manually) and it could easily be modified to include transaction log backups as well as full backups (link to my blog post about it, including scripts):

http://www.trycatchfinally.net/2009/09/moving-a-sql-server-database-to-another-server-on-a-schedule-without-using-replication/

Basically, a SQL Agent job is set up on the source side that takes the backups and compresses them (I was using SQL 2000 - however, SQL 2012 Web Edition doesn't support compression, so you'd likely stick with the same approach), and then ships them to a remote site via FTP. Once they're at the remote site, another SQL Agent job on the destination server uncompresses and restores the backup.

Using this process, you could keep a remote site pretty close to in sync by sending log backups every few minutes. However, log shipping would accomplish much the same thing and is natively built in to SQL Server 2012, so unless you've got an aversion for some reason, that's probably the way to go.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now