Goal: map a SQL Server container to ec2 instance using Docker volumes.
I'm interested in how and when the .mdf files for a database should be updating versus how they are actually updating in my environment.
For example, when a user registers in my app, only the database_log.ldf file is updated. My research says this is due to the database recovery settings being set to FULL. To work around this, my next step is likely to create a script to set the database recovery mode to SIMPLE every time a new container starts. Is this the right approach or am I digging myself a hole?
If you know the answer to the above question, you can likely provide guidance on the following sub-question, which is, at what point is the .mdf and .ldf files in the SQL Server container actually mapped to the host volume? Based on this Reddit post, it looks like the contents of the .mdf file are only transferred after the container is shut down?
If this is true, I wouldn't see that the .mdf file in the host volume has actually been updated until the SQL Server container shuts down. Is this valid?
The MDF files are written lazily by the lazy writer and checkpoint processes. When a change is made, only the log file is immediately updated. This is in both SIMPLE and FULL recovery modes.
To understand how write-ahead logging works in relation to the transaction log, it's important for you to know how modified data is written to disk. SQL Server maintains a buffer cache (also called a buffer pool) into which it reads data pages when data must be retrieved. When a page is modified in the buffer cache, it isn't immediately written back to disk; instead, the page is marked as dirty. A data page can have more than one logical write made before it's physically written to disk. For each logical write, a transaction log record is inserted in the log cache that records the modification. The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
This is unrelated to whether you are using containers and docker volumes or any other storage solution.