How Do You Query Maintenance Plan Package Metadata?

 Hello Dear Reader!  Since I’ve joined Pragmatic Works I’ve learned a lot, seen a lot, and assisted in interviewing a lot.  One of the things I’m often asked in the interviewing process is, “What is the best thing about working for Pragmatic Works?”   My answer is always the same, the People. 

We have some really brilliant people, and when you get one of us to work with you get a whole crew backing them up. 

  I have folks like MVP Jorge Segarra (@SQLChicken | Blog), Kathi Kellenberger (@AuntKathi|Blog), and MVP Jody Roberts(@Jody_WP|Blog)  and many others that are a phone call away.

 We have a DL, Distribution List, for out BI Geniuses, the DBA crew, and for many other incredible areas our business covers.  So today my buddy and fellow DBA Chad Churchwell (@ChadChurchwell | Blog) tossed out a question to the DBA DL, “Has anyone ever found a way to query the maintenance plans in SQL Server… they are nothing more than SSIS packages, but I am trying to go is find a way to programmatically get the backup location of the backup database task within the maintenance plans.”

“So Balls”, you say, “You figured this out on your own, and answered the question?”

Excellent question Dear Reader!  No, I didn’t. 

The answer was a great collaboration, I got a little bit, MCM and MVP Jason Strate (@StrateSQL | Blog) ran further with it, and Chad came back and delivered a great script.   To cut to the chase go read Chad’s blog with the final script here.

HOW’S IT MADE!?

http://ratemydesktop.org/pm/78H0/yay_science.html

When you make a Maintenance Plan in SQL Server it saves it internally as an SSIS package.  Instead of browsing through the tables in the MSDB database I opened up profiler and clicked through the GUI.  I created a folder on my C:\ called FindMe and another named FindMe2.

I then let the trace run as I created an Maintenance plan to backup AdventureWorks2012.  I saved it.  Closed it and then reopened and modified the folder path.  

I started looking through the profiler trace for the folder text.  I figured it would be passed through as a parameter.  It wasn’t.  Instead I found a call to msdb.dbo.sp_ssis_putpackage.  

Parameter @P6 for sp_ssis_putpackage was a rather large hexadecimal blob.  Using sp_helptext I ran the following script.

sp_helptext 'sp_ssis_putpackage'

The output showed me that @P6 was part of @packagedata and it was saved as column packagedata, an image data type, in table msdb.dbo.sysssispackages.  I queried the column I translated FindMe2 to hex, 46696e644d6532. I searched the package for the hex string and found it!  So I knew that I had arrived at the right place.

*(If you want to check my hex take each number as a 2 number pair, ie 46 translate to binary 01000110 then translate the binary to ascii = F 69=i 6e=n 64=d 4d=M 65=e 32=2, FindMe2).

I first cast the  image to a varchar(max), and lo and behold it was actually in XML format!  So I wrote this query.

SELECT name

      ,CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource

      ,packagetype

       ,id

      ,description

      ,createdate

  FROM msdb.dbo.sysssispackages

This gave me an XML document I could see that contained all of the metadata.  I passed this around to the collective brain trust and Jason Strate immediately responded with an XQUERY to get the data Chad needed.

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS

, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)

,ssis AS (

    SELECT name

        , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package

    FROM [msdb].[dbo].[sysssispackages]

    WHERE name = 'MaintenancePlan'

   )

SELECT s.name

    ,c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'NVARCHAR(MAX)')

FROM ssis s

    CROSS APPLY package.nodes('//DTS:ObjectData') t(c)

WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1

Chad then took it to the next level by writing the script to be compatible in SQL 2005 and SQL 2008 and up.  To see the final version though go check outhis blog on it!

WRAP IT UP

Every place that I’ve worked the people made the difference.  Having a solid team to bounce ideas and questions off of not only helps you grow, but it helps them as well.

So a very cool day collaborating with my fellow DBA’s, and it netted a script and some background info I thought you might be able to use.  

It was a lot of fun for me to learn, and I got a new script to toss in my tool box.

Until next time, Thanks for stopping by, and make mine SQL!

Thanks,

Brad

Automate Deploying Maintenance Plans in SQL Server 2005, 2008, and 2008 R2

Have you ever set up a SQL Server before? If you’ve landed on this post, chances are yes you have! And for most of us we haven’t set up just one SQL Server Instance we’ve set up SQL Server many, many times.  When you have to do a repetitive task you figure out the best way to do the task as quick as you can.  For DBA’s this normally means automating the process, setting up a SQL Agent Job, a Scheduled Task, or using a 3^rd party scheduling software because we’ve normally got other fires to put out.

So when you set up a new server what are some things that you automate?  Normally you have a standard set of Jobs, Alerts, and Maintenance Plans that you want to have in place.  You document them, you put together master Scripts that you will deploy so you don’t have to create them by hand.  But that doesn’t work for Maintenance Plans.

"So Ball's", you say "So I have to make them manually, big deal."  Ahh Dear Reader when your piecemealing them one at a time, it may seem like no big deal.  But let us say that we are upgrading HUNDREDS or even THOUSANDS of servers.  Your going to want to skip as many steps as possible.

I'VE FOUND A NAME FOR MY PAIN AND IT'S BATMAN MAINTENANCE PLANS

If you Create a Maintenance Plan, let’s make a Simple one, and try to script it out you’ll notice you can’t.  So if you take a look at the tables that store the Maintenance Plans In MSDB, you'll find that the Maintenance Task is in varbinary(max) format.  You can try scripting it out, but that is the old fashion way.  

Sometimes we as DBA's get used to doing things the old fashion way, we need to get with the times.

Embracing the New Hotness SSIS!

There's a New Game in Town!  If I imported an SSIS package to your server, and you wanted to place it on another server, how would you go about this Dear Reader.  Why you'd use SSIS!  Scripting out jobs is so SQL 2000, we've grown and so has our technology, so let's treat a Maintenance Plan like we should, it's an SSIS package and we shall use SSIS to migrate it.

If you look closely at the picture you will see I have a SQL 2005 & a SQL 2008 R2 instance on my laptop.  For this demo, I will create a Maintenance Plan in 2005, and using SSIS Export the Maintenance Plan to my 2008 R2 instance.  

Step 1 Create the Maintenance Plan

Name the Maintenance Plan

This will be a simple plan with two subplan's, Integrity Check and Update Statistics.

Let's Save the Maintenance Plan and verify that it created SQL Agent jobs for each subplan.

Step 3 Let's log onto my local SSIS instance (*This could be a remote instance SSIS doesn't have to live on the server where you created your Maintenance Plan)

Because this is local I don't have to Import the Maintenance Plan it is already in my SSIS server stored under Maintenance Plans, and it will be titled what ever you name it.

Step 4 Export the Plan to the desired location

Type in the Server Name that you want to deploy your package to.  Click on the ellipsis button

Click on the Maintenance Plan folder, Click OK

And Click Okay

Here is a before, so you can see there was not Maintenance Plan.

And Here is the After the Maintenance Plan is in place. 

But Wait There's More

If you take a closer look Dear Reader you will find that your Schedule and your SQL Agent Jobs have not been created.  You must manually re-create these.  

No Schedule

And No Agent Jobs.  Creating the Schedule will take some clicking, but the Agent jobs will be created as soon as you open and save the Maintenance Plan.

Plan Owner

The Plan Owner will end up being the Windows Account that you authenticated to SSIS with, in order to set the Maintenance Plan Owner as SA you should run the following script.

SQL 2005

use msdb;

go

update dbo.sysdtspackages90

set ownersid = (

select sid from msdb.sys.syslogins where name = N'sa')

SQL 2008 & 2008 R2

UPDATE msdb.dbo.sysssispackages

SET [ownersid]=0x01

I hope you find this helpful, and it get's you along the way of standardizing your Maintenance Plans in 2005, 2008, and 2008 R2.  (In a couple month's I'll be redoing all this for Denali!)

Thanks For Reading,

Brad