DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Kevin3NF

IRL: Cannot Connect to SQL Server

by Leave a Comment

IRL #2 – Connectivity Issues

Problem: Application owner is trying connect to SQL Server to create a database, connection failed.  Error message suggests checking Instance Name and “Allow Remote Connections” option set to True.

Background: This client has a wide variety of SQL Servers in Prod, Test and Dev.  Many different versions and editions, quite a few coming from 3rd party vendors.  This was in a new test environment for an application already up and running in Prod.

Resolution Attempts:

  • Try to Connect via IP address – failed
  • Verify Instance name – verified
  • Verify “Allow Remote Connections” is set to 1/True – it is
  • Verify SQL Browser Service Running – discovered Set to Disabled
  • Verify TCP/IP network protocol enabled – discovered disabled

Solution:

  • Enable TCP/IP for Named Instance SQLEXPRESS03
  • Restart Instance
  • Set SQL Browser Service to Automatic and start
  • Test logon via UDL file – success

Lessons you can benefit from:

  • SQL Express default install only enables Shared memory connections
  • Error messages are not always going to give you complete information
  • Have a process for testing

This entire process took about 10 minutes, including the instance restart.  There are 3 other Express edition installs on this server.

Thanks for reading!

Kevin3NF

Video: Getting Started with SQL Server Execution Plans

by Leave a Comment

I put this video together in about ten minutes of T-SQL code prep, and one-take of the video.   It is not meant to be an all-inclusive lesson…merely an introduction to how to read SQL Server Execution Plans (Right to Left, Top to Bottom, etc.) and some basic things to look at.  Senior performance tuning DBAs…you probably don’t want to watch this 🙂

Comments and likes are appreciated if you think it has value…

Thanks for watching!

Kevin3NF

Apprentice update 2019

by Leave a Comment

If you have been following this blog or my Twitter account long enough you are aware of the Apprentice.

I started teaching him SQL Server Database Administration a few hours a week back in January 2017.  Literally, starting with “What is a database?”

Much of the material and ideas have now turned into SQL Saturday presentations all over the US, or YouTube videos.

In May of 2018, I hired the Apprentice as a part-time Junior DBA, and handed him 4 entry-level clients.  These typically require no more than 15 minutes a day, but for him early on, each was an hour.   Different monitoring tools, different scripts, different VPNs, etc.  One has only 2 servers, others 20 or more between on-prem and Azure.  One has a failover cluster, replication and a huge Integration Services deployment.

He’s handled it all like a champ.  Knows the basics inside and out, and escalates to me with questions.

Fun stuff that happened in 2018:

  • Multiple SQL user group events
  • SQL Saturday Dallas – he helped run the Dallas DBAs booth
  • Got a 10% year-end bonus
  • LOTS of prep for test 70-462
  • Wrote a blog post
  • Had his first ever performance review

Goals and fun things for 2019:

  • Goal: Increase to 4 hours a day (need a new 2-hours/day client…anyone??)
  • Goal: Show up on time every day (12:30 pm)
  • Goal: Pass 70-462, and start working on 70-764/765 to get his MCSA
  • Fun: Travel to a SQL Saturday and co-present a pre-con and/or regular session
  • Fun: Blog something
  • Fun: Get a raise
  • Fun: Help find the next apprentice

Feel free to drop a comment of encouragement or stalk him on Twitter!

Thanks for reading!

Kevin3NF

SQL Saturday Cincinnati 2019

by Leave a Comment

Dallas DBAs is proud to announce that we are a 💎Diamond Sponsor💎 of SQL Saturday Cincinnati on March 30, 2019, being held at the University of Cincinnati.  We will be presenting a lunch session: “Your SQL Servers Are Misconfigured!”

In addition, Dallas DBAs team members Kevin and Derek will present a pre-conference on Friday, March 29: “DBA Fundamentals: Give yourself a solid SQL Server foundation.”  This all day session is targeted to Developers, Students, System Admins, Managers, Oracle DBAs, etc.  Anyone that can benefit from knowing more about the Administration side of SQL Server. $75.

Please come Saturday for a free day full of training options. Register here.

IRL: Non-yielding IOCP Listener

by 4 Comments

IRL – internet lingo for “In Real Life”

This is first in what I hope will be a regular series of posts that highlight a problem my team encountered on a SQL Server, what the cause was, and how we fixed it.

We will of course anonymize the details.  Part of this is to tell interesting stories, but a bigger part is to help future Googlers find how we resolved it.

IRL #1 – the Non-Yielding IOCP Listener

Problem: SQL Server running a major ERP system suddenly becomes non-responsive, effectively shutting down an entire retail company.  Online sales, Distribution, Customer Service…all of it.  2 weeks before Christmas.

Background:

  • SQL 2012, SP3 – Enterprise
  • 2-node Failover Cluster Instance
  • Replication Publisher
  • Other databases on this instance as well
  • Beefy hardware
  • No issues like this in at least a year that I have known them
  • No changes to the SQL code, SQL Instance or any application deployments

Investigation:

ERRORLOG (always go to the SQL Errorlog early in the process):

Note there are 2 different Non-yielding messages – Schedulers and IOCP Listener.

Also, stack dumps are being generated.  I do not have the tools or skills to analyze a stack dump.  I rely heavily on Microsoft for this when needed.

The stack dump .txt and .log files contained nothing other than what was in the ERRORLOG.

Resolution Attempts:

  • Failover to the B node.  Because its a cluster, and this might be a one-time glitch in the Matrix, right?
    • Post-failover the system was up for 30 minutes as the Application servers were restarted one by one.
    • When app server “4” was started we almost immediately got a repeat of the above behavior.
  • Try again, with fewer app servers – nope.
  • I suggested sending the stack dump to MS early on…but we kept trying failovers and restarts, thinking the App server load might be the issue.
  • We upgraded to the current SP, as this error in SQL 2008 was resolved in service packs, and in CUs for 2016/2017.  This did not change anything.
  • Lather, rinse, repeat in various ways for <redacted> hours until we decided to get Microsoft Involved.  We opened a Premier CritSit case, which triggers a LOT of attention and internal alarm bells on the MS side.  This has not changed since my last contract there in 2006.
  • Inside of 2 hours, MS had analyzed the dump and sent back this:
    • NonYielding Call Stack
      # Call Site
      00 <non-SQL Server .dll>
      01 0x0
      02 0x0
    • “We see the third party DLL located in the SQL Server address space: C:\Windows\System32\<non-SQL Server>.dll.”
    • A really good article that does reference the situation
    • “We see <non-SQL Server>.dll being called and it is interfering with the IOCP listener which is the thread that accepts incoming connections/TDS packets therefore preventing additional connections (the behavior we see when we cannot connect via SSMS, for example). We see the thread stalled for 15 seconds and 15 threads in SQL that have that on top of their stack. What is the <non-SQL Server>.dll?  It is calling a jmp detour in ntdll.dll.”

Solution:

  • Identified the .dll and verified with the vendor what it was doing
  • Uninstalled the application that put it there (both nodes)
  • Restarted everything with no further issues.

Lessons you can benefit from:

  • Stay current on Service Packs – many of the issues are fixed in SPs and CUs
  • If you get recurring stack dumps, call MS sooner rather than later…even on a credit card, $500 is cheaper than Prod being down
  • Karthik PK from MS has some good posts here and here on this

I am specifically leaving out the name of the offending .dll for two reasons:

  • Client anonymity
  • We don’t shame vendors around here…was probably a small bug in a product update

If you enjoyed this post and want to see more like it, please leave a comment, or ping me on Twitter

Thanks for reading!

Kevin3NF