Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving Data Across Domains Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 29, 2014 1:22 PM
Points: 5, Visits: 51
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?
Post #1566551
Posted Wednesday, April 30, 2014 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 29, 2014 1:22 PM
Points: 5, Visits: 51
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.
Post #1566572
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse