My client wants to replicate data from their F&O system to a 2nd server. This would allow them to run some reports that might otherwise affect performance.
Does anyone have recommendations on an approach that has the least impact on performance?
In general, these requirements are usually addressed by a data warehouse. You export data from transactional systems to a data warehouse and use this data in reports, rather than querying all the other systems directly. This means that running reports doesn’t have any impact on performance of transactional systems and you can also easily combine data from several systems.
F&O offers export to Azure Data Lake (for example), which you can then consume in Power BI reports.
@MartinDrab - another option that someone told me was to use “Always On” availability group
Apparently, we can pass in ApplicationIntent=ReadOnly in a connection string, and the reads can be routed to secondary replicas.
Where would the connection string be managed if they wanted to try that option?
Thanks
Always On is a feature of SQL server, but F&O in cloud uses Azure SQL, not SQL Server. Also, the production environment is managed by Microsoft (unless you want to use the on-premises deployment), therefore you can’t install more servers, change connection strings or anything like that. It won’t help you with your scenario.
1 Like