Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2003-12-17 : 10:50:00
|
| Hello-I have two identical databases on different boxes. Basically the are used for the same product, but for two different clinets (who are very very anal about making sure their data is separated). I was wondering if there was a way (triggers perhaps) to update a table on machine two to exactly match the same table on machine 1.I'd like to be able to do this two different ways, although either would be a start.1. I would like to be able to run a script on machine two, that compares the tables and updates the tables on machine two (drop or add column) to match the corresponding (same name) table on machine one.2. I would like to create a trigger so that if I add or remove a column in a table on machine one, it will also add or remove that column on machine two.Is there a way to do this or is it going to be something I have to continue doing manually?Thanks in Advance-Nick |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-17 : 12:05:15
|
| You could use replication or log shipping..I wouldn't use code to auto manipulate objects though...Why are your objects being manually altered on the fly?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 12:27:15
|
| Replication is for data modifications to get replicated to another database. Log shipping is for disaster recovery purposes only. I agree that with schema changes that they should be done manually. With replication, you can't modify the source tables anyway without dropping replication first. Script out your changes will make your life easier. If you are using EM to make the changes, go to the save change script button to save the code that EM will use to make the change. Then run this code on the other database.Tara |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-12-17 : 12:33:39
|
| Being a lazy programmer, I would hate to see you do this manually. I'm wondering if you could query syscolumns from each and compare the results for mismatches. This would give you what new columns were added. Set up a job to run this for you when you've got it worked out and it could save you the trouble of doing it manually. Gotta agree with Brett though. It's dangerous to do this in an automated form. Something could go wrong easily & quickly.Mike"oh, that monkey is going to pay" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-17 : 12:46:36
|
| I still want to know why the objects can be altered on the fly...If you do automate, you could put a timer on top of the box........4....3..2..1......booooooooomSince I haven't done replication, I misspoke..sorry...what happens if the publisher's objects change? Boom too?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 13:00:24
|
| You can't change the publisher's objects without dropping replication. So if you do change them, you drop replication, make your change, recreate replication and have it send over the objects.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-17 : 18:08:13
|
| SQL Compare from www.red-gate.com does this. Although you do actually have to run the program.However, a better solution from a "best practice" point of view would be to do ALL db object changes via script. Save the scripts with some sort of release / datestamp naming convention and apply them in the same order to any remote machines.Takes a little bit to get into the routine of this, but once you are there it makes your life so much easier.Damian |
|
|
|
|
|
|
|
|
Synchronzing Table Columns