May 6, 2009 at 2:42 am
Hi all,
Hoping for some advice here on the way my databases are joined and how to manage that when promoting schema updates to our test (and ultimately live) environments.
I'll try to give a simple example:
In development, say we have two databases: Database1_Dev and Database2_Dev. In test, they are called Database1_Test and Database2_Test. The live databases are simply called Database1 and Database2.
Although the databases are for two different applications, there are times where I need to join across the databases in a view or stored procedure. For example, let's say Database1_Dev has Table1 and Database2_Dev has Table2. This query is in a view in Database1_Dev:
SELECT *
FROM Table1 t1 INNER JOIN Database2_Dev.dbo.Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
This is obviously a made up example just to illustrate my problem.
This all works fine, but my question is this: when I do my schema updates from dev > test and then test > live, how do I maintain the links to the second database even though it has a different name on test and live? Database2_Dev does not exist on test (it's called Database2_Test) so the application will fall over.
If it's any use, we use Redgate SQL Compare for schema updates, though we may be moving to SQL Delta in the future.
Is there a simple way around this? Should my dev/test/live databases just have the same names in the first place to circumvent the issue?
Any advice would be much appreciated!
Cheers,
Chris
May 6, 2009 at 7:31 am
You don't link directly to them as you are doing here. Instead, you create either Synonyms or Views in your local database that points to them.
I like to do this by having 1) a separate schema in the local database for these definitions, 2) a local table with the list of table names that I wish to cross-link to in the external DB, and 3) an automatic procedure that will accept as an argument the name of the (new) external database and then, driving against the local TableList table will drop any existing cross-DB definitions and (re-)create all of the new cross-DB definitions pointing to the new database.
Then in DEV, I point it to one place, to another place in TEST/QA, and as part of deployment, to its ultimate target in PROD. It's really quite easy once you have that automated procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 7:41 am
Thanks very much for the response - Synonyms are exactly what I was looking for, and coupling it with a bit of abstraction should help me solve my problem.
Chris
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply