June 2, 2004 at 8:08 am
I am soon starting a new project that is going to be on a SQL Server and I want to do it right. Though I understand databases, I do not have a lot of exposure to SQL Server. I really would appreciate any inputs so that I can avoid any trouble in future. I know that I can have multiple instance of SQL Server running on the same machine.
What would be the right thing to do? To have multiple instances on a single machine, one each for development (DEV), test (TST) and production(PRD) or should I just have one instance with multiple databases? In that case I would have DEV,TST and PRD 3 separate databases in the same instance.
When I develop a new SP, which is the scenario under which copying a new Stored Procedure is much simplier?
I come from IBM AS/400 environment where we had a concept of libraries. So we would have DEVDTA, TSTDTA, PRDDTA as 3 different libraries for the database and DEVPGM, TSTPGM and PRDPGM as 3 different libraries for programs.
June 2, 2004 at 3:12 pm
Personally, I would go with multiple dbs on a single instance. I've never used multiple instances on a single machine, but I would imagine there would be a lot of overhead. I think the BEST scenario would be to have dev and test on a separate server altogether.
As far as migrating SPs, I think its a wash. You could connect to the dev/test/prod databases from a single EM or Query Analyzer session, so migration wouldn't be a problem in either scenario.
Steve
June 2, 2004 at 3:25 pm
It would be better if your test database were on a completely separate machine. What if you need to restart the box for some reason or if you do something in test that affects production.
Moving between test /dev and production should be done via scripts IMO so that it is duplicateable.
Francis
June 3, 2004 at 5:48 am
We have DEV and Test on seperate servers. The reason is for SQL Server and Windows service packs. If you want to put any of these on and test its best to have an isolated server so you do NOT effect production... and if something goes wrong production is not effected at all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply