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.

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: https://podcast.cosmosdownunder.com

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.

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.

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.

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. Solution I changed ‘TestRule’ to N’TestRule’ and it worked fine.

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.

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.

2021-02-11