Outstanding PASS Volunteer

Hello Dear Reader!  In June this year I was recognized by PASS as with an Outstanding Volunteer Award.  In an unusual move I found myself speechless.  This is a very quick post to give the true credit where it is due.  June was a very busy month for me and I had a blog post scheduled to post it in July, but… let’s just say a couple things happened on the way to the theater.  The timing felt a bit wrong.

“So Balls”, you say, “Why blog about it now?” 

Great question Dear Reader.  The original blog was a big Thank You, and I want to say that in earnest.  The reason I received my award was for a lot of the work and presentations I’ve done this year. 32 presentations so far in 2014 with 9 more planned out and hopefully a couple more to come. 

These presentations don’t happen in a vacuum.  I’ve been very blessed as a presenter and community member.  I’ve written recently about how SQL Saturday’s impacted my life.  This year friends in the community invited me to new and wonderful locations.  Mike Walsh (@Mike_Walsh| Blog)  and 1/2 of my future law firm of Biguns & Balls Jack Corbett (@UncleBiguns | Blog) started out my January in New Hampshire from there I've presented physically and remotely in Florida, Puerto Rico, Massachusetts, Wisconsin, Missouri, Texas, California, North Carolina, Virginia, Oregon, Colorado, Georgia, with more before the end of the year.  That doesn’t include Webinars where people from all over the world signed up to attend.

If you attended, were forced to attend, dragged, cajoled, or otherwise persuaded to attend I Thank You.  Dear Reader without you it doesn’t happen.

For the organizers, who put in their time, put on events, get sponsors, volunteers, and manage the process Thank You.  My time to present is a pittance compared to the effort it takes to lead a group and the hours that you put in.  Many Thanks for letting me be part of your group.

To PASS, the volunteers (the literally HUNDREDS of volunteers from SQL Saturday’s, program committees, Leadership Staff, down to the volunteer picking up coffee a noble and thankless job), Chapter Leaders, the employees whom I see and send emails to for the Summit or SQL Saturday’s, and all of those who I do not see Thank You. 

Your hard work helps create an environment that allows our community to thrive.  There have been lots of fantastic winners already this year.  I encourage you to check out the PASS page of Outstanding volunteers here.  Chances are if you’ve been to an event this year one has touched your life.

A Quick late-CONGRATULATIONS to my friend Shawn McGehee (@SQLShawn | OPASS) who is the August Outstanding volunteer.  Shawn is the Chapter Leader for OPASS and has done an incredible job.  He is leading the charge for our Pre-Cons for SQL Saturday Orlando amongst many, many other things!  Way to GO Shawn!!

To my friend who nominated me, the beers on me.  To anyone who wants me to present and I haven’t presented for you. If I’m in your area or you need a virtual presenter, shoot me a message on twitter or an email at bball@pragmaticworks.com  I’m always happy to help.

ONE MORE THING

“So Balls”, you say, “You’ve said Thank You, anything else?”

Why Yes Dear Reader!  Yes there is one more thing, actually 4 more things.  I got something big that I cannot share quite yet.  In celebration of the community and the influence you’ve had on my life I want to give something back.

So in October I’m going to blog a community script a week.  These will be scripts that people have asked for, as I’ve presented that I never found the time to get out there, plus a couple new ones.  One a week leading up to the PASS Summit in November.

As Always Dear Reader, Thanks for stopping by.

Thanks,

Brad

Why You Should Go to SQL Saturday

SQL Sat Puerto Rico

Hello Dear Reader!  Soon SQL Saturday #318 in Orlando FL will be here.  The SQL Community does a lot of work at SQL Saturday’s, present at them, and help put them on.  When talking about them, one of the most frequent questions I get asked is: “Why should I go to a SQL Saturday?” 

Almost 4 years ago I attended my first SQL Saturday, attending was a last minute decision and one that has changed my life.  I have a real passion for SQL Saturdays, and while results may vary, my simplest answer is “they can be life changing”.  Here’s how I got there.   

Summit 2013 - Denny's awesome Party

The only SQL Event I had ever attended was the first 24 Hours of PASS.  I loved it.  I watched with eager anticipation, this was the first SQL training I’d ever been to.  Every company I’d worked for thus far had balked at sending me to training. 

I desperately wanted training.  When I discovered the 24 Hours of PASS I became a fan, FREE SQL Training on the internet!!! What a concept!  Of course it was to plug the PASS Summit, and if training was a no go you can guess what my chances of ever going to the Summit in 2009 were. ZERO.  This was as close as I could get, but closer than I’d ever been before.

So as the PASS Summit 2010 was gearing up there was another 24 hours of PASS.  I reserved conference rooms at my company, registered for the events, had a router for network connections set up, and I pumped up the “free” training to the other DBA’s. I worked for two days from there as the sessions were streamed. 

Jorge at SQL Sat Jacksonville

While talking with the other DBA’s that when the magic moment happened.  My friend Greg and my buddy Dan Taylor (@DBABulldog | Blog) said, “If you like the 24 Hours of PASS you’ll love SQL Saturday”.  What’s a SQL Saturday I asked?

A free event where Consultants, MVP’s, and SQL Community members set up tracks and have free presentations all day long.  I was stunned.  It was like I was a child hearing about “FREE CANDY” given out at Halloween for the first time.  Where was this? When was this? This weekend!  In Orlando!  I can do that!  I had to pay $5 for my lunch, but other than that no cost.  I almost felt like I was getting away with something.  As if someone would stop me at the gate and say, “Sorry Sir, you get to sit in the lobby only paying attendees get to see the sessions.”  It didn’t happen.  I got in just fine.

Tom Larock kicking
off SQL Sat OC 

It was everything I’d wanted.  Sessions on Wait Stats, PBM, CMS, Indexing, two deep dives one on partitioning and another on CPU!  I met DBA’s that understood my pains, issues with hardware stressed beyond capacity, aging relic’s with critical LOB apps that we couldn’t get new hardware for, 3^rd party vendors with bad indexes, bad code, and little support.  People trying to find a way to survive with NEW insights and experiences sharing openly and free.  People who understood my issues without having to pretend that they actually understood.   

I met Tom Larock (@SQLRockstar | Blog), Argenis Fernandez (@DBArgenis | Blog), Jorge Segarra (@SQLChicken | Blog), Patrick LeBlanc (@PatrickDBA | Blog), and 1 half of my future law firm of Biguns and Balls Jack Corbett (@Unclebiguns | Blog).  There were more.  Lot’s more.  That could take me pages more.  The point is I made it and it was like coming home.

Jason and Steve at SQL Live 360 

That day started it off.  Without Kendal Van Dyke (@SQLDBA | Blog), Andy Warren (@SQLAndy | Blog), Karla Landrum (@KarlaKay22 | Blog), and Jack putting on this SQL Saturday 49 I’m not here today.  

I submitted to be a speaker at the next event I could, I started a blog (you may be familiar with this one), got on Linked-In, and even got a Twitter account.  That event, that one SQL Saturday lead me to presenting at 7 more the next year.  

Getting a spot in the 2^nd chance track at SQL Rally, getting voted in by the community at the PASS Summit 2011, and being invited to be on the planning committee for SQL Saturday Orlando #85 the following year after I’d first attended.

Summit 2013 with the guys

At the end of SQL Saturday Orlando every year we stand up top of a stair case and throw out t-shirts and give away raffle items.  In 2012 Andy Warren looked at me while we were tossing out t-shirts and asked “How’s the view from up here?”  I grinned imagining about 50 different replies, but in the end it was a simple “amazing” that left my mouth.

My second job after college took me to Virginia.   A friend had recommended me for the position.  He met me at the airport, as I flew in for my interview, so I would see a friendly face.  I thanked him.  He told me “I showed you the door, you have to walk through it”.  He was right.  I did.  That job taught me a lot and led me new places.

SQL Saturday was the same way.  It showed me the door.  Walking through it brought me new acquaintances, some new friends, new ideas, to SSUG’s, the PASS Summit, Dev Connection in Las Vegas, SQL Live 360 in Orlando, two books, and a pretty awesome job at Pragmatic Works.

Summit 2013 - Karaoke at the Pragmatic Works Party

This is just the journey so far.  Funny how close yet far away 2009 feels. There is always the question, Dear Reader, of where tomorrow will take you.  We all start somewhere.  Everyone has to have the first time.  That brings us back to the question. 

Why should you go to SQL Saturday?  Because they can be life changing.  Hope to see you at one soon, click here to register for Orlando.

As always, Thanks for stopping by.

Thanks,

Brad

Deck & Demo's Live & Thank You AZSSUG & OPASS!

Hello Dear Reader!  Just a quick post to say Thank You to the Arizona SQL Server User Group and to my home town nortth Orlando user group OPASS!

This week I was very lucky to present Inside the Query Optimizer to the AZ SSUG and Performance Tuning, NOW! to OPASS.  I had promised to get my decks and demo's live and I wanted to do that.

Click Here for the Deck for Inside the Query Optimizer, and here for Demo's.

Click Here for the Deck for Performance Tuning, NOW!, and here for Demo's.

AZ to FL and back again.  I believe next week I'll just rest :).

Seriously Thank you to the wonderful SSUG leaders, Matt & Amy in AZ, and Shawn, Karla, and Rodney in my home town.

Without you this isn't possible!  And Dear Attendee's Thank You, if you have any questions please feel free to shoot me an email.

As always Thanks for stopping by.

Thanks,

Brad

SQL Saturday 232 Orlando Call for Speakers!

Hello Dear Reader!  We are at that time of the year again.  SQL Saturdays are popping up all over the country, spring is beginning to give way to summer, and the planning for SQL Saturday Orlando 2013, aka #SQLSAT232, is gearing up.  

This year our team of unbelievable SQL Server professionals  Shawn McGehee (@SQLShawn | Blog), Karla Kay (@karlakay22 | Blog),  SQL MVP Kendal Van Dyke (@SQLDBA | Blog), SQL MVP Andy Warren (@SQLAndy | Blog), and Ben Cork are working to bring you an incredible event.

Leading up the speaker track are myself and SQL MVP Rodney Landrum(@SQLBeat|Blog).  That brings us to you Dear Reader.  I need your help, I need YOU to submit to be a speaker for SQL Saturday 232.

“So Balls”, you say, “What is SQL Saturday and why should I submit to be a speaker?”

That my Dear Reader is the $50,000 question.  The short answer, let’s go make a little history together.

COME MAKE SOME HISTORY

What is SQL Saturday?  From its humble beginnings, SQL Saturday was started as a way to give back to the SQL Community.  The statement “give back”, is over used and often clichéd.  It is used to give meaning to deeds, when people have trouble justifying “why” they did something with greater substance than going with the truth and saying they did it because “it was popular to do”.   True justification rolls off the tongue like drops of rain water fall off leaves.   In this case, however, I find to “give back” fitting. 

The professionals that assembled saw value in sharing knowledge.  Turning hard learned lessons, production level victories, and new and unexplored concepts into group discussions and learning.  In the end it created an amazing support group.  We are one group of professionals, which have a particular job. Sometimes we are on a team, other times alone, the people we interact with outside of our field do not often understand our challenges.

The one thing I hear over and over again from first time SQL Saturday goers is, “I feel like I arrived at a home that I never knew I was missing”.  I share that feeling, it was how I felt on my first day.  My first was SQL Saturday 49 Orlando 2010.  You can see, I arrived late to the party. 

The very first SQL Saturday happened in Orlando on November 10^th 2007.  It was the brain child of Andy Warren.  There was only one SQL Saturday in 2007.  The concept was simple, get MVP’s, Authors, first time presenters from the SQL Community, and put on a FREE day of training.

Six Tracks, Seven different time slots, and a total of 37 different sessions throughout the day and we had our first one in the bag.  The very next Year saw SQL Saturday 2 in Tampa, followed by SQL Saturday 3 in Jacksonville, SQL Saturday 4 back home in Orlando, and SQL Saturday 5 (the first one out of Florida) in Olympia Washington.  Five SQL Saturdays in 2 years.  Last year in 2012 there were 50 in many different States across the US and 32 in other countries and US Territories, for a total of 82 different events.

Now it’s time for SQL Saturday to come back home.  Now it’s time for SQL Saturday Orlando.  Once a year we do this.  We come back home to where it all started.  It is fitting that Orlando is associated with attractions from far across the globe.  There is a magic here that draws us in (no not just the Mouse).  This is where it all began and this is your chance to be a part.  I wouldn’t want you to miss it!

WHAT DO I NEED TO DO?

If you want to give a talk but don’t know where to begin, read this blog I put together ( I WANT YOU to Present at SQL Saturday 85).  I discuss Abstracts, how to write one, and how to put together an author biography.

Once you’ve done that all you need to do is click here to submit, the call closes 7/23/2013 so get yours in now!

“So Balls”, you say, “I already see some SQL BAD @$$es like Tim Ford, Devin Knight, and Andy Warren have already submitted?  What chance do I have?”

Not just good, but a GREAT chance Dear Reader!  SQL Saturday not only has the goal of educating people for free, but we grow local speakers. I was in charge of speakers for SQL Saturday 85 and my mission was to give everyone a slot especially first time speakers.    Rodney and I have talked about this, it took longer to drink the beer than it took to set our guidelines.

I want you to succeed Dear Reader, and so does the rest of the SQL Saturday Team.  If you have the fire in your belly to go out and give a talk to a group of IT professionals, newbie or Pro, you cannot find a better venue than SQL Saturday Orlando.

I hope to see you there!  As always Thanks for Reading!

Thanks,

Brad

SQL Saturday 151 BI Pre-con: Stacia Misner

Hello Dear Reader, SQL Saturday 151 Orlando is picking up steam.  The Pre-Con’s have been named and they are fantastic.  SQL Saturday Orlando is always a big event, the schedule has been posted, and the planning is well underway.  First stop the BI Pre-Con Featuring Stacia Misner(@StaciaMisner|Blog) taking place at the beautiful Lake Mary Hyatt Place hotel.

“So Balls,” you say, “Who is Stacia and why should I attend?”

Great question Dear Reader, I work with a lot of really great people in the BI world, even though that area is not my forte, and everyone agrees Stacia is one of the TOP BI experts in the world.  She is one of the instructors for the Microsoft SSAS Maestro Program.  Stacia has been the author of over 12 different books on the subject of SQL Server.  

Her most recent is Introducing SQL Server 2012, available as a free PDF download click here to get it!  I first met Stacia at SQL Saturday #62 back in Tampa in 2011.  She had also written the book introducing SQL Server 2008 R2 and I had some questions about Master Data Services.  I was trying to figure out if Master Data Services in the 2008 R2 release was right for a project I was working on.  I asked her if I could get her advice and she was polite, candid, and very helpful.  Now it is your chance to get to meet Stacia.

A 360-Degree View of SQL Server 2012 Business Intelligence

One of the greatest things about SQL Saturdays is that the top SQL speakers and consultants will offer their training services for an unbelievable deal.  Stacia has taught BI Immersion Courses, Pre-Cons, and spoken at seminars that cost thousands of dollars to attend. You can attend her SQL Saturday session, and get one on one time with this expert, for just $99.   So now let’s look at the plan for the day.

In this session, we’ll take a holistic look at the BI features in the latest version of SQL Server by reviewing the architecture requirements, exploring the implications for existing BI applications, and introducing new capabilities that support the transformation of data into business insight. We'll start with data integration and management by reviewing the overhaul that Integration Services received in this release, how to formalize the data cleansing process by using the new Data Quality Services, and how master data management is improved with the updates to Master Data Services. Then we'll discuss the improvements to analytical capabilities by exploring updates to Analysis Services, including the new Tabular Model, and enhancements available in PowerPivot. Last, we'll cover the new presentation layer options available in Reporting Services and the new release of Power View. Of course, you’ll see demonstrations of the new features, but the primary purpose of this session is to give you a chance to ask lots of questions and to get a look “under the hood” to better understand what you’ll need to do to get these BI features up and running properly. You’ll also learn how to prepare your data environment to leverage these features and how best to manage the user experience.

If you are in a BI shop, or are looking to expand your career and get more in-depth in the BI field this is a great training opportunity.  Click here to sign up for the pre-con.  Click here to register for SQL Saturday 151 if you haven’t already.  I hope to see you there!  As always Thanks for stopping by!

Thanks,

Brad

Thank You OPASS! Transparent Data Encryption Recap

At the start of March I had the great pleasure of being the opening DBA shot for the OPASS meeting.  Microsoft MVP Ken Tucker was the main act giving us an inside look at Silverlight MVVM Framework.

Going to OPASS is like going to the Oscar’s, the stars in the audience are part of the treat!  Everyone had great questions, and they were very interactive.  Jack Corbett (blog | twitter) and Karla Landrum (twitter) had a wonderful venue, Rikka Asian Bistro, for the OPASS meeting.

During my Talk I had promised to post my slide deck and the script that I used for my Demo, so I wanted to do that right off the bat.

Slide Deck – OPASS Transparent Data Encryption: The Lightning Round

Demo for the presentation

DEMO RECAP

So Dear Reader during the talk we covered a couple things.  We covered Why you should use Transparent Data Encryption (TDE), How to setup TDE, how to Disable TDE, and the effects TDE has on your backup and recovery Strategy, the slide deck and demo’s are attached so I’ll let you go through those on your own I’m just going to cover things from a logical level.  We’ll step through each of those, and then we’ll get to some advanced scripts on how to self manage certificates for TDE.

WHY?

Here is a slide from my deck, and this is the best reason why, if you are a DBA long enough eventually you will find yourself in a situation where a backup is lost, something will get stolen, or something will get hacked.  TDE will not save you from every scenario, but what it will do is make so when you are in this situation you have one more level of protection. 

“But Balls,” you say “This only covers your back-side as the DBA, why would my Boss care about this when it’s me on the line?”   

Well Dear Reader yes it is our back-side on the line, but so is our Boss’s, and the companies.  If you take the point of view of Jerry Macgwire “help me, help you. HELP ME, HELP YOU!”. 

Don’t go and yell at your boss, but you should know them well enough to know to find an approach that they will respond to.  If its ROI, bring up how much companies lose in money because of incidents like these, and future loss of customers profit if customer’s confidence in the business is shaken. 

HOW TO TURN IT ON

Get your minds out of the gutter J.  Turning on TDE is very easy we just follow 4 easy steps

1. In the Master Database Create a Master Key
2. In the Master Database Create a Certificate
3. In the User Database to be Encrypted Create a Database Encryption Key
4. Alter the User Database and Set Encryption On

It’s that easy.  After step 4 an encryption scan will begin on the database.  It is safe for user activity.  The only things you cannot do is alter the database at the Filegroup level.  No dropping, no adding.  While you are encrypting your database, It’s probably not the best time to be doing changes to the underlying file structure, (one thing at a time!).

Keep in mind that the second the 1^st user database becomes encrypted with TDE the TempDB will be encrypted as well.

TURNING IT OFF

“So Balls”, you say, “I turned TDE on my DEV system, did a demo for my boss, we’re not going to use it, how do I turn it off!”  We’ll Dear Reader it is pretty simple.

1. Alter All user databases and Set Encryption Off
2. Drop the Database Encryption Key for each User Database that was encrypted
3. (To Unencrypt the TempDB)-Restart the SQL Service
4. *DO NOT DROP THE CERTIFICATE

BACK THAT THANG DATABASE UP

When you turn on TDE it affects all Datafiles at rest.  All Data Files, Log Files (only the VLF’s utilized while TDE was turned on), snapshot files, and backup files.  So here are the things to consider.

If you turn on TDE on your Prod system, and you want to do a data refresh to your Dev or Staging environments you will need to restore a copy of the Certificate used to encrypt your Prod databases to your Dev or Staging server where the restore will take place.

If you want to use Log Shipping or Database Mirroring you will need to have the Certificate you use on your Primary or Principal Server on your Secondary or Mirror Server.

So if you have a Database using TDE, if you are using a technology that uses Restore and Recovery then you will need a copy of the Certificates on each SQL Instance where a copy of the Database will reside.

Now keep in mind this doesn’t mean you can’t move data around.  TDE works with data at rest.  That means that if you BCP data out of the database it is in an unencrypted state.  You don’t need to have a copy of the Certificates in your destination. 

So how does this effect your recovery strategy?  You need to backup your Certificates regularly.  You need to make sure that you plan for them like you do your backups.  If you sweep your backups to tape you’ll want to sweep your certificates to tape.  If you back up your databases to disk, backup your certificates to disk.  Keep redundant copies.  If you ever have to restore your database to a different server you will need them. 

I’ll be doing covering Key Management in my next post but all of the scripts you will need are in the download.

Each of these scripts need a little tweeking, but all the information is there.

Local DB, function, table, and encryption.sql (Sets Up a local DB, and TDE, to store passwords used in later scripts)

backup tde certificates.sql (T-SQL Script to automate certificate backups)

delete old TDE certificates.sql (T-SQL Script to automate certificate clean up)

Hope these get you on your way to learning how to use TDE!

Thanks,

Brad

OPASS: Transparent Data Encryption the Lightning Round!

I’ll be presenting at OPASS on March 8^th at 6:00 pm CLICK HERE to register to attend the meeting.

This is just a preview presentation so I won’t be deep diving the topic, we’ll just be scratching the surface.  So I’ll start by talking about Transparent Data Encryption, what is it?

WHAT IS IT?

Let’s break down the name into its three parts.

Transparent-The process is Transparent to everything going on in the server.  When you turn this on you do not need to make any changes to the way an application access the data.  As far as the Application knows this is the same database it was before it was encrypted.

Data-The data itself is affected. “So Balls,” you ask “’The Data’ is a pretty wide open term, what do you mean by ‘The Data’?”  Great question!  Any way in which SQL Stores Data is encrypted when Transparent Data Encryption is turned on, for the Database that it is turned on for.  This is a Database Level Technology similar to how Mirroring is a Database Level Technology.  Your Datafiles are encrypted and your Log File is encrypted  (VLF’s are encrypted starting when encryption is turned on, VLF’s previous to that will get encrypted when they are overwritten).

Encryption-There is some flexibility in the type of encryption Algorithm that you can select, during my demo’s I’ll be using AES 256 for my encryption level.

Need a little more?  Okay on to the next analogy.

PHYSICAL DATA SOLUTION

Seeing as how I’m a comic book kind of guy, the best analogy I can give is the X-MAN Colossus.  Transparent Data Encryption is a Physical solution, your .MDF, .NDF, .LDF, .BAK, .TRN, and .DS (Snapshot files) will be encrypted on the disk while at rest.

So on the outside your armor is on, inside (when data is in motion) however your flesh and blood.

DATA IN MOTION

“So Balls,” you say “What is this Data in Motion?”  Well Data in Motion just means that the data has been fetched from Disk by the Storage Engine, and is cached in Memory.  So while this is Transparent to all the applications that point to your database, you must keep in mind that SQL Server is an application as well.  And if you have access to read the data before you encrypted it, you have access after.  If you are looking for Transparent Data Encryption, TDE, to provide complete row level encryption after the data is fetched, then you need to be looking at Column level encryption.  TDE will only encrypt data at rest on the disk.

WHY USE IT

This is the Fifty Million Dollar question, and I’m going to give you a preview of my slides.

When Data is stolen it effect’s not just you the DBA, Your Customer's Identities, your Businesses reputation, and the financial toll to your Business as well.

Nobody wants to be the DBA in any of those headlines.  I’ve been in the situation where there have been security breaches, and every single gap you can cover is one that you are glad you did.  If your server is breached  you could find yourself in a room with a CIO, VP’s, Directors, Civilian Leadership, a General, Base Commander, Congressional Committee,  your Project Manager, or anyone one else in a position of authority.  In that moment what would you like to say?  My database(s) had Transparent Data Encryption on them, or we passed on having TDE on the server for [name the reason, and  you WILL really need to justify it].

 Sometimes this won’t be up to you.  You can recommend it and your business can choose to pass.  All you can do is be ready, because if you stay in the business long enough eventually you’ll be in this situation, *IF you’re lucky enough to catch the breach.

In the presentation we’ll cover what a datafile looks like in an unencrypted vs. an encrypted state, how to turn TDE on and how to turn TDE off.  How it effects the Tempdb, backup compression, and some tips and scripts for self managing TDE Certificates.

I’ll have a follow up post later in the week after the presentation.  I hope to see you there!

Thanks,

Brad