Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Quickly Copy Data Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 7:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
EdVassie (6/10/2013)
There are other ways of tackling this issue, but they do not exist yet for SQL Server.

Back in the late 1990's a number DB2 shops stopped taking full backups of their databases. They used functionality in some third-party backup products called 'offline database merge'.

The idea of this way to take an existing full backup, and apply to it either a differential or a log backup, giving you a new full backup. If you applied a log backup, you could choose any point of time in the log that the new full backup should relate to.

One advantage of this aproach is that the merge process could take place away from the database server. If you already had your full backup on an offsite server, you only needed to get your log backup sent there, and you could do the backup merge. The only time this process needed to connect to a database server was to register the new full backup.

IMHO this is a process that vendors of SQL backup products could ad to their product, assuming they can work around any patent issues that may exist. Anyone want to take up this challenge?


Gosh... sounds as simple as "merge replication" or "log shipping".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461555
Posted Monday, June 10, 2013 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,900, Visits: 3,301
sounds as simple as "merge replication" or "log shipping".


More like log shipping than merge replication, certainly in terms of complexity and effort.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1461592
Posted Monday, June 10, 2013 9:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:22 AM
Points: 2,923, Visits: 1,873
Jeff Moden (6/6/2013)

After the systems were down, I made a tape backup and FedEx'd it. We had everything back up and online with nearly half a day to spare.


Just goes to show that the solution doesn't always involve technology.

We do have electronic scrum boards but the old post card and blue tack method seems to work well.
Post-It notes for defects are good as well though for some reason I've started to have nightmares about Sesame Street.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1461603
Posted Monday, June 10, 2013 11:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:45 AM
Points: 893, Visits: 2,477
To start with, here's an example of a .7z compression command line might be:
7z a -t7z -mx1 -m0=LZMA2 -mmt8 -ssw -bd -y -pPassword -r My7zFile.7z "C:\test\*"

-t7z means use 7z file format (excellent compression at -mx9, and good speed with -mx1)
-mx1 means use very fast compression, while -mx2..8 are intermediate steps, and -mx9 is very tight compression More compression = more RAM required
-mmt8 means 8 threads. -mmt3 means 3 threads, -mmt1 means 1 thread, etc. More threads = more RAM require (at 4, 6, 8, etc., so 3/5/7 are good choices)
-ssw means also try to back up files with share locks, i.e. some types of opened/locked files. Consider carefully about this one.
-bd means no percentage indicator
-y means no prompts
-pPassword means use enryption (for -t7z files, that's AES), and use Password as the password. You could use -psWO4UXZRaykA and have sWO4UXZRaykA as your password.
-r means recursive - take if off if you're specifying files


As another post suggested, you must break each situation down.
First, you have requirements:
A) Maximum timeframe available (starting)
Ai) On the source side
Aii) On the target side
B) Maximum timeframe available (change set)
Bi) On the source side
Bii) On the target side
C) Allowable impacts (slowness, offline, shutdown, etc.)
Ci) On the source side
Cii) On the target side
D) Privacy, confidentiality, and other encryption/security requirements
Di) In some cases, this means encryption
Dii) In others, this means a licensed, bonded courier

Then, you have your situation - note that "compressed" values depend on your particular dataset - you'll have to test them.
1) Starting data size (uncompressed)
1a) Accounting for growth over N years
2) Starting data size (compressed)
2a) Accounting for growth over N years
3) Change set over time size (uncompressed) (minimum, average, maximum)
3a) Accounting for growth over N years
4) Change set over time size (compressed) (minimum, average, maximum)
4a) Accounting for growth over N years
5) Latency for each transfer method (uncompressed)
6) Latency for each transfer method (compressed)
7) Throughput for each transfer method (uncompressed)
8) Throughput for each transfer method (compressed)
9) Restartability
9a) Impact of restartability if not required, and if required

If we look at a few possible transfer options, remembering that the physical drives can be easily set up in parallel if your machines have the ports:
I) Local gigabit
I5) 5ms latency uncompressed
I7) 110MB/s throughput uncompressed
II) 15Mbps internet link
II5) 50ms latency uncompressed
II7) 1.5MB/s throughput uncompressed
III) 4TB USB2.0 drive, local
III5) 9ms latency uncompressed
III7) 30MB/s throughput uncompressed
IV) 4TB USB3.0 or eSATA or SATA or SAS drive, local
IV5) 9ms-22hr latency uncompressed
IV7) 110MB/s throughput uncompressed
V) 4TB USB3.0 or eSATA or SATA or SAS drive, shipped, 12 hour transit time
V5) 12-34 hour latency uncompressed (0-11 hours to fill, 12 hours to ship, 0-11 hours to read)
V7) 0-32MB/s throughput uncompressed (0-11 hours to fill, 12 hours to ship, 0-11 hours to read)
VI) 10.3TB Fusion ioDrive Octal, local
VI5) negligible latency uncompressed
VI7) 3.9GB/s write, 6.7GB/s read
VII) 10.3TB Fusion ioDrive Octal, shipped, 12 hour transit time
VII5) 0-12 hr latency uncompressed
VII7) 0-215MB/s throughput uncompressed (.75 hrs to fill, 12 hours to ship, .5 hours to read)

For transfer over networks, we can use copy, robocopy, Windows GUI, etc.
For compression, I'm going to join in with the 7-zip crowd, and specify a few things:
DO use 7z format, and LZMA2 mode.
You do NOT have to compress, copy, uncompress. Instead, you can compress locally, and then uncompress from the remote machine, so only the compressed data goes over the network! Alternately, you can compress _to_ the remote machine.
Note that I ran a quick, fake data test - a 768MB files of all 0x00 values (trivially compressible)
Straight Windows copy to a machine several hundred miles away, 250KB/s.
7-Zip compression via GUI, .7z, LZMA2, 64KB dictionary size, Solid, 273 byte word size, Fastest, directly to the machine (\\remote\share\test.7z), 20MB/s BEFORE decompression.
7-Zip compression via GUI, .7z, LZMA2, 64KB dictionary size, Solid, 273 byte word size, Fastest, directly to local and then the .7z file copied via Windows GUI, 96MB/s BEFORE decompression.
Post #1461684
Posted Monday, June 10, 2013 12:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
David.Poole (6/10/2013)

Post-It notes for defects are good as well though for some reason I've started to have nightmares about Sesame Street.


Heh... and "Agile Scrums". "Big Bird" attends both.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461712
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse