For our clients we want to provide a solution of having an off-site mirror of their database.
At this moment we do this by scheduling a database-backup every night, move and import that backup in our datacentre and during the day catch each and every sql query that updates or changes data or the schema in our clients' database, send that to our datacentre and run it against the backup we imported the previous night.
This process is brittle to say at least.
What I'm looking for is a way that keeps a database in our datacentre exactly the same as our clients database realtime. Mind you, this is one-way traffic only, from the client to our datacentre.
I have been testing with replication, but that doesn't work as straightforward as I would expect it to.
Are there other straightforward ways to do this?
Best Answer
Have you looked at SQL Server mirroring? You can have high safety mirroring which means that a transaction at the client is not done until the transaction has been mirrored to your data center database. Mirroring is done at the database level and there are different types of mirroring depending on your needs.
Please look at the following to see a more extensive description of mirroring: https://msdn.microsoft.com/en-us/library/ms189852.aspx