Tag Archives: TSQL

T-SQL Tuesday #45–Follow the Yellow Brick Road

Leave a reply

SQL TuesdayIs it me or does it seem like we just had a T-SQL Tuesday blog party? These days are just flying by! This week Mickey Stuewe (b|t) is hosting and she has brought up a fabulous question about auditing with the topic being Follow The Yellow Brick Road. I have been on both sides of the fence in shops where there has been very limited auditing versus mandated regulatory auditing of their systems – so what’s my preference? Glad you asked; pull up a chair as we travel down the yellow brick road from munchkin land while trying to avoid the wicked witch and her ape like minions.

What to Audit?

As we start out on this journey, there are multiple questions on what needs to be audited one should ask themselves. Let’s face it, there are many wicked witches out there that just flat out want to get to our data and the longer I’m in the business I’ve seen more and more attacks from within, then from the outside. Here are a few ideas on some things to audit:

  • Tables with sensitive data such as SSN’s, personal information, transaction data
  • Review of your QA, UAT, and Prod environments (some shops like to audit all 3 some like to only audit Prod) – don’t be the one who audit’s none!
  • Check-ins of code into your repository
  • Inserts
  • Updates
  • Growth trends
  • Security Access
  • File Shares

These are just a few ideas, but don’t limit yourself to just auditing who inserts or updates data into your systems. As you fly through the house like Dorothy did in the SQL tornado remember to think outside the box some and audit other areas such as growth, baseline trends. The SQL environment is always evolving, use the necessary tools to keep one step ahead of the storm.

How To Audit

All right, so I’ve identified what I want to Audit. It seems like it is a daunting task and I have no clue where to begin; that’s where the Tin Man comes into play. If you are a Data Professional then you have to care about the data enough to even be reading this post – – means you have a heart.

There are many different avenues you can take to audit your systems, a few of those are:

  • Triggers within SQL
  • 3rd Party Auditing Tools
  • CDC
  • Home grown utilities

Whatever avenue you decide to take, just take the stance of doing something. Doing nothing is not something I would advise; knowing who is changing your data, schema, files, etc. is an important aspect of any data professionals life – who knows if you have a mighty Wizard of Oz such as your auditing department or compliance department they might just be thankful for your efforts.

Summary

When you are going through your own SQL journey on the yellow brick road remember that auditing can be viewed upon as an asset. Look into what you are doing currently, can you improve upon it? Are you doing anything at all? Is your data being protected? Even better do you know if your data is being protected?

Take the time to put some safeguards in place in the end you will be glad you did.

T-SQL Tuesday

Wait a minute, wait just a minute. What is this T-SQL Tuesday you speak of. The mighty Wizard of Oz, Adam Machanic (b|t), started this party in 2009. Basically each month on the first Tuesday an invitation is sent out that describes a topic for that month; the second Tuesday bloggers put together a post regarding the topic and then send it back to the person who is hosting. If  you have a blog and are interested contact the mighty Wizard of Oz and he’ll get you on the schedule.

Back to Basics

Leave a reply

BasicsI can sum up this blog post in two words: The Basics

What do you mean?

I cannot be the only one in this same boat. There have been times where I, as a DBA, have overcomplicated resolutions when there was simply a very non complex answer. Come on, you know what I’m referring to – the basics. In looking up the exact meaning of what basic says; the dictionary tells me it is the “fundamental or basic principal”. I took that meaning this week and looked deeper into how I attack some DBA related items and I keep coming back to – “keep it simple” and “get back to the basics”.

The Basics

When I say basics I’m coming at it from a DBA standpoint. Do you have your own checklist that you go by for daily, monthly, quarterly, yearly checks? If not, then this is as good as time as any to start.

What are some of the things to include in your basic checklist?

Some items to include, but not limited to are:

  • Backup processes and alerting upon failure
  • Review of jobs (if any have failed)
  • Anything in the SQL Error Logs?
  • If you use Policy Based Management (PBM) and receive reports – any action to take?
  • Security logs – have you checked?
  • If issues are found how do you handle them? Just don’t sweep them under the rug
  • Hopefully you have something in place that tells you when you are running low on space (storage)….if not might want to start thinking about getting something implemented; by the way how is the space looking?
  • You’re fragmentation process for indexes working properly?
  • Remember those backups you checked? You spot checking any restores to validate them at all?

Summary

The list above is just a simple list to get started with….get back to the basics of DBA work. It’s a fun and enjoyable ride, but keep one thing in mind as you traverse through the SQL terrain not to overcomplicate things. You will find many checklists on the web by some renowned DBA’s that one could model a standard off of if you don’t already have one. I’m a big believer in automation and automate what you can to help you become more efficient and streamlined. Get the reports emailed to you when you start your day, at best make sure notifications are set up on the jobs running in case of failure.

Keep it simple, get some standards in place, above all remember the basic principals. Don’t overcomplicate issues and when you run across them don’t sweep them under the rug and wait for the next DBA to come along to fix them; take the time to fix them correctly.

Give Credit Where Credit Is Due

Leave a reply

Should I give credit?

We’ve all been there; it’s late in the day and you have been staring at your screen for hours on end looking for an answer to your problem. After much testing; going back and forth you stumble upon an article on a technical website, blog, or Microsoft site that has the answer you have been looking for.

What’s Next?

As you look at the article and you implement the code that has been found you find that it works like a champ; your problem has been solved and crisis has been averted. Your colleagues come running up to you shaking your hand telling you that you are the greatest SQL DBA super hero that has ever lived, the boss comes over and pats you on your back and tells you that you are well on your way; all the while you never pipe up and say “Hey, I found this at…..”

The Reality

Okay maybe it doesn’t quite get that out of control but you get the picture. The temptation is there to take glory for something that you didn’t write or produce. Sure you’ve found the solution to the problem by doing hour of research but does one really take the time out of the high-fiving, chest bumping to note how you came up with the resolution?

Give Credit Where Credit Is Due

Here lately I have been on several technical blogs, had conversations where credit is taken for work that was done by someone else. I’ve seen it among teams, I’ve seen it nationally, and I’ve seen it accidentally. One thing that I enjoy about the SQL Community is that everyone is so helpful to provide assistance to others who are in need of help or need assistance in understanding an issue that they are working on. In saying that I believe it is of the utmost importance that if something is utilized that someone else has mentioned, wrote, helped with that you give credit where credit is due.

What Can I Do?

One might ask, how do I give credit to someone I don’t even know…….to me this is the most simplest of things. I’ve never met a person when I’ve approached them and asked them that I would like to mention them in my post about the tool, code, or article they produced turn me down.

I believe it is a way to show respect to our colleagues and to our community…..

I believe it is a justice that we must take on as a responsible SQL DBA……

I believe it is proper etiquette to display……..

Have you ever had a utility, piece of code, article that you created only to find out later that someone took credit for it? I implore others who may come across this article to take a step back and give others credit where credit is due. You’ll be glad you did and at the end of the day just be honest.

How Much Longer?

Leave a reply

There have been instances in my career where I’ve had to perform backups off a routine schedule; I mean it’s inevitable, right? It’s nice to look at job history to see about how long the backup job normally takes; can give an estimate or an ideal of what to expect.

However, if you are like me I like to monitor and keep an eye on how much longer my backup will take. Below is a T-SQL script that I’ve picked up and tweaked over the years to help determine an estimated time on how much longer my backup will take.

SELECT r.session_id ,
r.command ,
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
GETDATE()), 20) AS [ETA Completion Time] ,
CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
/ 60.0) AS [ETA Hours] ,
CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE ( r.statement_end_offset
– r.statement_start_offset )
/ 2
END)
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ )

I apologize beforehand of the code being so choppy; my plugin editor is not working properly at the moment. The result set of this code set will provide you the following columns:

  • Session_id
  • Command
  • Percent Complete
  • ETA Completion Time
  • Elapsed Min
  • ETA Min
  • ETA Hours

There you have it; pretty straight forward and when a backup is kicked off you can execute this query to determine how much longer the backup will take. If my memory serves me correct I’ve ran this on SQL 2005 or greater.