Mirroring two SQL2000 DB's

[Grunners]

All,

What I'll eventually have are two databases, same structure and setup, each running on it's own physical server.

As group 'A' will be changing data on database 'A' and, group 'B' will be changing data on database 'B' during the day, what I want to do is mirror them each night so each morning these respective groups start at the same point.

They can't share one database either which is why I'm looking at this workaround. The two databases can't even be connected in any way whilst live - (business rules!)

http://www.access-programmers.co.uk/forums/showthread.php?t=166317&highlight=mirroring

...Seems to say that it can be done but I'm not sure if this is relating to a later version than 2000.

Any pointers?! Am currently looking at Replication in EM.

Many thanks in advance,

 

[SQL_Hell]

Hi Grunners,

Replication would be the right thing to use for this, but as ever I have to ask....

Why do this? why have two seperate databases which are the same?

Please explain why you chose this option and what the surrounding factors were.

 

[Grunners]

Hell,

As you have answered many of my questions in the past I'll explain!

It's two seperate companies that use the data and as they're seperate, they're not allowed to be linked up. That's the reason I've been given anyway.

I'd much rather them both just use the one DB but, 'rules is rules' and all that.

I also thought it would be easier to 'mirror' them in their entirety rather than try and slim one down. I'd only make a very small space saving for example for what might be quite a lot of work (and time).

I hope this goes some way to clearing things up?!

THANKS for the answer by the way...

 

[SQL_Hell]

Thanks for explaining, bit strange that they want to go through the hassle of setting up a data merge and have 2 databases on the same server even though they are essentially sharing the same data.

But as you say rules are rules so enough about that..

Do you need to deal with updated / deleted data as well as inserted data?

I just wonder what happens when the 2 companies update the same record on the same day, who is right? and on the same note what happens if one company makes a mistake and wipes out a 1000 postcodes (for example) and doesn't realise till the next day...that mistake will be replicated to the other company.

You could set up 2 way transactional replication or merge replication, but this can be tricky to set up and difficult to fix when it fails.

Are there going to be a lot of data changes in the day time from both companies?

Are there any indentity primary keys on any of the tables? do they need to be the same on each database or do you have sufficient surrogate keys to deal with this?

 

[Grunners]

Hmmm...

After reading your reply - especially the

Do you need to deal with updated / deleted data as well as inserted data?

I've decided that it's just too complicated to implement and run efficiently. As ever the business rules change so I've decided to change a few myself for the greater good - and for the person who will no doubt be asked to fix things if I'm away.

What we'll have is a 'master' database for company 'A' and then a much slimmed down version for company 'B'. I've decided that 'B' don't actually need half the information that 'A' does so at the end of each day, which should be sufficient, I'll just DTS any newly formed records across.

In a nutshell I started off wanting the earth and now I've decided to settle for something less. But it'll work, which is the main thing.

Many thanks for your time and insight, I fear yet again you've saved me a lot of time and stress!