Maintenance Task Considerations
We received a number of replies regarding the Maintenance Wizard shipping with SQL Server. A number of the comments raise concerns I hadn’t even considered, such as what happens when you move to another version of SQL Server and use undocumented tools to support the MS GUI.
David:
I share your opinion. When you combine the three points together, the picture gets worse:
- GUI created processes that may be created on one SQL box can be scripted out, but the undocumented support routines may not work correctly when ported to a different build of SQL Server, and may break when a SQL Server box or database is upgraded.
- Some of the undocumented stored procedures and functions have “magic values” for parameters that cause even less documented results. You have to be very careful modifying these parameters outside the GUI.
- Sometimes the GUI has trouble editing previously created processes, especially if they’ve been modified outside the GUI.
It would be nice if MS had created the GUI to strictly use the documented T-SQL and SSIS features. Then it would be easier and safer to use the GUI to create initial versions of processes and manually edit them later.
Aaron:
I think that the GUI that was included with Access 2000 is superior to anything in SQL Server.
Back then – 11 years ago, I could copy and paste a table, a view, a sproc, a function.
How many keystrokes does it take you to copy a table (with all the data) and all the DDL including Primary Key, etc?
Why was it easier with Access Data Projects in the year 2000 than it is today?
Editors Note: While I have concerns with Aaron’s approach to copy and paste, I suppose if you are going to have a GUI, why not go all the way?
Niel:
I concur with your frustration with Management Wizard SSIS defined job as you put it. I used the Maintenance Wizard for about a year and then gave it up, the main reason being difficulty of deployment, I never found or learned a good way to create a maintenance package on a development server and then successfully deploy that package to QA,UAT, and Production environments in an efficient manner.
When the day came that I had approximately 40 new SQL instances across 4 environments on which it was necessary to deploy these maintenance packages……..I finally gave up on the Maintenance SSIS Wizard.
Instead I wrote some maintenance scripts in TSQL and put them into SQL Jobs. This turned out to be much easier to deploy on those 40 SQL instances.
David:
I couldn’t agree with you more about the use of the undocumented and unsupported use of xp_delete_file.
Something I happened across last week was a maintenance plan that was using a maintenance task to clean up the old backup files. Problem surfaced in that it wasn’t working. After much research (Google) I found that the xp_delete_file procedure tries to confirm that the file is a valid backup, if it can’t confirm this then it doesn’t delete the file and it also does tell you that it didn’t delete the file.
Apparently the xp_delete_file tries to do a restoreheader on the file and if it fails it doesn’t delete the backup file. Because of this you also can’t have it cleanup anything that’s not a SQL file.
In my case, some of my backups are protected with a password because of sensitive data. I found that any backup where I had placed a password on it the maintenance task wasn’t removing the old back up files.
ODBC as a SQL Server Standard
David did some research are replies with more Detail on the Microsoft Shift away from OLEDB
Microsoft recently announced that they are developing an ODBC driver for Linux and Unix OSes, with the first release targeting RHEL5. http://blogs.msdn.com/b/brian_swan/archive/2011/10/13/microsoft-announces-sql-server-odbc-driver-for-linux.aspx
I think this makes their decision to go with ODBC-only more clear: Many web startups use open source databases running on Linux when they start out. When some of these startups take off and start to tax their original DB architecture, they currently only have one of three upgrade paths.
- Alter the FOSS DB to meet their needs, hiring programmers to specifically target pain-points in the DB for them.
- Alter their architecture to get around the limitations of the DB; either expanding it horizontally, supplementing it with a NoSQL DB, or switching entirely to a NoSQL DB.
- Pay up to Oracle for a proprietary DB, keeping their current architecture in exchange for jumping on Oracle’s treadmill.
I think Microsoft sees an opening with the third option to slowly convert these startups into “classic” Microsoft Enterprise customers.
I can’t speak from experience here, but Oracle’s DB has a reputation for difficulty in tuning and very high maintenance costs for very strong performance, while Microsoft’s SQL Server is easier to tune and maintain but not as high performing. However, when you can very easily scale SQL Server horizontally (especially so with SQL Azure), the extra server costs could be much less than Oracle’s tech support costs.
Similarly, for enterprises that standardized long ago on Solaris, but don’t like how Oracle has (mis)managed the operating system since, the only upgrade path before was one of the various enterprise Linux distributions, such as RHEL or SLES, or possibly IBM’s z-Series mainframes (running zOS or Linux).
So, by focusing development entirely on the ODBC standard, Microsoft can improve its performance and keep it feature-complete, and then by porting that driver to Linux and Unix machines, they make it easier to migrate from Linux and Solaris to Windows. Technically, this also makes it easier to migrate the other way, but people considering this move probably would have without the ODBC Linux/Unix driver, anyways.
Thanks for all the great feedback. If you want to share your thoughts send them to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint
Moving past the "what" and "why" of governance, an even… (read more)
Featured Script
UDF – Ending day of the previous month
A UDF to find the last day of the previous month, works very nice for a lot of accounting purposes…. (read more)