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

Pro SQL Server 2012 Practices

Hello Dear Reader.  Every now and then professionally you get to be a part of something really cool.  Back in the spring of 2012 Jonathan Gennick of Apress contacted me about participating in a book.  His idea?  Get together a lot of really amazing SQL Server Professionals in order to write on subject they were passionate about.

The list of professionals involved was one that made me immediately want to own the book.  When I got the chance to help I jumped!  I wasn't alone.

The companies whose employees collaborated on this book come from Red Gate Software, SQL Skills,Brent Ozar Unlimited, Pragmatic Works, and many others.  They are MVP's, MCM's, and regular old SQL Community folk like myself.

We all decided to write blogs reviewing/describing each others chapters.  I will be listing and updating them here.

If you're interested here is a link for the book on Amazon.  Big Thanks to Apress, Jonathan, and my fellow Authors!


Reviews/Summaries

Jes Schultz Borland @grrl_geek

1/14/2013   Book Review: Pro SQL Server 2012 Practices Indexing Outside the Bubble


Herve Roggero  @hroggero

1/15/2013 Chapter Review:  The Utility Database (By Chris Shaw) and how it applies in Cloud Computing 

I'll be updating this page on my blog daily as we crank out new reviews.  All the authors have pitched in and we'll be writting them out one day at a time.  Tomorrow SQL MVP Chris Shaw (@SQLShaw | Blog) all around awesome guy will have his review  on Compliance and Auditing. Thursday the Dr.  of Database Design himself SQL MVP Louis Davidson(@drsql | Blog) is up on Release Management.

Hope to see you then!

Thanks,

Brad

Dude Where Did my AD Account Go? Troubleshooting Duplicate SID's.

Hello Dear Reader!  I've been working on Setting up a Virtual Lab in using Virtual Box on my work laptop.  I originally thought of blogging through all of this but I've had a few issues.  I stepped away from this project at one point in time and was using a personal one.  However I really want to get this work on my Pragmatic Works laptop.

I may go back and do a step by step once I've got my feet underneath me, but for now I'll just be happy to have this up and running.

I've finally gotten my Domain Controller Online, Added two Servers to my Domain, and then I wanted to add a Domain Account as an Admin to each Server.  

So I logged onto the sever using the local admin, opened up Server Manager, opened up the Configuration tree, Opened Local Users and Groups, and Clicked on Administrators.

As I expected only the local admin account and a local SQL Server Account, that I'll be replacing with a domain account.

So I click add, type in SQLBalls, Authenticate to my domain to get the account added, and everything looks good.  I hit OK.

Hey where'd my AD account go?

So I went through the whole process again.  Click Add, added SQLBalls, validated against the Domain Controller, and then I get this error.

Well if my account is already in the group, then why isn't is showing up.  So I turn to trusty old DOS and open a Command Prompt Window and run net localgroup "administrators".

Sure enough my domain account is not in there.  It didn't take much searching on the Intrawebs to find other people with my issue.  After a little while I found people encountering this error when they had Cloned a VM.

The Domain Controller and the VM had the same SID's, Security Identifiers.  So to validate this I went and grabbed the handy sysinernals tool PSGetSID, to get this click here.  

Once I had downloaded this to my software share I put it on my Domain Controller and one of my other Servers.  I extracted it to a folder called PSGetSID, I know *how original*, navigated to the folder, and typed in psgetsid.

Now that I know what my SID for my Domain Controller is I need to find it for my other computer.  I extract the files and run the command and VOILA!

Sure enough I have duplicate SID's.  If you notice up at the top the Account name has a SID after it, before I click OK and it disappears.  That SID is the same one as both of my computers.

STEP 1 FIX! STEP 2 IT! REPEAT STEPS 1 & 2 UNTIL IT'S FIXED! 

So duplicate SID's are preventing me from adding one AD account to other computers on my domain. I had set up an image of Windows 2008 R2 that was my base image.  I had been keeping the widows update current, but I left it pretty much alone.  I would clone it before I taught a class, did a presentation, or experimented on really f***ing up doing non best practice things on my computer.

So Cloning the same image to make my Domain Controller led to this error.

"So Balls", you say, "How do you fix it?  And isn't there a better way to do things."

Yes Dear Reader there is.  I was saved by this blog by Ilija Brajkovic.  There is a tool called sysprep.  I should have run that before to clean up my base image before cloning it.  Now I can use it to change my SID.  I start out by pulling up run and typing in sysprep and click OK.

It will open up sysprep in it's windows folder.  I then double click on the sysprep.exe in order to launch the application.

Now that sysprep is open I make sure OOBE is selected, I need to click Generalize in order to generate a new SID, and I will also select Reboot.  Then Click OK. This ran very quickly for me.

As Ilija notes there will be some additional information to enter on reboot.  Note *I SHOULD HAVE DONE THIS RIGHT AFTER CLONING*  After reboot You will get prompted for the language choice.  *Warning this will reset your image to a factory setting, if you already have SQL Server Installed this will erase the instance.  This will detach drives, this will reset your TCP/IP Settings.  DO NOT DO THIS IF YOU DO NOT WANT TO WIPE CLEAN YOUR VM*

Then you check the box to Accepte the Agreement.

Then wait while your settings are finalized.

When I log back in my VM has been reset, hence the Enter System Out-of-Box Experience.  The software I had installed is still there. But I'm no longer on the domain, my computer name is changed, and hopefully my SID is different.  Lets run PSGetSID to validate that.

Excellent!  I've got my new SID.  I need to set my NIC card again to be on the right network, rename my server, add it to the domain, and reboot.  After that I can go back into the setup for my AD groups and add my User Account.


This time when I click OK it doesn't go away.  Alright Dear Reader, I hope you enjoyed this one, it was a lot of fun to figure it out!

Thanks,

Brad