Bes practice for working on a production server

Question

Post reply

Bes practice for working on a production server

YaHozna

Ten Centuries

Points: 1022
More actions
October 15, 2010 at 3:51 am

#143183

In light of my recent howler I thought I would get the advice of my peers as to best practice when working on a Production server.
Said howler occurred the other week when I was testing an ALL SERVER trigger on a development server. During testing I had to break off for quite a while and closed my script file down. On coming back to it some hours later I opened the script, without thinking hit F5, and .... Yep, suddenly one of our production servers started experiencing log-in failures. On everything. I'd created the trigger on a Production server by mistake. Cold-sweat time. Didn't take long to diagnose and fix the problem, which required a DAC connection to delete the trigger, however I am now currently wearing the dunce's hat.
So my question is what is Best Practice to avoid this sort of thing? Obviously trying to be more careful and aware of which server a query script is connected to is a starting point. But that's not always going to happen. I'm only human and accept that i have limitations 🙂 Another option is to perhaps only use a log-in that has limited permissions on Production servers. I bet I'm not the only DBA who finds it more convenient to use a log-in that is a sysadmin on the servers I'm responsible for. In retrospect maybe that's not such a great idea.
What then do other DBAs do to try and prevent this sort of mistake happening to them?
Regards,
YaHozna.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I use a special keyboard without the F5 key. 😛

Seriously, the main thing is avoiding experiments in production.

You experiment on the test server, then move working code to production.

Never, ever use the production server as if it was a dev/test server.

That said, mistakes happen: you can't prevent every possible mistake.

Could sound like a silly advice, but "be careful".

-- Gianluca Sartori

steveb. SSC-Forever Points: 46733 More actions · Answer 2

one thing you could do to make it a bit more obvious what server you are connecting to, is to define custom colours for each server under registered server - properties. This won't stop the mistake happening but if you can easily tell what server you are connected to, i usually have red for production and yellow for dev

grahamc SSCertifiable Points: 6762 More actions · Answer 3

Sadly, I had a junior DBA do the same thing... after that, needed to come with a way of stopping it. So had 2 options:

- Windows authentication for SQL servers except PROD where we use SQL accounts

- 2 Windows accounts, 1 for PROD servers, 1 for all others

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

steveb. (10/15/2010)
one thing you could do to make it a bit more obvious what server you are connecting to, is to define custom colours for each server under registered server - properties. This won't stop the mistake happening but if you can easily tell what server you are connected to, i usually have red for production and yellow for dev

SSMSToolsPack[/url] helps you with this. Give it a try, it's nice and free.

It also has a "new query template" that creates every new query window with a nice "ROLLBACK" command.

-- Gianluca Sartori

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Gianluca Sartori (10/15/2010)
steveb. (10/15/2010)
one thing you could do to make it a bit more obvious what server you are connecting to, is to define custom colours for each server under registered server - properties. This won't stop the mistake happening but if you can easily tell what server you are connected to, i usually have red for production and yellow for dev
SSMSToolsPack[/url] helps you with this. Give it a try, it's nice and free.
It also has a "new query template" that creates every new query window with a nice "ROLLBACK" command.

yeah that is a good link I have tried this out at home at was impressed, however due to IT rules we can't install this at work. Maybe useful to others though..

Oliiii SSCertifiable Points: 5328 More actions · Answer 6

Easy way:

start all your scripts with the word "return" (will save you from time to time)

never open Prod and non-prod server in the same SSMS

Good way:

as said above,use 1 prod account and 1 non prod account (although that's very easy for lazy people to bypass if you have named pipe enabled)

Best way:

Have your boss yell at you the words "Do you want to get fired?!" after your first mistake, you'll never make it again.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Best way to avoid this that I know of, two logins. Have a special login for going to the production system. Then, you have to do a Run As in SSMS or anything else to connect to production. It won't prevent you from doing something stupid, but it does make it quite a lot harder. It's actually a good practice in general too. I have an admin login for my machine, different than my standard login. Keeps me from doing something stupid locally, and helps prevent me from getting hacked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

YaHozna Ten Centuries Points: 1022 More actions · Answer 8

Many thanks indeed for all the sound advice. I will be putting pretty much all of it into practice.

Regards,

YaHozna.