What is the best practice to give users access to the production data?
We have a replicated database using SQL Log Shipping that restores on the top of the hour. But departments would like more accurate data. We tried SQL Transactional Replication but were worried about the load on the production server.
If we did create a true replicated database for the user, how can we verify the validity of the data? We currently have one Data Analyst and consider his reports to be the single source of truth. Users querying the database can quickly manipulate the data to show something totally different.
Now that Epicor is a properly indexed sql database replication is the only way to give anyone except the production application server access. Latency is under 5 seconds for ours.
I don’t allow any users to query the database or even run their own baqs in the production system.
We do have reporting and a data lake at corporate that use the replicated database.
SQL Server replication is your best friend. The tool is very reliable and you don’t need to replicate the whole database, just the tables required for your reports.