Wednesday, January 20, 2010

A Word About Backups At Home

I just posted this on a Subaru message forum (off topic section) because people were discussing backups and I couldn't deal with all the bad info. I thought I'd repost it here for posterity. Note that this is STRICTLY for home setups and has nothing to do with SQL backups and what you should be doing at work! ;)

1. - Know Your Limits
If you barely manage with Windows and have an ongoing problem with spyware/malware/viruses stop reading now. Go buy something from DLINK or Linksys, or whoever. You do not have the skills to build and maintain anything more robust and you'll need support. This will cost you a lot more money and you'll get less flexibility, but it'll be easy to use.
If you are an average to above average Windows or Linux user you can probably risk trying out some of the stuff I'll describe below.
If you're an expert Windows or Linux user you should already know this stuff.
If you're a mac user go overpay for something stylish then tell us all how much better it is and you are. Or suck it up and follow along below

2. - Identify Your Goals
You can waste plenty of time and money if you don't know what you NEED to do. You may WANT to backup everything you have in real time, but do you NEED to?
How much data can you afford to lose? How often is the data changing?
For example a normal computer user probably adds a few bookmarks, downloads a few shows, songs, imports some pictures from their camera, etc. each day or so. Maybe a weekly backup of a few directories/files would fit your needs. Maybe you need something to keep entire volumes in sync. Think about it, make a decision, then move on.

3. - Know Your Options
Options range from really easy & cheap, to very expensive and complex.
On the low end of the scale you've got USB attached storage. Maybe it's a HD or solid state device. Maybe it's an entry level RAID or JBOD.
In the middle you've got more advanced attached disk setups through eSATA, USB, or even network attached.
On the high end you've got custom built servers that do nothing but share files.
On the extreme high end you've got true NAS appliances from vendors like NetApp. If you know what these are you shouldn't be reading this!

4. - Know What Causes Problems
People. Hardware failures. Software glitches.
Most often at home you lose data because a hard drive dies, a CD or DVD degrades, or something else happens like your DLINK media server mainboard fries.
How do you prevent these things? REDUNDANCY!!!
That's it. Multiple copies of things in different places is the only way to guarantee it survives. I laugh at the people who throw a couple disks in a box and mirror them (RAID 1), then go on to brag about how they'll never, ever lose data. What happens when the RAID controller dies a few years down the road? What happens when you get a virus that overwrites all the data? What happens when they stop making the identical drive you need so the el-cheapo RAID controller will agree to rebuild your array? Or what about when little Johnny decides to try installing a new Linux partition over your Windows 7 setup? Yep; total data loss.
If you're doing something as simple as say once a week copying or auto-syncing say your "My Documents" and "My Pictures" folders (sorry Linux folks) to a USB drive you've just prevented total data loss for about $100 in hardware. Congrats!
That being said... if you CAN accept that you *might* lose all your data if X,Y,Z unlikely things happen then a simple RAID 1 setup could be all you need.

5. - Identify What You Want To Backup
Backing up everything is an option, but do you need to do it? Probably not. Identify all the things you can't lose and ensure they're safe. For me this is things like documents, movies, music, pictures, and code.

6. Pros & Cons
Everything you coudl possibly choose has pros and cons... I'll try to go over the most popular.
a) CDs & DVDs
-pro- cheap
-con- don't hold much data
-con- very labour intensive to use
-con- don't last too long
b)Hardware RAID 1/ RAID 10 (1+0, stripe + mirror, etc)
-pro- pretty cheap
-pro- most motherboards have this now
-pro- braindead easy to get disk level redundancy
-pro- disks last longer than CD/DVDs, but not as long as solid state devices.. solid state is still very expensive
-con- no redundancy of RAID controller (typically)
-con- typically need identical disks
-con- can lose all data to stupid mistake (blow away partition) or virus/corruption
-con- always possible driver issues
-con- might not be able to move RAID disks to new HW and have them recongized
c)Software RAID
-pro- probably pretty cheap or free with OS
-pro- very easy to use
-con- can lose all data to stupid mistake (blow away partition) or virus/corruption
-con- may not be portable/easy to upgrade
-con- performance hit
d)USB devices (single disk)
-pro- pretty cheap
-pro- very portable
-pro- no lock in to hardware, OS, manufacturer
-con- may need software to automate backups
-con- can fail pretty randomly
-con- performance is limited
d)Vendor Device (DLINK, Linksys, etc.)
-pro- very easy to use
-pro- often has HW RAID and funky GUIs, maybe even backup software
-con- often very expensive for what you get
-con- locked into the vendor
-con- when the HW fails it's unlikely you can fix it
-con- BUGS!! & hassle of firmware updates
-con- proprietary disk RAID setups
-con- often limit the size of disks you can use
-con- software included can sometimes be utter garbage
-con- may not work with all OSs
e)FreeNAS/Linux/Windows Home Server
-pro- Very robust, very flexible solution
-pro- Easy to work with (depending on skill level)
-pro- Commodity hardware can be used, purchased, upgraded
-con- Can become expensive
-con- Need decent computer skills
-con- Uses more space & power than other setups
-con- May not include anything to help you automate backups
-con- Need to stay on top of patching

Once you work through all of this you should have a good idea of what you need, what you need to backup, and hopefully have an idea of how you can back it up.
To help out a bit more I'll detail my setup...
I have two laptops, two PCs, a BlackBerry, and a wired home network + wireless access to it. I run Windows Xp, Windows 7, and Ubuntu. I want all my devices to be able to access music, pictures, and I want each device to have certain things like documents backed up. Securty of all this is a totally different matter...
My largest risk exposure is to fire. In the event of a fire I would lose everything because I'm not moving my data off site... it's too damn expensive and I don't trust online providers enough. At some point I'll look at getting a fireproof safe and then backing things up to external storage on a regular basis and putting the backups in there.
Aaaaaanyways...
My backup solution is an older P4 box with a few TB drives in it running FreeNAS. I use software RAID to protect against disk failure and RAID hardware failure and the performance is more than good enough. FreeNAS itself runs off a free 128MB USB stick I got and I've got another copy of it in case anything happens to it. If the computer did die I could easily slap the drives into any other FreeNAS box or even a linux box that reads UFS formatted disks and I'd be able to recover my data. That's really good.
On some PCs I have specific folders set to stay in constant sync with FreeNAS, while others do it on a schedule. Everything is enabled to "work offline" if FreeNAS wasn't available meaning I've got at least two distinct copies of my data. Total cost if you were to build this thing new is about $300-$600+ depending on the number and costs of disks you put in it. Go as cheap as you can on CPU.

So there y'all go. One REALLY long post about backups. You should be good-to-go now.

Cheers!

Thursday, January 7, 2010

SQL Server Login Error Review & Fix for "Error: 18456, Severity: 14, State: 11."

If you've got a SQL Server machine on a domain chances are you've seen error 18456 enough times to have seen the following:
  • Severity is always 14
  • State 8 is a bad password (password submitted doesn't match password stored in master)
  • State 9 is an invalid password (doesn't meet requirements)
  • States 2 & 5 are invalid usernames
  • State 40 means the logins default database isn't accessible to that login
  • State 11 means the login is valid, but something else went wrong
Now, state 11 is fun. You'll get this error and then you'll probably get a call or email from someone saying they should have access but it isn't working. Often in the logs you'll see something like this:


Login failed for user 'DOMAIN\SAMAccountName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a pretty good indicator that the users login credentials are out of date which is usually a result of a laptop user trying to access the resource with cached credentials after changing their password elsewhere.
No matter what there are two simple fixes for this that have always worked for me.
  1. Have the user lock their computer and login again.
  2. Have the user log off and logon again.
That's it! Those two steps will ensure that NTLM and/or Kerberos tickets/tokens are refreshed by talking to a current domain controller and the problem should go away.

NOTE: If you're seeing errors about SSPI context there is a possibility that your SQL Server service account is invalid (password changed, locked out, disabled, etc.), or your domain (or specific DC) is having issues.

Cheers!