5

Is anyone knowledgeable of alternatives to SQL Server replication?

I have a scenario where separate databases will share a lot of the same data which complex queries will match against. We want to separate the databases out from each other (i.e. not on the same SQL Server) so we can ensure the server resources are not a bottleneck.

What techniques allow fast and efficient data sharing, fairly instantly.

Replication seems to be quite a slow and cumbersome technology?

Are there any new features in SQL 2012 and 2014 that cater for this kind of scenario?

AlwaysOn Availability groups doesnt quite cut it as these are readonly replicas.

I need a read/write replica where multiple data sources interact with each other

Server A        Server B      Server C

All have read/write transactions, all require as instant 
as possible sharing of data between them of the results of the transactions.

Thanks for your help

  • Storage and RAM are cheap now a days. Why cant you add them and have schema separation within the database. All servers with read/write and instant sync - you are going to need significant architecture design and budget. – Kin Feb 10 '16 at 14:35
3

The reality is that SQL Server does not have a read-write scalability story in the same way that Oracle does in RAC so whatever you choose, you will always needing to consider that data-sets in one place or the other could be slightly behind. Availability Groups are probably the easiest solution to manage but then the only thing you are scaling out is readable replicas. Merge or Peer-peer replication probably provides much of what you need out of the box, but can be a bit of a headache especially in the wrong hands. You also need to think about avoiding conflicts.

I wouldn't attempt to roll out something yourself (i.e through SB or CDC) because this would ultimately become an administrative nightmare to manage in years to come and probably not provide you with richer functionality than is already provided.

0

Nothing is going to be instant unless it's synchronous data movement with AlwaysOn Availability Groups. You could setup two databases for your application. One is the one with the central data which would be read only on the secondary replicas, and one which is writeable which has the normal user data.

The second option would be SQL Server replication of the tables which you are writing to. What kind of data will you be putting into these tables that if it doesn't show up on the subscribers (Servers B and C) for a second or two that it would cause major problems? I've managed some of the largest SQL Server Replication environments in the world and as long as the application is designed correctly it shouldn't be an issue.

0

In our experience peer to peer replication is simpler and more robust than earlier forms.

A solution based on Service Broker would enable asynchronous distribution. The application would have to be resilient to the various combinations of CAP scenarios.

Master Data Services supports publishing consistent sets of tables. The cadence may not suit, though, as it in essence publishes in batch mode.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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