Steven Jones posted an excellent editorial today all about how your backups are only good if you know that you can restore from them. He couldn’t be more correct. I posted the following thoughts in the comments, but I know not everyone reads the comments in articles & editorials. Although, if it’s a good article, you should read the comments, especially on SQL Server Central. Frequently the discussion about the article can be as enlightening as the article itself. But I digress.
Steve’s point, pretty clearly stated but I’ll repeat it, backups don’t matter, restores do. I’m going to pile on to this point just a bit, because it can’t be emphasized enough. Nothing is more important than verifying backups, except, verifying that you know how to run a restore. You’re absolutely right when you say that backups are no good unless you can restore them, but it goes beyond actually validating that the backup files themselves are valid and accessible. You need to know that you, and any other DBA’s in the organization, can actually run a restore, knows how to read the file header, can do a point in time recovery, etc. Practice restoring databases not only validates that the backups are good, but that you’re good as well.
Permalink
1 Comment
I had a fun support call I need to share. A developer called up to tell me that a particular dev instance was offline. He informed me that the server SQL08\DEV01 (the names have been changed to protect the innocent) server was completely inaccessible. I knew that multiple development teams would shortly be calling and that I’d better get on this issue most riki-tik. I quickly typed the connection string into Management Studio and watched in confusion as the server instance popped up on my screen. It was fine. I did a number of checks, looking for active connections, recent connections, errors in the log, indications of a recent reboot… Nothing.
I called the developer back and told him that the server was fine. He called me again in two minutes to tell me that it was still offline, or was offline again. My connection was still open so I checked everything a second time, with the guy on the phone. I told him, no, it’s still there. Then he said, “Wait, which SQL08\DEV01 are you looking at? Is it the one with database X or database Y.” Now I was confused, but looked at the database list and told him that database X was on the server. “I want the other SQL08\DEV01” he then told me. The other one? What the…
Then it hit me. We have quite a few instances of development servers all running on the same server. So I started checking instances, looking at the databases on them and finally found that SQL08\DEV04 was what the guy needed. I called back and told him that he had the wrong instance and needed to change his connection string. I was then given a lecture that he had been using different copies of SQL08\DEV01 for quite some time and could I please find someone who knew what they were talking about to help him… Yeah, one of THOSE developers.
After a couple of deep, cleansing breaths, and one more fervent wish that the company would let me hang a heavy bag in my cubicle as a means of further relaxation during trying episodes (I promised not to put people’s faces, likenesses, caricatures or even names on the bag), I got back on the phone with him. I asked him to send me over his connection string so I could see what was going on.
When I got it I saw an interesting point. He had the server name & instance, but he also listed a port. It just so happens that we’d had an issue with the DEV04 instance and had recently recreated it from scratch. I guess whoever did it let it get a different port in the process. Mr. Knowledgable had been connecting to the wrong instance because, and this is the kicker, when you supply a port to the connection string, it ignores the instance name. This is why he was able to connect to the “other” DEV01 instance. Just something else to watch for.
Permalink
6 Comments
I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven’t been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is.
The fact of the matter is that I’ve been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I’ve blogged before about our use of Microsoft’s Operations Manager for monitoring our servers and how we’ve built custom rules and monitors to keep an eye on things. I’ve also mentioned how we use Idera’s Diagnostic Manager as a drill-down mechanism to keep on eye on SQL Server internals that OM just didn’t do as nifty a job on. What I haven’t mentioned is, that after attending the PASS Summit (and if you didn’t go, you sure missed out) and sitting through Buck Woody’s session on monitoring using Policy Based Management, “More Servers Less Control,” I’ve implemented PBM within our organization.
If you have yet to explore Policy Based Management, PBM, get on it. I know that others have blogged about their experiences with it, and there’s a reason that people are excited. Track down Buck Woody’s session over at the PASS site (it’s worth paying for it, trust me). You too can get this stuff up and running in no time. That’s the good news. Now for the bad news, PBM only works with SQL Server 2008. But, I’ve got some good news about that too. There’s a great utility published over at CodePlex (and isn’t that a great place to browse on occasion) called the Enterprise Policy Management Framework, EPM (you can never have enough acronyms, especially with three letters). The EPM Framework allows you to run policies against 2000 and 2005 servers. Sweet!
All of this is work has been part of a concerted effort within our organization to get really and truly proactive, to prevent errors before they occur. Between the modifications I’ve made to OM and the implementation of PBM through EPM and reports against the OM data through SSRS, SQL just isn’t having as many problems these days (I love talking through acronyms sometimes just to watch peoples toes curl). I’ve been busy as all get out, but it’s on pretty mundane, non-exciting stuff. Hence fewer blog posts.
Permalink
1 Comment
I’ve been somewhat light in my blogging of late. Unfortunately, I just haven’t hit any interesting or difficult problems lately that were worth sharing. I’ll try to break one of our production systems in an interesting way very soon so that I’ve got something to write about, even if it’s how to look for a new job.
Permalink
4 Comments
I was tagged earlier this week by Tim Ford to comment on my biggest weakness. I wish I could say that it’s the color yellow (stupidest weakness, ever), or kryptonite. I don’t have to wear ruby-quartz glasses everywhere (and good thing too). Certainly getting tied up by a man isn’t my weakness (eeewww). And while I do agree that with great power comes great responsibility, I don’t feel responsible for everything. I’m not suffering from a terminal heart condition (or shards of metal in my blood stream). So, I’ve avoided all the obvious (or silly) weaknesses. No, unfortunately, my weaknesses, and they are myriad, varied, and far too numerous, are pretty much of the straight human variety, despite my rather obviously being a mutant. Trying to pick one that stands out, it almost depends on the hour of the day, from the hundreds & thousands there are to choose from, is tough. The one weakness that does immediately come to mind though is communication and communication style.
I don’t have Asburger’s, I think. I tend to speak directly and bluntly. That’s when I speak at all. With effort I can be personable and friendly and, on rare occasions, charming. But it’s an effort. I have to try, and I have to keep thinking about talking to people, it’s a struggle and it shows. Left alone, left to my own devices, I stop talking or I only blurt out stuff occasionally or I try to run conversations, talking over people. It’s tough. The interruptions especially get people upset.
The obvious shortcomings of poor communication aren’t even what bothers me about it the most. The one thing that I think hurts me, and makes this truly a major weakness, is that I make people angry, but I do it unintentionally. Don’t get me wrong. I don’t mind making people angry. I’ve even gone out of my way to do get certain individuals good and upset. It’s when I do it accidently, because I’ve interrupted or because I’ve blurted out that their pet theory, whatever it might be, is stupid, that really hurts, me and that person. The fact is, what they’re saying may very well be stupid, but people don’t appreciate getting told that, especially in such a blunt manner. If you make people angry, they remember that. They don’t remember that you did help fix their problem, or that you saved the company oodles of cash or that you kept the project afloat. Nope, they remember that you really made them mad, which means that you’re “hard to work with.”
I’ve been working on this, a lot, over the years. Some of you who have met me out at conferences or at user group meetings might never have noticed this behavior. That’s because I’m using all that communication time to try to work on it. Some of you at those same functions may have been all too aware of it. I really have gotten pretty good at staying “on” and communicating at least reasonably well for long periods of time, but it’s bloody tiring.
My version of kryptonite is now on the table. Let’s see who has trouble with materials made out of wood, the color yellow, or gets weak when tied up by men. How about I tag, oh, Mike Walsh (I’ll bet it’s that tieing up thing) and Gail Shaw (probably red kryptonite).
Permalink
17 Comments
I’m going to try out Adam Machanic’s idea for a blog party. The topic this month are Date/Time tricks.
Instead of supplying a trick for Date/Time, I’m going to caution you about the tricks that you use. Let’s take a simple issue. You want to pull back data from a table, let’s use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let’s create an index on the table:
CREATE INDEX ixTest ON Production.TransactionHistoryArchive
(TransactionDate)
The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003:
SELECT tha.TransactionID
FROM Production.TransactionHistoryArchive AS tha
WHERE DATEPART(yy,tha.TransactionDate) = 2003
AND DATEPART(mm,tha.TransactionDate) = 7
In theory you should be able to use the index that was created earlier, but instead, you’ll see this execution plan:
The problem is occuring because there is a function running against the columns. This is going to force a scan, even though you have a good index. Rewriting the query so that it looks like this:
SELECT tha.ProductionID
FROM Production.TransactionHistoryArchive AS tha
WHERE tha.TransactionDate
BETWEEN '2003/7/1'
AND '2003/7/31'
Eliminates the function on the column so that the execution plan is now a nice clean index seek:
Whatever tricks you begin to apply to date/time, be careful of how you apply them. And, if you try a fix like I supplied above, be sure it returns the data you think it returns, testing is the key to applying anything you read on the internet.
Permalink
5 Comments
If you missed this, here’s your chance to make it up. If you were there, and like me, you need to rewatch it about six to eight times to try to understand everything that was presented, here’s your chance. Dr. DeWitt’s key note was probably the high point of the Summit or at least in the top 5. It’s not to be missed.
Permalink
Leave a Comment
Andy Warren in the latest PASS Connector has posted an update on where we’re at with the SQL Server Standard. I’m so happy that Andy has been keeping this out in front of people. It provides some impetus to get the work done. Except for the authors thanks (and they’re receiving $500, so they should say thank you), there’s very little feedback on the Standard to show whether or not people are interested, if the goals and ideas are worthy… In other words, you guys need to let us know what you think about the thing.
Two more articles are in the hopper to be published. Another has gone into copy edit. We’re technical editing two others. That’s five more, so you guys can expect to see another ten weeks worth of SQL Server Standard. But after that…
Don’t you want $500? Just submit an abstract and your writing history to grant.fritchey@sqlpass.org. We’ll get you started.
Permalink
Leave a Comment
I’ve been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That’s a tough one. It would make a much shorter, and easier, blog post to describe the things we don’t use it for. However, keeping with the spirit of these tags, I’ll try to lay out it.
For those that don’t know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we’ll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70’s and 80’s propped up by ancient men with pocket protectors, spit, bailing wire and happy thoughts. This also means that we have apps running on SQL Server 7, 2000, 2005 and 2008. Give me a couple of weeks and I’m sure I can get an R2 app deployed. There is also a few Oracle databases, our warehouse and Peoplesoft in particular. We even have a DB2 and, I think, one Sybase database somewhere.
I don’t want to go into lots of details about the type of data we store, lest I get in trouble, but basically, think insurance and you’ll get a pretty good idea of a lot of it. Add in the fact that my company prides itself on engineering to avoid risk and you’ll know that we gather quite a bit of information about the things that we insure. There are lots and lots of very small databases. Our largest db’s are just breaking 100gb, but must are in the 20-60gb range. We have a ton of OLTP systems gathering all the different data. These have been developed in just about every possible way. We even have a couple of systems using nHibernate under development. We move, mostly, pretty standard structured data. We have a few processes that are using XML, mostly from third party sources, to import data, so we’ve learned how to shred that stuff into the database. Spatial data, insurance remember, is the big new thing on the block. We’re seeing lots more implementations taking advantage of this. We don’t see much in the way of unstructured data, but some of the reports from the engineers falls into this realm. We also get quite a few photo’s from them that want us to store. We’re working on using FileStream to keep those in sync with the db rather than storing them within the database itself.
Currently, and I hate this, the overwhelming majority of our OLTP data is collected in SQL Server. All our datamarts used for reporting are in SQL Server. BUT, in the middle sits our Oracle warehouse. So we have to convert our data from SQL Server into Oracle and then back into SQL Server. It’s fun. Swapping data types over & over, shrinking column names only to re-expand them into something a human being can read… It’s lots of fun.
We use SSIS for almost all our ETL processes, although we have a few DTS packages still chugging away on some of the 2000 servers. We’re running a bit of replication, but nothing major. We have several fail-over clusters in production. We’re running virtual machines in lots of places. We’re expanding our Reporting Services implementation pretty rapidly. After attending Buck Woody’s sessions at PASS this year we’re getting Central Management Servers and Policy Based Management implemented.
Most of my time is spent working with development teams. We do most our deployment work using Visual Studio. I do database design, stored procedure writing and tuning, data access methods… you name it and I’m involved with it. The level of our involvement varies from team to team, but by & large, we get involved early in most development projects and are able to influence how databases are developed.
For monitoring we’re using Microsoft System Center Operations Manager (or whatever it’s called this week). We’ve modified it somewhat, adding special rules & monitors to keep an eye on the system better. We also use Idera’s SQL Diagnostic Manager to help keep a more detailed eye on the systems. I already mentioned that we use Visual Studio for most of our development & deployments. We also use several Red Gate products, Compare, Data Compare, Prompt, pretty much every day.
That’s pretty much it. We keep busy and keep the systems up, running & happy most of the time.
Because I think they’ll have interesting answers, I’m going to pass this on to Jeremiah Peschka and Tim Ford.
Permalink
4 Comments
Home of the Scary DBA 