October 7, 2008 at 10:55 am
You can remove the free space before you backup, etc. but that will only help your restore time as when you take a backup only the used space is backed up to the file.
You said you had the Redgate tools, can you use their backup tool to compress the backup?
Your db is pretty big. If you use a standard compression tool (Rar, Winzip, etc) if you can even get them to finish it'll take forever to compress and decomp.
Chris.
Chris.
October 7, 2008 at 11:11 am
I have a RedGate tool and I am planning to use it when I get the full back up from them. Do you know what can they do not to loose anytime to shrink the server?
October 7, 2008 at 11:24 am
Do you mean, can they shrink the database with users in it?
Chris.
October 7, 2008 at 11:26 am
Yes
October 7, 2008 at 11:28 am
You said I can remove the free space before I backup, How?
October 7, 2008 at 11:45 am
Here's how I remove the free space from a database. This is a logged operation so I switch to simple mode before I run it.
dbcc shrinkfile([data file name], [Desired size])
You can get the [data file name] by running this in the database:
Select * from sysfiles
Chris.
Chris.
October 8, 2008 at 7:14 am
I hate to rain on anyone's parade, but what I'm hearing is that there's no direct connection between the two servers, and there's an expectation of zero down-time. Sorry, but that's just not realistic. Even assuming that the original poster has connections to both environments at fairly high speed internet connections (I'll assume best case of 20mbps), the transfer time alone would be gigantic for 300 GIG. At 20mbps, that's 20 megabits, or roughly 2 megabytes per second. Even if you could achieve 100 percent network utilization for the entire transfer, that would require 300 Gigabytes x 10 bits per byte (to cover xmit overhead) or 3 Terabits, divied by 20 megabits, or roughly 1/6 of a million seconds. Convert that to minutes and you have 2777.77... , and in hours, that's just shy of 46.3, or just less than 2 full days. Of course, across the internet, there's absolutely ZERO CHANCE at 100% network bandwidth utilization for more than a few seconds at a time. If you managed to achieve 50% to 60%, you'd be getting extraordinary results, and typical results are often in the 40% to 50% range. Thus, you're talking about anywhere from 4 to 5 days, just to transfer the data, and that assumes no breaks in the connection during that time, which could potentially seriously complicate matters.
Now let's take a more realistic look at the intermediary's more likely internet connection: 5 mbps cable internet, or perhaps worse yet, a DSL connection at 2mbps. At those speeds, the time and throughput place the data transfer time at closer to a month or two. Hopefully, this data will convince all parties that a network transfer without anything less than a gigabit network direct connection, is totally absurd.
Thus what will HAVE to be done is backup to tape or to removable USB or FireWire hard drive. With zero down time, you could never catch up on transaction log restores, if for no other reason than the time it takes to transport the backup media. I think you can forget about zero down-time. It's just not possible as long as there's no direct network connection.
That said, however, IF you have sufficiently low transaction volume, AND thus you can transfer transaction log backup files in a short time window across the internet, you may be able to take the full backup to tape/disk, get the tape/disk transported to the new server, restore it, and then perhaps a late-night transaction log backup and extremely short down-time window for the final t-log backup and file transfer.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 8, 2008 at 7:33 am
Steve,
Agreed, its a pretty tall task and what you suggest is by far preferred over what I'm suggesting.
If yulichka compresses it with a backup compression tool such (Redgate, Quest, Idera, take your pick) he won't have a 300 GB database anymore. In fact there's a chance that there's lots of free space in his db as well.
He may be able to get down around 30-50 GB. Then, like you say if the transaction volume is low enough he can apply the logs to the new server and switch over at a low volume time.
Chris.
Chris.
October 8, 2008 at 7:59 am
Yes, and tall task is a bit of an understatement, as I just realized that the transfer time's I calculated would all be doubled, because as an intermediary, the original poster would require two transfers to move the data instead of just one - one to get the data FROM the source, and a second to transfer TO the destination. Even if the OP actually has only 10% of his database's physical size in terms of actual data, the absolute minimum transfer time is still around 9 hours, with a more realistic time of 4 to 5 days. That's well beyond reasonable. The OP's only option is a backup to removable media, with perhaps Fedex transportation, unless the OP has a fairly short actual distance between source and destination locations, and can drive the media from one to the other in a reasonably short time-frame.
Steve
(aka smunson)
:):):)
CDP (10/8/2008)
Steve,Agreed, its a pretty tall task and what you suggest is by far preferred over what I'm suggesting.
If yulichka compresses it with a backup compression tool such (Redgate, Quest, Idera, take your pick) he won't have a 300 GB database anymore. In fact there's a chance that there's lots of free space in his db as well.
He may be able to get down around 30-50 GB. Then, like you say if the transaction volume is low enough he can apply the logs to the new server and switch over at a low volume time.
Chris.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 8, 2008 at 8:06 am
Agreed what I'm suggesting is only as a last resort. I've seen companies send people on planes with backup tapes to do this before...
Chris.
October 8, 2008 at 8:07 am
The backup transfer time is less of an issue than the log backup sizes. You can easily FedEx the backup, or take 2 days to xfer it if in the time between the backup and application you haven't created too many log backups. Those are the key to minimizing downtime. Diffs can help here, but the logs matter.
If the full is too large, I'd FedEx it today, take the backup and burn to DVD/Disk as closer to the Fed Ex pickup as possible and then start log backups and xferring them to the new facility. That will let you play catch up.
Granted it's a pain, it might take 4-5 days to get things synched up, but that's the way it works and you can carry on logs until you can make the xfer, but you will have downtime. Might get it down to a few minutes, but it's going down.
Or you're going to be out of sync. You could conceivably make the switch in real time, one to the other, but you'll have data that hasn't been transferred, and you won't be able to use logs to move it. You could use a log tool to get the last xx minutes of transactions and manually run them, but that's risky in my mind.
October 8, 2008 at 8:32 am
How do I know Desired size? Thank you
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply