Advice needed on creating a test environment

I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
Is it possible to take a backup of prod at the appropriate time and restore
it on your test box?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:78asg1pda4tvee5ug2kh5o5et09bu8m458@.4ax.com...
I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
|||On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:

>Is it possible to take a backup of prod at the appropriate time and restore
>it on your test box?
I don't think I can do that because a lot of my development is on
stored procedures. If I back it up and restore I'd lose my work so I
would have to save off my work, restore the backup and then recreate
my work. Right now I am thinking that my only solution is going to be
using DTS to copy all the data over after it does its nightly
retrieval of data from corporate (it is a moderate amount of data)
|||Hi,
I think in this case you may go for Transactional replication only for
Selected tables.
U can specify the time at which it needs to Copy the data from Production to
Developement.
Any help reply..
Regards,
Herbert
"Matthew Speed" wrote:

> I have built a sizable (for me anyway) SQL application for a client on
> which they now base their entire business. I need to set up a
> parallel installation of the database for development purposes, the
> idea being I can make changes to the development version without
> impacting day to day operations until it is confirmed that all changes
> are working correctly. My problem is this: the application gets
> information from their headquarters every night along with information
> from employees throughout the day. Every night the system performs an
> hour of batch processing on the combined data. In order for this
> sandbox to work, I need to be able to import into the test system an
> exact copy of the data only on which the various stored procedures are
> going to do their work.
> It makes sense that the time to copy the data over is after the
> poduction system finishes getting its data and before the batch
> processing starts.
> Is the best way to do this to create a bunch of DTS packages to
> replace the development data tables with the production using an
> append?
>
|||Why don't you store all your work do disk? I wouldn't dare to work in an environment where my latest
revision is in the database? There are also tools that can compare schemas and create a diff DDL
script based on that. This way, you have your source code in .sql files and after restoring the
backup, you just run that .sql file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:bm4tg15kphl0966uf4ie311ov86q72jgui@.4ax.com...
> On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote:
>
> I don't think I can do that because a lot of my development is on
> stored procedures. If I back it up and restore I'd lose my work so I
> would have to save off my work, restore the backup and then recreate
> my work. Right now I am thinking that my only solution is going to be
> using DTS to copy all the data over after it does its nightly
> retrieval of data from corporate (it is a moderate amount of data)
|||On Thu, 25 Aug 2005 16:34:37 -0400, Matthew Speed <mspeed@.mspeed.net>
wrote:
>I have built a sizable (for me anyway) SQL application for a client on
>which they now base their entire business. I need to set up a
>parallel installation of the database for development purposes, the
>idea being I can make changes to the development version without
>impacting day to day operations until it is confirmed that all changes
>are working correctly. My problem is this: the application gets
>information from their headquarters every night along with information
>from employees throughout the day. Every night the system performs an
>hour of batch processing on the combined data. In order for this
>sandbox to work, I need to be able to import into the test system an
>exact copy of the data only on which the various stored procedures are
>going to do their work.
>It makes sense that the time to copy the data over is after the
>poduction system finishes getting its data and before the batch
>processing starts.
>Is the best way to do this to create a bunch of DTS packages to
>replace the development data tables with the production using an
>append?
Well, is that the only change made to the production data, appends?
Probably not!
Herbert's suggestion to consider replication is a good one.
But, do you really need up-to-date data every day? Usually there is
development that goes on for some days or weeks (or months or years),
often to a different schema than production is using (!!!), and only
then is even somewhat up-to-date data needed for testing.
It's pretty easy to script out all your SPs, restore a backup of
production onto your test machine, then script the SPs back in. Or
keep the SPs in a separate database.
Or, arrange some DTS logic sufficient to do your updates on demand,
but probably more than just appends.
Lots of ways to go, but frankly, none of them is painless!
J.