Need to provide access to database without giving access to production

  • Hoping I could find some best practice answers here: We have analysts who need to query and manipulate SQL data out of one of our prod databases, and obviously we do not want to give them access directly to the production DB. I was wondering the best way to handle this? Was thinking about spinning up another SQL instance and migrating a copy of the database there, but then I would need a way to keep the copy up to date with the production one, and that is where I am kind of lost.

  • How fresh does the data need to be? If a nightly update suffices and the servers are on the same network or otherwise able to copy files, then you could create a SQL Server Agent job to copy backups to the analysis server &  restore daily. If you need to filter or obfuscate data, you might need to do that process after the restore.

    If you need completely in-synch data, then replication is probably your only solution with 2008 (ever going to upgrade?). Replication can filter data.

     

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply