Month: November 2012

DBA Best Practices – A Blog Series: Episode 2 – Password Lists

 

Digital World, Digital Locks

One of the biggest digital assets that any company has is its secrets. These include passwords, key rings, certificates, and any other digital asset used to protect another asset from tampering or unauthorized access.

As a DBA, you are very likely to manage some of these assets for your company – and your employer trusts you with keeping them safe. Probably one of the most important of these assets are passwords. As you well know, the can be used anywhere: for service accounts, credentials, proxies, linked servers, DTS/SSIS packages, symmetrical keys, private keys, etc., etc.

Have you given some thought to what you’re doing to keep these passwords safe? Are you backing them up somewhere? Who else besides you can access them?

Good-Ol’ Post-It Notes Under Your Keyboard

If you have a password-protected Excel sheet for your passwords, I have bad news for you: Excel’s level of encryption is good for your grandma’s budget spreadsheet, not for a list of enterprise passwords.

I will try to summarize the main point of this best practice in one sentence: You should keep your passwords on an encrypted, access and version-controlled, backed-up, well-known shared location that every DBA on your team is aware of, and maintain copies of this password "database" on your DBA’s workstations.

Now I have to break down that statement to you:

– Encrypted: what’s the point of saving your passwords on a file that any Windows admin with enough privileges can read?

– Access controlled: This one is pretty much self-explanatory.

– Version controlled: Passwords change (and I’m really hoping you do change them) and version control would allow you to track what a previous password was if the utility you’ve chosen doesn’t handle that for you.

– Backed-up: You want a safe copy of the password list to be kept offline, preferably in long term storage, with relative ease of restoring.

– Well-known shared location: This is critical for teams: what good is a password list if only one person in the team knows where it is?

I have seen multiple examples of this that work well. They all start with an encrypted database. Certainly you could leverage SQL Server’s native encryption solutions like cell encryption for this. I have found such implementations to be impractical, for the most part.

Enter The World Of Utilities

There are a myriad of open source/free software solutions to help you here. One of my favorites is KeePass, which creates encrypted files that can be saved to a network share, Sharepoint, etc. KeePass has UIs for most operating systems, including Windows, MacOS, iOS, Android and Windows Phone.

Other solutions I’ve used before worth mentioning include PasswordSafe and 1Password, with the latter one being a paid solution – but wildly popular in mobile devices.

There are, of course, even more "enterprise-level" solutions available from 3rd party vendors. The truth is that most of the customers that I work with don’t need that level of protection of their digital assets, and something like a KeePass database on Sharepoint suits them very well.

What are you doing to safeguard your passwords? Leave a comment below, and join the discussion!

Cheers,

-Argenis

DBA Best Practices – A Blog Series: Episode 1 – Backups

 

This blog post is part of the DBA Best Practices series, on which various topics of concern for daily database operations are discussed. Your feedback and comments are very much welcome, so please drop by the comments section and be sure to leave your thoughts on the subject.

Morning Coffee

When I was a DBA, the first thing I did when I sat down at my desk at work was checking that all backups had completed successfully. It really was more of a ritual, since I had a dual system in place to check for backup completion: 1) the scheduled agent jobs to back up the databases were set to alert the NOC in failure, and 2) I had a script run from a central server every so often to check for any backup failures.

Why the redundancy, you might ask. Well, for one I was once bitten by the fact that database mail doesn’t work 100% of the time. Potential causes for failure include issues on the SMTP box that relays your server email, firewall problems, DNS issues, etc. And so to be sure that my backups completed fine, I needed to rely on a mechanism other than having the servers do the taking – I needed to interrogate the servers and ask each one if an issue had occurred. This is why I had a script run every so often.

Some of you might have monitoring tools in place like Microsoft System Center Operations Manager (SCOM) or similar 3rd party products that would track all these things for you. But at that moment, we had no resort but to write our own Powershell scripts to do it.

Now it goes without saying that if you don’t have backups in place, you might as well find another career. Your most sacred job as a DBA is to protect the data from a disaster, and only properly safeguarded backups can offer you peace of mind here.

“But, we have a cluster…we don’t need backups”

Sadly I’ve heard this line more than I would have liked to. You need to understand that a cluster is comprised of shared storage, and that is precisely your single point of failure. A cluster will protect you from an issue at the Operating System level, and also under an outage of any SQL-related service or dependent devices. But it will most definitely NOT protect you against corruption, nor will it protect you against somebody deleting data from a table – accidentally or otherwise.

Backup, fine. How often do I take a backup?

The answer to this is something you will hear frequently when working with databases: it depends.

What does it depend on? For one, you need to understand how much data your business is willing to lose. This is what’s called Recovery Point Objective, or RPO. If you don’t know how much data your business is willing to lose, you need to have an honest and realistic conversation about data loss expectations with your customers, internal or external. From my experience, their first answer to the question “how much data loss can you withstand?” will be “zero”. In that case, you will need to explain how zero data loss is very difficult and very costly to achieve, even in today’s computing environments.

Do you want to go ahead and take full backups of all your databases every hour, or even every day? Probably not, because of the impact that taking a full backup can have on a system. That’s what differential and transaction log backups are for.

Have I answered the question of how often to take a backup? No, and I did that on purpose. You need to think about how much time you have to recover from any event that requires you to restore your databases. This is what’s called Recovery Time Objective. Again, if you go ask your customer how long of an outage they can withstand, at first you will get a completely unrealistic number – and that will be your starting point for discussing a solution that is cost effective.

The point that I’m trying to get across is that you need to have a plan. This plan needs to be practiced, and tested. Like a football playbook, you need to rehearse the moves you’ll perform when the time comes. How often is up to you, and the objective is that you feel better about yourself and the steps you need to follow when emergency strikes.

A backup is nothing more than an untested restore

Backups are files. Files are prone to corruption. Put those two together and realize how you feel about those backups sitting on that network drive. When was the last time you restored any of those?

Restoring your backups on another box – that, by the way, doesn’t have to match the specs of your production server – will give you two things: 1) peace of mind, because now you know that your backups are good and 2) a place to offload your consistency checks with DBCC CHECKDB or any of the other DBCC commands like CHECKTABLE or CHECKCATALOG. This is a great strategy for VLDBs that cannot withstand the additional load created by the consistency checks.

If you choose to offload your consistency checks to another server though, be sure to run DBCC CHECKDB WITH PHYSICALONLY on the production server, and if you’re using SQL Server 2008 R2 SP1 CU4 and above, be sure to enable traceflags 2562 and/or 2549, which will speed up the PHYSICALONLY checks further – you can read more about this enhancement here.

Back to the “How Often” question for a second. If you have the disk, and the network latency, and the system resources to do so, why not backup the transaction log often? As in, every 5 minutes, or even less than that? There’s not much downside to doing it, as you will have to clear the log with a backup sooner than later, lest you risk running out space on your tlog, or even your drive. The one drawback to this approach is that you will have more files to deal with at restore time, and processing each file will add a bit of extra time to the entire process. But it might be worth that time knowing that you minimized the amount of data lost. Again, test your plan to make sure that it matches your particular needs.

Where to back up to? Network share? Locally? SAN volume?

This is another topic where everybody has a favorite choice. So, I’ll stick to mentioning what I like to do and what I consider to be the best practice in this regard. I like to backup to a SAN volume, i.e., a drive that actually lives in the SAN, and can be easily attached to another server in a pinch, saving you valuable time – you wouldn’t need to restore files on the network (slow) or pull out drives out a dead server (been there, done that, it’s also slow!).

The key is to have a copy of those backup files made quickly, and, if at all possible, to a remote target on a different datacenter – or even the cloud. There are plenty of solutions out there that can help you put such a solution together. That right there is the first step towards a practical Disaster Recovery plan. But there’s much more to DR, and that’s material for a different blog post in this series.

T-SQL Tuesday #36 (#tsql2sday)– Post-PASS Summit Depression

I had an email thread going with a prominent member of the SQL Server community today, where he confessed that he didn’t attend any sessions during the PASS Summit last week. He spent all of this time networking and catching up with people.

I, personally, can relate.

This year’s Summit was another incarnation of that ritual of SQL Server professionals meeting to share their knowledge, experience, and just have a wonderful time while doing so.

It’s been a few days after the Summit is over, and I’m definitely dealing with withdrawal.

My name is Argenis, and I’m a #SQLFamilyHolic.

 

 

 

 

(This post is part of the T-SQL Tuesday series, a monthly series of blog posts from members of the SQL Server community – this month, Chris Yates is hosting)

DBA Best Practices: A Blog Series

 

Introduction

After the success of the “Demystifying DBA Best Practices” Pre-Conference that my good friend Robert Davis, a.k.a. SQLSoldier [Blog|Twitter] and I delivered at multiple events, including the PASS Summit 2012, I have decided to blog about some of the topics discussed at the Pre-Con. My thanks go to Robert for agreeing to share this content with the larger SQL Server community.

This will be a rather lengthy blog series – and as in the Pre-Con, I expect a lot of interaction and feedback. Make sure you throw in your two cents in the comments section of every blog post.

First topic that I’ll be discussing in this blog series: The thing of utmost importance for any Database Administrator: the data. Let’s discuss the importance of backups and a solid restore strategy.

Care to share your thoughts on this subject in the comments section below?