Not that I’m looking to do this, but that’s the invitation from Raul Gonzalez this month.
This is the monthly blog party where we write on a topic chosen by the host. All topics are tracked at T-SQL Tuesday, and if you want to host, you need a blog and then ping me.
Just Do a Little
I’m going to tackle two sides to this, the administrative/sysadmin side and the development side. I think it’s easy to implement worst practices, especially in busy environments, if you haven’t gained some knowledge and prepared yourself to do better.
The main thing I’ll point out is that it can be hard to implement best practices, or sometimes even decide what is a best practice. However, you ought to be able to avoid the worst practices.
Worst DBA Practices – Poor Setup
One of the things that has often been done in technology, especially in the Microsoft-based world, is accept defaults, get software up and running, and forget to tackle ongoing practices. In particular, there are two things that I consider worst practices: backups and sysadmin.
First, no backups. Above all, even above security, we need backups for our data. If we have those, at least we can recover. When you set up a new database, you ought to ensure you have backups implemented. Right away. I’m saddened that Microsoft hasn’t made it easy to implement this as a part of setup. While you can use unattended setup or dbatools or something, it takes a little prep.
At the very least, schedule Ola’s backup solution in each instance that is set up. At least with USER_DATABASES set, this will pick up new dbs as a backup.
Second, don’t use sa/sysadmin or any privileged account for applications or even DBA scripts. Set up another account that can be disabled, password changed, or some other security measure. Too often people never set up another account and get used to using sa.
Truly a worst practice.
Worst Developer Practices – Starting with SELECT * and NOLOCK
Aaron Bertrand has a number of bad habits posts, which I think are worth reading. If you can’t adopt his best practices, at least avoid the other issues.
Two worst practices I think create technical debt and later problems are SELECT * and NOLOCK. If you can’t do anything else, at least avoid these.
The first (SELECT *) leads to issues with extra data movement across the wire, extra reads in SQL Server, and in general problems with refactoring items as you never know where an application requires certain columns. I know we won’t get perfect, but don’t use SELECT * in any production code. The only place for this is when you want a SELECT TOP 10 * to get a feel for what data is in the table. Every other query in an application ought to specify what columns it needs.
Note: Using SQL Prompt and getting all columns is just as bad. Pick the ones you need.
Secondly, NOLOCK should not be a default item. There are data integrity issues, which can cause you problems down the road. Putting this in often means everyone is terrified of removing it. Don’t start here.