SQL Maintenance on SQL Server Express

CCS recommends using the licensed versions of Microsoft SQL server products to get the full feature set and the most current patch level support.  In some cases, the SQL Server Express Edition (SSE) may be adequate for your needs.  As with most community version or express version software there are limitations.  Often this is a major feature like automatic task scheduling or a critical monitoring capability.  There limitation that SSE has is you cannot run the SQL Agent and schedule jobs.

To automate the SQL maintenance with SSE, you use stored procedures, combined with the Windows task scheduler.  I create stored procedures to do the maintenance tasks that I want to do, such as rebuilding indexes, updating statistics, and backing up the databases.  Personally, I prefer to do each task as a separate stored procedure.  This avoids the monolith procedure that is a nightmare to maintain.  These would be the same stored procedures that I would use for a full SQL server implementation, where I can use the SQL Server Agent to schedule jobs.

At this point, for SSE, I make one additional stored procedure.  This is one simply calls each of the maintenance procedures in turn.  Instead of creating a job with multiple steps, you simply call this procedure to perform all of the SQL maintenance tasks.

Now to be able to schedule the maintenance procedure to automatically run.  The key here, is to install the SQL command line utility (sqlcmd).  This utility allows you to execute SQL commands from the Windows command line.  It is simply a matter of using “sqlcmd” to execute the stored procedure which calls all the maintenance tasks.

The final piece to the puzzle, is to create a batch file with PowerShell or another tool that runs the sqlcmd line.  Once the batch file is done, use the Windows task manager, and scheduling the batch file to be run at the desired time.

Although this approach is easy in concept there can be several syntactical gotchas and quirks of the task scheduler to work through.   For example, Windows and SQL security requirements.  If you wish to explore the SSE alternative contact CCS support.

Dave.

Leave a Reply