SQL: Why use Change Data Capture for Azure SQL Database?

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).

There are many advantages to replication, including the lack of impact on the source system, however Azure SQL DB can't currently be a publisher, so it doesn't help here.

And other forms of replication aren't really useful here, or an available option. So if the source DB is an Azure SQL DB, we need to find something else.

Azure SQL Data Sync

Azure SQL Data Sync is an odd technology. It basically grew out of Merge Replication based ideas. It's not built on Merge Replication, but it's very similar in concept. It was in a preview state so long, and the team had so long since stopped posting information about it, that most of us never thought it would ever reach GA.

You create a setup similar to this:

The sync metadata lives in a DB in Azure, and a copy of the DB that you want to sync is created as an Azure SQL DB. The Azure Data Sync engine then synchronizes the data between the HUB and the other DBs. If any of the DBs are on-premises, then an on-premises agent does the work.

Azure Data Sync (like Merge Replication) is trigger-based. Triggers are used to capture the changes ready for synchronization.

I wasn't a fan or Merge, and I can't say I'm a great fan of Azure SQL Data Sync. While it's conceptually simple, you would not want to use it for anything except very low volume applications.

Change Tracking

Change Tracking is another technology that's come directly from SQL Server land. When it's enabled, a set of change tracking tables are created. As data is changed in the tables of interest, changes are recorded in the change tracking tables.

One positive aspect of Change Tracking is that it isn't based on triggers and it outperforms trigger-based solutions. There are two downsides:

  • The changes are written synchronously, and in the context of the transaction that writes the change to the tracked table. This can impact the performance of the changes to the tracked table i.e. usually two writes are happening for each one that would have happened.
  • You don't get to see all the changes, and not in the order that they happened. Change Tracking lets you know which rows have changed, based upon the table's primary key. You can also ask to have a summary of which columns were changed). This can be a challenge for dealing with referential integrity, and other issues.

Queues (and Service Broker)

Another interesting option is to write to a queue. With an on-premises SQL Server, we can use Service Broker. If you haven't seen Service Broker, it's a transacted queue that lives inside the database. (To learn about this, look here).

With SQL CLR code or with External Activation for Service Broker, we could write to other types of queue like RabbitMQ.

At the current time, Azure SQL Database doesn't currently support writing to external queues. However, I do expect to see this change, as so many people have voted to have this capability added.

Change Data Capture

Change Data Capture (CDC) is another technology direct from SQL Server land. CDC is based on reading changes from a database's transaction log.

When you use it with SQL Server, it shares the same transaction log reader that Transactional Replication (TR) does. If you enable either CDC or TR, a log reader is started. If you have both enabled, they use a single log reader.

A key upside of using a log reader is that it doesn't slow down the initial updates to the target table. The changes are read asynchronously, separately.

Until recently, though, you could not use CDC with Azure SQL Database. The log reader agent ran from within SQL Server Agent, and with Azure SQL Database, you didn't have a SQL Server Agent.

The product team have recently done the work to make CDC work with Azure SQL Database.  It is an interesting option for extracting changes from a database, so this is the first blog post in a series of posts about using CDC with Azure SQL Database. Links to other posts will be added here as they are available:

  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

 

 

ADF: Use MSIs not SQL Logins whenever possible

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.
 
Each ADF exposes an Azure-based identity on its property page. Here is the main menu for my BeanPerfection data factory:
Data factory properties page
You might have guessed you should go to the Managed identities menu option, but that is for User Managed identities. I'll write more about them another day. The identity we're interested in is a System Managed Identity.
On this properties page, it's actually called the Managed Identity Application ID. Like all other types of Service Principal, Managed Service Identities have an ApplicationID.

Creating a user in Azure SQL Database

I create a user in an Azure SQL Database with the FROM EXTERNAL PROVIDER option. This says that Azure SQL isn't performing the authentication.

The user name is the full name of the ADF. In Azure SQL Database, we can just use the name of the ADF instead of the identity. It will do that for us.

I always make this data factory name lower-case. Once the user is created, I add it to whatever role in the database makes sense. In my example above, I used the role name datafactory_access but there's nothing special about that name.

You need to decide which role to add it to based upon the tasks that the data factory needs to perform. While it's temptingly easy to just add it to db_owner, try to resist that. If in doubt, create a role that's used for data factory access and grant it permissions as they are needed.

Must execute using an AAD-Based Connection

If you just try to execute the statements above, you might find that you get an error message saying:

Msg 33159, Level 16, State 1, Line 8
Principal 'beanperfectiondatafactory' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

You cannot create any type of Azure AD-based user in an Azure SQL Database, if your connection was authenticated as a SQL login. You must use a connection that was itself made using Azure-AD authentication.

I find that the easiest way to do that, is to make sure I have an Azure Activity Directory Admin assigned for my Azure SQL Server, and then just execute the code right in the Azure Portal. I use the Query Editor tab in the main menu for the Azure SQL Database and connect as that administrator.

 

SQL: Fix: The parameters supplied for the procedure "sp_set_firewall_rule" are not valid.

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.

I’ve not seen a procedure before that wants a Unicode string that won’t accept an ASCII string. For example, this works just fine:

Apparently strict data type checking has always been a feature of extended stored procedures and this one checks specifically for a Unicode string. I really don't use extended stored procedures much anyway.

What threw me as well is that I couldn't find it in the list of system stored procedures. It's not there because it's actually an extended stored procedure. These used to mostly have xp prefixes, not sp prefixes, and are a good example of why I don't love using prefixes like that.

I really wish though, that this procedure had a better error message. While the current one is strictly correct, it is not actually all that helpful.

 

New Online Course Released: Advanced T-SQL for Developers and DBAs

I'm really pleased to let you know that our latest online on-demand course is now released:
 
 
To celebrate the release, you can get 25% off the pricing until Aug 14th by using coupon code ATSRELEASE
 
We've had so many requests from customers to bring this course to our online platform. It was always one of our most popular in-person courses, and it's now released and fully updated.
 
The course includes the instruction plus quizzes and the same hands-on labs that we use in the in-person courses.
We've made a big effort with this course to make it really easy for you to do the labs. The labs only require you to have a fairly recent version of  SQL Server Management Studio (SSMS) installed to complete them. You don't need to install anything else. We've provided the required databases online ready for you to connect to.
 
Not on the latest version of SQL Server? Not a problem either. Unlike most other courses, our courses always cover at least all the supported versions of SQL Server and show you what's changed between versions.
 
I hope you enjoy it.

Reliably dropping a SQL Server database if it exists

I often need to write scripts that drop and recreate databases. The hard part of that has always been reliably dropping a database if it already exists. And no, you wouldn't think that would be hard, but it is.

Built in Command

T-SQL has a built-in command for this.

You'd hope that would work, but it doesn't.  I wish it did. The problem is that it will fail if anyone is connected to the DB. And to check if anyone is attached, you first need to check if the DB exists, so it makes the whole "IF EXISTS" part that was added to this command, completely pointless.

Worse, if you have separate code to kick everyone off first, you always have a chance of a race condition, between when you kick everyone off, and when you execute the command.

Nearly OK

Years back, the Microsoft docs library said to drop a database like this:

This was promising, but unfortunately, it has an issue as well. Because you were in the master database when you issued the ALTER, you don't know that you are the single user. So, periodically, that would fail too.

Best Workaround

Over the last few days, we've had a discussion on an MVP list about how to work around this. Many thanks to Paul White, Erland Sommarskog, and Simon Sabin for contributing to it.

The best outcome I have right now is to use this:

To get the DROP to work properly, you need to execute the ALTER DATABASE from within the target database. That way, you end up being the single user, and even though you then execute a change to master, you hold the required session lock on the DB, and then the drop works as expected.

Because you can't have a USE Sales in the script if the Sales DB doesn't exist, this unfortunately has to be done in dynamic SQL code, where it is only executed if the DB does exit.

The last change to tempdb is just protection, if I have a script that then wants to create the DB and change to using it. If that goes wrong, I want to end up creating things in tempdb, not somewhere else like master.

What I wanted

What I've been asking for, and for a very long time, is this:

The ROLLBACK IMMEDIATE needs to be on the DROP DATABASE command, not on a separate ALTER command. Hopefully one day we'll get this.

SQL Interview: #14: Set operations using EXCEPT

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Medium

Question:

UNION and UNION ALL are commonly used to combine two sets of rows into a single set of rows.

EXCEPT is another set operator.

Can you explain what it does?

Answer:

EXCEPT is used to remove any rows in the first set of rows, if the same rows appear in the second set.

For example, in the code below:

The query returns all the Trading Names for customers unless a supplier also has that same name.

In other database engines (e.g. Oracle), this operator is called MINUS.

SQL: Password complexity rules for Azure SQL

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:

Identify the right Azure SQL Database SKU for your on-premises database (Data Migration Assistant) – SQL Server | Microsoft Docs

To avoid you reading the whole article, at the time of writing, these were the rules for Azure SQL Database:

  • Your password must be at least 8 characters in length and no more than 128 characters in length.
  • Your password must contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, etc.).
  • Your password cannot contain all or part of the login name. (Part of a login name is defined as three or more consecutive alphanumeric characters.)

Slightly more confusing is that the article says it's talking about the server admin password, but it also appears that this same list of restrictions applies to all logon/user passwords as well.

Azure SQL Managed Instance is exactly the same except it requires the password to be at least 16 characters in length.

I hope that helps someone, including myself when I next go looking for this.

 

SQL Interview: #13: Impact of optimize for adhoc workloads

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Server Tuning
Level: Advanced

Question:

SQL Server 2008 introduced a server option for optimize for adhoc workloads.

Can you explain the problem that it is designed to solve, what most commonly causes the problem, and what impact this option has when you enable it?

Answer:

On a SQL Server, you can end up with a large amount of memory being taken up by query plans that will likely never be used again.

There are two primary causes of this situation:

  • The less common cause is that you have a large number of adhoc queries being executed as one-off queries.
  • The most common cause is that you are using a framework like LINQ that does not handle data typing properly, and causes a large number of query plans to be created for each query. You might also be using lower-level frameworks like ADO.NET incorrectly.

As an example, command objects in ADO.NET have a parameters collection. If you add parameters using the AddWithValue() method, you specify the parameter name, and the value, but you do not specify the data type. The problem with this is that the framework then tries to work out what the data type is, from the value.

This means that if you pass a string like 'Hello', then it might guess nvarchar(5) but if you pass a string like 'Hello There', it might guess nvarchar(11).

The lengths of data types are part of the signature for each query plan. This means that you can easily end up with different query plans for every combination of every length of string that has ever been passed to the query. We call this Plan Cache Pollution.

The correct way to fix this is to avoid ever using methods like AddWithValue() and instead using a method where you specify the data type. The challenge here is that many frameworks like LINQ have this behaviour baked in, and the developer cannot change it.

When the optimize for adhoc workloads option has been set, the first time a query is seen, the hash for the query is stored (so it can be remembered) but the query plan is not stored. The next time the same query is seen, the compiled plan is then stored for reuse.

This avoids the plan cache becoming littered with one-off query plans.

Another option taken by some frameworks, is to just use varchar(4000) or nvarchar(4000), or worse nvarchar(max) for all strings. That will of course avoid the plan cache pollution, but it's likely to then cause issues with memory grants in poor quality plans.

 

T-SQL 101: #96 Choosing from alternatives with IIF in SQL Server T-SQL

In my last T-SQL 101 post, I described the CASE statement. Until SQL Server 2012, that was the only real option that we had for choosing between alternate values. In SQL Server 2012, Microsoft gave us another option with the IIF function.

The IF function is very similar to the IF function in Microsoft Excel. It takes three parameters:

  • A boolean value to check (normally this is an expression)
  • A value that will be returned if the first parameter is true.
  • A value that will be returned if the first parameter is false.

In the main image above, you can see the equivalent CASE statement and the simplified IF function equivalent.

Nesting

Again similar to how IF works in Excel, you can nest IF functions. For example, you can write:

In this case, we're saying that if the Size is Large, then 12 will be returned. But otherwise, if the Width is 13, then 0 will be returned. In all other cases, -12 will be returned.

Returned Data Type

The function looks at the data type of the second and third parameters (i.e. the values returned for true and false), and chooses the data type with the highest precedence. For example, if the second parameter is an int, and the third parameter is a bigint, then a bigint will be returned no matter which value is chosen.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: Weekday Across Years in SQL Server T-SQL

Another request that I received a while back, for a new function to our free SDU Tools for developers and DBAs, was to be able to find the day of the week, for the same day and month, over a range of years. A simple example would be to find what day Christmas will be each year for the next ten years. So we've added a new function WeekdayAcrossYears.

It takes four parameters:

@DayNumber int – day number in the target month
@MonthNumber int – target month number
@FromYear int – starting year
@ToYear int – ending year

The function returns a rowset with YearNumber and WeekDay (in English).

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.