Deployment Solution

To all those expecting to finally start using Deployment Server -my apologies. It's a quick return to Microsoft SQL Server I'm afraid. We need a brief chat about SQL Server's recovery models.

You see, a big gotcha which often catches unsuspecting Deployment Server administrators unaware is the problem of unchecked growth in the transaction logs. This will manifest in odd ways in the Deployment Console -clients not running tasks being the big one. The first impulse when this happens is to assume that it's an Altiris problem, so naturally this is the application you'd focus on. And of course, by attacking the wrong end of the problem you may well end up spending many days in fruitless troubleshooting.

So, pay attention. Ten minutes spent here might save you a lot of time later.

Quick Intro into MS SQL Server

When a new database is created, traditionally the default setting in SQL is for each transaction to be logged to file. This is so that your database can be restored back to any point in time covered by the log. This is nifty, but comes at the cost of your database needing maintenance. Without loving care, these transaction logs can grow and grow, until eventually they become so immense that system stability is compromised and things get weird.

So, let's start with the basics. When you create a database in SQL server, it will have at least two files;

• <database>.MDF
• <database>_log.LDF

The first holds the database data. This file is loaded when SQL server starts, and gets updated at each checkpoint¹. When a transaction occurs which modifies data, this transaction is appended to the end of the second file, which we call the transaction log.

Backing up the transaction log

The speed at which your transaction log grows will depend on how fast your data changes. In order to stop the log from growing excessively, you have to back it up and then remove from it all the transactions that have now been successfully stored in the backup file.

Backing up the transaction log is a standard database management task, and is required for point-in-time disaster recovery.

MS SQL Database Recovery Models

The recover models for MS SQL are:

1. Simple
   Effectively no transaction log, so database restores only possible using previous full database backups
2. Full
   Point-in-time recovery possible using last full backup and transaction log
3. Bulked-Logged
   Just as in the full logged case, the transaction log allows full point-in-time recovery in conjunction with last full backup. This mode provides more efficient logging for databases targeted by bulk updates. This can happen for example when SQL Servers undergo a nightly bulk update as part of a synchronisation process, and in this case you might temporarily switch the database recovery mode to bulk-logged for the duration of the sync.

The simple recovery mode is interesting. Every time the database synchronises with the data file on the disk (the checkpoint) it truncates the transaction log without a backup. This means that should you lose your data file, you have no idea of what transactions occurred since your last database backup. In short, your disaster recovery plan is to return your database to the time of your last backup.

The full and bulk-logged models allow for a growing transaction log. As all transactions are recorded you can restore you database from your last backup, and roll on the subsequent transactions from the log.

SQL Server 2008 Default Recovery Model

The default recovery model Microsoft uses for databases has recently changed with the advent of SQL Server 2008. In SQL 2000 and SQL 2005, the default recovery model was bulk-logged. In SQL 2008 however it's changed to simple.

This sounds terrible -the default is now not to log transactions?? Yep, and the reasoning is actually sound -most people installing SQL Server won't be doing it because they are SQL administrators, they'll be doing it because another application requires it. In such scenarios, you want SQL to install with its most stable, minimum-overhead configuration. As unchecked transaction log growth is such a problem, Microsoft have changed their default recovery model with the release SQL 2008 to work around the issue.

In the case where a SQL Server administrator is installing the product, they'll know all about recovery models and will change the model if necessary, putting the maintenance plans in place as required.

Since SQL 2000 I have personally always recommended that Altiris Administrators change the recovery model from bulk-logged to simple because,

1. No chance of the transaction log ever growing too large and bringing your database/server down.
2. Transaction logs can be quite big. Keeping backups can use quite a lot of disk space.
3. The data in the eXpress SQL database is not rapidly changing. Restoring the database solely to the previous night's backup is rarely an issue

So, to my mind the simple recovery model is completely adequate.

Confirming Your Database Recovery Model

1. Open up SQL Server Management Studio (available for both SQL 2005 and SQL 2008)
2. Login at the authentication dialog
3. In the Object Explorer navigation folder, right-click the express database and select 'Properties'
4. Under the 'Options' page, ensure that the recovery mode is configured as Simple.

   

5. Click 'OK' and exit SQL Server Management Studio

Why should I backup my database?

Backing up the database is critical on a production server. We all know it is, so why don't we all do it? Here's a list of good reasons to backup your database just in case you are not yet convinced. I've divided these reasons into two sections for clarity.

I leave it to you to discern into which of the above categories most database restores emerge from. The point I'm trying to make here is that a robust database backup strategy is essential, and as it happens the maintenance plan designer and wizards are pretty intuitive. So please backup your databases, especially if you are a tinkerer by nature, like pubs, and occasionally work in the evening.....

Why Shouldn't SQL Express be used in Production?

It might surprise you that the overriding reason for avoiding the SQL Server Express editions in production environments is not performance. Yes, it's true that SQL Express can only utilise a single CPU and 1GB of RAM, but for Deployment Server this isn't a huge problem (assuming nothing else is on-box). The problem with the Express Editions stems from the deliberate omission of the SQL Server agent, and consequently the lost ability to build and execute database maintenance plans².

So, keep the Express editions to your test & development machines. As soon as your servers hit production buy your SQL licenses and put decent maintenance plans in place.

Footnotes:

¹ At a checkpoint, the database in memory is flushed back to the data file on the hard disk. This ensures the data file remains current.

² Yes, you can get around this with T-SQL scripts and windows scheduled tasks, but I'd rather on the whole that you didn't.

Return to Index

Read Part 7: Deployment Server 6.9 - A Quick-Start Course, Part 7: The Deployment Console