Patent Is Up!

One of the patents I applied for at Microsoft has finally been approved (the others are still pending). Check it out here. I have to give most of the credit to my co-inventor, Gang Wang, who is a brilliant engineer. The idea of this formed while we had lunch and talked about the challenges of moving large medical images across the network and we literally defined this solution on a Chessecake Factory napkin.

I've worked in the Health IT space for several years now and have seen some common problem recur that relatively simple algorithms and technical solutions can fix and am working on some new inventions now that I will likely patent. However, I have an ethical problem with locking down and making money on an idea that serves the common good. On the other hand, by locking down the technology, I can better control how and where it is deployed so there is upside.

I just hope congress can fix the patent mess where the trolls can claim ownership for things such as xml...

Backup SQLExpress 2008

SQL Express 2008 is a wonderful product. Perhaps my view is based on how bad MSDE was and how non-competititive the other "free versions" of SQL have been. But SQL Express 2008 is ready for prime time - although I still prefer the "free" version of DB2 - but the fact that it doesn't yet support LINQ means I'll use SQL 2008 until it does.

However, I found that when I install SQL Express, it also installs the SQL Agent but in disabled format. When I try to enable and use the agent, I'm told it's not supported in SQL Express. If it's not supported, why the heck install it MS? Oh well, I need to find another means to automate the backups of SQL Express. Enter SMO...

I first used SMO on a recent project at an ISV. We had used WIX to build our setups and the database stuff was the most difficult. We finally created a hybrid setup where part was using a standard MSI and part a simple .NET app that had the same look and feel for the SQL setup using the SMO objects. It worked great and made the setup application much simpler and nimble.

The SMO objects are super for setup, configuration, and management of SQL. Yes, I could've looked at the Powershell objects but I've worked with Powershell in the past and it is a bit too heavy for what I wanted to do- just backup my databases.

So the solution was to create a light console application in C# and run it from the Task Scheduler on the server. The code is available in my toolbox and includes a precompiled version if you don't want to compile.

Or, the basic source code is:

// the datasource but we need to add the database from the list
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["dataSource"]);

conn.Open();
Server smoServer = new Server(new ServerConnection(conn));

// root backup path
string rootBackupPath = ConfigurationManager.AppSettings["rootBackupPath"];
if (string.IsNullOrEmpty(rootBackupPath))
{
rootBackupPath = smoServer.BackupDirectory;
}

// retain days; defaults to zero if nothing in the app config
int retainDays = Convert.ToInt32(ConfigurationManager.AppSettings["retainDays"]);

// truncate log; defaults to false if nothing in the app config
bool isTruncateLog = Convert.ToBoolean(ConfigurationManager.AppSettings["isTruncateLog"]);

// incremental; defaults to false if not in app config
bool isIncremental = Convert.ToBoolean(ConfigurationManager.AppSettings["isIncremental"]);

// full backup day - assumes once per week; defaults to 0 if not set in app config
int backupDay = Convert.ToInt32(ConfigurationManager.AppSettings["dayFullBack"]);

// now loop through the databases passed in and back 'em up
string[] databases = ConfigurationManager.AppSettings["databases"].ToString().Split(',');
foreach (string database in databases)
{
string backupName = string.Format("{0}\\{1}.bak", rootBackupPath, database.Trim());
BackupDeviceItem backupDeviceItem = new BackupDeviceItem(backupName, DeviceType.File);
Backup sqlBackup = new Backup();
sqlBackup.BackupSetName = string.Format("{0} backup", database);

// if there are no backups in the file, make this full regardles of the incremental settings
if (!System.IO.File.Exists(backupName))
{
isIncremental = false;
}
if (isIncremental && backupDay != 0 && DateTime.Now.DayOfWeek != (DayOfWeek)(backupDay))
{
sqlBackup.BackupSetDescription = string.Format("Incremental Backup of {0}", database);
}
else
{
isIncremental = false;
sqlBackup.BackupSetDescription = string.Format("Full Backup of {0}", database);
}
sqlBackup.Incremental = isIncremental;
sqlBackup.LogTruncation = isTruncateLog ? BackupTruncateLogType.Truncate : BackupTruncateLogType.NoTruncate;
sqlBackup.Devices.Add(backupDeviceItem);
sqlBackup.Action = BackupActionType.Database;
sqlBackup.Database = database.Trim();
if (retainDays > 0)
{
sqlBackup.RetainDays = retainDays;
}
sqlBackup.SqlBackup(smoServer);
}