With the prevalence of DotNetNuke DNN out there in the community and the large numbers of people utilising hosted solutions to host their websites, and the fact that SQL Server 2005 Express Edition is a very popular back-end database I figured this would be a good time blog about this version of SQL Server.
The first thing I would like to say is that Main stream support of SQL Server 2005 finished on the 12th April 2011. If you have the ability to then upgrading to SQL Server 2008 R2 is something that you should look at considering. However I understand that if you are utilising a hosted environment you are on the version provided by the hosting company.
As SQL Server 2005 Express is a free version of SQL Server it does have limitations compared to its paid for editions. Some of those limitations are as follows:
-
Limited to 1 CPU
-
Limited to 1 GB of RAM
-
Limited to 4 GB databases
-
No support for SQL Server Agent
If you are able to look at upgrading your current environment to SQL Server 2008 R2 aside from having a supported edition the big difference between 2005 & 2008 R2 is the database size has been increased from a 4 GB Limit to a 10 GB limit. SQL Server 2008 R2 Express is available for download and trial for you in both 32 & 64 bit versions, or you could try Denali Express CTP3
Because there is no SQL Server Agent as part of this edition of SQL Server and the large number of instance of SQL Server 2005 Express being used, I feel 1 of the biggest pain points being felt by people is the inability to run and schedule jobs against their databases, either to perform Database Maintenance or to perform regular processing. If you are in this boat then this Blog will enlighten you to a tool that is available to help out.
This tool has been around for a little while and I am not 100 {c6a8703d43a745210f119fcab54d82c98494005c48735bbb9f7b184b048e39fe} sure of how many people know about it or are using because they do not know about it but this tool can make your life much simpler in maintaining your instances of SQL Server Express. The supports the following versions of SQL Server Express:
-
SQL Server 2000
-
SQL Server 2005
-
SQL Server 2008
And has these requirements for you to install and run the application:
-
Windows 2000,2003, XP, Vista & Windows 7
-
.Net Framework 2.0
The Installation process for this application is very simple. Once you have extracted the folders and files from the downloaded zip file, you just copy the folder contents to your destination server location and execute the installservice.bat file.
Once the installation has completed can configure the SQLScheduler.WindowsService.exe.config file to have your appropriate SMPT server configured to allow you to send alerts when your jobs fail.
We are now ready to configure your jobs. Double click on SQLScheduler.exe and the GUI will be available.
Add a New Server
-
Right Click Servers and select “New Server”. Enter your Server\Instance Name or IP\Instance Name
Add New Job
-
Right Click on your newly created server and select “New Job”
-
Give you Job a Name and a description and select the type of authentication you will connect to your SQL Server Express Instance
-
Put your job details in either as calling a stored proc or tsql code
-
Schedule your job according to your requirements
-
Setup your job failure notifications
-
Click Save
You have now created your first scheduled job to run against your SQL Server Express Instance.
Delete a Job
-
Right click on the job you wish to delete and select delete
Execute a Job
-
You can manually execute a job as well as via the schedule. Right click the job and select Execute.
With the installation of SQLScheduler it will be added to the services on your server and be set to automatically start.
Happy hunting with this Scheduling tool for SQL Server Express editions. Come back and read my blog on the Database Maintenance jobs that you would want to implement on your system to give you confidence in your recoverability and performance of your system.
Leave a Reply