kohera-logo-regular.svg

Providing a SQL Server Express edition with a solid maintenance plan

Vector_BG.png

Providing a SQL Server Express edition with a solid maintenance plan

Vector_BG.png

One of the challenges of using the free express editions of SQL Server is the lack of the SQL Agent.

This means that creating a functioning maintenance plan is a bit more of a challenge then on a regular SQL Server instance. Luckily for us Ola Hallengren created his maintenance plan in such a way that it can easily be altered to make it usable for SQL express editions.

Ok let’s get started.

 

Creating the repository database

First things first, start with downloading Ola’s excellent maintenance scripts from his website, this ensures that you have the latest and greatest version. In this post I won’t explain all the parameters as Ola has an excellent wiki for all his

Personally I try to limit the creation of custom objects into the system databases, so I prefer creating a smaller database to contain all my DBA related objects. In this documentation we’ll use the name DBA_Express for this database.

 

Minor changes to Ola’s script

Changing the database from master to DBA_Express

 

Changing default behavior

 

It seems illogical to create the jobs as the agent is not running on a SQL Express, but I have a very good reasons to do so. It indeed doesn’t create any jobs on a SQL Express, but it doesn’t throw any errors neither so we can use this script on any SQL Edition

 

Scripts

Luckily for us Ola’s scripts are designed to run command line, so we’ll us the example scripts provided in his documentation to create 5 bat files. For this purpose, I have created the folder [D:\MSSQL\Maintenanceplan] where we can store these files. For Logging we create the [D:\MSSQL\Maintenanceplan\Log] folder

Backups

First things first, as a DBA backups is our main concern, so I’ll start with that. Please note that I’m against storing the backups on the same drive as the database files, but in this case I have no other choice. This directory will be backed up by using robocopy backups from that drive to a safe location.

Full Backups

 

Diff Backups

 

Log Backups

 

Cheating with compression

As an express edition doesn’t support backup compression and these servers tend to be rather thin provisioned it can be useful to compress the backup folder by using NTFS compression. As SQL Express database are limited to 10GB, and are seldom that big you could do this to save space. Don’t forget to turn it off once you go to a higher edition of SQL though, because you’ll hit the limit of NTFS compression quite fast on higher edition of SQL Server.

 

Index Rebuilds

Secondary let’s script our index and statistics maintenance, as this is an express edition I won’t bother splitting it up between indexes and statistics. Also we don’t bother including the online options as they are only supported in Enterprise editions.

 

Database Integrity

And then last but certainly not least, our database integrity checks. Again we are taking a special approach for express editions as we are not making any differentiation between system and user databases unlike on normal instances.

 

Adding the scripts to the windows scheduler

Start the windows task scheduler

 

Select Create Basic Task

 

Give your task a self-explaining name

 

Select your frequency

 

Select a moment

 

Select an Action

 

Point the action to the cmd files we created earlier on

 

Don’t forget to select to open the properties window before you select Finish

 

Make sure that your job will run with an account with the appropriate rights on both windows and SQL Server.

 

Make sure you test your newly created job, and then proceed to create the other schedules.

 

Special Remarks for the Log backups

As you cannot create a schedule that runs several times a day, you’ll have to create multiple log backup schedules that run daily.

 

2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...