Voice of the DBA

Author: way0utwest

  • The Decision to Fail

    failoverWe have a number of automated technologies that we can us to seamlessly move from primary to secondary systems without human intervention. SQL Server incorporates a number of these, and many companies use them to ensure their applications are highly available. However things don’t always go as planned in a disaster and sometimes humans get involved.

    Unless you are one of the companies with a very large budget and high risk of business issues when systems failover, you probably have some sort of high availability (HA) or disaster recovery (DR) process that requires human intervention. Log shipping, for example, usually requires that some human reconfigure the application to use secondary servers. Even with Availability Groups, clustering, or database mirroring, you may need to manually fail back to primary systems.

    In those cases, it’s not always a clear decision to do so. Many of the switches are disruptive, or have the potential to be disruptive. Cluster fail-overs should not impact the application, but there is a brief period where clients may not connect. Outside of disasters, Management, and often technical people, usually want to schedule any failovers after they have prepared the end user for potential issues, however brief.

    In disaster situations, when there hasn’t been a complete failure of a system, you may not want to have unscheduled failovers right away. This week I want to know:

    How do you make the decision to fail over from one system to another?

    I’m speaking to you, the data professional or the administrator. I would guess that most of you are not the one that ultimately makes the decision to leave your primary systems. Often I’ve found that someone in management has to make the decision, but with input from the technical people. In that case, think about how you present the situation and pros and cons of the failover. Do you give hard numbers, like latency and relative CPU power in failover machines or do you attempt to quantify the effects on the business when secondary systems are in use.

    I’ve rarely had a large budget for secondary systems. Network bandwidth, CPU and memory, and more are sometimes sacrificed in secondary systems in order to align the cost of these systems with the risk of needing them. In many cases, we didn’t have automatic failover for many systems because we had to know our primary systems would be down for more than 5 or 6 hours before we would switch to the backup environment.

    If you have similar guidelines or processes in place, let us know.

    Steve Jones

    The Voice of the DBA Podcasts

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

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