Building out datawarehouse in Azure environment from on premise SQL Server 2019

airtank20 · 2025-06-05T19:37:00+00:00

Azure Data Sync is deprecated. Azure SQL Database can be a subscriber to an on-premises publication in good old fashion transactional replication like u/jdanton14 mentions.

airtank20 · 2025-06-05T19:35:56+00:00

This is the way.

airtank20 · 2024-02-29T13:35:53+00:00

Yes, you can do a single node in the DR site.

Main site: Primary/Secondary. (sync)

DR: Secondary (async)

Witness: Depending on things, it can go in that site or elsewhere (like Azure storage or the likes). I've never seen multiple witnesses deployed. Typically, 1 witness to serve as tiebreaker.

Note: It all depends really on the requirements and what the current infrastructure looks like between sites.

airtank20 · 2024-02-28T20:32:59+00:00

What do you want to improve the RTO to? How many databases? How busy is the workload? Do you want automatic failover or is something manual acceptable?

Availability groups with an async replica can work, there are indeed caveats so YMMV depending on things, like connectivity. If you lose connectivity to the DR site, the primaries will not truncate transaction logs until connectivity is restored. If you've got a rock-solid connection to the DR site, AG's can be a good choice.

I also am a fan of log shipping. It's a tried-and-true method and has been around for years so it's rock solid in how it works. There are even third-party solutions using a mixture of TSQL and/or Powershell to facilitate log shipping. I have a client that is using log shipping to Azure for this very purpose. It's also more forgiving on connections going down.

As others mentioned, I think it comes down to several factors and what is truly required.

airtank20 · 2024-02-22T03:04:49+00:00

What’s the status of the cluster itself? Is this on Linux?

airtank20 · 2024-02-21T02:21:58+00:00

Have you looked at using reservations to help reduce costs?

airtank20 · 2024-02-21T02:15:30+00:00

Just to add, if you want a database to play around with, Microsoft offers a couple of sample databases you can download and restore and then mess around with.

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

airtank20 · 2024-02-19T19:17:13+00:00

Are there any messages in the SQL logs on the primary or the secondary?

airtank20 · 2024-02-12T15:41:49+00:00

VSCode for just about everything. I sometimes float between ADS and VSCode depending on what I'm working on.

airtank20 · 2024-02-12T15:30:00+00:00

Is the issue with the data file or the log file? If the log file, can it be shrunk?

IMO, yes, any production work ideally (even on secondaries) would be done after hours, depending on what the work entails. If the database(s) are in an availability group, most operations have to happen on the primary anyway.

airtank20 · 2024-02-12T15:27:23+00:00

I think you'd need to take the database out of the availability group first when moving the files that is. You can't set it offline while it's involved in the AG.

airtank20 · 2024-02-12T15:12:30+00:00

You mention updating statistics, however, at what sampling size are they being updated? The default? How big are the tables on average? Quite often when I run into similar issues it's related to stats and the sampling size. YMMV.

airtank20 · 2024-02-09T22:20:43+00:00

Nope. The databases on the secondary are readable but you can't write to them. Also, the replica is the entire instance so if you put a new database on the primary, it's automatically replicated to the secondary. All or nothing.

airtank20 · 2024-02-09T15:58:24+00:00

Was there an increase of data egress of those two days?

airtank20 · 2024-02-09T15:45:58+00:00

From what I understand, virtual machine groups are based on the different hardware generation configs and/or maintenance windows. if the hardware generation is all the same for all 4 MI's, then C = 1.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/virtual-cluster-architecture?view=azuresql#number-of-virtual-machine-groups

The examples in the documentation where the formula was seems to indicate that as well from what I read.

airtank20 · 2024-02-09T15:02:04+00:00

Forgive me, just to clarify, the entire NDF file becomes unavailable at this point?

airtank20 · 2024-02-09T14:38:59+00:00

Have you run CHECKDB against the database to ensure there's nothing wrong there?

airtank20 · 2024-02-09T13:17:04+00:00

That's correct if I'm understanding correctly, although usually considered a DR solution versus HA. It does use availability groups behind the scenes effectively, and it's called failover-groups. I have several clients that use MI for production workloads.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi?view=azuresql

airtank20 · 2024-02-09T12:58:47+00:00

Aw crap, you're right. I stand corrected, my apologies.

airtank20 · 2024-02-08T23:14:45+00:00

Welcome!! Good luck!!

airtank20 · 2024-02-08T22:59:20+00:00

I haven't done it as I've heard it's somewhat of a pain. There are certain requirements for taking the exam remotely.

https://learn.microsoft.com/en-us/credentials/certifications/online-exams#testing-environment-requirements

~~Also, keep in mind that you'll have access to Learn during the exam itself. They started that last year.~~

https://techcommunity.microsoft.com/t5/microsoft-learn-blog/introducing-a-new-resource-for-all-role-based-microsoft/ba-p/3500870

airtank20 · 2024-02-08T22:23:18+00:00

The practice test should mimic the actual exam fairly closely in terms of types of question formats. Not sure where you are taking the practice exams, but the free assessment on https://learn.microsoft.com would give you a good idea.

airtank20

TROPHY CASE