SQL Friday #124 - Docker for SQL Server Developer
Magnus Ahlkvist
SQL Server Specialist @ Transmokopter SQL AB | Microsoft Data Platform MVP
SQL Friday episode 124, but first edition of the newsletter SQL Friday.
Content
Newsletter introduction
I have had different ways of introducing the upcoming speaker for SQL Friday - Twitter, Bluesky, Facebook, posts on LinkedIn. Last week I wrote a LinkedIn article, and after that I got a new option to create a newsletter. So I'm trying that out as the way to announc SQL Friday news (next week's speaker etc) and also to spread some SQL-tips that I learned or was reminded about the past work-week.
SQL Friday #124 - Docker for SQL Server Developer
I'm very happy to announce one of the legends of the SQL Server Community, Klaus Aschenbrenner, as next week's SQL Friday speaker. Klaus was one of the first to publish short SQL Server related videos on YouTube, the most famous perhaps was about TempDB, which Klaus called "SQL Server's public toilet", and therefore Klaus shot the video in a public restroom.
As always, SQL Friday takes place online, at noon Central European Time
Join the meetup group for SQL Friday: SQL Friday | Meetup
Subscribe to the YouTube channel: TRANSMOKOPTER SQL AB - YouTube
About the presentation
Docker, Docker, Docker! Everyone talks about Docker! But how can you use Docker in combination with SQL Server? In this demo heavy session I will show you my approach how to use Docker to work in a Mac-based ecosystem with SQL Server 2019 without any dependencies to Windows. After attending this session you will have a better understanding about Docker, and the various use-cases it supports in combination with SQL Server.
About the speaker
Klaus Aschenbrenner provides with his company SQLpassion SQL Server consulting & training services across Europe. Klaus works with SQL Server for more than 20 years. Klaus has also written the book Pro SQL Server 2008. In his spare time he loves to do low-level OS development coding in x64 and ARM assembly.
SQL-tip of the week - DBCC CLONEDATABASE works in myserious ways
This week, I have used DBCC CLONEDATABASE quite a lot and I want to share some pitfalls and workarounds. For those who don't know - DBCC CLONEDATABASE is a T-SQL command used to create an empty clone of an existing database. There are options to include or not include things like Query Store data etc. It's an awesome way for troubleshooting, and as I've done this past week, to create an empty database with the full schema of an existing database to fill it with a small subset of data from the original database.
领英推荐
Pitfall 1 - DBCC CLONEDATABASE fails when Model database contains object-id's that are in the source database.
If there is an object in model database with the same object-id as an object in the original database, DBCC CLONEDATABASE sometimes fail, depending on CU-level and what type of object it is. I used SQL Server 2022 RTM version, and then DBCC CLONEDATABASE failed when ledger-tables in model had the same object-id as one of the tables in my source database. The workaround for that was to upgrade SQL Server to CU9. Looking at the list of bugfixes contained in CUs, it looks like CU1 would be enough to overcome this.
So, if you encounter error messages that there's a primary key violation in sysschobjs or similar, then this is probably the case. What you can do to identify such objects, run this query
SELECT
o.name as SourceDBObject,
o.object_id,
o2.name as ModelDBObject
FROM sys.objects o
INNER JOIN model.sys.objects o2
ON o.object_id = o2.object_id and o.is_ms_shipped = 0
So, that's how to identify the problem. And sometimes, installing a newer CU works. But not always (and not always with the desired outcome as I will show below). When the object is not a table, but, as in my case, a constraint of a user defined table type, it just won't work, even with the latest CU. In such case, what I needed to do was to drop and recreate the user defined table type in the source database. And as you may or may not know, if that user defined table type is a parameter to a stored procedure, that stored procedure has to be dropped or altered so that it's no longer depending on this table type.
Pitfall 2 - Some objects in the clone database can't be accessed
So, if you overcome the first hurdle, and you're finally able to create the clone database, chances are you step into this pitfall. One or more of the objects in your clone databasen can't be touched. When you try to query it, with an insert or with just a simple select, you get this very nasty error message:
Msg 596, Level 21, State 1
Cannot continue the execution because the session is in the kill state
Msg 0, Level 20, State 0
A severe error occured on the current command. The results, if any, should be discarded
That's not very nice of SQL Server, is it? It turns out, one of the tables in my source database has the same object-id as a new (for SQL Server 2022) table in model - a ledger-internal table. And the ledger-internal table and the table in my cloned database both exist. So when I try to access the user table, my session gets terminated.
The first workaround I tried was to drop and recreate the table in the clone, so taht it would get new object-id. But then I instead got this error message.
Msg 37386, Level 16, State 1
Cannot drop object '<my table name>' because it is a ledger history table or a ledger view
My workaround this time was to, in the original database, rename the original table name, create a new identical table and copy the data over to the new table. This is a rabbit hole, as the table has few or many constraints etc. And if the original database is a live production database, this is simply not an option.
Where are we now
It seems as though the ledger-tables causes still causes problems for DBCC CLONEDATABASE. Other objects with object-id collision between model database and the source database seems to be handled well by CLONEDATABASE. I don't know if this type of error can occur in databases created on SQL Server 2022, but I doubt it, because in those databases, the ledger tables are already in the original database and therefore those object-ids are occupied already. In my case, I'm working with databases that has lived since SQL Server 2005-days and upgraded several times.
Going back to the query to identify object-id collision, if you have collision on object-id with ledger-tables, you're in trouble. If the collision is with other types of table, you're probably safe.
Final words
Ever since the release of DBCC CLONEDATABASE in SQL Server 2016 SP1, there has been problems with it. The problems were fixed pretty fast once they were reported, but new problems arise with new versions of SQL Server, introducing new objects with is_ms_shipped=1 that introduces new weird behaviour. Some workarounds exist, but they may or may not work for you (and me). DBCC CLONEDATABASE is still a wonderful tool, I just wish it wouldn't have these reoccuring problems.