Another instance to develope on.

At the moment, we don't have a development server and develop on our
production server. We develop on "DEV_MyDatabase" and then deploy changes to
"MyDatabase".
This has 2 problems: (1) linked views from other databases have to change,
and (2) sometimes we accidently go into the wrong database.
I have a few ideas:
(1) Setting up a 2nd instance of SQL Server on the production server and
using that instance as the development server.
(2) we have another server that we use for Biztalk, so it has all the
Biztalk databases on that server. I could use that server as the dev server,
but I'm worried about developers going into Biztalk databases. I could creat
e
another SQL Server instance on that server and use that as the dev server.
But i'm not sure how that will affect Biztalk.
If anyone has any advice, please let me know. I have never worked on 2
instances of SQL Server on the same server, so I'm not aware of the pros and
cons.
Thanks,
CraigHi
Totally separate environments are the best solution, now-a-days hardware is
relatively cheap! A second instance is safer than using you alternate
database, but it will require more resources from the production server or
biztalk server depending which one is hosting it. Another option would be to
give the developers their own local copies of developer edition, but
maintenance and syncronisation may prove a high cost.
John
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:BDBD9077-2CF4-4FFC-A89E-4D255D3AB9C9@.microsoft.com...
> At the moment, we don't have a development server and develop on our
> production server. We develop on "DEV_MyDatabase" and then deploy changes
> to
> "MyDatabase".
> This has 2 problems: (1) linked views from other databases have to change,
> and (2) sometimes we accidently go into the wrong database.
> I have a few ideas:
> (1) Setting up a 2nd instance of SQL Server on the production server and
> using that instance as the development server.
> (2) we have another server that we use for Biztalk, so it has all the
> Biztalk databases on that server. I could use that server as the dev
> server,
> but I'm worried about developers going into Biztalk databases. I could
> create
> another SQL Server instance on that server and use that as the dev server.
> But i'm not sure how that will affect Biztalk.
> If anyone has any advice, please let me know. I have never worked on 2
> instances of SQL Server on the same server, so I'm not aware of the pros
> and
> cons.
> Thanks,
> Craig
>|||In my view to do any non-trivial SQL Server development effectively
requires a minimum of three environments: Development, Test and
Production. Check out SQL Server Developer Edition, which is only $50
for development and testing use. For that minimal cost you can run
separate server instances for each non-production environment.
Now which of these will go on physically separate servers or virtual
servers or share the same server is going to be determined by the
nature of your development effort and the size of your budget. I would
suggest that dev should always go on a separate server or at least a
virtual server away from any production application.
Test should mirror production as far as is possible. If your production
is to run on a default instance then you should test on a default
instance. If production is a named instance then test on a named
instance.
David Portas
SQL Server MVP
--