February 4, 2017 at 10:30 pm
Our Production sits in a very strict environment and different subnet from Non-Prod environment and is in a different subnet. Wont be able to connect to Prod from Non-Prod and vise versa .Is there anyway I can sync a Prod DB in Non-Prod environment ,the non-prod db has to be available for read\write . The sync has be occured only when required and rest of the time it should be accessible to users
Whats about an SSIS package to convert recent changes to a flat file and insert it into non-prod ? we have a network share which can be access from both environment.
February 7, 2017 at 2:20 am
If your setup is very "strict" you first need to consider whether you can (or should) publish production data directly to your test environment. If you can, then a basic database restore might work (you don't give any indication of the size of your database, nor the amount/frequency of changes). Security and Logins need careful consideration too.
Log Shipping could use the Network Share to transfer the logs between servers (but the test db would be read-only).
An SSIS package could transfer all tables to flat-files, which could then be imported into the test db. However, you would need to identify "recent changes"; depending on the structure of your tables, this might be easy or very hard; if you have complex relationships between tables, then just inserting new data might be very difficult. Various forms of change-tracking could be implemented to identify changed data, but that doesn't solve all the problems, and might also require schema changes to your production database.
February 7, 2017 at 7:14 am
Not backup and restore? Assuming the data isn't an issue.
February 9, 2017 at 4:57 am
Steve Jones - SSC Editor - Tuesday, February 7, 2017 7:14 AMNot backup and restore? Assuming the data isn't an issue.
Thanks Steve.
Finally that's our only option I think as the secondary will be modified in between . Is there anyway possible only to restore differential only to a running DB (I am stupid).
February 9, 2017 at 5:09 am
No. Only if you restored the full backup with the NORECOVERY option.
John
February 10, 2017 at 7:11 pm
Andy sql - Tuesday, February 7, 2017 2:20 AMIf your setup is very "strict" you first need to consider whether you can (or should) publish production data directly to your test environment.
+ 1 Billion! There's a reason why they've been assigned to different subnets.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2017 at 7:13 am
Jeff Moden - Friday, February 10, 2017 7:11 PMAndy sql - Tuesday, February 7, 2017 2:20 AMIf your setup is very "strict" you first need to consider whether you can (or should) publish production data directly to your test environment.+ 1 Billion! There's a reason why they've been assigned to different subnets.
Thanks Jeff,
We have cleaner scripts in place to remove\manipulate sensitive data.
May 1, 2017 at 2:16 pm
Rechana Rajan - Monday, May 1, 2017 7:13 AMJeff Moden - Friday, February 10, 2017 7:11 PMAndy sql - Tuesday, February 7, 2017 2:20 AMIf your setup is very "strict" you first need to consider whether you can (or should) publish production data directly to your test environment.+ 1 Billion! There's a reason why they've been assigned to different subnets.
Thanks Jeff,
We have cleaner scripts in place to remove\manipulate sensitive data.
Great. Where does the data reside until you can remove\manipulate the sensitive data?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply