August 7, 2019 at 6:06 am
Hi All,
In our env, we have multiple sql environments and very often we get DB refresh requests to restore db from PROD to sub-prod env's.
for this, we have created a powershell script which does the refresh. Below are the refresh steps
- take prod backup (backup size ~500GB )
- copy prod backup to sub-prod server using robocopy
- restore
- fix the orphan users
- change pwd as per sub-prod env
- send out email that refresh is complete
Now, we want to schedule this powershell script. We want to do this using SQL Agent Job.
My question is, whether to run this sql agent job on PROD Server or sub-prod server ( QA,UAT, DEV etc... )
Will be there any difference running from PROD ? just from performance perspective will it make any difference (taking backup, file copy from prod to QA ..)?
Thanks,
Sam
August 7, 2019 at 7:29 am
no difference from a performance point of view - backup and restore is still done on the same server, and copy of bak file still happens between the same servers.
however do take the following in consideration - if your non prod servers do not have a valid licensed SQL Server instance (e.g. they have a MSDN or a SQL Developer license) then having the job running on those servers and connecting to the prod server to do the backup is a break of licensing terms.
August 7, 2019 at 11:31 am
Agreed. You can run it as a push or a pull. It'll be the same. Probably, just as a general precaution, I'd run it as a pull, scheduling it from non-production servers.
Side note: I didn't see the step in your process where you clean the data to ensure that legally sensitive information, or just silly simple stuff like client emails, don't get transmitted to non-production environments where they can be more readily distributed to non-approved persons. I would absolutely do this on any system.
I KNOW you'll get push back, so here's a quick story on why this is not simply a good idea, but a job saving move.
A company I worked for had your process. No data cleansing. One day, a developer was working on automating sending emails... from our production list of email addresses, all our clients. Said developer also enjoyed browsing adult themed web sites at work. During testing, this person attached an adult themed photo to their test email. They thought they were only sending it to themselves. Instead, they sent it to the client list. After that, we cleaned the emails by policy.
Please, learn from this example and clean your data now. You don't need to perform a resume generating event to learn this lesson.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply