Jump on the SQLExpress – Useful info, ways to schedule and run maintenance jobs
Sometimes, folks come to me, and ask for help and support with their SQL Server, only to discover it’s the Express Edition. They want to know why they can’t connect to it or better yet, to restore their database, when in fact it was never backed up.
SQL Server Express Edition (SSEE) is a free product based on SQL Server technology. According to Microsoft, SQL Server 2008 Express is a powerful and reliable data management system that delivers a rich set of features. This edition of SQL Server is ideal for learning, developing and powering desktop, lightweight web and small server applications and local data stores. It is used also for redistribution by ISVs. Express Edition is available for download for Microsoft SQL Server 2005, 2008, and 2008 R2.
Here in this article, we will refer to the latest SSEE for version 2008 R2. If you currently have existing 2005 versions, or want to work with it, you can learn more about 2005 Express by clicking on highlighted link for an Overview of SQL Server 2005 Express Edition.
Thankfully for DBA’s and other users, the GUI tools also come for free with SQL Server Express including SQL Server Management Studio Express Edition (SSMSE), Surface Area Configuration Tool, and SQL Server Configuration Manager. These tools simplify the basic database administration and management. You can also connect to it, using the SSMS client tools from other versions.
The installation files for SQL Server Express Edition are a lightweight package that is available for the 32-bit and 64-bit platform and operating systems. (x86 and x64 systems).
In SQL Server 2005, the upper limit for databases was only 4GB. Now in SQL Server 2008 R2, the maximum database size is 10GB. Indeed, a pretty good size for a free edition. Many of my production size databases, including vendor supported apps, don’t even come close to that size! Of course, you’re not expected to build data warehouses on Express, but certainly intended to handle a decent amount of data.
To connect to a SQL Server Express Database Engine instance by using SSMSE, in the Connect to Server dialog box, specify any of the following:
.\sqlexpress, (local)\sqlexpress, or server name\sqlexpress
Please note that the default installation of SQL Server Express uses an instance name (SQLExpress). This instance name must be supplied to connect to SQL Server Express by using SSMSE. If you have an instance name other than SQLExpress, connect by using computer name\instance name.
SQL Server 2008 R2 Express Edition, as well as Service Pack 1 and Service Pack 2 are also now available, with the latest in feature enhancements, hotfixes and cumulative updates rolled into the deployment packages. As you can see, the MS SQL Server team has not neglected the express edition, and has taken care to support this application among its family of SQL Server products.
The only way to use SQL Agent is to upgrade and use one of the paid for SQL Server Editions. This option of course, is available, for a price. So you ask, how can I schedule and take backups of my Express Edition databases, and how can I run maintenance tasks on them?
Although the SQLAgent as a GUI tool is not available here, there are alternative solutions! The scripting engine is supported, and can easily take backups, rebuild indexes, etc. To do this, you can use sqlcmd.exe, batch files, and the Windows Task Scheduler.
Before we get into this really neat scripting solution I want to share, there are some other free GUI tools that are available, but please check the SQL version supported. In a previously published tip on MSSQLTips.com by Greg Robidoux, he shares information about a pretty full featured tool called SQLScheduler. However, it only supports, as far as I know, versions 2000 and 2005.
Finally, there’s one crafty fellow on codeplex.com, who developed his own SQLAgent: A Job Scheduler Framework, which shows how to create a generic scheduler that runs as Windows Service, and is an easy to use .NET solution. I’m sure there are others if you Google them, but this is what I came up with for FREE! Also, I have not tested these 3rd party solutions, so proceed with caution.
Now, you say you want a way to automate your Database Maintenance for SQL Server Express? You must check out this 2-part article on Automating Database maintenance in SQL 2005 Express Edition Part I and Part II by SQL MVP Jasper Smith. This solution supports versions 2005 and 2008/R2, and mimics some of the behavior of the old sqlmaint utility in SQL 2000. It creates a stored procedure that will execute the following operations:
- Full Database Backup
- Differential Database Backup
- Log Backup
- Housekeeping of backup files
- Database Integrity Checks
- Database Index Rebuilds
- Database index Reorganization
- Report Creation
The beauty of this process is that it is well-documented and allows you to input passable parameters via the SQLCmd.exe, such as specifying user dbs, systems dbs, backup type & retention, cleanup, report & backup folder paths. In addition, you can click on the companion links that support restores, integrity checks, backups, and verification. It uses SMO, from SQL 2005, but should be supported for backwards compatibility in SQL 2008.
In Part II, he has a command line version and gives you sample code and a standalone console application.
I found Jasper’s article(s) to be extremely useful and helpful, and was very easy to implement within minutes. Right now, my scheduled backups are humming along, with great status outcome reports. Best of all, it’s FREE! He did a good job on this, and I recommend it for those who need help with scheduling regular backups and maintenance on your SSEE! Hope this is helpful. Enjoy!
è Automating Database maintenance in SQL Express Edition ß
Pearl Knowledge Solutions, Inc. offers, in addition to its SQLCentric Monitoring and Remote DBA Services, solutions including SQLCentric and SafePeak,
SQLCentric has just received the SILVER award for Best Database and Performance Monitoring Product from SQL Server Magazine’s 2010 Community Choice Awards!
Ask about our comprehensive end-to-end performance assessment and report, and Remote DBA Services.
