SQL - Transactional Replication between existing databases
0
votes
2
answers
416
views
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.

Asked by Mandeep Singh
(3 rep)
Aug 29, 2022, 03:43 AM
Last activity: Aug 29, 2022, 02:54 PM
Last activity: Aug 29, 2022, 02:54 PM