SQL Flight Deck

SQL Flight Deck is a personal blog. A cockpit view into Microsoft SQL Server

> My LinkedIn

  • Unexpected Command Ordering in Transactional Replication

    The scenario

    Let’s take “Table A” as an example, this table has five columns “A, B, C, D, E”. Table A is replicated to a subscriber via an updatable subscription, allowing for bidirectional data changes – in this case, the type is queued updating.

    Inside a single transaction on the subscriber, the following actions occur:

    1. UPDATE TableA SET A = '', B = '' WHERE PK = 1
    2. An AFTER UPDATE trigger executes:
      • UPDATE TableA SET C = 'I''m a trigger' WHERE PK = 1
    3. UPDATE TableA SET D = '', E = '' WHERE PK = 1

    In total, the same row is successfully updated three times.

    The ordering oddity comes when the queue worker thread is applying those commands on the publisher.

    A profiler trace of the queue worker shows command 2 (originating from the trigger) being attempted first under @execution_mode “QFirstPass”. An update conflict is detected due to command 2 having the “old_msrepl_tran_version” of command 1, not the value of the current row on the publisher and this not matching. As a result, subsequent profiler rows shows the three commands being ran with @execution_mode “QPubWins”.

    Image courtesy of Sonnet 4.6

    Confusion ensues..

    Opus 4.8 suggests the following queue ordering is taking place on the subscriber. This is such that the nested UPDATE of C is queued before the update of columns A & B.

    S1: UPDATE A,B                      -- version stamped V0→V1 as part of the statement
      └─ T_user fires (NFR)
        └─ UPDATE C (S2)                -- version stamped V1→V2
          └─ T_sync(S2)           ENQUEUE C (old=V1) ← written to queue FIRST
      └─ T_sync(S1)               ENQUEUE A,B (old=V0) ← written SECOND
    S3: UPDATE D,E                      -- version stamped V2→V3
      └─ T_sync(S3)               ENQUEUE D,E (old=V2) ← written THIRD

    The execution of T_sync for S1 coming after the nested user trigger wouldn’t conform with Microsoft’s documentation of replication triggers which must be set as the first in ordering and are so by default.

    Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger is the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table is included in a subscription, sp_settriggerorder returns an error.

    The above suggests that the command Is queued in the incorrect order to begin with but what about a scenario where they’re queued in the correct order, but the queue worker thread is obtaining them in the wrong order for processing due to ordering ambiguity?

  • Microsoft Certified: Azure Database Administrator Associate

    I recently passed Microsoft’s Azure Database Administrator Associate exam and would like to share my thoughts for anyone in the same boat, pre or post certification.

    Whilst being an inherent way for me to grow the mental database of all things data, it was primarily an opportunity to prove to myself that I had the necessary skills to digest a wide range of new technologies and technical approaches and to break out of a comfortable skill ringfence for a time.

    The resource I used to prepare

    First up, the resource I think everyone mentions or advises – Microsoft Learn. The structured official course material for DP-300 is a must and provides the general fundamental framework of skills that’re required for the exam. In my opinion, the layout, brevity and clarity of the course content is well done and will be what’s kept most up to date with what they’d like you to know. Each section of the course syllabus is broken into modules, and within each of those is a module assessment. The combination of the module assessments and the cumulative course practice assessment were a good way to validate learning.

    When I had been looking to take this exam a number of months ago, towards the back end of 2025, Microsoft Learn was admittedly the sole resource I was using for preparation. In hindsight, now knowing the style of exam content, this might’ve been a dodged bullet! Whilst the Microsoft Learn content for the course is undeniably good, I would use it more as a starting, supplementary reference point for preparation.

    Revisiting the desire again to take the DP-300 in the last few weeks, I came across a set of course videos that Microsoft had uploaded to YouTube in the December of last year. I took these as a sign… a whopping 100% gain in my previous study resources! 😶‍🌫️ The video material is on-par with the Learn material for me in terms of quality. They’ve had to summarise the syllabus into a format/length without it being a full instructor-led, multi-day marathon. One advantage of the videos compared to the written Learn material is the visual walkthrough of the demos. I’d suspect these would be beneficial for the interactive labs that may be in the exam, though I did not have any to validate that sneaking suspicion.

    Also, don’t forget the new account credits that Azure offer – $200 in credits or 30 days, whichever is first. Use this, or an existing Azure subscription, to explore what’s covered in the demos and familiarise yourself with where settings and configuration live.

    As for third-party services that provide practice exam questions, I’m sure there are numerous that are credible and trustworthy in their question/answer accuracy and would be totally worth the cost but I have not used these before. Research before using – potentially search Reddit for general community feel-good score of one you’re interested in.

    Thoughts on the exam

    My exam consisted of some 47 odd questions with an additional 6 case study questions. Heading into it, I was expecting a series of lab tasks, the existence of which backed up by various anecdotal comments on Reddit, though to my surprise there were none. My belief was that this was such when the Azure lab environment was unavailable, presumably due to maintenance or outage, and that very well could’ve been true. I’d by lying if I didn’t say I was partly relieved.

    Additionally, in associate exams like this, you have access to an embedded web browser that is restricted to the Microsoft Learn domain. This was definitely valuable in confirming a number of answers that I’d marked for review towards the end and in hindsight I’d recommend anyone to be vaguely familiar with the search functionality or documentation layout. There isn’t the time available to be using it on the majority of questions, but where it is, you want to be as efficient as possible.

  • From NULL to WordPress

    Ignore the flight plan in the image above and just be glad that AI isn’t piloting you to your favourite holiday destination. Well, not yet at least…

    In an effort to better my technical proficiency, I thought it was about time to throw together a place to collate and technical write-ups, wins, fails or findings, so here goes.