Good evening everyone
Developers and Data analyst both write queries on the production servers. The developer writes entity framework while the analysts query the live database for ad-hoc request, create tables and sometimes create store procedures.
This obviously is not a best practice. We are looking to separate the developer role with data analysts. My initial guess is to have a new instance of server or database. The issues here, how do I make sure that the data analyst data is up to date and 24 hours behind. Any idea will be appreciated.
- create a databse and restore production backup to it on daily basis at midnight.
- create another database where analyst can ad-hoc request, create tables and sometimes create store procedures that points to the database from #1
This way their stuff stays intact and not overwritten by restore from prod.
Why are developers writing anything against the production database? They should be developing on a separate instance and promoting their changes to production once it has gone to a code review and change management process.
As for analysts - either backup/restore a copy to another instance or implement AOAG with a read-only secondary. Create a separate reports/analysis database where the analysts have permissions to create objects - and read access to the copy of production.
Thank you for your prompt response and suggestions. I appreciate