Using SQL Server Express as Distributor for Transactional Replication

reportingsql serversql-server-2008-r2sql-server-expresstransactional-replication

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

Best Answer

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.

Related Question