Tuesday, December 18, 2018

Actual Emails: What is a "soft delete"?

What's a "soft delete", and why should DBAs be aware of such behavior in tables?

So, a DELETE statement is a “hard” delete. The data is gone.

However, it is a common practice to build into tables a set of auditing fields…

For example:
CREATE TABLE dbo.whatever(
Id int identity(1,1) not null primary key,
WhateverInt int not null,
Createddate datetimeoffset(0) not null,
Createdby varchar(250) not null,
Modifieddate datetimeoffset(0) null,
Modifiedby varchar(250) not null,
IsActive bit not null CONSTRAINT DF_whatever_IsActive
The IsActive field, or anything similarly named like IsArchived or IsDeleted, is a common strategy to “soft” delete something and remove it from resultsets, but still retain history. All the queries on the table would then use WHERE IsActive = 1 to make sure they only viewed Active data, for example.

So, if you UPDATE a record to IsActive = 0 instead of DELETEing it, it disappears from queries and reports and screens, but it’s still there in case it’s useful. There may be some queries that intentionally want to query IsActive = 0 for historical or auditing purposes. 

Often, a filtered nonclustered index (introduced in SQL 2008) can be used to match the WHERE IsActive = 1, and then the index is smaller as a result and allows SQL Server to dramatically increase performance here. This is really only effective if a majority of the data has actually been soft-deleted.

For example: 
ON dbo.whatever (WhateverInt) 
WHERE IsActive = 1
We had one client with an IsArchived flag (or similar) on a table with tens of millions of rows. Only 1% of the data was “active” and waiting to be archived. Their queries specified WHERE IsArchived=0, but still performed very poorly – lots of data still to be accessed. We added and modified some nonclustered indexes as filtered indexes to account for IsArchived = 0, and not only did this greatly reduce the size the indexes, but the query result time went from minutes to instantaneous. Huge difference. We could also have added similarly-filtered nonclustered columnstore indexes.

DBAs need to understand table design and recognize "soft delete" keys and the associated business logic in applications, because the Missing Indexes feature and other common query tuning tools won't include them in recommendations for filtering.

Furthermore, we can even enforce filtered uniqueness using a unique nonclustered index. In this way, we could make sure that there is only one active record for a given key set, but allow many inactive records for the same key set!

Tuesday, November 06, 2018

No Speaker? No Problem! Host a non-traditional User Group meeting

Was honored to speak to fellow User Group leaders at the PASS Summit User Group leader meeting on Tuesday. Here's detail and links to some of the things I mentioned. Thanks to everyone who chimed in at the end provided their own ideas to the room of user group leaders from around the world!

First, consider having a joint meeting with a shared speaker with other user groups, like a .NET or Analytics UG in your area. Share sponsor, food, speaker, networking. More critical mass of fellow professionals, more ROI for sponsors, more potential future speakers and volunteers.

Second, make sure you ABC - Always Be 'Cruiting - new speakers, attracting people to get their feet wet for the first time with technical speaking. Make sure the atmosphere of your group remains approachable, constructive, and not intimidating to new speakers. Make sure you are always reaching out directly to new speakers, offering short-format speaking timeslots at your meetings, and offering mentoring and constructive feedback.

Non-traditional meeting ideas: I'm not claiming credit for creating any of these ideas, they've been executed by others for sure, in fact, I've tried to link to them when possible.
  • "Game shows" - Most fun if game shows actually have individuals at play, not just "ask the crowd" style
  • SQL Jeopardy! using PowerPoint - Not hard to make at all, just what it sounds like. Don't forget to phrase your answer in the form of a query!
  • I have a slidedeck for three rounds of Jeopardy! from SQLSaturday Baton Rouge 2016, happy to share
  • Here's another Jeopardy! game shared by the Baltimore SQL Server User Group
  • Here's another from Chad Crawford of the Utah SQL Server User Group
  • SQL Family Feud - Chad from the Utah County SQL Server Users Group also put out an online survey and delivered the data to anyone who requested it in January 2018. - SQLSat Dallas did it this year for a big finale as well, or, ask around to collect your own "surveys"!
  • There are a lot of list games that are fine for group play, including SQL-themed games in the style of Listography and the Game of Things, like topics like: write down as many data types as you can in the next 20 seconds, or "things developers say", or "most common table name"!
  • Lead an interactive Normalized Database Design for a topic that many people know the business rules for, or, for a brand new business case with a guest product owner - https://www.sqltact.com/2013/01/try-database-design-exercise-at-your.html
  • Lead an interactive "choose your own" Database Corruption Recovery Challenge from Steve Stedman's blog series - http://stevestedman.com/server-health/database-corruption-challenge/
  • Speaker Idol - self-explanatory - well in advance of the meeting, put the call out for "short-format" speakers only, ask bosses, sponsors, spouses etc. to be judges.
  • Licensing - this is not a common topic - ask a local software reseller to send a SQL licensing specialist to talk and do Q&A for an hour, can be very informative.
  • The Toolbox - ask everyone in advance to bring handwritten tsql or powershell scripts from their own "toolbox" to share and briefly demo
  • Networking Night - invite a wide array of people to give short-format talks on career, interview, soft-skills, invite attendees to bring resumes. In BTR, we also add this meeting as a Gold tier SQLSaturday sponsorship
  • New speakers night - another short-format idea, invite only new speakers to give talks, with experienced speakers primed to give constructive, positive feedback
  • PASS Summit knowledge transfer - invite everyone who attended the PASS Summit to give a recap/best-of style presentation to do knowledge transfer to everyone else.
  • Panel of Experts - again, make sure your group remains constructive, approachable and non-intimidating. But a panel of "experts" for Q&A might be a great idea to attract user group attendees with name recognition. 

Emphasizing STEM Community Education at your SQLSaturday

Was honored to speak to fellow SQLSaturday Organizers at the PASS Summit SQLSat organizer meeting on Tuesday. Here's an outline, detail and links to some of the things I mentioned.

In 2018 we celebrated our 10th annual SQLSaturday Baton Rouge event. Several years ago, our event leveled off as far as attendees go, around 500 per year, pretty steady.

One of the reasons we are a small town with a big SQLSaturday is that we are not an insular SQL Server event. Sure, we attract tons of SQL Server sessions, and the name is SQLSaturday, but we've reached across technology lines and now, across community lines. Look, it's not like we're partnering with Oracle here, we're still very much the game in town when it comes to SQL Server and business intelligence knowledge, including helping to launch a second local PASS chapter, the BR Analytics User Group.

A few years ago, we started coming up with a theme for the event, that generally dictated little but our swag. One year a brand new STEAM magnet high school was opening near LSU campus in Baton Rouge, the fancy new campus was being constructed. We decided to have a "building careers" theme, thus the bulk order of construction helmets with SQLSaturday stickers on them.

We kept going from there, wondering how more we could get involved in the community, what could we as a gathering of hundreds of IT professionals do for a city and a state that, aside from the new high school, was dis-investing in public education, healthcare, infrastructure, especially in north Baton Rouge which is just a few miles north of campus.

So we started collecting money for the Foundation for the public school system, with the help of an organizer, we got in touch with the local Star Wars cosplayers. So yeah, we made a loose alliance with the Empire and so far we've raised a few thousand for the Foundation for the EBR School System.

We've helped to spread the word on the Futures Fund, which is an organization that provides digital arts and web design and coding training to underprivileged youth in Baton Rouge, with training from local professionals in the community. We gave opportunities to their organizer and instructors to speak at our Friday night speaker's banquet and a time slot to discuss their important mission and strategy on Saturday.

This year, with help and advice from organizers of kid's tracks in Dallas and Jacksonville, we hosted our first STEM Kid's Track. It was a logical addition to our goal of getting more involved in our community, it attracted speakers and attendees alike to make it a fun parent-child Saturday. I'm hoping to expand and look for more new ideas to get involved in the local community, especially to add opportunities for young and disadvantaged kids a learning opportunity to share with their parents.

We're hardly experts at this. One of the reasons why we partnered with STEMupBR was to have volunteers, actual professional educators, using well-prepared plans for teaching stop motion animation with Minecraft and coding with littleBits inventions. Similar to the strategies we apply every day in our jobs, we didn't reinvent the wheel here, and we were fortunate to find awesome volunteers, and we let good people do what they're good at.

We're not perfect, and we're not done at reaching out and trying to make SQLSaturday as meaningful as possible for volunteers, attendees, sponsors, and the next generation of IT professionals.

Thanks for reading, but in short, here's some ideas for your SQLSaturday:

  • Reach out to local STEM education programs and initiatives. Offer them free space to do their thing in your event, with a built-in audience of tech savvy or tech enthusiast families
  • Reach out to the local public school system to see if they have any weekend or after-school programs that could easily be adapted to a one-day event.
  • Reach out to nonprofits and STEM education initiatives in your city. Face it, as an organizer for a large and free tech event, there are folks in NGO's and outreach organizations would will listen to you and are eager to partner. Partnering with a free event like SQLSaturday could be valuable to them when it comes to their grant-writing.
  • Reach out to local tech community charitable arms and foundations, who are always on the look-out for tech-focused ways to donate time and effort. If they're anything like the Foundation at my employer, they're also not just looking for places to donate money, but opportunities to get their employees involved in STEM education.
  • Reach out to STEM and STEAM-focused schools, technology incubators, and tech entrepreneur organizations, offer them a free booth in your sponsor area in exchange for volunteers, supplies, or curriculum.
Best of luck to you and your fellow organizers as you efforts to give back to your local STEM community.

Tuesday, September 11, 2018

SQL Admin Best Practices with DMV's at the Houston SQL Server UG

Thanks to the full room of SQL enthusiasts who joined me today for the September Houston SQL Server UG Group Meeting! What a great crowd that enjoyed the hospitality at Improving and some great discussion and networking.

It was great to meet everyone, this is the first time I've spoken to the Houston SQL UG despite my relatively close proximity but I hope it's not the last!

Here's a link to the slidedeck: https://github.com/williamadba/Public-Presentations/blob/master/HASSUG/DBA%20Best%20Practices%20with%20DMVs.pptx
and a link to the toolbox of scripts that I mentioned throughout the presentation: https://github.com/williamadba/sql-server-toolbox

Monday, August 20, 2018

Organizer's Thoughts on our STEM Kid's Track at SQLSaturday Baton Rouge

New this year to #SQLSatBR that was really nice for us - we hosted a kid's STEM track leveraging an existing children's education organization operated by the STEMupBR program inside the Foundation for EBR School System.  This is also the same organization that we have raised money for at SQLSatBR for the past four years.

Professional educators and their Americorps VISTA volunteers and our volunteers hosted 25 kids thru robotics, stop-motion animation, and LEGOs thru the day. Some kids had a parent in tow, some had a parent enjoying the rest of SQLSaturday. In the last timeslot we had a "showcase" for the kids to show off what they had created. Parents spent the final session of our SQLSaturday with their kids, and it was far from formal and far from another boring Saturday!

It was awesome, the kids would NOT stop talking about the cool things they created. For other #SQLSat organizers, contact the local school districts to see if they have a STEM program, or contact local WIT organizations, children's museums, or coding camps to see if you could partner for an all-day kid's track.

We had speakers bring kids from out of town, we had an attendee drive in from Houston with his daughter, and volunteers/organizers register their kids too. We will grow it next year because it was an inspiring addition to our event.

Originally I wrote the above as part of my PASS Post-event Form answer for SQLSaturday Baton Rouge, then turned it into a twitter thread. But I've expanded it somewhat here, so some thoughts on this part of SQLSatBR from the organizer's point of view.

We charged a small $10 fee per child to register because:

1) We wanted to start small this year, only 20, but then increased it to 25 when we had huge demand, and so we wanted those who registered to show up.

2) to cover snacks and consumable supplies, like printed materials and creative materials the kids used in the pictures here.

We're not pioneers here, we benefited from the wisdom and experience of others.

We're not the 1st, other SQLSats have done this before, and Devin Knight from SQLSat Jacksonville gave me some valuable tips. But, not operating it myself (as the SQLSaturday organizer) - instead partnering with pro STEM educators - seemed super convenient for me and effective for the kids. Partnering with a professional STEM organization like STEMupBR also gave me, the SQLSat organizer, peace of mind.

STEMupBR handled registration, parent forms, payment, refunds/cancellations. They were smart about having parents sign photography consent forms, had appropriate snacks and supplies, and a great grasp of the gear the kids were to play with.

Organizations STEMupBR, which was started with a grant from the White House US2020 grant program in 2014, also wants to partner with larger events like SQLSaturday and other STEM-oriented community events, as opposed to trying to stand up their own independent STEM events. SQLSaturday is a perfect opportunity to further their mission with a pre-primed audience of STEM-savvy parents and kids.

So you're an organizer, what's the toughest part about organizing a STEM kid's event this?

It's the volunteer adults. Let's face it, this is challenging. Professional educators have training, tools and tactics for working with kids on this stuff that your everyday Professional DBA or Dev does not have.

It's even more challenging at high school age levels, especially if you try to teach coding. It's hard to find professionals willing to dedicate their time to teaching coding to kids - though the ones who do it are AWESOME and should be thanked at the top and bottom of every hour. And it's hard to keep kids engaged if your content is not appropriately challenging.

This is why our STEM Kid's track at SQLSaturday Baton Rouge will likely never offer age levels above the 8th grade (not high school) - because the content and teachers needed to keep that age level interested and not make it "too simple" are hard to wrangle. On the other hand, it's often that scholastic organizations require volunteer hours of high schoolers - this would be a great opportunity for them to volunteer to help teach younger kids under the supervision of STEM education professionals. We'll advise that if you have an older kid in school, send them along to volunteer with their younger sibling, for example.

Between LSU, the Visit Baton Rouge local visitor's bureau, the Louisiana Technology Park, and now the STEMupBR program from local the school district, we have really worked some good partnerships with SQLSaturday and other organizations in the Baton Rouge community.

Photographs in this blog post used with permission from STEMupBR.

Saturday, August 11, 2018

SQLSaturday Baton Rouge thank yous

Hello everyone who was at #SQLSatBR this year!

We had a huge early morning wave of attendees this morning that inundated our sponsors just like a cooling rain inundated us in the afternoon. We had 25 kids in a STEM track that featured robots, magnets, stop-motion animation, and Legos. We showed off the awesome and much-needed work of the Futures Fund in Baton Rouge, we bragged about our Tiger, and we raised $1420.05 to donate to the Foundation for the East Baton Rouge Parish School System.

Oh, and we delivered 70 hours of free training to 487 new and old friends, enjoyed delicious jambalaya, then gave away an XBox One and more cool stuff at the end-of-day raffle. We were aided by an army of volunteers, sponsors from far and wide, and representatives of the Empire.

If you're a volunteer, I thank you, you made this thing happen, you cleaned up afterwards, you carried and sweated and smiled.

If you're a speaker, I thank you, you traveled thousands of miles, you prepared for hours (including the hours immediately prior to your session), you shared your enthusiasm and experience.

If you're a sponsor, I thank you, you helped us provide a free lunch, snacks, drinks, 55 technical books, speaker supplies, printed materials and much more to our attendees.

If you're an attendee, I thank you, you asked great questions and laughed at jokes and cheered and clapped and took notes and networked.

If you're a kid, know that our industry of Information Technology needs you, regardless of your color, gender, sexuality, schooling, economic status, or creed, and that there's a whole bunch of us working to make our workplaces inclusive, efficient, and successful places for you to build your careers.

See you next year, friends.

Monday, July 16, 2018

SQLSaturday Baton Rouge at LSU on August 11

We're a little under a month away from #SQLSatBR!

Registration is required!
We still have tables of all sizes available for sponsors!

Our 10th annual SQLSaturday features an 14-track lineup of speakers from around the country, assembled by our scheduling extraordinaire Kenny Neal. Here's the categories:
  • SQL Server Administration
  • SQL Server Beginner
  • SQL Server Development
  • .NET Dev
  • Business Intelligence
  • PowerBI
  • PowerShell
  • Windows Server/Virtualization
  • Career
  • CIO/Executive
  • Professional Development
  • and for the first time, StemUP Kid's Track (REGISTRATION and SMALL FEE REQUIRED)

Who attends SQLSaturdays?
What can SQLSaturday do for my career?

This event is entirely free for attendees (please pre-register), including a jambalaya lunch, and tons of raffle prizes to giveaway at the end of the day. Everything is paid for by our amazing sponsors.

Please pre-register if you plan on attending! Please also consider signing up to be a volunteer as well, and our expert volunteer coordinator Adrian Aucoin Jr. will put you to work, probably just by being a proctor and attending the same sessions you were going be in anyway. And we still have of sponsor tables available in the atrium to be a part of this local community-driven event.

Have questions about sponsoring? Check out our informational sponsorship PDF explaining all the benefits, costs, the day-of experience, and the process of getting involved in one of the largest and broadest SQLSaturday events in the world. Have questions? Please reach out to us via email at sqlpassbr@gmail.com.

Thursday, July 12, 2018

Baton Rouge User Groups Networking Night

What a great evening of interesting presentations, professional networking, and Jambalaya!

We had 5 speakers last night on topics covering: Availability Groups (me), software documentation, leveraging the SQL Server community, front-end design principals, and augmented reality!

Folks in attendance included people brand new to the User Groups, students, job seekers, hiring managers, junior-level to senior-level DBA's and Developers, bosses and their employees! Plus we had jambalaya from Pot & Paddle, our same vendor for SQLSaturday Baton Rouge.

I have uploaded my opening presentation, Availability Groups in 10 Minutes, to my Github.

Folks stayed over an hour afterwards networking!

Saturday, June 23, 2018

SQLSaturday Houston!

Thanks for joining me for a full room and a boatload of quick-delivered SQL 2017 information in the second timeslot today at SQLSaturday Houston 2018! Lots of great questions, especially about adoption of SQL Server 2017.

Quick recap of this answer question: outside of a third-party vendor's support, there is no good reason to upgrade to SQL 2014 or SQL 2016 instead of SQL Server 2017. As of this week, 2017 has been patched eight times. In fact, because of the new options available to deal with rare issues with the Cardinality Estimator changes in SQL Server 2014, it's easier to upgrade to SQL Server 2017.  Unfortunately, software vendors don't always respond to new SQL Servers to "certify" and "support" their software on new versions of SQL Server, or, they use support for new versions of SQL Server hostage along with new versions of their own software. This isn't cool, and to be clear, there isn't any technical reason to go with SQL 2014 or SQL 2016 as opposed to SQL 2017.

It's awesome to see so many great (and new!) sponsors at these events, helping keep alive the spirit of free community tech training. Be sure to thank sponsors and volunteers for making a day like this happen, trust me, it's appreciated!

Download of my slidedeck is available here and has been uploaded to the SQLSaturday Houston 2018 schedule site.

Thanks to light-up lanyards, the attendees could track me in the dark

Tuesday, June 19, 2018

SQLSat Houston This Weekend!

The wife and I are making the road trip this weekend to SQLSat Houston and we're both speaking!

This SQLSaturday Houston is going to be great - looking forward to seeing the #SQLFamily, many for the first time since Hurricane Harvey. I've been helping clients get back on their feet ever since and I hope to hear lots more stories of successful recovery.

I'll be giving my presentation on What's New In SQL Server 2017 and handing out a couple complimentary copies of the Microsoft SQL Server 2017 Administration Inside Out book that inspired the presentation.

Complete schedule: http://www.sqlsaturday.com/766/Sessions/Schedule.aspx

Sunday, May 20, 2018

Thanks for joining us at SQLSat Dallas!

Engaged crowds and great questions at both my morning sessions on Saturday morning at SQLSat Dallas, an event put together by a team of expert SQLSat organizers, including SQL Server 2017 Administration Inside Out co-author Mindy Curnutt.

Thanks to everyone who attended (and especially those who attended both!) my back-to-back sessions, What's New In SQL Server 2017 and Think Like a Certification Exam. Thanks again to UserGroup.TV, you can watch a recording of my first presentation: http://usergroup.tv/videos/whats-new-in-sql-server-2017-2.

I've uploaded my presentation slide decks for download to my Github and also to the SQLSaturday.com schedule website.

Again, asking only the tough questions.
Regrettably we won't be able to make SQLSat Pensacola in June, but we will see the #SQLFamily at SQLSaturday Houston on June 23 and then of course at our hometown SQLSat Baton Rouge on August 11. Baton Rouge's Call for Speakers is open for another 10 days or so, submit a session today!

Tuesday, May 15, 2018

See you at SQLSat Dallas!

Looking forward to making the road trip on Friday and joining the #SQLFamily at SQLSat Dallas 2018 this Saturday. It's my first SQLSat Dallas since my last trip to SQLSat Dallas in November 2013, and I'm looking forward to seeing everyone again.

I'll be speaking twice in the morning, back to back, with my two newest presentations: What's New In SQL Server 2017 and Think Like a Certification Exam.

Also check out presentations from fellow Baton Rougeans Thomas LeBlanc and John Wells!

Saturday, May 05, 2018

Thanks for joining us at the Spring Houston Tech Fest 2018

Congrats to Michael, Eric, John, all the organizers and volunteers, and all the speakers for a successful Spring Houston Tech Fest 2018. It was a pleasure for the wife and I to get back into speaking at this conference, and oh did we, with a combined six hours of quality Assaf material. It was also great to see a presence from local schools and IT companies, including a whole bunch of folks from Improving. As a local tech conference organizer myself, a big personal thank you to Improving for bringing a host of speakers.

Thanks to usergroup.tv, video of my first presentation, "What's New in SQL Server 2017", is available online.

This was the first time I presented my "Think Like A Certification Exam" session, based on my experience as a writer and SME for the SQL Server exams since 2012. Thanks for everyone who attended and asked so many great questions. And best of luck to all of you who are still students as you launch your careers! Maybe just wait a few years before shooting for the MCSE. :)

I also presented my "What's New In SQL Server 2017" session, gave away a copy of SQL Server 2017 Administration Inside Out, and was joined again by my colleague Steve Schaneville for "Twilight TimeZone: Handling Time in Your App Architecture", our delve into the murky depths of handling time zones in your applications.

All the slides from all three presentations are available for download here.

During my "Think Like a Certification Exam" we asked the hard questions. 

Tuesday, May 01, 2018

Ready for Houston Tech Fest 2018

Houston Tech Fest is this weekend!

In 2016, there was a wedding. In 2017, rescheduled at the last minute because of Harvey, I couldn't reschedule. But prior to 2016, I had spoken at Houston Tech Fest going back to 2010 (at least, memory fails). I'm really happy to be returning to this annual event in its new season and venue, this weekend at the San Jacinto College Central Campus in Pasadena, TX.

Since you're familiar with SQLSaturdays, Houston Tech Fest will feel quite familiar, though with a broader technology scope. Register for this free Saturday training event today: houstontechfest.com

I'll be speaking in three time slots:

Session 1: What's New In SQL Server 2017

Session 3: Think Like a Certification Exam

Session 5: Twilight TimeZone: Handling Time in Your App Architecture (jointly presented with fellow Sparkhound Principal Consultant Steve Schaneville)

My wife Christine will also be speaking three times at the event, and will almost certainly draw more folks to her sessions than any of mine will. Not jealous!

See you there!

Wednesday, March 14, 2018

What's New In SQL Server 2017? at the March '18 Baton Rouge SQL Server User Group

Thanks to everyone to attended the BRSSUG meeting tonight at our sponsor's location at the Baton Rouge Alliance Safety Council location and their awesome classroom training facility.

See you next month at the BRSSUG/BRDNUG meeting back at our usual location at the Technology Park.

All of our speakers recently at the user groups are local speakers, some speaking for the first time. It's great to see new local speakers growing into the role! Looking forward to hearing more from these and more home-grown tech speakers in the future!

Here's the slidedeck to download for my presentation of What's New in SQL Server 2017, let me know if you have any questions or followup.

Wednesday, March 07, 2018

SQL 2017 Administration Inside Out - link dump

Hello all-

More than a year's work went into the newest Inside Out book by Microsoft Press, I was happy to see it cross the finish last year and go into publication in February.

Here's a link dump of all the things in and around the book:
The writing team, in cover order:

Thursday, February 22, 2018

Activate Conference 2018: Presentation info

A special hello if you're visiting this blog post during or after my workshops on Thursday afternoon, February 22 at the Louisiana Tech Park as park of the Activate Conference 2018!

Here are all the links, downloads, and more you need for these two workshops on Thursday, Feb 22:

Databases 101 for the Aspiring App Dev
Student Track
2:45 PM - 4:15 PM

Your Applications and modern SQL Server 2017
Professional Track
4:30 PM - 6:00 PM

Links to get setup with SQL Server:

This is the link to download SQL for FREE:
The tools to dev in the Microsoft ecosystem are all free:
Download the WideWorldImporters sample database:

Presentation Downloads:

Slide decksDownload link  (.pptx files) 

ToolboxGithub (.sql files)
  • Look for toolbox.zip for an easy download, or 
  • Click on each file then download, or 
  • Click on each file, click “raw” on each file to copy/paste

Wednesday, February 21, 2018

Activate Conference 2018: Three days of Tech Learning

Tomorrow I'll be giving two workshops at the super-slick-branded Activate Conference 2018 in Baton Rouge at the Louisiana Technology Park, a conference filled with people and topics much cooler than me and my two database workshops on Thursday, February 22.

Activate is an event that aims to enrich our local tech community by establishing and furthering the careers of individuals in the web and technology field.

3-Day registration for the whole conference, which includes a day of workshops, a hackathon, and a Saturday conference, is free. VIP Admission is also available which includes a t-shirt, swag bag and kickoff dinner (tonight!).

On Saturday, Sparkhound will be hosting a table with our second annual Lego deconstruction competition, with an prize for the fastest times!

See you there, and best of luck to the organizers: Isral, Quinton, Lynsey, and all their many helpers and volunteers!

Thursday, February 15, 2018

"What's New in SQL 2017?" at the Baton Rouge SQL Server UG meeting

We had a great joint meeting tonight of the Baton Rouge SQL and .NET User Groups at the Louisiana Tech Park, and I gave the first version of a growing presentation on new features of SQL Server 2017 (with quite a few new SQL 2016 features in there for good measure).

Thanks to everyone who attended from both user groups!

Link to the presentation slidedeck here (including all the hidden slides I couldn't get to tonight).

I included a question from our SQL Sever 2017 Administration Inside Out author "fun" survey, you can read the whole survey on my blog.

Thanks to Thomas Leblanc for photo

Tuesday, January 09, 2018

SQL Server on TLS 1.2: Checklist to disabling TLS 1.1 and 1.0

A common finding in security audits these days is the failure to conduct all communications via TLS 1.2. (Correspondingly, a common cause for sudden SQL Server application connectivity failures is a sysadmin's inadvisable, reckless deactivation of TLS 1.0 and 1.1 on a server. Been there.)

News August 2023TLS 1.0 and TLS 1.1 soon to be disabled in Windows, raising the importance of being aware of any outdated connection providers that are reliant on TLS 1.0 or 1.1. Make an effort to update to TLS 1.2 or 1.3.

Moving SQL Server connections to TLS 1.2 is not solely (or even mostly) a SQL server change. We need to get all application/vendor developers in the loop to make the transition to TLS 1.2, apply a lot of .NET version-specific patches, and more.

Disabling TLS 1.0 and 1.1 on the Windows Server that runs the SQL instance is definitely something a lot of security-sensitive folks are wanting to do (what's TLS anyway?), but they’re often hamstrung by the applications connecting to the SQL server, or by features inside SQL Server itself that have been configured to use legacy algorithms or version settings.

Contrary to some opinion out there, connections will not use the lowest common denominator allowed by the server and the application's client. Connections will use TLS 1.2 if possible. This is usually a limitation of the application connectivity client or .NET framework version. But if you want to prevent (and therefore break/expose) connections from using TLS 1.0 or TLS 1.1, you need to disable TLS 1.0 and 1.1. This is the only way to make sure you're sniffing out the insecure connections.

We definitely need to test this out in pre-production before any production changes. The list of things needed to get onboard with TLS 1.2 could be lengthy, but it’s a worthwhile endeavor. An initial checklist to consider:

  • If using SQL Server prior to 2016, patch SQL Server. Info here
  • Any clients that use the .NET framework and ADO.NET connectors will need to get up to .NET Framework 4.6 to use TLS 1.2. See the "Additional fixes needed for SQL Server to use TLS 1.2" in this same link as above. There are patches for other connectivity platforms like ODBC and JDBC as well that are needed for both the client and servers. 
    • The clients/webservers/appservers, and the SQL Server will ALL need these .NET patches. There are patches needed for frameworks starting with .NET Framework 3.5 for TLS 1.2, again see link above.
    • For example, SQL Server Database Mail still uses .NET Framework 3.5 SP1, which needs a specific patch to allow TLS 1.2. See above link for OS-specific links.
  • You need to change registry keys on the Windows Server.
    • On operating systems prior to Windows 8/Server 2012, to enable TLS 1.2 you need to add keys, and modify existing keys to disable TLS 1.0 and 1.1. 
    • Starting with Windows 8 and later and Windows Server 2012 and later, TLS 1.2 is already enabled, and you need to add registry keys to disable TLS 1.0 and 1.1.
    • Keys here
    • Or, you can use a free tool like Nartac IIS Crypto to manage the registry edits for you.
    • Note that a reboot is required to make the registry changes take effect.
  • Anything in SQL Server that is encrypted using MD5 algorithms should probably be changed anyway (certificates, database keys, and endpoints for example) but it’s definitely going to be required for TLS 1.2. In fact, starting with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated.
  • SQLOLEDB will not receive support for TLS 1.2, so some connections using the OLEDB driver (as opposed to ODBC, Native Client, or ADO.NET) will need to be rewritten. should be replaced by the new MSOLEDBSQL OLEDB driver, released in 2018
    • The 'Microsoft.ACE.OLEDB.12.0' provider continues to work, apparently. 
    • Linked server connections using @provider='SQLOLEDB' will continue to work, because this actually uses the 'SQLNCLI' provider, the SQL Native Client. Edit: The SNAC is no longer maintained, this should also be migrated to either the new MSOLEDBSQL or the new ODBC drivers actively maintained.  The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 and SQL Server Management Studio 19 (SSMS).
    • For ODBC connections, I recommend using the latest ODBC Driver for SQL Server, not the ODBC functionality of the SQLNCLI/SQLNCLI11 or SQL Native Client (deprecated).
  • For SSRS, we also need to make sure SSRS is using only HTTPS. Again the above .NET framework components need to be updated on the SSRS server and application servers. More info here.      
Update: Next up? SQL Server 2022 introduced support for Transport Layer Security (TLS) 1.3 when TDS 8.0 is used

How can you tell what version of TLS is currently used for client connections? See my companion blog post on this topic.

Edit: Updated for news about MSOLEDBSQL re: connection providers.

SQL Server on TLS 1.2: XEvent session to catch TLS in use

How can you tell what version of TLS is currently used for client connections? Unfortunately there isn't a handy queryable column in sys.dm_exec_connections, which would be ideal, though there is a Connect item requesting this there is a feedback item requesting this.

For now, you can detect the TLS version with an Extended Events session starting with Service Pack 1 for SQL Server 2016 and Service Pack 4 for SQL Server 2012. You'll see the event "trace" in the channel Debug, which returns information from the SQL Network Interface (SNI) layer. (Note that the Debug Channel is not checked/displayed by default in the SSMS Extended Events search dialogue.)

See example below from SSMS.

Start your new XEvents session, then look for captured SSL Handshake events (function_name = Ssl:Handshake), which will contain the protocol, cipher, cipher strength, hash algorithm, and hash strength.

See example below.

Unfortunately, this event does not capture any other information about the login except for the Peer IP Address at the end of the "text" field, what you see in the screenshot above. Application and client information hasn't been exchanged yet with the SQL Server, so other fields are not populated. You can filter out the other SQL Network Interface (SNI) noise with a filter on your Extended Events session, to find successful and failed handshakes and their protocol.

See sample TSQL script to create the session below.


ADD EVENT sqlsni.trace(

    WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%')))
You could for example set up a lightweight xevent session to look for TLS 1.0 and TLS 1.1 activity:

ADD EVENT sqlsni.trace(
    WHERE (([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.0%'))
 OR ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.1%'))
--OR ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%TLS1.2%'))
ADD TARGET package0.ring_buffer(SET max_events_limit=(100000),max_memory=(10240))

What should I do to get my SQL Server only using TLS 1.2, and why? See my companion blog post on this topic.