I’m presenting at 10:15 AM Saturday, at SQL Saturday in Boston, on “Building Your First SQL Cluster”. I really love presenting this topic–if you want to learn about high availability and disaster recovery concepts, and how they apply to SQL Server.I will cover clustering concepts and hardware requirements. Additionally, I will go over who in your organization needs to involved in this process, and what infrastructure components you need to plan to have in place.
After this session, you will be ready to try your hand at clustering. I’ll post the slides here when I’m done.
In my earlier post “Adding a New Disk to a SQL Cluster Instance“, I needed to clarify something based on a comment. Any drive that gets added to your cluster that is not a mount point, as in occupies a physical drive letter, needs to be dependent on the SQL Server service. I didn’t think to mention that, because I recommended using mount points.
Thanks to reader Shaun Archer in the West Indies for pointing that out in the comments. I’d offer to send him some rum, but I believe he has a better supply chain than I do.
Recently, we got a new SAN (yeah!!!), unfortunately, our SAN vendor’s migration utilities are priced at such a level, that there is no way we were going to use them. So we had to resort to manually moving the disks. Fortunately in this instance all of the directory names and data files were the same, so there was no need to do anything internal to SQL Server. What happens when you have to make those changes will be part II of this post.
I would feel remiss to not include a statement about discussing your storage layout and performance needs with you Storage Administrator, but that’s outside of the scope of this particular blog post.
Of course, with any all activity like this, the #1 rule is TAKE A BACKUP BEFORE DOING ANY OF THIS! This is a NON-destructive process, but better safe than sorry.
If you don’t need to change any directory names—the process is as simple:
Mount the drives in your cluster (See my post here on how to do that)
Make your SQL Server service offline–I used failover cluster manager to do this
Copy the directories from your source drive to your target drive
Remove the older drives from your cluster service and take them offline (This isn’t a necessity, but I do it to prevent any possible conflict). You can bring these drives back if needed–you are not destroying any data
Select the drive you had chosen as the root drive (S:\) and change the drive letter to match what your SQL Server instance is expecting. The mount points underneath it should be maintained.
Make the SQL Server service dependent on the root disk. In order to do this right click on the SQL Server service name > Dependencies Tab > Add. See screenshot below
Restart your SQL Service–everything should be ready to go.
If anything fails investigate the alert log, for both Windows and SQL Server, and resolve any issues. A common problem would be the dependency or a directory not being copied (generally SQL binary files)
Of course, this is assuming you do not have to change directory names or move any databases. More on that next week.
For some reason this has been hot in my search options of late, and since I’m speaking on “Deploying Your Applications to SQL Azure” there, I needed to figure it out myself. So here’s a brief list of costs for the event.
Registration: $299+$199 for pre-con seminars–so $500
Flight: Orlando is one of the cheapest places one can fly in the US. So go ahead and budget $400, but you can probably get there for two.
Ground Transportation: Shuttles in Orlando seem pricey, so budget $60 round trip for shuttles.
Hotels: The convention hotel is a bit pricey at $159/night, but I found nearby hotels in the $70 range. If you can swing it-get a roommate and share costs. Even estimating 4 nights @$70, say $300.
Meals: Breakfast and lunch are covered by the conference, and if you’re trying to network hard, you can probably find some vendors to invite you to a night time party or a dinner. So you can really save some money there. I’ll include $100 for food, just in case things fall through.
So we’re up to $1400–it’s about half the cost of what going to Seattle for PASS would be, and it’s some really awesome SQL Server training.
I’m in the process of developing a presentation entitled “Building Your First SQL Server Cluster” for some upcoming SQL Saturdays, and some inspiration for a few blog posts have come out of it. This one is short and fairly simple.
Windows, as we all know and love, has us use drive letters as the root of file systems. As there are only 26 letters in our alphabet, combined with the fact that A, B, and C generally aren’t available, leaves us with only 23 or 22 available for a given cluster. While this may seem like a lot, if you are splitting out tempdb, transaction logs and backups, you can chew up a lot of letters in a hurry. If you are using multiple instances in your cluster, the problem is compounded. And remember–the cluster can only see drives that are attached to it–you can’t restore a backup from a UNC path.
So what is the solution to this conundrum? Mount points–mount points allow you to mount multiple physical devices under one drive letters, where they look similar to directories. This does prevent a bit of a monitoring challenge, but that’s best left for another post.
I’ve posted before on how to add a disk to a SQL Server cluster here. Using mount points is really straightforward–assuming you have 3 “disks” (could be SAN luns or Windows volumes (well not in a cluster), designate one of the drives to be the root (the drive letter S:\ in this case).
*Note–it doesn’t really matter which disk you choose to be the root–in my case I select the backup lun, but I discussed this with a couple of others, and no one knows of any impact to system performance.
When you are assigning a drive letter to the next device, stop–select the option to “add to empty NTFS folder”, make sure you have the root drive highlighted (Windows likes to default to C:\) and name your folder (you can easily rename through explorer later).
One note–when you go to add the disks to your cluster (if newly creating), you will only see the root disk as available, the other drives won’t be selectable. This is expected behavior and fine. Good luck and happy clustering.
When I left you last week, we were discussing a recruiter horror story, passed along from a friend in a past job. She had a really awful phone screen with HR, but the hiring manager still wanted to speak with her. The story continues…
The hiring manager schedules a phone screen with me, and we have a really nice conversation, discussing the technologies they are utilizing and how my skill set could fit in there. The company sounded interesting, my skills fit really nicely into their gaps and we decide to take the process further along. A moderate annoyance that happened in between, was the recruiter wanting to do a call with me to discuss interview strategy. That would have been fine, if he had given me any actual intelligence about the interview or people I was interviewing. Instead, he gave me standard boilerplate about how to answer interview questions, and wasted 30 minutes of my time.
A couple of days later, I did another phone screen, this time with another DBA there. That went well, and they decided to bring me in for an actual interview.
This is where the already odd process, got really weird. The company scheduled an interview with me, through the recruiter, and when I asked to see an agenda for the interview, the recruiter said the company doesn’t usually give us that information?? What the hell? How long am I going to be there? The recruiter said just plan to take the afternoon off.
This should have been a giant red flag, but I decided to follow through with it. The other question I had was about the benefits package at the new company–I currently work for a solid company, with an outstanding benefits package, so the benefits package is something I really need to account for when negotiating a final salary.
The recruiter said this company doesn’t give us that information, but you should ask HR when you interview. That was extremely odd, as every time I’ve interviewed with a firm their benefits package is either on their website, or is provided before an interview. Nonetheless, I persevered onward.
At the interview, I met the HR person, whom I asked about benefits—her response was basically that “we have some benefits”. I received no documentation, or anything official—this was starting to get scary. Even after meeting with her, I had no official agenda, she mentioned a couple of people I would be interviewing with, but I didn’t have their names on paper, so I had to try to remember them which was confusing.
Once again, the actual technical part of the interview was interesting, and the managers were nice, and technical. If the company halfway had their stuff together, I’d be all about this job. HR wise this was a total train wreck. At this point, I’m thinking they would have to offer me X+30, a G6, and a Porsche 911, and then I’d consider it.
I talk to the recruiter, express my concerns, and go on have a cocktail with a friend who lived nearby. About three days later, I get an email from recruiter saying he thinks they’re going to make an offer. This was immediately followed by an email from the company’s HR rep, with the top secret benefits document.
Well frankly, their benefits package sucked—I would have been paying like $100/month more than I was currently paying, for inferior benefits. The 401k match was lower, in general it sucked. Another email came shortly thereafter, with the actual offer, which was 10k lower than expected. I called the recruiter and said “no way in hell”. I suggested for 15k more I’d think about it.
The recruiter made a huge deal about this, but I was still within my expected salary range and they wanted me to work for them. Finally, after talking to his manager (am I buying a car, or trying to get a job??) said he wanted me to sleep on it, and think about if I was still ok with that. These recruiter guys saw a nice commission for themselves slowly disappearing.
I called back the next morning and gave them the same number. At this point, I was having real concerns about the company’s general lack of professionalism—if they are treating me as a prospective employee this way, how are they treating their customers? Since the company wasn’t publically traded, there is limited financial information available about them. However, there is a firm called Dun and Bradstreet who for the small price of $100 will supply you with as much information they have on a firm.
Well, 30 seconds later, I got an email that looked like this:
The company appeared to have some pretty major debt issues. Not quite the growing firm they had described. At this point, I forwarded the Dun and Bradstreet report to the recruiter, and resigned myself from consideration for the job. I said I’d gladly do some contract design and tuning work for them, but there was no way I’d work full time.
This is where the recruiters get really sleazy—they knew they were really close to a payday. They start calling my cell phone repeatedly—then I get an email, saying “we (the recruiting firm) talked to their HR, and that D&B report isn’t really accurate, and the company is very stable”. I didn’t even bother to write back at this point, but if I had, my response would have been—
“Do you honestly expect me to trust their HR department, who couldn’t even get me an agenda for an interview, to promise the financial viability of the firm? If you can get me three years of independently audited financial statements, and get them to raise the salary another 10k, then we might be able to talk. Until then, stop calling me”