Options
Nightly Refresh of Data Only
gmartin
Posts: 1 New member
We are about to begin using Red Gate Tools in our SQL Server environment. One of the possibilities we are most looking forward to, is the ability to keep our "DEV" database relatively fresh in terms of the data that is in it. We are going to be developing code (new functions and stored procedures) in the DEV DB, but we would like for the DEV DB to always be populated with data that is very close to the same data that is in PROD.
Absent a suite of tools like Red Gate, our only option really has been to backup PROD and restore it as DEV. This has obvious drawbacks. If DEV isn't quite ready to become PROD, this approach means that managing the code that is under development becomes problematic.
What we are hoping, is that a nightly job can run that will merge the data from PROD into DEV without changing the structure or code that is in DEV. I realize that any tables that have structurally changed between DEV and PROD will present a problem. But we do not change table structures very often at all.
Long winded way to say...
Also, I see things on the Red Gate site about DB Dev Ops. To be honest, I don't exactly know what that term precisely means. Any pointers toward educating me on that would also be appreciated.
Absent a suite of tools like Red Gate, our only option really has been to backup PROD and restore it as DEV. This has obvious drawbacks. If DEV isn't quite ready to become PROD, this approach means that managing the code that is under development becomes problematic.
What we are hoping, is that a nightly job can run that will merge the data from PROD into DEV without changing the structure or code that is in DEV. I realize that any tables that have structurally changed between DEV and PROD will present a problem. But we do not change table structures very often at all.
Long winded way to say...
- Is this possible?
- What is the best way to go about it?
Also, I see things on the Red Gate site about DB Dev Ops. To be honest, I don't exactly know what that term precisely means. Any pointers toward educating me on that would also be appreciated.
Tagged:
Answers
So what you can do is make use of SQL Data Compare and SQL Compare in conjunction with each other, so Initially you say the table structure doesn't change very often so first you can setup a Data Compare project with your PROD database as the source and DEV as the target.
Once setup with all the right options etc you can tick the box for select all and save the project, then you can reference the project in the cmdline and run it there and it will push all the data across.
If you wish to further automate this you can take a look at SQL Change Automation with Compare which is mentioned here: https://documentation.red-gate.com/sc/getting-started/licensing/changes-to-distribution-of-command-line
Now, as you mentioned the schemas/structure is likely to change rarely, so here you can setup a Compare project to compare and transfer the new structure from one database to the other.
It's also worth taking a look at our SQL Clone product, as this will create an exact "clone" of your production database at the current time and can be a quite lightweight approach as you can create and drop those relatively quickly for fast development. it's worth noting that changes to the development clone will be lost upon refresh.
Following on, if you contact the Redgate sales team they can talk further with you based on your scenario and provide the best products based on your requirements.
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
Welcome to the forums. We had this scenario too.
As well as the options listed above, here are some other possible options.
1. Create SSIS packages that will copy the data over from prod to dev. This will be a data only refresh. (You can use BIML to make it dynamic)
2. Backup/Restore from prod every night but also schedule a job to publish/deploy your dev work from source control to the freshly restored copy on your dev server
3. Create a separate dev database and do your development work using cross database references until they are ready for production. (not great but can work)
4. Truncate/Insert Into using linked server
5. Truncate/Insert Into using a replicated copy of the production database.
6. Truncate/Insert Into using polybase
7. Use transactional replication (prod=publisher, dev=subscriber). You can still do your dev work on the replicated copy.
Out of all of these, 7 would prob be the simplest. Needless to say, pros and cons to all of them.
We went for option 1. We had a really smart developer come in who automated it all for us.
"Also, I see things on the Red Gate site about DB Dev Ops. To be honest, I don't exactly know what that term precisely means. Any pointers toward educating me on that would also be appreciated."
As for this, I have written a post to help people get started with devops: https://benbrown-sql.com/devops/
Best of luck
Ben