Game on, Speaker Idol at PASS Summit

This week I am attending PASS Summit 2014 in Seattle, Washington. PASS Summit is  gathering of SQL Data professionals from around the world who come together to learn more about our craft.  As most of you reading this site will know, I enjoy sharing and speak at the regional SQL Saturday events.  At some point I would like to be able to challenge my self to speaking at a broader audience, and while not an official speaker, I have been selected to compete in a competition for new speakers while at Summit.

This competition is known as SQL Speaker Idol.  It will be held in the style of the American Idol singing competition, fortunately for everyone, no singing will be involved.  Instead, 12 speakers will be selected to  present a 5 minute talk on  subject of their choosing, with judging to be done by an expert panel.

So not only will I be speaking, folks will be actively judging me and telling me how terrible I am.  On the bright side, I have had lots of support from my local user groups.  Both Pinellas SQL Pass and Hillsborough SQL Pass User groups have allowed me to give my presentations at their users groups over the last few weeks.  I’ve had comments and suggestions from SQL MVP and MCM folks who have been presenting for years, so I feel somewhat prepared and very well supported.

Luckily my session in on Friday morning, so I have a few more days to prepare, and prepare I will.  I have asked a few folks to allow me to take a few minutes of their time over the course of this week to present the session to them and get more feedback; an audience is always better than jabbering away to my self.

So there it is folks.  For anyone at PASS Summit, come over to the Community Session room 400 on Friday at 10:15 to cheer me on.  I’ve already put the Speak Idol Final on my calendar as I am an optimist.  If you’ve got the same faith,  put the final on your calendar as well, same room at 2:45.

Second Year Presenting at SQL Saturday Orlando

SQL Saturday 318 Logo

Coming up on my schedule is another appearance at SQL Saturday Orlando.   Orlando is quite special to me as it was my first ever presentation, and their team is quite welcoming, and just plain awesome.

Looking at the line up this year, I’m quite excited to see a few of my favorite speakers Louis Davidson ( b | t ), David Klee ( b | t ) and none other than Mr. Argenis Fernandez ( b | t ) coming in to share their wisdom.

For those of you local to the Tampa area, you might recognize our veteran speakers Pam Shaw ( t ) ,  Miguel Cebollero ( b | t ),  Jeff Garbus ( b ), Ron Dameron ( b | t ), as well as our most recent contribution to the Microsoft team Jorge Segarra ( b | t ).  A locally recognizable, but new on the speaker list is Pinellas PASS User Group Leader Leigh Freijo ( t ) with a presentation on how to get hired.  Having been through one of Leigh’s interviews (successfully!), and having heard some of his interview tales, I can say that he has some insight on what will get you hired.

As far as my presentation, I’m working on a new one titled: ‘Encryption 101: A look at SQL Encryption Options’ which I hope will be as well received as my previous presentations.  We’re going to take a look at TDE, SSL, Column and even backup encryption options so safeguard your data. I’ve been really heads down in the details on this one and need to start getting it all together in a palatable 101 type presentation.  We are a few weeks out so I still have time to smoke test it on my wife.

If you haven’t already, get over and register. Its set to be a fantastic day of training as it has been for the last 8 years.

SQL Downgrades Made Easy

I was reading Kendra Little’s ( b | t ) post on SQL Server Edition Changes and it reminded me of the work I did at my last position.   I did LOTS of downgrades, seriously, LOTS.

When we started auditing our systems we found TONS of Enterprise and Standard Editions being used in development and QA environments.  In the past, Microsoft gave us a pass and only cared about what was being used in production, but this year they indicated that not to be the case, so a remediation was in order.

Since I had done a good deal of work with Michael Well’s ( b | t Scripted PowerShell Automated Deployment Engine  (SPADE) for new server setups, I was the best candidate to handle the job.  I lost count of the total number of downgrades that I did, but I have a few dozen pages in my notebooks that look like the one below.  This page would have been one night’s work.

Spade_Downgrade_Checklist

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The process which got me through a few hundred downgrades was fairly simple:

  1. The first step of downgrading is copying off your system files. If you are not sure where your system files are you can simply view the Start Up Parameters of the SQL Service.  I usually copy this value to a draft email or temp file for reference, just in case.  Once I find the master I copy the master, model, and msdb database to a temp folder.   If you skip this step, they will be deleted during the uninstall. Don’t ask me how I know this.
  2. Now that we have the lifeblood of our instance saved safely away, we can proceed with step 2, uninstall, which is just as it sounds.  Most of the time I will simply run the uninstaller and select everything as there is nothing better than a fresh install.  If you have SSAS or SSRS there are extra steps to safely migrate those services, which aren’t covered here.
  3.  By default, when SPADE is run, it copies all of the install media over one time and sets a file indicating that the media is in place.  If you run SPADE a subsequent time, it will see the file indicating the media is already copied locally and skip that step completely.  If you’ve used SPADE in the past this would result in you installing the previously installed version, again.  So prior to running SPADE, ensure you have deleted the install folder if it is there.
  4. Once SPADE has done its thing, installing everything, checking all the standard settings etc, we need to shut SQL down and move our old system databases back into place.  If you installed your INSTALLSQLDATADIR in a different location than you had prior, you will need to place your old system files in the new INSTALLSQLDATADIR location, once done, start the SQL Service again.  If all went well, it should start right up with no issues.
  5. In some cases, we were actively changing the names of the instance being downgraded, and this would require us to remove the old SPN entry and add a new one. If you get linked server issues with AD logins, you likely forgot this step.  As you can see from my checklist, there aren’t any checks on this column, as this was not the norm, but normal enough that it had its own check-box.
  6. If you are going to be industrious as I was and perform multiple downgrades at the same time, I recommend that you pre-stage the install media prior to your actual downgrade window.  SPADE has a -PRE flag that will allow you to run all the pre-checks without doing the actual install, this will stage your media locally, which will save you some time during the actual work. This check box was for just that, one check would be for version, other services (SSRS, SSIS, second instances) that would require more investigation, and I forget what the other check-box was for.

All in all, I was able to knock out this process multiple times, over multiple nights spread over a 2-3 month time frame.  Any thing that came up out of the ordinary during downgrade time I would simply skip that server and knock out the remainder.

One thing to note is that all of the servers that contained SSRS databases had to be coordinated with the team that ran those servers as the SQL Engine and SSRS engines have to be the same version, otherwise it wouldn’t operate.  Another gotcha, was SCOM I believe.  Luckily upgrading to a higher version has been a built in option of SQL Installers since 2005 I believe, a quick manual run of the install media in your SPADE folder and it can be upgraded in place, less than a minute in most cases.

This had been my experience, yours may vary.  If you have a large environment and find yourself doing builds fairly regularly, I highly suggest checking out SPADE, it can automate nearly all of your install and standardization tasks right out of the gate. Quick and Easy!

 

Export XML Columns to Individual Files

I was reading SqlServerCentral.com Articles Requested forum and saw this request.

Looking for an article that takes data from an XML column in a table and exports each field as a separate document.

While the request did indicate a PoSH or SSIS method, I wanted to give it a try using T-SQL. Lets look at how I managed it.

 

The Setup

To get started, I do some requisite cleanup, declare a few variables and create a simple table to hold our XML column that we will be exporting.

For data, lets query some of the system tables. In this case I’m going to create one XML document for Databases, Users, and Jobs.

 

And finally, we can use BCP to get our columns to disk:
 

Using this procedure we can pass in a document name, and the location we wish to save the XML file and get our results by calling the procedure like this:

Which results in three new files sitting in our C:\Temp directory:

Xml_output

The contents of those files are simply the XML we have stored in the XML typed document column.

XML_JobOutput

Things to Think About

This approach is demonstrating the basic mechanics of how it would work, but there are a few things to keep in mind. Many DBAs will restrict xp_cmdshell, as they rightly should, as it can be very dangerous in the wrong hands, but with proper precautions and monitoring, it is a valid tool in your tool belt. One final thing to think about is permissions. When xp_cmdshell is run, it runs as the SQL Server Service Account, unless configured more precisely with sp_xp_cmdshell_proxy for instance. Which ever account that is being used will need permissions to write to the @Location you provide.

Comments, Suggestions

This is my approach using T-SQL, I’d love to hear feedback on my approach or holes in where my approach might be wrong. Thanks for stopping by.

Tampa Code Camp Revivial

TampaCC_Main

If you were to look around Florida at the various cities technology based User Groups, and their accompanying local conferences you will see some of the best in the land.  Orlando started SQL Saturday since 2007 with Tampa following closely thereafter.  South Florida has hosted Code Camp for 10 years now, Orlando recently held their 9th annual Code Camp.  One thing you have not seen  is a continuous Code Camp in Tampa.

One would think that given our rich community of .NET Developers, Mobile and Web, Server and Desktop we could pull together an annual event, but we as community have dropped the ball over the last few years, until now.

This past weekend, I had the pleasure of volunteering and presenting at Tampa Code Camp 2014.  Greg Leonardo ( b | t ) and his lovely wife Kate, have put a lot of work into breathing life back into the Tampa Code Camp.  I was not privy to the attendance numbers, but the website says 156 folks were registered, which is a good start for a revival year.

While the lead up time between the event announcement (early June from what I can find)  and the event was very short, the event was quite nice.  There were three on-site vendors, which made getting signatures for the raffles quite easy.  The event was held at KForce’s national headquarters here in Tampa.  Since they are a staffing firm, they prevented any other staffing firm sponsors from being on site, which was likely a limiting factor to other sponsorship.

Attendees were greeted by volunteers and directed to a spread of Dunkin Donut’s and coffee before the keynote at 830.  The official start of the day was lead by local speaker extraordinaire Kelvin McDaniel ( b | t ) who shared his keynote on the topic of Community.  Afterwards, attendees were split into three morning sessions across three tracks, Web, Mobile and .NET Misc.

Lunch was a nice boxed lunch from Firehouse subs, with four sessions after lunch.   I had the last session in the .NET Misc track.  Considering the number of attendees, there was a decent turn out in my session,  with good questions throughout and lots of follow-up since.

Per the norm, the day ended with a few words from Greg, and a few giveaways.  All in all I was quite impressed with the output of their efforts.   I have some ideas that I’m going to share with them for next year and hopefully,  we can make it better than ever.  In the end I hope that the various obstacles preventing this event in the past years have been banished for good.  Tampa has a solid tech community and can easily support this event in the future.  With Greg and Kate leading the charge, I don’t see any way but up from here.  Kudos to you both.

 

For those that attended my presentation, the scripts are below.

[download id=”449″]