0

We have a medium sized SQL2005 database with references to other SQL Server and Netezza databases (via linked server). On PROD the references exist. In my VS2012 database project I have imported the references as linked servers or database references where appropriate and the project compiles. There are plenty of warnings though.

I need to be able to deploy my changes to DEV and TEST environments where there are not the referenced databases so I can test my changes. My changes are not in the objects that reference the missing databases. I cannot deploy using "schema compare" > "update" as it fails.

Please can any one advise what is the best practice in this case?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Dib
  • 2,001
  • 2
  • 29
  • 45
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). – John Saunders May 30 '14 at 16:02
  • 2
    I'd say you probably need those reference DBs in your DEV/TEST areas, even if they're just a shell of a database. Not sure about the Linked Server aspect, though I suspect installing another instance on that box could work. You'll have a lot easier time if your environments are similar from DEV through Production. You should be able to do a schema compare and deselect objects that you know will break if all else fails. – Peter Schott May 30 '14 at 16:35
  • @PeterSchott - Thank you. Re: referenced databases - I had worried that that might be the case. Re: Second instance: I had not thought of using another SQL Server instance on the same box. I will present that as an option to the team and see how that goes down. Re: similar environments: Our environments are that similar as our PROD is split over multiple clusters\instances and we only have a single instance for TEST and DEV. Also DEV has a lot of "concepts" and "trials" which muddies the water. – Dib May 31 '14 at 06:30
  • @JohnSaunders - Thank you for the link; an interesting discussion. Personally I was always brought up to say "please" and "thank you". If you want to edit these out in each of my questions, then be my guest. Thank you. – Dib May 31 '14 at 06:36
  • @Dib: this is a Q&A site, not a discussion forum. If we were having a conversation, then "please" and "thank you" would be good. But it's not a conversation. – John Saunders May 31 '14 at 10:17

1 Answers1

1

In your schema compares for DEV and TEST, check Exclude next to the objects that reference the missing databases. When you run the update, it will ignore those objects.

Keith
  • 20,636
  • 11
  • 84
  • 125
  • Thank you. We do use schema compare but if we start dropping out some of the objects that are causing the issue, then the objects that reference those missing objects start to cause the "UPDATE" to fail... – Dib May 31 '14 at 06:33
  • 1
    @Dib, then the code you're deploying isn't going to run at all. I highly suggest following Peter's comment or re-architect your database. – Keith Jun 02 '14 at 13:48