0

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. 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.

And 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.

Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.

-1

I know cross references between schemas/databases are commonly used in Oracle and now in MS-SQL as well :)

DBmaestro TeamWork (where I work) provides database enforced change management which consists of:

Database enforced version control – enforcing the version control process on the database objects.

Database baseline impact analysis – generating delta script (between environments) utilizing version control labels

and it does support cross references between schemas (Oracle) and databases (MS-SQL).

For more information on database enforced change management you can read our whitepaper

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.