0

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?

Improve this question
4
  • 1
    Transactional replication... it might not be straightforward, but it is exactly what you're looking for (assuming you want a near real-time copy)
    – gvee
    Commented Mar 17, 2015 at 14:55
  • As a test we have a replication set up but at the moment it's providing more headaches then anything else.
    – Pieter B
    Commented Mar 17, 2015 at 15:20
  • 1
    What headaches is it causing? Perhaps I can offer some assistance in chat? If so, feel free to invite me along.
    – gvee
    Commented Mar 17, 2015 at 15:26
  • Yeah, some of the constraints with transactional replication is every table would need a primary key which shouldn't be assumed. I'm a bit confused how the current setup works. But AG seem to be the best solution.
    – Queue Mann
    Commented Mar 26, 2015 at 15:54

1 Answer 1

Reset to default
2

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

Improve this answer
4
  • Yes we have looked at mirroring but the "This feature will be removed in a future version of Microsoft SQL Server" has put us off.
    – Pieter B
    Commented Mar 17, 2015 at 15:19
  • 1
    You can look at clustering. This is very reliable and using the high availability features of SQL Server 2012 and on can be off site (it used to require basically the clustered machines be in the same data center but not any more.). Clustering is done at the instance level, and is by a very large factor more complicated then mirroring, but its a very reliable way to mirror instances. Failover is automatic which only one mirroring type is. I would go with mirroring and then move to clustering when required by SQL Server.
    – benjamin moskovits
    Commented Mar 17, 2015 at 15:41
  • 1
    The cluster VS mirroring is dependent on what knowledge your data center has in setting up clusters , and other factors and can approach religious battles in intensity. They are both fine and appropriate solutions to what you need.
    – benjamin moskovits
    Commented Mar 17, 2015 at 15:44
  • Mirroring is still an option in SQL 2014 which was just released and mirroring is VERY straight forward to setup and fast. I'd say you're really safe going with mirroring right now until you can fully test and go with a replication method you really like. You might even be able to do read only availability groups in the near future if the price comes down. Replication is way more involved than mirroring.
    – Ali Razeghi - AWS
    Commented Mar 26, 2015 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.