Voice of the DBA

Author: way0utwest

  • Quickly Copy Data

    How quickly can you copy data from one place to another?
    How quickly can you copy data from one place to another?

    There’s no magic solution for many of the problems we face in SQL Server. Whether we are trying to maintain concurrency under high write loads, restore databases quicker, or ensure extremely high availability we need to make trade-offs. Sometimes those tradeoffs are frustrating, and people look to other solutions like NoSQL, not realizing that they are just making different trade-offs.

    I see the frustrations and comments constantly on the SQLServerCentral forums, and one recent discussion was no exception. In this particular debate, a user was frustrated by the requirement to copy backup files across the Internet to another location. The transfers were taking too long and the poster were trying to find some magic way of decreasing the transfer time. Increasing the bandwidth, the simplest solution, wasn’t an option, and that usually isn’t easy to do in a corporate environment. The poster seemed to think there had to be some magic way of reducing the time it takes to copy large backup files, and there isn’t any magic solution. The things that help reduce transfer time are often the common things we think of.

    Compression is an obvious solution. There are numerous free programs to compress files and even software that will compress the backups as they occur. There are techniques in Windows for speeding up copies, though many are Windows version dependent. Beware, however, since copies from your server might cause you memory issues. However there might be other, more creative solutions that people come up with.

    Imagine that you track the transfers, keeping meta data about the process. Perhaps you can find a way to send less data overall the next time. In some sense, this is the idea behind log shipping. If you only send the changes (the transaction log backup), you might save a tremendous amount of time and resources. That assumes, of course, that the changes in a period of time contain less data than the overall database. That might not be the case in some  workloads.

    There are other services that might help. Imagine that you back up to the cloud, either because you use a tool, feature, or service if your application exists in the cloud. Spinning up a new instance with a copy of that data in the cloud might eliminate the need for transfers completely if the other location can just access the data in the cloud.

    I’m sure some of you have other ideas, and I’m sure many of us would like to hear what creative ways you might have for moving data between locations.

    Steve Jones

    The Voice of the DBA Podcast

    No podcasts today due to some personal issues. The podcasts should return tomorrow.

  • Fear Fear

    Are you too fearful or just paranoid enough?
    Are you too fearful or just paranoid enough?

    I’m a conservative DBA. I get nervous when backups aren’t running, code isn’t in source control, and developers have access to production systems. I’ve had too many late night pages and weekend phone calls, not to mention many extra hours spent in the office from changes to systems that didn’t go well. That latter item leads me to limit the number of changes I make to systems whenever I can, including avoiding applying Cumulative Updates to SQL Server.

    When I read an editorial from Glenn Berry, I had to stop and think of whether or not I had a healthy respect for the problems that can occur from change, or if I was being overly conservative (or fearful). Glenn makes a good point that so many people do not upgrade or change their drivers, firmware, or other software. People don’t patch their SQL Servers, even with Service Packs. I’m sure some of that is fear, but some of it is neglect as well.

    For me the decision usually comes down to examining the reward/risk ratio, trying to understand if improvements are balanced by the risk of downtime. I do value stability above new features, mostly because if problems do occur, I will be the person fixing them. That doesn’t mean I avoid all changes. I think Service Packs need to be installed, though not necessarily the first month. I’ve also come to embrace some of the continuous integration (CI) and continuous deployment (CD) ideas as ways to both reduce a software inventory as well as hold developers to a higher quality standard. However if you want to deploy (and perhaps patch) in a continuous deployment environment, then you should ensure that your CI process performs strong checks and make sure your developers are holding themselves to a high level of quality.

    We change the way we work, and the tools we use in technology often. Change is a concept we embrace, and we should since the ways in which our systems work are regularly changing. Bugs are patched, new techniques and tools are developed that should make us more efficient and productive. Those don’t always work, and we should be wary, but we should also not fear change. We should evaluate each new possibility with the attitude that our decision to move forward “depends.” It depends on the ease with which we can integrate something or apply a change, and the ease with which we can roll back our changes if they do not perform as expected. It also takes practice to ensure that all those things are easy.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • CHOOSE in SQL Server 2012

    The CHOOSE command is new in the T-SQL as of SQL Server 2012. I hadn’t ever had the chance to work with it, but after seeing someone submit a piece recently, I decided to play with it a bit.

    This feature is essentially an indexing value from an array. Here’s a short example.

    SELECT CHOOSE(1, 'First', 'Second', 'Three');

    The first parameter to the CHOOSE() function is an integer, which is the position. The second (and subsequent) parameters are the array of values. Here’s a better example. Let’s say I have a lookup table of titles:

    CREATE TABLE titles
( titleid INT
, title VARCHAR(20))
;
INSERT titles 
 VALUES (1, 'Manager')
      , (2, 'Developer')
      , (3, 'DBA')
      , (4, 'Sysadmin')
      , (5, 'Storage Admin')
      , (6, 'Help Desk')
;

    I might potentially have a very large table. Imagine that I work at SalesForce.com or I have some type of EAV table here. If I am performing a join with employees, I could easily do this:

    SELECT 
   e.firstname
 , t.title
 FROM dbo.employees e
  INNER JOIN dbo.titles t
    ON    e.titleid = t.titleid

    However, suppose I have performance issues, or I’m joining to lots of tables. Perhaps I’d prefer not to actually join to another table for some reason. I could do this instead:

    SELECT 
   e.firstname
 , CHOOSE( e.titleid, 'Manager', 'Developer', 'DBA', 'Sysadmin', 'Storage Admin', 'Help Desk')
 FROM dbo.employees e

    Is this useful?

    I’m not sure. Most of the examples and places I can think of for using this are rather trivial, or inflexible. This seems like hard coding values into a procedure or function when a table join might be a better option.

    I haven’t found any blogs that present practical uses that make sense, but I’m sure some are out there. Let me know if you know of any.

    UPDATE: Rob Farley mentioned he uses this in the date dimension of data warehouses. I’m not sure if that’s the best solution, but Rob’s a smart guy, so I’ll take his word that this is a good use of the function.

  • The Joy of Technology

    Here's my Airplay receiver.
    Here’s my Airplay receiver.

    I ran across this piece on the joy of being a programmer, which resonated with me. I started at a programmer, at about the same age, 10 or so. I’m a little older than the author, but I’ve spent most of my life in the technology business, primarily working with SQL Server, but I’ve built software as well. I’m still building a little software, mostly for fun. I recently set up a Raspberry Pi to stream Airplay music from my idevices and created a ball shooting Lego Mindstorm robot. Granted the latter wasn’t much programming, but I’ve backed a Kickstarter project that will allow me to write some more complex code for the Legos with my kids and I’m looking forward to receiving the kit.

    Programming has been a fun hobby and career for me. Whether that’s been working in a front end language like C++ or VB, writing T-SQL that produces reports, or even scripting administrative tasks in VB Script or Powershell, it’s always a challenge. I find myself going through the same pattern of emotions over and over. Excitement as I start a project, confidence in an initial solution, frustration when things don’t work, and a smile after the burst of creativity that finds a solution. Granted, I may iterate through these emotions a few times, perhaps even spending more time struggling than solving, but it’s been an endeavor that I enjoy most of the time.

    Just like the author, I’ve had success in my career, and found technology to be a better job than many other jobs that people I’ve worked with have had. Looking at the accountants, the managers, the salespeople, and all the other workers in the various organizations I’ve been a part of, I think I’ve had one of the better jobs that exist in the world today. Physically easy work, mentally challenging, well paid, and with demand for workers. I hope you feel the same way about your technology career.

    Steve Jones

    The Voice of the DBA Podcasts

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