Voice of the DBA

Category: Editorial

  • How Do You Setup Your Instances?

    I’ve set up a lot of SQL Server instances in my career. I’ve gone from manual only setup in SQL Server 4.2 to more automated means in the latest versions. The easiest was actually in Azure where I only need to specify a few parameters for a PoSh cmdlet. However, unattended setup is pretty easy as well for local SQL Server instances. If you’ve never done it, you’re missing out. There are plenty of other ways to do this with tools like Chef and Puppet, AMIs in AWS, and more.

    Erik Darling wrote a post recently called Setting Up SQL Server: People Still Need Help. Erik’s point in the piece is not that installing SQL Server is hard, but that many people stick with the defaults once they’ve installed the instance, never changing anything. There are some basic things that you’ll want installed all the time, so having a repeatable process is important.

    This week I’m wondering how you install instances in your job. If you need to add a new SQL Server for production or development, what do you do? Let us know your process and procedure.

    I don’t set up too many instances, and in fact, I mostly add them as a lab instance on one of my machines. For the initial install at times, I’ll just run through the manual install, but I then have a quick config script that I use to change a few items, but very few. In most cases, I don’t do much more than add a few logins, limit memory, and enable the DAC. For the cases where I want to add a few different instances for testing, say for looking at patches or using mutli-server features, I’ll use an unattended install script.

    There are some amazing ways people have created for repeatable installs, such as the Finebuild project. If you’ve got a way that works for you, let us know. Just be sure that whatever repeatable process you use changes some of the defaults and ensures your SQL Server is better prepared for any workload to come.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Defending the RDBMS

    A few weeks ago I ran across an essay from Randolph West called, Relational Databases Aren’t the Problem. This was a response to another essay that made a case for relational databases being bad for many businesses. I thought that both pieces were interesting for different reasons. Certainly I don’t believe the the RDBMS is perfect, and it certainly can be hard for developers to build software that interfaces with a relational system.

    The original complaint about the RDBMS is somewhat rambling and deceitful, in my opinion. It is an excellent study of how to use a few concepts to confuse and create doubt in a casual reader. If I weren’t reading closely, I might fall for a number of the issues that exist with relational databases. However, in my mind, part of the issue is that quite a few of the issues that are discussed aren’t problems with relational databases, but often the issue with poorly developed software or design of the entities and relationships. I find myself even more disappointed that the author hasn’t really addressed any comments, but rather just pasted a link to his followup article.

    I do think that the defense from Mr. West does a good job, though it also misses some of the primary issues we struggle with relational databases. There are problems with the knowledge of how to build a well performing database, both from application developers that view this as a necessary evil as well as experienced database developers that don’t regularly improve their skills and try new design techniques.

    I also think that both of the pieces fail to address the issues of gathering and working with multiple rows of data. The second discussion of “doing without databases” really implements its own database management structure, which may work well, but is fraught with issues such as the concurrency issues of multiple users searching and scanning through data without having indexes. While indexes are overhead, they are necessary as hash buckets aren’t necessarily feasible for all the properties in a class. Also, if you end up building them for multiple properties, you’re building an index. There’s another good defense of some of the issues here.

    I do think that keeping more data in memory and synchronizing access to structures sounds great, but scaling that out to multiple systems, and ensuring consistency at high volumes, not to mention potential loss of data issues from crashes are a problem. Having a write ahead log in SQL Server does a wonderful job of ensuring we can handle redo/undo on system restart. The method presented doesn’t necessarily ensure this, though perhaps accepting some data loss from high concurrency changes is OK for many applications.

    I will say that the idea of all data in memory is interesting. I had to stop and think about how many databases really have more than 1TB of data. If we throw out indexes, does this cover most data stores? I bet this does, though that doesn’t mean that there aren’t issues with using in memory array structures, with widely varying data sizes.

    Would I use an in-memory data structure for software? It’s tempting, but honestly, I wouldn’t. The value of data is too high, with potential issues from poorly implemented ACID control structures. Plenty of issues have been found with different RDBMSs over their years, and even some in NoSQL systems. Thinking that I could avoid any issues and protect data is something I wouldn’t even try. After all, if there is some error, I’d prefer it from a system that many people use, rather than one I tried to emulate for no good reason.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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 at that point in time. 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 thing that many technical people like to do. Many of us are Type-A, and we like knowing our plans and having a schedule prepared in advance. We can then decide which sessions might have greater priority for us during a time slow and adjust our choices to have the best experience. After all, we may want to see two speakers, but if they present at the same time, we have to make a choice.

    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 in a way that makes sense, even to us. There are no shortage of people that make plans today and are unhappy a few weeks later.

    Even if we knew what we wanted to watch, many of us might think a session on database design has to cover third normal form, even when the text said this examines PKs and FKs. We might assume an SSIS data load talk included something on CSVs when the presenter described the talk as being for ragged right 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. We can make mistakes in our analysis often when we simplify our view of a problem to the point where our analysis is inherently flawed. If we don’t account for this and assume we’re flawed, we may overweight our conclusions.

    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 decision, but I try to ensure I have the most important, or significant, data. Or 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 report, our application, or 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 how I feel at the event. I should have a plan, but be willing to flex as circumstances change. And, always have a backup. I like to pick two or three sessions for every time slot, just in case.

    Steve Jones

    The Voice of the DBA Podcast

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

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