Voice of the DBA

Category: Editorial

  • The Helpful Triggers

    Today is the day for T-SQL Tuesday #106 entries. I’m the host this time, which means that I’ll have a busy week trying to compile all the entries for the round up. Last week I wrote the invitation, which was on triggers. Actually, I wrote the invitation in August, trying to be prepared in case my August host fell through, but Wayne Sheffield did a great job, so my hosting duties went on as planned. You still have time to write a post today for the party, but if you read this later, write at anytime. T-SQL Tuesday is a great set of ideas for blog posts, if you’re ever looking for an idea.

    When I think of triggers, overall I think of the headaches they have caused me. Since I rarely see them in most systems, when they do appear, it’s often because I can’t determine why some strange action is occurring and after hours of troubleshooting, someone else will usually suggest checking a trigger. At least, that’s been my experience in the past. These days when something unexpected happens, I tend to think trigger earlier, but I hate that they’re kind of hidden. The DDL is separate from the table, the folder in SSMS isn’t obvious. Really I wish that trigger folder was hidden unless there was a trigger. That would be nice.

    In any case, my thoughts on this T-SQL Tuesday are actually a time when I thought a trigger solved a problem really well for me. In this case we had an OLTP application and a finance application. In the pre-SSIS, pre-DTS days, moving data between systems was cumbersome, and in this case, I needed to move over some data reliably from one to the other. We elected for replication, since that would solve our issues, but these were disparate systems, with different schemas, and more importantly, a schema were were not supposed to alter for the finance application.

    Enter loose coupling. I set up two additional databases, one on each instance. In these I had my tables that were to be replicated, and that worked really well, until it didn’t. Even in the v6.5 days replication would sometimes flake. No problem, these are two small tables and I can reinitialize things easily. However, to get the data in and out of these tables, I decided to use triggers. We had an insert/update trigger that would take OLTP data and move it into the small publisher database on that system. On the other side, the subscriber database had a trigger to move data into the finance system. It worked well, and if we had replication issues, we could keep working, knowing that data would continue to stack up and we didn’t need to alter our application.

    I’m a fan of each system doing it’s own thing, usually independently, and having processes that do one thing. I can stack those up, but I expect and count on failures at some point. Usually these are minimal, but I try to avoid a long set of things that might have cascading failures if one thing ceases working. In this case, even if a trigger failed or replication had issues, I could manually add some data to another table and it would get moved by subsequent processes.

    Triggers are incredibly useful in places, but I do warn people to be careful. They always fire, and often we have exceptions we might need to deal with. We also need to be sure we easily handle multiple rows in every transaction. If you can remember that, and write tight, quick, small code, maybe you’ll find a helpful use for triggers rather than one that gives you headaches.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Hybrid Cloud

    When various vendors started to push the idea of using the cloud for computing infrastructure, the focus was on moving all your systems to a hosted solution. It didn’t matter if you were interested in IaaS, SaaS, or PaaS solutions, there press from salespeople and even some technology professionals was for a complete migration. Put all your infrastructure in AWS, Azure, or some other place and your systems will run great.

    Over time we’ve started to realize that moving to the cloud isn’t as simple as just renting a new box or service and there may be plenty of work to get settled. Even then, there are some limitations or restrictions, either technical or otherwise that might make the cloud a poor choice. Security and compliance are often reasons to not move, but certainly there are also challenges with technology mismatches between on-premises and cloud platforms as well.

    One of the very interesting things that I saw demonstrated a few years ago was the idea of the Azure Stack. The idea behind this technology is that a hybrid cloud can be built, with a single management view. Both the Azure cloud and local resources are combined together and managed as a single entity, with the choice of whether to run workloads on the vendor’s infrastructure or your own on-premises machines. There are other versions of this, for example, with OpenStack working in conjunction with AWS.

    There are many advantages to this, including the ability to limit certain workloads from bursting or moving from one environment to the other. One reason for this might be compliance. An organization might be required to keep some resources off of public networks, for example, the need to ensure that certain data is domiciled in a particular country. The reverse could also be true, with the desire to use a secure public portion of the Azure stack as their system might be certified in ways that your local infrastructure may not be.

    I think the idea of cloud resources, managed, moved, and scaled as needed is a great one. It doesn’t matter if this is all in your data center, all in the cloud, or both. Working this way allows a much more flexible use of any resource, wherever its located. Cutting edge organizations already do a small portion of this with ESX or Hyper-V hypervisors, software configured storage networks, or other software configured resources that can be reallocated on demand. Azure Stack, OpenStack, and other frameworks are a great way to start to leverage the cloud, but in a controlled manner that continues to use any capital investments you’ve made.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Republish: I Feel Like a Magician

    I’m off in the UK and buried with other work, so a republish today.

    I Feel Like a Magician

  • We Don’t Have Perfect Information

    I was discussing the PASS Summit with someone and they were wondering about building their schedule. Actually, they wanted to pick sessions, but see the choices in a calendar format, but the schedule wasn’t out. My suggestion was to just build the schedule and then sort out conflicts later.
    A few people have mentioned over the years that they want to build a schedule and be ready for the event to maximize their experience and be efficient. I think that’s a common, normal, technical person thing to do. We’re Type-A, we like knowing and having a set schedule.
    The problem is that we don’t have perfect information. Even if the descriptions and abstracts included perfect information about the agendas, what is covered, and to what depth, including demos, we’d still not necessarily assimilate and recognize all that data. We’d think a session on database design covered fourth normal form, even when the text said third normal, or we’d expect that an SSIS data load talk included something on CSVs when the presenter described the talk as being with flag text files.
    We’re human, and that means we have flaws in how we deal with the world. This includes the ways in which we model and analyze data. While we can make mistakes in our analysis, we often may simplify our view of a problem to the point where our analysis is inherently flawed.
    I try to remember this when I write reports from systems that others will use. I won’t have every piece of information that might affect a system, but I try to ensure I have the most important, or significant, data. At least, the data I (and the users) feel is significant. The important thing to remember is that out data is always incomplete, and it’s entirely possible that we have missed a valuable piece of data.
    When that happens, we have to adapt and adjust our systems, just like our conference schedule. We’ll learn more across time and we can use that information to change our system. I know that my view of a conference like the PASS Summit today, or even a week before the event, will be different than what I know, and how I feel, at the event. I should have a plan, but be willing to flex as circumstances change.
    Steve Jones