Friday, July 31, 2009

Things You Need To Know If You Use DFS Replication

In addition to being a SQL DBA I'm also a network administrator, or at least I pretend to be. This is a bit off-topic from my usual SQL Server fare, but I figured my pain is your gain. Today, July 31, is System Administrator Appreciation Day. If you use DFS Replication in your company, give your sysadmin a gift by forwarding this on to him\her.

Background - What Is DFS Anyways?
DFS, or Distributed File System, is a feature built into Windows Server that allows you to organize multiple SMB shares into a single DFS share. Accessing the DFS share automatically redirects (under the covers) to one of the SMB shares that are part of the group. It's a neat bit of technology that provides location transparency and redundancy simultaneously. Another part of DFS is the ability to replicate changes made to a file (or files) in one of the SMB shares to all of the other SMB shares in the group. Prior to Windows Server 2003 R2 this feature was called File Replication Service and, while it worked, it was less than efficient because any change to a file resulted in the entire file being copied (imagine making a 1 line change to a 100 MB file). Beginning with Server 2003 R2 it was renamed to DFS Replication. Along with it came some nice improvements which included scheduling, bandwidth throttling, and most importantly Remote Differential Compression which detects and replicates only the part of the file which changed and not the entire file.

DFS (and DFS replication) is really nifty when it works. My company uses it to keep content synchronized on servers across multiple datacenters. However, there are a few caveats that you need to be aware of. I'll call them lessons learned, and unfortunately I had to learn them the hard way. I'm going to share them with you in the hopes that you won't have to go through the same pain that I did.

Lesson #1 – Be Very Careful When Removing And Re-Adding Members To A DFS Replication Group
Let's pretend you've got DFS Replication set up and you want to add a server (referred to as a member) into your group. You start with a blank target directory on the member and a short while after adding it to the group it gets populated with the files & folders from the other members in the group. At some point in the future you need to remove that member temporarily and then re-add it. Thinking you need to start with a blank target directory like the first time you wipe the directory clean and then add the member back to the group. A short time later you start to see the opposite of what you expect – instead of the member receiving the files & folders from the other members they start disappearing from every member in the group. What the heck happened?!?

It turns out that when you delete a member from a DFS replication group information about the member isn't actually deleted from the DFS replication database. Instead, the member is marked with a 30-day tombstone flag. If the member is added back into the DFS replication group the flag is deleted and the original objects for the member are reused. Any changes made to the recently re-added member are then replicated to the other members. So deleting those files from the member before re-adding it? They get picked up and replicated to the other members.

This "feature" and 3 workarounds are documented in Microsoft KB article 961655. Do yourself a favor and read it.

Lesson #2 – Back Up Your DFS Shares
Lesson #1 leads to lesson #2, which should a no brainer – Back up your DFS shares. DFS Replication will replicate all changes to files, including deletes. Would you like to explain to management how you just lost all your files permanently because someone mistakenly deleted all of them and you weren't taking backups? I wouldn't.

Lesson #3 – Recover Deleted Files In A Pinch
Suppose you didn't learn from lesson #2, files got deleted by mistake, and you don't have backups. All hope is not lost. It turns out that DFS Replication keeps a hidden, private folder which contains a copy of the deleted files. It's limited in size so it's not foolproof but it just might save you in a pinch.

Ned Pyle, a Technical Lead for the Directory Services team at Microsoft, posted a handy VB Script that you can use to restore data if you're in disaster recovery mode. I've used it and it saved my butt. By the way, remember lesson #2 about backing up your DFS shares? (hint, hint)

Lesson #4 – Files With The Temporary Attribute Won't Replicate
Filters can be applied to exclude files from replicating based on their extension (e.g. .BAK), but what about when a non-excluded file just won't seem to replicate? It might have the temporary attribute set. DFS Replication won't pick up changes to those files. You wouldn't know that unless you found the single line mentioning it in this TechNet article (see if you can find the line!) or came across this post on the Microsoft Storage Team's blog.

How do you fix that? One way is to use Robocopy to strip the temporary attribute off the file(s) when copying into the DFS share. The switch is: /A-:T

Lesson #5 – Monitor DFS Replication Performance
One big downside to DFS Replication is that unlike other Microsoft products there's no shiny GUI to monitor DFS replication performance. That doesn't mean it can't be done – it just requires a little extra work. There's a command line executable included with DFS called dfsradmin that will create an HTML report showing DFS Replication's health status. There's a nice writeup here and here on how to automate DFS replication health reports. I highly recommend that you take the time to read it and implement your own automated reports.

I hope that my lessons learned the hard way will save you some of the pain that I had to go through. Despite the hiccups that I've had with DFS I remain a big fan of using it to keep content synchronized across multiple locations. One last bit of advice – be sure to check out the File Cabinet blog from the Storage Team at Microsoft. It's a fantastic resource for DFS information that's helped me out many times and will no doubt help you too.

Tuesday, July 28, 2009

SQL Logins For Windows Domain Accounts Limited To Pre-Windows 2000 Format

You may have noticed that when you create a login on a SQL server that's mapped to a Windows domain account you have to use the pre-Windows 2000 format [domain\login]. Did you also notice that there's a limitation of 20 characters on the login portion of this format?

Let's pretend that you use nice descriptive names for application accounts, for example:

  • Sales.ReportUtil.ProdService
  • Sales.ReportUtil.ProdWebuser

When you try to add these logins the 20 character limit cuts them both off at "Sales.ReportUtil.Pro". In other words, without employing a workaround (e.g. create AD groups for these accounts and add logins for the groups) you cannot add both of them to the same SQL Server. There just so happens to be another format, User Principal Name (looks like username@domain), that is not limited to 20 characters…but is not supported by SQL server. Maybe I'm missing something here, but this is 2009 and you can use the UPN format in just about every other product Microsoft makes.

I'm hoping there's not some ugly technical reason why the UPN format can't be used and that it was just a minor oversight on Microsoft's part that it was left out. Assuming the latter, I've submitted a suggestion to Microsoft Connect to correct this. If you're annoyed by this too please visit connect and vote for Connect ID 477636.

Monday, July 27, 2009

Interested In *Free* SQL Training? Attend A SQL Saturday Near You!

Can't make the PASS summit? Or maybe you're going but looking for something whet your appetite between now and November. How about attending one of these SQL Saturday events happening soon?

Why go to a SQL Saturday? How about:

  • FREE training – in additional to local presenters you'll get the chance to see pros like Steve Jones (Baton Rouge), Tim Mitchell (Baton Rouge), Michelle Ufford (East Iowa), and Andy Warren (South Florida). The speakers list isn't finalized for some of these events yet; folks like Brad McGehee and Joe Celko have been at past events so you never know what kind of talent you'll get the chance to learn from.
  • FREE swag like books, shirts, software, and other fun stuff to barter with back at the office (or keep for yourself!)
  • FREE food (depending on the event – some events ask for a minimal donation to cover lunch)
  • An opportunity to build your professional network by meeting other SQL DBAs & developers in your area

These events are also a fantastic opportunity for you to jump into public speaking. SQL Saturday focuses on local speakers and many events are still looking for speakers. SQL Saturday is how I got my start in speaking and helped me gain the experience and confidence necessary to speak at the PASS summit coming this November. Speaking experience is also a great addition to your resume. I encourage you to consider answering the call for speakers if you're interested in advancing your career or even just giving back to the SQL community.

On a personal note, I'm scheduled to present three sessions at South Florida on Aug 8:

We all know times are tough and many companies are cutting back on training budgets or cutting them out altogether. How can you argue with FREE training, swag, and food plus the chance to mix and mingle with other SQL professionals? Sign up for a SQL Saturday in your area today!

Thursday, July 23, 2009

So You Want To Meet People At PASS…But What Do You Say?

Going to the PASS Summit this November? Let's fast forward and pretend it's November 2. You've made it to Seattle and are at the convention center for the welcome reception. While there you spot so-and-so whose blog you read or so-and-so who you follow on Twitter. You'd like to  say something to them so you walk up and introduce yourself. Do you: a) strike up a meaningful conversation and finish by exchanging contact information…or: b) pause awkwardly for a few moments, struggling to figure out what to say next, only to walk away empty handed?

I'd be willing to bet that most people (including me) stand a fair chance of having answered the latter. In this day and age we tend to be pretty good at tweeting, texting, and emailing, but for many holding a conversation can be difficult. Unlike email which gives you time to articulate your thoughts (or delete them before hitting the send button!) on your own terms and time, holding a conversation is an engaging exercise that requires you to be quick on your feet. I've been in many circumstances where I've completely fumbled my way through the conversation, even with people who I know fairly well. It happens to the best of us.

There are a lot of people I know I want to meet at PASS this year and probably just as many more I'll want to talk to once I'm there. What do I say to break the ice? How do I remember their name? How do I keep the conversation interesting? This is PASS, the place for people who work with SQL Server to see and be seen. I'd rather not find myself in the middle of those long awkward pauses so I'm going to do something to help hedge my bets. Right before this year's welcome reception there's a special 2 hour workshop held by Don Gabor called "Networking to Build Business Contacts". I'm going and I think you should too.

$60 gets you into the session plus a signed copy of Don's book How to Start a Conversation and Make Friends. Not sure if it's worth it? Read Andy Warren's 6 part series (Part 1, Part 2, Part 3, Part 4, Part 5, Part 6) chronicling his personal coaching sessions over the phone. I happen to know Andy personally and I've seen him put Don's advice into action. Trust me, $60 is a steal for the kind of ROI that knowing how to hold a good conversation will net you.

The bonus is that not only will I pick up some valuable skills that I hope will last well beyond the conference but I'll get to meet some of the people on my list who are also going to the workshop (e.g. Tom LaRock and Grant Fritchey). If you're going to PASS and you care about building your professional network, I strongly encourage you to sign up too. If you do, make it a point to find me and say hello. Lucky for us we'll both know a few tricks to hold a great conversation.

Wednesday, July 22, 2009

Replication Snapshot Errors Caused By Compatibility Level Setting

I ran across an interesting replication error recently that's worth sharing. It happened while using a distributor running SQL 2008, a publisher running SQL 2005, and the published database set to 2000 (80) compatibility. When adding a new subscription (version and compatibility of subscriber are irrelevant) the snapshot agent failed with the following error (extra details omitted for readability):

Error messages:
Source: Microsoft.SqlServer.Smo

Message: Script failed for Table 'dbo.Template_HeaderFooter'.

Message: Column HeaderFooter_Value in object Template_HeaderFooter contains type NVarCharMax, which is not supported in the target server version, SQL Server 2000.

The distributor was recently upgraded from SQL 2005 where this wasn't a problem. A quick search of Microsoft's KB turned up nothing on the error. After some tinkering I was able to figure out a workaround: change the compatibility level of the published DB to 2005 (90). While this works, it's less than ideal if your DB is already live because you may break code by changing the compatibility level.

Unfortunately I haven't found any other workarounds to the problem so if this is happening to you your best bet is to pick a time when no one is using the DB, change the compatibility level, take your snapshot, then change the compatibility level back. Of course an even better strategy is to work with your development teams to get the DB moved up to 2005 compatibility permanently.

Tuesday, July 14, 2009

SQL Quiz #4 - Leadership

Chris Shaw posts SQL quiz #4, I make an observation on Twitter about who gets tagged, Brent Ozar tags me for complaining being a squeaky wheel, and then I wait 3 months a really long time before posting a reply. Well, better late than never so here goes…

Question: Who has been a great leader in your career and what made them a great leader?

First I want to lay out my criteria for what makes a great leader:

  • A manager who keeps things moving towards a goal or in a particular direction
  • A motivator who keeps everyone enthusiastic about working towards that goal
  • Someone who challenges you to do things that you don't know how to so that you can learn and grow
  • Someone who sees your potential and puts opportunities in front of you
  • Someone who sets an example by walking the walk (the opposite of someone who says "do as I say, not as I do")

That said there are two people who stand out in my mind: Ryan (last name withheld to protect the innocent), a manager I've had in two previous jobs, and Andy Warren (last name not withheld – practically everybody in the SQL community knows who Andy is!).

Ryan was a lead developer at my first job out of college. He was a rock star – he "got" technology and software and he was the go-to guy that everyone sought out when they needed advice or help fixing a problem. On the other hand I had a "me" complex – I thought I was hot stuff so I didn't like Ryan much because I saw him as competition. It didn't take me long to realize that I knew squat and that I needed to get over myself and start learning from the people around me. I'm guessing Ryan noticed my change in attitude because after that he seemed to look out for me as he advanced into management. In particular he sent me for my CCNA certification and eventually put me in charge of the production infrastructure. Ryan later left for another job but sought me out to come work for him again where he put me into a role that helped me grow both technically and professionally. We worked together for a few more years before parting ways (permanently? who knows…), and I will always appreciate the doors that he opened for me. Of all the managers that I've ever worked for he was the only one that met every one of my criteria for a great leader.

Andy Warren
A few years ago I was introduced to Andy Warren by a coworker. Andy was looking for local speakers to give presentations at OPASS and, being the kind of person who's up for new challenges,I volunteered…and completely bombed it. But as bad as I thought it went, Andy still give me constructive feedback on how to do better and asked me if I wanted to try again at the first SQL Saturday in Orlando. I took his advice and did a much better job; In fact, I liked speaking so much that I started volunteering to give more technical presentations whenever the chance presented itself. I also enjoyed reading Andy's blog every day and thought about starting my own. Andy was more than encouraging when I talked with him about it, and that's always been the case with him no matter if I've needed help with something or just a different way of looking at things. I'm fortunate to have become friends with Andy and it's his leadership that helped me become active in the SQL community and lead to being selected as a first time speaker at this year's PASS summit. I hope that one day I have the opportunity to emulate Andy and help someone up and coming in the SQL community the way that he helped me.

I'm not going to tag anybody else since the train left the station on this one a few months ago…and I promise that I'll respond quicker the next time if I still get tagged!

Wednesday, July 1, 2009

The Best Thing I Learned At PASS

Note: I'm writing this as part of the "Best Thing I Learned At PASS" contest. Why? Because if I win it's worth a free registration or hotel stay for the 2009 Summit, and I like free!

Let me start by making a small confession: I've never actually been to the PASS summit in person. So how in the world can I write about what I've learned? Because I've been there vicariously through the blogs and Twitter streams of people who were there last year. Thank goodness, because before the rise of blogging\social networking if you had asked me what happens at the PASS summit I would have said…I honestly don’t know what happens at PASS besides a bunch of technical presentations given by people who write books and magazine articles for a living. My ignorance about what PASS is became clear to me last November as I read about the daily keynotes, the community sessions, and the nighttime parties. I came to realize that PASS is about so much more than just sitting in technical presentations. It’s about learning from people just like you who are doing the same things as you do every day. It’s about uniting with your peers and meeting new people who share a common interest. It’s about escaping the daily workload to reenergize and reinvigorate the passion to do our jobs as best as we possibly can when we return. So even though I haven’t been to PASS in person, I’ve learned one important thing: For the sake of my career, I need to go to PASS. If I can figure that out without actually having been to PASS, just imagine what I’ll learn when I do go this year.