Ryan Adams Blog

SQL, Active Directory, Scripting

Network Binding Order Warning

One of the more common errors I see when setting up a Windows cluster (usually in preparation for a SQL Server Failover Cluster Instance or a SQL Server Availability Group) is regarding an incorrect network binding order.  You will see this presented as a warning in the Cluster Validation Report.  There are actually two different errors that I have seen with the first being very common and the second being more rare.

Error 1

Rule “Network binding order” generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

What this error means is that the network card used to connect to your domain network is not at the top of the network binding order.  The fix for this is usually pretty easy because we just need to go into Control Panel…Network and Internet…Network Connections and make the change.  Once we get there we need to identify which NIC card is connected to the domain network and that can be seen in the “Network Category” column shown in the screen shot below.  You’ll notice that I have labeled my connections Public and Private and they both show “Domain Network”.  If you are configuring a multi-subnet cluster you will see the exact same thing, but if your cluster is on a single subnet the Private network connection will show “Public Network”.  So in a single subnet it’s the one labeled “Domain Network” that you are targeting and in a multi-subnet cluster it’s your Public connection you are targeting.

GUI Network Binding Order

In order to change the network binding order we need to go into the advanced settings.  Starting in Windows 2008 this option is hidden.  If you don’t see it hit ALT on your keyboard and the Advanced option pointed out in the previous screen shot will appear.  We need to select that and then go to Advanced Settings.  You will now be presented with the Advanced Settings box shown below.  In the screen shot you will see that my Public network is the second in the binding order and we need to move it to the top by selecting it and hitting the up arrow.  Click OK and go run Cluster validation again to see if it is resolved.

Network Binding Order

There is a chance that either your domain network was already at the top of the binding order, or you ran Cluster Validation again and it failed with the same error.  If that is the case then you either have a ghost network card (can be caused by NIC changes or driver changes) or the Microsoft Failover Cluster Virtual Adapter is bound before the domain network.  These adapters are not show in the GUI, but can be found in the registry and other places like the ipconfig /all command.

Unfortunately the network binding order in the registry uses GUIDs instead of friendly names, so we’ll have to do some translating to find and move the domain network to the top.  The first thing we will do is go figure out what the GUID of the domain network NIC is by running the following command from a command prompt.


WMIC Nicconfig Get Description, SettingID

You’ll remember that I renamed my NIC cards to be called Public and Private, but that’s the friendly name and not what will be returned from WMIC.  WMIC returns what is in the “Device Name” column from the very first screen shot above.  In my case it is called “Intel(R) PRO/1000 MT Desktop Adapter”.  You can see this pointed out in the screen shot below where we can see the output of WMIC in the command window.  Note that the GUID starts with A7.

Registry Network Binding Order

Now we just need to open Regedit and head to HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\Bind.  That’s right, we’re getting down and dirty now so make sure you know what you are doing and above all else backup your registry before you make any changes.  Mistakes in the registry can be costly and destroy a system so proceed with caution because from here on out the responsibility lies solely with you…not me.  In the screen shot above you can see that my Public Domain Network is next to the last in the list and we need it to be at the top.  As an aside, I have also pointed out where the Microsoft Failover Cluster Virtual Adapter is located since I see this listed above the Public network from time to time.

The fix here is to cut the GUID for the Public Domain Network that starts with A7 and paste it at the top of the list.  Now we can go run Cluster Validation and life should be good unless you get the second error we’ll talk about now.

Error 2

Note that the error message is the same error you got above.  However, it’s a completely different issue.  So let’s say you verified the above and that the domain network is the first in the list, but the error persists.  Go Look in the following file and search for “IsDomainInCorrectBindOrder” to find the warning in the log file.


C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\"Newest Log File Folder By Date"\Detail.txt

Here is a sample of the section you are looking for.

Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NetworkBindingFacet
NetworkBindingFacet: Looking up network binding order.
NetworkBindingFacet: Network: ‘Production Team’ Device: ‘\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’ Domain: ‘LITWARE.COM’ Adapter Id: ‘{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’
NetworkBindingFacet: Network: ‘Local Area Connection’ Device: ‘\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}’ Domain: ” Adapter Id: ‘{4DB91193-72F1-4713-A938-EB73F27CFEC8}’
NetworkBindingFacet: Network: ‘Heart Beat’ Device: ‘\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}’ Domain: ” Adapter Id: ‘{5AC63784-8088-40F7-93C8-37F9CD03D445}’
NetworkBindingFacet: Network: ‘BackUp Network’ Device: ‘\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’ Domain: ” Adapter Id: ‘{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’
IsDomainInCorrectBindOrder: The top network interface ‘Production Team’ is bound to domain ‘LITWARE.COM’ and the current domain is ‘CONTOSO.COM’.
Evaluating rule : IsDomainNetworkTopOfBindings
Rule running on machine: Server1
Rule evaluation done : Warning
Rule evaluation message: The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order.
Send result to channel: RulesEngineNotificationChannel

The issue here is that the server is joined to the LITWARE.COM domain, but the current domain is that of the currently logged in user which happens to be CONTOSO.COM.  Another way to say this is that the server is joined to the LITWARE.COM domain, but you logged with a user account from the CONTOSO.COM domain to create the cluster.  From a domain perspective these are completely different domains that have been trusted and it’s possible that they are in different forests too, but again they are trusted.  Technically this configuration is correct as the public/domain joined network is indeed at the top of the list.  You have two choices here.  You can safely ignore this warning or you can log out and back in with a user in the LITWARE.COM domain.

If you are looking for more information on Clustering, AGs, or FCIs you can go Find More Posts Here.

Let’s say you have a port conflict and need to change the port on your Availability Group endpoint.  How can we accomplish this?

The first step is to change the port on the endpoint itself.  Note that as soon as you do this the replica on which you are running it will no longer be able to replicate data until you have updated the URL as well.  Here is the code to change the port, just make sure you know what the IP is unless you are changing that too.  If you are, then read this post on changing the endpoint IP address.

ALTER ENDPOINT [MyEndpoint]

STATE = STARTED

AS TCP (LISTENER_PORT = 7777, LISTENER_IP = (10.x.x.x))

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

 

The second step is to change the replica URL.  This part is pretty straight forward since you already know the port from above.  What I want to encourage you to do is use the full URL or FQDN for the server name.


ALTER AVAILABILITY GROUP [MyAG]

MODIFY REPLICA ON 'MyNode1' WITH (ENDPOINT_URL = 'TCP://MyNode1.mydomain.com:7777')

GO

That’s all there is to it!

I had someone email me and ask how they could change the IP address on their Availability Group Endpoint.  It’s no surprise that IPs need to be changed from time to time due to certain circumstances, but what you might be wondering is where during the creation of your AG did you assign the endpoint an IP.  If you used the GUI to create your AG then the answer is that you never assigned your endpoint an IP.  So why do you even care?

The one thing I challenge folks to think about when I teach my HA/DR precons and AG sessions is to think about the types and amount of traffic across the entire ecosystem.  An Availability Group is going to have cluster heart beat traffic, user traffic, SQL replica traffic, and depending on your storage you might have traffic for that as well.  Your network is just like plumbing.  You can only fit so much water through a 1 inch pipe at a time.  The same goes for network connections, and that’s a lot of traffic to put over a single NIC port.  However, you can separate all of those traffic types and changing the IP of the endpoint will let you give the AG data replica traffic its own network pipe.

Every replica in the AG has its own endpoint so you will need a new IP for each one.  Go assign the IPs to a NIC card in each server.  Now we need to change the endpoint to use the new IP on each replica.  The good news here is that no outage is required to do this.  Here is the code to change your endpoint.  You just need to edit the endpoint, port (Read the caution below), and IP.

ALTER ENDPOINT [MyEndpoint]

STATE = STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = (10.x.x.x))

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

http://cuba-clothing.com/blog/?m=capital-one-auto-loan-approval-odds Caution!

You MUST use the same port you are using today. If you decide to change the port then the URL will also need to be updated. You can read this post on how to do that.

Have you ever seen the error below in your SQL Server log shortly after startup?  You’ll actually see two of them and you can see the difference between them in the screen shot, but here is the text.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Node1.stars.com:1433 ] for the SQL Server Service.  Windows return code: 0x2098, state: 15.  Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.  This is an informational message.  Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Kerb Error in Log

So what causes this error and how can you fix it?  The first thing to note is that it is an informational message and not actually an error.  There are two scenarios in which you will see this message.

  1. The first scenario is what you see right out of the box.  SQL Server does not have the rights on a domain service account to register Service Principal Names (SPNs).  The description we see here is very clear in telling us that SQL Server could not register the required SPNs.  It even tells us the exact SPN syntax it attempted to register.  If you want to use Kerberos you have to register the SPNs manually or give the service account the right to perform the change itself.  If you decided to register them manually then now is a good time to write down the SPNs from the description.
  2. The second scenario is a weird one that throws people off.  If you choose to manually register the SPNs on the service account and restart SQL Server, you’ll still see the same message in the log.  Now why in the world would this message even show up if you already registered the SPNs?  In fact, many folks will see this message and assume they are not using Kerberos, because the message clearly states that it could not register the SPNs.  The assumption is usually that they got the SPN syntax wrong or that the SPNs never got registered.

Just for kicks, let’s jump back over to my test server and take a look at the current connections.  Most folks will add a WHERE clause to the following query to just look at their current connection, but I’m going to caution you about that.  If you’re on the server itself you won’t get accurate results because you end up using Shared Memory unless it’s disabled.  We are looking to see if there are any Kerberos connections at all so we don’t want to filter the result set.

SELECT * FROM sys.dm_exec_connections
--WHERE session_id = @@SPID

Kerb Connection

Well that’s an interesting result, huh?  I clearly have Kerberos connections despite the message I keep getting in the SQL Server log.  So why is that?  Well it comes down to the semantics of the message.  The message said it couldn’t register the SPNs and that’s true.  It couldn’t register them because you already did it.  So if you ever see this message, make sure you go look at your connections first (using the above query) to see if it is something you need to address or not.  If you see KERBEROS in the auth_scheme column then you are all set.

If you want the message to go away completely, there is only one way to do that.  You have to give the account running the SQL Server service the permissions to change its own SPNs.  You can do that by opening the properties of the account and heading to the security tab.  You will find an account in the account list called SELF.  Grant that account “Write to Public Information”, restart the SQL Server service, and the message will disappear.  Now you’ll see a new message stating that SQL Server was able to successfully register the required SPNs.

If you are looking for more information around Kerberos and SQL Server you can find more right here on my blog at http://www.ryanjadams.com/category/kerberos/ .

I ran across a bug in SSMS 2016 CTP 2 back in August.  If you try to right click and look at the properties of a 2014 availability group using the SSMS 2016 CTP you’ll get an error.  The bug had already been submitted by my friend Trayce Jordan, so I just added some comments on my specific version (13.0.400.91) where I saw the issue.  You can find that Connect Item Here, but you’ll notice that is has been closed and marked as fixed.

I’m not sure why it was marked as fixed, because the issue still exists in version 13.0.600.65 and 13.0.800.111 and there are no comments at all about the purported resolution.  Here is the error text and what it looks like.

Cannot read property BasicAvailabilityGroup. This property is not available on SQL Server 2014. (Microsoft.sqlserver.Smo)

AG Property Error

You’ll notice that it refers to something called a Basic Availability Group.  That’s a new feature being added in SQL Server 2016 which essentially mimics Mirroring.  Although the feature has been in the CTP and “Public”, it has been under NDA until the last several weeks.  I’ll be blogging about this next month after the holidays.

Back to the task at hand.  Since this issue was originally reported in CTP 2 (and closed as fixed without comments) I opened a new Connect Item on it today to report it still existing in CTP 3.  If you support availability groups then I suggest you take the time to go up vote it.

VOTE HERE

 

SQLSaturday Austin

I’ll be doing a precon for SQLSaturday #461 in Austin on January 29th, 2016 titled “Creating a High Availability and Disaster Recovery Plan” .  If you are in the area or thinking of attending this event, 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 plan.

see url Still not convinced?  Here are some testimonials from previous classes.

Dallas Attendee

I just wanted to say thanks again for the training!  I took the 70-462(Administering SQL Server) exam yesterday and got all of the High Availability questions correct.  Your training was very thorough and I really appreciate it!

Oklahoma City Attendee

This is the best class I have attended in my entire life!

Denver Attendee

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.

Madison Attendee

One thing I liked the most about this session is the speed of the presenter was just right.  He would get our feedback frequently to see how fast or slow we want to move.  He had a lot of real world examples from his own experience and from other participants.

Madison Attendee

Great lecture!  Ryan is a very logical presenter; made the material extremely easy to understand.  I greatly enjoyed the class.

click here Want the full description?  Here it is:

Abstract: You have been tasked to configure a high availability and/or disaster recovery plan to ensure your company’s systems are always up and running while able to withstand a disaster. SQL Server offers several options, but how do you choose the right one to meet your business requirements? I’ve been faced with this same problem. I’ll help you figure out how to choose the right technology. We will start off with a solid foundation in backups and demonstrate how to configure them for initializing the various HA/DR technologies while also cutting your backup and restore time in half. You will see how you can keep an offsite copy of your database in sync with your production system with mirroring.

You’ve read that several of the HA/DR solutions require Windows Failover Clustering, but you don’t know anything about managing a complicated Windows cluster. I’ll show you how to setup and configure a Windows cluster and we’ll build on that with SQL Server AlwaysOn Failover Clustering Instances that provide the 99.999% of up-time your management demands. Lastly we’ll dive into how to setup AlwaysOn Availability Groups to solve both HA and DR in one solution. We will see how all these technologies work together by discussing a case study and developing a comprehensive solution just like you have to do when you return to the office. Here’s what you will learn:

Planning a backup strategy to avoid a data disaster
Cutting your backup and restore time in half when initializing DBs for Mirroring or Availability Groups
Configuring mirroring to keep production in real-time sync with an offsite database copy
Learning configuration tips to increase throughput
Configuring a Windows Failover Cluster to meet that five 9s business requirement
Discovering how to properly configure a Windows Cluster quorum to support the AlwaysOn feature set
Implementing a SQL AlwaysOn Failover Cluster Instance
Creating a SQL AlwaysOn Availability Group to solve both your HA and DR requirements in one solution

payday loans in daly city ca You’re just as excited and convinced as these fine folks, right? Then click the link below to get signed up.

Eventbrite_Button

All I can say is, “Wow…Thank you!”.  I won a seat on the PASS Board of Directors and it’s very surreal.  I am beyond excited, but not for what happened.  I’m excited and energized for what is to come.  PASS is young and our industry is in constant flux so I know there are some exciting things ahead.

Thank you to everyone that supported me and stuck by me.  I count myself very lucky to have such amazing #sqlfamily and your support is overwhelming.  If you ever need something my door is always open.

In an effort to be transparent, as I promised I would in my campaign, I want to explain my actions regarding the email sent out through the PASS Virtual Chapter.  As part of my campaign, I reached out to my personal network and asked folks if they would be willing to send out a note on my behalf.  I asked chapter leaders and that included the leaders of the Performance Virtual chapter, of which I am President.  Some graciously chose to send an email on my behalf, and the Performance Virtual Chapter was one of them.  I did not send anything myself and I did not coerce anyone into doing it.  I verified the rules prior to making the request, but I didn’t think about the perception that it would send to the community.

I had no intention of ill will or being unfair since all candidates have the same right to ask community members and leaders for support.  I also had no intention of taking advantage of my position in leadership.  However, I see that it was wrong to send the request that I did.

I’ve come to see that leaders should strive to adhere to a standard higher than simply following the rules.  Leaders should avoid even the appearance of impropriety.  I failed at both those tasks.  I created a situation where people were concerned that I had abused my position as a VC leader.  The fact that I didn’t break any rules doesn’t matter, I am 100% responsible.

This has been a painful experience for me in many ways but it has also helped me to be a better leader. Moving forward my goal won’t simply be to follow the rules. My goal will be to always avoid a situation where anyone might ever accuse me of using a position of public trust to benefit myself.

I apologize to the community and my supporters for the harm this has caused.  Please be assured that I have learned from this.  I view myself as a man of integrity and I will continue to exemplify that in everything I do.

The Rules | NomCom Response

Today I am going to continue with my PASS Election Questions and Answers series.  Here is where you can find the rest of the series and my campaign landing page.

Question 1

If elected, what would prompt you to step down before the end of your term?

Answer

I always complete a commitment. That is something that was instilled in me by my parents and something I teach my children. The only reason I would ever step down would be an illness in my immediate family.

Question 2

Where do you stand on PASS BAC? Should we do it?

Answer

Yes this is something we should be doing…for now.  The market is changing constantly and trends are pointing businesses toward analyzing their data to get business insights. PASS needs to remain relevant in the current market place to support industry workers. A part of remaining relevant is to consistently evaluate current programs against the industry and look for opportunities to engage in new ways.

The PASS BAC Conference has been in the green which indicates that the interest and market exist.  As the business and data groups grow closer together it makes sense for PASS to serve this branch of our growing market.

Most DBAs don’t like this because they think it means PASS is moving away from SQL Server DBAs which is the core that PASS grew from.  I’m a DBA myself and I understand this sentiment and here is where the rubber meets the road.  I think it makes sense now for PASS to test this new market and extend itself that way.  I don’t think PASS should be moving toward BA and away from a DBA centric focus.  I also think that just because it makes sense now doesn’t mean it will always be that way.  Things should always be re-evaluated on a regular basis.  Does this meet business goals?  Is this inline with the mission of PASS to Connect…Share…Learn?  Right now I think it does.  Will it next year?  We need to re-evaluate it and make sure it is still the right thing to do.

You wouldn’t create a DR plan or have backups and never test them, right?

Question 3

What will you do if PASS BAC needs every portfolio owner to give up 25% of their budget?

Answer

This is tough without knowing specifics, but a request like that would need to be justified with an ROI and KPI on the benefits to PASS. Value needs to be shown before a decision can be made and a request like that would need some impressive margins.

Question 4

How will you grow PASS?

Answer

PASS is a grassroots organization and we can continue to grow PASS by nurturing the areas that touch PASS at those levels. I also think we can stimulate future growth by getting involved with our youth in the educational systems.

As we continue through the campaign period for this year’s PASS Board of Directors Election (Voting opens tomorrow morning), I want to continue to show our community members the type of board member I would be by asking myself some questions.  You can find my first questions post here.  I also want our members to know that this is the type of transparency you can expect from me.  I’ll blog about everything I can and solicit your feedback.

Here are the other posts in the series.

 

Question 1

How will you influence progress in the portfolio you are given?

Answer

I am a very organized and goal driven person. I will influence progress through close relationships with PASS HQ and making sure we have a plan with documented steps that we can apply to an agreed upon timeline.

Question 2

If what you want to do and the budget you have is not enough, what will you do?

Answer

I will start looking for alternatives that would be more cost effective. If it is something beneficial to multiple portfolios I would look to share the cost for a mutual benefit. If it is something that might be beneficial to other organizations I would look to see if a partnership might be established. I will also look into what can be done in the current year budget to increase funds for the following year.

Question 3

If you don’t get any support from any board member for something you are passionate about, what will you do?

Answer

Sometimes we can let our passion get the best of us and it causes us to be blind to the bigger picture.  This is something that I am very aware of working in such a large corporation for so many years.  That experience has allowed me to identify those situations, step back, and evaluate it again.

You have to ask yourself what it is that everyone else sees that you do not. Discussions with individual board members to understand their thinking would be needed to reconcile my passion with the true needs of the community.  The needs of the community will always be my focus.