Best way to make a database available on two instances of SQL

[plyon70]

I need to find the best way to either copy or "update" a database through a network so both databases are pretty much mirrored. Here is the catch, the production systems will be running MSDE. I have SQL 2k Developer so I can build it there, generate a script, and then update MSDE.

I just do not know what would be the best way to go about this. Originally I was thinking backup and then a restore but that seems to be too many steps.

Would copying the database work? Or would this be a job for a REALLY complex query?

If someone could point me in the right direction for this it would be greatly appreciated.

 

[jitter]

You might could look into using merge or snapshot replication.

These work with MSDE engine.


jitter

Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[mrdenny]

Do both databases need to be read/write, or just one?

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[plyon70]

Thank you very much. It wasn't too long after I posted this I tried replication. I am actually testing that tonight on MSDE to see if I can get it to work.

As far as mrdenny ... to answer you. The primary needs to write to the secondary in case the primary fails. I just need to come up w/ an automated solution to bring the data back from the secondary to the primary once I get that one back up.

During the time the primary is down the secondary will be carying the work load.

 

[mrdenny]

That would be transactional replication. Unfornitually MSDE can't be the publisher for transactional replication.

I'd recommend a backup and restore method like log shipping but hand written (it's nice and free that way).

I don't have a FAQ written on doing this, but it's not hard.

In the event of a failure you simply re-restore the most recent transaction log database without leaving the database in restore mode.

To move back to the production box, you backup and restore from secondary to primary, than start the process again from primary to secondary every few minutes.

If you need more info let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[plyon70]

Is there any way to work around MSDE not able to be a publisher for transactional replication? That would be ideal for what I need to do.

 

[plyon70]

I know typically in the computer world there are rules that can be bent if you know how to bend them. Just curious if this is one rule that can be bent.

 

[mrdenny]

Nope, I know of no way around that. That's why I recommend the build your own log shipping method.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[plyon70]

Do you know of a good resource that I could use to learn how to write something like that?

 

[mrdenny]

Not that I know of.

I'll try to write a FAQ to cover it.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)