Due to arrive from China today. I got a shipping notice of 2nd day shipping from Lenovo last week, but I think they must mean 2 days in the US. The shipping started with arrival at the UPS facility in China, from which it’s slowly made its way across the water to arrive in the US on Saturday.

I’m excited, and looking forward to unboxing this thing and getting a real laptop keyboard back in my hands.

I saw a post recently from my fellow Red Gate-er, Brad McGeHee, on backups. Brad’s got a ton of experience and he puts out some great posts that help many DBAs do a better job each and every day. I highly recommend you follow Brad’s blog if you’re new to the field.

I think that this is great advice, but I wanted to expand on it a bit since I’ve had slightly different experiences than Brad, and I’ve had a system that worked well for me.

My experience has primarily been with smaller databases. They’ve ranged from MBs to a few hundred GBs, nothing TB sized in my production experience, which for the most part ended in 2003. Since then I’ve worked only for some small companies as a consultant and for SQLServerCentral.

If time and hardware allowed it, I have always run full backups every night. That provides me protection for data that typically changed substantially every day, and was critical for the business. A full backup gave me a relatively quick way to recover the database in the event of an issue.

I have always run log backups as well. Only in very rare circumstances, < 1% of the time, have I managed databases in simple mode. The time between log backups has varied depending on the recovery interval I’ve needed, but typically log backups are run as rarely as every hour and as often as every couple minutes.

That’s Brad’s advice, and it’s worked well. The place where we disagree is with differential backups. I have found them to be a handy tool that speeds recovery for me. If you’ve ever had to recover a lot of logs, like the number from midnight until 6pm the next day, you realize it’s a lot of work. Even if you have scripted the recovery, it’s nerve wracking to try and restore all those logs in order and ensure that they get your system back. And it’s time consuming to try and debug any issues that may crop up.

After one particularly harrowing RAID controller failure, I decided that I’d use differentials as a way to speed recovery. With a differential every two to four hours, you can cut the number of logs you need to restore substantially. You also reduce your risk of a possible problem with some of your files by having fewer files to depend on at one point in time.

Saving Dollars

I found another reason to use differentials at my last job before SQLServerCentral. I worked for a large company with hundreds of SQL Servers. We had a large TSM backup device that handled file servers, mail servers, and SQL Servers. At that time, the TSM was configured to only back up changed data (incremental in backup terms) and we were backing up over a TB of data every day. In 2002, that was a lot of data and it cost us literally hundreds of dollars a day in tape costs.

At the time I was working with DBAssociates, the inventors of Litespeed, trying to build a case to use the product on our SQL Servers. It seemed like it would be a good use investment, and I had to write an ROI paper to present to my boss. As I was working on it, it occurred to me that if I moved away from nightly full backups to a weekly full with differentials I might save even more space. I tested the backups, extrapolated out the savings in backup space, and included the data in my report.

At the time we bought nearly $60,000 of Litespeed licenses and paid for them in a little over a year with tape savings. The savings were partially from the compression of data, but also from the move to differential backups instead of full backups each night.

Have a Good Process

Part of the reason that I’ve been able to have success with differentials is that I’ve always ensured we had a good process for not only keeping a full backup on disk, but also ensuring that a good copy was moved offsite each day. This protection from an issue in the office, which I have dealt with, helps to make a differential backup a tool worth using.

Evolving your backup process from a simple “copy to disk or tape” into something that include offsite recovery, differential backups, and good procedures that non-DBAs can follow will save your company money, and at the same time reduce your risk of data loss.

You can even further evolve things with other SQL Server technologies like log shipping, replication, and database mirroring.

I’ve heard imitation is a form of flattery, and I guess that’s true. Today I got an up close and personal look.

I saw this note at Database Journal that they were starting a new daily newsletter with daily news: Database Daily

Some of you might remember that Database Weekly, the other site that I run (with help from Phil Factor, Tony Davis, and a few others at Red Gate software), used to be known as Database Daily. The name changed when we realized that we would likely never put it out daily, but rather stick with weekly. At least someone at Red Gate realized that. I was happy with Database Daily, and often updated the website on a daily basis with links.

When we came up with the idea, Andy, Brian, and I debated names for a few days, and liked Database Daily, so we grabbed it. Our plan had been to start with a weekly newsletter and as we found a decent amount of news, we’d add in more newsletters.

However the load of adding stuff daily was too hard. I managed it by adding links twice a week (Tue and Fri) and that’s still how I do things, though occasionally I see something on other days and just add it.

I’m not sure if we still have the domain, but if we do, I think it’s worth hanging on to. Especially now that someone else things it’s a good idea.

The other day I was trying to help someone that had removed the BUILTIN/Administrators logins from their instance without first setting up another user/group as sysadmin. Actually I wasn’t helping. I was telling them that finding the media would be a good move to prepare for the reinstallation.

Then someone posted a note that if you start SQL Server in single user mode, then you can connect with the administrator account and get sysadmin access. It’s true and there’s a BOL entry about this: Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

I was surprised that this was the case. If you get locked out on your Windows machine, you’re basically done. There are a few password cracker programs, but the reality should be that if you don’t have the administrator password, and you don’t have any administrator groups define, you should not get in. Sucks if it happens, but that’s strong security.

Why is it different with SQL Server? If someone can log on as “administrator” and start SQL Server in single user mode, then they have access to all your data. I know you should trust your admins, but typically we have separation of duties, and the Exchange administrator should not necessarily be poking around in SQL Server.

On top of that, “administrator” is a generic login. Your auditing won’t tell you who connected.

I think this is a problem, and I actually need to write an editorial on this. It’s a hole that I think deserves to be removed.

The other night I was tired, a few long days, early mornings with the kids, and slightly wired, but needing to rest. My wife was alseep, so I blogged a bit, watched a little TV, and then turned out the light about 10:30.

I lay there, and for some reason, about 10:50 or so, I woke up. I was pretty sure I hadn’t scheduled a newsletter. It just occurred to me, so I grabbed the laptop next to the bed, fired it up in the dark, my eyes 2” from the keyboard trying to type my crazy 10+ letter password.

I checked, and sure enough it wasn’t scheduled. I did a quick schedule and test, and then went to bed. Strange that I’d catch it that late at night, but glad I did.

Some days it pays to have the laptop next to the bed. I might not have gotten up if I’d have to go downstairs to check that computer.

After my editorial this week on Coding By the Sea, my boss actually pinged me to say that I could have a week if I wanted. I could work on a project of my choosing for a week that would benefit the company in some way.

At first I thought, very cool! I could take a week off, maybe even disappear from the house for a week and work on something. However reality set in pretty quickly and I wasn’t sure this was the thing for me.

Disappearing from my life for a week is hard. I have a family, busy kid life, and if I were gone for long hours to dedicate to some project it would be a burden on my wife. Even if I came home every night, would that work? Would I get enough done? Then there’s the matter of SQLServerCentral. For me to close down for a week I’d have to spend a lot of time over the next few weeks doing double work to get my pipelines ready for a week of me not working.

I think the first thing I’d have to do is decide on a project. I have a couple book  ideas, but I’m not sure how much I’d get done in a week. Even full-time writing at my pace I’d get something done, but I’m not sure how much progress I’d make on something that should be 150 pages.

I thought about some type of software, but I’m not sure I’m a good enough developer to actually take ideas and make them work. In all likelihood I wouldn’t get out of my own way and spend too much time debugging things.

I could work on some reports, after all Reporting Services is on my list of 2010 goals. However I’m not sure what benefit there is to my company (SQLServerCentral or Red Gate) with that one.

The one thing I did think of was to work on getting 2-3 top-notch presentations done. Things I could share with other people in Red Gate or even outside the company. SQL Server presentations that would incorporate the Red Gate tools into the presentation. I think they are good tools, and there is value in purchasing them. It would be good to find a way to show DBAs how the tools help without making it an advertising commercial. It’s an interesting challenge, and one I’d like to tackle at some point.

I heard back from my boss that Red Gate is actually scheduling a down week for some teams (development groups) where they can try to knock something out in a week. The timing for that doesn’t really work for me, but perhaps I’ll try to get a week sometime this year to work on some personal project.

As long as it’s after ski season, I think I could get a lot done in a week.

I guess you could look at this one of two ways. Either PASS acquired SQL Saturday, or we (Andy, Brian, and I) bequeathed it to PASS. The truth is it’s a bit of both. We were looking to get some help in keeping the SQLSaturday events going, while also freeing up the time required to make them happen.

This was mostly Andy’s baby, from start to finish, with support from Brian and I, so the credit really goes to Andy. He came up with the idea, did most of the work with various people around the country to get the events setup and running, and he attended more than anyone. I think most of the event organizers would agree that Andy’s mentoring was invaluable in getting the SQL Saturday franchises up and running.

This was a non-profit venture, which really didn’t make any money for the SQL Share (formerly End to End Training and JumpstartTV) company. It was a nice marketing move, it was great for the community, and I think a fantastic idea for keeping the SQL Server community vibrant. It was also a little bit of a time sink. That wasn’t great for something that wasn’t generating any revenue in this economy.

This is a perfect venture for PASS to be involved in. It fits their mission, and I’m looking forward to seeing even more events in the future under the PASS/SQL Saturday brand.

I had the privilege of going to 3 or 4 events over the last few years, and I’m looking to try to get to a few more this year. I’ll still be supporting the events, and speaking when I can.

I talked a little recently in a blog about gaps in identity values. They can occur when you delete rows, and the identity process doesn’t “fill in” those gaps or otherwise manage the values to maintain a continuous sequence. Something similar happens with transactions, and it’s a behavior you might not expect.
If you were do to this:
CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
BEGIN TRANSACTION
INSERT dbo.MyTable DEFAULT VALUES
INSERT dbo.MyTable DEFAULT VALUES
ROLLBACK
INSERT dbo.MyTable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
What you get is something you might not expect:


In the script, 5 values were inserted, then a transaction started and 2 more values were inserted. The transaction was rolled back, so there are still 5 values in the table. We add another one and it gets an 8, not a 6.

Why not?

It’s a valid question, and it occurs for this reason. In the code above, the transaction starts and the next row inserts a new value (6) into the table. The identity process now increments it’s “next row” to 7. The next statement runs and it adds a new value (7) and the “next” counter is set to 8. When the rollback occurs, the counter is not changed and so the last INSERT adds a row with a value of 8.
This seems silly at first until you think about concurrency. Imagine that we run the code above to this point:
BEGIN TRANSACTION
INSERT dbo.MyTable DEFAULT VALUES
Now another user manages to insert a row into the table at this split second. They would actually insert a row with a value of 7, and the second INSERT in this transaction would insert an 8 for it’s value. If the rollback changed the counter, how do we then account for the row with a value of “7” that’s in the table? Inserts from another session are not a part of this transaction.
Resetting the counter could have drastic implications in a busy environment with lots of connections. It doesn’t make sense, and so this is why a transaction rollback doesn’t reset the identity value. You can try it yourself by running part of this code, then inserting a value in another session before the rollback.
Once again, the identity property for a table doesn’t manage sequence or gaps, and it is not a part of a transaction rollback. Don’t depend on specific values being set for the identity column in your tables.

One of the reasons that I did not vote for President Obama was his stance on our energy issues, specifically the nuclear power issue. He had listed in his positions that he did not support more nuclear power plants, which I think is a mistake. It’s not a litmus test, but one of the many items I think is important.

However he recently asked Congress to pass a bill that included a call for more “clean, nuclear power plants. I think that’s great, and it’s something we need to do.

Having worked in a plant, I appreciate the dangers and issues of waste. However we can, and have, stored waste off for some time at various plants. In addition, I tend to favor above ground, protected, but monitored storage as opposed to putting it under ground and forgetting about it.

I don’t think we should go completely nuclear, but I think that having more plants, maybe even 1 per state or more, would drastically change the amount of fossil fuels we burn.

And support a few more computers :)

I had meant to check what was up with my Toshiba laptop since I was curious. I gave up last week and ordered a Lenovo after they hadn’t updated the status on my machine. But I was wondering if it was coming back, more as curiosity as to what is going on with their service.

I shipped it back last Monday, that’s now 6 business days, and they should have had it for 3. The status on my case number is “closed” and there are no notes. I would call, but I’m busy, and this blog post is about all it’s worth.

Coincidentally, I got a shipment notice today for my Lenovo laptop. I’ll have this one well before the Toshiba is fixed.

I saw a blog post from my friend Paul Randal recently in which he recounted the books he’d read in 2009. It’s an impressive list, 100 books, that he compiled in his travels. Paul and his wife, Kimberly, travel a lot, and I think if I were in a similar situation, I’d read 100 books a year as well. However it got me wondering how many I’d read. I’ve tracked them in the past, but since I moved my blog and started using the Kindle and iPhone, I haven’t been doing it. My totals in the past were:

• 2008 - 47
• 2007 - 65
• 2006 - 68
• 2005 - 112
• 2004 - 59
• 2003 - 42
• 2002 - 47
• 2001 - 43
• 2000 - 49
• 1999 – 49

2008 is also wrong as I got the Kindle in September and started to read on there, forgetting to track books since I was busy and reading quicker on there. I went to my Amazon Kindle account and noticed a few books that are missing, so I’ll need to add those back.
However.
This post is about 2009, so in terms of looking over 2009, here’s what I have:

• 54 ebooks from Amazon
• 6 ebooks from Barnes and Noble
• a total of 68 books
• of the 68, 19 were re-reads
• 1 classic from the Gutenberg project. I started a second (War and Peace) that has not been completed.

I don’t have the breakdown of books, though they’re mostly fiction. Of the books I read, I think that a few stood out.
The Count of Monte Cristo – I grabbed this, along with a few other Gutenberg project books when I first got the Kindle. I struggled with it, and I have to say the first 100 pages barely held my interest. However I plugged away, and eventually really enjoyed this book. The story is amazing and I was surprised how much was happening in there. I ended up really enjoying it.
The Snowball – I noticed this at the 2007 PASS Summit when Andy Warren and I were walking around the bookstore. He needed books for the ride home, and since I had the kindle I was just keeping him company. I actually made a note and bought this on my way home, starting it then, but finishing it in 2009. I’ve read a lot about Warren Buffet over the years, but this was an amazing look at the guy. Made me realize that not only am I nothing like him, I wouldn’t want to trade places at all.
Grift Sense – Not sure why I picked this up, but it captivated my interest and I read all the books by James Swain. It deals with cons and casinos, and I loved it. Looking for a similar series from someone else.
It's Your Ship: Management Techniques from the Best Damn Ship in the Navy, USS Benfold – This caught my eye in a bookstore and I decided to try it. It’s fantastic, and it shows how someone can really transform a group with good leadership.
The Clone Republic – I got back into Science Fiction in 2007 with Old Man’s War, which I think is one of the best books I’ve read. This series, which I started on an Amazon recommendation was great as well. Not an all time favorite, but really enjoyable.
The full list, as best as I can determine, is:

1. Hear No Evil, James Grippando
2. Nine Dragons, Michael Connelly
3. The Night Monster: A Novel of Suspense, James Swain
4. The Merchant of Death, D.J. MacHale
5. Pursuit of Honor: A Novel, Vince Flynn
6. Long Lost, Harlan Coben
7. The Lost Symbol, Dan Brown
8. Sucker Bet, James Swain
9. Midnight Rambler: A Novel of Suspense, James Swain
10. Deadman's Bluff: A Novel, James Swain
11. The Night Stalker: A Novel, James Swain
12. Dark Watch, Jack Du Brul
13. Skeleton Coast, Jack Du Brul
14. The Doomsday Key: A Novel, James Rollins
15. First Family, David Baldacci
16. A Plague of Secrets, John Lescroart
17. Killer Summer (Walt Fleming), Ridley Pearson
18. Havoc, Jack Du Brul
19. Finger Lickin' Fifteen (Stephanie Plum Novels), Janet Evanovich
20. The Scarecrow, Michael Connelly
21. Bad Luck and Trouble (Jack Reacher, No. 11), Lee Child
22. Gone Tomorrow: A Reacher Novel, Lee Child
23. Cemetery Dance, Douglas Preston and Lincoln Child
24. Dance of Death, Douglas Preston and Lincoln Child
25. Wheel of Darkness, Douglas Preston and Lincoln Child
26. Wicked Prey, John Sandford
27. Private Wars, Greg Rucka
28. Walking Dead: A Novel of Suspense, Greg Rucka
29. Patriot Acts, Greg Rucka
30. Critical Space, Greg Rucka
31. The Lost Fleet: Relentless, Jack Campbell
32. Valiant, Jack Campbell
33. Courageous, Jack Campbell
34. Fearless, Jack Campbell
35. Dauntless,  Jack Campbell
36. Team of Rivals: The Political Genius of Abraham Lincoln, Doris Kearns Goodwin
37. To the Nines (Stephanie Plum, No. 9), Janet Evanovich
38. Ten Big Ones (Stephanie Plum, No. 10), Janet Evanovich
39. Helfort's War Book 2: The Battle of the Hammer Worlds, Graham Sharp Paul
40. The Da Vinci Code: A Novel, Dan Brown
41. The Clone Alliance, Steven L. Kent
42. Rogue Clone, Steven L. Kent
43. Helfort's War Book 1: The Battle at the Moons of Hell, Graham Sharp Paul
44. Hard Eight (Stephanie Plum, No. 8), Janet Evanovich
45. Seven Up (Stephanie Plum, No. 7), Janet Evanovich
46. Hot Six (Stephanie Plum, No. 6), Janet Evanovich
47. High Five (Stephanie Plum, No. 5), Janet Evanovich
48. Four to Score (Stephanie Plum, No. 4), Janet Evanovich
49. Three Plums In One, Janet Evanovich (three books, #s 1, 2, and 3 of this series)
50. Fleet of Worlds, Edward M. Lerner
51. The Clone Republic, Steven L. Kent
52. Boys Will Be Boys, Jeff Pearlman
53. The Billionaire Who Wasn't: How Chuck Feeney Made and Gave Away a Fortune Without Anyone Knowing, Conor O'Clery
54. Along Came a Spider, James Patterson
55. Atomic Lobster, Tim Dorsey
56. The Bodies Left Behind: A Novel, Jeffery Deaver
57. Teddy Roosevelt, Edd Winfield Parks
58. The Clone Elite, Steven L. Kent
59. It's Your Ship: Management Techniques from the Best Damn Ship in the Navy, USS Benfold, Captain D Abrashoff
60. The Snowball: Warren Buffett and the Business of Life, Alice Schroeder
61. Grift Sense (Tony Valentine Series #1), James Swain
62. Funny Money (Tony Valentine Series #2), James Swain
63. Deadman's Poker (Tony Valentine Series #6), James Swain
64. Sacred Stone (Oregon Files Series #2), Clive Cussler
65. Mr. Lucky (Tony Valentine Series #5), James Swain
66. The Count of Monte Cristo, Alexander Dumas

I started out 2010 with a vacation after New Years and finished 2 books I’d started in 2009 early, plus re-read 4 more since I didn’t have an Internet connection and it was convenient. As of Jan 25, I’ve got 16 books done, but that’s with a week’s vacation.
It will be interesting to see where 2010 takes me. I’ve started rewriting book reviews on my blog, so I’ll be able to track things.

I’m not sure how this book got on my radar, but it turned out to be fairly interesting. It’s written by a professor at Harvard that uses Game Theory and readily available information about the world to both predict what will happen in a general sense, and to guide his consulting clients to make decisions based on those predictions.
It’s an interesting idea, kind of unbelievable, and smacking of the way predictions are made in Foundation. However as I read, and learned about both big predictions on the world stage, as well as small ones in a lawsuit, it seems that there is possibly some value and truth here.
The book talks about using insights into how people behave, along with good guesses about their beliefs, to simulate how they behave in rounds of negotiation. By simulating their interactions, and even randomly shocking the system, Prof. de Mesquita seems to be able to do a good job of predicting outcomes. Not exact dates or times, but general events, like if I offer to do X instead of holding out for Y in a lawsuit, what is likely to happen from the other side. Or how can North Korea be convinced to shelve nuclear armament plans? How can Israel and Palestine find peace?
It tends to delve into the bigger world stage issues, but it’s also very current, having been completed in the summer of 2009. If you are curious about how BI could help, and how game theory could be used, it’s worth a read. No technical details, but enough information to pique your interest.

In the end, that was what made the difference. I was typing along one night on my spare Acer and the cursor kept jumping. I was getting too close to the touchpad, and it kept picking up my hands, jumping the cursor. I type rapidly, and it kept causing me to get text in different places in the document, which is really frustrating. It slows me down while raising my blood pressure.

So in looking at laptops, that got me thinking that I want a 15” machine again, not a smaller one. Having more space for my hands to work on the keyboard was important. But I remember having issues at times with the Toshiba, especially as I work in bed quite often.

I was debating between the Lenovo and the Dell, spending more time than I needed considering things, but then I noticed the little sick in the middle of the Thinkpad keyboard.

I had a Thinkpad years ago when it was IBM, and I really liked the trackpoint pointer. I know lots of people didn’t, but I found it handy. So I was sure that a Lenovo was the way to go. I could use the Trackpoint and turn off the touchpad. That will help me be much more productive.

Then my wife said she had a trackpoint on her Dell. Sure enough, she does, though she’d lost the rubber cover and never used it. I checked for other machines, and there are a few that have them, but not many. So I chatted with a Dell rep and found that they have a few Lattitude models with them. Unfortunately those seem to be older technology, with DDR2 memory and limited options. I’m not an extreme performance person, but I want to be sure that I can process video and I didn’t want to go with older technology if I’m spending $1500.

So I decided on a Thinkpad W510. It’s got the trackpoint and plenty of power, and the same price as the Latitude or the Thinkpad T510.

Because of the little trackpoint.

Identity values are very useful, but there can be misconceptions about the way in which they work. One of the more interesting things that I see people assuming is that an identity value will ensure you have a continuous range of values for that column. They assume that this code:
CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
DROP TABLE dbo.MyTable
It will, and it will get give you a list of 5 rows, with continuous values. Assuming that this pattern continues in your application, you will have a continuous list of values. What about this code?
CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
GO
UPDATE mytable SET id = 12
WHERE id = 3
go
SELECT * FROM mytable
GO
DROP TABLE dbo.MyTable
You’ll actually get an error, since you can’t specify identity values directly, but you can do this:
CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
GO
DELETE mytable WHERE id = 3
GO
SELECT * FROM mytable
GO
That will give me these results:

If I now do this:
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
I get

My next value doesn’t fill in the gaps.
An identity value is strictly a “what’s the next number” process. It does nothing to manage the existing values, and it does nothing about filling in gaps. That’s important to know, and to be aware of when designing your database around identity values.

I finally ordered a new laptop, a Lenovo W510, that caught my eye. I was thinking the T series 510, but once I added a few things it was the same price as the W510, but had 2 fewer cores. Since I do a decent amount of video stuff, the extra cores should come in handy.

No word yet on my Toshiba, and I don’t even know what to do with that. It’s been nearly two months, and I’ve missed some things at work without having a machine I can use while traveling and away from my desk.