Backup SQL Server

[Tom]

Hi All,

I have two servers, one in the UK and one in the US. Both are running the standard edition of SQL Server 2000.

The one in the UK will be used as the main server and we wish to have the one in the US contain an identical mirror/backup of the database in the UK. This way, if there is a problem in the UK we can switch over to the US and everything will be fine.

How do we go about doing this? I guess it needs to update the backup server on a transactional basis.

Also, what happens if the UK server goes offline and we then swith to the US server (which should be in the same state as the UK one before it went offline)? The database will then be taking updates extra but it won't be able to keep the UK server informed of the changes. When the UK server comes back online it would have to be synchronised again.

This is obviously a big topic so perhaps if you could just point me in the right direction.
Thanks
Tom

[Karl]

Tom,

there are several options that come to mind. Mainly either transactional replication or log shipping. Now, even though you can set up log shipping yourself manually on SQL Server 2000 Standard, Enterprise Edition does it all for you in effect.

The other option, transactional replication requires more effort in implementing it and also administering it.

The biggest problem, in both cases, is going to be the reliability and speed of the link between the US and the UK. How many transactions you get will also affect the outcome, if the transaction rate is low then perhaps you don't have to worry about the speed of the link so much (in which case I might be inclined to favour log shipping on 2000 enterprise). With log shipping you might have to prepare to accept some loss of data if things go wrong (depends on how often you ship logs). Having said that transactional replication doesn't guarantee that you won't lose data.

One other thing worth mentioning is that your server in the US will never be online if you use log shipping, up until the moment you decide to switch over. With transactional replication it can be online all the time. Even though you can theoretically leave the log shipped database in read-only mode you can't restore logs whilst there are users in the database so there's no point.

Which ever one you go for, you're always going to have to synchronise the UK server once it comes back up.

It's difficult to say much more given that I don't know a lot of the specifics but I hope that this will get you off on the right direction at least.

Regards,

Karl

------------
Tom at 5/24/01 5:35:47 AM

Hi All,

I have two servers, one in the UK and one in the US. Both are running the standard edition of SQL Server 2000.

The one in the UK will be used as the main server and we wish to have the one in the US contain an identical mirror/backup of the database in the UK. This way, if there is a problem in the UK we can switch over to the US and everything will be fine.

How do we go about doing this? I guess it needs to update the backup server on a transactional basis.

Also, what happens if the UK server goes offline and we then swith to the US server (which should be in the same state as the UK one before it went offline)? The database will then be taking updates extra but it won't be able to keep the UK server informed of the changes. When the UK server comes back online it would have to be synchronised again.

This is obviously a big topic so perhaps if you could just point me in the right direction.
Thanks
Tom

[Tom]

Thanks for that Karl.

Transactions I guess will probably be about 1000/2000 day consisting of fairly small updates. There won't be any large text fields involved.

How would we manually sync the servers?

Thanks
Tom

------------
Karl at 5/24/01 6:06:32 AM


Tom,

there are several options that come to mind. Mainly either transactional replication or log shipping. Now, even though you can set up log shipping yourself manually on SQL Server 2000 Standard, Enterprise Edition does it all for you in effect.

The other option, transactional replication requires more effort in implementing it and also administering it.

The biggest problem, in both cases, is going to be the reliability and speed of the link between the US and the UK. How many transactions you get will also affect the outcome, if the transaction rate is low then perhaps you don't have to worry about the speed of the link so much (in which case I might be inclined to favour log shipping on 2000 enterprise). With log shipping you might have to prepare to accept some loss of data if things go wrong (depends on how often you ship logs). Having said that transactional replication doesn't guarantee that you won't lose data.

One other thing worth mentioning is that your server in the US will never be online if you use log shipping, up until the moment you decide to switch over. With transactional replication it can be online all the time. Even though you can theoretically leave the log shipped database in read-only mode you can't restore logs whilst there are users in the database so there's no point.

Which ever one you go for, you're always going to have to synchronise the UK server once it comes back up.

It's difficult to say much more given that I don't know a lot of the specifics but I hope that this will get you off on the right direction at least.

Regards,

Karl

------------
Tom at 5/24/01 5:35:47 AM

Hi All,

I have two servers, one in the UK and one in the US. Both are running the standard edition of SQL Server 2000.

The one in the UK will be used as the main server and we wish to have the one in the US contain an identical mirror/backup of the database in the UK. This way, if there is a problem in the UK we can switch over to the US and everything will be fine.

How do we go about doing this? I guess it needs to update the backup server on a transactional basis.

Also, what happens if the UK server goes offline and we then swith to the US server (which should be in the same state as the UK one before it went offline)? The database will then be taking updates extra but it won't be able to keep the UK server informed of the changes. When the UK server comes back online it would have to be synchronised again.

This is obviously a big topic so perhaps if you could just point me in the right direction.
Thanks
Tom