The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
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
accept rate: 32%

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 28 Dec '16, 16:15

question was seen: 530 times

last updated: 30 Dec '16, 15:04