How would you handle cross database queries in different environments. For example, db1-development and db2-development, db1-production and db2-production.
您将如何处理不同环境中的跨数据库查询。例如,db1-development和db2-development,db1-production和db2-production。
If I want to do a cross-database query in development from db2 to db1 I could use the fully qualified name, [db1-development].[schema].[table]. But how do I maintain the queries and stored procedures between the different environments? [db1-development].[schema].[table] will not work in production because the database names are different.
如果我想在开发中从db2到db1进行跨数据库查询,我可以使用完全限定名称[db1-development]。[schema]。[table]。但是,如何在不同环境之间维护查询和存储过程? [db1-development]。[schema]。[table]在生产中不起作用,因为数据库名称不同。
I can see search and replace as a possible solution but I am hoping there is a more elegant way to solve this problem. If there are db specific solutions, I am using SQL Server 2005.
我可以看到搜索和替换作为一种可能的解决方案,但我希望有一种更优雅的方法来解决这个问题。如果有特定于db的解决方案,我使用的是SQL Server 2005。
2 个解决方案
#1
7
Why are the database names different between dev and prod? It'd, obviously, be easiest if they were the same.
为什么dev和prod之间的数据库名称不同?显然,如果它们是相同的话,它是最容易的。
If it's a single table shared, then you could create a view over it - which only requires that you change that view when moving to production.
如果它是共享的单个表,那么您可以在其上创建一个视图 - 这只需要在转移到生产时更改该视图。
Otherwise, you'll want to create a SYNONYM for the objects, and make sure to always reference that. You'll still need to change the SYNONYM creation scripts, but that can be done in a build script fairly easily, I think.
否则,您将要为对象创建SYNONYM,并确保始终引用它。你仍然需要更改SYNONYM创建脚本,但我认为这可以很容易地在构建脚本中完成。
#2
0
For this reason, it's not practical to use different names for development and production databases. Using the same db name on development, production, and optionally, acceptance/Q&A environments, makes your SQL code much easier to maintain.
因此,为开发和生产数据库使用不同的名称是不切实际的。在开发,生产和可选的验收/问答环境中使用相同的数据库名称,使您的SQL代码更易于维护。
However, if you really have to, you could get creative with views and dynamic SQL. For example, you put the actual data retrieval query inside a view, and then you select like this:
但是,如果您真的需要,您可以通过视图和动态SQL获得创意。例如,您将实际数据检索查询放在视图中,然后选择如下:
declare @environment varchar(10)
set @environment = 'db-dev' -- input parameter, comes from app layer
declare @sql varchar(8000)
set @sql = 'select * from ' + @environment + '.dbo.view'
execute(@sql)
But it's far from pretty...
但它远非漂亮......
#1
7
Why are the database names different between dev and prod? It'd, obviously, be easiest if they were the same.
为什么dev和prod之间的数据库名称不同?显然,如果它们是相同的话,它是最容易的。
If it's a single table shared, then you could create a view over it - which only requires that you change that view when moving to production.
如果它是共享的单个表,那么您可以在其上创建一个视图 - 这只需要在转移到生产时更改该视图。
Otherwise, you'll want to create a SYNONYM for the objects, and make sure to always reference that. You'll still need to change the SYNONYM creation scripts, but that can be done in a build script fairly easily, I think.
否则,您将要为对象创建SYNONYM,并确保始终引用它。你仍然需要更改SYNONYM创建脚本,但我认为这可以很容易地在构建脚本中完成。
#2
0
For this reason, it's not practical to use different names for development and production databases. Using the same db name on development, production, and optionally, acceptance/Q&A environments, makes your SQL code much easier to maintain.
因此,为开发和生产数据库使用不同的名称是不切实际的。在开发,生产和可选的验收/问答环境中使用相同的数据库名称,使您的SQL代码更易于维护。
However, if you really have to, you could get creative with views and dynamic SQL. For example, you put the actual data retrieval query inside a view, and then you select like this:
但是,如果您真的需要,您可以通过视图和动态SQL获得创意。例如,您将实际数据检索查询放在视图中,然后选择如下:
declare @environment varchar(10)
set @environment = 'db-dev' -- input parameter, comes from app layer
declare @sql varchar(8000)
set @sql = 'select * from ' + @environment + '.dbo.view'
execute(@sql)
But it's far from pretty...
但它远非漂亮......