如何为SQL Server存储过程设置开发环境?

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.

我最近开始研究一个遗留应用程序，该应用程序的大部分业务逻辑分布在两个或三个SQL Server数据库的存储过程中。目前，所有的代码都是在活动环境中编辑的。

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:

由于我对代码库还不熟悉，所以我并不想对活动环境做任何更改，因此我正在尝试建立一个开发环境。我们使用Visual Studio来编写所有数据库对象，并跟踪Subversion中的那些对象。这样，我就可以设置一个dev SQL Server实例来工作，但是有一个问题是，代码中充满了对服务器和数据库名的硬编码引用，例如，许多存储的procs看起来如下所示:

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.

1)有一个运行全局搜索和替换subversion工作副本上的服务器名的脚本，然后针对我的测试数据库运行修改后的代码。

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.

2)通过编辑将server1和server2重定向到数据库的开发实例的windows\system32\drivers\etc\主机文件，在本地框上设置dns别名。在生产服务器上，这些将指向生产数据库。

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?

在你看来，哪一个更好?你认为我所忽略的和应该考虑的风险有哪些吗?

2 个解决方案

#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

如果所有引用都指向链接服务器“Server2”等，那么您可以在您的开发框中为该链接服务器指定一个不同的底层SQL服务器

You can do one of:

你可以这样做:

Use sp_setnetname after creation
使用sp_setnetname创建后
Set the @datasrc of sp_addlinkedserver during creation. This separates linked server name from actual server name
在创建期间设置sp_addlinkedserver的@datasrc。这将链接的服务器名与实际的服务器名分开

#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

正确的解决方案似乎是删除对生产服务器的所有硬编码引用(您的解决方案1)。您只需要服务器，如果您正在做跨服务器工作——按照GBN，您可以添加sp_addlinkedserver。

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

DNS别名路由是危险的。如果出现任何失败，您将从开发环境中更新生产数据。

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.

附加说明:如果您能够访问Visual Studio DBPro之类的工具，您还可以使用变量来标记您的脚本，例如引用一个表，如$(服务器).$(数据库).dbo。表在部署项目时，将替换适当的环境值。

#1