I have huge amounts of data in a database and need to transfer that data to another server. I'm using SerializeJSON to write the data to files, that worked fine for smaller amounts of data but now I'm getting out of memory errors. The database servers are not in the same network. They cannor 'see' each other, so that's why I want to transfer the data in files.
Any thoughts about how I can transfer data from one database to another using files and ColdFusion?
The way I'd approach this is to cut out the application server (ColdFusion) and try to get the process rolling using DTS. The idea is to export the specific database rows from each table as a single export, which you can then import into the other database.
So look at your schema and work out the tables you need to query, and write those queries for each table. Depending on primary keys in the new database you may need to modify your import statements to avoid inserting the primary key and instead let the database take care of that - and you will need to adjust any other imports referencing this (changed) primary key.
It's a tricky job and I think you are best doing it by hand using database tools such as DTS.
I hope that helps.
Your question gives the impression that the OOM is occurring writing out data. Perhaps by attempting to write an entire recordset. Instead you probably need to write an algorithm that iterates through pages of data - SQL Server's ROW_NUMBER(ORDER BY x, y, z) is the right approach for this. You could then write out several files or use fileWrite(fileObj, data) to create one large file - the latter you probably need to do a format other than JSON, such as CSV.
Now if the issue is on data insert, there is a bug with ColdFusion and looping over SQL statements. Basically you need to put the <cfquery>INSERT...</cfquery> inside <cfthread/> when looping repeatedly.
Finally, if you're running into issues reading the data from a file you need to do buffered reads . In this case you'll either need to use CSV as your serialization format or use a SAX XML parser, if you use XML, but I am unaware of a JSON parser that reads in a buffered fashion.
Take a look at Jailer. It's a Java based GUI application that allows you to select specific tables of a database and export the data based on some criteria. It works very well and it's easy to use.
If you have the SQL Server Management Studio why not create a complete backup file, will all the data, then run a restore on the new database. That ought to restore all the schema, tables and data.
hth, larry
