I was recently working with a student in a one on one format and one the scenarios he wanted to learn to handle in SQL Server (he was an Oracle DBA cross training) was how to recover from the proverbial accidentally dropped table when there wasn't enough space to do a parallel restore and then move the missing object/data back to the original database. Have to say I'd never been in the situation where I didn't have the disk space for that, and of course the challenge is that even though you have missing data, you also typically have a lot of good data you don't want to lose.
What do you do? (humor for today)
Here's the steps:
It can be a little more complicated if multiple objects were affected, say in the case of a foreign key with cascading deletes enabled, but the overall process stays the same. Time to recover is basically 2X what it should be if you had space available on that server or another one.
Maybe something to think about and bake into your next DR practice session.
The Jacksonville SQL Server Users Group (www.jssug.com) is hosting a SQL 2008 launch event on Friday, Sep 12, 2008 in downtown Jacksonville. Plans are for 15 sessions, all focusing on SQL 2008 and most being presented by speakers who live in the greater Jacksonville area. Registration is open at http://www.sqlsaturday.com/register.aspx?eventid=10. Think of this is a super user group meeting rather than a true community event, the former being less formal and less complicated logistically.
As an interesting aside we're hosting the event on the SQLSaturday site/framework even though it's not truly a 'Saturday' event. It has the tools and process in place, why not use it? We're expecting that to happen more going forward and are working on some ideas to let us present various views of the events so as to avoid the mental disconnect resulting from having a Friday event hosted on a Saturday site!
Slightly off topic today.
It's fair to say I'm not a great traveler. Waiting for planes, dealing with the hassles of travel, etc, etc, all result in me prefering to stay in Orlando as much as possible. But I don't quite have the luxury of not travelling at all, and recently I spent a week in Boston - one of my favorite cities to visit, provided the weather is nice (nice as viewed by a Florida resident). It's a good city for taxi or public transportation, having a car is a pain between lack of parking, toll roads, and even my hotel wanting to charge $30 a day for having a car.
After hours visits this time included my favorite ship the USS Constitution, Faneuil Hall for dinner one night, the Harvard Coop Bookstore (in particular this reminded me of how anemic the computer book sections at most book stores has become), a walk through Boston Common, a great and unexpected visit to the site of the Boston Massacre along with an off the cuff telling of the history of the event by a local attorney we met while walking, and probably the highlight was a fantastic burger at Mr. Bartleys (you have to read the menu!).
An interesting side note was I ran across Warheads at a local gift shop. They are a sour candy I'd seen on a FoodTV show and given that I like Tangerine Sours, thought I would try. Labelled as an extreme candy and correctly so, not for the faint of heart, and not for me either!
I'd like to think that each city has something unique to offer and it's just a matter of finding it, but not always easy when you're there for only a few days and can only wander after 5 pm. A little easier in bigger cities, but still not simple.
I've used Quicken for a long time now and it's the center of how I manage finances at home. There are similar packages on market from other vendors (MS Money, etc) which probably offer similar features, but I've always liked that Intuit stays focused on their market and I love the way they drive revenue (Quicken 2004, 2005, etc, etc). In my view there's a single trick to making Quicken work for you and that's to use financial institutions that support a direct connection to Quicken. Every couple days I'll hit the update button, download my latest transactions from checking, savings, credit cards, 401k, and stock account, and then spend 5-10 minutes categorizing them and clicking ok. It's a lot faster than typing in all the transactions from receipts and in many cases will set the category for you automatically.
There are some really nice benefits to this. One is that you're looking at your accounts every couple days to once a week depending on your schedule and that lets you instantly spot signs of fraud or identity theft. Another is that you can easily reconcile right then to your online balance. When my monthly statement comes in I file it, never needing to do the old fashioned and often painful work of reconciling.
Because getting the data in and maintaining it is about as easy as it can get, it's not a big step to run a report or two to look at spending patterns, figure out much you've spent on pet food, or build a budget and compare actual to forecast. I know there are many ways to manage money, but this has worked for me and it's one of those patterns I depend on.
One of the things I teach in our admin course is that it's not enough to just be the gatekeeper/central point of change when it comes to being a DBA, we should be adding value:
You'll notice that I don't put in the list verifying the change is correct - that's a QA task!
One sub bullet point I've added to 'is it stupid' recently is checking queries for unused tables. I ran across a couple instances lately of what was probably copy/paste code reuse and I was tuning the queries, realized that several tables were included that had no effect on the results. Easy to miss something when you see things like this so it definitely needs to go back for testing, but it's worth taking the deeper look right then, before you deploy.
As I write this a tropical storm is passing over Orlando and has turned out to be milder than expected (good) and authorities have taken steps to cancel school and ask people to stay at home if at all possible just in case (also good in my view). Yes, you can drive in 40-50 mph winds if you have to, but why take chances if you don't have to? Let the first responders work on important stuff, not having to come bail you out because you just couldn't miss a day of work.
An old friend emailed to say his employer was not closing for the day or even giving employees the option, because the winds wouldn't be that strong. In this case the winds are lighter than expected, but just as easily could have gone the other way. It's not just employee safety that's an issue though, local schools are closed - what do single parents do on 4 hours notice that they need a sitter for the day? Does the employer let them bring the child to work? Is this really taking care of employees?
Some businesses need to be open as long as possible during these events - grocery stores, gas stations, home improvement, and of course police/fire/medical staff. Part of the job for all of them, but even for them it's useful to remember that they'll be more effective if they know what will be expected of them well in advance and you help them take care of family in whatever ways you can.
For many businesses it's about lost profit more than anything. Close for a day and you miss out on 1/20 to 1/30 of the planned monthly revenue. Miss your numbers and your President/Board start getting itchy. There's going to be a few days a year where stuff goes wrong, from storms to bad software upgrades!
And what about us might ask, we run a training business here in Orlando, what steps do we take? Typically we follow along with whatever the local school distict does. If they close, we close. As I write this I'm sure that it's safe enough to be out and about, but at 7 am I couldn't have guaranteed that for the day. Safety of our students come first - not out of fear of some lawsuit but because it simply should come first. We also try to be good citizens as part of our community, and if they want us off the roads, we'll do that if we can. Students are stuck at their hotels of course, not the most fun venue, and we call to check on them to be sure all is well. Easy for us because we charge by the class? Our primary plan is to make up the hours after the storm passes. Should that not be possible, we'll refund them a pro-rated amount based on lost hours. It's not perfect, but it's a decent compromise.
Personally I don't find it too hard to decide what to do in these situations, I want to treat clients/employees the way I want to be treated, and I'd prefer to be at home with my family. Work in important, but not that important.
Registration has been open for only a few weeks and we've got 110 registered so far, a nice start! We already have a number of potential sessions submitted and more on the way. Registration and call for speakers is open at http://www.sqlsaturday.com/register.aspx?eventid=11.
If you haven't attended a SQLSaturday before - especially if you're thinking about hosting one - please consider finding your way to Orlando for the weekend. It's a great free learning experience on the SQL side, and nothing like watching an event in action to learn how to do it and what you might want to change.
I was visiting with a client recently when they asked me to come take a look at an incident in progress, the number of connections to the server had just about doubled over what they were normally. As far as troubleshooting this scenario I always start by checking for blocking. If you've got spids blocked they can't go back into the connection pool, forcing the connection pool manager to open up new threads.
No blocking? The next step is to run two different group bys against sysprocesses, one by hostname, the other by program name. I'm looking for a clue that will show all the connections are being generated from one machine or one application, or both! In this case we could see that the connections were evenly distributed across the web farm, but trying to break down by application wouldn't work because they didn't set the application name in their connection string. Fixing this is easy, just add the following to the current connection string:
;Application Name=My App v1.1.1
We couldn't identify anything obviously wrong on the server, in the error/event log, or in their application logging. Within a few minutes we saw a nice reduction in connections and soon things were back to normal. Would having the application name have made a difference? Unknown to unlikely in this case, but given that it's so easy to do, why not make the change so that next time you have one more piece of data?
Actually it was subset of the group, but Kathi & Julie from the St Louis SQL Server User Group did a great job of marketing my free Succesful Technical Speaking class. Microsoft was kind enough to provide both meeting space as well as lunch in a great show of community support. We had about 10 people attend and for most of them I think it was the right class at the right time, start the process of conquering the fear of 'I can't answer every question'.
I'm always re-evaluating this class in particular - should I rebrand it to Beginning Technical Speaking? Should I include how to use Powerpoint (I talk about technique but no hands on right now because it switches from ideas to doing), longer labs (some people tend to get very pale at at the idea of a 15-20 minute first time presentation!). I'm presenting the class again here in Orlando in September, I think I'll do one more iteration and then decide what approach to take for next year.
Part of the class is why someone wants to speak. Many reasons, but a common one is to build their resume/profile - nothing all wrong with that. Presenting is good, but you'll reach far more people by writing an article for SSC and probably have less stress because it can be a very deliberate effort. Not trying to talk anyone out of speaking, but if you're really serious, you need to do both. You'll reach different audiences and built two different skill sets. I've been talking to my friend Steve Jones about him building a writing class, but so far I haven't put forth a really compelling reason - but my best one is that not only does he have the writing experience, he sees on a regular basis lots of different styles, good and bad. We'll see!
But to return to speaking for a moment, I'll be trying to about 10 different out of Florida user groups next year to meet new people, sharpen my skills, and of course - to market my skills and training business. If you're looking for a speaker drop me a note via www.endtoendtraining.com (no, I'm not posting my email here!) and we can also talk about whether the speaking class might be a nice add on to my visit.
This was actually posted about a week ago, fell behind a little during travelling. Building a Security Philosophy was written to get people to think about they approach security. Do you give the proverbial Junior DBA only partial access? Do you believe in table access? Do you use the built in roles?
I have opinions on the topic, but it's not clear that there are always right answers, and definitely some that are situational. Many of us have the philosophy that we acquired at the first job, or from the first manager or peer - at some point it's worth revisiting to decide if we still agree with those principles held for so long!
Just had a note from John Magnabosco, the schedule for the event on Oct 4, 2008 has been published. I'll be there doing a presentation on Replication, Steve Jones is doing one on Green Computing, and I see Kevin Kline has two on the agenda as well.
This is a true community event, with content on .Net, Sharepoint, Enterprise Development, and more - about 36 sessions so far. Hope to see you there!
Paul Waters and team from the SQL Server Innovators Guild have decided to host a SQLSaturday at ECPI Greenville, SC Campus on October 11, 2008. Registration and call for speakers is now open!
Part of running a businness is deciding when and where to spend your hard earned cash. It's interesting to look at those decisions as a consumer and wonder what goes into them - is there a lesson that I can apply somewhere?
For example, if you buy gas and pay at the pump you're typically asked if you want a receipt. This serves two purposes; one is too save on the cost of paper for the pump (I suspect the main reason) and the other is not just print it and have it wind up on the ground near the pump. Easy enough for each of us to make that yes/no decision (though why it typically occurs at the end of the purchase instead of the beginning in most cases is unclear).
By comparison, if you visit your local Lowes/Home Depot you'll get a receipt that might easily be two feet long, and most of that is information about taking some online survey. Borders does a long receipt that has a discount if you come back and buy something in x days. I don't find either approach especially effective; I'm there that day because I needed to be/wanted to be, and I'm unlikely to take a survey unless something really bad happened (and in that case, much more likely to seek out the manager then), and I don't plan trips to the book store based on the date of the discount (maybe I should).
So is it that even a very low hit rate on the receipts is worth the printing cost, or is it that it just seemed like a good idea and the cost is trivial?
I know I'm not comparing apples to apples, gas stations don't usually have a big margin on gas - it's the thought process behind the decision that interests me. Would gas stations do better long term if they printed longer receipts?
Recently I needed to make a call and the only number I had was a mnemonic one. I get why they are useful, you want something easier to remember than numbers and perhaps just memorable in general, but my phone doesn't happen to have the letters on the keys (BlackBerry curve). Then I'm looking around for a phone with real keys.
Turns out that there is a reasonably simple solution on the Blackberry, just hold down the ALT key and enter the letters, when you hit dial it converts them to numbers. Yes, it's apparently in the manual, but seriously, is that something I'd be reading? It's a phone!
So business people, what I'd wish for is that in addition to the memorable number put the actual number in parentheses after it.
But a good blog entry bakes the whole phone number. Want to see if there is a mnemonic for your phone number? Or learn what makes a number easy to dial?
With all the options we have for viewing query plans I still prefer the graphical view. Combined with tooltips and the occasional journey to the properties dialog it solves the problems I work on. The only downside is that the plan view tends to be verbose and the zoom options just don't work well. Resize to much smaller than the default and you can barely read them, leaving the 'fly over' mode (using the "+" button in the bottom right corner) to navigate the plan looking for whatever is to be found. It's not unworkable, but it does get in the way of a holistic view of what happens when. In other words, we have to divert a portion of our attention from understanding the problem to just using the tools.
Given todays prices there is a simple alternative, just upgrade your primary monitor to 24". No, that won't handle every plan, but it make most of them a lot easier to work with. At probably $400 it's not cheap, but doable. I've seen 22" wide screens recently for under $200.
I do tuning with two monitors, writing/examing queries on the left, Profiler on the right. Alt-Tabbing around is another distraction when I'm problem solving. Using Profiler let's me see easily the exact cost as well as the history of what I've tried/changed so far. I mention that because while using monitors of two different sizes is better than using two monitors, it's also a little visually distracting. The best case is having monitors that are exactly the same.
So there you go fellow DBA's, a starting point for the budget request for next year. Justifying tools is never easy compared to 'solutions', but monitors is an easier sell than some. Struggling to get the monitors approved? Suggest that perhaps your boss, or the CFO/CEO give dual 24" monitors a try to see if there any benefit. Shortly after that monitor sales will go up, everyone in sales will have two monitors, and if you're really lucky, you'll get them too!
