Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
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 at 16:21
    
Yes. If you want near to real time data, then replication is your best choice. –  Kin Jan 30 at 16:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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