November 18, 2011 at 1:06 pm
All,
Thanks in advance for any guidance you can give.
I work for an ISV that installs our product in Clients' data centers on their servers. SQL Server is the DB engine supporting our stack, with our latest builds of our product running on SQL 2008 R2. (We do have 2000, 2005, and 2008 in our client base as well).
We have a standardized maintenance strategy that consists of several maintenance plans. These plans are almost identical every time, with the exception of connection info (i.e. server name), and file paths for backups and logging. Also, there is some slight variability in the actual names of the DBs.
Anyways, rather than creating these plans manually, (probably ~50-60 plans a year) I'd like to save time and reduce the chance for error by automating. Unfortunately, my research leads me to believe that all of my options have major flaws:
1) SMO does not actually have any classes/interfaces for interacting with Maintenance Plans
2) Creating an SSIS package through SSIS's programmatic interface means that it won't register as a maintenance plan. This is a major flaw, as our in-house-developed monitoring scripts look at the MSDB maint plan tables for the breakdown in task duration. (Yes, we could change our monitoring to an SSIS-based tracking, but that would be a major overhaul and involve a lot of other moving parts...not happening).
3) Creating a "template" plan, manually exporting it via Management Studio, and then having some script/program modify the XML at the client, then manually importing it at each client seems to be the best way. However, I get nervous when I see all those GUIDs, non-intuitive elements, and Management Studio versioning #'s in the XML, and I'd rather not get something wrong and then have strange/unpredictable Maint Plan behavior at a client.
Any thoughts? Anything new in SQL 2008 (or R2, or Denali) that might help us out?
Aaron
November 18, 2011 at 1:45 pm
Instead of using the Microsoft wizard plans, I have a "DBA" database with configurable procedures in it, that manages all of that for me.
Much more granular control of the thing, dead easy to deploy, customized to fit current needs, and since it's mine (not my employer's IP) and I know every line of code in it, it's very easy to upgrade/refactor/debug/etc.
I have a script that will create it, create and populate the necessary tables, create the necessary SQL Admin jobs, including notifications and alerts, etc.
Why not do something like that? Once you've got it working, and have a single-script-deployment solution, you're all set. Takes a lot of work the first time around, saves even more over time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2011 at 8:26 am
You can export the SSMS maintenance plans and put them into a BIDS SSIS package. Then, you can modify the connection strings, backup paths, etc... and Save Copy As to the new server.
This can be automated using dtutil - look it up for the particular parameters and setup. The real issue is making sure the GUID gets updated when it is saved to the new server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 21, 2011 at 9:58 am
GSquared,
Thanks for your post. Yeah, I'm seriously considering the Proc/Job approach instead of a Maint-Plan-based approach. The only reason we haven't done that yet is our frontline support (the analysts who log into our clients' servers and the ones who are in the system the most) are used to Maint Plans as the vehicle for maintenance automation, and making a change to a code-based solution means it is less supportable on their end.
Still, manually creating maint plans for the next 50 clients we engage does NOT sound appealing. 🙂
Thanks,
Aaron
November 21, 2011 at 10:00 am
Thanks Jeffrey,
Sounds like you are a proponent of #3, mentioned in my original post. If I do stick with a Maint Plan-based strategy, then #3 seems the most feasible to me right now.
Thanks for responding,
Aaron
November 22, 2011 at 12:48 pm
I wouldn't say I was a proponent of #3 - just that it can be done. I worked with an individual who set this up using VBScript - and he says it works well.
I use the saved SSIS project, modify the connection manually and then Save Copy As...
Or, I create the MP manually (no, I do not use the wizard).
I am not against the proc/script/job method - but since I have already setup this way it is just as easy to do this as it is to do separate agent jobs and custom procedures.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2011 at 12:56 pm
Thanks Jeffrey.
With your SSIS project approach, are you able to get the package to log to the existing maintenance plan tables in MSDB? And have Management Studio recognize your package as a Maintenance Plan? If so, did you have to do anything special?
Our monitoring solution's customized code depends on a Maint Plan being recognized as a Maint Plan in order for us to track failures, task durations, conformity of a plan to our standard template over time (i.e. track when it has been changed by someone outside of our knowledge), etc.
I'd been avoiding the SSIS approach (either programmatically or through a project) due to my understanding that we'd lose the connection between SSIS and the MSDB maint plan objects.
In other words, if we don't have that, we'll need to modify our customized monitoring anyways, so we might as well start from scratch and evaluate the best long-term approach.
Thanks!
Aaron
November 23, 2011 at 1:07 pm
Why don't you deliver an agent backup job that calls a delivered backup stored procedure. Both can be scirpted.
Have the variables involved (db name, backup destination folder, etc...) passed as parameters.
Have your client populate the parameters to meet thier needs and enable the job.
If they have data centers they must have the expertise for that. Plus it adds flexibility.
November 23, 2011 at 1:23 pm
Or deliver Ola Hallengren's Maintenance Solution. Give them defrag and CheckDB along with backup jobs.
November 23, 2011 at 1:38 pm
ammo-963576 (11/22/2011)
With your SSIS project approach, are you able to get the package to log to the existing maintenance plan tables in MSDB? And have Management Studio recognize your package as a Maintenance Plan? If so, did you have to do anything special?
You just need to save the package to the Maintenance Plans folder in SSIS on your server (it's under Stored Packages --> MSDB --> Maintenance Plans using SSMS. I just tested uploading a package to there from my file system using SSMS connected to Integration Services. Then I when connected to my database engine via SSMS the plan showed up under Management --> Maintenance Plans. Strangely it didn't appear to work when I did the Save a Copy As to the same location via BIDS.
Thanks,
MWise
November 23, 2011 at 2:58 pm
ammo-963576 (11/22/2011)
Thanks Jeffrey.With your SSIS project approach, are you able to get the package to log to the existing maintenance plan tables in MSDB? And have Management Studio recognize your package as a Maintenance Plan? If so, did you have to do anything special?
Our monitoring solution's customized code depends on a Maint Plan being recognized as a Maint Plan in order for us to track failures, task durations, conformity of a plan to our standard template over time (i.e. track when it has been changed by someone outside of our knowledge), etc.
I'd been avoiding the SSIS approach (either programmatically or through a project) due to my understanding that we'd lose the connection between SSIS and the MSDB maint plan objects.
In other words, if we don't have that, we'll need to modify our customized monitoring anyways, so we might as well start from scratch and evaluate the best long-term approach.
Thanks!
Aaron
What you do is add an existing item to your SSIS package. Then, you access one of your servers and pull down the maintenance plans.
The package will then have the maintenance plan definition and can be saved back to another server. When you save it to the new server, using the Save As option you select the server and put it in the maintenance plan folder.
Then, open it in SSMS as a maintenance plan - and you will see everything you expect to see.
Remember, you have to change the Local Connection to the new server - but, do not change the name or it won't work. Just change which server it points to.
I also update the tables directly to change the maintenance plan owner - but, that isn't required.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 24, 2011 at 6:01 am
GSquared (11/18/2011)
Instead of using the Microsoft wizard plans, I have a "DBA" database with configurable procedures in it, that manages all of that for me.Much more granular control of the thing, dead easy to deploy, customized to fit current needs, and since it's mine (not my employer's IP) and I know every line of code in it, it's very easy to upgrade/refactor/debug/etc.
I have a script that will create it, create and populate the necessary tables, create the necessary SQL Admin jobs, including notifications and alerts, etc.
Why not do something like that? Once you've got it working, and have a single-script-deployment solution, you're all set. Takes a lot of work the first time around, saves even more over time.
GSquared!
You want to share Script regarding Jobs-notification and alerts ?
thnx for sharing.
Kahn
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply