December 16, 2013 at 3:21 pm
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.
December 16, 2013 at 3:29 pm
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
December 16, 2013 at 8:15 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2013 at 8:33 am
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.
.
December 17, 2013 at 10:33 am
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.
December 18, 2013 at 5:10 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 3, 2015 at 11:29 pm
I have already installed the SQL server 2012 Express edition. Can I now install SQL Server finebuild to automate certain tasks.
will it overwrite the current sql Server installation?
June 5, 2015 at 9:21 am
Can I now install SQL Server finebuild to automate certain tasks
Sorry, FineBuild can not be used to configure an existing instance. This is something tha tmight get added, but for now the answer is no.
If you use FineBuild to install a new instance, then it will install the maintanance tasks for that instance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply