July 8, 2010 at 12:52 am
I'm not sure this is the right forum for this question, but here it is.
Is it wise or not wise to use suffixes in the database names in different environments?
I.e.:
"MyDatabase" for production, "MyDatabase_Test" for testing, "MyDatabase_Dev" for development.
I googled on the subject but didn't find much.
I would be pleased to learn from your opinions.
July 8, 2010 at 1:05 am
In my own opinion, and as a standard I've seen in many different environments, I'd prefer the same name in all environments. This allows you to write scripts that "USE MyDatabase" without having to change it. (You can always run your scripts through osql and pass in the database name, but that could be annoying.)
Cross-database code (which I'd discourage as well) might have some trouble if it's different in all environments. Restoring from production into development would be difficult if a lot of the code needs "_Dev" added to make it work.
Out of curiosity, what benefits do you think you might realise by having different names in different environments?
July 8, 2010 at 2:05 am
Out of curiosity, what benefits do you think you might realise by having different names in different environments?
Well, just to mention one: in config-files both the server and the database are specified. Our servernames don't allways have 'a syntax' that shows what environment they are in. In the past, mistakes have been made (luckily just a test application pointing to a developement database). I hope by using a 'readable' suffix (preferable in CAPS) the chances of a mistake are reduced.
Another: SSMS does not show very clear what server you are on (only in the right corner of the 'status line'). Same argument as above.
July 8, 2010 at 6:01 am
In my experience different database names minimize the chances of expensive - some times catastrofic errors and mistakes happening because somebody got confused.
We do have a naming convention that clearly identifies when a database is either Production, Test or Development.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 8, 2010 at 8:31 am
Interesting discussion and not going the way I would have expected.
Since part of having a dev, qa, staging, prod environment seperation is testing the code and testing the deployment process, it strikes me that having to modify the connection string and the deployments at each seperate environment causes no end of trouble. We always use the same name for the db throughout all the environments. Then, we're as careful as we can be about who can connect to which environment and what they can do. We have different service accounts for the apps in each environment so that the QA system doesn't one day suddenly connect up to production.
"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
July 22, 2010 at 5:16 am
Thank you all for your responses.
Anybody knows of other pro's and/or con's?
thnx,
Frits.
July 22, 2010 at 12:16 pm
Pro for same db name throughout: code promotion. It enables the same version of the script to be ran in all environments. Call it good old fashioned CYA but I don't like being on the hook for modifying somebody else's scripts.
use geography_dev --> use geography_qa --> use geography_prod
...from geography_dev.dbo.state --> ...from geography_qa.dbo.state --> ...from geography_prod.dbo.state
_____________________________________________________________________
- Nate
July 23, 2010 at 6:51 am
PaulB-TheOneAndOnly (7/8/2010)
In my experience different database names minimize the chances of expensive - some times catastrofic errors and mistakes happening because somebody got confused.We do have a naming convention that clearly identifies when a database is either Production, Test or Development.
Different server's or instances (DEV/SYSTEST/UAT/ProdSupport) take care of this problem.
I believe it's more common practice to keep the database name the same in all environments.
July 23, 2010 at 7:36 am
Grant's point about who can connect and migrate to production - if this is possible in your environment - is a good practice for avoiding lots of problems.
Anytime you modify code, especially if it's to target different environments, you introduce a variable. You could end up with multiple versions, or 1 version that you'd always have to check.
Its very common to separate duties, especially from an audit standpoint.
Greg E
July 23, 2010 at 7:45 am
Greg Edwards-268690 (7/23/2010)
Anytime you modify code, especially if it's to target different environments, you introduce a variable. You could end up with multiple versions, or 1 version that you'd always have to check.
This is true if you have to alter the code at all.
In our case code is smart enough to learn in which environment is sitting then it points to the right database - no code changes are needed. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply