As promised in my prior blog post, we’ll talk now about direct seeding feature and backups. We found an interesting LIMIT_CONCURRENT_BACKUPS value for current state of the hadr_automatic_seeding_state_transition event. As said last time, this value is meaningful by itself.
So let’s try to perform a backup while the database is concerned by an active seeding session. In fact, my backup was blocked (as I supposed) and here what I found from the sys.dm_tran_locks and sys.dm_exec_requests DMVs.
SELECT r.command, r.wait_type, r.wait_resource, DB_NAME(tl.resource_database_id) as [database_name], tl.resource_type, tl.resource_subtype, tl.request_mode, tl.request_type, tl.request_status, r.session_id as blocked_session_id, r.blocking_session_id FROM sys.dm_tran_locks as tl join sys.dm_exec_requests as r on tl.request_session_id = r.session_id WHERE tl.request_session_id = <concerned session>
- The blocked session (my backup)
- Blocking session (my direct seeding session)
In short, my backup session is waiting on database object with a LCK_M_U lock type that is already held by my direct seeding session. But the interesting part is that adding a database with direct seeding mode seems to be similar to performing a backup operation from a locking perspective. We also recognize characteristic wait types that occur during a backup operation (ASYNC_IO_COMPLETION, BACKUPBUFFER). So direct seeding as a streaming backup? Well that sounds good!
Well, let’s continue with this other query
SELECT r.command, r.wait_type, r.wait_resource, r.scheduler_id from sys.dm_exec_requests as r join sys.dm_os_schedulers as s on s.scheduler_id = r.scheduler_id where r.session_id = 82 AND s.status = 'VISIBLE ONLINE'
Do you notice the command text related to the direct seeding session? VDI_CLIENT_WORKER seems to indicate that SQL Server is using the virtual device interface internally during the seeding operation (VDI prefix as Virtual Device Interface). If SQL Server uses backup stream and VDI interface internally during seeding operation, it may explain why it has to limit concurrent backups in our case.
How to check if SQL Server uses VDI during direct seeding operation? Well, we know that using VDI implies to use sqlvdi.dll. So my first idea consisted in checking if the corresponding dll is loaded as module used by the sqlservr.exe process and the tasklist tool is a good candidate to answer to our need.
The above picture seems to confirm my assumption but this test is not perfect. Indeed, we may also claim that anything else (excluding the seeding operation itself) could have trigger the load of the sqlvdi.dll. So my second idea was to capture the stack dump from the windbg tool while running the seeding session and here what I was able to see:
We may recognize CHadrBuildSourceStateLimitConcurrentBackups (similar to value LIMIT_CONCURRENT_BACKUPS value from the hadr_automatic_seeding_state_transition event) what is a parameter to the function CallFunctAndResumeBuildTask(). Let’s also notice the use of sqlmin!CHadrBuildSourceStateSeeding::CreateVdiSession function that seems to confirm that SQL Server is using VDI session during seeding process.
Ok after this funny test, let’s back to something more important to keep in mind: we know that direct seeding will limit concurrent backups. Thus it will imply than our existing RPO may be impacted especially if the seeding operation takes a very long time.
Happy database seeding!!