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:
- Know how much data it’s OK to lose and how long your databases can be offline
- Know who has the ability to drop a database in your production SQL Servers
- Monitor your production SQL Servers
- Use x64 architecture for production SQL Servers
- Be ready to back up or restore a database
- Have a plan if your SQL Server stops working (even if the plan is to turn off your cell phone)
- Document who’s next in line if you stop working (even if the person next in line is nobody)
- Remember to lock your workstation when you get up from your desk
As for the “nevers”, I’ll let you come up with that list.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
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.
Would they respond with howls of laughter? Or would your licensing rate just go up?
Never assume! Otherwise keep your resume updated and put the word assume under skill set.
MS just announced SQL Server will run on Linux. Who needs Unix 🙂
https://www.microsoft.com/en-in/server-cloud/sql-server-on-linux.aspx
Never listen to a VAR company’s SQL Server recommendation when implementing Microsoft Dynamics AX.
The same rules apply for Dynamics GP! Our Var was better then the rest but I we did not follow their guidelines 100%
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.
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 !
Oh. Yeah. Been there, done that oops!
Another one: Never pluralize an acronym with an apostrophe.
I’m sure this is one of my many regular sins against grammar.
Never put the word ‘TEST’ in your Production SQL Server Name and/or SQL Instance.
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.
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.
Note: that predates the existence of sequential GUID’s…
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…
Never let anyone (developer, manager, or in between) get away unquestioned with sentences containing the words “assume” or “should”.
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!
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.
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