Eight Things A DBA Should Always Do with SQL Server

I started tweeting various single-words you could start an argument with the other day. I really liked this suggestion from @SQL_Kiwi:

It got me thinking. What are the things that I would say you a DBA should always do or be ready for with SQL Server, unconditionally?

You should always:

As for the “nevers”, I’ll let you come up with that list.

Previous Post
Oracle Flashback: Undeleting Data
Next Post
We’re Hiring a Salesperson

44 Comments. Leave new

  • Never enable auto-shrink.

  • Another good one to start discussions among DBA’s, sys admins, storage admins and pretty much everyone in IT…. Virtualization

  • DBA should always set job notifications to notify on failure in production.

    • Except if it’s a developer’s job. The last thing I want as a DBA is job failure notifications for non-DBA jobs.

  • Never ask your Microsoft Rep when they will have SQL Server for Unix.

  • Never listen to a VAR company’s SQL Server recommendation when implementing Microsoft Dynamics AX.

  • Never leave your ‘sa’ password blank.

    That’s about the only one I can think of that may not have a ton of rebuttals, besides:
    Never hit your server with a sledge hammer, despite the occasional desire to do so.

    • Kendra Little
      January 8, 2015 2:52 pm

      Sometimes you just have that Office Space kind of a day. I guess that’s what printers are for.

      • Funny that. I’m from Dallas and have met Mike Judge several times. We have talked about our worst or the worst IT contracting jobs and laughed about a shared hatred for all Fax machines. Joked more than once that someone should play Frog Baseball with the Fax Machine to teach it a lesson. After one of these sessions me and a friend realized we knew where one of these Fax Machines lived and how to get to it.

        NEVER commit a hate crime against a Fax Machine. Especially while intoxicated.

    • How about: never use SQL Authentication?

      • Tis unfortunate when vendor’s apps require SQL Authentication.

      • Why?

      • Scenario for using SQL Authentication (please inform if invalid):
        I’m a developer and accidental DBA. I’ve created a SQL Authentication account for DBA sysadmin tasks, while my integrated security account is lumped with all other users for testing.

        • Alex – people often create a separate AD account for that, and then remote desktop into a different machine when they do domain-admin-level work.

          • Thanks Brent. That was my initial feedback as well; the AD administrator said he’s against setting up an additional “test” account for me, so I’m making due for now, but I’ll add it to my growing list of issues to readdress.

  • Never: Run a bunch of code without reading it OR
    Assuming that you are running it against a test instance !

  • Another one: Never pluralize an acronym with an apostrophe.

  • Never put the word ‘TEST’ in your Production SQL Server Name and/or SQL Instance.

    • Kendra Little
      January 8, 2015 3:06 pm

      I shouldn’t laugh, because someone probably learned that the hard way, but…. oh wow, that’s funny. And true.

      • A “SysAdmin” with no SQL experience should never set up a SQL Server… (everything and I mean everything on C:)

  • Pushing the edge maybe, but never use a GUID as a PK/clustered index on a highly transactional OLTP db.

    • James Anderson
      January 9, 2015 10:47 am

      I wouldn’t say never on that as there may be a requirement to maintain a unique Id across multiple tables/databses/systems. A sequential GUID can be a bit better.

      • That requirement (unique ID across many systems) is why the architects at my previous employer chose to use a GUID, and also why it was an absolute nightmare to fix once we had exhausted the other options to improve perf.

  • Never assume the backup was done on the production side by another member of your team before you start making major changes.

  • Never give developers sys admin access. Ever. (ooh, stirs pot!!)

  • Never assume you know all there is to know about SQL Server

    • “Never assume you know all there is to know about SQL Server” – Because the Developers with SysAdmin are the true experts.

      • You can solve that by…

        If you are the DBA, also become the Developer and vice versa…

  • Never use a consultant’s data conversion SSIS package.

    Never trust a vendor who says his application maintains data integrity without the use of foreign keys, triggers, contraints, or enforced natural keys.

    For all things in life – never use a cunning plan when a simple one will do.

  • Never assume the backups will restore…

  • Philip Kelley
    January 10, 2015 8:52 pm

    Never let anyone (developer, manager, or in between) get away unquestioned with sentences containing the words “assume” or “should”.

  • Matthew Holloway
    January 11, 2015 10:28 pm

    Never assume that the staging database is not actually the production database just because someone was supposed to update the connection string during the last roll out.

    • Ooooooo OUCH!

      • Matthew Holloway
        January 12, 2015 2:39 pm

        Oh aye, my first and biggest mistake in SQL (3 months into first experiences with SQL), I deleted what should have been an obsolete database several months after a roll out.
        Consequently I now take DB’s offline before deleting them and we now take backups of our staging servers… even though in theory dbs should never live here longer than a day.

        • Matthew Holloway
          January 12, 2015 2:47 pm

          A colleague was able to restore everything except the last hour or so and some schema changes with some fancy restores from the prod database that was not actually being updated and the log files we had from being in full recovery mode.
          It wasn’ t pretty. There was a fair bit of shouting and cursing the new guy. It was a lesson in never assuming and always having more backups than you think you will ever need.

  • Never turn your back on a running Sql Server. Back Slowly Away.

  • Never delete the tempdb. It’s not temporary