Archive

Archive for the ‘T-SQL Tuesday’ Category

T-SQL Tuesday #72 Invitation – Data Modeling Gone Wrong

10 Nov 2015 3 comments

SqlTuesdayT-SQL Tuesday is here again. I’ve had good intentions the past few times this event has come around and even have drafts still waiting to be queued up which I will eventually turn into regular blog posts, but I decided to just make time this month and jump back into the monthly party.

This month Mickey Stuewe (b|t) is hosting and has asked for some data modeling practices that should be avoided, and how to fix them if they occur.

What is Data Modeling?

Data Modeling itself is referred to as the first step of database design as you move from conceptual, to logical, to actual physical schema.

While that definition sounds simplistic, we can expound upon it to arrive to the conclusion that data modeling is a very important aspect from database design on all levels.

What to Avoid?

As a data professional and in senior management I’ve seen pit falls wide-spread in various business units when it comes to design architecture. The listing you are about to read are some of the methods and items I’ve discovered on my journey and conducting gap analysis type of events that carry a chain reaction. They consist of doomed failure from the get go.

  1. Audience – the audience and/or participants should be defined up front. I differ with many and that’s okay. To me the ability to identify business stakeholders, subject matter experts, technical groups, BA’s is an integral piece to the puzzle. Too many times I have seen the engine pull out of the gates with a design to only find out that the design and documentation to not even meet the criteria and standards of the shop.
  2. Detailed Project – how many times have you received documentation only to find out there was not enough meat to get the project off the ground? As a data professional we do think out of the box; however it is imperative to be clear and concise up front. When my team is given projects to complete that involve Database Design and creation, I implore business units to provide as much detail up front that is agreed upon. This helps streamline and makes for better efficiency.
  3. Understandability – With details comes the ability to articulate understandably. All to often items get lost in translation which causes additional work on the back-end of the database. This could mean unfortunate schema changes, large amounts of affected data, and so on.
  4. Business Continuity – ask yourself a question in design phase. Is what you are building that will be presented to the business efficient? Will business be able to decipher what is being presented back to them; if not why?
  5. Downstream Analytics – How does the business want to see this data in the form of analytics or reporting?  Most modern systems are going to either be queried by, or push data to, ETL processes that populate warehouses or other semantic structures.  Avoid complex table relationships that can only be interpreted by the code that stores the data.  Make sure you define all your data domains so that the BI professionals are not scratching their heads trying to interpret what a status of ‘8’ means. (In speaking with a colleague, Tom Taylor, at my shop – he brought up this valid point).

Items To Look For

Some key and general practices to look at and decide on are:

  1. Primary Keys – yes they are your friend – add them.
  2. Look at all audit data and what needs to be audited
  3. Clustered/Non Clustered indexes – have you read through your execution plan?
  4. Has the scope of the data model been met?
  5. Are tables normalized properly?
  6. One Data Modeling Tool – it’s easier if the team is looking at one utility together; if you have many varieties spread across many team members it could leave views skewed.

Conclusion

Data modeling, in and of itself, is a key component for any business. What often falls by the wayside is the poor leg work done up front. You have to lay a proper foundation in order to be successful with any design; taking into consideration all personnel in order to make the best strategic decisions to move forward.

Hopefully the next time you go down this path you have some questions to ask yourself along with some solutions to those problems.

What is T-SQL Tuesday.

Adam Machanic’s (b|t) started the T-SQL Tuesday blog party in December of 2009. Each month an invitation is sent out on the first Tuesday of the month, inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week. If you are interested in hosting and are an active blogger than reach out to Adam and let him know of your interest.

TSQL Tuesday #63 – Security

11 Feb 2015 2 comments

SQL TuesdayThis months T-SQL Tuesday is hosted by none other than Kenneth Fisher (B|T). His topic for this month revolves around security and how you manage security. There probably couldn’t be a more fitting topic; especially with the many breaches we have had lately both ones that are known and ones that are not known.

With that said I want to take this time to expound on a wider variety of topics instead of diving into specific targeted areas within SQL.

When I first heard this topic I immediately drifted to thoughts such as:

  • Afterthought
  • Responsibility
  • Lax
  • Validation
  • Vendor Apps
  • Breaches within
  • Password Strength

Afterthought

Countless times over the years I have seen, reviewed, fixed, and contemplated over security within SQL that simply was an afterthought. Security whether role based, AD Groups, etc. should be worked into any project plan. If you have ever inherited a system only to review that 600 users have sysadmin access you know how detrimental that could be to the data contained within.

Responsibility

Being a DBA means you have great responsibility. Every single database is under your care, own it. Each day someone will be trying to access that database; at least that should be your mentality especially with any production environment.

Lax

A lot of us are creature are habits. It is very easy for a data professional to fall into the trap of becoming accustomed to daily routines. Security should not fall into this category; I repeat security should not fall into this category. Do you know who has access to your databases and why? Do you know what user accounts are tied to specific groups? If you can’t answer this then you may find yourself in this category

Validation

I like this one, how many of us validate our security measures? Do we take any proactive approaches to see just how safe our data is? Maybe you rely on an outside 3rd party to see if they can hack in; whatever the case maybe it would behoove us as a group of data professionals to be actively testing our systems looking for points of entry. I will be completely honest; if you aren’t you can guarantee someone else is.

Vendor Apps

Yes, I am a vendor installing an app your company purchased and we will need sysadmin rights on the box or cluster. Um. yeah you go right ahead – NOT. I hope by now, as a DBA, you have strategies in place where you will work with the vendor directly or have some form of processes that allow for tracking of such activity. Remember, these databases are yours if you maintain them; you be the gate keeper not the other way around. Don’t let anyone on  your system without our knowledge and you better know what kind of data is on your system and who is accessing it.

Breaches Within

If you aren’t careful all your eggs will be in the “protecting from the outside syndrome”. Yes potential threats are rampant from people both stateside and abroad; with that said however have you ever thought about what maybe at risk within your own walls? Do you have safeguards in place for co-workers and fellow employees? Security cannot be just thought of with outside threats. No you need to prepare for both outside and inside threats. To make it even better if you are on a DBA team is your team being audited to keep everyone honest? The data should be your top priority

Password Strength

These little rinky dinky passwords aren’t cutting it guys. Ensure you are following best practices and standards when setting up password strength. The easier you make it the easier it is for threats and breaches to occur. Are the passwords on  your systems set to be changed every so often? But that would require a lot of work – yes and when you sign up to be a DBA or Data Professional you retain great responsibility.

Conclusion

Security is one place where you cannot be lackadaisical about. It is a crucial role within SQL or any platform for that matter that usually becomes an afterthought. If you are in a shop you should review your security guidelines and if you don’t have any I suggest that you take initiative and create some. Without proper security you ones business could be jeopardized and once  issues arise what would become of the companies reputation; or your reputation. Be proactive, make it yours, own it, and get it done.

TSQL Tuesday

This is a block party started by the renowned Adam Machanic (B|T); if you are interested in becoming a host one month and an avid blogger you can reach out to him via the methods above.

T-SQL Tuesday #61 – Giving Back

TSQL2sdayIt just seems to me that these block parties of T-SQL Tuesday come quicker each month. I’m always thrilled when I have the opportunity to host or participate.

This month Wayne Sheffield (B|T) is our host. Wayne is good people, first time I got to meet Wayne was at SQL Saturday in Louisville, KY. He was presenting a session on SQL Myths and busting them to pieces; it was truly a great session. Wayne’s picked a fantastic topic in getting the opportunity to reflect back on 2014 and looking ahead to 2015.

The topic in which he chose is simple; yet those two words can be quite profound Giving Back. More precisely what would you give back to the SQL Community in 2015.

Looking Back

2014 was filled with many opportunities, both from a professional standpoint and career standpoint. Appreciation and gratitude ran rampant with the many friendships formed and cultivated over this past year along with some great vendor ties. Now how do we keep that steam engine rolling down the tracks from a personal standpoint?

Looking Ahead

I could go on about the many speaking events, books and articles coming to fruition, sessions poured over – but that is not what I’ve come to a conclusion on. Anything and everything I’ve ever done I have given it my all. SQL is no different. To me, what I do is fun – it  isn’t a job; it is part of my DNA.

I will continue to strive to better myself and challenge myself; to be daring and pick up where this steam engine leaves 2014 and heads into 2015. How do I do that?

Investing Time In Others

Will my schedule slow down? Not in the least if anything it will pick up, but one thing that the SQL Community has taught me is that investing time in others is top on my list. You see I’m living proof of what investing time in others is all about. People have been doing this my whole life, my mentors in the SQL Community, and they know who they are, took a chance on me.

SQL has been good to me; why can’t I share that with someone else who is in need. I tell you what; if you are serious and you want to get plugged in and learn then send me a message. I will be happy to help, assist, push you to reach your maximum potential. With that said nothing will be handed to you; learning is a constant. It is the fire within that drives us to reach our fullest potential.

So Wayne, investing time in others. Whether that means speaking at local, regional, national events, blogging, mentoring, answering questions ~ you will find me somewhere trying to invest time in others. With words of encouragement and with words of motivation.

T-SQL Tuesday

Adam Machanic (B|T) had a vision 6 years ago in creating what is now known as T-SQL Tuesday. If you are serious about it and want to try out your gig in hosting then drop him a line. You won’t regret it; I’ve been blessed to host a few occasions over the years and it has been a blast.

Thanks Adam for your vision and thank you Wayne for hosting. Hurry back to Louisville where you can teach us some more good stuff.

Categories: T-SQL Tuesday Tags: ,

T-SQL Tuesday #60 Roundup – Step Right Up

16 Nov 2014 2 comments

TSQL2sdayWe had a great turnout for this months T-SQL Tuesday Block Party. Coming into this month and knowing that I was hosting, I felt a strong sense of picking a topic that would help others. A topic where sound professionals such as the ones below shared some of their thoughts on the how and the why to learning.

One thing is evident to me in reading these posts. You can be the most senior professional in our industry down to someone who is just attending their first T-SQL Tuesday, but one thing holds true ~ the people.

All right, with that said check out what the party attendees had to say, a special thanks out to each of them, and I would be amiss if I didn’t mention Adam Machanic (B|T) who with this party marked the 60th (5 years). Appreciate the vision you had 5 years ago Adam.

Party attendees:

Aaron Bertrand (B|T) – provides a great synopsis of why experience has helped in learning and tapping into Q and A sites. I liked this approach; was pretty awesome to see him write this and also pretty awesome he took the time to share this for others to read while flying from one conference to another. Much appreciated sir.

Rob Farley (B|T) – provides a twist on how he learns from others by cultivating and developing relationships. One key piece from his post is “listening” what others have to say. I got a lot out of this post.

Kenneth Fisher (B|T) – provides a security gem you will want to read along with some practical examples of tracking down user perms and AD groups.

Russ Thomas (B|T) – provides a nice trick to add to anyone’s arsenal. A simple task that many may not know or have forgotten.

Rob Sewell (B|T) – provides a nice take on the “Problem Step Record”; along with providing some reasons why it may be beneficial to start using it. Another good read.

Tom Roush (B|T) – provides a great blog post on what #sqlhelp can do for you along with what the community is all about. Captured every essence of community.

Jason Brimhall (B|T) – provides some discussion around what he picked up from PASS Summit14 and the use of the debugger; brilliant actually.

Malathi Mahadevan (B|T) – provides insight about learning promotion of work, networking, community involvement. This was a nice take from someone who has been in the trenches of attending PASS Summit for 13 years.

Nancy Daniels (B|T) – first, I think it is awesome that this is Nancy’s first #T-SQL2sday. Second, Nancy has provided steps on how she fixed a patch gone awry on one of her clustered environments. Nice way to step through the issue

Warwick Rudd (B|T) – provides some sound advice and real word examples on how multiple solutions can achieve an outcome. I actually like the comment made regarding PowerShell

Boris Hristov (B|T) – provides a thought provoking post. This one struck home with me as I have done exactly what he mentioned. Achieving the ability to arrive at a solution and deliver it in its most simplistic form so others can understand…..stellar and well said.

Mickey Steuwe (B|T) – provides a real world example of recovering from a transaction log filling up a disk; you won’t believe where the solution came from!

Cathrine Wilhelmsen (B|T) – provides a couple of good examples from some established data professionals around security and notifications. One of the sessions mentioned was one of Argenis Fernandez (B|T) and securing your SQL server. I’ve sat in on that one; stellar post to glean some information from.

Keep rocking guys!!

T-SQL Tuesday #60 Something New Learned

11 Nov 2014 2 comments

SQL Tuesday

So, here it is. I put the challenge out to discuss something new learned last week. I was fortunate enough to attend the PASS Summit last week in Seattle. While this post will not be my summarization of that trip (that will be another post) I did have several take-a-ways. I sat in some stellar sessions with some renowned speakers.

However, one re-occurring theme kept coming to my mind – the people. Listen, I’ve been through a lot over my 15 years with SQL, and my 3 years actively involved in the community and this past week affirmed something for me. LISTEN to the people.

I place strong value in the sessions I attended; along with that I have to note that face time; one on one time with real people in my industry is about the best form of learning I could ever hope to obtain.

With that learning comes in issues related to both SQL and non SQL attributes. I had so many positive conversations on leadership alone that sparked a new kind of fire within me; one that was not as bright as what my technical fire had been.

Guys, listen. I could write 10 blog posts on how buffer size could help with backups, the need to have always on implemented, or how to tune indexes all day long. The people, better yet the community is where I believe the learning lies within. Out of 5k people last week I ended up meeting a guy that works two blocks from me and we got to discuss the community and what it means to us.

Have you challenged yourself lately? I mean have you really challenged yourself lately in learning something. I don’t care if you are just starting out or the most seasoned vet around; the ability to learn happens everyday and I’m learning that is what separates the exceptional data professional from the data professional.

You see, the exceptional data professional hangs around the community zone at Pass Summit to help others in the community with issue they may have. The exceptional data professional sits down next to you when you are the new kid on the block and encourages you to make the most of your career then tells you some of his/her pitfalls they had that you can avoid, and the exceptional data professional takes you under his/her wing when you ask them for help or assistance.

You don’t have to travel all the way to Seattle to learn; no you have learning opportunities all around you. From SQL Saturday’s to Virtual Chapters on the web but it starts with you. That’s right, you have to be willing to take that first step; get involved and start learning.

I can tell you from experience and the roller coaster ride I’ve been on for the past three years that you will not regret it. Strive for excellence and provide that leadership through service that the community seeks. Yeah, I may be a tad passionate about what I do; you’ll find that kind of trait with others in the community.

So, I’ve challenged myself……..will you?

T-SQL Tuesday #59 – My Hero!

SQL TuesdayIt always amazes me how fast these T-SQL Tuesday block parties come about; it seems like we just finished one and here we are yet again. This month the party is hosted by Tracy McKibben (Blog | Twitter) and the monthly topic that was chosen is “Heroes”.

This topic can be taken in many different directions; this post will be geared toward the SQL environment and what I’ve learned from a professional career standpoint. I’ve been blessed to have had some influential people in my career. One of the first things that crept into my mind was the “My Fab Five” post I did earlier this year. If you haven’t read it I urge you to check that one out as well; after you read this one of course!

I see so many heroes in the SQL Community; ones that aren’t heralded but come in day in and day out and get the job done. The tireless volunteer who keeps going year in and year out and doesn’t complain one time. PASS Summit 2014 is coming up; have you ever thought about how much effort that goes into putting something like that on? So many behind the scenes people who work countless hours – those are the type of heroes that I’d like to pay tribute to.

I would be amiss if I didn’t mention 4 influential people in my own career to date. I think back from both a business, professional, and technical perspective two individuals who deserve a lot of credit in bringing me along are not technical evangelists nor are they knee deep in the SQL Community; but they taught me, in more ways than most, the ropes and expectations of business. Both of these people took a chance on me early in my career and have had a big hand in molding my psych in both the business and technical realm. I have much respect for both of these individuals for the tenacity they bring daily and focus they have instilled in me – Brad Cunningham and Chris Howard. You will not see these guys in the headlines at the PASS Summit, but it is guys like this who allow guys like me and afford guys like me the ability to enhance and further my knowledge on a daily basis.

Piggy backing onto these two guys I’ve had some of the best in the Community take me under their wing so to speak. This doesn’t mean I haven’t approached others in the community nor does it mean that others aren’t helpful, but like the 2 people I mentioned above these 2 people did the same thing. They took a chance on me and allowed me to spread my wings and flourish within the SQL Community. I look up to these two individuals greatly and thanks is not enough for all they’ve done for me ~ Thank You John Sansom (Blog | Twitter) and Chris Shaw (Blog | Twitter) for rolling the dice and taking a chance on me when you didn’t have to.

Heroes –  the SQL Community is full of them. If you are in it for the fame and fortune then you are in it for the wrong reasons. Heroes are found all around us; you don’t have to look far to find them. To the many unsung heroes I thank you for your hard work and dedication for it is all of our efforts on a daily basis that make the SQL Community what it is.

If you are interested in hosting a T-SQL Tuesday party you can contact it’s creator the mighty Adam Machanic (Blog | Twitter) who is a hero in his own right.

heroes

Passwords – A T-SQL Tuesday Topic

LateBeing engrossed with the daily tasks at hand I completely missed this month’s T-SQL Tuesday is being brought to you by Sebastian Meine Blog | Twitter.

Although the time frame has passed I would be amiss if I didn’t continue on my journey of joining in these block parties; with that said I’m going to write what I “would” have contributed. This month’s topic is intriguing in that it can cover a wide array of discussion – Passwords.

When I think of passwords I think of etiquette. I cannot tell you how many times I have been on calls, meetings, emails, and the list could go on of scenarios that relate to passwords where users just don’t think or take into consideration the impact of their actions. To me the last four words are the key, “impact of their (our) actions“.

Password Etiquette

  • Conference Calls – how many times have you been on a production call with numerous individuals and hear someone say, “Okay here is the user name and password?” If you have then you are not the only one. Credentials should be kept out of the hands of unnecessary individuals.
  • Open Text Passwords in tables – check into encrypting those; protect yourself before you realize breaches have occurred and you are left holding the bag.
  • Email – transmitting password information via email; not a big fan of. This kind of relates back to the conference call section; who all is on the email? Are you sending it to Project Managers and the like? Probably not the best choice to make.
  • Backups sent offsite – do you have any backups going off site? Is any pertinent credentials contained in the dB and if so are your backups being encrypted before shipping them off?
  • Length – Look at the length of the passwords you are creating; how strong is the password you are making?
  • Sharing – don’t do it; simple enough.

All the above reflects, what I deem, good etiquette. That barely scratches the surface. You have to take into consideration many other factors one of them being a policy.  Small, big, medium – whatever kind of shop you are in define out what the best practice is for your shop and then adhere to it. A good reference could be found on Technet Best Practices

Lastly, if you feel as though a password has been compromised be proactive and take the necessary steps to change it. Don’t wait for something to happen; you be the game changer.

Get your defense model in place and let the good times roll.

%d bloggers like this: