SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quickly Copy Data


Quickly Copy Data

Author
Message
Alan Nordquist
Alan Nordquist
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 25
The expression I grew up with is, "Never underestimate the bandwidth of a station wagon full of tapes, speeding down the highway."
Alan Nordquist
Alan Nordquist
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 25
The expression I grew up with is, "Never underestimate the bandwidth of a station wagon full of tapes, speeding down the highway."
Alan Nordquist
Alan Nordquist
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 25
The expression I grew up with is, "Never underestimate the bandwidth of a station wagon full of tapes, speeding down the highway."
lptech
lptech
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 3443
Looks like we need to get introduce deduplication to the DBMS world. In the virtual world, VMWare and Hyper V only keep one copy of the same block in memory, and when doing backups for that matter. Granted, we have transaction log and differential backups, but deduplication for database backups should be built into the backup subsystem. So if only 1% of the database blocks have changed, the backup command should only back up those blocks. During restores, ALL of the blocks should be retrieved without DBA intervention.

Backupand restore is an area where SQL Server isn't even where DB2 was in V3, back in the early 1990's.
chillsdon
chillsdon
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 48
I copy SQL Server backups over the Internet. I have two remote servers connected using a (paid for) LogMeIn Hamachi2 Peer-to-Peer VPN. I then transfer the files over the VPN using rsync (a Linux application not used much in the Windows world but is available as part of the Cygwin install). No need to compress the backups - just let rsync (client/server) work out which blocks have changed and it will only transmit the differences.

Its all command line stuff so create a script and launch it from another step in your SQL backup job following the actual backup.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: Administrators
Points: 149476 Visits: 19448
lptech (6/6/2013)
Looks like we need to get introduce deduplication to the DBMS world. In the virtual world, VMWare and Hyper V only keep one copy of the same block in memory, and when doing backups for that matter. Granted, we have transaction log and differential backups, but deduplication for database backups should be built into the backup subsystem. So if only 1% of the database blocks have changed, the backup command should only back up those blocks. During restores, ALL of the blocks should be retrieved without DBA intervention.

Backupand restore is an area where SQL Server isn't even where DB2 was in V3, back in the early 1990's.


I would agree. Some of the de-dupe products don't work well over time with SQL Server, since those original blocks have to be maintained over time and assembled with the changed ones.

However, I'd think the backup process could be streamlined and at the same time smart enough to produce some "changed blocks" from a base backup, excluding indexes. If we throw those out, because they are re-org'd, then I think lots of data rarely changes.

The thing is, we'd need to rewrite those blocks the same instead of just streaming out pages. After index movement of data, including the clustered indexes, the blocks are not necessarily in the same spot each day when they are streamed out from the datbaase.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jason 63816
jason 63816
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 48
1) Compression Tools - the free tools do such a great job, why bother paying for something? 7zip works great and I usually get a SQL backup down to 5-10% of the original size. If you want to pay for proprietary tools, have at it - but consider if you are actually getting a better tool. I'm not aware of any magic tool that gives you significantly better compression just because you pay for it (in fact, I (very unscientifically) seemed to get better results with 7zip than with winzip.

2) I was also going to suggest rsync. There is a product called Syncrify Backup that incorporates rsync packaged up with a backup client and backup web server package, very reasonably priced. Works great. It will also compress the network traffic, so you don't need to compress beforehand (and probably works better uncompressed, since the SQL backup is likely to have fewer changed blocks of data that way - depends on specific workloads for your database of course). Allows for versioning on the backup server as well, so you could potentially restore a backup from days or weeks ago (and only uses up space for the changed blocks, so you don't need full copies of the database for each version).
philip-825831
philip-825831
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 55
Being a resourced (money) constrained part-time DB admin, I use free tools to transfer my backups.

I compress with gzip using the rsyncable flag, then transfer with rsync. For those wanting to use rsync, and are storing compressed data, make sure the compression is rsync compatible. Plenty of information on this is out there.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14899 Visits: 11848
To copy a backup of a large database over a WAN, I recommend the following:
1. Make a compressed backup of the database to multiple output files. Example: Make 20x5 GB files, instead of 1x100 GB file. The compression and multiple output file options are available with the BACKUP command.
2. Setup multiple streams of file copies so that you are copying multiple files at a time, say 5 in parallel. This will help to make sure that the WAN pipe stays full.
3. If possible, make sure that the source and target machines for the file copy are running Server Message Block (SMB) 2.0 or higher protocol (Windows Server 2008 and above, Windows Vista, Windows 7, Windows 8) . There were big enhancements to this protocol that dramatically improve performance on high latency links (WAN connections).
djackson 22568
djackson 22568
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2672 Visits: 1241
It comes down to doing analysis. Too often we get into the rut of doing things exactly the same way every time.

When faced with a problem that seems overwhelming, throw out all assumptions. Everything. Then ANALYZE the problem. Determine the facts, restraints, and make sure they are real and not imagined. Then start thinking about how to fix it. Take things a step at a time.

A good example occurred to me today. Someone had a complex spreadsheet that had data elements used to make a decision. Instead of helping them write a formula to do what they wanted, I wrote a formula to do a portion of what they wanted. Then another, and another, and another. Once those were done and tested, I explained that they could combine them all into one formula (good luck!) or simply write one more that based the answer on the ones we wrote. All I did was break the work down into manageable pieces.

What seemed to be impossible (to them) seemed so obvious once I showed them the methodology I use. I didn't do anything special, and nothing they could not have done themselves. The difficulty they had was they were focused entirely on the big picture of AZ columns and 7,000 rows. When they explained each piece to me one at a time, it was easy to do.

Dave
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search