SQL Server and Other Topics

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014.
I specialize in Performance Tuning, High Availability and SQL Development.
Contact me through this site or through my Twitter handle @SQLDiver

Introduction

'm continuously mentoring enterprise companies in how they can simplify their lives. The worst comment I hear on a regular basis is, "doing it manually is why I have job security". ARGHHHHH! No it is why you can be replaced in a heartbeat.

So DBAs, wake up! If you do the same task, manually, over and over, you're doing it wrong. When do you have time to study and play with the new features? When do you have time to watch your Pluralsight videos? How do you improve your skills... when you have to take all of your time doing the same thing manually when you can automate it??

Over the next blog posts I'm going to give you some tips and tricks and how to's for automating your installs using PowerShell... the easy way.

See, I didn't really need to re-write his blog post. Short and sweet and how I have always started by unattended install process at every employer and now client since the first one.

The rest is tweaking the passed in parameters from command line or PowerShell script. I highly recommend you pass in the passwords for install. You can save the admin groups in the configuration.ini file.

Its what follows that takes some work and PowerShell to configure... but, if you have a few PowerShell modules installed and everything is much easier.

Modules you will need to install on your "management server". Click the link for each of the following for instructions on how to install:

It has been more than a year since I blogged a word. Half of that is due to too much going on and half of that is due to wanting to move my blog to Microsoft.

So let me start on the second issue, Microsoft is working to get their blog sites under "control" and decrease the amount of administration effort by Microsoft employees. That means that at this moment they aren't creating any new blog sites. It also means I procrastinated just enough time to totally miss the boat.

Now the second part. I've spent 26 weeks in Silicon valley, 20 weeks in Chicago, 4 weeks in Boston, 4 weeks in Philly and now Charlotte. During that time I moved twice, once to our condo in Seagrove Beach, FL and 5 months later into a house in Inlet Beach, FL. We still have a garage and a 10' x 20' full of boxes. Yes, we bought a house with about half the available square footage (including the basement in the original house).

What have I been doing? I completed several Availability Group POCs, multiple "health checks" with hundreds of servers. A production hybrid AG and lots and lots of training.

I've put together a CosmosDB presentation that I've given several times internal and external. I'm also putting together a CosmosDB demo as well as a Managed Instance presentation and demo.

In coming weeks, months and years, I'm going to try to blog more of the tips and tricks that I'm using to build the AGs along with a lot of PowerShell code to improve your scripts.

Now, what most of you want to hear. Yes, Microsoft is an awesome company to work for! Yes, I still can't believe that I work for Microsoft. The management so far have been amazing, it doesn't mean that there aren't those that forget about the work life balance. I'm lucky that my last two bosses, Shelly and Matt, have been great managers. I've been able to meet all of the management up the line between me and the 5th level above me, but a new one, and they've all been amazing. I actually had one of the senior managers ask to meet me.

All in all, I should have joined earlier. Yes, I can make more money as an independent, but then I have to do so much more work.

Ed Watson (b|t ) and I will be presenting our full day pre-conference session on performance tuning. This session, named Performance Tuning for Mere Mortals will take the attendee through basic configuration for performance, troubleshooting performance as well as some performance tuning.

We will be taking a beginners look at execution plans as well as many other great free tools for performance tuning.

I have really enjoyed being an independent consultant, even in the down times, because it gave me the freedom to help my wife with her business. Being independent means I have to go out and find work, which is more difficult than you would expect, which is why you have to have a great network. When I started my network was fairly small. After three years of speaking at SQL Saturdays and PASS Summit, my visibility, friendships and network have grown.

I believe this year as an independent would have been my best ever, but an opportunity knocked on my door that I had basically given up on.

For the past three years there has been one company that I've wanted to work for more than being an independent consultant. Yes, Microsoft. I've interviewed with Microsoft three times in the past three years. The first time another person with better BI skills was chosen, it was a a little depressing, but definitely understood. The second time the interviewers were the same as last time and all of them were very positive about my skills from the last go round. I expected to get this position due to their feedback until I found out it was the same day as a large amount of layoffs.

This time it came out of the blue from a friend at Microsoft that forwarded my info to the recruiting staff. A couple of days later a phone screen was scheduled. The phone screen was pretty simple (some of the questions were silly). The recruiter called me immediately after to schedule the technical interview. It happened within a couple of days and it was actually pretty tough... I had a brain fart when I was asked, name a couple of wait types... ahh... I know this.... I know lots of wait types... work with them daily... what is wrong with my brain!! I was so embarrassed that my brain wasn't working.

Immediately after the technical interview the recruiter called and said, when can you come to Reston, VA for a face to face interview? Schedule for Thursday of the next week. The travel coordinator waited until Wednesday (the day before) to get me confirmed on a flight. I was really sweating, not sure what was going on, but finally it came through (after my bedtime).

The day of the interview was pretty interesting. As an interviewee I had a little tiny office where the interviewers would come and interview me. I'm assuming there are multiple people interviewing for the same position in the office, since I've seen several people walk by.

After my first interview I thought I saw someone I knew to the point I started typing him an email when the second interviewer walked in. This was a pretty long technical interview, and no brain farts...thank goodness! The interview ended and this time I definitely recognized my buddy Ryan Adams walking by... I called out his name, "Ryan". We started talking and he asked me, "you're a BI guy, right"? Nope, I do the same things you do... we both said about the same time, "oh no, I'm not going to get the job because you are"! It was pretty funny but a little nerve racking.

Both of our next interviews were phone interviews with different people. While they were setting us up, I asked them, are me and Ryan interviewing for the same position? I was happy to hear, no we are not going for the same position.

The local coordinator of the interviews that day told us to give them 7 to 10 days for the recruiter to get back to us, we both were a little shocked about that as we were told we would hear that evening whether we go the position.

Ryan and I went to dinner that night and Ryan got a call form the recruiter. Recruiter says, we want to move forward, Ryan got up so he could go somewhere quieter to talk. Sure enough, he has a verbal offer. Yea! All dinner I'm thinking, where's my call. :-)

By this time I know I did well on the interview. But I'm still a little nervous I haven't received the call.

We decided to meet for breakfast the next morning before driving to the airport. Still no call by breakfast.

At the airport, Ryan and I were one gate away from each other so we hung out before his plane boarded (mine was boarding close to an hour later). No phone call yet.

With business as usual, I completed a phone "interview" with a potential client; just in case.

So we board the plane and I'm sitting in the exit row way back in the back of the plane. Everyone is on the plane, the door is shut and here comes the flight attended to make sure we know we're in an emergency row. Bzzzzzzz... yep there is my phone ringing and the phone number is from Redmond... not now... yes, now.

I of course answered the phone and the recruiter asks, you home yet? "Nope", I said, "on the plane, doors shut and everyone looking at me. How's it going? " He says, "Well, we want to move forward, how's..." the flight attended is being very cool as I mouth, "sorry!" She says, "we can't leave until you hang up the phone". "No problem", I said, "its a job offer". I told the recruiter I needed to hang-up the phone but I would be online as soon as we got to altitude. The negotiations started from cruising altitude.

Yep, I got the job!

Best news, Microsoft is still hiring for PFE and Consultants. Let me know if you're interested!

Gauntlet Dropped

This is my first time blogging for T-SQL Tuesday and I join in because this topic is one I have passion for.

Lets start by saying, public speaking is a very scary thing for many people. I know many database professionals who attend every SQL Saturday within 500 miles. You guys know who I'm talking about! These guys have accumulated so much knowledge from all of the sessions they've attended they should put together a session and submit.

I grew up being that loud mouthed, outgoing and aggressive...oh wait, not me my brother. Starting over, I was the shy guy, no kidding guys stop laughing. I had very little confidence in high school and about the same in college. When I told my guidance counselor that I wanted to study computer science he basically said I was overreaching. My ego was so small I almost believed him. Wish I remembered his name...jerk.

I started taking classes at Ball State University as a Computer Science major. During my study I decided to take a public speaking class, I can't remember why, I just remember if scared the bejesus out of me. My first talk was short and I could hardly speak from the shacking. I was miserable and a little embarrassed. To add insult to injury the lead cheerleader from high school, you know the really cute one...was in my class. What luck.

I made it through class, probably passed the class due to sympathy from the instructor and swore I'd never speak in public again.

Yep, there it is. Never again. Not if you paid me millions of dollars. Nope, no way, no how. You see where this is going right?

I have been a database professional for more than 20 years; do you think there is anything I can teach the newbies? You betcha. I remember back in 1996, when I was converting a Microsoft Access "app" to use SQL Server as the back end. The time I was a manager in the cellular phone fraud department at BellSouth Mobility. The fraud detection software we had was very limited in its capabilities because cell phone fraud was fairly new. Remember 1996 was the Atlanta Olympics. The fraud was a coming and it was going to be big.

I figured out how to pull the phone calls of all calls logged as fraudulent from the fraud system into MS Access and eventually into SQL Server 4.2 .... argh! I used the data to cross reference common phone numbers called by the fraud phones. From that I was able to create a rule that would allow me to generate a sheet for the Secret Service. Yes, it was pretty cool. There was my start with SQL Server.

Speaking, back to speaking...so, over the years my confidence in what I was doing grew. I was giving little training sessions to the customer service reps on how to handle the calls from people with fraud on their bills. Yep, public speaking at a smaller level, but still pretty scared when I started. Usually about 15 minutes in my excitement about my subject took over. I forgot that I was frightened of speaking.

As I progressed through all of my positions I had to talk to bigger groups of people, and still was extremely nervous speaking.

Years later I started meeting some of my DBA friends at a restaurant/pub called Taco Mac. These guys all were active with SQLSaturdays. I started helping by volunteering and eventually being on the organizing team in Atlanta. I had been to two SQLSaturdays before. At the time Aaron Nelson, Julie Smith, Audrey Hammonds, worked for me and got me involved with PASS. Aaron convinced me to go to SQL Rally in Orlando, then PASS Summit in Seattle the same year, 2011. Work even paid for it (amazing).

So what does this have to do with speaking? There are several things you have to do to speak:

Have a presentation.

Submit to your local user group or any SQLSaturday.

Get picked (usually pretty easy).

Practice.

Present.

The part I left out; usually you need someone to push you over the edge, coax you to pull the trigger. Take that first step.

That part was played by Aaron Nelson, Geoff Hiten, Rob Volk and Ed Watson. Weekly, Thursday nights, someone would tell me, you have to put together a session. I always came back with, Yeah, I know. Working on it.

Performance tuning and troubleshooting was something I did a lot and enjoyed doing. It is so much fun taking a developers code that he has improved run times from 15 minutes to 12.5 minutes. This query can't be improved! Let's deploy it to production. Ummmm... let me look at it. A few changes in the query and it is coming back in sub-second times. Its the face of the developer when he sees it that makes my day.

This is where I would start, showing a few best practice settings, a few ways to use existing free tools to help find the problem query...tada, I have a presentation. I named my session Performance Tuning for Mere Mortals. Why? Because I want to teach the beginners things I didn't know as a beginner.

I submitted for Jacksonville SQL Saturday since I knew a few of the guys going, but I didn't tell them until I got picked. I GOT PICKED! Wow, suckers, I'm a newbie, with a history of being deathly frightened of speaking. They picked me? Just to be clear, the Jacksonville SQL Saturday team follows the guidelines of give new speakers a chance. That's how we build the army of speakers to teach the masses for free.

Why am I doing this? I don't know. I'm not nervous yet...maybe later.

We loaded up my big blue Durango, with Rob Volk, and Damu Venkatesan.

The day of the event I had the pleasure of meeting several people who gave me some advice before my session...still not nervous...ok..cool... Chris Skorlinski an escalation engineer from Microsoft did a similar session before mine in the same room. So I went early to see his presentation. It had some good stuff in it that maybe I could add to a future presentation. I love going to sessions to learn from the speakers about speaking. I always learn something new. Chris and I talked a lot about presenting and he gave me a load of tips and tricks.

Now its my turn, not nervous yet. Wow, I had more than 40 people who want to here MY session... so I started. There were a lot of questions which was awesome. Even better one guy in the back of the room asked a few meaningful questions... I think he was a plant. :-) I remember seeing him in the speaker ready room.

Wait, still not nervous, but running out of time... I get a little worried about running over... yes this is my first session and I had way more questions that I expected... but its all cool. I like to build in extra slides just in case I get waylaid by questions or get no participation at all. Time is up... what? I finished on time and I didn't pass out. 1 down and I'm still buzzing from the adrenaline. I had multiple people come up and thank me for the great session...wow.

I went back to the speaker ready room and Chad Churchwell, Microsoft PFE (the guy from the back of the room) was in there. Again, I asked for feedback and tips. He gave me some great ones. Here was my start, all of the speakers are willing to help make you better and in a very positive way. I still talk to Chad every once in awhile, and I keep hoping to see Chris at a SQLSaturday...I have to do Charlotte next year.

Today, I've spoken at 20 SQLSaturdays and given 4 precon sessions. I've also presented twice at PASS Summit (the last two years). Not too bad for someone who was afraid of speaking.

The moral of the story is, you have something to tell, period. If you're a data professional that something could improve someone's life (business or personal). It could be the one thing that took them from beginner to senior, the spark needed to achieve great things, or even the one thing that stopped them and others from doing something stupid in production. :-)

You do not have to be an MVP or Microsoft employee to speak. You do not need to be a senior data professional. For that matter, you don't have to be a data professional at all. Just ask Karlyn LeBlanc who is not a data professional but presented Balancing Work and Home to Create a Better You at multiple SQLSaturdays I attended. Great session.

See, there really isn't any excuse for not putting together a presentation. You too could be presenting at PASS Summit next year! Take the first step. Put together a presentation. Ask me @SQLDiver to give you some feedback before submitting it. I'll even help with your abstract!

And for those friends that still aren't speaking... you know who you are...I'm writing this for you! Get your presentation together.

Highlights

Social Events

As usually the social events are the most valuable aspect of the conference. Most organizations are very critical about conferences assuming they are all about parties. Yes the headline for this section is social events...the most important thing gained from the social events were the relationships built with the other data professionals. People I look up to and have asked for help at one time or another.

My skills are better because of these guys, and their Tweets and Blogs. Knowing them and talking to them builds a relationship that opens doors for when I need help. And if I happen to see some good Karaoke while I build the relationship...awesome!

Monday Night Andy Warren Steve Jones Networking Dinner

Yardhouse is a great place for dinner. Add Steve Jones and Andy Warren and you have a great networking event!

PASS Summit Welcome Party

Tuesday night PASS throws this big First Timer/Summit Attendee event every year. It is a great way to network with first timers, and start building the relationships with SQL Professionals. There were so many first timers this year that I got to talk to, passed out close to 30 business cards. I hope they all stay in touch.

Denny Cherry/SIOS Karaoke

Denny always has the best party of the year. I have blackmail video tape on multiple people... you know who you are. :-) Actually, it is a who's who of the SQL Server World including Microsoft employees, Data Platform MVPs and the rest of my 200 closest friends! When Denny announces the party, you had better signup right away. This year the party was on Tuesday.

Redgate Rocks Party

Thursday night was a huge party night. Redgate had their Regate Rocks party where once again we had a blast with friends from around the globe. The Redgate employees are always awesome to hang around! Annabel, Grant, Karis, Tony, Carly and all of the Awesome Redgate employees know how to have fun.

Bush Garden

Bush Garden is the default Karaoke destination ... every night (I only made it one time this year). I saw R$% F#4rLy, #en%4 P@5*74, $%@#$%, and #!$%$!$ from Microsoft (redacted). What happens at Bush Garden stays at Bush Garden! :-)

Nope, I didn't sing!

Others

I attended many social events that were invite only that I won't go into details about since they were invite only. These are events where prior year socialization helped build relationships with Vendors that have helped my career greatly.

So in short, "socialization" is extremely valuable to the employers, because their employee is building his network of professionals (and potentially putting a bug in a future employee's ear).

Sessions

Of course my favorite session was my own. This is the second year that I've been a presenter at the PASS Summit Conference. I had 375 people attend my session, Execution Plans for Mere Mortals. It is only a 75 minute session (that I could make into an all day precon). As a previously, petrified of public speaking, speaker, I love getting on stage and teaching beginners. Sure I can teach some deep super advanced detailed session, but beginners are more fun and I love to give back.

I had a very long line of people who had questions at the end and wanted to give me feedback saying how much they loved my session. Always much appreciated. I had multiple friends and previous co-workers in the crowd which was even more fun. Several of them it was the first time for them to see me speak.

Bob Ward's "Inside SQL Server In-memory OLTP

Sitting right behind Kalen Delaney, the original writer of the In-Memory OLTP white paper (which I've read multiple versions), I got to hear the always deep Bob Ward teach us so much more than we'll ever be able to remember. Bob usually brings out the debugger very quickly to look at what is going on inside SQL Server (I think Geoff Hiten (@sqlcraftsman) said it was less than 12 minutes).

Awesome 3 hour session.

Joey D'Antoni's "Building Secure Applications in Azure SQL Database"

Joey gave a great session on Azure SQL Database security. I'm currently working with a client where security is extremely important and Joey gave me some good tips on how to answer their concerns with having their data in Azure.

I've been working with Azure for a short time (a little more than a year), playing with it a little longer, so the more experience I get the better. I am working hard to prepare for the 70-532, 533 and 534. I plan on taking all three before the end of the year. Keep your fingers crossed.

Bill's session was awesome, as it talked a lot about the Elastic Database capabilities in Azure. This one hit home as in the recent past I had a client who needed this functionality pretty badly. I'm already putting together my document about the Elastic Database capabilities for my client.

Brandon Leach's "Data Pages, Allocation Units, IAM chains... Oh My!"

I saw this one previously, Brandon does an excellent job of going through the data page. An awesome session for sure!

So many great sessions, I wasn't disappointed in any of the ones I saw. I am very sad at not being able to see several of the ones that were on my list such as Mladen's "Algorithms For Searching Through Encrypted Data". Great speaker and great subject. I missed all of the chalk talks which I love every year.

Steve Stedman's "When Database Corruption Strikes", who I've been following through the weekly "Database Corruption Challenges" that are too much fun.

Food

Yes, I said food... I gained 4 of the 6 pounds I recently lost. Breakfast is always pretty good (the bacon is awesome)! I'm a little miffed that breakfast ends at 8:00AM instead of 9. Many speakers spend their mornings trying to prepare for their presentation the morning of their session. I did, and I missed breakfast by about 45 minutes. I had planned on running in and getting my breakfast quickly then onto Dr. Dewitt's keynote.... unfortunately I had to go find breakfast somewhere... I had to prepare for a big day after all.

I'm going to say it once.... why does breakfast have to end so early (I see the amount of food thrown away in the morning and many attendees miss breakfast because it closes so early). At least allow speakers to get breakfast later.

The lunches are also awesome, chicken, steak, fish, every day.... did I say I gained 4 pounds?

And there is that one day I joined Karla and Rob V. at Blue C Sushi...mmmm sushi with my friends.

Goodbyes

It is always bitter sweet to say goodbye. Tim Mitchell arranges a final dinner at Crab Pot every year (sorry I was late Tim). Again, another opportunity to network with people I didn't know before.

This session was a very basic beginner session going over how an execution plan is generated, some basic operators and join operators. Just enough to get a beginner up and running. It is certainly not enough detail for experienced users.

For the past several years I've been a speaker at SQLSaturday Oregon and PASS Summit. SQLSaturday Oregon is historically the weekend before Summit which makes it convenient to fly into Oregon, take the #SQLTrain up to Seattle with a big group of close friends and experience the camaraderie of people I only get to see once a year if I''m lucky.

Because there are so many people coming in from outside the States to SQLSaturday Oregon, because of the timing, its even more special.

Funny thing is for both PASS Summit and SQLSaturday Oregon, my Execution Plans for Mere Mortals was picked. This is my last opportunity to run through my session before I present it on Thursday, October 27th.

I've updated the session and improved much of the content for beginners. I love that this session was picked because it is so near and dear to my heart, performance tuning is some of the most fun activities I do. When I have someone complaining that their critical to life stored procedure is taking 45 minutes and that they've made major improvements to get it to 43 minutes, but still suffers timeouts in production I go to work.

My work starts with an execution plan. I normally search the procedure cache for a copy of a recent copy of the execution plan, just to see how it compares with a lower environment like DEV or QA. Does it have the same plan? Can I use those environments to track down the problem?

This is where the fun comes in... using the awesomeness of SentryOne's (new name!) Plan Explorer, I quickly take a look at the plan fro plan cache and see if there are any red flags. Do I see anything that is out of the ordinary? Does this plan really need a hash join? Is that sort warning a major problem or just a red herring? Are there any residual predicate issues?

In my session, I will show the attendees many of these issues and where they will be found. This session is for beginners that are somewhat lost in the graphical execution plan. The session is only 75 minutes for a subject that could fill a day, seriously. That session is in the works.

If you're wanting to get the basic beginner information about execution plans, come see me at either SQLSaturday Oregon #572 or PASS Summit 2016!!

The article is about what could happen if after a reboot another application/service accidentally grabbed the SQL Server port because the service starts up before the SQL Server Service and uses a dynamic range that includes the SQL Server port. I've never run into this in of the companies I've worked with over the past 20 years, but it makes sense to mitigate the problem in the beginning.

1) There had better be a really good reason there would be another application installed on "MY" database server...but...say there is.

Chris suggests adding the following to your steps for installation. Ok fair enough, something very simple to add to my scripts that get run from my automated install process... lets do it:

This is the netsh command to be run from the Windows command line. I'll add it to my PowerShell install script.

Starting in June, the SQL Saturdays start ramping up. This year in June I did SQLSaturday Chattanooga. Once I got past Chattanooga I got a bit of a breather. Then its Indianapolis August 13, Spartanburg August 20 and Columbus, GA August 27 - 1-2-3, one right after the other. I sadly, but luckily wasn't picked for Louisville. That would have made 4 in a row.

For those that don't speak, this is an opportunity to meet many of the newbies out there looking for help. I'm from a time when our options for help were limited. I bought a bunch of books, Ken Henderson's, Ron Soukup, Patrick Dalton... many people didn't get the benefit of learning from these guys. Ken died in 2008, a great loss for everyone in the SQL Server community.

This is why I speak, to pass on the things that I've learned over the 20 years I've been playing around with SQL Server. I love meeting newbies and mentoring the guys who have been doing this for a few short years. It is why I do what I do.

I'll never forget meeting the guys I followed later in my profession, like Denny Cherry, Kalen Delaney, Paul and Kimberly... the list goes on. Even some of my close friends that I drink a beer with every Thursday are on that list. People on that list are associates I work with through Linchpin People, SolidQ, Intellinet and even Microsoft.

Every SQLSaturday I speak at I build a new relationship with a speaker... Indianapolis was especially true of this where I got to spend a lot of time with Kevin Kline, Ginger Grant, Adam Machanic...not to mention my middle school buddy...Aaron Cutshall.

This is where I get my inspiration to speak!

So readers... its your turn! Put your session together and submit! If you want someone to look at your presentation and give some feedback... send it to me.