In today’s world IT professionals may find themselves involved with moving massive amounts of data more than in days past. With cheaper servers, open source operating systems and open source databases, you may be asked for example to move from Oracle on SPARC processors running Solaris to PostgreSQL on Intel processors running Linux. With database virtualization you might be asked to migrate a database from “raw iron” (i.e. dedicated physical server) to a hypervisor. And now with the cloud you could  be asked to relocate a database from on-premise to the public cloud. The point is that today there are many reasons to move entire databases from source to target. Moreover there could be reasons to move large portions of the data as well. You might be following good data life cycle management guidelines and moving older data to slower, less expensive secondary and tertiary storage (e.g. FLASH/SSD -> 15K SAS -> 7200RPM SATA). Likewise you might be using the Microsoft SQL Server “stretch database” concept to keep hot data on-premise and warm to cold data in a public cloud, regardless of whether your chosen database platform inherently offers that capability. For as myriad of reasons you could be asked to move mountains of data.

So what tool should you use? If I were doing some landscaping on my yard and the goal was to selectively place some new dirt in key locations to level my yard then I would use a shovel and wheel borrow. If instead I needed to raise the entire yard by two inches due to erosion, then I would probably employ a dump track and bulldozer. My point – always use the right tool for the right job. Moving data is no exception. I generally like to break the data movement tools into two categories: highly selective as with a rifle vs. scattered with a shotgun. When I hunt bear I need bullets to penetrate the thick skin and do large amounts of internal damage. Thus I need a bolt action, high powered rifle. When I hunt pheasant however I need to be able to shoot them as they take flight and accuracy cannot be counted upon. Thus I need a semi automatic shotgun with wide choke (i.e. spread of the pellets) and repeat fire in case I miss. I believe that the figure below very simply and effectively distinguishes their key difference.

rifle-vs-shotgun

Large data migration projects are no different. Common ETL (extract, translate and load) tools are far more like the rifle than the shotgun. They are better at per table operations and complex, conditional logic. They are good moving a table or small groups of related tables from source to target. But asking these tools or those using them to define projects to move hundreds or even thousands of tables is not just Herculean, but in most cases simply overwhelming. You need a data shotgun. You you need  is Cirro’s Data Puppy. Data Puppy has been designed from the ground up to move large numbers of tables from source to target while using patented algorithms to handle any and all required implicit data conversions due to internal implementation differences across database platforms. Moreover Data Puppy possesses a highly scalable, threaded processing design which enables leveraging today’s computing power. Want to move 15,000 Peoplesoft tables on Oracle to PostgreSQL, you simply define a Data Puppy project and run it. For example doing exactly that took me about 5-10 minutes to define and an hour or so to run. So choosing the right tool was critical in getting this task done on time and within budget.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About bscalzo2

Bert Scalzo is an Oracle ACE, blogger, author, speaker and database technology consultant. His work experience includes stints as product manager for DBArtisan and Rapid SQL at IDERA and chief architect for the popular Toad family of products at Quest Software. He has three decades of Oracle® database experience and previously worked for both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and his academic credentials include a BS, MS and Ph.D. in computer science, as well as an MBA. He has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert’s areas of interest include data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®. He has written for Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ and Toad World. Bert has also written the following books: • Oracle DBA Guide to Data Warehousing and Star Schemas • TOAD Handbook (1st Edition) • TOAD Handbook (2nd Edition) • TOAD Pocket Reference (2nd Edition) • Database Benchmarking: Practical Methods for Oracle & SQL Server • Advanced Oracle Utilities: The Definitive Reference • Oracle on VMware: Expert Tips for Database Virtualization • Introduction to Oracle: Basic Skills for Any Oracle User • Introduction to SQL Server: Basic Skills for Any SQL Server User • Toad Unleashed • Leveraging Oracle Database 12cR2 Testing Tools • Database Benchmarking and Stress Testing (coming 2018)

Category

Uncategorized