SQL Down Under show 86 with guest Armando Lacerda discussing SQL Server 2022 snapshot backups and data virtualization is now published

And yet another new SQL Down Under show is published!

Once again, I had the great pleasure yesterday to record a podcast with one of my long-term data platform MVP friends Armando Lacerda.

Armando is a cloud architect and engineer focused on data platform.
He is a long-term data platform MVP, MCT, speaker, trainer and coder.

In the show, we discuss changes involving two aspects of SQL Server 2022:

  • Backups using snapshots
  • Data virtualization

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

SQL Down Under show 85 with guest Bob Duffy discussing building data warehouses is now published

And another new SQL Down Under show is published!

Once again, I had the great pleasure yesterday to record a podcast with one of my UK based friends Bob Duffy.

Bob is a Principal Data Architect at Prodata's SQL Centre of Excellence in Dublin, Ireland. He helps enterprise customers build large scale enterprise data warehouses in Azure, typically integrating ERP and financial systems.

Bob is a long-term Data Platform MVP, a Microsoft Certified Architect, Master, and Analysis Services Maestro. He holds a number of Azure role-based certifications. Bob also specialises in performance tuning, architecture, load testing, DevOps, Software engineering, MQ architecture, semantic modelling and meta data driven design on the MS platform.

Bob used to work for Microsoft Consulting Services a Senior Consultant and subject matter expert for BI/data in Western Europe in 2005.

In the show, we discuss the approaches he takes to building data warehouses on the Microsoft BI platforms.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

Fabric Down Under show 7 with guest Philip Seamark now available!

Once again, I had the great pleasure to record a Fabric Down Under podcast. This time it was with a fellow "Down Under" guest Philip Seamark, from across the "ditch" (as we both call it) in New Zealand.

Phil is a member of the Fabric Customer Advisory Team and works as a DAX and Data modelling specialist.

He gets involved when enterprise customers need deeper technical support.

In this show, I discuss Phil's thoughts on Direct Lake which is one of the very new options that came with Microsoft Fabric. It adds another mode to Power BI, in addition to Import and Direct Query modes that have been there previously.

You'll find this show, along with the previous shows at: https://podcast.fabricdownunder.com

Fix: Getting R and Python to actually work on SQL Server 2022

One of my more surprisingly popular blog posts in the past talked about Machine Learning: Testing your installation of R and Python in SQL Server 2017. The problem is that SQL Server 2022 changed things.

Now the SQL Server team has published an article on how to make it work.  What I've been finding though, is that people are struggling to follow that article. And more concerning, people using named instances of SQL Server (like SQLEXPRESS) couldn't get it to work at all, no matter how much they tried.

It took me a while to work out what was needed, so I figured it was time to share it with others. I've also submitted a few pull requests to get that page updated. So, by the time you read this, it might already be updated.

What you need to do for R and Python on SQL Server 2022

You need to start by installing SQL Server and making sure you choose at least BOTH the Database Engine Services and the Maching Learning Services and Language.

Unlike earlier versions, the R and Python libraries are not installed or configured for you. If you try to execute R code at this point, even after configuring SQL Server, you will see this type of error:

The error for Python is similar:

I've included both in case someone is searching for this. At least it tries to link to a solution, even though the link is still a docs link not a Learn link. (I'll put in a pull request for that too)

Getting R to work

Let's start by getting R to work.

You need to download and install the latest R 4.2. I got this from https://cran.r-project.org/bin/windows/base/old/4.2.3/ and I installed it with all default options.

R download

You then need to install some standard R dependencies. You do this by running RTerm. I found it here:

Location of RTerm

I right-clicked and ran as administrator, just in case. Then you execute this code step one row at a time, making sure that each installs ok. Note that on the first one you install, you'll probably be prompted for a CRAN mirror site. Pick one near you.

Then there are some Microsoft packages that need to be installed.  Execute these statements one at a time, making sure that each works. You can ignore warnings at this point. (And note this is two lines even though they show line-wrapped here)

Now the first challenge

The next step is where things went wrong. You need to find a program called RegisterRext.exe. For me, it was in this folder: C:\Program Files\R\R-4.2.3\library\RevoScaleR\rxLibs\x64

RegisterRext location

You open a command prompt (cmd.exe) as an administrator. Then you need to configure the instance of SQL Server with the version of R. The article says you do this by executing:

That works fine if you have a default instance of SQL Server and if it's called MSSQLSERVER (the default name). Not so much if you have a named instance. I was trying this on a machine with a SQLEXPRESS named instance as I'd heard this was an issue.

The article didn't give you any idea or example for a named instance. So I tried all the things I normally would for a named instance, including not knowing if I needed to escape the backslash:

  • ".\SQLEXPRESS"
  • "MACHINENAME\SQLEXPRESS"
  • ".\\SQLEXPRESS"
  • "MACHINENAME\\SQLEXPRESS"

and so on. I was about to give up when I finally just tried "SQLEXPRESS". And that worked ! I understand why people are confused. Nowhere else in SQL Server land do you refer to a named instance that way.

So the command needed in that case was just:

Configure and test R in SQL Server

Then it's time to test the SQL Server end.  You need to start by executing:

And you then need to restart the SQL Server service.

Next you can see if it's working. Execute the following in SQL Server:

That should show you 1 as a response.

But no cigar yet

All good so far. So, then I tried an actual R query, as suggested in the article:

Well, I didn't get a great response with that. It should have worked at that point but instead, after quite a while, it returned this:

OK, that looked a bit scary. I started spending ages trying to work out what on earth was wrong with sqlsatellite.dll.

And the weird thing? After a while, and a few more attempts, it just started working. No clue as to why it didn't just work the first time. But I've now seen that on two machines, so it's no coincidence. Be patient. Eventually it returned:

More complex R query

I then was able to execute something more complex:

And R is working

And then R is working !

Python

Then it was time to see how I went with Python. Once again, not as smooth sailing as I'd hoped.

Again, you need to download Python installer. But the link for Python 3.1 that's included in the article goes to a page that now tells you that it's too old to have binaries available. (For Python apparently that means less than a year old). I started to see if it would work with Python 3.11 (the earliest with binaries) but eventually scared myself off, as I knew that wasn't going to be good.

I searched for "Python 3.10 for Windows" and ended up with a link at the same site, and it did have binaries. That link was:

https://www.python.org/downloads/release/python-3100/

I downloaded it, but importantly, I chose a custom install. (Very easy to miss on the first page). The main thing you need to change is the install folder. It will want to install it in one of your private folders by default, but instead, choose C:\Program Files\Python310 as the install folder. Near the end of the install I also chose hte option to Disable the path length limit. I doubt that's needed here but I've run into it before so I decided to choose it.

Next in an admin command window (same one as before if it's still open), you need to step through these three commands:

That took a little while but was all good.

Another challenge

You then need to set some permissions on folders. The instructions say to execute this command:

Again though, that assumes that the service that's running your SQL Server Launchpad service is "NT Service\MSSQLLAUNCHPAD".

Mine wasn't.

You can find what it is (if you don't remember from when you installed it), by opening (just Windows start/run) services.msc and looking for the service.

services.msc output

Being a standard SQLEXPRESS installation, mine was actually "NT Service\MSSQLLaunchpad$SQLEXPRESS", so instead of the above command, I needed to execute:

That then worked ok. We also need to set one more permission (and it's unrelated to your instances):

We're not done yet

At this point, we're back to needing to use a different version of RegisterRext.exe to configure the SQL Server instance for the version of Python.

The instructions say to execute:

Once again, that's not going to work for my SQLEXPRESS named instance, so I needed to execute this instead:

Now it's time to test Python in SQL Server

Because you've already enabled external scripts in SQL Server, if you've previously installed R, you don't need to enable them again.

Instead, I tested Python:

And that worked:

Simple Python test

As did a more complex query:

More complex Python query

And we're finally done !

I hope that helps someone else get started. (I've also pushed pull requests to the Learn site to get some of this updated. Hopefully it will be by the time you read this).

 

 

 

SQL: Understanding Change Data Capture for Azure SQL Database – Part 5 – Accessing CDC from another database

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.

In an earlier post, I talked about how you can access the list of logins in the master database from within an Azure SQL Database. I'd seen far too many people who were told that wasn't possible.

In that post, I showed how to set up a database scoped credential, an external data source, and an external table, so I won't repeat that detail here. Please refer to that post for specifics.

With Change Data Capture (CDC), what I'm often needing to do is this:

Two databases with arrow linking them

I have a source database (probably an OLTP database) and have enabled CDC on some tables. But I want to drag the data changes across to a staging area in the data warehouse database.

I wish the product team had made this easy but, at present, it isn't.

The Challenge

I can create an external table in the data warehouse database, that points to a table or a view in the source database. What I want to access though, is a table-valued function like the one I described in part 3:

cdc.fn_cdc_get_net_changes_dbo_NewEmployees

But it requires parameters, most importantly a @FromLSN and a @ToLSN. But I can't pass parameters to an external table. (I wish I could).

So how do I create a way to read from these functions, when I can't pass parameters, and I don't want all the data since the beginning of time?

There are two ways to tackle this.

Approach 1

In the past, here's what I've been doing instead:

Step 1: Local table

I create a local table, let's call it DataLoad.SourceConfigurations, and it has at least two columns for FromLSN and ToLSN. (Note these will be binary columns).

Step 2: External Table in the Source Database

I then create an external table in the source database, that points to that table that I just created in the data warehouse database. Let's call it DW.SourceConfigurations.

Step 3: View in the Source Database

I next create a view in the source database that reads from the DW.SourceConfigurations external table, to get the FromLSN and ToLSN values and uses them to call the CDC table-valued function. Let's call the view cdc.get_required_changes. I'll leave it in the cdc schema along with the standard functions. (Obviously I could have put it elsewhere).

The beauty of this, is that I can now control what's returned by this cdc.get_required_changes view, by updating the values in the DataLoad.SourceConfigurations table in the data warehouse database.

Step 4: External Table in the Data Warehouse Database

The final setup step is then to create an external table, let's call it DataLoad.RequiredChanges, in the data warehouse database, that points to the cdc.get_required_changes view in the source database.

End Result

Once we've done this, we have a workable solution. I can process changes from the source database in the data warehouse database almost as though they were local CDC changes.

I just put the LSN values that I want into DataLoad.SourceConfigurations, and then read rows from DataLoad.RequiredChanges.

Approach 2

A more recent, and simpler approach is to wrap the call to the CDC functions in a stored procedure.

We can now call a remote stored procedure by using

sp_execute_remote

This option appeared well after the other options were available. We'll still need a local table to store where we are up to, but we can just pass the values directly to the stored procedure, which can make the SELECT statement against the CDC function.

I hope this helps someone get started with this trickier aspect of using CDC in Azure SQL Database.

  1. Why use Change Data Capture for Azure SQL Database?
  2. How Change Data Capture works in Azure SQL Database
  3. Enabling and using Change Data Capture in Azure SQL Database
  4. Change Data Capture and Azure SQL Database Service Level Objectives
  5. Accessing Change Data Capture Data from Another Azure SQL Database

SQL Server Locking, Blocking, and Deadlocks for Developers and DBAs Course Released

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.

Why this course?

I often get called to customer sites and they tell me that they have blocking issues. But what they call blocking issues isn't always accurate. They might mean locks that are held for a long time, or they might mean deadlocks. These are really common issues for customers.

  • Have you been working with SQL Server or Azure SQL Database and having issues with blocking and deadlocks?
  • Would you like to really understand how SQL Server manages transactions and locks?
  • Not sure what isolation levels are about, and which ones you should be using?
  • Don't know your app locks from your version stores?
  • Do you know the basics but think it's time to extend your knowledge?
  • You want to learn from an expert.

If any of these apply to you, this course is for you! And as well as detailed instruction, the course also offers optional practical exercises and quizzes to reinforce your learning.

SQL Server Locking, Blocking and Deadlocks for Developers and DBAs (sqldownunder.com)

SQL Down Under show 84 with guest Rob Sewell discussing SQL Server command line utilities is now published

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.

In the show, we discuss command line utilities for SQL Server, including the latest go-sqlcmd offering. We also include a shout out to the upcoming SQL Bits conference.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

SQL: Even more details on finding rows that have changed using HASHBYTES and FOR JSON PATH

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:

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.

The Challenge

The challenge with this, is that you might have a column in the data that should NOT be checked. Perhaps you've stored a column called HashedData in the table and it should not be included in the hash calculations. Or perhaps there's an identity column that you really need to excluded.

The problem with the code above is that there wasn't an easy way to exclude the column.

Making a Wish

What I really wish that SQL Server had is a way to exclude a specific column or columns from a SELECT list.

When you are writing utility code, it would be incredibly useful to be able to say just:

I'm not a big Snowflake fan but it's an example of something that it does better than SQL Server in T-SQL.

If I had that, I could have just excluded it in the CROSS APPLY above.

JSON_MODIFY to the Rescue

But there is a way around this. Since SQL Server 2016, we've had JSON_MODIFY.

And I can use that to modify the data before hashing it. So to exclude a column called HashValue, I can write:

With JSON_MODIFY, we can choose a JSON property to remove by setting it to NULL.

This makes it far easier to write generic code, without the need to list all the columns (as you would with CONCAT or CONCAT_WS), and without the need for dynamic SQL.

SQL: Understanding Change Data Capture for Azure SQL Database – Part 4 – Azure SQL Database Service Level Objectives

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.

I was left saying "please say it ain't so!".

Testing

I thought it was time to find out what actually happens when you try it.

I started by creating a database with the lowest SLO (Basic). Now I know these don't hold enough data to really even be very meaningful, but I wanted to know what would happen.

I didn't get far.

I created the table from the scripts in part 3 of this series, and then tried to do the first enable of CDC.

To say that's disappointing is an understatement. But i tmakes it clear that Basic, S0, S1, and S2 aren't going to let you enable it.

What about Scaling?

Then I wondered what would happen if I enabled it on an S3, and then tried to scale down.

So I scaled the DB to S3 ok, and enabled CDC as in part 3. All worked as expected.

I then scaled the DB back to S2 using the Azure Portal. And alas, that failed too:

I suppose I'm not surprised, but I had hoped it might at least have automatically disabled CDC if it really had to.

It's a Mistake

I think these licensing restrictions for CDC in Azure SQL DB are a mistake.

Unhappy woman
Unsplash image from Getty

Back in SQL Server 2016 SP1, I was one of the main instigators to get the product team to make sure that the programming surface of the database worked across all the tiers (Enterprise, Standard, Developer, Express) wherever possible.

This restriction on CDC goes directly against that ethos. It harks back again to large single database thinking, not cloudy thinking.

I understand that it's probably needed for performance in production scenarios, but what I'm always trying to tell the team is that production scenarios aren't the only scenarios.

Developers also need to write code. We should let them write and test functionality. It can be clear to them that it won't work the same as on a production instance, but that's no different to many other aspects of the system. A Basic SLO database won't run a complex query over a lot of data either, but we don't expect it to.

But it's important to let them write their code and make sure it basically functions, no matter what level of SLO they're working with. If you require them all to have an expensive database, just to test code or concepts, you greatly limit how many developers will use the features. 

Clouds
Unsplash image by Dallas Reedy

Having those types of restrictions also restricts the cloudiness of the solution, in that you couldn't scale up/down across a range of SLOs. We often take S7 or S8 databases and temporarily "park" them at S2 while not in use. This type of restriction kills those options as well, and makes the overall solution less cloudy.

And One More Part to This Series

That's service level objectives for the use of CDC in Azure SQL DB. We'll move to accessing CDC data from another Azure SQL DB next.

  1. Why use Change Data Capture for Azure SQL Database?
  2. How Change Data Capture works in Azure SQL Database
  3. Enabling and using Change Data Capture in Azure SQL Database
  4. Change Data Capture and Azure SQL Database Service Level Objectives
  5. Accessing Change Data Capture Data from Another Azure SQL Database

 

SQL Question: ROUND() didn't go to the same school I did?

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:

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.

Rounding

There isn't just one "correct" way to do rounding. Here is info on it: https://en.wikipedia.org/wiki/Rounding

The T-SQL ROUND() function does apply "standard" mathematical rounding. Values from 5 and up in the next digit go to the next value, less than 5 goes down. So you'd expect 94.55 to be 94.6 not 94.5.

It's worth noting that not all languages do this. Some languages implement Bankers' Rounding. This was designed to avoid the skew that you can get if you have a whole lot of .5 values. For example, 2.5 + 3.5 + 4.5 would be 3 + 4 + 5 = 12 if you rounded them all to integers first. With Bankers' rounding, it alternates. 0.5 goes to 0, 1.5 goes to 2, 2.5 also goes to 2, 3.5 goes to 4, etc. And so the 2.5 + 3.5 + 4.5 would be 2 + 4 + 4 which is 10, and closer to the underlying total of 10.5.

Older versions of VB, etc. used Banker's Rounding, but that's not what the T-SQL ROUND() function does.

So what's up with ROUND() ?

In that case, what's the problem with his original query?

The issue is data types. He didn't provide decimal values to round, he provided strings. That means they have to go to another data type before they are rounded. Which one?

If I execute this code, you might see the problem:

as it returns this:

Note that the value is a float. And as I've talked about in this blog many times, floats are evil for business applications, and a common mistake.

To see the difference, instead of using the implicit conversion, try this:

And that returns this output:

Note that the rounded value is the expected value, and the output data type is the expected decimal type.

Finally, also keep in mind what when SQL Server Management Studio shows you a float value, it also rounds it before it shows it to you. So a value like 9.9999999999999999 might appear as 10.0 but it's not 10.0.

Hope this helps someone.