In our environment we have different servers for prod and dev, different servers for our external environments dev and prod and corresponding different AS400's for dev and prod. Our application code in the database includes OPENQUERY statements and four part object addresses. These constructs cannot be replicated directly between the environments and work without changing of the statements ie change the prod server name to the dev server name.
I believe that we are not alone in this regard and i would like to know what techniques are recommended or being used to allow for a smooth DbGhost build of a dev database without environment problems. I am currently considering creating all affected object scripts with " if @@servername= prod " type syntax.
We do a similar thing where I work and use linked servers to connect to different SQL Server databases. Each linked server points to a different database in each environment, but because the linked server name is the same in all environments our db ghost scripts do not have to change.
I'm pretty sure you can use OPENQUERY with linked servers.
We had the same problem until we replaced all 4 part object references to use Synonyms (new in SQL2005). We manage the Synonyms outside of DBGhost, i.e. we don't script them up. They remain static to the db they belong in so they can keep pointed at their correct "dancing partner". For similar reasons we replaced all user based permissions with role based because our DMZ domain users don't exist outside of the DMZ... and grants fail. Hope this helps.
script to allow for Dev,Prod differences
Posted - 03/10/2006 : 10:32:56
