October 16, 2009 at 11:53 am
What simple interview question do you all ask alot in an SQL Server interview that most people tend to have a problem with?
The question I always ask is this one, and it is really not a difficult one either. 🙂
What is the difference between DENY and REVOKE?
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
October 16, 2009 at 12:01 pm
My favorite topic to use in an interview is to have them discuss the differences between working with small databases and VLDB's, in excess of several hundred gig. When they tell me that they just use the database maintenance wizard on a 500 GB database, don't worry about the log blowing up during large batch processing etc..., I know it's time to move on to the next candidate.
October 16, 2009 at 12:05 pm
Index types. Never yet found someone who could tell me two types and the differences between them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 12:27 pm
Well, I feel better already, since I can answer all three of those suggested so far.
I like the indexes one. And differences between temp tables and table variables. Or questions about the purpose of the transaction log.
But I really would prefer a show-me test. Create a small, simple database, and put some obvious problems into it, some medium problems in it, and some difficult-to-find problems into it.
For easy problems, do something like create a table with columns all named "Col" plus a number, and all of them varchar(max) data type. Or have a table that violates 1NF just grossly.
For medium problems, how about two triggers designed to deadlock? How many would find that, even if they have a few minutes to play with the database?
For hard-to-find, it can be a missing log backup (delete the file) that breaks the backup chain. That kind of thing. (In that particular case, I wouldn't expect anyone to just notice the problem without prompting. If anyone ever did, that would be amazing. The discussion on what to do to handle it would be interesting though.) Or a proc named with "sp_", and a proc with the exact same name in master? Even better yet, have them be two slightly different versions of the same proc, so that output is almost but not quite what you'd expect.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 12:36 pm
GSquared (10/16/2009)
For hard-to-find, it can be a missing log backup (delete the file) that breaks the backup chain.
Want to be really nasty? After deleting said backup, take a hex editor to the data file and zero-out part or all of a page. See how far the person gets in finding and recovering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 1:37 pm
GilaMonster (10/16/2009)
GSquared (10/16/2009)
For hard-to-find, it can be a missing log backup (delete the file) that breaks the backup chain.Want to be really nasty? After deleting said backup, take a hex editor to the data file and zero-out part or all of a page. See how far the person gets in finding and recovering.
Sounds good. I was more thinking in terms of truncating a table, committing the transaction, and asking the prospective DBA to undo the truncation. Either one will test more than the ability to just run a restore through the GUI in Management Studio.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 1:44 pm
GSquared (10/16/2009)
GilaMonster (10/16/2009)
GSquared (10/16/2009)
For hard-to-find, it can be a missing log backup (delete the file) that breaks the backup chain.Want to be really nasty? After deleting said backup, take a hex editor to the data file and zero-out part or all of a page. See how far the person gets in finding and recovering.
Sounds good. I was more thinking in terms of truncating a table, committing the transaction, and asking the prospective DBA to undo the truncation. Either one will test more than the ability to just run a restore through the GUI in Management Studio.
Guys I was talking "simple" 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
October 16, 2009 at 1:50 pm
Yep, you were. And we digressed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 2:10 pm
talltop-969015 (10/16/2009)
GSquared (10/16/2009)
GilaMonster (10/16/2009)
GSquared (10/16/2009)
For hard-to-find, it can be a missing log backup (delete the file) that breaks the backup chain.Want to be really nasty? After deleting said backup, take a hex editor to the data file and zero-out part or all of a page. See how far the person gets in finding and recovering.
Sounds good. I was more thinking in terms of truncating a table, committing the transaction, and asking the prospective DBA to undo the truncation. Either one will test more than the ability to just run a restore through the GUI in Management Studio.
Guys I was talking "simple" 🙂
What, you mean single page restores with a broken log chain isn't simple?
😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 2:32 pm
Of course, if that particular page was affected by some transaction in the lost file....
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 2:41 pm
GSquared (10/16/2009)
Of course, if that particular page was affected by some transaction in the lost file....
As far as I'm aware, it doesn't matter. With a missing log file, the page can't be restored alone. That requires restoring the page and all of the logs since the full, regardless of whether the log backup in question had a transaction affecting the page or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 2:55 pm
GilaMonster (10/16/2009)
GSquared (10/16/2009)
Of course, if that particular page was affected by some transaction in the lost file....As far as I'm aware, it doesn't matter. With a missing log file, the page can't be restored alone. That requires restoring the page and all of the logs since the full, regardless of whether the log backup in question had a transaction affecting the page or not.
Yeah, I'm just trying to add to the fun of the potential interview question/scenario.
So far as I know, and I could be wrong, this scenario basically results in restoring to the point immediately prior to the lost log backup file, and losing anything after that. It might be possible to recover data after that point, but I'd have to research how.
My first thought was parse out the log files after that and see if you could replay the transactions from them, or if that would result in data corruption because of the lost pieces of the chain. Would depend on the nature of the database and the transactions, I guess.
That's one reason to go "belt and suspenders" and have both log and diff backups throughout the day. Or to have the log and full backups copied to a second location after they are created, thus backing up the backups. Of course, a mirror/logship/replication solution would probably also allow recovery from this scenario, because it's a fair bet that the lost log file and lost page aren't a problem in the target database. But assuming none of those things are being done, I don't think there's a way to guarantee no data loss in a broken backup chain.
Edit: And this is what I mean about having the test database be a conversation-starter. One interview I went to, they had a server crash while I was in the interview. It went so far as a stack dump and forced shutdown. Definitely gave me a better idea of how their shop was set up, and gave them a chance to ask me questions about a real-life situation in realtime. (I didn't get hired, with the reason given that I was "too strong on the subject", whatever that means, but it certainly made for an interesting interview.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 3:03 pm
GSquared (10/16/2009)
So far as I know, and I could be wrong, this scenario basically results in restoring to the point immediately prior to the lost log backup file, and losing anything after that. It might be possible to recover data after that point, but I'd have to research how.
Or run repair, lose the page, restore a copy of the DB alongside, roll the logs as far forward as possible and compare the two tables. Won't be able to tell what was deleted intentionally and what was lost with the repair (unless it's an identity column clustered index and lots more investigation is done before repairing)
Stuff post the lost log would have to be pulled out with a log reader tool, one that can read log backups, not just the active portion of the log
Of course, a mirror/logship/replication solution would probably also allow recovery from this scenario, because it's a fair bet that the lost log file and lost page aren't a problem in the target database.
In 2008 with mirroring the damaged page would be automatically requested from the mirror and repaired as soon as it was found corrupt. (enterprise edition only)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2009 at 6:56 am
Right. So without some form of disaster recovery/prevention, it's looking like that one would result in potential data loss, and certainly removes the guarantee of integrity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 7:09 am
GilaMonster (10/16/2009)
Index types. Never yet found someone who could tell me two types and the differences between them
Seriously? I find this a bit worrying, was this for DBA positions?
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply