Hi there.
I've created a Copy Live to Test package in BIDS that automates pretty much everything (the only user input required after executing is logging into Security when it pops up, then dropping and creating logins). It's saved me a lot of time.
I have a couple questions about for anyone who cares to answer...
First, does this seem all right to do (it has worked like a charm so far)?
Second, has anyone else done this with CL2T? I've only been using BIDS for about 5 months and am curious as to how other people have gone about things. I have a feeling I'm putting more strain on our network than is necessary.
Thanks,
Joshua Wager
Hi Joshua,
We use SSIS here as well to replicate to a test environment, a conversion environment and a failover environment. The SSIS package consists of "execute sql tasks" basically everything is written is SQL commands then placed in tasks on the SSIS package. The package is then stored as a job in SQL server and we too must drop and recreate users manually. I find the network noise to be very low doing this, and the job runs in approximately 50 seconds.
It works great for our needs.
Naomi
Thanks, Naomi - may I ask how your failover environment is set up? I've been looking into this, too but am a little lost - I'm kind of learning as I go.
And if you can think of ways to make it better (I'm sure anyone could!) I'd love to hear about it.
For instance, I can't tell if it would be better to backup directly to (and restore directly from) our data server. You could cut out a few steps (the copying and deleting) by doing this. I'm not sure which would be better to do.
I may not have to use this as much now that we've put all of our test components on virtual machines - all I have to do now if I'm trying out a script on the test database is take a quick snapshot of the v.m. before I run a script, and revert back after running it, and so on. This virtualization stuff (that's all new to me as well) is great for testing.