Thanks for Joining us at SQLSaturday Pensacola!

Thanks for joining us at #SQLSatPensacola!

The 8:30am Careers in Technology panel featured lots of talk about Twitter, so might as well mention that the panelists were (in audience's order, left to right): @SqlrUs @SQLEspresso @mviron @svenaelterman @william_a_dba @sqllocks @SQLServerDBA318 @IrishSQL. It was the first of four back-to-back sessions for the Assafs!

Then Christine gave a presentation on Giving Feedback: How to Effectively Communicate to your Employees. I presented on SQL Admin Best Practices with DMVs in the auditorium before lunch.  Here's the slidedeck and info for my presentation. The slidedeck has also been uploaded to the SQLSat Pensacola schedule page. The lunch WIT session with Rie, Monica, and Christine was also full of wisdom and truths.

It was great to see #SQLFamily again this weekend, hope to see you next time!

Next up is SQLSaturday Birmingham, then Baton Rouge on August 17!

SQLSaturday Pensacola 2019 Coming Soon, See You There

One of my favorite (and my first) SQLSaturday events, Pensacola is this weekend June 29. Check out their lineup and info! After that I'll be at SQLSat Birmingham, then my hometown SQLSaturday Baton Rouge on Aug 17. Hopefully everyone in the region can make it out to all these great SQLSaturday events.

At SQLSaturday Pensacola, both my wife and I will be presenting, then catching up on reading and relaxation. I'll be presenting in the morning on SQL Admin Best Practices with DMVs, and she'll be presenting (a much more popular topic) for current and future leaders, Giving Feedback: How to Effectively Communicate to your Employees. I'll also be part of the Careers in IT - Pensacola 2019 panel in the first timeslot.

We'll definitely both be joining Rie Irish for Women in Tech: Thriving Not Just Surviving as well.

SQLSat Pensacola is also sporting a pair of precons on the Friday before, including Zero To Dashboard by Angela Henry and Managing and Architecting Azure Data Platform by fellow good looking bald guy John Morehouse.

Looking forward to #SQLFamily again this weekend, hope to see you there!

Actual Emails: Will MSOLEDB work for connecting to an Availability Group?

Scenario:

We need to configure an existing legacy application from an external vendor to talk to our new SQL Server 2017 Availability Group, which spans multiple subnets. In the end, that last bit is the key. The old data provider MSOLEDB will work for connecting to single-subnet Availability Group listeners, but won't work consistently when connecting to a multisubnet Availability Group's listener. The key is the ability to specify MultiSubNetFailover=True in the connection parameters.

Client's software vendor:

The connection used is the Microsoft OLE DB Provider for SQL Server that is supplied by Microsoft to create the Data Link Properties. When configuring our Data Link, we use the "Microsoft OLE DB Provider for SQL Server". The connection string is formatted: 

Provider=SQLOLEDB.1;Password="whatever";Persist Security Info=True;User ID=username;Initial Catalog=Test;Data Source=ServerName 

Answer:

Good info, but we do need to make a change here. SQLOLEDB is the provider from back in the SQL 2000 era. Do not recommend its use for new development. It has been replaced by the Native Client (SNAC), which has since been replaced by MSOLEDBSQL (I linked below).  It should be easy and transparent to upgrade the provider from SQLOLEDB with no negative impact.

Here’s why we need to upgrade the data provider to talk to our SQL Availability Group. The SQL Server Listener for a multi-subnet Availability Group actually has two IP’s. When you perform at a command line:

Nslookup SQLListenerNameWhatever 

You get back an IP in each subnet (in our case, two), for each replica SQL instance in the Availability Group. 

When a connection string uses MultiSubNetFailover=True and connecting to the Availability Group Listener name (not the IP or either SQL Server instance name), BOTH IP’s are tried simultaneously and immediately, and the driver talks only to the IP that replies: the primary replica.  After a failover, the other IP begins to reply immediately, and so there is no delay in reconnectivity when a failover occurs. 

Without specifying MultiSubNetFailover=True, your application will (essentially randomly) pick ONE of the two IP’s for the Listener, and try it. There is no way to “rig” one IP to be first consistently over time. If it picks the primary replica, everything works! If it picks the IP for the current secondary replica… your application’s connection timeout will have to expire and then try the next IP.  This is why I’m bringing this up – the application will timeout upon SQL login without MultiSubNetFailover=True.

This hasn’t been an issue with your other clients if they aren’t using a multisubnet availability group. If they have an Availability Group all inside only one subnet, then the Listener only has one IP in DNS, and MultiSubNetFailover=True isn’t required.

You should be fine to install the MSOLEDBSQL provider released in 2018 and use that in your data link. Obviously it should be tested for due diligence, but it should work. At the very least, you could try instead the SQL Native Client 11 (SQLNCLI11), which was released for SQL Server 2012, and it also should work just fine for both OLEDB or ODBC.

Let me know if you have any more questions.

Connection string information for SQL Server Availability Groups:

• The SQL Native Client (SNAC) Sqlncli10 will NOT work for connecting to a multisubnet Availability Group because it cannot specify MultiSubNetFailover=True. Must use the newer SQLNCLI11, however, the SQL Native Client (SNAC) is no longer maintained by Microsoft, so you should instead use the new discrete OLE DB or ODBC drivers.
• Recommend convert your connection strings from sqlncli1n or sqloledb, to MSOLEDBSQL. OLEDB has been un-deprecated via the new MSOLEDBSQL provider released in 2018. Download the new driver here if needed: https://www.microsoft.com/en-us/download/details.aspx?id=56730 (Note that of the two identically-named files, the 64-bit one is the larger file size of the two.)
• There is also the ODBC Driver 17 for SQL Server provider, and the newer ODBC Driver 18 for SQL Server.

• In SSIS, the new MSOLEDBSQL OLE DB driver appears as "Native OLE DB\Microsoft OLE DB Driver for SQL Server". The old OLE DB driver is "Native OLE DB\Microsoft OLE DB Provider for SQL Server". 

Note: you also need to make sure your Windows Cluster has RegisterAllProviderIP's set to ON for a multisubnet Availability Group!

Edit March 2023: Added latest OLE DB/ODBC information.

Thanks for joining us at SQLSaturday Dallas!

Thanks for joining hundreds of SQL pros from around the country today at UTDallas!

Here's my session downloads from my presentation today at 1:15 in room 1.102, there's a handy .zip file in there with all the contents for easy reference. They have also been uploaded to the SQLSaturday Dallas website and downloadable from the schedule page.

Why a 101-level presentation? One of the pieces of feedback we consistently have received over the past 10 years of SQLSaturday Baton Rouge is there's not enough entry-level content. This is why I try to deliver low-level entry ramp style presentations for jobseekers, job switchers, and students at SQLSaturdays. And it's not something "beneath" me, because these foundational, fundamental principles are important for new careers and, at the edges, I still learn things that round out my knowledge and further my own career.

Is @william_a_dba giving a presentation on security permissions or singing opera? Or both? #SQLSatDallas pic.twitter.com/bYFwbsmFn2

— Christine Assaf (@HRTact) June 1, 2019

Near capacity here for @HRTact at #SQLSatDallas. I'm not saying she drew three times the number of people that I did, but all her people are awake too. :/ #NotBitter #NotJealous :) pic.twitter.com/F4hQAUaV5i

— William Assaf (@william_a_dba) June 1, 2019