How to set up a development environment for SQL Server stored procedures?

Question

Hi,

I have recently started working on a legacy application that has most of its business logic in stored procedures spread over two or three SQL Server databases. Currently all code is also edited in the live environment.

As I am new to the codebase, I don't really want to make any changes in the live environment and therefore I'm trying to set up a development environment. We're using Visual Studio to script out all the database objects and are tracking those in Subversion. With that I can then set up a dev SQL Server instance to work in but one problem is that the code is full of hard coded references to server and database names, for example many of the stored procs look something like the following:

CREATE PROCEDURE server1.db1.dbo.proc1 AS

INSERT INTO db1.dbo.table1
EXEC server2.db2.dbo.proc2

END

How can I change all these references in a safe manner? So far I have thought of two options:

1) Have a script that runs a global search and replace of the server names on my subversion working copy and then run that modified code against my test databases.

2) Set up a dns alias on my local box by editing my \windows\system32\drivers\etc\hosts file that redirects server1 and server2 to development instances of the databases. On the production servers these would then point at the production databases.

Which of these is a better setup in your opinion? Do you see any risks with either that I have neglected and which should be taken into account?

Answer 1

If all references are to linked server "Server2" etc then you can specify a different underlying SQL Server on your dev box for that linked server

You can do one of:

• Use sp_setnetname after creation
• Set the @datasrc of sp_addlinkedserver during creation. This separates linked server name from actual server name

Answer 2

The correct solution seems to be to remove all hard coded references to the production server, if possible (your solution 1). It is unusual to share a server database between production and dev envioronments (unless this is a separate database storing e.g. only static / reference data). You only need the server if you are doing cross server work - as per GBN, you can add sp_addlinkedserver

The DNS Alias route is dangerous. If anything fails, you will be updating production data from your dev environment.

Side note : If you have access to a tool such as Visual Studio DBPro, you can also use variables to tokenize your scripts will e.g. reference a table such as $(SERVER).$(DATABASE).dbo.Table When the project is deployed, the appropriate environment values are substituted.