March 5, 2014 at 9:58 am
I have been tasked to look into this, and it seems to me there are three ways to do that:
- Make a backup periodically and restore it on dev as DBNAME_Test as needed
- Replicate the production database to development as DBNAME_Test
- Log Ship the production transaction to DBNAME_Test
The data in this database need not be up to the second, minute or even date, just reasonably recent. As little disturbance upon production as possible is desirable. There is a difference in recovery models between environments, one being Full and the other Simple.
How would YOU do this?
March 5, 2014 at 10:16 am
it depends on whether the developers are allowed to do DDL changes, and how often those changes need to be re-applied when you refresh.
with regular restores, the develoepr changes get wiped unless they are preserved in a script, and would need to be re-applied each time the db is restored.
i believe when you set up replication, you can chose which columns transfer over...so if the developers add columns/ tables ,etc, you still get the data moved over; it's a little tougher to set up, i guess.
i thought log shipping was just building an always-in-recovery unaccessible copy of the database that is there in case of disaster recovery, and is not editable or accessible.
Lowell
March 5, 2014 at 10:20 am
Depends on what you mean by test.
A development and unit test environment?
A QA environment?
A performance/load testing environment?
For the 1st I recommend using SSDT to setup a database project and developing a set of test data that you can apply to the environment.
For 2 and 3, I still recommend using and SSDT database project from which you can apply developer changes after restoring a backup (with PII data cleansed beforehand).
Instead of a database project you could also use RedGate SQL Source Control or some other source control product that will allow you to apply developer changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 10:26 am
Lowell (3/5/2014)
i thought log shipping was just building an always-in-recovery unaccessible copy of the database that is there in case of disaster recovery, and is not editable or accessible.
Lowell, to clarify - when a log shipped DB is set up in STANDBY it is readily accessible by queries (note, it is READONLY and cannot be edited). Each time a new transaction log is applied, there is an option to either disconnect existing users so the log can be applied (I believe this is the default), or it waits until there is no connection to the database being restored. I'm certain in most cases you'd want to set it to disconnect users, as there most definitely would be cases of long running queries impacting the log shipping. this method would only be good for running queries against though...
For the OP, I would recommend replication, including schema changes, and omitting procedures, views, and UDF's from the article list (as Lowell has already pointed out, those objects would be completely overwritten by their replicated changes should they occur). Important to note, that even in replication, should developers create any new columns, they might get whacked as well in the event a new snapshot was taken and redelivered to the subscriber(s)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 5, 2014 at 10:40 am
More clarification -
The idea here is to presumably provide a fairly recent set of data for testing, also presumably without any schema changes versus production per say. As the DBA here, I am still trying to clarify the intentions of the parties requesting this, but at times that is difficult as they are not terribly database savvy in general.
My goal then at present is to distill the various options along with the various pros and cons of each (overhead, agility, etc.) so that such people can see what is involved so as to hopefully educate them (and myself) to what is possible.
I have in the past worked in far more well defined and stringent environments with high transaction counts and integrated environments, but that was admittedly in the SQL 2000 era with DTS packages and so on. While I understand things like Replication, Log Shipping, Mirroring and Clustering conceptually, it is without the benefit of real world experience with the "gotchas" and so on.
In any event, the replies offered are surely appreciated.
March 5, 2014 at 2:10 pm
This might not be exactly applicable to your situation, but in my data warehouse I just recreate the ETL flow and import data from the various sources into the database using SSIS. That way you can also test the batch logic as well. In an ideal situatio, there's almost no difference between Dev, UA and Prod, with the Dev holding the latest changes still in development of course.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 4:45 am
We had a situation where we needed to provide a database for testing and development that matched production, but, didn't have sensitive data (trust me, you want to clean up the account emails for your business people prior to letting that loose in development). We took our nightly backups and restored them to a staging server. That gave us a test of our backups as well as having a ready staging environment. Then, we shrank this database (yes, not something you normally do in Production, but we're not there) in order to make it as small as possible and then took a backup of it. That backup was restored to another location where we also cleaned all the sensitive data out (straight T-SQL, but you could use an SSIS package or something else). Out of paranoia, we shrank again, then took a backup of this database. We now had a database that was a copy of production, sans sensitive data, that was as small as we could make it. That's what was distributed to test and development environments.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply