Synchronising databases automatically

[waggers]

I am trying to determine the best way of automatically synchronising databases using SQL Server 7.0

We have three servers all using the same database - one is used for development work, a second is used for development and pre-production testing and the main machine is the production server.

Is there an easy (and safe) way to synchronise the development servers with the databse on the production machine?

I am aware that I can use backups and restore to the other machine, but I am looking for a simpler, automatic type method.

Thanks in advance

Waggers

 

[christine1]

I too want to know the answer to this one. I currently use the back up and retoring method across 4 servers. I wish there was an easier and quicker way. It takes up production time and valuable network resources. Any advice out there?


Christine

 

[foxdev]

I'll jump in here and say replication.

The biggest potential drawback is the processing drain on your production database. There is a matrix of options (push vs. pull subscriptions, immediate updating transactional or snapshot, etc.), so you can probably find one that imposes the least overhead.

From what I can gather from your requirements, snapshot replication (i.e., once a night) would probably be best for you.

Robert Bradley

http://www.foxdev.com/

 

[christine1]

Robert,

I'm still using 6.5. I've heard and had nothing but bad experiences on replication in 6.5. Any insight/advice on this?

Christine

 

[foxdev]

Sorry, but I've never used 6.5.

If you need both foolproofishness and automation, could you create and schedule a batch/command file that backs up the data, copies/FTPs it to the other machines, and invokes a restore on them?

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

If you don't have do have them synchonized frequently (once a night), you could consider bakup/restore. If they have to be synchronized frequently (like 10 times a day or whatever), then I would implement transactional replication.

Tom

 

[waggers]

Thanks all,

I will take foxdev's advice and look at snapshot replication as it only needs to run once a day for out purposes and the servers are currently not under any pressure during the 'wee dark hours'.

Good luck with your efforts, christine1

waggers

 

[christine1]

waggers, how are you going to go about it? I don't have nearly as many servers but enough that we really need synchronising across the board. I don't know where to begin. Let me know how it turns out.

Christine1

 

[foxdev]

Christine brought up a good topic: so many folks here have solved various SQL Server challenges, it would be nice if, once a mountain is climbed, they would make a "tip" post on how they did it. I certainly could learn a lot from other's accomplishments.

Robert Bradley

http://www.foxdev.com/

 

[waggers]

Hi all,

I agree with your comments about sharing how it is done.

I am still reviewing this subject and when I have decided on how to implement it at this location, I will pass on my experiences.

It won't be quick as my boss has got me doing other things first and I'm doing this as I can....in the meantime it is being done using backup / restores (not the best but at least it works)

waggers

 

[christine1]

Sure would appreciate taht waggers. Good luck to you in your endeavor and keep us posted.

By the way I too have to use the back up and retore method which weighs down the network tremendously anytime I have to copy a file across it.

See ya,

Christine1