Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Express Does Not Provide A Way to Setup Maintenance Plans Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 3:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, Visits: 146
We just installed a couple of SQL Server Express editions with a few standard databases.

Our DBAs are insisting that SQL Server Express does NOT provide the necessary tools to create maintenance plans. Is this true? If so, how are all the SQL Server Express Editions out there being maintained? I would suspect the Express Edition does offer at least a limited version of the Maintenance Wizard to run some basic tasks.
Post #1523455
Posted Monday, December 16, 2013 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
defyant_2004 (12/16/2013)
We just installed a couple of SQL Server Express editions with a few standard databases.

Our DBAs are insisting that SQL Server Express does NOT provide the necessary tools to create maintenance plans. Is this true? If so, how are all the SQL Server Express Editions out there being maintained? I would suspect the Express Edition does offer at least a limited version of the Maintenance Wizard to run some basic tasks.


that is correct; Express is a stripped down version of SQL, and does not include the SQL server agent, which contains the ability to create jobs/ maintenance plans.

you could create scheduled tasks in the Windows Scheduled Tasks to execute powershell or sqlcmd calls which perform the same kinds of functions, but it's a much more scripted-out solution, rather than a powerful GUI to help you.
alternatively, you might buy a Developer version for 50 dollars and replace the express instance with that instead; you cannot upgrade an express version, i think; that would require backing up the databases scripting out all sorts of settings , roles and users , uninstalling express, and installing Develoepr(or any otehr fuller version), and finally running the scripts and restorign the databases.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1523463
Posted Monday, December 16, 2013 8:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 14,201, Visits: 28,530
Nope. No maintenance wizard, no SQL Agent, so to maintain Express editions you'll need to have some type of scheduling service and create T-SQL scripts for all the maintenance.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1523511
Posted Tuesday, December 17, 2013 8:33 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 2:06 PM
Points: 611, Visits: 516
just a quick note: Powershell and other batches from windows task is the way I've managed it in the past. Developer edition is not supposed to be used in production. There are licensing issues there. It is a lot more scripting, but it is possible to manage it entirely from the windows task scheduler.


One thing to think about... how much are you saving by using express instead of standard edition when you add in the cost of the DBA's to work on scripting out jobs manually? If it forces you all to need to hire an additional DBA, you may just be costing yourself money.


.
Post #1523731
Posted Tuesday, December 17, 2013 10:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:06 AM
Points: 557, Visits: 1,648
Most maintanace functions can be done in SQLExpress through SQL Server Management Studio, but manually. Most Express instances are maintained when a DBA gets around to it, with utilities developed by vendors of the applications hosted on them, or only when something breaks.

In my opinion, SQLExpress is appropriate for 1) Configuration databases that are rarely updated (when they are they should be manually backed up and copied off server), and 2) testing complex scripts or commands that you're nervious about running on a DEV server the first time.

That said, it is possible to script out maintanance routines and run them from the command line via Windows Task manager, but the more databases you have, the more unweildy and time consuming the management of this will get. If the data is that important and you don't have 3rd party vendors with their own maintananace tools, consider at least getting Standard Edition.
Post #1523808
Posted Wednesday, December 18, 2013 5:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 2,900, Visits: 3,298
If you are setting up a lot of SQL Express instances it may be worth looking at SQL FineBuild.

The latest 3.2.0 release detects if Express Edition is being installed and sets up a number of Windows tasks to do the normal database maintenance processes (backups, index rebuild, etc). If an edition with SQL Agent is installed, FineBuild sets up the same tasks to run within SQL Agent.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1524050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse