Instance for development and for production on SQL 2005 box

Question

Post reply

Instance for development and for production on SQL 2005 box

H-122523

Hall of Fame

Points: 3667
More actions
November 8, 2006 at 5:29 am

#92699

Hello,
does anybody have any experience with a SQL Server 2005 setup in which two instances are used (one for development, one for production)?
In general I know that it is practice to separate development and production environment, but I am trying to save on license costs, and need to know if it is easy or difficult for a developer to screw up the server in such a way that the production instance would suffer.
tia
Hans

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

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

we have this on one of our servers

there is a dev db and a prod db. developers only have access to the databases and not the server itself

H-122523 Hall of Fame Points: 3667 More actions · Answer 2

Thank you for this reply.

Limiting the access to the database is a good option. It raises a question though: how do you go about when a developer needs a new component (library, activex object etc.) on the server? The component must be tested; suppose it crashes the development instance? Will the production instance be fine? I guess it depends on the component?

Hans

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

I'd say it definately depends on what the developer is trying to do. If I have 2 databases, instances whatever and I have 2 tables with a bunch of records Say 1 million in each table and all of a sudden I use a cross join when selecting into a temp table. I'm thinking I might just affect the Production performance while my badly formed query eats up all kinds of memory...

Also in my current environment we write custom dlls that the applciation expects to be in a certain directory. what happens when I acidentally overwrite the production dll with my severly hosed development one?

But perhaps that's might just be me being paranoid...

If it's licensing you're concerned about, I'd suggest looking into an MSDN subscription or perhaps the MS action Pack. They are cheap compared to what you might spend for a retail license, and they are for devlopment and in house use only, so I'd think that would fit the bill nicely.

To help us help you read this[/url]For better help with performance problems please read this[/url]

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

I believe the original question is the effect of two instances on the same server. It is a good idea to limit the number of connections to the dev instance. In the same time, do not forget to set the database size restriction in case of running out of hard disk space. I cannot see any other reason why we cannot do it.

H-122523 Hall of Fame Points: 3667 More actions · Answer 5

Thank you for this valuable feedback. It gave me some clue about what to take into account when using more than one instance.

As we have MSDN I will certainly make use of that option to limit the licenses.

Thanks again.

Hans

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Licensing should not be a major issue. The second instance just has to be the 'Devlopers Edition', so you just need the OS license. As for 2 SQL instances, Production and Devopment, on the samer server, it can be done but I do not advocate doing it. But if you do, I sugest at least a 4 Xeon CPU server. Now if you are using SQL Server Standard, the most RAM that you can use on the server is 6 Gb - 2 Gb for the OS and 2 Gb for each SQL Server instance. You will need to do some minor configuration of each instance as well for memory. The script below should do the trick:

use master

go

exec sp_configure 'show advanced options', 1

go

reconfigure with override

go

checkpoint

go

exec sp_configure 'max server memory', 2048 -- Mb

exec sp_configure 'min server memory', 2048 -- Mb

---

--- Used in Win 2K3 - in Win 2K3 this is not needed

---

exec sp_configure 'set working set size', 1 -- 0=no, 1=yes

go

reconfigure with override

go

checkpoint

go

Finally you woll need to set processor affinity on both instances. This will lessen. but not totally eliminate, the possibility of one instance hogging too much resource and crippling the other instance. Here's a pretty good MS article on what you need to do: http://support.microsoft.com/kb/298402

Disks, yes more is better ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

A couple of things:

1. If you are working with the instances, they have a different naming conventions, you have to acount for that.

2. Developer edition is a counterpart of the Enterprise Edition, so some features that work in Developer may or may not work in Standard.

3. Name your instance and the database for development something completely different to avoid mistakes

Regards,Yelena Varsha

H-122523 Hall of Fame Points: 3667 More actions · Answer 8

Rudy, Yelena,

Thank you for this information.

Now I am a bit confused; I thought that through the use of AWE I could use 3GB per Standard Edition server?

Hans