Q&A

Q&A with Denny Cherry: Top Things You Should NEVER Do in SQL Server (and Other Tips)

• By Becky Nagel
• 09/05/2019

SQL Server guru Denny Cherry is going to be telling stories during his upcoming session "Things You Should Never Do In Microsoft SQL Server" at the Nov. 17-22 Live! 360 conference at the Royal Pacific Resort at Universal Orlando. While described as a "fun session," it will impart lessons learned from SQL Server users doing things horribly wrong, resulting in horrible results (don't worry, no one will be named!).

Cherry will use the horrible stories to teach attendees at the session what not to do, and how to make the pain stop if they do things they shouldn't have done. He took some time off from his busy consulting service to answer some questions about his presentation.

You've been called in to fix a lot of bad SQL Server implementations that have gone wrong. Is there one horror implementation you've run into that stands out among the rest?
Probably one of the worst problems that I've seen was thankfully one of the easiest to fix. The application was using an open source scheduler to scheduler things to happen in the application. The open source scheduler stored its data in SQL Server. The columns were different data types than the calling code, and because it was open source there was no one to call and fix it. We simply changed the database tables to match the data types of the code, and the developers promised to never upgrade the job scheduler. I'm guessing in a few years they will have forgotten and I'll get a phone call.

Not to give too much away from your upcoming Live! 360 session, "Things You Should Never Do In Microsoft SQL Server," but what is the No. 1 thing that database admins/IT pros/developers should never, ever, EVER do in Microsoft SQL Server?
People need to stop thinking that they know better than SQL Server how SQL Server works. SQL Server is really good at doing what it does, and trying to get SQL Server to do things your way just isn't going to work.

Is there anything that people think they should never, ever do that are actually OK, either always or in certain circumstances?
Most everything has exceptions. We never ever want to shrink a database, except when we have to.

We never want to enable auto-close, except when we should because for a specific use case it makes sense. Pretty much everything has an exception, knowing when you need to use that exception and when not to is key to a lot of things.

People aren't perfect, and mistakes will be made. Is there anything database pros can do to help mitigate the impact of dangerous errors?
Backups. Backups are the "push here in case of emergency" button.

SQL Server 2019 is set to be released later this year. Is there a particular feature you've seen in the CTPs or that you know are coming that you're most excited about?
The Accelerated Database Recovery feature of SQL Server 2019 is really cool. This is going to help systems come online faster when the system is restarted. I see a lot of people taking advantage of this.

Any features that administrators should be wary of?
Merge replication is still horrible, and should be avoided like it's got the plague. As far as new features go, pausing an index build and not letting it finish would lead to indexes that weren't actually there, so you can't actually use them.

What is your No. 1 tip for getting the most out of SQL Server and Azure together? Is there anything that users should be aware of?
Design things correctly. A lot of the performance problems that I see in Azure come down to not designing things correctly. Sometimes this might be a simple change, sometimes this may need a total application rewrite to make the application more cloud ready.

What role do you see Microsoft's upcoming "Azure SQL Database Managed Instance" playing in the future of Microsoft enterprises?
The new MI is going to enable applications that couldn't go to the cloud because they are vendor apps, or are in some way expecting a "normal" SQL Server, to be able to be moved into the cloud. Not everything should go to MI, some things should be built on traditional SQL DB, but the things that need MI's features should definitely go to MI.

Is there anything about your session or anything else that you wished we asked you that we didn't?
You'll have to come to the session to find out. :)