As I was going thru the list of possible laptops that I could buy as a backup machine, my youngest son pointed at the latest Microsoft Surface Pro device. He was very happy with his Surface tablet – great graphics, lightweight, HD webcam, touch screen and a detachable keyboard. As he continued to brag about his Surface device, he didn’t notice how quickly I hit the Checkout button on Amazon. The refurbished Dell Latitude 7480 laptop with minimal hardware specifications that I ordered was on its way.
Foundations Help You Build
Thanks to SQL Server MVP and SQLSkills CEO Paul Randal (Twitter | Blog,) the SQL Server transaction log has become one of my favorite topics. Sure, everyone will be talking about the latest and greatest features of the next release of SQL Server. Why not? The latest features get the marketing teams at Microsoft all hyped up. Its what the sales teams will focus on. Just watch the Introducing SQL Server 2019 video on Channel9. You won’t find anybody talking about the SQL Server transaction log. Why? Because it’s not new. It’s not shiny. And shiny things don’t sell.
But just like that refurbished Dell Latitude 7480 laptop that I got, I only needed the bare minimum to start building stuff on top of it. Once I got the laptop, I took out the 4 GB memory modules and plugged mine – allowing it to run 32 GB of memory. I then took out the built-in hard drive and plugged my own M.2 SSD. I felt like Cade Yeager bringing Optimus Prime back to life. Anything is possible.
That’s what the SQL Server transaction log is capable of. Because it contains the history of every action that modified anything in the database, the product team at Microsoft can be very creative with what they can come up with. Here’s a not-so-exhaustive-list of features that the SQL Server transaction log allowed.
- – Write-ahead logging. Because SQL Server will have to first record in the log any change about to be made, before modifying any data
- – DBCC CHECKDB in SQL Server 2000. SQL Server analyzed the transaction log when running DBCC CHECKDB to get a consistent view of the database (the current version of DBCC CHECKDB uses database snapshots)
- – Database Restore and Recovery. Recovery of individual transactions, point-in-time recovery, crash recovery, restoring the entire database, a file, a filegroup and even a page from backup
- – Log Shipping. Allows automated sending of log backups from one database server and restoring on to another database server
- – Replication. Allows copying and distributing data and database objects from one database to another by reading the transaction log records
- – Database Mirroring. Allows for an extra copy of a database on a different database server by copying transaction log records from the source and applying it to the target
- – Availability Group. Allows for multiple copies of a group of databases on multiple database servers similar to database mirroring
Now, you might be thinking, “I think you’re being a bit biased. These all pertain to SQL Server HA/DR.” Well, what about these?
- – Change Data Capture. Change Data Capture (CDC) records insert, update, and delete activity applied to a SQL Server table by describing them in the transaction log
- – Indirect Checkpoints. Allows you to control database checkpoints for faster and more predictable recovery time than automatic checkpoints (yes, this is still related to DR but can also be used as a performance optimization enhancement)
- – Database Recovery Advisor. Still related to DR, this allows you to construct restore plans that implement optimal correct restore sequences
- – Delayed Transaction Durability. Another one related to DR but can also be used as a performance optimization enhancement, this allows you to configure SQL Server to asynchronously (instead of synchronously) write log records to disk
You can also be creative in your own way when working with the SQL Server transaction log. Here are some of the ways that I’ve used it in the past.
- – How To Analyze Database Activity If You Don’t Have A Monitoring Tool
- – How To Forecast Database Disk Capacity If You Don’t Have A Monitoring Tool
- – Data Types and How They Affect HA/DR
That’s how powerful the SQL Server transaction log is. It’s the reason why ApexSQL created a log reader utility.
So, before you focus on the new SQL Server features, take a look at the ones that have been there for a while. Microsoft will continue to build on the features that helped SQL Server become what it is today. And, that’s why I like to learn more about the SQL Server transaction log. It’s one of the foundation features that other features build on top of.
