Voice of the DBA

Category: Editorial

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

  • Better Coding

    When the census was last taken in the US, it was still mostly a paper affair. My wife and I sat down with the form, filled it out, and mailed it back to the US government. While much of the data is publicly available, the process of gathering the data was primarily an analog process. That was in 2010, and governments are trying to do things differently. I suspect our 2020 census in the US will be mostly a digital effort, though I hope the companies setting up the applications learn from other countries’ issues.

    Australia had their census this year, 2016, and they planned on having most citizens complete the form online, with a requirement to opt-in to get the paper version. However, things didn’t go smoothly. There was a DDOS attack on the night the census was set to collect data. There were also concerns from many people over data privacy. All in all, not a great showing for the Australian Bureau of Statistics and their IT contractors. Things got worse in the weekend after the census was scheduled when a few university students built a website designed to handle a larger load than the government over a weekend for substantially less money.

    To be fair, the university students’ site wouldn’t necessarily meet all the requirements for privacy and data security. It also doesn’t seem to have the validation and verification that a website needs. Not to mention a database setup to keep the data for later analysis. Those things would certainly need to be added and tested, but this does show that sometimes there are better ways to develop software than the way that has been done in the past. Large government projects get bogged down, perhaps worse than many corporate projects, but I’m not sure. Perhaps we just need people that code better, and certainly, people that manage developers better.

    Certainly the cost of projects for the things like the census might not go down much. After all, I’m sure a fair amount of the cost charged by IBM goes back to the salespeople and engineers responsible for landing the contract. However, I also think that far too often there is too much overhead in large projects starts to obscure the most important part of any software system: it needs to work. I would prefer that even long term, large scale projects be developed in an agile fashion, and take on beta testers throughout (public or private). Let actual users play with the site in advance, report bugs, simulate a load, even watch the site develop in real time.

    And when you’re ready to deploy, from experience, I can tell you it’s not too hard to delete all that transactional data before you go live.

    Steve Jones

    The Voice of the DBA Podcast

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