April 6, 2011 at 9:57 am
Hi everyone,
I am looking for a 'best practice' and would appreciate any information I can get. Here is my situation...
We backup our live databases and do regular restores to a development server. The databases on the development server have "_Dev" at the end of their names. So let's say I have two databases on the live database named "Orders" and "Email". On our development server the databases are called "Orders_Dev" and "Email_Dev".
There are several stored procedures on "Orders" that cross-database join to "Email" (and vice-versa). When the database is restored on the development server, obviously the cross-database join would no longer work. "Email" is now "Email_Dev".
Currently we have added a step to the restore job that is literally one huge script of ALTER PROCEDURE statements, altering each procedure to use the development database name. As procedures are identified/created that require cross-database calls, we append them to this job step.
This doesn't seem to me like the best way to handle this, so I would love to hear from people to see how others deal with this. Please keep in mind that changing the database names so they match the names on live is not an option. The Powers That Be will not allow it.
Thanks so much!
April 6, 2011 at 10:01 am
My first instinct was to use synonyms but I don't really save any work saved over there. Would be a pain to refactor the application to use them...
April 6, 2011 at 10:04 am
I prefer to keep the database names the same between environments. One reason is the very reason you are describing. If you develop something in that database and then promote it to a QA or prod environment, now you have to change all of those procs again. It just seems easier to me. If you already have a database on that server by that name, then why not add an instance specific for these db restores - again keeps it much more simple.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 10:42 am
Thanks for the feedback; however, changing the names is not an option. While I would love to be able to do that, and had never actually seen a setup where database names are changed to reflect the server they're on, that is the way it's done here. In fact, I am setting up SQL Server 2008 and we have a couple databases that use the same name as on live, and I've been asked to make sure to change the names to "%_Dev", making an even larger mess.
I am stuck with things the way they are, and am just looking for a better way to deal with the database name differences.
April 6, 2011 at 10:47 am
The good thing about synonyms if that they can be rebuilt quite easily with a script. Maybe I'd check out that route.
April 6, 2011 at 10:48 am
I think then using the synonyms as suggested may be a better option. This would require significant rework up front and promote that synonym through to prod, but then lower maint in the long run.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply