November 18, 2002 at 8:26 am
My boss just changed his mind once again. Now he wants me to copy all of this years data to another set of drives. That means doing a snapshot of 192 GB of data. Has anyone done this? What I'm looking for is:
1. How long will it take?
2. Will it 'tie' up my databases? or can I do it without affecting my users?
3. Any suggestions on the best way to accomplish this?
-SQLBill
November 18, 2002 at 9:46 am
I have done transactional replication on a 40gb database. I initialized the subscription server using a snapshot. I took about 8-9 hours to transfer the data. I also had Concurrent Snapshots turned on. With that turned on, no locks are placed on the publication database while the snapshot is being applied. This allows processing to continue at the publisher as usual. It then applies the transactions after the snapshot is in place on the destination server. I personally would not recommend this method. The application of the snapshot is trasaction logged on the destination server, so you would need a VERY large transaction log. I would recommend using a database backup to apply to the subscriber. It is easiest to do this when there is little server activity. When you create the subscription, you can specify that the data already exists at the subscriber. If you use the backup/restore method, you will have to generate the replication procs yourself at the subscriber. There is a proc called: sp_scriptpublicationcustomprocs that will create the scrips to be compiled at the subscriber. That proc is included with SQL 2000 service pack 1.
-Dan
-Dan
November 18, 2002 at 10:29 am
This won't be used for tranactional replication. It's just a one-time copy of the whole database. I completely overlooked the option of just restoring the database from tape to the new set of drives. I was trying to make my job harder than it needs to be.
-Bill
November 18, 2002 at 6:24 pm
Yes, deffinmently use the backup restore method. 192Gb will take quite a while and has many factors that affect the time.
November 22, 2002 at 4:32 am
Why not to use detach and attach.
So the copy could be done at the LAN speed, Attaching the DB is also fast.
So if you do not have the most accurate (I mean the last second) data then I would do this.
Gabor
Bye
Gabor
November 24, 2002 at 11:58 pm
You should be able to just back it up to a fast tape system and restore from the tape. The tape shouldn't take more than 4-6 hrs probably. Once you're done with that, the restore probably won't be considered time crucial.
David
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply