June 6, 2008 at 3:32 am
Hi geniuses!
What would be the best strategy for updating the database with new (or updated) stored procedures and data and even changing tables (adding columns)?
To clarify..
I'm designing a .net program that will enable users to send new data to their local database but if a change occurs in the main (remote) database the program will update the local database to match the main. Change means what I described in my first paragraph. The existing data must not be lost when updating.
One very lame attempt would be:
1. make a backup of the main DB (mainDB)
2. copy a backup of the local DB to temp DB (localDBtemp)
3. "restore" the mainDB into the localDB
4. import data from localDBtemp to "restored" localDB
I hope I described it good enough. Any help much appreciated. Perhaps there's a good tutorial on topic?
June 6, 2008 at 7:51 am
Honestly, that's a tough one.
Best approach I can recommend is to establish a very tight discipline around your main production builds. They should only be done by a script that has been tested so that it runs without error prior to using it in production. Once used, it should be the only mechanism for upgrades. Once that's in place (and believe, that's hard work), it should be fairly simple to then run that same change script on each of the other servers. I'd suggest looking into getting a copy of Visual Studio Team Edition for Databases. You might also consider looking at DBGhost or some of the processes that people have built around Red Gate's SQL Compare. All three of these tools work with code stored in source control, which will be your best bet for being able to generate a repeatable, known state for your db.
That's my 2 cents.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2008 at 7:31 pm
How I would do it:
1) Implement DDL triggers/events on MainDB to catch the desired changes.
2) Use Service Broker to SEND the event XML to an EndPoint on LocalDB's server.
3) Use Service Broker on LocalDB to Receive the event XML through the aforementioned EndPoint.
4) break down the XML to reconstruct the original DDL and use Dynamic SQL to re-execute it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 7, 2008 at 7:34 pm
And I wouldn't use .NET to do this (at least not based on the requirements so far).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2008 at 3:28 pm
Ugh, oh.. I thought it would be easier (guys (and any potential gal), still time to chime in :D)
rbarryyoung, your version sounds the most logical to me. Just curious, why wouldn't you use .net?
June 8, 2008 at 5:21 pm
Because .net would just make it harder. I would use .net if I needed to, bu so far this would be easier without it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply