SQL Server database "fail safe"

Question

SQL Server database "fail safe"

PiMané

SSChampion

Points: 13965
More actions
May 5, 2021 at 11:41 am

#3879583

Hi,
I have SQL Server 2019 Enterprise Edition and would like to know what would be the best way to have a "copy" of a database ready to wakeup if the main server goes down (either the server or the SAN): Always On availability group or transactional replication or other way.
The database has a lot of transactions per second.
Regards,
Pedro

If you need to work better, try working less...
May 6, 2021 at 9:16 am

This was removed by the editor as SPAM
May 8, 2021 at 10:43 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Availability Groups are generally considered to be the modern, safe, approach to high availability. At the very least, they get a lot more love & attention from Microsoft than any of the alternatives.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PiMané SSChampion Points: 13965 More actions · Answer 2

Thanks...

I'll into it then.

Regards,

Pedro

If you need to work better, try working less...

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

We just do a Windows Cluster (I say "we"... I mean our infrastructure group). It's worked well for us over the last decade and has advantages over things like AG. Of course, "It Depends" but thought I'd bring it up as a viable and fairly low maintenance, low headache option that also allows you to do things like temporarily shift a database from the FULL Recovery Model to the BULK LOGGED Recovery Model for "spec.ops" and then back again with no fuss.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 4

Jeff Moden wrote:

We just do a Windows Cluster (I say "we"... I mean our infrastructure group). It's worked well for us over the last decade and has advantages over things like AG. Of course, "It Depends" but thought I'd bring it up as a viable and fairly low maintenance, low headache option that also allows you to do things like temporarily shift a database from the FULL Recovery Model to the BULK LOGGED Recovery Model for "spec.ops" and then back again with no fuss.

This also has the advantage of working with all editions - but is really just for HA (high availability). For a DR solution - this won't work if you lose the SAN where your shared storage for the cluster resides.

The advantage of an AG is the ability to have your data on a different storage systems - or even in a separate data center. The downside - of course - is that you cannot switch recovery model to bulk-logged. Another advantage to an AG is the ability to have a near real-time read-only replica for reporting (or ETL processes).

Just for clarity - I have a 3-node cluster with one instance. Two nodes use shared storage and the instance is installed in the cluster - the 3rd node does not have shared storage and has a stand-alone instance installed. There is an AG setup with only those databases that are needed for reporting/extracts from the cluster instance to the stand-alone on the 3rd node. Works very well...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

I guess I'm not sure exactly how the infrastructure guys are doing it but we have the Windows Cluster and it feeds a secondary offsite server (about 80 miles away) without doing anything in SQL Server to make it happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PiMané SSChampion Points: 13965 More actions · Answer 6

The purpose is to have the data on a different datacenter.

Don't know if you know OVH but their datacenter burned down completely last month.

So we need to find the best and fastest way of replicating data to another datacenter.

If you need to work better, try working less...

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 7

Probably SAN replication.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

That's the term that I've used for this before. I don't know how it works because it's out of my wheelhouse at work but it works.

The good part is, I don't have to worry about it or things like Recovery Models or log files or anything else. As they say in the Navy, "It works fine, fails safe, and drains to the bilge". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 9

Jeff Moden wrote:

That's the term that I've used for this before. I don't know how it works because it's out of my wheelhouse at work but it works.
The good part is, I don't have to worry about it or things like Recovery Models or log files or anything else. As they say in the Navy, "It works fine, fails safe, and drains to the bilge". 😀

SAN replication works well for DR - as long as the latency between sites is low enough and the bandwidth is high enough. A site 80 miles away may be eligible for 'black' fiber - it has been a long time since I looked into this topic and a lot has changed since then. But 80 miles is close enough that the latency will be low enough to transfer data across in near real-time.

Both AG's and SAN replication run into the same issues - if the destination is too far and latency too high - any significant amount of data changes that need to be replicated across the network can take a long time and risk data loss in the event of a disaster.

The only advantage AG would have over the SAN replication would be a near real-time read-only replica, but honestly - I wouldn't set that up on a DR site anyways. I would have that setup on a local AG - with another secondary for DR. In the event of a disaster - reporting/extracts are less important and can be rebuilt if needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

No simple answer here. Clustering, AGs, both work, but have limitations, pros, and cons. Best bet is to engage a consultant to talk through some ideas for an hour. I might recommend sqlha.com, Allan Hirt.