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

Tom%20Mangano's gravatar image

Tom Mangano
632202636
accept rate: 6%


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...

permanent link

answered 29 Dec '16, 06:48

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 29 Dec '16, 11:29

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 :)

permanent link

answered 30 Dec '16, 15:04

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×299
×40

question asked: 28 Dec '16, 16:15

question was seen: 1,160 times

last updated: 30 Dec '16, 15:04