Ryan Adams Blog

SQL, Active Directory, Scripting

Buffer Pool Extension was released in SQL 2014 so it’s not new.  It is also not advertised very much, but that’s not for lack of usefulness.  RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM.  BPE allows us to extend the buffer cache by storing clean pages on an SSD drive.

In this video I show how easy it is to implement Buffer Pool Extension.  You don’t even have to reboot or take an outage to turn it on, but remember to always test!  You can find the TEST code below the video.

--Review current BPE configuration
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Reduce SQL Server Max memory to restrict the BP and force the use of BPE
EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure 'max server memory (MB)', '2000';
EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;

--Enable BPE
--Go look at the file size on disk right after you run this

--Now that it is enabled we'll inspect the configuration again
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Try to read enough data to fill BP and start using BPE
USE AdventureworksDW2016CTP3;
SELECT * FROM dbo.FactResellerSalesXL_PageCompressed;
--If the above didn't do the trick then query this table as well
--SELECT * FROM dbo.FactResellerSalesXL_CCI;

--Let's see what went to BPE.  If there are no results then go query more data.
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC;

--Turn BPE off. Go look in c:\temp to see what happens to the physical data file

--Put Max Server Memory back where it was
EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure 'max server memory (MB)', '3500';
EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;

I have had several Query Store questions come in from customers lately.  Some of the questions stemmed from things read on the internet so this video is aimed at both answering the questions and debunking some false information you might find out there.  We want concrete answers to the following questions, so we’ll demonstrate them all:

  • Does Query Store track cross database queries?
  • Does Query Store track queries run from the context of another database?
  • Query Store tracks every individual query in a batch, but can you relate the query back to the originating batch?
  • Does Query Store track queries that use Linked Servers?
  • Does Query Store track queries that use OPENROWSET calls?
  • Query Store does NOT track DDL statements, so what about SELECT INTO? (You might find out if this statement is truly DDL or DML)
  • What if I use Temp Tables or Table Variables since those would probably be cross database queries?

In this video I talk about Trace Flag 1117 and how it was designed to help keep data file growth with multiple data files in TempDB consistent.  You’ll see how growth occurs if the files are disproportionate and also the affect it has on TempDB contention with SQL Server special pages (PFS, GAM, SGAM).

For more on the behavior of Trace Flag 1117 and 1118 in SQL Server 2016 CHECK HERE.


I’m sure everyone remembers the movie The Matrix.  You probably also remember the scene where Morpheus asks Neo if he wants to take the red pill or the blue pill.  Almost 19 years ago I was Neo and I chose the red pill.  For me that pill was Verizon.  Back then it was WorldCom (We all know how well that worked out!), then MCI, and finally Verizon.

New Challenges

New Challenges

Now it’s time for me to try the blue pill.  Starting January 9th I am more than excited to announce that I’ll be taking a Senior Premier Field Engineer position with Microsoft.  I had a lot of offers and it was a tough choice, but this was the right move for my family and I.  The opportunities that I had were all a direct result of the SQL Server community, including this one.  It really goes to show how powerful the community is and I couldn’t be more thankful to be a part of it.  Of course this does mean that I will have to step down from the PASS Board of Directors and you can read more about that HERE.

I want to thank everyone in the community that helped and supported me.  I am truly blessed and grateful to be a part of such an amazing SQL family.


I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it.  I waited…and waited…and waited, but still nothing.  I started to investigate and verified that database mail was indeed enabled, Service Broker was running, and the mail queue was started and running.  I went to go look at the sysmail_allitems view to see the status of my test emails to find that the sent_status column showed them as unsent.  The only errors I could find were these:

Log: SQL Server Agent
[474] Unable to refresh Database Mail profile Server Mail. (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database.
System.Data.SqlClient.SqlException: profile name is not valid at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.Td)

Log: SQL Server Agent
[260] Unable to start mail session.

I spent the better part of a day trying to figure this out.  Those error messages were not exactly helpful in pointing to the root cause.  Since this was a new server that I did not build myself nor did I install SQL myself I decided to uninstall and reinstall SQL.  When I ran the install it failed and upon investigation of the install log file I found an error that pointed me toward the issue.

Error: 17182, Severity: 16, State: 1.
2016-03-23 14:49:21.08 spid12s     TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

At this point I knew I had a protocol issue.  The thing to note here is that this is actually blocking the install of SQL Server and I’ll explain why that is in just a minute.


You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0.  This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry.  Also note that TLS 1.2 was NOT disabled.  The server was running Windows 2012 R2.  These protocols were disabled to prevent the possibility of a POODLE attack.  If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008.  To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.

I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018.  In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.

You might be wondering how SQL Server was originally installed if having the protocols disabled prevented my subsequent installation attempt.  The reason is that the registry changes to disable them was made after the original install of SQL Server.  I’m glad this error happened on my subsequent install or I might not have ever figured this out.

Database Mail Breaks

So far the only issue I have run across by disabling these protocols (post installation) is that Database Mail can’t send emails and they just queue up with only the above vague messages.  The one that says it is unable to refresh a profile is an error and the other one is a warning in the SQLAgent log.  Most people don’t have alerts raised for this and if you sent those via Database Mail, you’ll never get them anyway.  This is where I want everyone to be cautious.  If these protocols have been disabled (and you may not be aware of it) then you will not get alerts for things like failed SQLAgent jobs that could be backing up your DBs or Hard IO errors, etc…  That means you could have issues on your SQL Server and might not even know about it, because the email notification is just sitting in the queue.

The Cause

The reason this issue exists is because Database Mail does not yet use the .Net Framework 4.6.1.  That is the newest version of the Framework and the only one that supports TLS 1.1 and TLS 1.2 for SMTP.  The .NET framework version 3.5 SP1 and earlier did not provide support for applications to use TLS 1.2 as a cryptographic protocol. In the update below they enabled the usage of TLS 1.2 in the .NET Framework 3.5 SP1.

The Fix

Microsoft could have fixed this in two different ways.  The first is that Database Mail be changed to utilize the .Net Framework 4.6.1 if it is installed.  The second is that the old versions of the .Net Framework be changed to support TLS 1.2.  As mentioned above, Microsoft chose option number two and just released a fix.  Check out THIS ARTICLE to obtain the fix.  The article will help you decide if you are running a vulnerable version or not and obtain the correct fix if needed.

Let me start off with a disclaimer that these thoughts on the January PASS board meeting are my own as I do not speak on behalf of PASS.

I have been on the PASS Board of Directors for about 4 months now and we had our first in person board meeting in late January. You can go here to read the January meeting minutes.

Since then, it has been a blur of quick changes as we had a few unexpected ones.  Thomas LaRock chose not to run in the PASS Executive elections in 2015 (blog) and therefore  became the Immediate Past President.  Adam Jorgensen was elected to the President role.  James Rowland-Jones (JRJ) was elected to fill the EVP seat, which would begin in January 2016; however, he later had to resign when he accepted a position with Microsoft.  JRJ’s resignation left the remainder of JRJ’s two-year term as a PASS Board Director vacant immediately.  To fill that spot, the board needed to appoint someone to finish out the Director-at-Large term.  Argenis Fernandez was appointed and voted in by the board last December.

Things appeared to be settled and Adam had assigned portfolios. I was assigned the Programs portfolio, which largely consists of our annual Summit conference.  As for the EVP vacancy in the ExecCo, Grant Fritchey was voted in as the replacement, which he assumed January 1.  This left the remainder of his seat open, meaning the board had to appoint another person and voted on Allen White during the January Board meeting. It’s extremely unusual to have this much change in such a short amount of time.

Adam took this opportunity to make some portfolio changes that worked out extremely well because it put all of us in portfolios that we were suited to best. Argenis was moved to the SQLSaturday portfolio, which was an excellent fit for someone that knows these events well from multiple viewpoints. I was moved to the Chapters portfolio. I’ve been on the board of the North Texas SQL Server User Group for the last five years and a regional mentor for the last four, so this was a perfect fit for me, especially since this area is my passion. Lastly, Allen was assigned the Programs portfolio. I personally couldn’t be more excited to have him in this role and his experience in PASS and the industry will most certainly give this portfolio great guidance. I know some of the things he is thinking about and I can say that Programs has a bright future.

So what is coming up in the future and what plans are being made? Normally in the past every portfolio owner would present their goals and plans for the coming year at the January board meeting.   Adam, since taking on the role as President, is taking the time to look strategically at PASS’s priorities and setting a long term vision.   Each portfolio director will work with the Exec to ensure that our goals frame the organization’s strategic priorities.  As a newly elected Director, this gave me a time advantage which is helpful since a couple of my goals are a little lofty.  It gives me time to do the research and discover the probability of success with those goals. My hope was to share those with you in this post, but since they are still a little fluid you’ll have to remain in suspense. I will say that the goals are in support of increasing communication and resources, marketing, and enhancing the Regional Mentor (RM) program.

There are a lot of exciting things coming and tons of amazing ideas from the board and that just energizes me more. One of the biggest takeaways I had from the meeting was the passion of the folks currently on the board. Every idea and every decision is poured over painstakingly and that has never been more clear to me than after these board meetings.  No one is perfect and we might not get it right every time, but I can assure you that these folks always do their due diligence. Being on the board is not easy, and I have more respect for that and the folks in that room now than I already had.

I have 2 things I would like to ask of the community.

1.)  Every year at the PASS Summit I make it a point to thank every single person serving on the board for their time, commitment, and willingness to make difficult decisions that support the best interest of PASS. My first challenge for you is to do the same and thank those that have had an impact on you or the community. We are a completely volunteer-run organization and surprisingly, we don’t do this nearly enough.  A simple “Thank You!” goes a long way and encourages us all.

2.)  My second request is that if you have suggestions or changes you would like to see for Chapters or the Regional Mentor program, please send me an email. I might not be able to fix everything or accommodate every request, but I can certainly try or at the very least set the path in motion. I was invited to an RM call recently for the North Central region and I walked away with a bunch of notes, so know that my door is always open.


Have you ever had the need to query Active Directory right out of SQL Server using native TSQL?  There are 3 ways to accomplish this.  The first is CLR, but I won’t be covering that method.  The other two methods are OPENROWSET and OPENQUERY.  OPENROWSET requires Ad Hoc Distributed Queries to be enabled and OPENQUERY requires a linked server.  I had a fellow MVP ask me how to do this recently, but his restriction was that he could not use linked servers.  He will need to use the OPENROWSET method to avoid linked servers, but I also challenged him to consider creating the linked server, running the OPENQUERY, and then deleting it.  You can do all that in 1 piece of code and I’ll show that below.  It’s just an option worth knowing in case it fits for your environment.

In the case of my friend, they used something random for user names, but he wanted to get the friendly name.  In Active Directory the attribute he was looking at is called the sAMAccountName, but the attribute he needed was the displayName.  My examples below will only pull the displayName attribute, but you can easily add in any attribute you are looking for.  If you are not sure what attribute you are looking for or what is even available, then I suggest you download and take a look at the Softerra LDAP Browser.  It is also useful for learning how to write LDAP queries.  The last thing to be aware of is that what you see in Active Directory Users and Computers is generally not the real attribute name or it is not spelled exactly the same when referencing it programmatically via an LDAP query.  Below are some screen shots of those attributes we are dealing with in this article and their real name in AD.  You can reference this article on translating what you see in ADUC to LDAP or the real AD name.

Attributes in ADUC

Query Active Directory DisplayName
Query Active Directory SAM and UPN

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

The second part of the code looks at using OPENQUERY.  The first thing we do is create a linked server using the Active Directory provider called ADsDSOObject.  Next we write our OPENQUERY and you’ll notice that it looks very similar to OPENROWSET, but instead of defining the connection provider in the function call we reference the linked server.  The same rule applies here about how many attributes you pull back and how many you actually plan on using, so continue to keep that in mind.  The last thing I do is delete the linked server.

Query Active Directory

--There are 2 ways to query AD from SQL Server.&amp;nbsp; The first is using OPENROWSET and the second is using OPENQUERY which requires a linked server.

--You have to enable Ad Hoc Distributed Queries to use OPENROWSET.&amp;nbsp; Note the OPENQUERY does NOT require this to be enabled since it uses Linked servers.

sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;
sp_configure 'show advanced options', 0;

SELECT DisplayName
FROM OPENROWSET('ADSDSOOBJECT','adsdatasource','SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass =&amp;nbsp;''User'' ')

--Here is where we create our Linked Server connection to AD
EXEC master.dbo.sp_addlinkedserver @server = N'AD', @srvproduct=N'Active Directory Services Interface', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AD', @locallogin = NULL , @useself = N'True'

--Here is the query using the above created Linked server
SELECT displayName FROM OpenQuery (AD,
'SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass =&amp;nbsp;''User'' ')

--Here we delete our Linked Server
EXEC master.dbo.sp_dropserver @server=N'AD', @droplogins='droplogins'


More Active Directory and SQL Related Articles


Yesterday Microsoft announced that they will now support SQL Server on Linux.  They are targeting mid-2017, but you can download a preview now.  I am on a technical list where I saw someone inquire about the support for Windows authentication or if SQL authentication would be the only choice.  Those of you that know me remember that Active Directory is my second strongest skill set next to SQL Server.  What you might not know is that I went through a Linux phase and up until a couple years ago I always had a Linux machine sitting next to my Windows machine.  It appears it may be time for me to get back into this world.

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

SQL Server on Linux Authentication with Samba

    • Make sure to remove any installed binary Samba packages to start clean
      • rpm -qa | grep samba
      • rpm -e SambaPackageName
      • Do the above command for each samba package found.  If it complains about dependencies then run the command like the following
        • rpm -e – -nodeps SambaPackageName
    • Download latest Samba source code
    • Extract the source code
      • tar xvfz samba-latest.tar.gz
    • Run the configure script from the source directory to create a make file
      • ./configure –with-msdfs –with-smbwrapper –with-smbmount –with-ldap –with-ads –with-cifsmount –with-libsmbclient –with-winbind –with-krb5 –with-pam –with-acl-support 2>&1 | tee config.my.log
    • Run make from the source directory
      • make 2>&1 | tee make.my.log
    • Run make install from the source directory
      • make install 2>&1 | tee makeinstall.my.log
    • Copy the following files to your documents directory for future reference
      • config.my.log
      • make.my.log
      • makeinstall.my.log
      • config.status
        • This file contains the options that you ran the configure script with
        • Search for “with options” to find the line with your configure options
    • Add Samba executable directory to path for root and yourself
      • Add the following to the end of the PATH statement in /root/.bash_profile and /home/myuser/.bash_profile
        • :/usr/local/samba/bin:/usr/local/samba/sbin
    • Add Samba MAN pages directory to the MAN path
      • Edit /etc/man.config and add the following two lines to the end
        • MANPATH /usr/local/samba/man
        • MANPATH_MAP /usr/local/samba/bin /usr/local/samba/man
    • Copy /usr/local/samba/sbin/mount.cifs to /sbin so that you can mount with CIFS
    • Make sure to create a smb.conf file in /usr/local/samba/lib BEFORE you start Samba
      • A sample can be copied from the examples folder in the downloaded source code
      • run testparm on the smb.conf to verify correct syntax
    • To automatically start Samba upon boot
      • create a file named startsmb in /usr/local/samba/sbin with the following contents
        • #!/bin/sh
        • /usr/local/samba/sbin/smbd -D
        • /usr/local/samba/sbin/nmbd -D
        • /usr/local/samba/sbin/winbindd
      • Make it executable with chmod +x startsmb
      • Add the following line to /etc/rc.local to execute the above script upon boot
        • /usr/local/samba/sbin/startsmb
    • Edit /etc/resolv.conf and make sure it has appropriate entries for “search” and “nameserver”
      • search mydomain.com
      • nameserver 192.168.0.xxx
      • nameserver 192.168.0.xxx
    • Configure Kerberos for ADS realm and KDC
      • Edit /etc/krb5.conf and add the following to the [libdefaults] section
        • default_tgs_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_tkt_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • preferred_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_realm = MYDOMAIN.COM
        • dns_lookup_kdc = true
    • Join Samba server to the domain as a domain member
      • net ads join -U username
    • Look for and delete the following files if they exist
      • /lib/libnss_winbind.so
      • /lib/libnss_winbind.so.2
      • /lib/libnss_wins.so
      • /lib/libnss_wins.so.2
      • /lib/security/pam_winbind.so
      • /lib/security/pam_winbind.so.2
    • Copy your newly compiled winbind, wins, and pam libraries from the source
      • cp sourcecodepath/source/nsswitch/libnss_winbind.so /lib
        • chown root /lib/libnss_winbind.so
        • chgrp root /lib/libnss_winbind.so
        • chmod 755 /lib/libnss_winbind.so
      • cp sourcecodepath/source/nsswitch/libnss_wins.so /lib
        • chown root /lib/libnss_wins.so
        • chgrp root /lib/libnss_wins.so
        • chmod 755 /lib/libnss_wins.so
      • cp sourcecodepath/source/bin/pam_winbind.so /lib/security
        • chown root /lib/security/pam_winbind.so
        • chgrp root /lib/security/pam_winbind.so
        • chmod 755 /lib/security/pam_winbind.so
    • Create a symbolic link for your libnss_winbind and libnss_wins libraries
      • execute /sbin/ldconfig
      • verify that libnss_winbind.so.2 and libnss_wins.so.2 exist in the /lib directory
    • Modify /etc/nsswitch.conf to make the lines for passwd and group look like this
      • passwd: files winbind
      • group:  files winbind
    • Add the following lines to your smb.conf file in the global section
      • idmap uid = 10000-20000
      • idmap gid = 10000-20000
      • winbind enum users = no
      • winbind enum groups = no
      • winbind use default domain = yes
      • winbind nested groups = yes
      • allow trusted domains = no
      • template homedir = /home/%D/%U
      • template shell = /bin/bash
    • Stop and restart the SMBD NMDB and WINBIND processes.  You can restart them and ensure the starting order is correct by executing the /usr/local/samba/sbin/startsmb file you created earlier.

Configure PAM

  • Configure PAM to use Winbind for authentication on the local system if desired
    • Edit /etc/pam.d/system-auth and add the following lines
      • auth             sufficient    pam_winbind.so use_first_pass
      • account        sufficient    pam_winbind.so use_first_pass
      • password     sufficient    pam_winbind.so use_first_pass
      • session         required    pam_winbind.so use_first_pass
  • The finished file should look like the following
    • #%PAM-1.0
    • # This file is auto-generated.
    • # User changes will be destroyed the next time authconfig is run.
    • auth        required      pam_env.so
    • auth        sufficient    pam_unix.so nullok try_first_pass
    • auth        sufficient    pam_winbind.so use_first_pass
    • auth        requisite     pam_succeed_if.so uid >= 500 quiet
    • auth        required      pam_deny.so
    • account     required      pam_unix.so
    • account     sufficient    pam_succeed_if.so uid < 500 quiet
    • account     sufficient    pam_winbind.so use_first_pass
    • account     required      pam_permit.so
    • password    requisite     pam_cracklib.so try_first_pass retry=3
    • password    sufficient    pam_unix.so md5 shadow nullok try_first_pass use_authtok
    • password    sufficient    pam_winbind.so use_first_pass
    • password    required      pam_deny.so
    • session     optional      pam_keyinit.so revoke
    • session     required      pam_limits.so
    • session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
    • session     required      pam_unix.so
    • session     required      pam_winbind.so use_first_pass

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 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.



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




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.


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


That’s all there is to it!