April 30, 2014 at 12:36 pm
I have a scenario I'm faced with that I wanted to share and see what elegant solutions you guys could suggest.
Scenario:
Without getting into unnecessary detail, our company security model restricts the access of anything in datacenter a from datacenter b, all transfers have to be done through a particular share.
Production SQL Server host machine is in Datacenter A. Staging SQL Server and Data Processing is in Datacenter B. Once a month, I will run a copy-only backup from the production instance to a secured share in Datacenter A. The .bak file is copied via MPLS link to a secured share in Datacenter B. The backup file is restored to a processing environment and the database is 'updated' with newly processed data. The newly processed database is restored to an internal UAT environment for validation and once signed off on is then promoted to production by first transferring the new .bak file to the share located in Datacenter A and then through normal T-SQL scripts.
These processes have to be run by non-technical people.
My initial thoughts are to use Agent Jobs that can be manually executed. This would be pretty simple and straight forward for the end user.
What I'd really like to figure out is a way to automate the transfer from share to share, which makes me think perhaps a powershell script that can execute the t-sql backup from production to the share, initiate the copy from share to share, and restore the database to the processing environment so that with a single PS script the user could execute.
Oh yeah, almsot forgot. The service account the production database is running under is in a different domain than the processing environment. How would you overcome the permissions on the .bak file when created with a service account in a different domain?
Any advice from you veterans out there?
April 30, 2014 at 1:12 pm
BTW, I'm not asking for scripts or anything, I can hammer that stuff out, I'm thinking from a high level process standpoint and what tools.
I'm still leaning toward a PS script to run the T-SQL backup database commands, bits for file transfer across the network with another T-SQL block at the end of the script to restore the backup to the processing environment. Seems like a simple solution that should work well, but I know there are lots of smart folks out there that may have some better suggestions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply