Replication, can I use it accross sites?

Other SQL Server Topics
1

Morning all,

I have a client that has an existing SQL server but wants to same database & same data on a second database server 200 miles away with any changes copied between the 2 in pretty much real time. Primarally they will be making changes on the existing site but want the ability to makes changes from the secondary site too...

I've been doing some reading up and I'm a little unsure if I can use 'Replication' for this task...? If so, is it pretty streight forward to set up & how much bandwidth would might it require....?

Many thanks

Dave

2

Yes you can use replication for this, however it is not straight forward. There's a bit of a learning curve with replication. And a huge learning curve if you are expecting writes to occur at both sites.

As far as bandwidth, that's not something we can answer. Depends on how busy replication is going to be. Even when the publisher, subscriber and distributor were all on the same subnet, my critical transactional replication would be latent by a couple of hours at times.

3

Hi Tara,

Thanks for your reply. I thought this might be the case...

Had another chat with the client this morning, the spec has changed a little.....
The primary server is located in a satellite office. The remote server will be located at head office.
They are looking for the ability to be able to move all operations from satellite office to head office almost at the flick of a switch. So data-wise the remote server must be pretty upto date. Sync-wise I think it would be better only sync 1 way primary to remote, if / when they want to switch back I will just do a manual backup & restore.

After doing a lot of reading on replication would I be right in my understanding that it doesn't create a true database copy, to the point where you can turn off the primary and run entirely from the secondary...? Might mirroring be more upto this task...?

Hope it makes sense....

Thanks

Dave

Oh currently using SQL 2008 R2 Enterprise...

4

That's correct about replication. What you are asking for is a DR or HA feature, which replication is NOT. For HA and DR, there's Availability Groups and Database Mirroring.

What version and edition are you using? What is the network speed between the two sites? You are probably going to need to use asynchronous for either technology, which means there will be a delay between the two. If you use synchronous, it'll impact performance of the writes on the primary.