InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 17:32:22 GMT20http://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxThanks! 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, PhilMon, 18 Apr 2011 08:43:46 GMTpashisbehttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxstrikes 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.Fri, 10 Oct 2008 10:13:03 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxWhat 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 ThomasFri, 10 Oct 2008 06:54:56 GMTAnthony L. Thomashttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxyes 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 processFri, 10 Oct 2008 04:31:41 GMTBCLynchhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxI 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.Fri, 10 Oct 2008 02:40:36 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxThat'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 could1. Create MP1 and it derived scheduled jobs on Server A1A. Use SSIS to export MP1 to a DTSx file inthe file system2. Use BIDS to open the MP1 dtsx file2a Save the MP1 file (now open in BIDS) to Server 23. 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 :-)Thu, 09 Oct 2008 08:32:50 GMTBCLynchhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxThese 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 ThomasThu, 09 Oct 2008 06:02:44 GMTAnthony L. Thomashttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxI 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.Wed, 08 Oct 2008 14:00:50 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspx[quote][b]BCLynch (10/8/2008)[/b][hr]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 unshceduledMy questions areWhy 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?andWhy is all of this such a pain in the ***?ThanksBarry[/quote]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. Wed, 08 Oct 2008 10:15:25 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxHi 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 unshceduledMy questions areWhy 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?andWhy is all of this such a pain in the ***?ThanksBarryWed, 08 Oct 2008 09:43:52 GMTBCLynchhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxDon't know how relevant this thread is anymore, but I was able to move a 2K5 maintenance plan by exporting it in SSIS. After the export, I had to open up the resultant dtsx file in Visual Studio and change the global path variable for reporting to a path on the new server, change the report task path property to the same location, and update my connection configuration. Resaved the file with the new path and connection manager value (I was moving from a cluster instance to a standalone, so had to define localhost). Imported into the new sql server and set up the schedule...Worked fine.I can give further detail on the process if needed.Tue, 19 Aug 2008 01:39:52 GMTSean Kohlerhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxI've been able to get there (on MS SQL Server 2005), with thanks to directions at http://sqlserver-qa.net/blogs/tools/archive/2007/09/09/value-of-9-6-2007-12-00-00-am-is-not-valid-for-value-value-should-be-between-mindate-and-maxdate.aspx about how to open, save and modify existing Maintenance Plans.It's not as straight forward as I'd like. I still have to put some effort into changing connections and due to having named instances, we have to change MsDtsSrvr.ini.xml.It does all work and seems to be less work than creating the Maintenance Plans manually, manely due to our preferred connection settings in the Maintenance Plans. But it is a bit of a hassle that you cannot just use one product to do it all. And I do wonder if I could get this to work if I had two named instances on the same server. I'm not sure how one would change MsDtsSrvr.ini.xml for that.If anyone would like a detailed description of how I got it working, let me know and I'll post it. Or mail me directly.Tue, 12 Aug 2008 06:50:14 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxHello Anthony,Thanks for the info, but how do I open an existing Maintenance Plan in the Business Intelligence Development Studio? From what I see I can't log into the MS SQL Server to go to the msdb table.DeboraThu, 31 Jul 2008 06:09:08 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxBasically, yes, but even in SS2K you might have needed to use the DTS Designer to modify package connections if you used "(local)" and/or logged package executions locally.It is a little more complicated because the 9.0 SSIS packages can be more complex, but the idea is generally the same.I certainly would not try upgrading an SS2K MSDB to SS2K5 and migrating platforms to boot. I'd upgrade the database first, and then try to relocate a copy.Sincerely,Anthony ThomasWed, 23 Jul 2008 10:19:11 GMTAnthony L. Thomashttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxAnthony, thanks for that, most helpful. so what it means is in 2005 we can still use the DR process of copying over and restoring the msdb to recover jobs, alerts and packages. However whereas in 2000 all we had to do was update sysjobs.originating_server witht the new server name, we will now also need to go through the SSIS packages in BIDS and update the connection manager objects, which could be time consuming if you have lots of packages, unlesss that can be automated.Tue, 22 Jul 2008 16:13:01 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxFirst of all, DTS (and/or SSIS) packages are just that, packages. They can be saved to a code file, a proprietary file (.dts or .dtsx), or saved in the MSDB repository. When they are saved in the repository, they are stored as an IMAGE (or large binary) data type.That is just storage. What the package does, how it executes, and the environment where it executes is up to you and the definitions within the package itself.The tools outside of SQL Server help develop, deploy, and run the packages...regardless of where they are stored.If you have restored a copy of an MSDB database from one platform or another, or exported out these image files and imported them to another system, you should still be able to read them with the native DTS, SSIS, BI Studio tools.Keep in mind that SS2K packages are stored in MSDB.dbo.sysdtspackages, whereas SS2K5 packages are stored in MSDB.dbo.sysdtspackages90, and can only be read by the appropriate tools.The SS2K5 tools can read SS2K packages, but only for execution, not for modification or upgrade.Hope this helps.Sincerely,Anthony ThomasTue, 22 Jul 2008 15:45:59 GMTAnthony L. Thomashttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxAnthony question for you perhaps you can help with. If you can get maintenance plans working on a new server after copying over msdb, can you get all SSIS packages working which were saved to msdb?Tue, 22 Jul 2008 13:16:05 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxYou must keep in mind that only the SS2K maintenance plans are stored in the maintenance plans MSDB system tables as legacy object.The new SS2K5 maintenance plans are modified SSIS (or DTS) packages and are stored in the dbo.sysdtspackages90 table, specifically in the Image column (why not VARBINARY(MAX)?).Anyway, with the Business Intelligence Studio, you can add packages to projects, save them as files, or save copies to files, as SSIS package files, or, yes, back into the MSDB repository.It is quite interesting to take a look at all of the "additional" items created for a maintenance plan package. But, one of those will be the connection manager objects, which contains the definition for the Local Server Connection object, and which can be modified.Good luck.Sincerely,Anthony ThomasTue, 22 Jul 2008 12:05:03 GMTAnthony L. Thomashttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspx[quote][b]ddonck (6/12/2008)[/b][hr]And one last update. Since I couldn't find a way to rename the local connection I decided to start fresh and delete the existing Maintenance Plans. That failed due to the local connection. I tried to hack my way through by deleting in the msdb tables, but that didn't work. And since I didn't make a backup of my original msdb after creation (I know, my bad), I was stuck.To prevent a complete reinstall, I tried to load a backup of a freshly created msdb from another server, but this again gave problems. At the end I had to reinstall the complete MS SQL Server.In short: I don't recommend anyone else try this.[/quote]looks like u have hit another reason why using SSIs for mantenance plans was not best idea in 2005 if you are a production DBA.why use an integration tool to run intra server database maintenance tasks............ a bugbear of mineFri, 13 Jun 2008 07:19:26 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxAnd one last update. Since I couldn't find a way to rename the local connection I decided to start fresh and delete the existing Maintenance Plans. That failed due to the local connection. I tried to hack my way through by deleting in the msdb tables, but that didn't work. And since I didn't make a backup of my original msdb after creation (I know, my bad), I was stuck.To prevent a complete reinstall, I tried to load a backup of a freshly created msdb from another server, but this again gave problems. At the end I had to reinstall the complete MS SQL Server.In short: I don't recommend anyone else try this.Thu, 12 Jun 2008 02:55:36 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxThere turns out to be something of a problem with loading a msdb backup (in MS SQL 2005). In Maintenance Plans the "Local server connection" is set to the original server and cannot be changed. You can ofcourse create a different connection (as we do) and point that to . (dot = local server). But it's stille quite confusing to have an Maintenance Plan in MS SQL Server A that has a local connection to MS SQL Server B.Where it get's this from I do not yet know. It's not in any of msdb's tables or views. It might well be in one of the system tables, but as we're not allowed to query them anymore I don't know how to check (let alone change).If anyone has any idea as how to solve this, I'd love to know.Fri, 06 Jun 2008 07:03:56 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxYou will have to update the table sysjobs in msdb. The field is originating_server and should reflect the new server name. I've done this in the past without issue.Edit - This is for S2K.Wed, 28 May 2008 08:45:39 GMT tosscrosbyhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxThought I'd just try to load a dump of msdb (from another MS SQL where I'd already created the Maintenance Plans) and this seems to work like a charm. I did have to reboot the MS SQL Server to be able to view and edit the Maintenance Plan tasks.The only thing you might need to change is the databases the Transactionlog backups are made on (since "all databases" doesn't skip the "simple" databases, you'll have set specific databases) and directory locations.This might actually work for MS SQL 2000 too, though you'd have to change some data in system tables as some of them contain the name of the MS SQL Server.Wed, 28 May 2008 05:27:39 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxI'm looking for the same thing, only for MS SQL 2005. Has anyone got any ideas?Wed, 28 May 2008 04:34:01 GMTddonckhttp://www.sqlservercentral.com/Forums/Topic501078-5-1.aspxAfter installing of new SQL Server 2000 server I wolud like to avoid manual creating of DB Maintenance Plans that are created on another SQL Server 2000. Is it possible to do using any of the wizards that can generate SQL scripts or there is other way to move data into corresponding tables in msdb database?Thu, 15 May 2008 01:55:21 GMTmiodrag.sabljic