In any environment where you have multiple DBAs and a heavy workload, there exists the chance for mistakes to be made. When those mistakes are made on the production system, bad things can happen. Depending on what your production system does, whole shifts of people could be idled while the problem is corrected.
Everybody makes mistakes. Even the most careful people can run a query on the wrong server because they are tired and overworked. We don’t always have the option to sleep or step away from the computer. To help avoid mistakes, blocking avenues for them makes the most sense. Below I am going to outline a few ideas for segregating your DEV and PROD environments, and I would welcome any comments with additional suggestions.
Separate accounts for production access
The accounts your DBAs (or others with elevated access) use to access production server should be different and – importantly – non-overlapping with the accounts they use for other environments (both should be Windows domain accounts.) This will prevent the AD account used on your PC from having direct access to production servers. You have to take a second step (run as…) in order to connect to prod. This affords some protection, but I suggest pairing it with…
Access production servers via RDP only
Whether your RDP directly into the production box, or into an intermediate box , this offers protection against those who might choose to leave a Run As… session of SSMS (or Visual Studio) open on their desktop at all times for quick access to production. A firewall rule to block TCP access on the SQL ports, but allowing RDP port access should help lock this down tight.
Separate Service Accounts
Best practice is to have a separate service account for each instance of SQL server running in your environment. I have yet to work for a company that does this, but at a minimum the accounts used for your production servers should be different than the ones for your other tiers. This helps in a couple of ways: First, if you’re installing a new dev or test tier server and bungle the password a couple of times, you don’t lock out your production service account. Second, it prevents you from accidentally targeting production with a SQL Service from another tier, like a SQL Agent job or Replication (which also uses SQL Agent Jobs).
The ultimate lockdown: HOSTS files/Network Segmentation
Preventing any connections from your other tiers to your production tiers can be taken a step further by adjusting the HOSTS file entries on your dev/qa/preprod tiers so that any connections to production servers are re-routed into a black hole. In a small environment, this might be all that is necessary. In a larger environment, it is much easier to set up separate VLANs and implement rules preventing traffic initiation from your lower tiers to production. This prevents someone from setting up a linked server connection to production from a lower tier and mucking about with data that way. Not that anyone would ever do that.
Introducing this level of prevention in your environment can cause headaches beyond just the hurdles needed to access production. What about when you want to try to compare schemas between production and development? Or do data comparisons? Isn’t this just a huge pain?
Yeah, it’s a pain. But it doesn’t have to be huge. Some schema comparison tools will let you run comparisons against backup files instead of live DBs. Some of them will also create schema snapshots, so you can archive those and compare schemas offline. Data comparisons get a little trickier, but I would argue that those tricks help keep your environment much less vulnerable to an oops, and oopses can be VERY costly.