Mfcttrf

Why do all fields in a QFT transform like *irreducible* representations of some group?

Are illustrations in novels frowned upon?

Can't stopover at Sapporo when going from Asahikawa to Chitose airport?

Why don't electrons take the shorter path in coils?

Can you feel passing through the sound barrier in an F-16?

Is there any practical application for performing a double Fourier transform? ...or an inverse Fourier transform on a time-domain input?

Cross-referencing enumerate item

What is this symbol: semicircles facing eachother

Would it be possible to have a GMO that produces chocolate?

How to use "Du hast/ Du hattest'?

How should I face my manager if I make a mistake because a senior coworker explained something incorrectly to me?

Justifying the use of directed energy weapons

Shouldn't the "credit score" prevent Americans from going deeper and deeper into personal debt?

Why were the crew so desperate to catch Truman and return him to Seahaven?

Is my soulless catatonic body a valid target for the Imprisonment spell?

Potential new partner angry about first collaboration - how to answer email to close up this encounter in a graceful manner

Would this system work to purify water?

Can pay be witheld for hours cleaning up after closing time?

Efficiently pathfinding many flocking enemies around obstacles

What are some interesting features that are common cross-linguistically but don't exist in English?

Is a player able to change alignment midway through an adventure?

Mathematical uses of string theory

What professions would a medieval village with a population of 100 need?

What magic extends life or grants immortality?

What is the best option for High availability on a data warehouse?

High Availability for postgresqlAvailability mode - manual failover mode, best practice for availability mode?Data warehouse server. How do you calculate RAM/CPU specifications?I'm confused about High Availability/Always OnHigh Availability Options For SQL ServerHigh availability database switchoverBest way to capture changes on OLTP for Data Warehouse: Create/Update column or CDCNetwork Maintenance and High Availability Group Prep

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

I have a customer who have an existing 1.5Gb 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!

availability-groups sql-server-2017 data-warehouse high-availability disaster-recovery

share|improve this question

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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?
  
  – scsimon
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  

add a comment | 

4

I have a customer who have an existing 1.5Gb 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!

availability-groups sql-server-2017 data-warehouse high-availability disaster-recovery

share|improve this question

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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?
  
  – scsimon
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  

add a comment | 

I have a customer who have an existing 1.5Gb 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!

availability-groups sql-server-2017 data-warehouse high-availability disaster-recovery

share|improve this question

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

edited 8 hours ago

Randi Vertongen

8,12533 gold badges1111 silver badges3131 bronze badges

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

New contributor

Nick Baker is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

Nick BakerNick Baker

2133 bronze badges

2 Answers
2

active

oldest

votes

5

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).

share|improve this answer

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Nick Baker is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245995%2fwhat-is-the-best-option-for-high-availability-on-a-data-warehouse%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

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).

share|improve this answer

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  

add a comment | 

5

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).

share|improve this answer

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  

add a comment | 

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).

share|improve this answer

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

share|improve this answer

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

answered 7 hours ago

Erik DarlingErik Darling

26.2k1313 gold badges8181 silver badges131131 bronze badges

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

share|improve this answer

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

add a comment | 

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

share|improve this answer

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

add a comment | 

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

share|improve this answer

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

share|improve this answer

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges

answered 8 hours ago

Ramakant DadhichiRamakant Dadhichi

1,28755 silver badges2121 bronze badges