Azure-Sql-Db

This is the final part of a series on using Change Data Capture with Azure SQL Database, and looks at accessing the data from change data capture from another database.

Scenario

I often use Azure SQL Database as a data warehouse. It’s a perfectly fine database for that. But the challenge with Azure SQL Database is always about how to access data in other databases.

Many people don’t seem to realize that you can use External Tables (and External Data Sources) to read data in one Azure SQL Database, in another Azure SQL Database.

2024-01-22

The latest online on-demand course that I’ve been working on is now out the door. SQL Server Locking, Blocking, and Deadlocks for Developers and DBAs is available at our site: SQL Server Locking, Blocking and Deadlocks for Developers and DBAs (sqldownunder.com)

It’s designed to let you learn to find and fix locking issues, and to avoid them in the first place.

To celebrate the release, use coupon code LOCKINGRELEASE until January 20th to get 25% off the price.

2023-12-31

I know it’s been a while, but there’s a new SQL Down Under show published!

While I’ve been busy with https://cosmosdownunder.com and https://fabricdownunder.com , I haven’t forgotten the SQL Down Under shows. It’s time for some more SQL Server love.

I had the great pleasure yesterday to record a podcast with one of my UK based friends Rob Sewell. Rob’s one of the rare breed that is both a Data Platform MVP and an Azure MVP.

2023-11-11

In a previous post, I wrote about how to create a hash of all the columns in a table, by using FOR JSON PATH and HASHBYTES. This is incredibly useful if you need to check if incoming data is different to existing table data.

The code that I suggested (based on WideWorldImporters) was as follows:

SELECT po.PurchaseOrderID,
       HASHBYTES('SHA2_256', pod.PurchaseOrderData) AS HashedData
FROM Purchasing.PurchaseOrders AS po
CROSS APPLY
(
    SELECT po.*
    FOR JSON PATH, ROOT('Purchase Order'), INCLUDE_NULL_VALUES
) AS pod(PurchaseOrderData);

The challenge with that code though, is that for the existing table data, it’s best calculated when the data is stored, rather than every time it’s queried. And, bonus points if you then create an index that holds just the key for matching plus an included column for the HashedData. With a bit of careful work, you can get an efficient join happening to find differences.

2023-10-09

This is part 4 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.

When I was reading the documentation for CDC in Azure SQL Database, I kept coming across a mention that it required at least an S3 service level objective (SLO), if you were using a DTU-based database.

I really hoped that wasn’t the case.

2023-09-23

I had an email from a friend today where he asked what was wrong with SQL Server, and wondered if ROUND() didn’t go to the same school that he did.

The example was:

SELECT '0.5' [SOURCE VALUE], '1' [SHOULD ROUND TO], ROUND('0.5',0) [SQL SAYS]
UNION
SELECT '1.55', '1.6', ROUND('1.55',1)
UNION
SELECT '94.55', '94.6', ROUND('94.55',1);

The output was:

What was puzzling him is why SQL Server’s ROUND was converting 94.55 to 94.5 and not 94.6. Now writing numbers as strings is problematic to start with, but that aside, he was puzzled by the output.

2023-07-11

This is part 3 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.

To show how this works, I have created an Azure SQL Database called CDCTest. I created it as a DTU-based database with a service level objective (SLO) of S3. I’ll discuss more about the licensing implications of CDC in part 4.

2023-06-02

In the part 1 of this series, I discussed the positioning of Change Data Capture. In part 2, I want to cover how it works.

Log Reading

There are many ways that you can output details of changes that occur in data within SQL Server. Many of those methods require actions to occur at the time the data change is made. This can be problematic.

The first problem with this, is the performance impact on the application that’s making the change. If I update a row in a table and there is part of the process that writes details of that change to some type of audit or tracking log, I’ve now increased the work that needs to happen in the context of the application that’s making the change. Generally what this means, is that I’ve slowed the application down by at least doubling the work that needs to be performed. That might not be well-received.

2023-03-06

I often need to capture the changes from one database into another. The most common reason is that I’m wanting to bring changes from a transactional system across into a data warehouse that’s part of a BI setup.

So which technology is best to use for this?

That’s not a trivial question to answer but here are some thoughts:

Replication?

Unfortunately, this one’s not available for Azure SQL DB as yet. Azure SQL DB can be a subscriber in Transactional Replication. We often use it this way. If we have an on-premises SQL Server, one of our favourite ways to get data into the cloud is by using Transactional Replication. (If you need to get your head around Replication with SQL Server, just head to our course here).

2023-02-26