8

I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with SQL 2017 standard on each and has now asked me to plan a HA/DR plan for the new DW database/instance.

I immediately thought of using AlwaysOn Availability Groups, although I have never used them before, and none of the articles I read talk about typical data warehouse workloads - it's all OLTP applications. With a large daily ETL process and a smaller intra-day ETL process running on their current DW, will that have an impact on how we approach this?

Thanks - any help to point me in the right direction here would be beneficial!

Improve this question
5
  • 2
    IMHO, Always On is for OLTP. Also, for Always On AG you need to have Enterprise edition which means $$$. You can run Always On fail-over cluster instances on Standard edition though, and basic availability groups Ref Docs. Though he stated HA/DR, does he really just want DR? This would change the suggestions. What's the RPO and RTO for this DW?
    – S3S
    Commented Aug 22, 2019 at 13:47
  • Hi Nick, welcome to dba.SE. Is the focus on a single database or multiple databases? As @scsimon mentioned, when using standard edition you would only be able to use 'Basic Always On availability groups'. This can work depending on some other factors. (E.G. with multiple databases in different basic AG's, some db's might be primary and other databases might be secondary on the same instance.)
    – Randi Vertongen
    Commented Aug 22, 2019 at 13:50
  • Thanks - he mentioned DR, but he's flexible. In fact the term he used was 'HA-ish' so I think he's open to various sensible suggestions. I hadn't considered failover clusters - my lack of understanding as primarily a developer, not a DBA. I'll look into that.
    – Nick Baker
    Commented Aug 22, 2019 at 15:41
  • 1
    The database size is a typo? 1.5Gb fits in a express installation and with such a small data size almost anything HA-related within SQL looks as overkill to me...
    – Paolo
    Commented Aug 23, 2019 at 3:29
  • Sorry yes that should be 1.5Tb
    – Nick Baker
    Commented Aug 23, 2019 at 11:51

2 Answers 2

Reset to default
10

Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since there's no automatic failover.

If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a Failover Cluster.

It doesn't care what recovery model your databases are in, since it's more reliant on Windows than SQL Server. Your SQL Server just has the option to live on different nodes in the cluster if something goes amok with one. The one requirement of this technology is using shared storage, like a SAN.

You get automatic failover in most situations, but no readable replica (you don't get that with BAG, either). With Standard Edition, you're limited to a two node cluster, but that shouldn't be a big deal.

Failover Clusters are also a lot easier to manage if you're not too savvy with SQL Server. You really need basic Windows sysadmin skills. AGs can be a tough cookie when they go down, or when a patch goes bad (which sadly happens rather often).

Improve this answer
2
  • Awesome thanks that is exactly right - simple recovery model all the way. I had wondered about the impact of changing that just for this. I'll do some more reading on failover clusters thanks.
    – Nick Baker
    Commented Aug 22, 2019 at 15:39
  • Definitely getting an upvote here, but I would also opine that if the OP is running VMs with an appropriate backup method in place, FCIs largely become obsolete as their purpose is largely redundant (in regards to DR) to a VMotion event, though they still have HA relevance in regards to minimizing downtime with server patching.
    – John Eisbrener
    Commented Aug 23, 2019 at 13:55
0

I can see your have SQL Server 2017 STandard Edition, so you can use the Basic Always On availability groups for a single database.

Please check the details here : Click here

Limitations

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.