Indexes and Powershell

I ran across a blog before the Thanksgiving Holiday that caught my eye. Its something Ive been intending to get into recently, but just didn't find the time. So on the slow day of Wednesday before the Thanksgiving holiday I took the time to dig into it and play with it in my system. It was a blog by SQLFool (Michelle Ufford | blog | twitter ) on how she deals with indexes. She basically has a proc that shows her Missing Indexes. She also uses Kimberly Tripp's (blog | twitter) sp_HelpIndex2 proc to dig into the actual table that says its missing indexes. The results of these 2 sets of information are manually processed and decisions are made about indexes.

Simple enough. You need the knowledge of how your systems is being accessed, coupled with the information these two procs provide and a little time, and you too can make informed decisions about your indexing needs.

I then coupled this information with a script from Pinal Dave (blog | twitter) that looks at unused indexes. I tweeked both these scripts from Pinal and Michelle to match the results I was looking for. Small tweeks, mind you. The result were 2 queries I could use to look into a particular database, see in a glance all missing and unused indexes. Using Kimberly's proc to review the table for existing indexes and combining this with the knowledge I had of our systems, I made choices and either added or removed indexes at will. I felt powerful. The power of the DBA coursing thru my veins...

< side note >

I want to take this chance to thank these folks for their work, their sharing and their knowledge. It's nice to stand on their shoulders, grab solutions that work, implement them, tweek them to my needs and system and topology, and devise a workable solution that fits my needs.

Thank You!!

< /side note >

After digging into several databases in several servers, I ended up on a particular database last night, just before I went home. While I was looking at this db one of our database developers called me with a complaint about a slow running report he was working on. After looking into it, locating the spid and digging around it to see what I could determine, we determined that it was indeed processing, it was indeed working, just very slowly. It had been running for an hour and a half so far, when he called me. My first thought was that I had caused the slowness with the index work I was performing. After disclosing this to him, and determining that it was not the case, we kept looking. At one point I decided to give up, remembering that I was just about to dig into this database in particular and look at its missing and unused indexes. I informed him we should do that now, instead of looking more.

He killed the report and we watched it complete. He could tell that it had processes somewhat, but was not finished yet. I then looked for missing indexes, and with his help, identified likely candidates that would affect his query in particular. We added a few indexes to these tables along with 2 other tables. I think in total we added 6 indexes only to this database. We then removed several indexes that were unused. After this work I suggested he kick off the report. Almost immediately we received an alert from SQLMonitor indicating that we had high processor utilization. This lasted for 3 minutes and levels returned to normal. The report was finished in 4 minutes. This report, remember, had been approaching 2 hours previously, and now completed in 4 minutes. We executed it again, and monitored again. Same results. Yeah!! Database Developer is happy. I am happy. I go home.

The next day I realize that this is valuable information and could help me historically, if I could collect it. I set out to do so, and if you are still reading, do not be disappointed. I succeeded, or I wouldn't be writing this blog about it, I'd still be working on it...

I converted the 2 queries I was using into powershell variables, and then I execute this query against each database I want too, against each database server I choose too. I collect this data, and store it in a staging table in my DatabaseMonitoring database. After it has all been processed I call a proc that sticks this collected data from the staging table into a historical table, adding an Identity ID and a datetime stamp. Now I know when the execution occurred, along with the information about the missing and unused index, per server, per database, per table, etc.

I did not get fancy with the powershell and do any looping or anything like that. The DBs that I look into are hard coded in the script. Someday, maybe, I can get back to the script and make it purty. But now, I'm done. The code is put into a job that will fire off periodically and collect this information historically. Now I just have to look at it periodically as well.

I want to take some time soon to pull this accumulated data into a report and add it to my 'Monitor Everything' report that sends me daily email statuses for all these processes I have added to our monitoring topology. Soon. After a few projects are complete, I will return.

a little powershell will do me good

I had an issue today and needed to report versions of OS and SQL Server for a few machines to support. I was unsure of the OS versions of these machines. After asking my team for any documentation on said machines or what versions the OS was currently, I thought... Powershell can probably do this for me. Whip out my google-fu and a few minutes later I have a small working script I can execute against a box to determine the pieces of information I desired.

Simple.

get-wmiobject Win32_OperatingSystem -comp Server1 '

| select __Server,Caption,ServicePackMajorVersion

get-wmiobject Win32_OperatingSystem -comp Server2 '

| select __Server,Caption,ServicePackMajorVersion

Here is a sample of the Powershell script I used. It returns 3 columns. The Server Name, the OS version and the Service Pack. Simple little table of information.

Obviously you could add to this script to perform a loop from a list of servers. You could augment this to do many things. But for me, it was a simple solution to determining what the OS version was so that I could report this to the support staff I was dealing with. And whats better, I didn't have to bother any other humans for the needed information.

What have I done?

I thought this was interesting when I compiled it recently for a consulting company that I am working with (SQLSolutionsGroup). Its basically a list of the speaking engagements I have performed as well as articles written. I enjoyed putting it together, as it represents rows of data that indicate an activity I performed on a certain date. Its a table of data. And since I had never really sat down to write it down, to form the data, I thought it was interesting. I plan on keeping track of this and adding to it.

So, look, if you care too. This is mainly for me. I enjoy it.

Speaking Engagements

User Groups

Utah User Groups June 2005 Reporting Services

Utah User Groups August 2005 Profiler / Trace

Utah User Groups February 2006 Sarbanes-Oxley

Utah User Groups January 2008 Query Processing

Salt Lake City User Group 2004-2008 various topics

Utah County User Group 2006-2008 various topics

Code Camps

Salt Lake City Code Camp October 2006 Profiler / Trace

Salt Lake City Code Camp April 2008 Query Processing

Salt Lake City Code Camp November 2008 Query Processing

Miscellaneous

Microsoft 2006 Analysis Services

ACS 2006 Sarbanes-Oxley

PASS SIG 2007 Some topic

SQL Server World Wide User Group Virtual Conference

SSWUG vConference April 2009 Auditing your users

SSWUG vConference April 2009 Best Practices

SSWUG vConference April 2009 Job Execution System

SSWUG vConference April 2009 Monitor Index Fragmentation

SSWUG vConference April 2010 Configuring and Managing GoldenGate

SSWUG vConference April 2010 Database Switch

SSWUG vConference April 2010 SQL Server Profiler and Tracing

SSWUG vConference October 2010 Database Change Process

SSWUG vConference October 2010 On Call Duties

SSWUG vConference October 2010 Professional Development

SSWUG vConference October 2010 SQL Server Logical Query Processing

SSWUG vConference April 2011 Data File Sizes

SSWUG vConference April 2011 Documentation – you know you love it

SSWUG vConference April 2011 Server Profiler and Tracing, a “How To”

SSWUG vConference April 2011 Stand-alone DBA dailyweeklymonthly

SQL Saturday

SQL Saturday 54, SLC October 2010 Database Switch

SQL Saturday 54, SLC October 2010 SQL Server Profiler and Tracing

SQL Saturday 66, Colorado February 2011 Documentation – you know you love it

SQL Saturday 94, SLC October 2011 Data File Sizes

SQL Saturday 94, SLC October 2011 Documentation – you know you love it

SQL Saturday 97, Austin October 2011 Data File Sizes

SQL Saturday 104, Colorado Springs January 2012 Utility Belt Pre-Con

Articles

SQLServerCentral.com

From the Real World : On Call Again – 04 Apr 2009

On-Call Duties – 27 Oct 2008

Audit Database Changes in the Real World – 11 June 2008

A Simple DR Solution – 31 July 2008

Job Execution System – 27 Mar 2008

A Failed Jobs Monitoring System – 4 Sept 2009

Simple-Talk.com

Can Twitter Help your Professional Development? - 16 Dec 2008

SQLServerPerformance.com

Monitoring Index Fragmentation - 14 Nov 2008

So, you find yourself On-Call - 22 Aug 2008

Automate Audit Requests - 26 May 2008

Audit Data Modifications - 15 Apr 2008

Meme Monday: SQL Family

Tom LaRock’s (blog|twitter) question this month: What does #sqlfamily mean to me?

When I think of Family, I think of my parents, my brothers and sister. I think back to the beginning when I was younger and we all hung out together. I remember the fun things we did together. I recall the times that they helped me or that I helped them. I know that there were bad times. But i also know and remember the good times. If I haven't seen my brother for years, when I do, it always begins with an embrace, a recounting of stories, a telling of my current history. We recall the good things. And push out the bad things, because after all, we are all Family. That trumps all. Family.

Now as I think about my #SQLFamily, I think the same things. Some of the members of this family have been around for a long time and there is a long history of good memories to draw from. Let's recount those. Let's relish in those. Let's repeat those by helping each other out when we need it most. When I wonder around the virtual house we all inhabit late at night, worried about something, I know that one of my brothers or sisters will be there, they will see my hurt, and they will ask what they can do to help out. Just like my family would do. When I meet a member of my #SQLFamily virtually for months or even years in the intertubes we occupy, our relationship can still be formed and solidified even though no physical presence between us exists. But on that fateful day when we actually meet, you can bet there will be a joyous embracing and retelling of stories that made us a better person for having interacted. I can recall almost each and every instance when I finally met a member of my extended #SQLFamily for the first time, and felt that tug of kinship, that knowingness of shared experiences, that pull of another family member you have forged a relationship with. I can feel that urge to help and be helped and better ourselves through our interactions.

Just like a family that loves each other, regardless of the goings on, regardless of the silliness we all experience from time to time, our Family and #SQLFamily will always be there for us. We will be there for them. And they for us. Always.

This is what Family means to me. This is what #SQLFamily means to me.

Yeah for SQL Monitor, Boo for SQL Monitor

Over the weekend, starting on Wednesday evening, I was on vacation. In Utah there is a school break in the Spring and Fall, sometimes with the kids having an entire week off. I took Thursday and Friday off, and we headed south to visit Zion National Park. We had a glorious time. Internet connectivity was via WiFi while we were in the campground only. Our cell phones stopped receiving cell signal about 40 minutes from the National Park, and remained eerily quiet for the duration of the weekend. This is a blessing and a curse. Knowing that things may go awry and I may need to pop into my databases always leaves me a bit uneasy. So, as often as I could, I connected to the WiFi and checked my work mail. A few times a day, usually in the morning and evenings.

On Sunday morning I was able to connect and to my surprise, SQL Monitor had captured a slew of error messages. 10 or 12 of them in a row, all occurring since midnight. My heart skipped a beat. What was going on... some digging ensued.

I feel so luck to have SQL monitor watching my systems, and emailing myself, along with 2 others in the IT department. If something awry occurred, it would let us know, and if I didn't jump on it, at least the other 2 humans would know via email something was up. So, something was up. I found it, and was investigating. Thanks SQL Monitor for watching my DBs while I climbed to Angels Landing in Zion National Park.

What was the error that SQL Monitor was complaining to me about? A nice generic one. 'SQL Server error log entry'. This means that something happened, was logged into the error log, and, well, that's it. Just that that think happened. If you are like me, your heart starts skipping a few more beats. As I looked at each alert down the chain, they were all the same. What I didnt notice at this juncture was that each alert was coming to me via email roughly and an hour interval. This is valuable to know, as it wasn't 12 alerts about 12 error log entries. It was an alert about an error log entry, and it was being repeated every hour. Much different story. So i dig deeper. To do this I need to drill into the alert itself and look at more details. Now I learn the following

The Database ID 6, Page (1:118112), slot 0 for LOB data type node does not exist.

The suggestion was to run dbcc CheckTable on the offending object. I need to know what database that is. Database ID 6. Is it one of my important databases? or a lesser important one? or a supplemental one. Which one is it... aaarrrggghhhh, I NEED TO KNOW!!! Since I had been out in the mountains, it seemed like my TSQL-fu was lacking and it did take me a minute to remember I could query sys.databases to see which one was Database ID 6. This was probably a few seconds, but in the panic of digging, it always seems longer. So, Database ID 6 turns out to be the database RedGateMonitor. Whew. Its only that db that is having an issue. A couple of quick DBCC CheckDB commands later I realize that all other DBs are in proper order. At least at the instance that I ran the CheckDB command. (hehe). So, I run on the RedGateMonitor and it encountered an object that has some issues. It is the object 'data.Cluster_SqlServer_SqlProcess_UnstableSamples'. I have no idea what this is, or if its needed, or what I should do with it. But, since its RedGateMonitor itself that has an issue, I figure I have some time.

Yeah for SQL Monitor for finding an issue on my DBs. Boo for SQLMonitor for its own object becoming funked up.

So, as you picture this, picture me on the top bunk of our camper, in my jammies (the desired PG rating refused to allow me to let you picture me in my undies), with a laptop on my lap, hooked into the RV campground WiFi, hunting down this issue. I am much more calm now that I know what db and what object is causing the issue. I quickly pen an email to my crew letting them know that I have discovered the issue, and a fix will come at some point, but until such time, we will continue to receive emails about every hour reminding us. Luckily its the weekend, and they can be ignored. My next step was to tweet my question about this object to #sqlmonitor and #kickasssupport. Knowing that someone will see it, and we'll get going on a solution soon. Maybe on Monday, maybe before.

Sure enough, I come in to work on Monday and see a tweet from @JowleyMonster with suggestions on how to remediate my issue. After a few attempts at recovering the data in the table via backups, I resorted to simply running a DBCC CheckDB with repair_allow_data_loss. I had to put the DB into SINGLE_USER mode first. Then run the DBCC CheckDB command. Then continue to verify via DBCC CheckTable on the particular object in question, followed by a larger DBCC CheckDB on the entire DB. When it all seemed OK, I returned the DB to MULTI_USER.

Now, you may be screaming at the screen now that I simply horked the connections to SQL Monitor, and did so in a graceful manner. I thought the same thing, and honestly wanted to try it out. I have been being so careful with it with other operations, I wanted to see what happened. In other instances I will actually stop monitoring everything, then have someone actually shut down the service, and then verify that all connections from SQLMonitor had terminated. But this time, I was curious.

It handled itself perfectly. Obviously the webpage that I had up throughout this ordeal was in a funked state. With a refresh, a few moments of uncertainty and stress on my part, it refreshed just fine. All was back to normal.

I am now free to pursue other tasks, as I leave the little gremlins that are SQLMonitor to do their job watching my DBs and letting me know when something is awry. It will happen. They will let me know. And I will dig in and fix them. I love having them around watching, especially when I am not.

FirstTimers Cheat Sheet 3 : More Humans to Hunt

In an effort to not leave anyone out from one of my previous blogs where I went into considerable detail on some 20 individuals, I have a larger list of humans to hunt while at #SQLPASS next week. Now, in case some of you complain, this list is not comprehensive either. But is an attempt to list out those humans that have interacted with me to a degree that they stood out and i listed them. This is in no way a complete list of the people you should hunt.

So, onto the list. Add these fine folks to your network. Learn from them. Each one has a certain set of skills that help them in their daily work and personal lives. I bet if you try, you will find something that you can learn from them as I have. The best part is that they too are willing to share this information with you. So we can all become better. All of us.

kevin kline

adam mechanic

jonathan kehayias

joe webb

aaron bertrand

jessica moss

randy knight

geoff hitten

jes borland

lori edwards

jeramiah peshka

alan hirt

lous davidson

neil hambly

karla landrum

rodney landrum

meredith ryan-smith

ted krueger

ben miller

midnightdbas

rob farley

paul white

mladen prajdic

tim mitchell

jack corbett

phillip beazley

argenis fernandez

erin stellato

kendal van dyke

kathi kellenberger

scott stauffer

brian knight

simon sabin

tara kizer

robert davis

merrill aldrich

jorge segarra

bill fellows

doug lane

joseph richberg

peter shire

tim ford

Since the goal of the Summit, in my opinion is to Learn and Network, this is the Network portion, which I have relabeled Hunt. Now we have LURN / HUNT as the two goals of the summit.

As with the previous list, I challenge you to find these folks. Meet them. Add them to your network. If someone can meet all these, as with the previous list, and bring me proof, you will win a prize. Some of these fine folks I have yet to meet IRL and hope to do so this summit myself.

When you are not LURNing, happy HUNTing.

FirstTimers Cheat Sheet 2 : Adventurous Version : Cards

I was challenged to create cards to match my words. Similar to those used to track down various individuals in Iraq. Allowing you at home to print up said cards and hunt down said individuals.

http://dl.dropbox.com/u/11735707/2001%20Summit%20Pack%20of%20Cards.pdf

Go get them. Print them. Hunt them. The first one to meet them all, who didn't know them all, gets a prize from me.

FirstTimers Cheat Sheet 2 : Adventurous Version

I recently gave you some ideas of some good things to do while at the PASS Summit next week.

Now let's get a little more adventurous. I want you to hunt down some specific people. Keep in mind that I love each and every one of these individuals as a brother or sister. And though I may be asking you to do something that would appear to not be in your best interest, I promise that you will have a good time doing it, and the individual you meet should be nice to you in return, i think.

Ok, here we go. Things to do when you find these people while at the summit.

[EDIT : if you do not appear in this list, do not fret. I can only fit so many per blog. SQLFamily is huge. More blogs to come]

Try to get a run in with Steve Jones from SQL Server Central. This man is an inspiration to so many for his daily running routine. Even if you see him in the elevator while going to run will be an inspiration. Ask him how much he likes shoveling hay. If you dont have guts enough to do this, simply thank him for being the godfather of this SQL community we now enjoy. If you have the time, listen and learn. He can teach you so much about so much. As those of us that have learned from him for many many years can attest.

Challenge Grant Fritchey to an arm wrestle. Or better, tell him you can chop wood faster than he can. Actually, anything you can challenge him to in his presence will impress me, as he has a stern persona at first glance, but is one of the nicest people you will meet at the summit. Ask him about Azure, about performance, about anything, and be prepared to learn.

Purchase a cup of coffee and deliver it to Buck Woody. Do this and you may make a friend for life. I dare you to ask him if he likes tea. Any kind of tea. If you cannot find him while at the summit, keep your eyes peeled for a blur in the hallways, that's probably him simply moving faster than the naked eye can detect as he is amped up on caffeine. 10 minutes learning from him will keep you busy for months, if not years.

When you run into Paul Randal, challenge him to a game of Blokus, hinting that you could probably kick his butt. This will prove to be a fun conversation, if you survive the first accusation of superiority. My first time meeting him is so ingrained in my memory, I will always cherish it. This man will impress you from the get go, and you will be surprised when he speaks that his accent doesn't quite match the one you had in your head all these years as you read his blog. Remember that he wrote most of what you use today in SQL Server. Be prepared to learn from him. Listen raptly.

If you are lucky you will meet Kimberly Tripp soon after you meet Paul Randal. She will smile at you and make you feel welcome. As if you are actually of worth. Realizing that you are in the midst of SQL Server royalty, open yourself up to what she can share with you. Amazed you will be.

If you want to meet the nicest guy at the Summit, then you need to hunt down my brother from another mother, Andy Leonard, and become his brother thru conversation. He is so knowledgeable and so kind, you will simply be blown away at how much you learn from him while listening to him speak. If you don't feel like giving a bear hug at the end of this encounter, you may have done it wrong. He is the best.

If you like lists like I do, then you mush try to find Brad McGehee and learn from him how to keep track of and take care of your database servers. Ask him about Hawaii. He lived there. This alone is an amazing treat, in my mind. One of the nicest people you will meet is Brad. Learn from him. Let him teach you. You will be amazed.

Once you run into Tom LaRock, ensure that you ask him about his favorite sausage. Go on and on about how much you love sausage. Or is it bacon. I cant remember... find out for yourself. After giving him a hard time, sit back and let him teach you about the community. About how we need to help each other. About how we can do a better job. Soon you will find yourself volunteering to help him on whichever of the myriad endeavors he is embarking upon to make us all better people, in spite of ourselves. He will make you a better person simply by being around him.

I'm a sucker for accents, and have always loved to listen to Gail Shaw. You will too. Be prepared to ask a simple performance question of her, if you can find her, and then record the answer. Type it up and read it over and over and be amazed at how much knowledge you will be innovated with in a short period from her. If you could extract a small ounce of her knowledge, it would probably more than double what you already have.

If you are looking for a quite rock of knowledge that will thoroughly impress you and impress upon you that you need to be a better person, seek out Andy Warren. His quiet way will shock you into the reality of continuous improvement. Listen to him when he talks, and how he talks, and watch others learn from him. He is a giant of a man and you should be humbled to simply learn as much as you can from him.

When you meet Denny Cherry, be ready to be cussed at and maybe even yelled at, but come out of the event knowing so much more about storage than you ever thought your little brain could handle. He is not for the faint of heart, but has such a store of knowledge that you would be amiss if you skipped this opportunity to learn from him.

If you see a giant (literal) of a man wondering around with a camera, stop and say HI to Pat Wright. He too has a quiet way of pushing you out of your comfort zone and into the community and then letting you do what you will within it. But you are a member of it because of him. How many of us can say that? I know I am a member of the community solely because of his influence. Tell him HI from me when you do see him. I owe him sooo much.

If you get a chance to hunt down the elusive Brent Ozar, do so. At first I thought he was a fictitious bot generated from Quest on twitter, but he turned out to be real. A real boy with a vast collection of knowledge. And given the chance, he will share it all with you. You will not be able to take it all in at once, so drink from this fountain only as much as you can take it. He will teach you so much.

If you run into Aaron Nelson, ask him if he's ever heard of this new language called PowerShell. Tell him you think its a fad. Then if he is still talking to you, ask him to help you with a script. He probably has it on him, and can give you a copy. And you will be opened up into the world of Powershell that you never knew existed. Anything you can think of, he's probably already done it and will share it with you.

When you see Allen Kinsel, which you will. because he is everywhere, simply run up and give him a hug. He may resist. But while hugging him tell him how thankful you are that he made the PASS Summit work. None of us will ever know how much he puts into this event. We all ose him so much. After thanking him, tell him how sorry you are that he still has copies of SQL 6.5 in production. Offer him your condolences.

If you happen to see a DBA that looks like a criminal and causes you to pause, possibly switching to the other side of the hallway, this may be Chris Shaw. Don't avoid him. His shaven head and stern face is covering a plethora of Administrative knowledge that you cannot afford to pass up. He will give you his shirt off his back, if he could. Another one of the nicest people you will meet with a head for Data that will amaze you. Again, learn from him. Learn. Soak it in.

I could go on with more, and hope to continue this list as the Summit grows closer. This is a list of people I think you should meet. I have met them. I love them. I learn from them. I am amazed to be in the same community as they. They are SQLFamily. You should take the time to meet and add them to your network. Become part of the SQLFamily.

A Post just for me

These photos are from the Austin SQL Saturday event. I just wanted to grab them so I oculd show my family.

http://www.flickr.com/photos/sqlserverio/6211465364/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210954761/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210955225/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210958065/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210959395/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211472002/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210960329/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6210995959/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211522598/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211531668/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211542400/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211031085/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211031599/in/set-72157627692791739/

http://www.flickr.com/photos/sqlserverio/6211543762/in/set-72157627692791739/

FirstTimers Cheat Sheet

FirstTimers Cheat Sheet

Let me start by saying that I have been there. Standing in a room or strangers, wanting to reach out to them, add them to my network, but unable to do so. Why? Why couldn’t I? Well, that would take way too long to blog about. So let’s just skip to the how.

Let’s start over. Why do you go to PASS Summit? To Learn and To Network. Learning is easy. Attend at least 1 session while at the summit and I guarantee that you will learn something. It’s the second part that causes some of us to stumble.

Let’s break down networking. My own definition is to add individuals to my network of individuals that can help me in my day job, my tasks. Notice that I said add individual(s). This entails more than one. Multiples. Many. You get the picture. People. Humans like you.

How can we do this quickly and easily? I’ll show you 3 easy steps with several ground rules associated with these steps. First, back to the definition. Adding individuals to your network. These will be bonds that you create with another human, adding them to your toolbox of skills to accomplish your tasks. This is a selfish goal. But to accomplish this, you must be unselfish. You must reach out to them and be interested in them. Some people you may have very little in common, while you will find others that you have many points of interest connected. This is ok. They all go into your networking bucket. All. Stick them all in there.

Ground rules

For the next 3 steps, I want the following rules to apply. Do not talk about yourself, unless asked. Do not dismiss the individual during any of the steps. In between steps you may have to break the contact. This is OK. Contact can be reestablished later. Learn how to be genuinely interested in their responses to your 3 steps. I didn’t say pretend. Actually become interested. This is part of the unselfishness on your part. If all that happens is that you learn about them, then the goal has been accomplished. You may say to yourself that they know nothing about you. That’s ok. The goal here is for you to add them to ‘your’ network. Selfish goal, but accomplished by unselfish means. Become interested in each response. Become enthralled in what makes them human and a database professional and everything in between.

Steps

What I want you to do here is to memorize these steps. If you cannot do that, then write them down on a cheat sheet. I want you to ask the other humans the following questions.

• · Who
• · Where
• · What

When do you ask the other humans these questions? Whenever you find yourself near an individual for more than 10 seconds, turn to them, look them in the eye and ask them these questions. Realize that there are 3 and that a break in contact may occur in between any of these questions, and that is ok. Let it happen organically. But these are the questions to ask. Remember to be interested in their responses, and do not simply wait for a break to talk about yourself. In fact, do not talk about yourself, unless asked.

Who

Look them in the eye and ask them ‘Who are you’? Do not look at their name badge. This only tell them their name. You may forget this anyway. Try not to. But this is not what you are asking. You want to know who this person is. Let them tell you, in their own words. This is a nice simple ice breaker. It’s like asking a date out for a drink instead of dinner. Thus allowing an out if it’s not working out and they are trapped in a longer event than planned for (dinner). This is short and sweet. ‘Who are you’? It may sound brusk and you may alter it as needed to fit your personality. Point is that you are engaging them, and asking about them. DO NOT TALK ABOUT YOURSELF, unless asked. Remember that you are adding them to your network. They may not be adding you back. That’s ok. It may happen, it may not. It’s OK. Remember to be interested in them. ‘Who’ they are. Learn from them. Let them go on and on about themselves. I bet you will find that they are experts in talking about themselves. It’s one of my favorite topics.

Where

This digs a little deeper and can be several questions, depending on how it’s going. Where do you live? Where are you from? Where do you work? Each of these questions opens up a plethora of responses and follow up questions. As organically as possible, let these happen on their own. This section of steps can become rather large. Be ready to take the time and be interested. Remember that you are still forbidden from adding your own information at this point, unless asked. (I bet you will be asked and this will allow the other individual a chance to find out the same things as you are finding out, and thus add you to their network as you are adding them to your own). As you find out another piece of info, try to dig a little deeper, or make a connection.

Before I go on to the last step, realize that we have said nothing really about databases, our skills, our goals, our talents, our favorite or least favorite piece of the database world. We are getting to know the human. Remember that they are 1^st a human like you, second an IT professional, and third a database professional. There are many things you can have in common before you actually reach the database professional stage of information gathering. Hopefully before this stage even occurs you have forged enough bonds with this person, built up a relationship of trust and added them to your network, regardless of whether they are in BI, DBA, Development, Contracting, etc.

What

This question gets more into the nitty gritty and you start seeing where you overlap in skills and talents. Some sample questions for this stage are ‘What do you do?’, ‘What brings you to the Summit?’, ‘What do you hope to gain out of this Summit?’, ‘What is your favorite part of SQL Server?’, ‘What are you good at?’, ‘What are you not so good at?’, ‘What do you hope to be better at?’, and so on. Add here as you see fit. But the point of this section is to learn more about them. As I have said before, you are here to learn about them. Where possible, refrain from telling your own tales and wowing them. Let them ask you. It will happen. It’s human nature. But by you showing a genuine interest in their skills, in them, you forge that bond that will elevate this person into your upper echelon of helpful networked individuals.

Once you have exhausted these initial questions, realize that that may be the end of this interaction. You may or may not have made a friend for life. Time will tell. But it is important that you break away before it becomes awkward. Let this happen organically, naturally. Remember to not monopolize their time. Selfishly, you are here to form bonds with as many people as possible; adding each one to your network. Sticking it out beyond usefulness does neither of you any good.

Let’s Pretend

You find yourself standing next to me in a hall. You turn to me, without looking at my badge, and ask me ‘Who are you?’ I will probably grab my badge to double check the name, and then tell you my name. I will then probably do the same to you, asking your name, while still looking you in the eye. My eye will then wonder to your name badge to see it visually, as I attempt to burn your name and face into my memory. Sometimes this burning succeeds, other times it does not. But I will always remember this interaction with you.

Next you ask me ‘Where do you come from?’ For me, this answer requires a bit of time. I come from California. That’s where I was born and raised. It’s part of who I am so I will answer with this bit of information. But I will also tell you that I am from Utah, as this is there I live now. With this information you have a plethora of follow up questions. You can ask me about where in California I grew up, why I left, if I know Fresno, where your uncle lives and you visited once. I will tell you that I do know Fresno, and have visited friends of our family many times. In fact, they live in Clovis, nearby to Fresno. Since I opened up more information about Fresno, feel free to dig into this. At this point you realize that you know someone that lives in Clovis, and ask me if I knew them. Why not? I may know them. You’d be surprised. Next you may ask me about my living in Utah. Here you can be adventurous and ask me if I am ‘Mormon’, or even more adventurous and ask me how many wives I have. This will lead to some interesting discussions. Maybe you only want to ask me if I have visited Lake Powell or Zion National Park, or if I snow ski? Realize that you have not said you do any of these things. You are asking me about me. You may want to ask ‘Where do you work?’ This too will open up a plethora of follow up questions. See the pattern here? You are not talking about you. You are asking about me. Letting me go on and on about me. It’s one of my favorite topics.

You get the idea. Start small. Grow organically. Let the networking happen easily and efficiently. Stick to these simple questions and branch out when you feel comfortable. Learn about them. Add them to your network. Remember this interaction. Make as many of these as you can.

When you find yourself having 10 seconds near another human, lean over, look them in the eye, and then ask them Who, Where and What. You may have just found a friend for life. At the least, you may forge a bond with another human that can be added to your network. Goal accomplished.

My experience with the RedGate SQL Azure Backup tool

Success!!

I should end this blog post here. You should simply be happy that it worked. I got it to work. Woohoo!!

But, alas, I write here to help me. And you. So, on with the explanation.

Last night, I had some issues with the command line tool seemingly doing nothing as I submitted to it various commands that I knew worked elsewhere. Manually I could get it to work just fine. Thru powershell or SSM via xp_cmdshell, I could not.

After various discussions with Grant Fritchey (t|b) and others at RedGate, Grant asked the dumb question that needed to be asked. "What version are you on? I'm on version X". While I thought this was silly, as did he, that was the solution. The version that I had simply wasn't working like we both expected. Upgrade, and viola! success.

So here is what I did. On my local machine, I created 1 SQL Agent Job was created with 3 steps. This job will reach out to the cloud, grab the database I want to backup, and bring it down to my box for safe keeping.

Step 1 checks if the database exists, and drops it if it does. Here is the code:

1. IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
2. DROP DATABASE [MyLocalDatabase]

Step 2 calls the Command Line utility for SQL Azure Backup. Here is the code:

1. EXEC sys.xp_cmdshell 'C:\redgate.sqlazurebackupcommandline.exe
2. /as:MyDatabase.Database.Windows.Net
3. /ad:MyDatabase
4. /au:MyUser
5. /ap:MyPassword
6. /ls:MyLocalMachine
7. /ld:MyLocalDatabase'

Step 3 calls the Command Line utility for SQL Azure Backup. Here is the code:

1. IF EXISTS ( SELECT * FROM master.sys.databases WHERE name = 'MyLocalDatabase')
2. SELECT 'Database exists'
3. ELSE
4. BEGIN
5. SELECT 'Database does not exist'
6. RAISERROR( 'Database does not exist', 16, 1)
7. end

Nice and simple. This job will backup my Azure database to a local copy.

You may be asking yourself 'Why?'. Since Azure has redundant copies of my database already, why do I need to make a backup at all? The scenario in which this task becomes important to me is within the scope of Release Management. As we are uploading new features to our Azure hosted solution, we inevitably need to make database changes. In order to ensure continuity of service and function, I ALWAYS enforce a process of backup/snapshot/something of the existing database, prior to and after changes. This extra step will save me time if we ever need to revert back to another version of the database, even an Azure database.

My basic release process is like this.

1. Become involved in the Release as soon as possible to assist with architecture
2. Review proposed DB changes
3. Produce Release Plan of all steps necessary to perform release
4. Review Release Plan with all necessary folks
5. Ratify Release Plan as a group
6. Test Release Plan in environment that mimics target environment
7. Prior to actual release, take a Snapshot and/or Backup of target database
8. Perform Release according to Release Plan
9. After actual release, take a Snapshot and/or Backup of target database
10. Compare Pre and Post Snapshots and/or Backups
11. Resume normal life

In the normal DB world (not with my head in the Clouds) I have various tools and processes that have helped me along this path to a very successful release process. Granted the process may not fit in every situation and needs to be flexible, for the most part I follow it to a T. Once a process has been established, and a set of tasks can occur to fulfill that process, automation is my next step. Remove the human factor from the steps to ensure success. Remove me from remembering what steps i need to take to accomplish the task. Automate!!

Introduce the Cloud, and the inability to perform some of these tasks and I get freaked out a bit. Luckily, designing solutions is something I enjoy doing, and this was a fun one to work on. I now have an automated task that I can click once, and perform the backup I need, allowing me to continue with my Release Process.

My thanks to those that helped me. I hope that this may help you as well. It has helped me. I now have a repeatable task that I can kick off and not worry that Ive forgotten any piece of the puzzle. Next release to the Cloud (next friday) we'll use this little piece of the repeatable puzzle. Soon, a monkey could perform the steps for us. And yes, I may be that monkey.

My experience with the RedGate SQL Azure Backup tool

We have a few databases that are in SQL Azure. We need to back them up from time to time. Since there isn't an easy way to do this from SQL Azure itself, we have started using RedGate's SQL Azure Backup tool. The UI works great, even though the little slidey effect gets old after the first few executions. I was able to quickly and easily connect and backup my databases from the cloud to my local machine. Backup!!

The next time I went to backup, it errored, because the database already existed. I figured I could easily fix this with a quick rename of the existing database. But that was not the case. Since the backup was actually creating files based on the internal names and that DB already existed. I had to drop the database. I could have done something else, like backup the local, and restore it to keep a historical copy. But I simply dropped the last backed up db. Then the UI was able to backup without any issues.

Next? Well, why not try the command line. If you are like me, it will take you several (read many) iterations to get a command line anything to work properly. After quite a few iterations of stupid mistakes on my part, I finally got a simple command line script that would work and backup my db. Yeah!!

So, now I have a process. I drop the database. Then I run the command line and I have my database backed up. Yeah. Except for the parts where I have to do all those steps, over and over... that part sucks.

So I start thinking. I see these as 2 steps. 1 step to drop the db could be put into a SQL Agent job. The second step of running the command line, I could put into a batch file and execute via SQL via another step in the SQL Agent job. Yeah.

Boo.

The drop step works just fine. But the second step does not work. I have a batch file that I can execute and watch it process the backup successfully. But when this is wrapped in a SQL Agent job, it does nothing. So I grab the xp_cmdshell (I know, I know) statement and execute this in SSMS, it seems to take some time, then returns nothing. And apparently nothing has transpired either. No database was created. Nothing. No error. Hum....

I tried Powershell to no avail also.

So at this point, I have a job that kicks off both steps. Both succeed, but only the database is dropped. I will then manually kick off the batch file and backup the database via the RedGate tool command line.

It works. Its 2 steps. Its not automateable yet. But I am closer. Always getting closer.

Soon, I suspect, I will figure out what I did horribly wrong and remediate it, and I will have an automateable solution that will backup my Azure database to my local machine. At least I have that backup. And this will make me happier when we release code to the Azure database.

Backups, snapshots and a budding release process to the Cloud. We're getting closer.

Now I want to be more efficient.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS? addendum

I wrote this blog post the other day about SQL PASS and how to experience it and ensure that you experience it all. Break out of your shell and soak it all in.

But I forgot to mention a few things, and I hope to clear these up in this post.

A few of my favorite things that always happen at the Summit are

SQL Server Central opening party

Welcome Reception & Quiz Bowl

Exhibitor Reception

Women in Technology Luncheon

Friends of RedGate Dinner

Keep in mind that these events are on different dates and times. And they often come with food. And people. People that you should be networking with. In a situation that allows you to network and get to know each other, ifn you'll simply open your mouth and talk to the other humans.

The SQL Server Central party is one of my favorites. Its always a relaxing event, with easy social interactions. Steve Jones and Red Gate put on a great party that allow us humans to interact easily with games, giveaways and fun. I would suggest you find your way to this event and join in. Its a hoot. Maybe you'll loose at whatever gambling game you participate in. Maybe you'll win a prize as Steve randomly circles the event giving out tons of swag. Maybe you'll make a fool of yourself and have the entire table yelling and screeching as you attempt to reach 21 and explode when you actually reach 21. In any event. you get my drift. You should attend, meet some great folks, play some games, and you might even get a descent shirt out of it.

The Welcome Reception and Quiz Bowl are a huge opening event, prior to the SQL Server Central party. It has food. It often has music. I've seen video games in the middle of the room. It may even have food. You wont really need to buy dinner. And all of a sudden there will be a few folks up on stage answering questions and amazing you with their knowledge and humor. The Quiz Bowl is always an entertaining event to participate in. You could even win a book or other swag as the quiz is going on by merely being close enough to the stage to join in.

On Wednesday night, after all the sessions of the day, you'll find yourself wondering into the Exhibitor Reception, which is located in the Exhibitor Hall. Besides all the people you can network with and wave at and join in with conversations, there's food. Again, dinner doesnt need to be purchased as there will be descent food here to keep your motor running. Take an opportunity to wonder around and visit with the vendors. Get some swag. Get some food. But don't forget to talk to the humans. They are all around. They have knowledge that can assist you in your day to day work. Or maybe you can help them. Maybe you need to make a list of things to talk about and refer to it, to assist you. But open your mouth. Talk to the others. Remember we are all there for the same purpose. To learn. To Share. To Network.

As a father with female offspring that I often do not understand, I find that attending the Women in Technology Luncheon is an eye opening event. Learning from the expert panel is always enlightening. I tend to sit there and think about my girls and how I can share this information with them. Help them enter the workforce ready and armed with what they may need to make it easier on them. This event, though it contains the value 'Women' is not solely intended for the women of the Summit. Its for us all. It helps us all. Oh, did I mention that there is food? Yeah, there is. Lunch. Good lunch. As you attend this event, put your head on straight and participate. Soak in the atmosphere and the topic.

Another event that I love to attend is the Friends of RedGate Dinner. This is such an honor to be a member of this fine club. Being able to rub shoulders in a social environment with the employees of this company is always engaging and fun. There is food, but that's actually not the top priority. Being able to interact with this crew of folks by far outweighs the food. This allows mere humans like me to be in the presence of many fine folks that have influence in the community as a whole. Open up your ears and you can learn tons from these folks. By volunteering in the community, gaining a position of influence and being able to share with folks is what allows you to become a member of this group of 'Friends'. Sidle up to these folks and learn. Learn all you can.

So, as you can tell, there are a lot of scheduled events that occur. Check out the schedule yourself. Try to hit as many of these events as possible. Especially these on my list of favorites. Maybe some of these can become your favorites.

Other things that you can do that I have loved to do.

Photo Walk

Breakfast at Lowells

Grabbing fresh fruit and other snacks at Pike Place

Wandering the streets towards Pike Place

Visiting a French Bakery with your new southern brother

Make yourself some goals. Write up some talking points and use them in a pinch to open discussions with other people. I guarantee that if you will make an effort and talk to the humans, you will find several fine folks that will become fast friends and cohorts in this journey we are all on to better ourselves thru professional development and education. You'll learn or share what you have learned. You'll make friends. You'll experience the best of SQLPASS.

I don't know anyone, I don't sing karaoke, I don't drink. Can I still have fun at SQLPASS?

The Summit for the Professional Association for SQL Server is the premiere event of the year for the group commonly referred to as PASS. Its coming up soon. In October to be more precise. If you are not planning on attending, stop what you are doing and make plans.

I attended my first PASS summit in 2004 in Florida after the hurricane had hit the Florida area. Since Disney World was close by, my family came along with me. We rented a condo and stayed there for the duration. I had a rental car, and I would get dropped off at the Summit while they went to Disney World. I joined them twice, leaving the summit once early, and again on Friday. We went as an entire family on Saturday and then flew home. Each day I would arrive at the summit after breakfast and usually after the keynote. I would attend session and after session, learning tons of valuable information, and then meet my family somehow afterwards. That was the extent of my participation in the summit.

Looking back and realizing that some amazing memories were created with my family at Disney World, I think I would do things differently now. Knowing what I now know.

I would attend the summit earlier each day, earlier than breakfast. People are there already, milling about. I could get to know them. We could network. I could learn from them. No just the sessions. I would talk to more people while at breakfast. I would ask what they did, what they struggled with, and I would share those same things with them from my day to day experiences.

I would attend the keynotes and instead of making sure I had that saftey chair between me and anyone else in my row, I would sit next to someone. In fact, if I saw a group of folks talking and chatting, i might siddle up to them, if nothing else than to listen in and hope to join the conversation when I could. Inbetween sessions I might look again for a group of folks that seem to be discussing interesting topics and join in. If I was late to a session, I wouldn't stress about it. I'd attend when I could.

But what about drinking? What about all the parties? What about SQLKaraoke? What about wearing a Kilt? None of these things are things I would do in my day to day life. I never go to a bar. I have never drank alcohol in my life, nor do I smoke. Why would I go to something like this? Should I go to a gathering like this? Should I wear a kilt?

Well, let me tell you that after the first summit, I went to my next summit and implemented some of the things I mentioned above that I said I should have done from the beginning. I met people. I talked to people. I ate breakfast with people. We discussed issues. Soon I learned that there were even more networking opportunities out there for me. After the sessions had closed, people kept doing things. Kept hanging out. Yes, lots of them drink and even tend to sing crudely in front of an audience. Some will even wear a kilt. And no, these are not things I usually do, so I would usually return to my hotel. I should avoid them? WRONG!!

At one of my early volunteer outings with other PASS volunteers I realized that even if I didn't drink, I could still have fun with people that did. I talked. I networked. I commiserated. I discussed issues. I partied like I party. When someone tried to make fun of me for not drinking, another PASS volunteer stood up and defended me. He didnt drink either. But he sure likes pie. Instead of leaving or avoiding the gathering, I met more and more people. More and more opened up to me. I met even more people. It's insane the number of people you can meet if you open yourself up to meeting them.

Remember, they are all like you. There is not a cool kids club. We are all part of the club. Its up to us to determine how much we participate. Or not. And if we don't, we are simply hurting ourselves. Because others are meeting, talking, planning, sharing and learning from each other. Why aren't you out there with them doing the same?

Sleep when you return home from PASS. Soak up every moment you can with the SQLFamily.

Get out there and meet them. Open up. Experience PASS.