Today is the day when order becomes chaos
I recently needed to move live data (> 6 Terabytes) from an Azure data warehouse to a another Azure data warehouse in different tenant while keeping track of any ongoing data modifications at the source. My secondary objective was to avoid data corruption – so I figured I needed a CDC system. At the time … Continue reading Change Data Capture* For Azure SQL Data warehouse
Microsoft recently added snapshots to the Azure SQL datawarehouse recovery arsenal. What does it do? A snapshot is like a point-in-time marker, which allows you to restore a datawarehouse to a state recorded at the marker. Restore points are useful in more than just the traditional bcp scenarios, such as disaster recovery. Restore points can … Continue reading Restoring Azure SQL Datawarehouse
Last year the Azure DW team announced the availability of added another hotly requested feature, auto creation of statistics. Auto creating statistics has many known benefits which impacts DevOps, as database deployment pipelines don’t have to include the statistics creating queried, DBA’s who don’t have to worry about the lack of statistics on objects and … Continue reading Azure DW, The Peril of AUTO_CREATE_STATISTICS
Getting the sizes for an Azure SQL DW Tables can be tricky. This nifty function can get all the tables sizes (data, index, reserved, number of rows) for all user created tables. Get-AzureDWTableSize.ps1
In this post we explore data warehouse performance, taking into account the new Gen2 tier. A key goal when optimising request (data warehouse speak for query) performance is to reduce any costly DMS operation, specifically shufflemove and broadcastmove. Performing the basic steps outlined in part 1, will in most cases reduce the impact of the costly operations. … Continue reading Azure Data warehouse Performance Tuning 102
A short article about capturing transient telemetry. Most DMVs in the Azure SQL data warehouse are transient, which means data stored in the table is not persisted usually after: A Pause / Resume / Scale event After a max rollover threshold has been reached (10000 rows) For example, the data in the sys.dm_pdw_request_steps dmv – … Continue reading Capture Telemetry – Azure SQL Data Warehouse
The Data warehouse has recently been upgraded to the next version called Gen2. The good news is that the upgrade process is relatively seamless. Upgrading is supposed to be a one step process but I have decided to add 2 extra steps: Backup / Take a snapshot Upgrade the data warehouse Rebuild all indexes Backup / … Continue reading Upgrading The Azure Data warehouse To Gen2
When it comes to performance tuning or troubleshooting, for a standard SQL server many tools and techniques exist. For example query execution plans can be analysed, profiler exists, and a host of other dmvs which a DBA cannot live without. In an Azure Data warehouse there are similar dmvs. However some basic principles can be … Continue reading Azure Data warehouse Performance Tuning 101
Managed Service Identity (MSI) was introduced last year. Since then quite a few articles have been written about it: Use a Windows VM Managed Service Identity (MSI) to access Azure Key Vault: Azure SQL authentication with a Managed Service Identity MSI gives your code an automatically managed identity for authenticating to Azure services, so that you … Continue reading Azure MSI: Connect Using PowerShell Or .NET?
Microsoft’s Azure data warehouse is a cloud hosted PaaS offering. Which implies compute and storage resources are managed by Microsoft / Azure fabric. Scheduled and unscheduled maintenance activities occur which are supposed to be transparent, i.e. should not impact the platform. This is a false assumption. On some occasions maintenance will impact the platform. For … Continue reading Azure Parallel Data Warehouse (PDW) Errors #1: cancelled transactions
peter.irojah.innovation 