I’ve been writing and teaching about SQL Server for more than 20 years, and I’ve explained certain product features hundreds of times. So a part of me thinks that “everyone should know this by now” and I’ll be shocked and surprised when I see a comment on a blog or a question on a forum reflecting lack of particular knowledge.
One basic example is the fact that SQL Server backups are completely online operations and have been since the very first version. You can make a backup while the SQL Server system is in use, and SQL Server will make sure the data is consistent when and if you ever restore the backed up data. So when I see a comment in a forum indicating a need to take the SQL Server service down before a backup, I wonder if the writer has ever read any SQL Server documentation!
I have to remind myself that people come to SQL Server on all kinds of paths, with all kinds of backgrounds. Some are thrust into the role of DBA without any formal training at all and expected to get to work immediately. They might not know where to find any documentation, and figuring out the best blogs and forums can take some time.
Another very basic piece of information is about the SIMPLE recovery model. I have seen countless comments from people implying that in SIMPLE recovery, your transaction log can’t fill up, and even worse, that there’s NO LOGGING in SIMPLE recovery. Neither of these suppositions is true, but there still seems to be plenty of people who think at least one of these behaviors is true. How many white papers, blog posts, conference presentations, and “Free SQL Server Tips” email newsletters will it take until everyone knows this? Last year, I gave a talk for the 24 Hours of PASS event called “What’s Simple About SIMPLE Recovery?” The answer is that the only thing that’s simple is your backup strategy because you don’t have to plan for making and storing log backups. But, of course, not everyone using SQL Server listened to that talk.
Another common misconception is that when a CHECKPOINT occurs in a database, it only writes data from committed transactions to disk. In fact, some of Microsoft’s own official courseware for SQL Server administration stated this as a fact, when it’s completely untrue. CHECKPOINT writes all dirty pages to disk, whether the transaction that changed them has committed or not. I was at a meeting at Microsoft a couple of years ago when someone at the meeting reported hearing this myth from a customer, who claimed he had heard it from an official Microsoft trainer, and everyone at the meeting became very concerned. They said they had to figure out how they could “get the word out” and let “everyone” know how CHECKPOINT actually behaves.
But how could they make sure that everyone knows something? Is there one source everyone using SQL Server reads? Or should they we just trust that people who need help will eventually try to find it, and if the first answer they get doesn’t work, they’ll try again? The answers are out there for those who seek them.