Voice of the DBA

Author: way0utwest

  • 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.

  • No Limits

    Google Compute Engine
    No limits for the Google Compute Engine, at least not any practical limits.

    We’ve had Amazon Web Services (AWS) available for some time, and used for some interesting projects that wouldn’t be possible if groups had to purchase their own equipment. From password cracking to cancer research, there are some amazing possibilities. Windows Azure works as well, and there are customers such as banks, using the cloud to perform complex analysis of data. Not to be left out, Google announced recently they were provide their Google Compute Engine to anyone, without any limits on scale. You have to pay for it, but if you want 770,000 cores to solve a problem, they’ll provide them if you can pay for the resources. It’s US$2m/day, but it’s there.

    I don’t know what that means for us as data professionals, but I suspect there will be any number of companies that will consider using these types of resources to work on complex analysis of problems. Running a simulation might be something that costs a few hundred dollars a day, using resources for minutes at a time. Looking over some of the different projects people have used cloud computing engines for, it seems that many businesses might find this to be a cost effective way to perform data mining or BI type workloads.

    If you can get the data to the engine. I still wonder how challenging this is, apart from all the security concerns. Just moving that much data around, refreshing it, removing the stale data, could be very complex. It concerns me that as more people attempt this type of work, we will have more and more poor decisions made because of data quality, age, or accuracy. After all, garbage in, garbage out has been a tenet of data professionals for decades.

    If you’re using cloud resources for computation, I’d love to know about it. I know many of the data professionals out there have reservations, and that’s healthy. It shouldn’t blind you to the possibilities that this is a most cost effective way of actually getting analysis and answers for your users.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Separate Accounts

    Separated Strawberry
    Is separation that much of a hassle? I don’t think so.

    Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn’t changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.

    One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.

    The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it’s really needed. That’s a better recommendation, but I still prefer completely separate accounts. I know that some security groups don’t like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.

    Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?

    I’m not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.

    Let us know this Friday how you feel and what works for you.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Standing Desk – Flooring

    My standing desk experiment has worked out well. I have gotten used to standing there and I’ve slowly found places for my coffee, water, phone, and other things that I use at my desk. I still need to work on the keyboard/mouse stand, but that’s a future project. Today I talk flooring.

    When Buck Woody mentioned using a pad on the floor, I chalked some of it up to his sitting for a long time at a desk. I wondered with my three years of running if it would matter to me. In my first month or working in the basement, on the hard cement floor, I was fine. I was wearing shoes, however, since it was cold down there.

    When I moved upstairs, I had an area carpet, but then wooden floors. I didn’t think much of it until I worked a couple of days in bare feet during the summer. I could feel my calves and legs aching a bit and they were tired. Even my knees were slightly sore.

    I made sure I had shoes on every day, either thick soled Merrill Mocs or running shoes. That worked, but I wondered if that was the best idea. Even with shoes, I might be wearing out my body a bit.

    The other day I decided to experiment, and spent $20 on an anti-fatigue mat at Home Depot. Actually $24 and change, but it wasn’t a big investment. I put it on top of the carpet for now to see.

    Photo Aug 01, 8 35 29 AM

    It’s an open mat, with lots of circles in it. This is the type of mat I’d stood on in restaurants while working as a cook or bartender. It’s not conducive to bare feet as it is, but my initial test was to see how it worked with shoes. As you can see, I left the tag on since I’m not sure I want to keep this.

    After two days on this, it’s hard to tell if there’s a difference. I’ve got some sore legs already, so I’m not sure. I think I need to move it below a surface and try it on bare feet. If this doesn’t work, I can always use this in the shop, so there’s no great loss.