Alternatives to Microsoft SQL replication
Posted on 2013-11-27
One of our companies products currently has aprox 12 Dbs in total with 10-100Gbs in each SQL DBs
The customer Support team need to be able to query these databases real time and extract information from them...
We do not trust the customer support team with access to the Production cluster so we currently have transactional replication setup which replicates the DBs to a another standby server that the Support team can query.
Why don't we trust them? well it seems even with Read only access they can still manage to craft complex or inefficient queries that can bring the DB cluster to a halt!
Issue for us is that every time a new release of the product is made (every month) there are usually some sort of schema changes.... As a result we frequently have to completely remove the replication publishers and subscribers and basically start it again...
Of course during the time that the DBs are re-replicating the support team can not query the databases... and so are effectively "blind"
So we are looking for viable alternatives to SQL transactional replication?
-Log shipping is no good as latency is too bad (e.g 15 mins) and the DBs have to frequently be in single user mode if the shipping time is brought lower
-SQL mirroing is no good as the mirroring task needs single user access on the mirrored DBs
-DoubleTake is no good as the replicated MDF/LDFs need to be kept offline whilst DoubleTake is replicating the data
Can anybody suggest any viable alternatives?
Of course having a reporting interface on the product with canned queries would be the best solution... but until that is developed by development - the SysAdmins get stuck with this issue!