Suggestions on database refresh

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Suggestions on database refresh

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-19 : 10:11:06

I have been tasked with designing an automated process to restore production data to our testing environments on an as needed basis. The schedule would revolve around our software testing and deployment schedules. I'm looking for suggestions on best practices for this task in the form of advise / links to references / etc.. Instead of presenting all of my requirements here, I'll spare you that information :). Since part of it also needs to encompass data stored in Oracle (10g). I've done a several Google searches but would like to validate / invalidate my research against the advise of the experts here.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 15:04:13

to get any valuable recommendations you need to post particulars, like what you mean by refresh, where the data is, what kind of data it is, how much data there is...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 15:32:40

To refresh your test environments with a copy of production, use BACKUP/RESTORE method.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-19 : 16:16:45

quote:
Originally posted by anonymous1

to get any valuable recommendations you need to post particulars, like what you mean by refresh, where the data is, what kind of data it is, how much data there is...

See inline below for individual responses.

quote:
like what you mean by refresh

Sorry, I was under the assumption that data refreshes are a known concept, my bad. To elaborate, we develop software internally to support our business. This software goes though stages - development, quality assurance testing, user acceptance and finally production. Each of these stages has their own independent environment (servers and all). The bad thing about this is during the development and testing phases the data can get really out of whack. To counter this we want to develop a process where we can cycle production data down to these lower environments. The key is just the data is needed.

quote:
where the data is

The data resides in instances of SQL Server 2005. Each environment has their own server and own instance. The hardware and drive configurations on each environment can not be counted on to match (i.e. we have SAN solution on production but development everything is local disk, etc.). Our test environments fall in between development and production in terms of performance but are still local disk but just with several drives to split out data, index, logs.

quote:
what kind of data it is

Data is just data isn't it? Most of it are basic data types, there might be some image data but not much. The data spans across several databases though. One thing to note, there is one database where we store environment specific settings that *WILL NOT* move down.

quote:
how much data there is

Across the six (6) databases that need to have data moved down, we're probably talking about 120-150 GB of just data excluding indexes. In production we store the indexes on a separate file group on a different drive. We can exclude bringing down the indexes though and just have our process rebuild them since CPU power is cheap.

Some additional things to note:

(A) The intent when the process is complete, the only thing that was changed in the end is data. I do not wish to copy down permissions, users, jobs, etc. Just data. Read this comment for what it's worth since if the best solution entails overwriting this type of information and rebuilding certain pieces, then so be it. I'm just looking for the best solution for our situation.

(B) The end result needs to be an automated solution that does not require a DBA type individual present or even aware the process is running. The goal is to service our development staff with a process they can integrate into their deployment strategy.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-19 : 16:17:36

quote:
Originally posted by tkizer

To refresh your test environments with a copy of production, use BACKUP/RESTORE method.

Thanks for the reply but I would like to follow up with a few more questions:

Would you recommend using this approach through backups to disk (and obviously restores from disk) or though tape backup software (like Veritas - now known as Backup Exec (I think))

Would this approach include all database objects?

How would this compare to using say replication or data mirroring?

Thanks for your time responding. I just fell into this new role and am attempting to get my arms around managing both SQL Server and Oracle while dealing with a contracted vendor for the actual day to day administration.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 16:23:44

quote:
Originally posted by tfountain

quote:
Originally posted by tkizer

To refresh your test environments with a copy of production, use BACKUP/RESTORE method.

Via tape, via SQL Server and disks? Will this account for only data or all objects?

How you backup your data is up to you and not us. Copy the file to your test environment, then restore it.

It accounts for all objects and data. Backup/restore gets you everything inside that database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 16:49:46

a restore will also cause you to lose all in-progress data and in-progress objects. given the fact the "refresh" will occur without any DBA present or aware, backup/restore sounds like a very bad idea.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:02:45

quote:
Originally posted by anonymous1

a restore will also cause you to lose all in-progress data and in-progress objects. given the fact the "refresh" will occur without any DBA present or aware, backup/restore sounds like a very bad idea.

How is it a bad idea? It does not impact production whatsoever. He is wanting to refresh a test environment with production data. Backup/restore is how you do it and has been proven as the best method.

Perhaps you aren't understanding what a refresh is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 17:12:00

"The key is just the data is needed."
do you have programmatic restores occuring through out the day on your development servers?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:16:21

And then he says:

quote:

(A) The intent when the process is complete, the only thing that was changed in the end is data. I do not wish to copy down permissions, users, jobs, etc. Just data. Read this comment for what it's worth since if the best solution entails overwriting this type of information and rebuilding certain pieces, then so be it. I'm just looking for the best solution for our situation.

A refresh should include the entire database and not just portions of it. Note that jobs, logins, and anything else outside of the database are not touched.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:16:56

We refresh weekly. The entire process is automated.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 17:19:53

tfountain, you may want to clarify, but " do not wish to copy down permissions", still sounds like "just the data is needed."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:22:53

You can easily script it out to put permissions back if they are different from prod and test. Our permissions are different too, so I've got a script that fixes it.

Don't let permissions get in your way for this refresh. Backup/restore is still the method to use.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-20 : 09:31:40

@tkizer - is your automated backup/restore process done through SQL or through another backup solution (eg. Backup Exec)? Also, how do you account for new/changed/removed data structure and objects (views, etc)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-20 : 12:11:24

We use only SQL Server to backup our databases, well we also use SQL Litespeed but that's still via SQL Server. So anyway, we backup our databases to disk. We then have Netbackup in place to sweep those files to tape. But that process isn't involved with the refresh. Here are my refresh steps:

1. From prod server, find the newest full backup
2. From prod server, zip up this file
3. From either prod or test server, copy the zipped file to the test server
4. From test server, unzip the file
5. From test server, restore the database(s)
6. From test server, unorphan accounts, fix permissions, change recovery models, and run any application scripts to get database in correct state.

As for the question about new/changed objects, everything that we develop is in source control. We have processes in place that create build scripts for the database. Once the database is refreshed, if developers want to upgrade the database to a new application version than what production is running, they simply run their build scripts on it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-21 : 11:27:41

tkizer, your shop sounds like mine :). You stated this entire process is automated, correct? What did you use to code this (C#, T-SQL, etc.)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-21 : 13:13:44

TSQL only

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-21 : 13:45:46

tkizer (and everyone else!), thanks for your input. This has really confirmed my own findings as well.