We have a customer that wants to have an up to date reporting database. The reporting database will be on a separate server than the production database. It seems our 2 choices are to schedule a Mobilink sync every x (1 - 5) minutes or to use replication. Based on cost and performance, what's our best choice? Thanks for any help.
asked 28 Dec '16, 16:15
I'd say that High Availability (and particularly the Read-only scale-out flavour) are further choices, in case the reporting requires only read-only access (I'm not sure about the creation of temporary tables/procedures)...
Here are two samples from Breck's Blog:
Note that both HA and Read-only scale-out require a separate license.
IMHO, the primary advantage would be that you don't need do declare/setup any replication/sync logic - the secondary database will be a real copy of the primary and therefore automatically receive any change made by the latter. The "mirror mode" will decide how "fast" the changes are applied.
Just to add: A further alternative might be to use proxy tables within the reporting database to pull the data from the production database and store them locally in the reporting database - that would require neither a further license nor a replication/sync setup. However, it may require some "data refresh" before queries are run.
I guess it might be helpful to know whether the reporting should access all data or just a subset (w.r.t. to tables/columns or rows) and if the data change rate is very high (so that a complete re-import would be unfeasable) and whether the reporting will be affected by frequent schema changes and the like...
One-way replication using SQL Remote (dbremote.exe) is very popular for simple data reporting applications. The copy database can be updated, and the two databases are very loosely coupled which makes network and load management easy... those two requirements are not always apparent in advance :)
answered 30 Dec '16, 15:04