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
This looks like a great solution to something which I usually have to schedule half a day for (including contingency time). Am going to try and set this up, if I have any problems or questions I'll know who to call!!
Thanks, Debbie
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.
I can send you the .sln if you'd like, Debbie - I know the thing could be made smarter by creating variables for the servers and whatnot - haven't done that yet. But if you have the .sln all you'd really have to do is modify the connections and a few other settings within the individual task configuation windows.
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.
A copy of the .sln would be brilliant, thank you. We arn't using any virtual servers at the moment. We have a LIVE and a TEST setup and we copy the database nightly onto another server, just as a DR check.
Basically all we have is a separate SQL instance on our test box that we replicate to, then point the t_defaults settings to the failover api, credit card server, report server, and seat server.
This allows us to have an immediate failover environment and a test environment without the additional hardware or schedule issues. We use to replicate our live environment over to the test environment everyday but if someone was building something in test it would be written over daily which became an issue. Now if our production server goes down we just bounce over to the failover system because it is kept only 10 minute difference from production.