Azure

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

I published a new Cosmos Down Under podcast today.

In the podcast, I’ve discussed the new integration between Azure Cosmos DB and Azure Data Explorer with the program manager for the feature: Vincent-Philippe Lauzon.

It’s a great new capability and worth half an hour of your time to find out about it.

I hope you enjoy it. You’ll find it, along with all Cosmos Down Under podcasts at:

Cosmos Down Under Podcast

2023-02-17

I recorded the first of a series of podcasts for the SQL Server 2022 timeframe recently. Once again, it was great to chat with another old friend Davide Mauri. Davide is a Principal Product Manager for Azure SQL Database.

Note that the topic I chose for today was the new  sp_invoke_external_rest_endpoint system stored procedure, and it’s currently only available for Azure SQL Database but I expect it will get ported to other flavours of SQL Server over time. Davide has been deeply involved with the development of this procedure.

2022-12-16

I get pretty excited about new T-SQL enhancements. Back in 2016, I was so pleased to see Microsoft finally add a string split option to T-SQL, but my enthusiasm was limited when I saw how it was implemented. Now that’s mostly fixed !

While it’s possible to build functions that did string splitting just like I wanted, the problem is that no matter how you implemented them, they were really just too slow. And most importantly, much slower than a built-in function would be.

2021-11-05

Azure Data Factory (ADF) is great for moving data around. It often needs to connect to SQL databases. If you’re creating linked services for this, please try to avoid using SQL logins. Don’t use usernames and passwords for authentication if you can avoid them.

Azure SQL Database lets you can create a user from a data factory’s managed service identity (MSI). MSIs are a special type of Service Principal (SP). They provide an identity where you don’t need to manage the ID or any sort of password.  Azure does that for you.

2021-09-26

I often use the procedure sp_set_firewall_rule to set firewall rules for Azure SQL Server. (There’s a similar call to set the firewall for databases). The other day though, I got an error that had me puzzled:

I also tried it with named parameters and got the same error.

When I looked at my previous scripts, I realised that I had used a Unicode string for the first parameter previously.

2021-08-25

Azure SQL (both Azure SQL Database and Azure SQL Managed Instance) both have different password complexity rules to SQL Server. I was reading an email discussion list and a poster asked where he could find the list of password complexity rules for Azure SQL. I said I’d never seen a list.

Well it turns out that there is a list, but not where you might have thought to look. They’re spelled out in this article:

2021-04-28

Dynamic Data Masking was added to SQL Server back in 2016. I’ve almost never used it since. Why? There were two reasons:

#1: The first reason was that it didn’t work in conjunction with other security features. In particular, you couldn’t use dynamic data masking in conjunction with Always Encrypted. From the start, I’ve been telling the product group that this is a misstep. There’s very little data that I’d want to hide from my own staff, that I’m happy still being visible to a hosting provider.

2021-03-23

When I’m building Azure Data Factory pipelines, I often want to rescale, i.e. change the Service Level Objective (SLO) of Azure SQL Databases before and after processing. For example: I might have a database that sits at S0 or S1 all day long when not being processed (to allows for the odd adhoc query), but I want it at S6 to do overnight ingestion of data and loading of analytic data models. Then I want it to go back to what it was before.

2021-03-02

In the dying days of PASS, one of the attempts at keeping the organization afloat was the introduction of the PASS Pro membership. One benefit of that was access to a series of Learning Experiences. Since the demise of PASS, our friends at Red-Gate have purchased the assets, and that includes those courses.

I was pleased to hear from the Red-Gate team that the courses have now been made available for free, and without any type of paywall/loginwall. That’s just awesome.

2021-02-11