Source Control Production Server

mark_anderson_us

Hi Guys

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?

David Atkinson

What I think you're saying is that you effectively don't have a separate dev environment from which you would push changes to production. That worries me a little. Is there any reason you can't maintain a dev DB and simply copy any client-made changes to this environment on a regular basis using tools such as SQL Compare and SQL Data Compare?

If you want to use your production server as your development server, and you want to minimize the load caused my SQL Source Control polling the database for changes, you can disable this as described here:
http://www.red-gate.com/MessageBoard/vi ... hp?t=12837.

However, this would mean you wouldn't get the blue indicators in the Object Explorer as and when you change the objects. Instead they would only appear when you visit the Commit tab at which point SQL Source Control does a comparison and calculates them.

Yes, you would need a repository for each database you wish to source control. This is no more than creating an empty folder in Subversion for each one.

SQL Source Control has a filter feature that will let you filter out object types you don't wish to consider.

SQL Source Control is trivial to set up so I'd urge you to install it and test it out for yourself to see if it meets your requirements.

I'm not sure I'm clear as to what you hope to achieve with CI. Would you be able to elaborate on this? Do you have a CI tool in place?

David Atkinson
Product Manager
Red Gate

mark_anderson_us

Hi David

Thanks for the reply. We could only maintain a dev DB is if it's on a different SQL Intance/Server because the vendor-supplied application we use has 7 connected db's and a lot of cross-db queries. We currently have 3 sets: Live_, QA_, Test_, but it's a pain to work like this. If we develop in QA_, all cross db statements begin with QA_ and we have to remember to change them to Live_ before deploying (which would have to be a manula process).

Obviously, we could do what we wnat with DB's, etc. But the license for the vendor's app wouldn;t work if we used a difference instance name, so we wouldn;t be able to test effects of our changes

Right now, even if we wanted to we couldn't use a new instance because of memory constraints on server, but a new server is being commisioned in a few months

Am I missing something, or do you see an alternative to this?

One last question: does the filter reduce the polling volume, or just what we see?

Regards

Mark

David Atkinson

The filter only changes what you see.

You would try turning off as I suggested? Do you have an objection to this?

Or use synonyms for all cross-database references and have a script that changes the synonyms when you deploy to production. If storage is an issue, consider tools like SQL Virtual Restore or SQL Storage Compress.

mark_anderson_us

Hi David

Will give it a go with polling turned off. Just concerned that I won't see my colleague has soemthing checked out and I start working on it

Regarding this:

"Or use synonyms for all cross-database references and have a script that changes the synonyms when you deploy to production"

The changes would be easy because it simply swapping "QA_" for "Live_". Could the script be run automatically as part of deploy?

Regards

Mark

David Atkinson

If you periodically visit the Commit tab, this will refresh the changes.

I don't see why you couldn't recreate your synonyms in a post-deployment script. Why not give it a go and report back to this thread?

mark_anderson_us

Hi David

Is a post-deployment script a red-gate thing? I tried searching support site and didn't find anything.

Can you give me a hint how I'd do such a thing. Let's assume I have an SP in QA_Planning that I'm going to edit and this selects data from QA_Orders and QA_Customers. From what I understand, I would deploy this to Live_Planning and then run this post deployment script. unless it's part of red-gate, I would imaging that it'll be tricky to get all the objects it uses in other DB's

Regards

Mark

mark_anderson_us

Hi David

You can ignore most of last post. Just discovered what synonyms are! (never come across them before). Seems they would solve it, but begs a different question - why would I need a post deployment script if I use these?

Regards

Mark

David Atkinson

No, it's not a Red Gate thing. I'm suggesting you run the script independently.

I'm no expert but I think you'd need a synonym for each object that is referenced from a different database.

eg.

CREATE SYNONYM synonymname
FOR QA_Planning.yourschema.yourobject

-- your post-deployment script would need to remove the QA synonyms and replace them with the ones that will work in the production environment.

DROP SYNONYM synonymname

CREATE SYNONYM synonymname
FOR PROD_Planning.yourschema.yourobject

mark_anderson_us

Thanks David

from my quick reading (for anyone else who reads this). i can create a synonym called "Order" in both DB's and then I don't need any prefix: Orders will point to correct DB's (assuming the deployment process doesn't resolve synonyms to their targets).

Will have a play and post back

Regards

Mark

mark_anderson_us

DIdn't work. Synonyms are at table level. Got way too many to use