Ryan Adams Blog

SQL, Active Directory, Scripting

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

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

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.

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

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.

The 2014 NomCom made some changes to how the election process works and it’s something you need to pay attention to.  It was posted on the PASS blog, but not really advertised that much so I want to highlight those changes and their importance.  I served on the NomCom in 2013 and we knew changes were needed and I think the changes that were made are a great move in the right direction.  The first thing the 2014 NomCom changed are the minimum requirements to even be considered for an interview.  You now need to meet the following requirements:

• Be a current eligible voting member of PASS
• Have direct volunteer experience with PASS
• Work with one of the Microsoft data technologies
• Be free of conflict of interest that would affect eligibility
• Be fluent in written and spoken English
• Agree to the time and travel commitments required to serve on the PASS Board of Directors
• Submit three references with a statement of support

The Big Change

This is something I encourage EVERY voter to pay close attention to.  In the past, all candidates were ranked from 1-10 or however many candidates there were.  As a voter that gives you some indication of how successful and prepared a candidate is to serve the community.  However, we are data folks and that’s just not enough information to make an informed decision.  It gives you a scale without any context around it.  Wouldn’t it be better if you could see what candidates were graded on and how they compare?  Well now you can.  The rankings are now gone and as a voter you get to see the scores of every candidate and how they stack up with some context around what they mean.

I think this was a great change and something every voter should look at as they consider their vote.  Below are the scores and here is a link to the PASS Campaign Site.  I didn’t beat out the incumbents, but considering how small the scale is I think I gave them a run for their money.  I hope this shows my dedication and ability to lead this great organization as well as your vote.

SCALE: Weak (1) Acceptable (2) Strong (3) Very Strong (4)

 

Candidate Experience with PASSVision for PASSUnderstanding of OrganizationOther Board Experience
Ryan Adams 3.63.23.02.4
Argenis Fernandez2.82.22.21.4
Tim Ford4.03.84.03.2
Jen Stirrup4.03.84.03.2

 

Candidate Written & Verbal Commun
ication Skills
Leadership/
Management Experience/
Skills
Financial/
Budget Experience
ReferencesRegional Influence
Ryan Adams 3.43.22.63.83.0
Argenis Fernandez3.02.81.83.63.4
Tim Ford3.83.63.83.83.2
Jen Stirrup3.43.63.03.63.6