I received warnings in Microsoft System Center Advisor (a proactive monitoring tool included with Microsoft Premier Support) about my databases running in SQL Express. How do you manage and maintain those SQL Express databases?
First, remember that although it is not the full version of Microsoft SQL, it does still require patching and maintenance. Windows Updates should show SQL service packs and patches in available offerings. As a default plan, I try to keep my SQL Express databases on the same patch level as my full SQL Servers. (Unless other limitations apply). If you have access to Microsoft System Center Advisor, it will keep you updated for any SQL Express installations on Windows Server 2008 R2 or newer.
Microsoft provides a version of SQL Server Management Studio for SQL Express in Microsoft Downloads. It is listed as Microsoft SQL Server 2008 R2 Management Studio Express. Although SQL Server Express does not allow for Maintenance Plans, you can still manually run scripts for maintenance tasks such as checkdb, reindex, backup, etc.
After installing Management Studio and gaining access to the database I could manually backup the database and run some management tasks with scripts I created earlier. To maintain the health of the MSONLINE database, I wanted to backup the database, check the consistency, and reindex the database.
To accomplish this, create a SQL script to backup all the databases on your instance, one to run checkdb, and one to reindex. (You can search the internet to find examples or script database in Management Studio). Save these scripts in a folder locally on the server. In my example, I use C:\SQL_Scripts\MSONLINE.
Create a Windows Command Script file to run the SQL scripts. This will allow you to run this as a Scheduled Task in Windows Server. In my script, I add all three maintenance tasks as shown below and direct output to a report file in the same folder:
REM Backup databases
sqlcmd -E -S servername\MSONLINE -i”C:\SQL_Scripts\MSONLINE\BackupDB.sql” -o “C:\SQL_Scripts\MSONLINE\Backup.rpt”
REM Check Database
sqlcmd -E -S servername\MSONLINE -i”C:\SQL_Scripts\MSONLINE\CheckDB.sql” -o “C:\SQL_Scripts\MSONLINE\CheckDB.rpt”
REM Rebuild Index
sqlcmd -E -S servername\MSONLINE -i”C:\SQL_Scripts\MSONLINE\Reindex.sql” -o “C:\SQL_Scripts\MSONLINE\Reindex.rpt”
You may also want to add a script to automatically cleanup your backup folder.
This should help you keep your Directory Sync SQL Express database running and healthy.
Check out Microsoft System Center Advisor at https://www.systemcenteradvisor.com/. For more information on Microsoft Premier Support see http://www.microsoft.com/en-us/microsoftservices/support.aspx