0

We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers for monitoring purposes.

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Last thing to keep in mind is that the connection between the companies is a 10 Mbps connection.

We are both running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

How would you tackle this?

Edit

I don't expect the database to grow to more than 8GB in the next 2-3 years.

Marc
  • 1
  • 3

3 Answers3

1

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Depending on the database size, I would use backup with compression and restore. You can easily automate this process.

Just take full backup with compression once every night, upload it to FTP (probably secure) and have a process on your end to download it and restore the database (overwriting the previous copy).

There are other methods like using SSIS - if you want selected tables only or using BCP OUT and BCP IN - provided you have same schema on your side.

Dont get into the hassle of implementing replication as making schema changes is a big headache + you have to generate snapshot and depending on the size of your database and network, it will have a performance impact on the source server.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

Depending on the size of the database you could use replication or I would get them to backup the database daily to a file share and then create a job to to restore it dail.

Parvinder Nijjar
  • 33
  • 1
  • 4
0

As described by you i feel:

1: Replication sounds like a good option for this, but there would be some overhead (not technical overhead, but the knowledge need to support it).

For setting up replication read here :https://msdn.microsoft.com/en-us/library/aa337389.aspx

  1. Microsoft Sync Framework

A comprehensive synchronization platform that enables collaboration and offline access for applications, services, and devices with support for any data type, any data store, any transfer protocol, and any network topology.

https://msdn.microsoft.com/en-us/sync/bb736753

  1. Log shipping is also one good option you can opt with as it gives you option of database at secondary server to be in read only, restoring and standby modes.

https://msdn.microsoft.com/en-us/library/ms187103.aspx

Personally , i will prefer to go with Replication!

KASQLDBA
  • 7,203
  • 6
  • 30
  • 53