SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple environment DB's on one instance or one instance per environment on a physical server


Multiple environment DB's on one instance or one instance per environment on a physical server

Author
Message
aMSDeveloper
aMSDeveloper
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 71
I m trying to figure out the pros and cons of using multiple environments on one physical server or a SQL instance per environment. So, heres the situation:

For our testing we have like 5 logical environments and the code progression is as follows Initial Integration(II) -> Integration(I) -> System Testing(ST) -> Security(y) -> Performance(P). For each environment I will have the a group of databases (say about 4 - DB1, DB2, DB3, DB4), and these such 4 will exist per logical environment.

I am trying to think of pros and cons of having ONE sql Instance and have multiple db's (so it will be like IIDB1, IIDB2, IIDB3, IIDB4, IDB1, IDB2 and so on) OR have one sql instance per environment, so technically this will be (server\InitialIntegration, server\Integration and so on and each will have DB1, DB2, DB3 and DB4)

So basically ONE instance and have multiple db's or per instance per logical environment.

One pro I can think of if its multiple instances is, I can bring one instance down and not affect others, but what about performance if I had multiple SQL instances, memor pressure etc?

One pro with ONE SQL Instance is, I can move data, code etc between db's easy than between instances.

I would like other thoughts.

Also, mind you, this is ONE physical server in SQL 2012.
AJ@SQL
AJ@SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 309
Well, you need to review a lot of things and also business decisions needs to be kept in mind. Few Pointers:
1. One bad database can cause issue with the entire instance impacting all other DBs as well, so each DB should be maintained very intelligently.
2. Security policies should be strongly setup on single instance as there would be different set of users needing accesses to perform different activities on the databases.
3. Easy to manage a Single Instance then multiple instances.
4. The server configuration (sp_configure) should be configured keeping in mind all other DBs/applications pointing to this instance.
5. TempDB should be configured to make sure it serves the requirement for all DBs on the single instance.
6. Maintenance jobs should be created/scheduled keeping in mind the entire server configurations.

There could be more other experts might suggest, but these things atleast should be kept in mind when deciding.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search