SQL - Transactional Replication between existing databases

Question

I want to reconfigure a Dynamics AX 2012 SQL Server to improve performance of an existing architecture where one SQL server is used for both transactions and reports. This reduces SQL performance during peak loads (large MIS report generation).

In the proposed architecture, the transaction SQL replicates to a report SQL to share the burden and improve performance. I chose Transactional replication to implement this. But, here is the problem:

1. Because I'm using SQL standard, I can choose all the Tables but not all Views and SPs for replication because some contain Indexed keys and for that SQL Enterprise is required.

2. If I don't replicate all the Views and SPs then AX fails to recognise the report SQL.

Is it possible to restore a backup from the Transaction SQL to Report SQL (which contains all Tables, Views and SPs) and then enable Transactional replication to replicate just the tables?

Or, should I just use the Basic always-on availability group in SQL STD and setup a read replica?

Or is there a smarter :) way around this?

Thank you in advance for the help.

Adding a screenshot of the Indexed views issue with STD SQL.

Answer 1

Depending on your requirements for data latency, you can use Log Shipping to create a read-only reporting copy of the database on another server (or the same server).

There are some downsides, the data is not real-time since it is only as recent as the last log restore, and each time a log restore occurs it disconnects users from the secondary database, but if you don't have a need for real-time data reporting this might be a simple solution.

If you do require real-time reporting, you can set up transactional replication for your tables and simply script out your Views and SPs and manually apply them to your subscriber database. You can even use this as a post-initialisation script to be run after reinitialising your subscribers.

If the only purpose for the replication of data is reporting, then I would suggest only replicating the objects you actually require for your reporting to the subscriber database. This reduces the resources required on the subscriber and reduces the time required for things like initialisation of the subscriptions.

Answer 2

As noted in your updated screenshot, the warning you receive is only in regards to an ancient version of SQL Server, version 2000. As you mentioned in the comments, you're running SQL Server 2016 (Standard Edition). You definitely can replicate Indexed Views without issue. (Some deep dive research confirms this as well, and goes to say this was possibly a non-issue even for Replication in SQL Server 2000 too.)

I would recommend continuing forward with Transactional Replication, which is something I've used in the past to offload Dynamics AX 2012 to a secondary reporting server for performance and customization reasons too.

The only pain point I used to run into, is if we added certain customizations to the AX database itself (on the Publisher side), we would have to drop and recreate the Replication Publication, which we generated scripts for automatically with SSMS. But starting in SQL Server 2014, Replication supports dropping of the table articles when scripted correctly, so this may not even be an issue anymore.