What's New In SQL 2019? at the Little Rock, AR SQL Server User Group

Thanks to everyone who joined the Little Rock SQL Server User Group for a night all about SQL Server 2019! We got off into tangents and use cases for Big Data Clusters, columnstore, graph databases. We reviewed cool new syntax and patterns when they'll be used. We laughed at developers who use GUIDs as their clustered indexes. We had pizza, we had iced tea in a mayonnaise jar. Overall a great time!

Kudos to Mahanand and Chase from Vestcom for organizing!

Slidedeck here: https://github.com/williamadba/Public-Presentations/blob/master/BRSSUG/What's%20New%20in%20SQL%202019.pptx

Any referenced "toolbox" labs here: https://github.com/SparkhoundSQL/sql-server-toolbox

Thanks for all the kudos and great feedback on the presentation, will be incorporating it for as long as this presentation is topical!

What's New in SQL 2019? at BRSSUG

Thanks to everyone who joined the BRSSUG for a night all about SQL Server 2019! We got off into tangents and use cases for Big Data Clusters, columnstore, graph databases. We reviewed cool new syntax and patterns when they'll be used. We laughed at developers who use GUIDs as their clustered indexes. We discussed why it's a B+tree not a B-tree. Overall a great time!

Slidedeck here: https://github.com/williamadba/Public-Presentations/blob/master/BRSSUG/What's%20New%20in%20SQL%202019.pptx

Any referenced "toolbox" labs here: https://github.com/SparkhoundSQL/sql-server-toolbox

Thanks for all the kudos and great feedback on the presentation, will be incorporating it for as long as this presentation is topical!

Tips to build a your-departure-proof leadership team in your tech community

This is a fairly poignant issue for many technical community leaders who feel alone in what can be a thankless job. Let's talk about some guidance and tips for fostering and farming fellow volunteers from the user community. As a user group leader for 10+ years myself, and also the spouse of the leader of a successful local activism startup, I hope to share some wisdom here. I welcome any additional feedback from the host of technical user group leaders as well.

I presented these ideas at PASS Summit's Local Group leaders meeting on 11/5/2019.

1. What to look for in a volunteer

   

   Baton Rouge UG meeting

• Enthusiasm is the primary job qualification. Period.
• For any nonprofit, for any volunteer organization, enthusiasm is always the number one criteria.
• Nice to haves: 
• Good written and verbal communication skills.
• Reliable transportation and attendance. 
• Willingness to spend time sending emails and following up. 
• Someone with a network of connections in the technical or local community.
• But really, without enthusiasm, this all falls apart.
• Understand that people get involved in nonprofits for different reasons.
• Some people just want to help, not lead.
• Some people will want to eventually lead. Both are OK and you should encourage either.
• Some people will want to volunteer to meet, to network, to further their career. That's ok too.
• Some folks may be too shy to ask to be involved, or not sure of the protocol. Make sure you regularly announce that you're looking to add more volunteer leaders to the organization, and if necessary, reach out and ask someone. They may be honored to be asked!
2. How to best delegate

   

   Baton Rouge UG meeting

• Creative, heuristic work is more motivational than strict directions. You don't have to provide detailed, step by step instructions or standard operating procedures to people. In fact, you shouldn't. That can actually be stifling to enthusiasm.
• Provide details on outcomes and maybe things like frequency or timing. 
• Give volunteers tasks, let them own it. Make sure everyone knows what they should do, but let them create, with support and followup and encouragement on your part.
• A leader in a volunteer organization doesn't bark orders. Again, ask volunteers do tasks where they can feel ownership, so that they can feel empowered. Creative, even.
• As a leader, you are setting the norms for board membership, setting the bar for what leadership looks like. This looks like hounding sponsors, speakers, sending emails, updating websites, proactively planning, etc., the things you're already been doing.
• When you lead by example cleaning up garbage or a spill, enthusiastic volunteers will see this and follow suit. You've defined the behavior of a leader in this org.
• When it's something easy, simple, or right in front of you, don't delegate it, do it yourself. 
• For example, everyone's job is garbage cleanup duty afterwards, or clean up a spill. Everyone's job is to get their own coffee and lunch. Everyone's job is to move things with a hand truck.
• Stop using any personal or work emails to conduct User Group business. Get organized with shared online tools like a shared group accounts for sending emails, online docs, shared calendars.
• Share relationship contact info for food vendors, facility contacts. Communicate regularly.
• Part of that empowering of your volunteers is going to include listening to their suggestions to change the way things are done, the way things YOU have been doing them. Part of leading a volunteer org is trying out their ideas, and part of fostering fellow volunteer leaders is allowing their ideas to come to fruition.
3. Some anti-patterns and caveats



Some of the Baton Rouge UG Organizers at SQLSatBR

• Someone who thinks they're too cool to show enthusiasm, either doesn't have enthusiasm, or hasn't been prompted to show it. Make sure you show enthusiasm that is contagious.
• People who want to lead after their first meeting... great enthusiasm! But maybe don't assign critical group tasks until they have participated regularly.
• Anyone familiar with the term "slackivist"? The social media sharer and retweeter makes for a great member, but generating that content and showing up to the meetings is what makes for a great future user group leader. A good future user group leader attends, speaks, volunteers, helps move things and be places in addition to being a social media megaphone.  
• Don't throw too much at a new volunteer, regardless of enthusiasm level. Make sure they feel part of a team, not that you are dumping all the workload on them. The internal and external perception should be that a new volunteer is joining the team, with all the benefits of teamwork therein.

The SQLSaturday pitch to host facilities

Not being able to find a good, affordable venue to host a SQLSaturday is a show-stopping problem that is common to a lot of events, especially SQLSaturdays getting started or having to relocate in their city. Let's talk about the sales pitch that we as SQLSaturday organizers can give during initial introductions and conversations with a new potential host venue.

I presented these ideas at PASS Summit's SQLSaturday leaders meeting on 11/5/2019.

1. First off, to review typical meeting facilities:

   

   LSU Business Education Complex atrium, home of SQLSatBR

• community colleges and universities, technical/trade colleges
• training or conference centers
• technology parks or business incubators
• Microsoft Technology Centers
• hotels as a last resort, because of the typically high cost
2. Leverage PASS
• Mention how PASS is an international organization that has hosted over a thousand of these events, include a link. This is a large, leading professional organization for data platform professionals.
• Mention that SQLSaturdays around the world are hosted at major universities, include some that have happened in nearby cities, similar-sized markets, or their competitors!
• Put an emphasis on engaging with the local community of potential students, clients, tech enthusiasts. SQLSaturdays are authentic opportunities to get involved in the local tech community, something that many public institutions of higher education desire.
• Mention the local and (inter)national sponsors at nearby or recent events, and what locations hosted them.
3. Pitching to universities

   

   SQLSatBR sponsor lineup, including facility host LSU
   and our monthly UG hosts, LTP and ASC

• If going after a university or college, try first reaching out directly to faculty, especially faculty who teach databases, business intelligence, or analytics. By leveraging them internally, you can cut through a lot of bureaucracy if the event becomes internally-sponsored.
• Many universities have master's or certificate programs in data analytics and cybersecurity these days. Try to reach out to that program's faculty personally.
• Of course, use your alumni/alumnae relationship, or that of a fellow SQLSaturday organizer, to work inside the academic institution, bypassing red tape.
• Now, if you're talking to someone who manages the facilities for rental, they probably don't care about any of that. They're probably just thinking in terms of rooms, fees, cleaning staff, etc. Try to talk to someone on the instruction or academic side.
• Make sure to state that everyone can attend, not just user group members. The general public, including faculty and students, is welcomed, and that it is free. 
• Universities, especially public institutions, will balk at charging admission.
• Make sure to offer an opportunity for the host facility's faculty, staff, instructors, or experts can get involved to speak with guaranteed slots on the schedule. Worth it if you can get the facility for free!
• Again, maybe don't talk to facilities or building management. If you're an alumni, or know an alumni, work that relationship for a meeting.
• Mention that some other (admittedly, not many) get facilities for free because of the benefit to the academic institution for hosting such an event.
• Being organized as a non-profit entity may help...
• Operating or representing a nonprofit org cannot hurt. In the United States, this is a 501c3 or 501c6. Some organizations only give free space to nonprofits, for example, many public university systems only work with nonprofits, and public libraries do this too.
• Incorporating as a non-profit is a different topic altogether with differences but if you can't figure out your local bureaucracy yourself, for help forming the organization, consider going to a law clinic at a University, or local services that specialize in forming and servicing nonprofits.
• Gilda Alvarez added a great addition to this topic right after I spoke at the SQLSaturday organizer meeting: Work a relationship with a large company's recruiting as an in to the university. In Orlando, Gilda's organizers worked through Deloitte's recruiting arm to convince a university to host a SQLSaturday event, and it worked out very well.
• Use your experience in the field to offer to speak to University clubs, such as AITP or ACM, or Women in Technology groups, or just as a guest lecturer. Consider also contacting and trying to get involved in departmental advisory committees. These are ways to meet faculty and start working on strategic partnership between the school and the SQLSaturday. 
4. Get them involved directly



LSU's booth at SQLSaturday Baton Rouge

• Offer a top-level sponsorship and table to your host organization, free of charge, to help your host facility attract new students or clients.
• Feature them and their message just like any other sponsor.
• Offer a spot on your schedule to any host faculty.
• Offer to put their students on the schedule to give a project showcase of their work.
• Consider scheduling when students are on campus and can attend.
• Mention that your event can help showcase their building, draw community attention to it. Especially if the building or a part of campus is new.
5. Tips for good host relationship
• Bring your own free-standing folding cardboard garbage bins and bags, making garbage cleanup fast and easy, and without overfilling the host venue's small trash cans.
• If possible, haul the garbage directly to a dumpster for the venue, and recycling to the appropriate place.
• Make sure your volunteers team knows that everyone's job is to pick up trash and clean up at the end of the day, and sweep all the classrooms for trash.
• Be sure to thank the host facility at the end of day raffle, publicly, and invite them to join. 
• Use easels or free-standing signage instead of taping signage to walls, doors, or glass. In general, don't tape anything to walls or doors!

Availability Groups: What to do without MultiSubNetFailover = True

I received an email from a client who is having issues with third-party applications connecting to their three-subnet SQL Server Availability Group. After an exchange with Microsoft Support, they discovered that the applications weren't specifying MultiSubNetFailover = True in their connection strings. As a result, because RegisterAllProvidersIP = 1 in the cluster, connections were randomly experiencing high latency upon connecting, as client-side DNS queries over time had a 66% chance of returning the wrong IP from the listener.

They set RegisterAllProvidersIP = 0, but before you take that as advice keep reading. That fixed the connectivity latency problem for now. I'm not sure whether the application connection strings can't or won't be changed to include MultiSubNetFailover = True. This decision was made either because of vendor limitations and/or because of vendor reliance on old data providers. Here was my guidance regarding Microsoft's recommendation to specify RegisterAllProvidersIP = 0 and lower the HostrecordTTL to 120 (seconds):

I guess this is good quick fix guidance but the problem really is on the application side. Assuming those connection strings or providers can’t change, sure. But you lose the ability to failover fast and you will have increased load on DNS servers.

With RegisterAllProvidersIP disabled on the client access point (the cluster network), only one IP address is made available for the listener: the IP of the Listener in the primary replica's subnet. When RegisterAllProvidersIP is enabled, all site IPs for the listener are simultaneously listed. Connection strings using MultiSubnetFailover = True will try all IPs simultaneously and use the one that responds, providing for the fastest possible transition after an availability group failover. That's desirable.

With RegisterAllProvidersIP = 1, caveat remains that any connection strings that aren't using or can't use MultiSubnetFailover will have connection problems. So RegisterAllProvidersIP should be enabled only when MultiSubnetFailover=True can be used in all application connection strings.

With RegisterAllProvidersIP=0, failovers may be problematic. HostrecordTTL = 120 is 2 minutes. Availability Group failovers will result in up to a 2 minute outage, after which time the client OS will query DNS for new information. With RegisterAllProvidersIP =1, failovers can be more or less instantaneous, or at least not hampered by DNS. 

Also by lowering the HostrecordTTL, traffic to and constant load on your DNS server(s) will increase tenfold, as the default is 20 minutes.  What MS has recommended is a short-term solution but it’s not scalable obviously. It’s not getting to the real source of problem – the connection strings. 

It would be much better if we got the third-party applications to change their connection strings to use MultiSubnetFailover = True, and then you could enable RegisterAllProvidersIP =1. This has been around since 2012, it should be available if they are using any modern data provider. If the third party app is still using OLEDB, there is a new data provider for MSOLEDBSQL released in 2018 that works for all previous MS OLE DB implementations. The new ODBC Driver 17 supports Multisubnetfailover, if they’re using ODBC. If the vendor apps are still using something old like SQLNCLI10 (from 2008), or something non-Microsoft, check for newer versions. More info here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-client-connectivity-sql-server?view=sql-server-ver15

Idera GeekSync: SQL Security Principals and Permissions 101

Thanks to Idera and all 239 of you who joined the Idera GeekSync webinar today for my topic on SQL Security Principals and Permissions 101.

Fun fact: Principals and Principles have totally different meanings, but both apply. Principals - in that each login, role, group, user, etc., in SQL Server is a principal - as in an important person, or, in one of the definitions, "a person for whom another acts as an agent or representative." But also, the presentation is about Principles - as in good guidance, or "a fundamental truth or proposition that serves as the foundation for a system of belief or behavior or for a chain of reasoning."

We discussed both! Not only a 101-level intro to authentication, logins and users, but also guidance on migrating and dealing with logins and users, SQL Injection, vulnerability and threat detection, data provider updates, permissions for non-sysadmins, separate accounts for sysadmins, and the nature of permissions in/around SQL Server stored procs, views, and functions.

Got good feedback from Idera on the comments, hope it was a useful and entertaining (albeit fast-paced because of time) review of SQL Server Security and Permissions for you.

PowerPoint available for download here: https://github.com/williamadba/Public-Presentations/tree/master/Idera%20GeekSync

Lab scripts for the sproc/view/udf permissions demo:
https://github.com/SparkhoundSQL/sql-server-toolbox/blob/master/lab%20-%20security%20p1.sql
https://github.com/SparkhoundSQL/sql-server-toolbox/blob/master/lab%20-%20security%20p2.sql

[UPDATE] Idera has posted the recording here: https://www.idera.com/resourcecentral/webcasts/geeksync/sql-security-principals-and-permissions

SSIS Scaleout: Cannot open certificate store on the machine

When attempting to connect a worker node to the current Master node of a SQL Server Integration Services (SSIS) Scaleout, for on-prem SQL Servers, if you receive the error:

Validation Error(s):
Cannot open certificate store on the machine
First off, launch SSMS on the SQL Server instance you intend to be the master. The steps to troubleshoot start with the two steps here:
https://docs.microsoft.com/en-us/sql/integration-services/scale-out/troubleshooting-scale-out#cannot-open-certificate-store

"1. Run Scale Out Manager as administrator. If you open Scale Out Manager with SSMS, you have to run SSMS as administrator.

2. Start the Remote Registry service on the computer if it is not running."

3. Finally, if you are trying to add a named instance, there is one more gotcha. The Scaleout Worker is the server name not the SQL Instance name. Do not provide the full named instance name, for example, "SQLSERVER-1\INSTANCENAME", only provide the server name, in this case, "SQLSERVER-1".

"Think Like a Certification Exam" at the PASS Marathon!

Thanks to the 56 of you who joined and actively answered my nerdy sample cert exam questions in the #PASSMarathon webinar "Think Like a Certification Exam" today. Ya'll had way too much fun answering those things, it was a fun and fulfilling experience to share with the community!

I had a lot of fun presenting both GroupBy and PASS Marathon webinars today, it is certainly a different experience to interact with your audience via the chat box but we made it work and had a lot of fun! Thanks to all of you who participated!

Slidedeck for this presentation is available here: https://github.com/williamadba/Public-Presentations/tree/master/PASS%20Career%20Development%20Marathon%202019

GroupBy: SQL Server Admin Best Practices with DMV's

Thanks for the 40+ of you online for GroupBy 2019 North America, excited to have presented my DMV session for the audience.

Big shoutout to Bob Pusateri's fantastic preceding presentation, which I hadn't seen before, on Locks, Blocks, and Snapshots, that was masterful explanation of the concepts. Thanks also Jeremy Alexander for being GroupBy's v-jay/video moderator/streaming host for entertaining!

Slidedeck: https://github.com/williamadba/Public-Presentations/blob/master/GroupBy%20Conference%202019/DBA%20Best%20Practices%20with%20DMVs.pptx

Toolbox: https://github.com/sparkhoundsql/sql-server-toolbox

SQL Server Admin Best Practices with DMV's at GroupBy

Excited to be presenting at virtual GroupBy Conference for the first time on Thursday, during the North America time slots. I just finished tuning up this old presentation that I've presented dozens of times. It's been a great learning tool for me throughout my career and informative for DBA's and Developers at any level of experience. This time I packed in some new SQL Server 2019 content as well, so I'm looking forward to demoing both my old classic hand-tooled jokes as well as fresh new content.

In all this time though, this is the first I'll be presenting this slidedeck to a virtual audience, so be sure to use the chat window to give me a lol or a groan every now and then. I've already made the slide

deck available here and I'll be ready to roll on Thursday at 1pm Central.

Later Thursday afternoon, I'll be presenting a second webinar, Think Like a Certification Exam, in the PASS Career Development Marathon. Hope to see you online there as well!

You can register for this free all-online conference here. Here's the GroupBy conference lineup:

Europe - October 9th   9:00 UTC | Alexander Arvidsson Boring Is Stable, Stable Is Good. SQL Server Best Practices 10:00 UTC | Magnus Ahlkvist  Eight hours of work in 20 minutes, partitioning rocks 12:00 UTC | Emanuele Meazzo How to use PowerBI as a free monitoring tool (5 free reports for SQL Server) 13:00 UTC | Alex Yates  Solving the dev database problem with GitHub, Docker and PsDatabaseClone 14:00 UTC | Rich Benner  Adventure – Performance Edition

Download Invite

North America - October 10th   9:00 PDT | Aaron Nelson  Azure Data Studio  SQL People 10:00 PDT | Bob Pusateri Locks, Blocks, and Snapshots: Maximizing Database Concurrency 11:00 PDT | William Assaf  SQL Admin Best Practices with DMVs 13:00 PDT | Collin Lysford  Demystifying Dynamic SQL 14:00 PDT | Erik Darling  The SQL Server Performance Tasting Menu 15:00 PDT | Mohammad Darab Big Data Clusters for the Absolute Beginner

Download Invite

"Think Like a Certification Exam" at the PASS Career Dev marathon

Join me for my new webinar "Think Like a Certification Exam" at #PASSmarathon taking place on October 10!

One of my favorite non-technical presentations to give explains how and why exams are developed, who they're for, and the process behind item writing. This will help the potential exam taker, in any technology field including but not limited to Microsoft certification exams. Gain exam insights, the best prep strategies, an test-taking strategies from a veteran exam writer.

Register now for expert-led webinar content through #sqlpass: https://www.pass.org/marathon/2019/careerdev/Registration.aspx #PASSmarathon #CareerDevelopment

I'll be presenting at 6pmCT, and my wife is also giving a certainly more interesting presentation two hours before:

Have you heard? I’m speaking at #PASSmarathon!

Register for my session on
Giving Feedback:https://t.co/aHKyPoPXLV

Don’t miss out
on this FREE learning opportunity October 10! #sqlpass #PASSmarathon#CareerDevelopment pic.twitter.com/q3JcoAPzbQ

— Christine Assaf (@HRTact) September 30, 2019

Good to see you at Houston Tech Fest 2019!

It was awesome to see a great crowd at the triumphant return of Houston Tech Fest 2019. This year's event was hosted at the beautiful Microsoft Office in Houston. Thanks to everyone who joined my presentation on SQL Server Permissions and Security Principals 101 after the lengthy lunch break and learned all about SQL Server permissions, starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and much more.

You can download the slidedeck here: https://github.com/williamadba/Public-Presentations/blob/master/Houston%20Tech%20Fest%202019/SQL%20Security%20Principals%20and%20Permissions%20101.pptx

and reference the toolbox here: https://github.com/sparkhoundsql/sql-server-toolbox

Thanks again to the host employees at the Houston Microsoft Office for opening their doors on the 10th floor to a wide variety of area tech professionals. I met database administrators, sysadmins, Agile Scrum masters, recruiters (even someone looking for a LAMP developer), students, jobseekers, IT managers, and more.

SQL Security Principals and Permissions 101 at September '19 BRSSUG

Great attendance and great meeting last night for the September '19 BRSSUG meeting, great to see lots of new faces at the meeting following our successful SQLSaturday event in August.

My "SQL Security Principals and Permissions 101" presentation is a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and much more. This session is perfect for junior DBAs, developers and system admins of on-prem and Azure-based SQL platforms.

You can download the slidedeck here: https://github.com/williamadba/Public-Presentations/blob/master/BRSSUG/SQL%20Security%20Principals%20and%20Permissions%20101.pptx

and reference the toolbox here: https://github.com/sparkhoundsql/sql-server-toolbox

One note to add for other presenters: this presentation is broad enough to be long-lived. I originally developed this presentation in 2011, but many of the topics are just as relevant today as they were 8 years ago, even though I've added a large amount of Azure content, a section on SQL Injection, and have added/removed a slide or detail here and there for time. Continuously developing a 101-level,  topical presentation like this has been a great driver for my own ongoing education and technical reading. Between this and some other broad topic presentations like my Admin Best Practices with  DMV's presentation, I'm regularly making myself notes on new (or new-to-me) facts and tips to add over the years. I recommend this approach to new technical speakers!

@william_a_dba talking Permissions and Security #batonrouge #brssug pic.twitter.com/BWz1S2EeLi

— John Wells (@SQLServerDBA318) September 11, 2019

My Organizer's Notes and Goodbye after #SQLSatBR 2019

Some 2019 tips for other SQLSat organizers from my last year as lead organizer of SQLSatBR. I hope as a fellow organizer, you can pull some ideas and inspiration for your future events!

• Last year we tried to take an "action" pic of each sponsor booth. We sent a personal email and included those photos to the sponsors this year, with an email "We'd love to have you back at SQLSaturday Baton Rouge 2019!" This year, we included action shots from Saturday in our "thank you" email that also included the scanned raffle badges for those sponsors. It worked to renew a sponsor in a couple cases, and we received some really nice replies to those emails. Examples:

• Always capture booth "action" photos, of conversations taking place and with big crowds in the background/foreground. Use them as great call-outs on social media. Big crowd shots are always winners, tell your photographers! Then, we post all our photos in a shared community photo album for anyone to contribute.
  
• Our Thursday night bag-packing event was just so impressive. Notice our double-barreled, assembly-line approach to packing 500 bags in <2 hours. We also packed our third annual speaker goodie bags as a team that night, which was nice, since I'd done that on my dinner table in past years. 


• In an effort to reduce waste and our landfill impact, we bought biodegradable badge holders this year instead of the slightly cheaper PVC badge holders which are worn once, tossed in a landfill forever. (Biodegradable plastic badge holders aren't as common to find, we bought from kleertech.com and they worked fine with the SpeedPASS PDF name badges that are 4" wide.) We also had our vendor use biodegradable paper-based serving boxes for the lunches instead of standard styrofoam. 

• Be sure to recognize your user group and host facility partners, offer them a special place in the sponsor lineup. We were fortunate in a position to offer both the La. Tech Park and LSU a free booth at our event, as a big thank you for the free host facilities they provide us year after year. We also reached out and provided free booth space to the local Toastmasters group and a nonprofit that offers STEM education to low SES kids in our city.  But it's important that we make sure to reach out and offer our host facilities some recognition and an opportunity to participate, rather than just a transactional facility rental.
  
  Especially if your host is a higher education facility that provides post-grad certificates or degrees, which are major revenue sources, they would likely appreciate an opportunity to staff a booth and get their pitch out. This type of relationship is the type that leads the school to give us the facility for free, because the department is "hosting" our event rather than renting us rooms, so we bend over backwards to thank them.

• We plan on expanding the use of facilitated panel discussions next year, they have been the past three years well attended and an additional honor to speakers and community partners including new potential sponsors. This includes the CIT Panel that we've done for years in partnership with Careers in Tech and our CIO Panels in the past. 

• Feedback from Kenny Neal, our amazing schedule master: "If we could get a drag and drop scheduling module for sqlsaturday.com it would be great. Like a listing of approved sessions on one side and let me drag them where I want them." Our schedulemaster was otherwise very happy with the new features on the admin SQLSaturday site this year. Gotta keep them happy. Big thanks to all speakers who spoke multiple times, some on short notice, and some three times back-to-back-to-back. Speaker dropouts are always a reality and easy to overcome when you have an diverse, experienced speaker lineup from around the country. Our strategy is to accept single sessions from as many distinct speakers as possible, and then add second sessions from their repertoire as needed.
  
• Our Friday sponsor/speaker/volunteer dinner featured live music from the jazz band led by one of our long-time volunteers and a local data professional, Lori St George. Honestly it exceeded all expectations and Lori's Collaboration Band was a huge hit. It was an amazing collection of musical genius until they brought some of our SQLSatBR organizers up on stage for a hard-working finale.
  
  Are there any fun music acts in your local SQL community? Pro tip: don't ask them to play for free. Don't ask any independent/freelance folks to do what they do for free. We paid our tee shirt artist for design time, our band (no website for them... yet), our user group logo designer, and the baker of our pralines and bread pudding. If someone wants to volunteer their time, great, but if you're asking them for their normal services offer to pay their normal rate.
  
• Blown away by all the people who participated in our small remembrance of SQLSaturday volunteer photographer and Baton Rouge community volunteer Carter Spade. Carter tragically passed away in July, leaving a void in many volunteer organizations like ours. Thanks for joining us in that, friends. Thanks for all your help in years past, Carter.
  
• Our second year of a STEM Kid's Track was a hit. I visited the classroom after lunch, and the 30 kids in there were actively typing, plugging away, smiling and laughing, planning and debugging, and definitely the opposite of bored. Produced by the Foundation for the EBR School System, it sold out really fast despite being greatly expanded. There were definitely some disappointed parents. Next year we plan two classrooms, and an expanded partnership with STEM educational organizations in the city including the La. Women in Technology group, who we raised money for at our end-of-day raffle, netting a total donation of $1452.
  
  Someone asked why a few years ago we started emphasizing, featuring, showcasing, and raising money for STEM education nonprofits like The Futures Fund. It's because stuff like this helps the event to be more than "established IT professional training established IT professionals". We cast a wider net, a wider view. I don't want to be a part of an insular same-faces-every-year cycle. That's one of the reasons I have stepped down as lead organizer of SQLSatBR, but will be around to help the volunteers ready to step up.

Why am I stepping down as SQLSatBR lead organizer? There are a few reasons, I'll explain some here. I definitely will continue to support the both our SQLSat Baton Rouge conferences in 2020 as a volunteer, as well as the SQL user groups, as well as my role as a PASS Regional Mentor. But not as lead organizer for the SQLSatBR planning committee.

1. Organizing the SQLSaturday has been really good for me professionally and personally. I've met tons of people. I've exercised skills as a public speaker, decision-maker, organizer, logistics. I've made friends from fellow organizers and volunteers, vendors in the community. I've exercised gratefulness in making sure to thank people as often and as individually as possible. So, why shouldn't others be able to do this? Other volunteers deserve the chance to own a larger portion of the conference we've built together since 2009. The Baton Rouge SQLSat scene is great to have not only a new SQLSat BI event coming in 2020, but also a dozen folks who I think would be perfectly capable and enthusiastic about running the conference, talking with sponsorships, making sure the spending lines up with the fundraising, talking to vendors, etc.
2. First off, we have a ton of volunteer/organizers. I have had a lot of help. I want to make sure a smooth a transition as possible happens, so while I won't be lead, I will still be around. I'll probably be speaking more, leading less. But others deserve, want, and are enthusiastic about filling my shoes. There's no better time, the table is set and a great crew of volunteers is hungry. I left with a tight throat but with no animus, no regrets, and no apprehension that this thing that I have grown and loved will continue on. It'll be different in their way, and that's OK.
3. I'm not sure what I'll do with my freed-up time in the late summers, but I'll figure it out. Don't worry about me. My wife and I have an empty nest and many adventures ahead of us. 'nuff said.

In case I got a little too choked up on Friday night, here's the notes I printed:

Lemme talk a little about SQLSaturday Baton Rouge.
I was just bragging on Twitter about the awesome volunteers we have, literally a couple dozen people show up, new faces and old, to help pack 500 attendee bags and a few dozen speaker bags. We have an amazing venue given to us free via our partnership with the LSU College of Business and the Stephenson Department of Entrepreneurship and Information Systems. We have an artist we’ve known and trusted for years for our poster and tee shirt designs. We’ve got documents, spreadsheets, contact lists. We got shared accounts for email and docs and social media. We’ve got a UPS Store box, we’ve got a high-falootin business bank account with have a dozen card holders and hours of paperwork, we’ve got a legal not for profit corporation. We even got branding for the SQL, .NET and Analytics User Groups to share a common logo concept across all our platforms and signage. We’ve got an amazing team up here, of DBA’s, Dev’s, and Data Professionals, and Microsoft MVP’s and Microsoft employees,
, and one lead singer who is also a data professional.
, and one Oracle DBA but we don’t hold that against Kristen. 

So when I say that 2019 is my last year as lead organizer of SQLSaturday Baton Rouge, I am stepping down from that role, and this is me announcing that to everyone, everyone including my wife, I’m saying it with a high degree of confidence that this thing we’re all doing with our free time in the late summer is in good hands. I’m not vanishing, I’ll be around to help, I’ll probably speak at the event, but this team has got this and we will work deliberately to make sure we’re as successful in 2020 as we have been in the past.
So, enough from me, thank you all, please eat, enjoy the music, see you tomorrow morning.

See you Saturday in Baton Rouge!

See you in Baton Rouge this weekend! We have 13 tracks of professional development and training, from entry-level to expert, for SQL Server Administrators, Business Intelligence Developers, Data Analysts, ETL Developers, C#/VB.NET Developers, Mobile Developers, Windows Server Admins,
SharePoint Architects, SharePoint Developers, Network Administrators, Quality Assurance Analysts, IT Managers, Students, Project Managers, Hiring Managers, Jobseekers of all levels of experience, Students, CIO's and CEO's.

Register here: SQLSatBR.com

Print your SpeedPASS PDF here: pass.org/MYPASS.aspx?viewctl=MySQLSaturday

Signup to Volunteer: sqlsaturday.com/867/Volunteers.aspx

Think Like a Certification Exam at SQLSaturday Birmingham

Thanks for joining us at SQLSat Birmingham! Had a great session with an awesome amount of participation and shared insights, including some very clever test takers.

Congrats and kudos to the organizing team for SQLSaturday Bham, John Baldwin, Samir Behara, and their team of volunteers from many different user groups. Thrilled to see that like in Baton Rouge, Birmingham is having monthly meetings with SQL, .NET, Agile, and PowerBI User Groups assembled together, a veritable supergroup of user communities that is a big draw. This really helps them (and us) gather a critical mass of attendees that helps attract sponsors, increase networking possibilities, keep regular attendees, and attract new ones. Next step? A fully integrated SQLSaturday event, with tracks populated by speakers and attendees from each technical community!

You can download my slidedeck for the presentation here: https://github.com/williamadba/Public-Presentations/blob/master/SQLSat%20Bham%202019/Think%20Like%20a%20Certification%20Exam.pptx

PS How could I have forgotten that there was a cat in Alien AND in Aliens? Gah. That one snuck past my alpha and beta exam testing phases...

This is one of my fav presentations by @william_a_dba. #sqlsatbham pic.twitter.com/XYgHFeOYPV

— Christine Assaf (@HRTact) July 27, 2019

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

See you at SQLSaturday Dallas!

Both my wife and I will be speaking this weekend at SQLSaturday Dallas, back-to-back in room 1.102, and both on topics perfect for entry level IT pros, job seekers, and students. We're honored to be on a SQLSaturday schedule filled with some of the biggest names in the industry, so I hope to see you there! Looking forward to seeing so many #SQLFamily there again.

I'll be presenting on SQL Security Principals and Permissions 101, a ground-level introduction to SQL Server authentication, security principals and even some security principles. We'll go through some code demos about how stored procedures assist the DBA by abstracting the permissions necessary for execution, and how you could design a database security model using role-based, least-permissions pattern. I think everyone can learn something from this session, from DBA's to developers, and how better to secure their databases.

See you at UT Dallas this Saturday, June 1!

Untrusted FK's demo + ADS notebooks

Back in 2012 at SQLSaturday Pensacola, I did a lunch-time lightning talk on indexing, which was supposed to have a big finale on how FK's could silently help with performance, and it didn't work. Didn't show the performance boost. It was because, thanks to poor scripting by SSMS, FK's are enabled but not trusted. This blog post serves as a long-belated knowledge share of a problem I didn't figure out live, as well as a successful recreation of that big finale.

Fast forward to a couple weeks ago at the May 2019 Baton Rouge SQL Server User Group, and local fellow SQL pro John Wells gave a presentation to a mixed audience of dev's and DBA's on performance tips, and one of them was to check up on untrusted FK's. Thought occurred to me to create some lab's to demonstrate not only how to untrusted FK's get accidentally created by well-intentioned dev's and DBA's, but how to fix, and also a demonstration on how they can be silent performance drags. So with all respect to John, Chris Bell who John mentioned in his presentation, and many others who have spoken and written well on the topic already, below is a lab demonstrating the issue, resolution, and performance impact.

To further one more goal, I've built the lab into a tsql script but also a new Azure Data Studio SQL notebook. Either offer a step-by-step introduction and demonstration. New to SQL Notebooks? No worries, they were released generally in March 2019 as a new feature of Azure Data Studio (which can be launched from SSMS 18+), which itself was made GA in September 2018.

A SQL Notebook is another way to combine text instructions with step-by-step, modular executed SQL statements in a single file. Notebooks are a popular form of mixed media, combining text, proofs, queries, and their live interactive output. They are already quite popular tools for data scientists to package methodology, analysis and live output. SQL Notebooks include kernels for querying SQL, PySpark, Spark, Python with multiple languages, and now in preview for SQL 2019 CTP 3.0, working with big data clusters. SQL Notebooks could also be used by DBA's and developers as rich, well-documented runbooks for complex exercises deployment or disaster recovery exercises.

SQL Notebooks are easy to learn and use, consider a SQL Notebook for your next detailed query breakdown or summary data analysis. Maybe you'll learn two things at once!

Github links to the SparkhoundSQL toolbox for the Untrusted FK's lab: .sql .ipynb

PS Check it out, Github even parses the notebook JSON correctly to display a (non-interactive) view of the notebook outside of ADS with the saved resultsets. Neat!

UPDATE: Refined my terminology a little bit to favor "SQL Notebooks" not "ADS Notebooks"

Four Data Integration Design Questions to Ask

We get requests to move data between custom systems regularly, even within systems. I was advising a client on something fairly simple—a collection of tables out of one vendor system to feed another—and I thought I'd make a blog post out of the array of questions that always come up.

Regardless of the strategy for data movement, whether it be:

• SQL Server Integration Services (SSIS) locally or in Azure Integration Runtime (IR)
• Stored procedures
• SQL replication
• Secondary readable Availability Groups
• Azure Data Factory 2.0 (not 1.0, oh goodness, never 1.0)
• Transactional movement featuring message queues or APIs
• Any streaming solution
• ETL or ELT
• Any other kind of transformation I'm forgetting to mention

The following questions should be asked before designing a data movement plan.

(There are no correct answers to these questions of course, but you must be able to determine the answers from the business case.)

1. What is the latency requirement for the changes from the data source(s) to be copied to the destination?
Common answers: Instantly, no longer than 5 min, or 30 min, or nightly.

2. How many rows are expected to change in the source(s) in a given time period? 
Common answers: Anywhere from few rows per month to all/most the rows in a table every day.

3. What types of data changes are performed in the source(s)? 
Is the source data inserted, updated, and/or deleted? 

4. Do we have a reliable way to identify "the delta"? 
How do we know which rows have changed, including hard deleted rows (vs soft deleted rows)?

Let's dive more into the last question, because this is where the design meets the implementation method. There's a reason we always design tables with an IDENTITY column and some basic auditing fields.

First off, a truncate/insert strategy is not scalable. I have redesigned more of these strategies than I can remember, often because of database developer myopia. A truncate/reinsert strategy, even a bulk insert strategy, will inevitably outgrow its time boundary identified in Question 1. Don't waste your time and resources on such a strategy, you need to identify a way to find out what changed the in data source now.

But what if we don't or can't trust the application to always modify a "ChangeDate"? This is certainly the easiest way to know if the row has changed, but what if the original table wasn't designed with such a field? We should consider whether we can alter the data source(s) with useful, built-in SQL Server features like Change Tracking (CT), Change Data Capture (CDC), or a more recently-introduced feature called Temporal Tables. The latter can provide a reliable, built-in modified date and row history, transparent to applications. All of these strategies are well documented and have easy to use labs available.

Each of these solutions is very useful and recommended in its use case, and much preferred over a trigger-based system which will add complexity and overhead to transactions. A "pull" of recent changes is much preferred for most scenarios over a "push" of each change inside the transaction.

Caveats remain however—and this came up with a recent client—the impact on future updates/patches for databases must account for implementations of CT, CDC, or Temporal Tables. The same caveats apply to replication (useful in spots) and database triggers. Don't enable these SQL features without consulting with and advising the maintaining developers on the potential impact and need for testing.

One more crucial factor often overlooked as part of Question 4 are the intermediate transactions, especially in the case of less-than-instant data movement. If a row changes from status 1, to status 2, to status 3, can we just send over the row state with status 3? Or must we apply an insert for status 1, an update for status 2, and then another update for status 3 to the destination? This could be a major problem if the destination has an indirect dependency on evaluating the status changes; for example, to calculate the durations between statuses.

I once designed a data warehouse for tracking the performance of auditors, and we were analyzing the workflow for the bottlenecks in a 20-step process. Each of the 20 steps and its corresponding row state and time stamp were the whole point of the analysis. This demanded some sort of row-versioning in the data source. Not all change detection strategies work for this, however. Change Tracking, for example, would not suffice. Know your solutions!

You shouldn't move forward with any data movement design before answering these questions.

Are there any other common questions you'd ask for before deciding on a plan for a project like this?