SQL Prompt – The Power Within

*Photo by Red Gate

*Photo by Red Gate

Come a little closer; no seriously come on over just a little closer as I don’t want to you to miss this. Can you hear me? Good, listen if you are in this Data Professional game for very long than you will have some “go to” utilities when you need them. Over my 14 year career thus far I’ve accumulated many scripts, procedures, techniques, and vendor related products.

One product that has remained steadfast for me over the past several years is Red Gates’ SQL Prompt. If you are already using this utility than you know what I am talking about; if you haven’t then you are missing out. I’m continuously amazed at some of the options the product contains. A brief overview and you will find the following:

  • IntelliSense-style code completion
  • Customizable code formatting
  • Save code snippets
  • Refactor SQL code
  • Tab History – save, search, and recover tabs in SSMS

Why did I bold Tab History you may ask? Well pull up a chair, sit down, and listen up.

The Setting

Have you ever had one of those mornings where you wished you would have stayed in bed, perhaps one might call it a “do over”. Well that was me not long ago; the day was actually fitting as the rain was pouring, had no umbrella present, and forgot the ole key card to the shop. For the ones that know me also know that I quit drinking coffee a while back. Ha, yes, I usually get the jaw drops for fellow co-workers and beyond; if there ever was a day I needed some then it was that day.

I sit down, fire up the laptop, and off we go. I starting looking at my plate and opening up a script I had saved the previous day; or had I?!?!? I go to my repository and nothing, absolutely nothing. As I mutter the words to myself, you got to be kidding me, I dive further into my folder structure thinking I might have misplaced it. Aha (there will be more coming in a future post about this little word), I know what let’s open SSMS and check the most recent files. I know I saved it the day before and I probably just put it in the wrong place. Wait, what, wait a minute – nothing there either.

Well this day is just off to a great start; isn’t it?

The Power Within

As I stand up and start walking to the break room to clear my head, get back on track, and re-asses the events thus far I remember something. SQL Prompt has a built in feature that might save the day. I come back to my desk and look at the following:

Tab History

Tab History – two words. Who knew that they would be so important on a day that started off bleak. I click on the Tab History and complete a search of the word “local” since I knew what was inside the script:

Info

 

Scrolling down through on SQLQuery7 I found my script I was looking for! Yes, if you heard a loud clasp of thunder or the earth moving some more on it’s axis that was me doing a SQL Happy Dance. This one feature saved my bacon due to deadlines that I had to meet the next day.

While this utility is a nice feature, we need to look at the utility in terms of a broader scope. SQL Prompt offers much more.

  • Allowing the ability to write code more smoothly
  • You can customize your SQL Code formatting rules
  • Affords you the ability to save code snippets and better yet share them across your team
  • Refactors SQL code
  • Exploration of your database (another feature I like)

Conclusion

A data professional is always looking for ways to improve their processes and become more efficient. SQL Prompt is a utility that accomplishes that goal. If you are a seasoned vet of it then great; however if you have never tried it then give it a go. You will be glad you did.

I tell you what; check out what my other colleagues have to say on something they learned recently:

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

 

,

3 Comments

T-SQL Tuesday #56 – Assumptions

SQL-Tuesday.jpgQuestion: “What time is it?”

Answer: “T-SQL Tuesday time”

Question: “What time?”

Answer: “T-SQL Tuesday time”

“I can’t hear you?!?!?!”

That’s right; it’s that time again where we come together for a block party T-SQL Tuesday #56 style; which this month is hosted by Dev Nambi (blog | twitter). Dev has garnered a topic around what assumptions we make in our work environment within the realm of SQL.

The Assumption

One of the biggest assumptions I’ve come into contact in many places is the saying, “I’m only as good as my last backup”. While that is a semi true statement it does leave the process unfinished. Let me explain:

Johnny (picked a name out of the air) is given a task to create a new database and with that he designs his maintenance strategy. Backups fall into that maintenance strategy. The new mechanism to take a backup is put into place and we are set; full backups set to run off hours.

At least this is what a lot of data professionals assume; taking a form of backup is fantastic. This post is not meant to go into the details of how to take a backup but based on the assumption that your backups are good. The second half to the equation is restoring those backups.

What? You mean I need to actually test my backup to see if it works. Yes that is exactly what I mean. I have seen several cases where backups are taken and everyone sleeps at night. Then the business unit decides they want to pull that backup; you go to restore the backup and the file is corrupt.

Summary

Don’t just assume that your backups are ready to go. Take a more proactive approach and test your backups. Granted each shop is different with their own standards and regulations; with that said if someone asked you to restore your backup……could you with confidence?

What Is T-SQL Tuesday

T-SQL Tuesday was created by the mighty Adam Machanic ( Blog | Twitter); if you are interesting in hosting a T-SQL Tuesday party or want to learn more about it check out his blog. Let’s get involved and make our community that much better.

Leave a comment

Worst Day As A DBA

5Worst_FoRG-banner_613x90_ChrisYates_V1

I remember the day pretty vividly, it was in the summer months and I was as green as green can be coming into the technology field. Walking through the doors to the start of my career  I was ready to tackle the world. The training for new hire sessions had already been completed and it was game time. I didn’t know what a SQL High Five meant at the time but I would have given one to everybody as that is how I was feeling walking through the doors.

The introductions began and I got the normal “new guy”, “fresh meat”, “greenhorn” labels that most people call newbies. As we turned the corner I noticed something that looked a bit off to me. A pen cap stuck in the keyboard by one of the developers. Hmm that is odd, they didn’t teach anything about pen caps in keyboards in college. That person was not at their desk at the time but we did find them in the break room and he was introduced to me as one of the developers.

Time past and the introductions complete I went back to my desk in my little cubicle and couldn’t help but think back to that pen cap. The curiosity was too great so off I go; back over to the desk with the pen cap, mind you this is about an hour later.

Hey man, I got to ask you a question. What’s up with that pen cap?

“Oh, yeah I was building an import process and I forgot and left a MSG box in there. I started to load the files in and instead of stopping it I figured this was a quick way to get through it.”

Hmm, interesting tactic but the red flags and sirens started to go off in my head. Being the new kid on the street and the youngster I went to one of my peers and started poking around a bit. Explaining what I saw I was amazed to learn that this in fact had happened before.

“Before”, little did I know those 6 letters would start to build the foundation of my DBA career. What, wait a minute….”before” you say? Yeah, ole Billy (no not his real name) over there has done that “before”. Nice, so I go back to my desk again and sit down. I take out a pen and paper and start writing down my questions.

1. Where is Billy loading this data?

2. What kind of data is Billy loading?

3. What kind of access does Billy have?

4. Does my boss know this?

5. What method is being used to import the data?

6. Who is the business owner?

Now realize I hadn’t even turned my computer on yet to get the lay of the land. Off I go with my questions.

Um Hey Billy, just out of curiosity where you loading that data? Prod he replies

My heart sunk, I knew the writing on the walls and where this was going. What kind of data are you using? Client data for our system. Back to my desk I go; sit down flip on the computer. I started researching, digging, and sure enough my thoughts were now a reality.

Dev Ops had gone rogue and had access directly to prod. Remember the pen cap; well after realizing that the import was loading more data than the file had in it we discovered the app didn’t have a stopping mechanism and no duplicate checks. In the end we were loading a 100,000 record file 8 times!

Light bulb goes off in my head, as I turn to colleague. Hey where is the last backup? It is on x drive but it won’t do you no good. Why is that I ask; yeah it’s a week old. We run them manually before we leave for the day.

In the end that worst day started off my worst week but looking back I believe that worst day started the groundwork for a solid foundation. How or why is that you ask?

1. Security – I’m a huge proponent of it and probably rightfully so after enduring the major cleanup that ensued.

2. Documentation – no documentation was found anywhere; we all can do a better job of this; me included.

3. Don’t be afraid to speak up; if something is off to you question it. Research it. Dig in and figure it out.

4. Just because something is done one way for years doesn’t mean it is the right way. Evolve and become more efficient. Do you think having a pen cap on an enter key to load data is efficient?

5. If you are a newbie and seasoned vet review your systems on a routine scheduled basis.

6. Backups – are you taking them? If so are you in turn testing them or validating them?

Some of the things I know now that I didn’t then are handy utilities such as Red Gate’s SQL Backup Pro that could have benefited me; take a look at their arsenal for the data professional. A wide range of products that will allow you to streamline your processes and tasks.

I look back on my time there and we brought it so far. We righted the ship but it was no easy task and is not for the faint of heart. It taught me to chip away at the wrongs and turn them to rights. I speak a lot about being a game changer. That means if you see something amiss go after it. Make it right.

While I have had a few “worst” days since then, I’ve learned one thing about being a Data Professional ~ being one comes with a price tag of having great responsibility. Don’t abuse it.

 

5worst_1_300x250_NEW

,

Leave a comment

PASS Summit – What Does It Mean To Me?

PASS_14_Google_240x400 (1)PASS Summitwhat does it mean to me? So listen, I’m not perfect. I will never claim to be and you will never her me utter those words. I make mistakes every day, but I try to learn from those mistakes as much as possible.

I was asked by several people yesterday via email and word of mouth conversations what PASS Summit meant to me and how are community is. During the work day I was not able to keep up with all the happenings since session selections came out, but I did catch quite a bit on my feed. Since being approached and being just one voice in this big game I thought it prudent to share with others what PASS Summit has meant to me.

For me personally, PASS Summit changed my career. I rolled into town (Seattle) back in 2011 not sure what to expect. Brand new to this scene; I didn’t know anyone from anybody. I can still remember to this day walking into the convention center thinking to myself, “What in the world did I get myself into?”

Each session I went to seemed to give me something I could take back and incorporate into my job. I was able to meet and interact with fellow colleagues in technology from all over the world. It exposed me to another part of what we call “The SQL Family” I had not known before.

I can recall purchasing the Deep Dives book and introducing myself to all the MVP contributors. Eating breakfast and lunch with 5000 people before going to learn and try to enhance my skill set. It lit a fire in me for my career that I hadn’t had before. Some of the techniques learned then are still part of my everyday work now. So, as you can see the PASS Summit has meant a great deal to me and where I am at today.

Fast forwarding to today; what spawned these questions to me by others stems from session selection discussions. I will not dive into processes or procedures as I am not privy to the background and the inner workings of selection of sessions. That’s not my goal nor do I want it to be with this post. I see many points some valid and some not in my opinion; however I do believe that is part of being a SQL Family / community. We can share our thoughts, opinions, concerns and review processes, policies, and procedures. It is the basis and foundation on how we grow and improve. At the end of the day we are all in this together.

I look forward to attending this year and learning from a great group of speakers. Heck all the volunteers, speakers, attendees take time out of their families lives, work schedules, and the like to attend. It is definitely a unique environment and one that I hope can continue to grow and overcome hurdles.  Who knows I hope in the very near future my session is selected maybe then I to can share what I’ve learned along my journey.

Looking back I can honestly say the conference in its entirety changed my career, my outlook, and my drive. I don’t have all the answers but I will continue to give it all I got day in and day out, and from a past attendee I thank all the speakers (both old and new) and the volunteers that make this happen.

What about you? What do you think about PASS Summit? What are some of your opinions on the process for selections? Can we improve; if so how?

 

,

2 Comments

SQL Saturday 286 Roundup

IndianaWeslyanI got to admit, this SQL Saturday was a power packed lineup with some really good talented speakers. Due to other speaking obligations I was unable to submit a session or attend the full day, but the first half of the day did not disappoint. The venue at Indiana Wesleyan was fantastic and my hats off to all the speakers and volunteers.

I was able to attend 2 and a half sessions (yes you read that right as I wasn’t able to stay for the full time for the last session). I didn’t realize it then but looking back was glad to see that the sessions in which I did attend were my fellow Friends Of Red Gate colleagues.

Session 1: The day started off early with a session on Changing Your Habits To Improve Performance of Your T-SQL  by Mickey Stuewe ( B | T ). The session was a smooth session that captured the audience both with intelligence and structure. Several good points on how to improve SQL queries, formatting, and cursors. It was even nice to see an attendee ask what formatting tool was being used; which the reply was SQL Prompt.

Not long into it one of my fab five walked into the room Mr. Steve Jones ( B | T ) himself. It was an honor to meet him and have him give a session here in Louisville even though I could not be in attendance for it. He was one of the most down to earth people I’ve met in the SQL Community thus far.

Session 2: Next up on the list was a speaker I’ve been wanting to hear for awhile ~ Wayne Sheffield ( B | T). His session was titled Table Vars & Temp Tables – WHAT YOU NEED TO KNOW!  This session was not for the faint of heart as we dove into a fair good amount of technical data surrounding myths and the like. A very interactive session that provided some great insight into the internals of the structures found in tempdb etc. For those who have not heard Wayne speak on this session I urge you to download the session and go through it at your leisure; there is a treasure trove of goodness to be found.

Session 3: Last up on my list prior to my departure for my own speaking engagement was none other than the Scary DBA himself Grant Fritchey (  B | T ). His reputation proceeds him, but I found it awesome that when I walked in that he was doing burpees (which is a form of Crossfit training). His session was one I wanted to stay for Best Practices for Database Deployment. Grant is one of the coolest speakers I’ve been able to hear in a while; not sure if it is because I can relate the DBA portion or what but the first half was spot on with structure of DBAs, developers, releases and finding the middle ground. At the end of the day we all want to end up at the same goal and working together not against each other is key.

I hated to miss Steve Jones,  Ed Watson ( B | T), and Jason Brimhall’s ( B | T) afternoon sessions but glad they are able to be downloaded here. (please check the schedule tab; downloads will be found there)

I’m glad I was able to meet a lot of new faces and interact with some fresh talent coming up. The community is alive and well guys; groups like what was experienced today in Louisville shows the eagerness of people wanting to learn. Taking their own time out of their own days; thankful for families who support us in our SQL endeavors. I can’t wait for next year to submit some session(s).

None of today would have been possible if it wasn’t for Malathi Mahadevan ( B | T). My hats off to you for your continued effort year in and  year out.

Until the next time……

Leave a comment

Alerts – Who Knew?

CollaborateImageThis week I am back at it with my SQLCoOp friends in sharing something new that we’ve learned since our last post. You ready? Great, pull up a chair and let’s see where this takes us shall we?

Wait a second…….do you hear that……..no, listen closer…….ALERT ALERT ALERT! Sirens seem to be going off all around and somehow we found ourselves in all hands on deck mode. Have you ever been there before? Chances are if you have worked in technology at all this has happened to you at one point in your career. If it hasn’t then eventually it will; trust me.

How do you handle such scenarios when they do come up? Reporting of alerts off your systems or applications can be very useful and while there are many ways to accomplish this I was introduced to an SSIS method I had to deploy this week. It made me dive in a little deeper and look into how the methodology behind it. If we break it down; it can be done so my analyzing 4 steps within the package.

Main Package:

SSISAlert

The main package will consist of a database call, a for each loop container, setting of a variables task, then a wsdl (windows service) by utilizing an expression. The intent of this post is to show how you can make these calls happen and not to go into depth of what is located within the procedures or wsdl file; that shall be something for another today or better yet this should set a foundation in place for additional research one can do…..thus the learning something new part.

Getting the Exceptions

The Get Exceptions Alert utilized a simple SQL Task editor; once you create this editor you will notice the below screen shot. To complete the set up simply fill in the name and description. The next two important properties are the most important of them all. The connection and the SQL Statement. The connection is simply the database to which you are making the connection to; the second is executing a stored procedure that will gather all the alerts by the application that need to be emailed out.

SQLTaskEditor

For Each Container

You remember the SQL Task Editor we set up in the first step; this next step is what takes that result set; loops through the iterations and gets a collection of the alerts to be sent. Part of the homework is setting up the return result set in the SQL Task Editor. Once that is complete then you set the result set in the ADO Object Source variable noted below; this will allow multiple iterations to flow through. In this case the selection of enumeration mode should be set to rows in the first table

ForEachContainer

Set Variables

One of the key components are the variables to which you will utilize. These may be different depending on the in depth alerting that is wanting to be accomplished but for simplicity sake I will list out what the one I ran across was utilizing. We will notice that we have seven variables that will be set.

  • Alert – the alert name
  • Alert Info – what is so fascinating about this alert
  • Alert Type ID – correlates back to the type of alert that was set off
  • Result – the result of the alert
  • Result Data Set – the data that caused the alert and in this case not used
  • Source Process – what process triggered the alert
  • Source System – what system did this come from

SettingVariables

Registering the Alerts

Registering the alert and sending the email notification is the last step to the puzzle. This is done by utilizing the windows task service editor within SSIS. As you can see I have a wsdl file located on my local drive in a directory called Windows Services. As I mentioned before, I will not be going into detail of the contents of the wsdl file; simply know that it will trigger the email notification. The result parameter will be fired off to a group of individuals to review on a time based period. That parameter value will be supplied in the output section of the Web Service Task editor.

RegisterAlerts

Conclusion

There are several ways to arrive at this same goal and I found this one to be unique and one that is not traveled often. The package can be set up to be executed by the Agent on a time based interval. Whatever the case may be and whatever method that you do choose the important part is to keep learning.

I tell you what; check out what my other colleagues have to say on something they learned recently:

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

,

3 Comments

Trumpet Sounds; Call To Post

ChurchillThis past weekend was the KY Derby. All eyes are on this one day of horse racing; celebrities come in to town in what seems to be a whirlwind two days. According to experts over 100 million dollars is pumped into the local economy.

I get to see all the hoopla firsthand, and throughout the week can walk down the street from the shop and see no telling who walking down the street on their way to the limos, Escalade’s, and the such.

The Race

Then comes the day of the race. The big race where people go to the track spend the day, and then in the evening for 2 minutes, which has been deemed by others as the most exciting 2 minutes in sports, the horses are loaded in and off they go. Some 160k people cheering in the stands and millions of dollars are at stake.

The horses start off in a pack going into turn 1,2, and 3. Jockey’s maneuver their way in the field to position themselves for that final stretch. Turn 4 and horses are in their stride; jockeys kick it into gear and now an all out sprint ensues. Sometimes there is a dead heat; sometimes there is a huge gap, but nonetheless it is a mad dash to the finish.

The DBA Race

As I went throughout the week leading up to the Derby my mind kept wondering to how closely it reminded me of the life of a Data Professional. Being a Data Professional means that you will be in the same space and many other fellow Data Professionals and if you pay attention closely all jockeying for that last leg to the finish line. I’ve kind of stepped back and started looking at how I was running the race.

If you go at an all out sprint in turns 1,2,3 would you really have anything left on turn 4 in the home stretch?

What if you laid back in the field and waited for the opportune time but come to find out you waited to long and the bottleneck wouldn’t give you a clear shot at the end goal?

Or you could be like the one jockey I saw get thrown from their horse during the race.

I’m finding that being a Data Professional and, to be more exact, being a DBA my journey can be at times related to a race. I remember when I first started out how excited you become; just like starting out of the gate. Then, as you go through your journey and you see some of the Data Professionals that you started out with start to fade. Whether they get burned out, choose a different career, move on, or whatever the case may be the numbers start to drop then it is another cycle where new faces come in.

The Call To Post

The trumpeter starts the call and in walk the Data Professionals, as we get ready to prepare to run our race don’t settle for the being average. What are some of the ways when you start out of the gate as a DBA you can do to prepare yourself  for the journey? I’m not sure there is any one right answer but I can share with you what has helped me along the way. It’s not rocket science and it is not something that you can snap your fingers and it be done. If this helps just one aspiring DBA then so be it; it would be worth it.

  • Community – I was late to this game and had I become more involved with the community earlier on in my career I believe it would have helped me more. I’ve already elaborated on my “Fab Five” and if you haven’t read that yet check it out. For me I looked for people in my industry who were at the top of their game and who I knew were what I considered the best of the best. Learning from others in the community is a huge positive and one can garner much knowledge from others if we would do just two things – be still and listen.
  • Initiative – how bad do you want it? If you are expecting someone to hand you success and you are a data professional then you are in the wrong business. Early on in life I was taught discipline and a hard work ethic. If you want it that’s fine; go after it and prove to yourself that you can do it.  One quote I always remember being told to me is that “Somewhere someone is practicing getting better at the skill and goals you want to achieve; how bad do you want it?”
  • The Craft – learn it; live it; breath it. If you are a DBA like myself then what are some of the things that you can do to enhance your shop? Perhaps it is implementing a maintenance strategy around index fragmentation, or is it disaster recovery? Have you restored that backup yet, do you know if you were asked for a restore from 3 days ago could you do it? How about exploring the Plan Cache and getting a feel for how your stored procedures are acting? Oh wait do you know if you have any missing indexes present in your current environment? Speaking of environment have you documented your environments yet? So much work to be done in this are that a paragraph in a blog post doesn’t do it justice.
  • Ask Questions – have you ever been in a session or speaking with a group of people and you wanted to ask a question but didn’t because you thought it would make you appear to not know what you are talking about. Guess what, we’ve all been there. The end of the day every question is valid. If you don’t know then say you don’t know; research and find the answer that is called learning. The challenge is to continually learn; go back up to my Community point. My “Fab-Five” are ones that continually learn and hone their craft and skill set.
  • Don’t be afraid to make mistakes - now don’t take this point and execute something in production that causes your company an outage and money. No that’s not what I’m saying. Think outside the box; test new alternatives and do so in a manner that meets your shops integrity and criteria with environments.  Why wait for someone else to come to a solution; everyone can bring something to the table.

The Roses

Every horse that wins the Derby is dawned with roses over their mane. Guys I don’t know when my race will end, but when that time comes I want people to realize that I gave it my all with integrity, character, honesty, and in a trustworthy manner. I want that developer to be able to come to me and not worry about me chewing his head off (yes that has happened before), or the network guy who can come talk to me about space concerns, heck why not throw in the business unit requesting some help with architect something out. No we (DBAs) aren’t perfect and days will come where we flat out fubarb and make a mess out of it, but guess what? How did you run the race?

Rest assured we’ll get to the finish line one day. Let’s have some fun along the way and realize that we won’t be perfect everyday, run a steady race, and continue to work hard day in and day out. For it is in the face of adversity when true character will shine through.

For those just starting out check out the advice in a collaboration John Sansom made happen that I was honored to be a part of. For those that have been around keep working hard. Hard work will pay off; keep fighting the good fight.

, , ,

1 Comment

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: