2

We have a group of users within my company that are running reports directly against our production database. It has gotten to the point where they are hammering it enough to cause performance issues for the rest of the company.

I'd like to setup a reporting database for them within our local data center. Our production database is located in a different data center across the country. Due to the costs I cannot spin up another SQL Server database in the data center that hosts our production database.

In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?

enter image description here

Improve this question

1 Answer 1

Reset to default
8

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?

SQL Server Express cannot serve as a Publisher or Distributor.

SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details.

EDIT : To make my answer more meaningful, I am adding more details

Test any scenario that you are going to implement to avoid any surprises !!

In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

This is true up-to certain extent only. Its a best practice, but depending on how much data you are replicating and how busy (in terms of activity or transactions) is your publication database and the network latency between publisher-distributor-subscriber, this setup will affect you.

In your scenario, if your database is --

  • having less or moderate activity then

    • a local distributor would be sufficient and you can just have a publisher-distributor on one server running Enterprise edition and another subscriber running EE or standard edition (choice is yours). This way you save in terms of hardware and possibly licensing costs.

  • highly active then

Other thoughts:

Since you are already using Enterprise Edition, why not use Database Snapshots - with caution !!.

From Looking at Database Snapshot Performance

When using database snapshots, even in SQL Server 2012, there is an overhead associated with the additional writes required for copying data pages to the sparse files for the snapshots. If using database snapshots is a part of your general configuration, I would really be careful about planning the I/O subsystem to meet the workload requirements for concurrent I/O activity to the database snapshot sparse files.

So database mirroring is another option as well and you can create snapshots on the mirrored database and have it as reporting database.

Also, if your business does not want real time data, then a custom solution can be designed that can Extract, Transform and Load the data to another server using SSIS (or any tool of your choice) and that can be used as reporting.

Improve this answer
2
  • Database Snapshots are single point in time right? Meaning the data is only as current as the most recent snapshot? We need a solution that will provide the users near-real time data.
    – ProfessionalAmateur
    Jan 30, 2014 at 16:21
  • Yes. If you want near to real time data, then replication is your best choice.
    – Kin Shah
    Jan 30, 2014 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.