Automating TempDB Configuration in Azure

One of the unique things about managing SQL Server on Azure VMs is that we use the local D: drive for TempDB. The D: drive (or /dev/sdb1 for those of you running on Linux) is a direct-attached solid state drive (on nearly all VM tiers) which offers the lowest latency storage in Azure, making it ideal for TempDB’s busy workload. There is only one catch–that temporary volume in Azure is ephemeral, meaning the data gets wiped whenever your VM is rebooted.


You may think this isn’t a big deal–TempDB gets recreated every time your instance restarts, so who cares if the files get wiped? Well, it’s not so much the files as the directory that the files live in. You could just place your files at the root of the D:\ drive, however that would require your SQL Server service start as admin. Since we like to follow security best practices, we aren’t going to do that. I usually follow this process as defined by Jim Donahoe b|t) in this post.

I was teaching Azure infrastructure last week, and decided that it might be a good idea to do this using Desired State Configuration (DSC) which is part of automation. DSC allows you to use PowerShell scripts and a specific template format to define the configuration a group of machines (or a single machine). Documentation on DSC is sporadic, this project is a work in progress, because I had a client deadline.

But before I can even think about DSC, I needed to code the process in PowerShell. I start out by calling Dbatools which greatly simplifies my TempDB config. By using Set-DBATempDBConfig I just need to pass in the volume size, which I can get from WMI–I’m allocating 80% of the volume to TempDB (I use 80% because it’s below the cutoff of most monitoring tools) and then the script does the rest. I have a good old tempdb script,  but by using DBATools I eliminate the need to figure how to run that in the context of automation.

You can import PowerShell modules into Azure Automation now–this is a relatively recent change. I don’t have this fully baked into DSC yet, but you can see the PowerShell to create the PowerShell script (yes, read that correctly) and the scheduled task in Github.

I’d welcome any feedback and I will add a new post when I finish the DSC piece of this.

You’re Speaking…and You Don’t Have Slides

I had this dream that other week. I was in the big room at PASS Summit, sitting in the audience. I was relaxed, as I thought I was presenting later in the day, when I quickly realized, due to the lack of speaker on the stage, that I was the next speaker, and the room was full. And I was playing with my laptop and I didn’t have a slide deck. In my dream, this talk was a 300 level session on troubleshooting SQL Server, something I feel like I could do pretty easily, you know with slides. Or a whiteboard.


I woke up, before I started speaking. So, I’m not sure how I would have handled it—interpretive dance? I’m a pretty bad dancer. One thing, I will mention, and I saw my friend Allan Hirt (b|t) have to do this last month in Boston—really good (and really well rehearsed) speakers, can do a very good talk without their slides. Slides can be a crutch—one of the common refrains in Speaker Idol judging is don’t read your slides. It is bad form—do I sometimes read my slides? Yeah, everyone does occasionally. But when you want to deliver a solid technical message, the best way to do that is telling stories.

I’m doing a talk next month in Belgium (April 10, in Gent), right before SQL Bits. It’s going to be about what not to do in DR. My slide deck is mostly going to be pictures, and I’m going to tell stories—stories from throughout my career, and some stores from friends. It’s going to be fun, names will be changed to protect the guilty.

So my question and guidance for you dear readers, is to think about what you would do if the projector failed and you did not have a whiteboard. I can think of a number of talks I can do without a whiteboard–in India last year, another instructor and I demonstrated Azure networking by using our bodies as props. What would you do in this situation?

8 Things To Do After You Install SQL Server

Scripts to come..

Script file for the run once script to do all of this is here.

The script to do the installation and the ini file to drive it, is here.

Sales Reps–Please Don’t BS Me, Alright?

Today is my morning of big data storage events, I’m attending two from two different vendors in about four hours. One down so far, and it was pretty good, until…

I’ve bashed sales reps before (on twitter and on this blog), I’ve even offered lists of things not to do. Well today’s presentation was on par with some of the best I’ve seen. I was engaged, and we had a good discussion of the architecture of Hadoop, and the kind of data applications where it really sense. I was engaged, and wasn’t bashing the vendor on twitter like I sometimes do.

But Then,

The vendor had a slide with the Hadoop ecosystem up–there are a lot of components there. And they aren’t all needed. I though a really good comparison would be to SQL Server, we don’t always need replication or analysis services installed, but if we want to have a database we need the engine. Hadoop is a lot like that–you can get by with just a few components out of the total stack.

At that moment the presenter mentioned SQL Server, and I thought, great this will be a really great example. Then he asked “What is the core engine to SQL Server?” (The right answer I think is Sybase, then it was rewritten for 2005, iirc, someone correct me if I’m way off) He eventually responded with “Jet Database” using the example that you can install SQL Server without installing Jet. As far as I can tell and from my twitter queries, SQL has never run on jet, but Jet may run on SQL Server now.

Anyway, the trivia isn’t the point–if you are quoting a fact in your presentation, be certain of it, and if you aren’t either don’t use that fact, or clarify, saying “I think this to be the truth, but I’m open to facts”. After this, A) I didn’t trust the speaker’s credibility and B) I was distracted trying to confirm the fact the Jet was never a part of SQL Server.

I guess I can add one more thing for sales reps not to do–don’t make $&%# up, you may have a subject matter expert in the room, and you will look like an idiot.

SQL Server Clustering — Why you should use mount points

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.

A recruiter story, Part II

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”

The end. Be careful out there folks!!

A Recruiter Story, Part I

This conversation came up on Twitter a few weeks ago between Brent Ozar (blog | twitter), Tom LaRock (blog| twitter) and Adam Machanic (blog|twitter). Brent has posted before about how to work with recruiters, here and here.

There is a perception in the IT community that recruiters are nothing better than bottom feeding, used car salesman. As a point of reference, I’m trying to hire a DBA right now, and I’ve posted to Linked In and Twitter about it, and have had to be hassled by several sleaze bags types. A really good, professional recruiter is hard to find, and if you find one keep their name in your Rolodex forever.

I’ll echo what Brent says in one of his posts—recruiters don’t work for you, and they aren’t the employees of the company you’re trying to work for, they are contracted middle men, who only get paid if you get the job, and typically work there for six months. So remember—you don’t have the same best interests as a recruiter. Also, please note, most of these blogs are written with the perspective that your are currently employed, and looking for a more interesting role, if you’re currently unemployed, all bets are off, you need to try to talk with everyone you can (just don’t work with anyone who asks you for money!!! Major no, no)

The names in this post have been omitted to protect the innocent and the guilty.  This was a story told to me by a fellow DBA who worked with me in past job.

I get a call from a recruiter about a Senior DBA position with a healthcare software company in the area, and the salary range is X-X+25. I told the recruiter that I would really need to be at the very top of the range for this to work. Of course, the recruiter says no problem. This position was interesting to me, because I would be involved a lot more in development, and I could continue to carry my healthcare experience along.

I send my resume along to the recruiter, and their HR likes it and schedules quick phone screen (with HR).  When HR calls me the first question, after pleasantries were exchanged, was “What is your salary range?”, no questions about my experience or details about the role. Of course, I try to back away from the question; by saying., you know I need to hear more about the responsibilities of the position, etc. The HR rep won’t back down, so I say I need to at least be at X+25, she responds with “Oh, we were only looking to pay X, and no more” . So that call ended quickly—I called the recruiter back and told him the story, and he didn’t quite know what to say.

The next day the recruiter calls back, and says the hiring manager would like to talk to you, and that they would be willing to pay X+25 for an extremely qualified candidate such as yourself…

Come back next week for the conclusion to this exciting story!

How to Transfer an IT Project Effectively

A couple of weeks ago I got some really good news—I was transferring into a new role at my current company. Since this is an internal transfer, the standard two-week notice stretched to four and I had time to properly transfer my existing projects.

It is important to be as effective as possible in doing this, so you aren’t dealing with your existing projects in your new role.

The first thing I recommend doing is introducing the project contact person (In this case a business user) to the DBA you are handing off to. Ideally, you can do this in person, but since we are at multiple sites, I had to do it over email. Make it clear you will be transitioning all responsibilities over to the other DBA, and are trying to be successful in a new role, so can no longer support this project (be really nice about it—kill ‘em with kindness).

Secondly, if you have an email folder for the project, share it with the rest of your DBA team. If you don’t have one—create one and copy as much mail as you can into it. The search function in Outlook 2010 works really well, and sharing the mail may save you a phone call or two down the road.

Finally, and most importantly, you need to build out a project document. Put as much detail in as possible. I used the below sections for mine:

• Project Contacts—Who you work with, and what is their business function • Vendor Contact Info—Who you work with directly (If a small vendor) or contact/support info for a larger vendor

• Business Purpose—What does this application do, and what does it support? What are the uptime requirements?

• Authentication—How is authentication handled? Are there SQL logins to worry about? • Security Groups—This ties back to authentication, but are there domain groups controlling access to the application? Which groups control which rights?

• SQL Jobs—If the application has agent jobs—what are they? What do they do? How do you handle job failure? Who is the point of contact on the jobs?

• General Narrative—Describe anything else about the application. In our case there are some subprocesses that were failing, so I included that information. I also discussed the application patching process, along with some persistent issues we were trying to resolve with the vendor

. • HA/DR—This was touched on earlier, but how is specifically implemented? Where is the database mirrored/replicated to?

• Architecture—A very detailed Visio showing the whole landscape for the application—client connections, database connections, application servers, any external connections.

This list is in no way complete—feel free to add anything in the comments, I’d love to improve my process.

This is important stuff—in order to be as successful as you can  in a new role, you need to be able to transition projects effectively, but this same process can be used to transfer support of an application to an operations team, or outsourcing overnight support.

%d bloggers like this: