Voice of the DBA

Author: way0utwest

  • Implementing Continuous Integration in Redmond

    On May 15, 2015, Redgate Software and one of our partners, Northwest Cadence, will be presenting one of the DLM Training Workshops I helped build in Redmond, WA. I’m excited to be traveling up to WA for the event and helping support Dan Wood as he delivers the training on Continuous Integration (CI).

    This is our Database Continuous Integration workshop, and it’s a great way to learn how you can take your database code and get it automatically tested and built in a CI process. CI has taken hold in many parts of the software industry, but not so much databases.

    This workshop uses the Redgate tools, which make the process easier. However you can just use concepts with your own scripting or other tools. Our goal here is to help you get comfortable with the idea of CI and

    Requirements

    The workshop assumes you have your database in a version control system (VCS). If you don’t, then you should. We have a workshop on using source control with databases as well, but really you should just script our your database and put is in SVN, Git, or something. Or just use SQL Source Control, which makes it really easy.

    The other requirement if you have a laptop with you. We’ve built a number of labs, and we really, really want you to try and implement this yourself. We’ll provide a virtual machine up in the cloud, but we want you to have the chance to actually do the work.

    Register and Learn

    You can register today and purchase a ticket if your company is interested and you want to get some hands on experience with the technology. If you want to learn how to better engineer your software development on SQL Server, and haven’t given this a try (or are worried about it), think about coming.

    Register today

    SQL Saturday #393 – Redmond

    Our workshop is the Friday before SQL Saturday #393 in Redmond, WA. That’s a free event, and you can come there as well. I’ll be delivering a talk there, hopefully on testing, which is certainly important to CI.

    Either way, you can get a full day of in depth training on Friday at the workshop, and then a variety of topics on Saturday at the SQL Saturday event.

    Hope to see you there.

  • Code Reviews

    When I first started to be paid money to write software, I worked on my own. I had to test my own code, decide when it could be deployed, and make the decision to deploy it. I had to notify users, and over time, I had to delay deployments because my mistakes would stop a business from getting work done. Fortunately I didn’t cause any serious loss of revenue for a small construction company due to my coding errors.

    Later I worked at a large company and was surprised when I finished a piece of work and had to submit to a code review. My boss told me to get two other programmers, at least one a senior level developer, and have them review my code. In those days we reviewed code on paper, with a red pen reminiscent of my school days. The amount of ink expended on my work was a bit overwhelming, but fortunately I recovered and was able to fix most of the issues quickly.

    I’ve talked to a number of developers over the years and it seems that most of them make their own decisions about how ready their work is for deployment. Code reviews seem to be the exception rather than the rule, but I wasn’t sure if that was the case for SQL Server professionals. Are we more conservative and formal in our approaches? Here’s the question this week.

    Do you perform code reviews?

    Do you review just application code (C#, Java, etc)? Are stored procedures and functions included? Do you include DDL and DML changes in reviews? Are DBAs able to apply changes to production without a second employee checking them?

    In many ways, despite the stereotype that DBAs want to be careful with their systems, I’ve seen many DBAs rashly apply a change to a production server without any oversight or second opinons. It has seemed to me that having the control to decide what changes are made to a SQL Server instance has led to the execution of that power without any oversight for many individuals.

    Let us know this week if you review code, and what things matter. I certainly thing naming and structure matter, though I’m willing to let formatting go as many tools these days will reformat code in whatever structure an individual developer prefers.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Searching for Plans

    Today’s editorial was originally published on Sept 13, 2010. It is being re-run as Steve is away at training.

    I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.

    “…SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”

    I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.

    Or would I?

    I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

    Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

    I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

    Steve Jones

  • Security Through Chaos

    This editorial was originally published on June 16, 2005. It is being re-run as Steve is away at training.

    I can’t really disclose who told me this, or at which company, but I found it very interesting. Recently there was a worm that rolled through a large number of Windows systems on the Internet. A few friends told me about it since it had rippled fairly quickly through their companies. These were all large organizations, with over 2,000 people employed in each of them. However, one company had almost no infections. The spread between their internal systems was almost non-existant.

    Now I’m sure that you are all wondering what great technique they used so you can deploy it in your environment. I was as well, until I heard the details. I dismissed it at first, but then thought it did make some sense. I’m not sure I’d recommend the technique, but it was interesting.

    Their defense was chaos. They don’t really have a central IT organization, standards are almost non-existent, no central AD setup, not even a standard platform. They do make anti-virus, firewalls, etc. available, but it is up to individual departments, people, and labs/data center areas to deploy them as they see fit. Need a resource from another group? Better start making friends. Want to breach a firewall? I’d recommend buying a Starbucks card or a 6 pack of Red Bull for the admin of that firewall.

    Now this is a technology company and most of the employees are fairly smart technologists. They are each responsible for the most part for their systems. If they break it, they need to fix it or find someone to help because a broken computer is not an excuse for work not being complete. But an individual can have a Mac, PC, Sparc, run Windows, Linux, whatever, as long they get their job done.

    Samba is in use as a file system in many places, but permissions control is distributed. Sarbanes-Oxley, secuirty, auditing, etc. all still apply, but there is no central group that ensures it’s performed in a consistent manner.

    As I mentioned, at first I was shocked. I thought this was ridiculous. But the more I thought about it, the more I realized that it made some sense. You couldn’t easily break into their network because what worked in one place wouldn’t necessarily work in another. Compromise one password and you might not get very far at all, even if you had an administrator password.

    I’m still not completely sure what to think of this, but apparently it works. Probably just like my life with three kids and a wife that works hard. You juggle all the balls and hope none of them drops.

    Steve Jones