We are working with several vendor supplied databases and have many stored procedures, etc. that cross DB's (and a growing number that cross linked servers). The license for the main product we use is tied to the host/instance name, so we can't run a copy on a dev server. (We do have a clone VM on a separate VLAN with the same IP, hostname, etc. so the applications work and we can test very large upgrades to the OS and tools like Scribe, but it's not practical to do that for small changes to SP's because of the network isolation).
On the production server, we CAN (and do) have a copy of the DB's and run different version of apps on same server, so we have Live_[DBName], QA_[DBName] and Test_[DBName]. While this lets us test new versions and patches it's no good for dev, because if we're working in QA and need a crossDB query it will point to QA_. If we then commit that to Live, the crossDB query will be wrong (still pointing to QA, but no SQL error will occur as QA_DBName is perfectly valid).
The QA DB's are frequently copied from Production, and when we make big changes (us or the vendor), we test them in QA first (copy of Production). Unfortunately a lot of the changes the vendor makes modify data as well as schema so I don't see how that could be considered the dev DB (as it's frequently overwritten too).
Before the vendor applies any updates, we will copy Live_ DB's to QA_ and then we can run a compare to see what changed when the vendor di the upgrade. (First step would be to update QA_ and see what changed compared to Live_).
We work using SSMS via RDC to main production server too. We will be using VisualSVN as back-end for this and other stuff (with TortoiseSVN and VS plug-ins)
There are two of us that work on the DB's. What we really want is the CI and source control for *ONLY* SP's (largely for BI reports), Triggers and UDF's (not the schema) so that we can see who changed what, revision diffs, and (manually) revert changes if required. We did consider using just SVN, but the CI bit is the real strong point of Red-Gate (no remembering to Save and Commit, just do Alter/Exec/Commit)
1. So, would source controlling just those objects put a lot of load on the production server?
2. It sounds like what we need is shared setup (one DB we both use). Is this correct
3. The App uses 7 different DB's (Customers, Orders, Planning, Costing, etc.), so would I link each DB to it's own repository?
Any other suggestions for a setting things up?