My work wants to setup a secondary database to the live system but the “child” database must be as up to date as the live one at all times, well within 5 to 10 minutes. Both the LIVE and the child databases need to be accessible at the same time as well, some work such as reports will be against the child database and with that some calculations and so on will need to be done on the data, all within the child database if at all possible.
I have heard Replication and Mirroring can achieve this is it possible and can anyone point me to some good articles please?
So Parent / Child databases essentially. Whatever happens in parent must go to child, but what happens in child mustn’t got to parent. Both must be available at all times and child needs to be as up to date as possible.
Both replication and database mirroring (now called Always On) can deliver these things, if you have enough bandwidth between the two instances, and the hardware can handle the load, but they do have differences.
With replication you can add indexes to the database tables that can help reports run faster. But you may run into contention problems if you have long running (5 minutes or more) reports. You could be tempted to use NOLOCK, but then you will have to deal with dirty data in your reports.
With Always On, you don't have the contention issue, but you may need a lot of extra tempdb space in the reporting server to hold all the snapshots of the data that can be generated by a long running report.
Always On will automatically pick up table, procedure and view changes, while you will have to manually change these on the secondary with replication.