database objects cross references
yazalpizar
Posts: 4 New member
We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem, SQL Source Control takes care of it. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues.
One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.
So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.
For the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.
One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.
So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.
For the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.
Comments
Would you be able to provide specific details of the error that you are experiencing? Are you able to send in an error report?
Thanks.
Redgate Software
The problem with cross-database dependencies is that the engine is only capable of deploying properly if there are one-way dependencies. For instance, if you want to deploy two databases then it would be successful if one depends on the other one. If they depend on each other then this will cause problems as whichever database the engine tries to deploy first will depend on objects that aren't there. At the moment the engine is not capable of resolving this. It's not ideal, but the way of sorting this out is to manually work out an order of deploying objects such that it won't cause dependency issues.
I hope this helps but if you'd like more information then please ask.
Redgate Software