Voice of the DBA

Author: way0utwest

  • Change Management

    Hurry up and wait.

    Hurry up and wait.

    In a couple of the large enterprises I’ve worked in, that might have been the IT motto. It seemed as though the internal developers were always under pressure to get applications finished as soon as possible. However we often found that when applications were finished, there would be a delay in deploying the new code to the production servers, usually because of a strict change control process that required documentation and testing of the changes on related systems. In many smaller companies I’ve worked in, we had no change control process at all and could deploy updates at any time.

    I’m not sure which of those two systems I prefer. In general, I prefer to have some change management process to ensure that I can easily determine what changes were made at any time. However I’ve found that any change management quickly becomes a bottleneck devoid of common sense and full of bureaucratic nonsense. This Friday, I decided to ask a question about change management, but not about your opinion of whether it’s good or not. The question this week is:

    Do you follow a change control process 90+% of the time?

    By this question, I mean is change control a habit, an ingrained sense of the way you work and deploy updates to a live environment. I’m not asking if you have a formal process, if it’s a team process, or anything about the details, but rather do you actually follow some methodology to track and manage changes?

    I’d like to think most of us would, if for no other reason than to answer the common “what changed?” question that always comes up when something breaks. However I’m curious to see the results.

    Steve Jones

    The Voice of the DBA Podcasts

    We are still having hosting issues with the podcasts. We hope to resolve this and be back to releasing the podcast versions of the editorial next week.

  • Disk Drives in the Sky

    Not too many data professionals are working with databases in the cloud, and while I think it’s a platform that will grow, it’s not suited for all environments. One thing all of us need, however, is bigger, better, and faster storage for our data. Our data volumes are growing, sometimes surprising us with the sheer number of bits our queries must go through, and access times for storage are often a bottleneck in our systems.

    There have been some interesting advances in how storage is connected to our systems, and there are some even more interesting ways in which the large cloud vendors are tackling the storage issues they face. I read a fascinating piece on the various storage systems used by Google, Amazon, and Azure. It talks about the various architectures being used, and there are some innovative ideas being implemented in order to ensure there is a high level of reliability and performance for these systems.

    Should you be interested in storage architectures as a data professional? I think that having a basic idea of the options out there will give you the chance to understand the options your storage people (or vendors) may recommend in the future, and have some idea of the suitability of these systems for your databases. I don’t think you need to understand the detailed implementations, but knowing the pro and cons of these systems, as well as the experiences of other companies, may help you detect how much fluff is being presented to you.

    There’s one part at the end of the piece that caught my eye: “as data volumes continue to go up for “big data” applications, server memory is becoming “the new disk” and file systems are becoming where the log for application activity gets stored—”the new tape.” That’s interesting. Maybe we’ll start to do more in stream processing, ala Streaminsight, on our data and keep more aggregations and analysis in memory, reducing the need for disk access.

    Steve Jones

    The Voice of the DBA Podcasts

    My apologies. The hosting provider for the podcasts is having issues, so we do not have them available today.

  • A few quick time calculations

    Have you ever needed to do a quick time calculations of the amount of hours/minutes/seconds that have passed? Suppose you needed to get the total number of minutes that have passed for a total time of ‘2:24’.

    There are some easy ways to do this, and the normal calculation that you might make is to multiple hours by 60 and then add minutes, so something like:

    DECLARE @t TIME, @n INT SELECT @t = '2:24' SELECT @n = DATEPART( hh, @t) * 60
          + DATEPART(mi, @t) SELECT @n

    That returns 144, which is the correct value (60 * 2 = 120, adding 24). However there’s an easier, and cleaner, way.

    SELECT DATEDIFF(mi, 0, @t)

    You can let SQL Server do the math, grabbing the DATEDIFF function and using 0 as a starting point.

    Number of seconds in a day?

    DECLARE @t TIME, @n INT, @d DATETIME SELECT @t = '11:59:59PM' SELECT DATEDIFF(ss, 0, @t) + 1

  • Returning Results from an Insert – OUTPUT clause

    I needed to return an identity value recently from an insert for use in another piece of code. For a client front end, you can easily encapsulate your insert in a stored procedure and then SELECT scope_identity() to get the last identity. However there’s an easier way: the output clause.

    The OUTPUT clause is a clause that goes in your INSERT statement and allows you access to the INSERTED table, just like a trigger (also the DELETED table.

    A short example below, where data is being added by the server in the state of an identity and a default. I am returning them with the OUTPUT clause.

    CREATE TABLE mytesttable
( MyID INT IDENTITY , mychar VARCHAR(20) , mydate DATE DEFAULT GETDATE() ) GO DECLARE @mytable TABLE ( i INT, d DATE); INSERT dbo.mytesttable (mychar) OUTPUT INSERTED.myid, INSERTED.mydate INTO @mytable
VALUES ( 'First Row') SELECT i, d FROM @mytable

    There are any number of ways to use this data, especially in terms of logging or inserts into another table. It should be cleaner code, but it doesn’t mean that you should be running inserts from the client without stored procedures, or at least without explicit parameters. Make sure you still use those.