Production Database

SQL Server Administration
1

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.

Thanks guys

2

My recommendation:

  1. create a databse and restore production backup to it on daily basis at midnight.
  2. 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.

3

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.

1 Like
4

Thanks. I appreciate

5

Thank you for your prompt response and suggestions. I appreciate

6

Just to emphasis...

  1. No one should be working in prod, period.
  2. Doing the nightly restores would allow both Developers and Data Analysts to work in a Dev environment with little fear. Of course, this is also the first step in creating a proper deployment process to prod. The code should go through both QA and UAT before it even comes near prod.
  3. No Developer or Data Analyst should be allowed to deploy their own code to prod. See item 1 above.
  4. The night restores will also be proof positive that you can actually do production restores. It would also be a good place to do a DBCC CheckDB without impacting prod.
  5. In case you doubt any of this, see Item 1 again. :wink: You won't be able to ever pass an audit nor have a leg to stand on if court action results from a mistake or bit of malicious code that someone wrote if Item 1 isn't followed.
1 Like
7

Thanks Jeff