October 8, 2008 at 9:43 am
Hi there
I took a maintenance plan (conencted to SSIS) end exported it to a file. I connected to a clean sql server and imported the dtsx file.
This created the maintenance plan object, but it did not create any of the related jobs under SQL agent.
Upon looking at the maintenance plan I could see each of the subtasks marked as unshceduled.
I manually rescheduled one of the tasks and magically all the agents jobs now reappeared. None of the other tasks changed from unshceduled
My questions are
Why did the jobs magically appear only when I manually rescheduled one of them/
How can I relink the jobs created by importing the dtsx maintenance plan to its jobs?
and
Why is all of this such a pain in the ***?
Thanks
Barry
October 8, 2008 at 10:15 am
BCLynch (10/8/2008)
Hi thereI took a maintenance plan (conencted to SSIS) end exported it to a file. I connected to a clean sql server and imported the dtsx file.
This created the maintenance plan object, but it did not create any of the related jobs under SQL agent.
Upon looking at the maintenance plan I could see each of the subtasks marked as unshceduled.
I manually rescheduled one of the tasks and magically all the agents jobs now reappeared. None of the other tasks changed from unshceduled
My questions are
Why did the jobs magically appear only when I manually rescheduled one of them/
How can I relink the jobs created by importing the dtsx maintenance plan to its jobs?
and
Why is all of this such a pain in the ***?
Thanks
Barry
I wish I knew the answer to your questions. As to why it is such a pain I think this is a black hole in DR procedures microsoft didn't see ( or would not acknowledge) when SSIS was used for maintenance plans and SSIS seperated out to use the BIDS interface. We production DBAs always seem to get the dirty end of the stick.
It is sad there are no MS whitepapers on the subject of migrating the msdb database (that I can find). mind you, I'm not sure the SQL2000 quick fix of updating sysjobs.originating_server was advertised on official MS sites.
End of Rant.
---------------------------------------------------------------------
October 8, 2008 at 2:00 pm
I was able to get the jobs to appear by stopping and starting one of the services. Can't remember which one right now and can't check it as I'm at home.
October 9, 2008 at 6:02 am
These difficulties are due to the need for backwards compatibility and the new architecture. If you examine an SS2K5 (or SS2K8, for that matter) MSDB database, you will notice the original db_maint... tables that support the old SS2K maintenance plans and architecture.
You will also notice two sets of dts... type tables (dts... and dts...90). These are to support both the old SS2K and new SSIS packages.
Even in SS2K, the data between the db_maint tables and SQL Agent job tables were linked, but only parsed by the Database Maintenance Wizard.
The same is true for SS2K5 (only parsed by the Wizard); however, the database maintenance tasks are now defined through SSIS packages and stored in BLOBs within the dts...90 tables.
So, the jobs are not scanned (or reconciled) until the Database Maintenance Wizard parses them out.
You must keep in mind that the BIDS interface is NOT the DBMW; however, BIDS can connect to a SQL Server repository to extract dts table packages, and then edit them through that interface. BIDS can then "Save As" a new dts table package (The Repository) on the same SQL Server instance, another instance on the same host, or a remote instance, just like the DTS Designer version 8.0 (SS2K). But, because BIDS is NOT DBMW, the jobs are not reconciled until opened by the later interface once BIDS has created the new package.
Hope this help clarify some of the mechanisms the SQL Server tools are employing.
Keep in mind that prior to SP2 (or was it SP1), there was no DBMW interface and all maintenance plans had to be administrated through SSIS and BIDS. So, the situation is improving. Take note that this appears to be the design direction, to elminate the DBMW and force adoption of SSIS entirely (maybe...)
Sincerely,
Anthony Thomas
October 9, 2008 at 8:32 am
That's interesting (and detailed) commentary. Not in a position to test right now so I'll ask you. No doubt like a lot of people here I'm looking to identify a technique to "automate" (i.e minimise manual intervention/reconfiguration) the deployment of an existing MP and related tasks to multiple servers.
Are you saying that I could
1. Create MP1 and it derived scheduled jobs on Server A
1A. Use SSIS to export MP1 to a DTSx file inthe file system
2. Use BIDS to open the MP1 dtsx file
2a Save the MP1 file (now open in BIDS) to Server 2
3. Somehow (there's the rub) use the MPW on Server 2 to reparse MP1 on Server 2??
I appreciate now that the SSIS dtsx export feature is to allow editing in BIDS rather than, as I assumed, to allow importation into a different SQL Server - a mechanism to migrate MPS and related jobs. I think they should actually have blocked SQL import of the DTSX because all it does is cause confusion when it doesn't emerge fully formed after the import. Then again maybe I should have read more and assumed less 🙂
October 10, 2008 at 2:40 am
I was mistaken earlier when I claimed I stopped and started a Service to get the jobs to appear. What I in fact did was open the Maintenance Plans and changed something in them (for instance the size of the block in the window) and then saved all Maintenance Plans. After that I had the jobs.
October 10, 2008 at 4:31 am
yes that is what happened with me - after I imported the dtsx I rescheduled one of the subtasks. Magically all the the other subtask jobs appeared.
BUT none of the scheduling information was retained for the magically appaering subtasks when I reexaminied them using the MPW. At that point I abandoned the approach as viable as a general semi-automated process
October 10, 2008 at 6:54 am
What you have to keep in mind is that DTS and MPW are NOT the same thing, but SS2K5, and beyond, has chosen to integrate the tasks, not the schedules.
If you were to use SSIS to schedule the executions instead of SQL Agent, there would be less problems. Moreover, by using a centralized SSIS system, you could feasibly do away with the DBMPW altogether.
Also, a note in reply to some previous comments about automation: you do not have to save the DTS/SSIS MP package to a file; you merely need to use the "Save As" feature to redirect to another installation.
If you wanted to completely automate the process, but not deploy an SSIS engine, I would:
1. Create a template DBMP and use BIDS to save it to a file.
2. Use SMO and BIDS APIs to use the template DTS/SSIS package to modify and save to any other system that required them. Through BIDS (or any other .NET API), you can manipulate and save packages, to scripts, DTS files, or SQL Server repositories (MSDB). Through SMO, you can populate the SQL Agent jobs.
Again, the original intent for merging the technologies was to allow the SSIS engine to manage, schedule, and execute the MP packages, not SQL Agent. The inclusion of the MPW and SQL Agent jobs was an afterthought because not everyone wanted to deploy SSIS next to their respective Data Engines, nor wanted to necessarily deploy a dedicated SSIS host.
What we have is a compromise: which, as with all compromises, can do both, but neither well.
Sincerely,
Anthony Thomas
October 10, 2008 at 10:13 am
strikes me if you are not going to use the DBMPW to create the database maintenance jobs, then avoid SSIS altogether, its not the best tool for the job. Keep it simple and just write stored procs or scripts and schedule them via SQL Agent jobs. These can then be easily migrated by scripting out the jobs and loading them into a new server.
Of course this still leaves transferring all those SSIS packages that are not part of DBMPs and would need transferring in a DR situation.
I will be doing a test DR soon so as an experiment I will just restore the msdb and see how bad the mess is, but I will have the SSIS packages downloaded to file via DTUTIL as well as a belt and braces approach.
---------------------------------------------------------------------
April 18, 2011 at 8:43 am
Thanks!
Two of our SQL 2000 maintenance plan jobs were failing however the maintenance plan listed in the job name didn't match any of the maintenance plans listed. But with your advice I found the correct table in which to use the plan id (listed in the job step definition) and found the related plan.
Sincerely,
Phil
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply