tag:support.appharbor.com,2010-11-23:/discussions/problems/171-populating-a-database-using-a-large-datasetAppHarbor: Discussion 2011-04-08T03:50:22Ztag:support.appharbor.com,2010-11-23:Comment/53068302011-02-13T19:36:42Z2011-02-13T19:36:42ZPopulating a Database using a Large Dataset<div><p>I'm assuming you're running the above code from your local
machine. You could probably speed up the process by running the
data-population code from a machine with low latency to our
database servers. You could, for example, boot up a an EC2 instance
in the us-east region and run your code there.</p>
<p>We'd love to learn how you would like to move data to your
AppHarbor database. Right now we are looking at letting you upload
SQL Server backup-files and having us restore them, or at providing
a script that makes it easy to use to use <a href=
"http://msdn.microsoft.com/en-us/library/ms162802.aspx">SQL Server
Bulk Copy</a> (bcp).</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/53068302011-02-13T20:34:04Z2011-02-13T20:34:04ZPopulating a Database using a Large Dataset<div><p>Thanks Michael. I looked into bcp a little, but I thought it can
only populate one table? Since I have multiple tables, I didn't
think I could use it.</p>
<p>I've never used EC2 before. Any info on how to go about starting
up an instance?</p></div>tanseytag:support.appharbor.com,2010-11-23:Comment/53068302011-02-13T20:49:42Z2011-02-13T20:50:05ZPopulating a Database using a Large Dataset<div><p>I'll try and do a PowerShell script that bcp's all tables in a
database in the afternoon (although I have to think about
referential constraints).</p>
<p>For EC2, you have to register for <a href=
"http://aws.amazon.com/ec2/">Amazon Web Services</a>. Once you're
in the EC2 console, you can boot up Windows instance. It'll
probably take you around an half-an-hour the first time you try it.
For the data-population you need to do, a 'small' instance should
do the job. Remember to shut down the instance once you're done
(you pay by the hour).</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/53068302011-02-13T22:56:33Z2011-02-13T22:56:33ZPopulating a Database using a Large Dataset<div><p>Great! For the shell script, would I just need to create a
database locally and run the script on it?</p></div>tanseytag:support.appharbor.com,2010-11-23:Comment/53068302011-02-23T16:11:51Z2011-02-24T06:31:57ZPopulating a Database using a Large Dataset<div><p>Hi Wesley, really sorry about the wait.</p>
<p>I have a console app for you, <a href=
"https://github.com/appharbor/AppHarbor-SqlServerBulkCopy">get it
on Github</a> and check out the <a href=
"http://blog.appharbor.com/2011/2/24/moving-data-between-microsoft-sql-server-databases">
explanatory blog post</a>. Build the project and run the console
app. You can see the <a href=
"https://github.com/appharbor/AppHarbor-SqlServerBulkCopy/blob/master/src/AppHarbor.SqlServerBulkCopy/Program.cs">
required parameters in the source</a> -- they should be
self-explanatory.</p>
<p>You need to make sure that source and destination database
schemas match (you can generate database creation script by
right-clicking source database -> tasks -> Generate
scripts).</p>
<p>Again, sorry about the wait and let me know if you hit any
snags.</p></div>friism