如何搭建SQL Server存储过程的开发环境？
我最近开始开发一个遗留应用程序，该应用程序的大部分业务逻辑都位于分布在两个或三个 SQL Server 数据库中的存储过程中。目前所有代码也是在实时环境中编辑的。
由于我是代码库的新手，我真的不想在实时环境中进行任何更改，因此我正在尝试建立一个开发环境。我们使用 Visual Studio 编写所有数据库对象的脚本，并在 Subversion 中跟踪这些对象。 这样我就可以设置一个开发 SQL Server 实例来工作，但一个问题是代码充满了对服务器和数据库名称的硬编码引用，例如许多存储过程如下所示：
CREATE PROCEDURE server1.db1.dbo.proc1 AS
INSERT INTO db1.dbo.table1
EXEC server2.db2.dbo.proc2
END
我怎样才能以安全的方式更改所有这些引用？到目前为止，我想到了两个选择：
1）使用一个脚本来运行全局搜索并替换我的 subversion 工作副本上的服务器名称，然后针对我的测试数据库运行修改后的代码。
2) 通过编辑 \windows\system32\drivers\etc\hosts 文件在我的本地机器上设置 dns 别名，该文件将 server1 和 server2 重定向到数据库的开发实例。在生产服务器上，这些将指向生产数据库。
您认为以下哪一个设置更好？您是否发现我忽略的任何风险以及应该考虑哪些风险？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果所有引用都是链接服务器“Server2”等，那么您可以在开发盒上为该链接服务器指定不同的底层 SQL Server
您可以执行以下操作之一：
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:
正确的解决方案似乎是删除对生产服务器的所有硬编码引用（如果可能的话）（您的解决方案 1）。在生产环境和开发环境之间共享服务器数据库是不常见的（除非这是一个单独的数据库，例如仅存储静态/参考数据）。仅当您进行跨服务器工作时才需要该服务器 - 根据 GBN，您可以添加 sp_addlinkedserver
DNS 别名路由是危险的。如果出现任何问题，您将从开发环境中更新生产数据。
旁注：如果您有权使用 Visual Studio DBPro 等工具，您还可以使用变量来标记您的脚本
例如引用一个表，例如 $(SERVER).$(DATABASE).dbo.Table
部署项目时，将替换适当的环境值。
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.