Ryan Adams Blog

SQL, Active Directory, Scripting

80s Tees Tommy Boy T-Shirt

I was just awarded as a Microsoft SQL Server MVP!

I can’t believe Microsoft has chosen me for this award and to be included in such a prestigious program.  There are only a little over 70 SQL Server MVPs in the United States and a little over 350 world wide.

For my readers who are unfamiliar with the program here is how Microsoft describes it.

Microsoft Most Valuable Professionals, or MVPs are exceptional community leaders who actively share their high-quality, real-world deep technical expertise with the community and with Microsoft. They are committed to helping others get the most out of their experience with Microsoft products and technologies.

You can learn more about the program HERE

I wouldn’t be in this position if it were not for my understanding and supportive wife.  My deepest gratitude and love to her.

I also have to thank the SQL community for all the support and opportunities.  I love this community, and I look forward to continuing to serve and spread some knowledge.

I presented a session titled “SQL Server AlwaysOn Quickstart” on September 10th for the 24 Hours of PASS event.  You can view the session recording HERE if you missed it.  The session was a preview of the full session I will be presenting at the PASS Summit 2014 this November.  I will be presenting the full session on the first day of the Summit, November 5th, 2014 at 10:15am PST.

The session is right before lunch which means I don’t have to hurry out to make room for the next presenter since there is a 2 hour lunch break.  You came to the Summit to learn about SQL Server and get your questions answered so I’ll stay after the session for as long as it takes to answer every question you have.  If I don’t have the answer then I’ll find it for you and I really mean that.  Ask anyone who I promised to followup with after one of my sessions.

Make sure to add my session when you’re using the Schedule Builder to plan your Summit sessions!  Now on to the questions I received during the 24HOP session.

  • The FCI needs shared storage and the AG needs local storage. I’m confused on how they can be implemented together.
    • The nodes in your cluster can have both shared storage and local storage.  The AG will use the local storage (could still be SAN attached just not shared) and essentially mirror the DBs on all nodes.  The FCI will use the shared storage and might not even be installed across all nodes in the cluster.  They are absolutely supported together.  Come see my session and I’ll show you how.  Click here to download one of my presentations showing you a sample architecture.
  • I noticed you did not add the system databases to your availability group, is there a reason for that?
    • Yes.  Just like mirroring system databases are not eligible and cannot be added to an AG.
  • If SQL 2012 supports 1 primary and 4 secondary and SQL 2014 supports 1 primary and 8 secondary, how many are read/write?
    • Only the primary is read/write.  All secondary replicas are read only and you can disable that functionality or limit it if you want.
  • Are the replicas synchronized?
    • For a replica to be considered “synchronized” it must be in Synchronous Mode.  You can only have a maximum of two replicas in Synchronous Mode.
  • Is it common to use a SQL FCI as a primary replica and another SQL FCI as a secondary replica for protection and still take advantage of AGs for reporting purposes?
    • With the exception of the reporting part this used to be common prior to AGs.  You would have two 2 node FCIs in different data centers and mirror between them.  This gave you HA in both data centers and DR if an entire data center went down.  However, this is not so practical with AGs.  You would be more likely to have 2 replicas in each data center with an AG across them and no FCIs.
  • How would you handle getting stored procedures and SQL Agent jobs onto the secondary replicas?
    • You don’t have to worry about stored procedures since those are stored in the database and will be replicated automatically.  SQL Agent jobs will have to be re-created on the secondary replicas and additional code added to exit gracefully if the replica is currently acting as a secondary.  You’ll want them to run automatically if the replica becomes the primary.  See THIS POST for more information on automating logins and thanks to Robert Davis (Blog|Twitter) for writing the code.
  • How do you handle installing SQL Service packs after AGs have been implemented?
    • First make sure you have backups (That’s always rule #1).  You’ll want to update a secondary replica first, make sure it is in synchronous mode, and then fail over to it.  Now you can upgrade the remaining replicas and fail back to your preferred replica when done.
  • Does SELECT @@SERVERNAME return the listener name or the node name?
    • It returns the node name.  However, older versions of SQL Server (for an FCI) will return the listener name.  The best way to get the node name no matter what version you are on is to SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)
  • Wouldn’t it be possible to have an indexed view on a secondary replica to use with reporting off of that replica?
    • You cannot create database objects on secondary replicas separate from the primary.  You have to create them on the primary and they will sync to the secondary replicas.  Remember that secondary replicas are READ ONLY and can never be written to.
  • With AGs in automatic failover mode how does the client connection timeout have to be configured?
    • Your applications need to be written to retry the connection.  You should also add MultiSubnetFailover=true to the connection string.  You should consider reducing the TTL of the listener name.  I’ll write another blog post explaining this in more detail, but that is the short answer.
  • What is the advantage of having a group of databases in a single AG?
    • If you have several databases that support a single application, if any one of them fails then they all failover together.  If you were mirroring them it was possible that myDB2 could failover to the mirror while myDB1 and myDB3 continued to run on the primary.  That would break the application, but AGs mitigate that risk factor since they stay together within that logical construct.
  • Can you have replicas in different domains?
    • No.  All replicas have to be part of the same Windows Cluster which requires that all nodes be in the same domain.  As a previous AD guy I’ll take this a step further and point out that in most situations things are fine to be in different domains so long as they are trusted somewhere in the forest hierarchy.  That is NOT the case here.
  • All servers in our environment are in a cluster of 8 nodes. Would that be okay ,or do you still need to create another cluster inside that cluster?
    • You cannot create a cluster within a cluster.  In this case you don’t need to do anything since you already have a cluster, you’re one step ahead of the game.

 

Worst Day
I learned a hard lesson when I first started as a DBA.  Although to be honest, it’s not a lesson I should have had to learn (I’ll keep you in suspense).  I work for a large Fortune 100 company and as with many companies our size, there are many processes in IT.

I was in charge of a database for an instance of Microsoft Operations Manager 2000.  If you have ever supported that database you already feel my pain, but trust me there are much worse out there.  I digress.  I needed a backup plan and with MOM backing up the database is not enough.  You also have to backup your management packs if you changed anything from the default.  In addition, any custom management packs would also have  to be backed up.  I wrote a script to export and copy those to another server daily.  I’m very glad I had this in place because it saved my bacon in the end.

So what about the database?  We had a piece of backup software that the server folks put on all servers to take care of backups.  It’s been long enough now (~12 years) that I don’t even remember what the software was at the time.  They asked if we had databases on the server to make sure they got them backed up and assured me that the entire OS drive would be backed up as well.  All was well with the world.  We went through the typical deployment phases of procuring the hardware, getting it racked, getting it connected and configured on the network, and installing the OS.  Once the platform was there and ready to go we began working on the middleware pieces.  I won’t get into the MOM component architecture, but we did have two separate servers one each for the MOM Management database and the MOM Reporting database.

Part of the middleware piece is making sure everything third party is installed and working correctly.  The backup software was obviously one of those.  It installed without issue and the backup folks got successful backups.  Off we went to the races and the deployment went smoothly.

Fast forward about a year and I get an alert that the MOM Management database went into suspect mode.  Not something I wanted to see, especially as a new DBA and being SQL 2000.  I did some searching on the internet and at the time the results were sparse.  I attempted a DBCC CHECKDB REPAIR without luck and even resorted to a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS which also got me no where.  Keep in mind that this is a MOM alerting system, so losing some data was not a big deal and would have been faster than a restore.  Otherwise, I would not have entertained that option at this point.

So what was next?  Restore the most recent backup of course!  We called the backup guys and told them to restore the most recent backup.  They responded and said, “What backup?”  Very funny guys, just let me know when it’s done.  No, we don’t have a backup of that at all.  In fact, we have no record of ever backing it up.

That will make any day a bad day.  It’s also why I keep emails, like the one where they confirmed successful backups.  I’m now completely up the creek without a paddle.  I had to rebuild the entire MOM environment.  It took me all night, but I managed to rebuild everything.  I was so thankful that I had scripted out exports of all the management packs or there was no way I could have done all that in one night.

Ever since then I have always done my own backups whether there is a backup team doing it as well or not.  I tend to back things up to my DR server and I often setup a round robin scenario where server 1 backs up to server 2 and server 2 to  server 3 and server 3 back to server 1.

A tough lesson in trust to learn, but it has not and will not ever happen to me again!5worst_1_300x250_NEW

Many of you may already know that I am a PASS regional mentor for the South Central region of the United States.  My role is to help user group leaders obtain the tools they need to have successful groups and provide an open platform to discuss challenges and successes.  One of the groups in my region asked me about how to handle the monetary part of hosting a pre-con for their upcoming SQLSaturday.  My response was to explain three different methods that I have seen others use.  What I wanted to do was use estimated costs to give a clearer picture of what everyone walks with at the end of the day.

 

Option 1 – This option takes all costs out of the gross profit prior to splitting the net profit.

Gross Profit – Venue Cost – Lunch Cost – Travel Costs = Net Profit

Net Profit is split 50/50

Let’s put some numbers into that equation to better understand what the speaker and UG would stand to profit.  I usually see most precons set at $125.  Some will give an early bird of $99 and then raise it to $125 at a certain date, but we’ll keep the math simple.  Let’s assume we get 15 people to attend at $125 each.  That gives us a gross profit of $1,875.  Finding a free venue for a pre-con is far easier than that of the SQLSaturday, but we’ll assume it costs $100 for cleaning and facility services.  We’ll also guess that lunch is $15 a person and would cost a total of $225.  The last part is travel and we’ll guess 2 nights in a hotel is $240 (technically it should be 3 days, but the third is for the SQLSaturday so we won’t count that) .  Flight costs can vary and I’ve personally paid as little as $200 and almost up to $400 so we’ll go with $300 to be in the middle.  Let’s plug those numbers in and see what we get.

1875 -100 -225 -540 = 1010

1010/2 = 505

Both the speaker and the user group will have a net profit of $505.

 

Option 2 - This option has the speakers pay for their travel, but the profit is split 75/25 favoring the speaker.  If your user group is a larger or more financially sound group this is an excellent option.  From a user group standpoint all you really want is enough money to help with meetings.  For the smaller groups a SQLSaturday pre-con can fund an entire year of meetings.

Gross Profit – Venue Cost – Lunch Cost = Net Profit

Net Profit is split 75/25

1875 -100 -225 = 1550

25% of 1550 = 387.50

75% of 1550 = 1162.50

With this option the UG gets $387.50, but the speaker still has travel costs so subtract $540 from his profit and he gets $622.50.

 

Option 3 – This option is what most folks used to do and I suspect many still do.  However, most groups opt for one of the options above these days.  I suspect the reason this option has lost popularity is because it doesn’t attract speakers with the lower profit margin.  Look at the numbers and you’ll see what I mean.  If the flight or hotel cost was higher or the speaker had to rent a car (which I did not even factor in, but is extremely common) then the speaker would be lucky to even break even and might even lose money.

Gross Profit – Venue Cost – Lunch Cost = Net Profit

Net Profit is split 50/50

1875 -100 -225 = 1550

1550/2 = 775

So here the UG gets $725 and the speaker after travel costs gets $235.

 

I hope this helps for anyone out there hosting a pre-con to at least be able to plug some numbers into these options to make a more informed decision for your user group.  The other thing to factor in is that most SQLSaturday events that host a pre-con actually host two or more sessions.  That means the profit doubles or triples for the user group, but the speaker profits from above will remain.

So these are the 3 options I have seen used, but I’m curious if anyone else is using a different method.  If so, please post it in the comments for everyone to see.  I’m also curious what method your group is using and if this post made you think about changing it.

SQLSaturday Denver

 

I’ll be doing a precon for SQLSaturday #331 in Denver Colorado on September 19th, 2014 titled “A Day of High Availability and Disaster Recovery” .  If you can make it, I would love to have you in the class.  We are going to cover backups, windows clustering, AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups, and more.  This class will take each of those technologies in a progressive order to build on each other.  At the end of the day we will have a single solution built out in virtual machines on my laptop that use all of those technologies to build a comprehensive and real world high availability and disaster recovery architecture.  Click the button below to register and I have included the session abstract as well.

Eventbrite_Button

 

 

Let’s spend a day looking at several High Availability and Disaster Recovery solutions for SQL Server. We’ll start off with a solid foundation as we look at backups and how to both configure and performance tune them. After we build our foundation, we’ll take a look at SQL Server Mirroring and why it’s still an important tool in our tool box. Since all of the HA/DR solutions we will be looking at either sit on top of or can be combined with Windows Failover Clustering, we’ll learn how to setup and configure a windows cluster to host these solutions. Next we’ll build on that platform by looking at SQL Server AlwaysOn Failover Clustering Instances. Last we’ll dive into how to setup SQL Server AlwaysOn Availability Groups. Once we have a firm understanding of all these technologies we’ll see how they all work together by discussing a case study together and developing a comprehensive solution. Here’s what you will learn:

SQL Server backup types
SQL Server recovery models
How to design a backup plan
How to performance tune your backups for free
How to configure mirroring
Mirroring configuration tips to increase throughput
Setup and Configure a Windows Failover Cluster
Discover how to properly configure quorum to support the AlwaysOn feature set
Setup and Configure a SQL AlwaysOn Failover Cluster Instance
Setup and Configure a SQL AlwaysOn Availability Group

 UPDATE

I thought I would post an update after the event.  The class went great and we had some excellent discussions!  5pm came up on us really fast and although I had covered all the material, I had some more demos.  I offered to stay and go through them and about 7 of the 15 attendees decided to hang around.  I kept going for another hour before we got kicked out of the facility.  It was a fantastic day and I had an absolute blast.  I had added a case study for us to go over and see how many ways we could solve the business requirements, but we simply didn’t have time for that.  The feedback that I got was excellent and not a single negative thing was mentioned, in fact here is a testimonial from one of the attendees.

Testimonial

This past weekend was SQL Saturday #331 Denver. I always enjoy SQL Saturdays, but I was especially looking forward to this weekend because I was going to attend a SQL Saturday Pre-Con for the first time! The pre-con was fantastic! The Denver SQL Server User Group did an outstanding job, and it was a wonderful day of learning and networking.

I attended Ryan Adams’ session A Day of High Availability and Disaster Recovery. Ryan did an excellent job with this all-day session. You could tell that his agenda was carefully planned to build upon each module. Ryan’s first module was an in-depth tour of backups and restores, pointing out that these concepts were foundational to all High Availability (HA) and Disaster Recovery (DR) solutions. In his next module, Ryan expanded on backup/restores as he introduced us to mirroring. Ryan then walked us through the concept of Windows Server Failover Clustering (WSFC). Culminating in his module on AlwaysOn Failover Cluster Instances and Availability Groups, Ryan tied all the concepts together providing a holistic view of HA/DR. I would definitely recommend this all-day session to anyone looking to expand his or her knowledge of HA/DR. To find out more about Ryan Adams, you should check out his blog at ryanjadams.com.

SQLSaturday Baton Rouge

I’ll be doing a precon for SQLSaturday #324 in Baton Rouge Louisiana on August 1st, 2014 titled “A Day of High Availability and Disaster Recovery” .  If you can make it, I would love to have you in the class.  We are going to cover backups, windows clustering, AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups, and more.  This class will take each of those technologies in a progressive order to build on each other.  At the end of the day we will have a single solution built out in virtual machines on my laptop that use all of those technologies to build a comprehensive and real world high availability and disaster recovery architecture.  Click the button below to register and I have included the session abstract as well.

Eventbrite - SQL Saturday #324 Baton Rouge Pre-Con with Ryan Adams

 

Let’s spend a day looking at several High Availability and Disaster Recovery solutions for SQL Server. We’ll start off with a solid foundation as we look at backups and how to both configure and performance tune them. After we build our foundation, we’ll take a look at SQL Server Mirroring and why it’s still an important tool in our tool box. Since all of the HA/DR solutions we will be looking at either sit on top of or can be combined with Windows Failover Clustering, we’ll learn how to setup and configure a windows cluster to host these solutions. Next we’ll build on that platform by looking at SQL Server AlwaysOn Failover Clustering Instances. Last we’ll dive into how to setup SQL Server AlwaysOn Availability Groups. Once we have a firm understanding of all these technologies we’ll see how they all work together by discussing a case study together and developing a comprehensive solution. Here’s what you will learn:

SQL Server backup types
SQL Server recovery models
How to design a backup plan
How to performance tune your backups for free
How to configure mirroring
Mirroring configuration tips to increase throughput
Setup and Configure a Windows Failover Cluster
Discover how to properly configure quorum to support the AlwaysOn feature set
Setup and Configure a SQL AlwaysOn Failover Cluster Instance
Setup and Configure a SQL AlwaysOn Availability Group

PASS Summit 2014 Speaking Badge_250x250I am excited and humbled to be speaking at the PASS Summit for my second year in a row! There were 943 submissions and only 144 spots, so I am really excited and honored to be a part of the largest and most amazing SQL Server conference in the world.  They are expecting around 5,000 people this year in Seattle from November 4-7.

The current full conference price is $1595 and considering the speakers are consultants and folks like you and me that deploy these technologies in the real world every day, it’s worth every penny.  However, today (June 27th, 2014) is the last day to register at that price.  It goes up by $300 to $1895 tomorrow.  I have more good news.  You can contact your local chapter or virtual chapter to get a discount code worth $150 off.  The codes are good at the current price and after the price hike.  There will be more price hikes as the conference date gets closer.

I’m President of the PASS Performance Virtual Chapter so if you are looking for that code, or don’t have time to get one from your chapter, you can use this code:

VCSUM23

At the Summit I will be presenting a session titled “SQL Server AlwaysOn Quickstart”.  I would love to see you in my session.  Even if you have seen me present this session before, I’ll be making some changes and adding content since I have more time.  It is a beginner level introduction, but I talk about every aspect of the technology.  Here is the abstract so you know what to expect, and I hope to see you there!

In this presentation I’ll explain what the SQL Server AlwaysOn high availability and disaster recovery solution is all about.  I’ll talk about the different levels of protection it provides through Windows Clustering, SQL Clustering, and Availability Groups.  We’ll discuss how these three things come together to protect your databases. We’ll finish with a dive into availability group configuration, the new capabilities it gives us, and what’s new in SQL Server 2014.

The Kerberos Configuration Manager is a really handy tool that can make configuring Kerberos a much easier task, but it’s got a nasty little bug.  Configuring Kerberos can be tricky.  In what way you ask?  Here is a short list of just some of the things you have to consider.

  • What is my SPN supposed to look like?
  • Should I let SQL Server handle registering my SPNs or should I do it manually?
  • Are there special considerations if I’m running a failover cluster?
  • Where does the SPN go?
  • Do I have permissions to add/change/remove SPNs and if not what is the permission I need to request?
  • Do I need delegation enabled and on which account?

That’s the short list, but you don’t have to worry about all that stuff because you have the Kerberos Configuration Manager, right?  Not so fast.

This tool works great for most scenarios, but if your environment has a split DNS, multiple domains, or multiple DNS name spaces you better take a second look at the SPNs it suggests.  Many DBAs are not familiar with these concepts so let me give a real simple explanation and then we can work through an example.  A split DNS is where hosts are resolvable in more than one namespace.  This is fairly common when companies merge or buy out other companies.

Let’s say the company “Movie Studio A” uses the DNS namespace of movies.com and all hosts are registered in that domain.  When they create their active directory they decide to call it MovieStudioA.com.  They join all their servers to the domain and use movies.com as their primary domain suffix.  Server1 is now resolvable as server1.movies.com and server1.moviestudioa.com.  The problem here is if you open a command prompt and ping “Server1″ it will resolve it as server1.movies.com because of the DNS suffix.

Kerberos will only work with the Active Directory domain name and NOT any other resolvable DNS namespace.  Unfortunately the Kerberos Configuration Manager makes SPN suggestions based on how the client machine resolves the server name you input.  What it should do after resolving and contacting the server is get the domain it is joined to and correctly build the FQDN, but that is not the case.

Let’s see what it looks like.  I have a server called Server1 that is joined to the stars.com domain.  The FQDN is Server1.stars.com.  This server is also registered in another DNS namespace called DNSOnlyDomain.com.  Here is what the Kerberos Configuration Manager says the SPNs should be.

Kerberos Configuration Manager with bad SPNs

Kerberos Configuration Manager with bad SPNs

Those SPNs are not correct because they are from a DNS domain and not the Active Directory domain.  They should reflect the Active Directory domain and look like this.

MSSQLSvc/server1.stars.com

MSSQLSvc/server1.stars.com:1433

If you are using the Kerberos Configuration Manager make sure you know what Active Directory domain your server is joined to so you can identify if the suggested SPNs are correct.

I’m officially on the slate and running for the PASS 2014 Nomination Committee!

Why should you vote for Ryan?

I love the PASS SQL Community and everything it embodies.  I am very involved at the local and regional level.  I know all of the current board members and most of the past board members and those relationships have given me a good view into the PASS organization.  I served on the 2013 NomCom last year so I’m already intimately familiar with the processes.

Why am I qualified?

I have been involved with the community for the past 10 years and I was on the 2013 NomCom last year.  I am in my second term and fourth year serving on the board of directors for the North Texas SQL Server User Group.  I am in my second year as President of the Performance Virtual Chapter and served on the board the year prior.   I have been a regional mentor for the South Central Region for 3 years.  I was on the local core organizing committee for SQLRally Dallas as well as all 5 Dallas SQLSaturdays.   In addition, I have spoken at many SQLSaturdays, SQLRally, SQLCruise, PASS Summit, 24HOP, Dev Connections, local and remote user groups, and virtual chapters.  In the past I have been a session moderator for 24HOP and served on the Summit Program Committee.

Why do I want to serve on the 2014 committee?

I believe the NomCom should have a stronger influence than it does today.  This year’s NomCom will have an extended time frame with the ability to review elections processes.  I served last year and those processes are fresh on my mind.  I believe that they need some changes and my prior experience gives me a perspective on what changes are needed and the overall impact on the PASS organization.  I’m excited to have the opportunity to review those processes for any possible improvements.

PASS is an amazing organization that has provided myself and others with both personal and professional opportunities.  Having a part in vetting the future leaders of an organization that provides so many opportunities for so many people would be a tremendous honor.

How do you vote?

There are only 3 spots on the committee and I would very much appreciate your vote to fill one of those spots.  If you were a PASS member by June 1st, 2014 with a COMPLETELY filled out profile then check your email for a link to your ballot.  Feel free to post any questions here and I will be more than happy to answer them.  You can also visit the PASS Elections Site to stay current with updates on this year’s elections or keep an eye on the #passvotes Twitter hashtag.

Voters can cast 3 votes total (1 for each seat), but I want you to think about how many you actually vote for.  It is much more powerful to cast a vote for a single candidate than it is for all 3.  This applies to the board of directors election as well.  Of course I would love for that one vote to be for me!

How can you help?

I’m also going to ask you for a favor in addition to your vote.  I want you to either post here or email me (if you prefer privacy) the questions you would ask PASS board of director candidates during an interview.  The NomCom interviews and ranks each candidate.  The nomination committee’s job is make sure the PASS board gets a slate of qualified candidates.  In order for the committee to do that I want to know what matters to you, the member.  The members are PASS.  We want to make sure the candidates are thinking about what matters to you.

I ran into an interesting issue while installing SQL 2012 SP1 in a failover cluster configuration and utilizing the local TempDB feature.  If you are unfamiliar with the new feature in SQL 2012 that allows you store TempDB on a local drive in a failover cluster you can read more here.  Here is a screen shot showing where during the install you can configure your data directories.  You can click on the image for a larger view.

TempDBLocalDirectory

You can see that I put TempDB on the local C:\ drive.  Now let me clarify that this was on a test virtual machine and putting TempDB on the operating system drive is a BAD practice.  In the words of The Offspring, “Don’t do this at home or at all!”  When you click next on that dialog you get a popup that there are warnings and to look below for more information.  You’ll notice a new warning area shows up at the bottom of that dialog.  Here is a screen shot, but the message gets cut off and you have to hover over it to see the whole thing.  It says, “You have specified a local directory C:\MSSQL11.INST01\MSSQL\Data as the tempdb data or log directory for a SQL Server cluster.  To avoid possible failures during a failover, you must make sure that the same directory exists on each cluster node and grant read/write permission to the SQL Server service.”

TempDBLocalDirError

If that directory does not currently exist, SQL Server will both create it and give the SQL Server service account permission to it.  At this point we don’t have to worry about anything as the install will take care of it.  What that message really means is that you have to make sure that it exists on each cluster node except this one.

This is also where the story takes an even more interesting twist.  After seeing that warning, I would fully expect to see it again when adding a node.  However, I don’t get a warning at all.   Maybe it created the directory for me since the first node did?  Now a lot of people might not think twice and assume everything is good since there was no warning or they simply might not remember once they get to the other node.  I’m not one of those people, so I’ll go look to see if the directory exists.  What happens if you don’t and try to failover to that node?

It will fail over again to the original node or another node.  You should see 5 events in the event log.  Here’s what I see on my test system.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf’.

FCB::Open failed: Could not open file C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).

Unable to open the physical file “C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf”. Operating system error 3: “3(The system cannot find the path specified.)”.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

That’s pretty much what I would expect to see.  That last error is misleading of course because we know the drive is not full, it’s because the directory does not exist.  At this point the fix is an easy one.  We just need to go to that node and create the directory C:\MSSQL11.INST01\MSSQL\Data and make sure that the account running the SQL Server service has change control on it.

I’m almost positive that adding a node gave a warning in a previous version like the RTM or a CTP, but I would have to go try that out to be sure.  On one hand Microsoft did warn you on the first node, but I still think it would be nice have it show up when adding nodes as well.  Besides, you might be adding a node 6 months to a year later and not remember.