Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #33 – Trick Shot

    tsqltuesdayIt’s T-SQL Tuesday time again, and this is my post for #33. The host is Mike Fal and his topic is Trick Shots, which is an interesting one. I’m not a tricky guy, and I tend to lean towards common, practical approaches to problems. I’m not sure this post will be great, but I like participating, and so I will.

    The T-SQL Tuesday blog party takes place every month, and if you’d like to host, contact the originator, Adam Machanic(b|t) .

    Tricks Shots

    Once upon a time, I was a DBA. I worked with a number of developers who were, how can I say this politely, not terribly careful about which objects they changed or added. It was understandable since they had jobs and work to get done. However I was responsible for deploying their changes to our QA, and ultimately production, servers.

    Not knowing what to deploy is a pain. It leads to mistakes, broken features, and more importantly, long hours from me trying to determine what changed objects went with which features. Since I also want to deploy the same code to production as QA, I want to smooth out this deployment process as much as possible.

    Back in 2000/2001, we didn’t have SQL Source Control tracking changes by individuals. We had to manually check in and out of our VCS for all database changes, which was not a habit most developers had built. As a result, we would constantly have new objects appear, and old ones changed as developers needed to meet new requirements. I tried to handle all the database development work, but there were times I couldn’t keep up.

    As you might expect, when deployment time came, we had a lot of objects in the development database that weren’t in the production database. SQL Compare made it easy to find out which objects were different, but the problem we faced was that not all changes would be deployed at once. We needed specific code changes linked to specific objects, which wasn’t a simple task with 10-12 developers.

    A few months of mad scrambles to track down objects and try to meet our weekly QA and deployment goals had me working on a better solution. There had to be a way in the SQL Server metadata to track changes to objects. I dug around the SQL Server 2000 sysobjects views and found a creation date, but not an alteration date. However I did find a version number that was undocumented, but incremented on ever ALTER of an object.

    Using this information, I build a process that would capture the state of all objects in a table, and then compare this to the current state of sysobjects, returning differences to me. I built this as an hourly report, and had it send changes to me. This didn’t prevent changes, but it allowed me to quickly track down what had changed, and send a note to the developer to link this to a particular item in our project plan. A few minutes an hour (with no changes many hours), let me break out the database changes into a deployment project for the next week.

    The Trick

    The trick in this case was finding information I needed from SQL Server that wasn’t documented. It doesn’t apply any more and the metadata in SQL Server 2005 and later has grown so much that you can more easily find changes.

    What I Learned

    I learned a few things here. First, I could build my own systems on the SQL Server platform to help me out. I didn’t need to depend on what Microsoft provided, if I needed something different. This led me to view the management and administration of the instance as just another application. One I built on top of the platform the same as my developers.

    This also taught me that I needed to be like the reed, flexing and bending to survive in situations. My developers were willing to work with me, but they were human, and they had other priorities. I needed to work with them and get along, adapting some of my ideas and needs to work with them. I did get them to work on manual check ins and outs, but they slipped up, and my system helped both catch those mistakes, and remind them in a gentle way. My emails asking to link an object change to the project never complained they missed something, but they realized the reason I sent it and it helped reinforce the habit of checking objects out of VCS before editing them.

  • T-SQL Tuesday #32 – A Life in the Day

    tsqltuesdayIt’s T-SQL Tuesday time again, delayed a week this month, so I had  a whole extra week to get ready. This month the party is hosted by Erin Stallato, fellow MVP, runner, and a SQL Server expert. Such an expert, she was recently hired by SQL Skills, one of the top SQL Server consulting firms in the world.

    The T-SQL Tuesday party is the brainchild of Adam Machanic and it’s been a lot of fun to participate over the last few years. If you’re like to host one, you just need a blog, and then contact Adam for a month with your topic.

    A Life in the Day

    This month’s topic is a life in the day of your job. Erin’s plan was to track your life on Wednesday, July 11 or Thursday, July 12. That won’t work for me, as you can see from my tracking on Wed and Thur.

    Wed, July 11

    Wake up, check email, kiss the wife and kids and drive to the Denver Airport. Board a flight for London.

    Thur, July 12

    Arrive in London, clear customs, get a tube to Central London. Leave my bags at the hotel, find some food, read, run in Hyde Park, nap, practice SQL in the City presentations, dinner with Red Gate people, sleep.

    Travel

    This is a travel/talk week for me, so I don’t have a lot going on those days. So for this post, I’m going to track Monday, July 9, 2012 as it’s a semi-normal day for me.

    Mon, July 9

    It’s summer, and I work at home, so that means that I can sleep in a bit. A party with friends last night, so I wasn’t up until 8:30. I usually wake up, and reach over to grab my phone. I can check email and lay there for a few minutes, slowly getting going. Today there weren’t any big items to handle. No meetings, a few updates for my SQL in the City talk, and regular mix of reported posts, new content submitted, newsletters, etc. I also usually check Twitter to see if any news is breaking in the SQL world.

    Make coffee. Usually the first thing I do, heading downstairs before I walk into the office. Then I walk up to my standing desk and log in, checking the site.

    Most mornings I have a bit of a routine. I double check email to see anything new since I’ve walked downstairs and then start to handle any of the items I read in bed. Usually that means removing SPAM posts, answering questions from Red Gate or members of the community. I get a fair number of normal business queries each day, and try to get them answered. Today I had a note about travel in London with my reservations and questions about my dinner order Thur (we are well taken care of at Red Gate). I had a few notes about my talks I responded to and a few posts to remove.

    Go get coffee.

    Next I flip through all my post notifications. I have my daily editorial thread, but I also answer or participate in about 80-100 threads a month on SQLServerCentral. I answer some as I sip coffee, usually spending 30-60 minutes on discussions.

    Moar coffee (as Jes Borland would tweet)

    I flip through a few newsletters, just to see what others are doing/writing about. I get a lot of editorial ideas from here (and Twitter, and Google, etc), so I subscribe to 20-30 different newsletters. The SQLskills and Brent Ozar, PLF newsletters come out Monday, and I usually read through them. They are always interesting to me.

    Water.

    I’m doing the Body for Life diet with my wife. We’ve done it a few times, so lots of water throughout the day. I also eat every 2-3 hours, smaller meals. These have some good matches with the standing desk. I need to regularly hit the bathroom, or break for 10-15 minutes to cook something. Those breaks are good for the standing desk. If I ever run out of water, I walk away to fill it.

    This Friday and Saturday I have to give two presentations each day for SQL in the City. I’m traveling Wed/Thur, so my first step is to get newsletter scheduled for the week. I am usually 2-3 days ahead on podcasts and 4-5 days on editorials. Last week I scheduled a few re-runs for Thur/Fri, so today I edit down a podcast for Tuesday and upload it. Wed is a guest, so I schedule out newsletters through Friday.

    Now it’s practice time. These are new presentations, so first I run through the demos I have. I want the demos working and then backed out and ready. I go through each one with a setup and a reset script for the demo. Most of these are with Red Gate tools, so they’re easy.

    Once demos are ready, I’ll go through the presentation here, incorporating the demos and checking timing. The SQL in the City events are packed and scheduled fairly tight, so I don’t want to go over my time. Today I’ll do two rehearsals of each presentation to be ready.

    Throughout the day I have Twitter up (using Twhirl). I’ll glance at the stream periodically, but I rarely scroll back or look at things. It’s a nice break, and it lets me see what’s happening in the SQL world. Between run throughs of the demos and decks, I’ll glance around.

    I keep email open, but I don’t check all that often, maybe once every couple hours. With Pandora on headphones, I can concentrate on looking over my demos and notes. Not a good day. My VMs ran slow, and I blew up each demo twice, which was slightly worrisome. However I made a few notes on how to smooth things out for the actual delivery, and I’ll go over them live, on the laptop, tomorrow. The contained databases demos were especially problematic as I found a few problems with my reset scripts. I’ll clean those tomorrow as well.

    After two runs, that’s enough. I glanced at the forums on SQLServerCentral to see if anything much had popped up, then spent an hour working on a few editorials. I polished off 2 that were mostly written. I’ll give those a review tomorrow. I started two new ones, getting about 2-3 paragraphs written. Those are mostly done, but I ran out of thoughts. I copied in 2 other links, with just draft titles. I’ll try to work on those while I’m traveling.

    6:00 and I’m calling it a day. Time to run.

  • T-SQL Tuesday #31 – Logging

    TSQL2sDay150x150It’s T-SQL Tuesday time again, and this month Aaron Nelson (blog | @sqlvariant) is hosting. The topic is logging, and if you’re like to participate, read Aaron’s post and learn the rules. We do this on the second Tuesday of every month.

    If you’d like to host, contact Adam Machanic. It’s easy to do. Get on the schedule, pick a topic, and then write a post.

    A list of previous posts is here,

    Logging

    I’ve found documentation of events to be one of the most important things I can do in my career. Finding out what happened, what changed, or what I did has been important many times, and often helped me come through difficult situations.

    Logging is the automated version of documentation. All kinds of applications, including SQL Server, produce logs of the various activity on the system. In SQL Server, we are moving to an eventing system, and if you haven’t looked at Extended Events, you should.

    One of the times when I found logging to be lacking was in a startup I worked at a decade ago. We had a number of developers that were working on various development servers. They had full rights, and they were allowed to build their own objects. That was a little concern to a controlling DBA like me, but I allowed it since they often wanted new objects quickly, and if I allowed them to write their own, they’d use stored procedures.

    A good compromise, if you ask me.

    However in the hectic pace of development, I found that the developers didn’t often keep good notes about what was being built for which features and functions. Since we had to produce a build script fairly quickly every Monday in order to update our QA systems, we would find that developers invariably would forget objects and we would not have a well tested QA script on Monday afternoon.

    I decided that we needed to better log the changes on our development server. I didn’t care about every change, especially intermediate changes to objects, but I did care about the gross changes made each day.

    This was in the SQL Server 2000 days, with limited tracking of changes outside of SQL Trace. Since I had no desire to move through lots of trace files, even in an automated fashion, I decided on a much simpler method.

    In sysobjects (now sys.objects), there was a crdate field, which tells you when the object was created. However that doesn’t change if an ALTER TABLE is run (or any other ALTER). That stumped me briefly, but I decided to search further.

    I found that there was a schema_ver field, which is incremented every time the object is changed. Since the majority of our developer changes were ALTERs, I could track the version number and then compare this each day. I tested this out, and it worked well.

    The outline of the solution is that I grabbed a copy of the sysobjects table every day and stored it in a temporary table. I then used a left join to compare this with the previous values stored in a table I’d created to store the data. When I found differences, I logged them in a table, along with the date, and sent myself an email. I would then overwrite the stored version of the objects with the version from the temp table, giving me a baseline for the next execution.

    At the end of the week, I’d have an aggregate list of all objects changed, which I could then compare against our build script.

    At the time we were in an agile environment, releasing new code every Wednesday, and operating on very short timelines. The logging I did cut down on mistakes and allowed us to have a smooth release process that functioned for over 18 months, with code releases nearly every Wednesday outside of holidays.

  • T-SQL Tuesday #30 – Ethics

    TSQL2sDay150x150This month the topic is hosted by Chris Shaw, and his topic is ethics.

    The blog party is the second Tuesday of each month. There is a theme and it is hosted by a different blogger every month. I’ve got a complete list of topics on my blog if you want to read about the past entries.

    It was started by Adam Machanic and if you want to host one, let him know. It can be fun, though a little busy as you compile a summary of the posts from the month.

    Ethics

    I thought this was a great topic, and I’ve actually written on it before. Chris asks a few questions about ethics and they are good ones. I’d like to think that most people have a good code of ethics, but I also think many people haven’t really thought through what their ethics are on this situation. You should do your job, and do what you’re told, but I think breaking the law, or performing an action that violates your moral principles is a no-no.

    However when you get into more subtle issues, like the one Chris talks about with regard to security, what do you do?

    Overall you have to follow your moral compass and use The Test.

    • If you have any doubt about what you’ve been asked to do, seek a second opinion, and get a confirmation from your boss (or their boss) in writing.
    • If you are sure it’s wrong, don’t do it, give you reasons, and let them get someone else to do it.
    • If it’s illegal, you should report it.

    I know sometimes this puts you in a bad situation, and you may fear for your job. That’s natural, and understandable, especially if you are the sole breadwinner for your family.

    However you also have to live with your actions, and they reflect on your soul daily. I would ask that you make the right decision, even when it’s not the most popular, profitable, or palatable one.