Voice of the DBA

Category: Editorial

  • Rogue Algorithms

    This editorial was originally published on Sept 5, 2-12. It is being re-published as Steve is on vacation.

    I have always thought that computers do some amazing things, but they tend to do what we tell them to do, even if what we tell them to do is not what we want them to do. In those cases we usually find computers are helping us make mistakes faster than ever before. As we automate and link more and more systems, this can become a bigger and bigger problem in the world.

    I ran across this short link on a rogue algorithm that caused a fluctuation in a number of stocks. It looks like this was a case of poor analysis and poor QA at one firm, but imagine if we had some type of “update” released to a large number of companies? This type of problem could cause a dramatic short term fluctuation in the stock market, which shouldn’t affect most of us in the long run, but you never know. If this caused a company to go out of business, and they were the company holding your retirement savings, you might feel differently.

    This isn’t necessarily a problem that affects just financial institutions and their custom software. This is potentially a problem in any business that writes its own algorithms. That includes many of our companies, and many of us. We write queries, reports, and develop algorithms that help “the business” analyze data and make decisions that can affect our companies.

    This is worrisome to me, especially when we have companies that press for more and more analysis, with algorithms written more and more quickly to respond to business events. Success in this area often depends on the developer having an understanding of the nature of the business and the meaning of data, and strong working relationships with the business analysts. That comes over time, and is easily lost when employee turnover is too high.

    I hope that companies are learning that there is value to retaining employees, especially those that work with data and have invested effort in learning more about their business. Unfortunately I think that it take a disaster like this for some managers to understand the value of retaining employees and their knowledge. Even more unfortunate is the fact some managers never learn that.

  • Avoiding Stored Procedures

    This editorial was originally published on Aug 13, 2012. It is bein re-published as Steve is on vacation.

    I ran across this piece from a developer on why he avoids stored procedures and thought it made some good arguments. The primary thrust of the piece is that ORM tools (Object-Relational Mapping) have evolved to handle most of the requirements of many applications. They also save a ton of development time since so many stored procedures written are simple CRUD type operations.

    In many cases I agree. Having developers write stored procedures is silly and a waste of time. Procedures that select a few fields, or that update a table based on a primary key are mind-numbingly simple to write, but they take time to get in place. That’s time a developer isn’t spending thinking about the application and logic. Plus with any ORMs and tools like LINQ, you can write one line of code and let the ORM handle all of the work of getting or storing the data. Good points, and in many cases that’s correct. If you do mostly CRUD type work, this is a good reason to perhaps avoid stored procedures and let a tool do the work for you.

    Unless you use a different tool. There are plenty of tools, most of them free, that will generate that CRUD code for you. A few templates or snippets will handle the front end side of the call as well, building code to call stored procedures. If you’re actually typing this stuff over and over, you are wasting time.

    My brain started to wander when I saw “A database should be limited to the role of a persistence layer” which is silly sounding when you move beyond CRUD operations. It completely shut off when I saw “your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database” since I think this rarely happens. If it does for you, fine, but the vast majority of apps never leave their initial database.

    There are benefits in ORM tools, but you need to understand how the ORM works, what it’s strengths and weaknesses are. Blindly following the basic pattern for your state-lookup-data-editing dialog for all reporting screens is a sure way to cause yourself some problems. Allowing the ORM to define your relational database, without spending some time thinking about the benefits of good database design and proper modeling is asking for performance problems, or even integrity issues.

    ORM tools are just that tools. Used well, they can perform admirably, but just as I don’t use a hammer to drive a screw into wood, don’t depend on your ORM handling everything database related for you in an efficient manner.

    Steve Jones

  • Separate Accounts

    This editorial was originally published on Aug 10, 2012. It is being re-run as Steve is on vacation.

    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.

    Steve Jones

  • Up, Up, and Away

    I’m leaving tonight.

    Not forever, but for a couple of weeks. While that’s not a big deal for many people, I’ve rarely been away from home for more than a week. My longest business trips have been 11 days. Short, I know, but that’s the way I like things. Many of my colleagues think nothing of taking trips for multiple weeks, traveling the entire time. However, even during my six week sabbatical, I was at home the majority of the time. Perhaps that will change as my children get older since my wife likes to travel, but we’ll see. Certainly I’ve started to get used to living out of a suitcase, working on a laptop, and having minimal material things around. I expect the next few years will have me working out of our horse trailer as my wife teaches clients or participates in races.

    I’m looking forward to the trip, which will be a dichotomy of both relaxation and busy work times. I first start off with a week’s vacation in Scotland with my wife. She’s never been, and it’s been nearly 30 years for me, so we’ll take some time and wander around the Edinburgh area. I’m looking forward to the chance to wander around without an agenda and enjoy another culture. I’ve gotten a few ideas from friends on places to visit, and might even enhance the adventure by renting a car. I’ve driven (briefly) on the left side of the road, but it was a stressful experience.

    After a week of vacation, my wife and I will part at Heathrow airport, flying in opposite directions. While she returns to Denver, I’m going East, to Oslo for SQL Saturday #532. This is my first trip to Norway, and I’m looking forward to it. I arrive early Friday, so I have the chance to wander around a bit before the event Saturday. Then it’s back to the UK early Sunday since I’ll probably need to do laundry and get ready for a work week.

    My second week will be in the Redgate Software offices, but it’s a busy week. This is our Down Tools Week, with a number of projects that I’m interested in. I had hoped to be able to focus on one all week, but that won’t be the case. I’ve got SQL in the City preparations to begin, as well as a trip to London to deliver a talk on Tuesday at the London SQL Meetup. I suspect I’ll be doing something different each day, including some catchup with the great work our development teams are doing on various products.

    I finish off the trip at SQL Saturday #520 in Cambridge, where I’ll be giving a short, quick keynote to kick off the day. That’s exciting, and will make for a long day as I also have two more sessions to deliver. Then it’s back down to Heathrow that evening to begin my journey home.

    I am very fortunate to be able to travel around the world, speaking about SQL Server and meeting some amazing data professionals. It can be hard, but it’s always enjoyable and I look forward to shaking hands with many new and current friends on this journey.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.6MB) podcast or subscribe to the feed at iTunes and Mevio .