Microsoft SQL databases have two components: Transaction Logs (.LDF) and Databases (.MDF). Transaction Logs are designed to keep track of each transaction with the database. If properly configured, they can provide some fault tolerance to failures. Properly configured logs can "re-play" the database transactions since the last good backup to minimize data loss in a catastrophic database failure. The reality of databases is that they grow as your data grows. This means these databases and logs need space to operate and grow. However, there is much more to the story of having healthy servers running healthy SQL databases.
SQL Databases and Free Space
It is really important that when we size SQL databases and transaction logs, we provide adequate "free" space beyond the actual size of these databases and transaction logs and while accounting for database growth over time. Yesterday we had two different clients who "ran out of space" on two different SQL servers. One was an accounting database that was the client's line of business application. They had SQL databases and transaction logs on C: (always a bad thing). User data, SQL data, and Windows Updates created an unnatural space crisis and the SQL database simply shutdown. The users in multiple buildings were sitting around.
We had another client who had previously installed SQL databases on C: (again bad) and had undersized volumes. The result was the same - SQL melted down. Multiple buildings were effected. A general best practice is to have minimally 25% more free space than the databases and transaction logs actually require. Ideally it is more like 33% and if you want to factor in some growth, more like 50%. I have seen databases shutdown on volumes that have multiple gigabytes free because the transaction logs think there won't be enough churn space to do what they do. They chose to shut down and protect the data versus risk corruption.
CSI's Paladin Monitoring service can manage the stability of your SQL application as well as monitor and alert on disk space issues that could compromise the functionality of your SQL database application as well as actually put your data at risk of corruption.
SQL Databases and Backup
Properly configured SQL databases use special SQL backup agents to get a "clean" backup of the active SQL databases. These specialty agents also manage and control the size of the transaction logs based upon the validation that they backup is complete. These transaction logs must be regularly accounted for and managed or you risk severe space issues. This is a major feature of these specialty agents. These specialty agents generally cost extra money. If you are backing up SQL databases with traditional backup programs without these agents, you may not be getting a good backup of your most important databases.
There are a number of configuration options to allow good SQL backups. While the specialty agents are the most comprehensive in terms of backup there are other free alternatives. With real Microsoft SQL we can set up a scheduled Database Maintenance Plan via Microsoft SQL Management Studio. This will automatically create a static backup of the database. Your traditional backup process will then backup the static database copy cleanly without any special agents. Restores would be a two-step process and you may lose a little data, but it is free and the core data would be safe. We also have the option of backing up SQL using the traditional Microsoft built-in backup program which is SQL aware. Then your normal backup program would backup that static file. We also can back up the SQL database via a SQL script on a schedule. Again your normal backup program would then backup a static database file.
CSI's Paladin Monitoring service can monitor and manage the integrity of your data backup process to ensure that if bad things do happen, you'll always have a "Plan B" to keep your data safe..
It is extremely important that you know which of these scenarios you are using. One attendee to our recent backup seminar said, "my data is gold to me". If that data was corrupted or not properly backed up, he is out of business. If you are unsure about where you stand with the health and integrity of your critical data and backups, contact CSI. We'll help you figure this out for you.
Leave a comment!
You must be logged in to post a comment.