Bulkcopy 100 million rows of data to Azure SQL Server using C#?

c# sqlbulkcopy sql-server-2017

Question

I have a local SQL Server 2017 database, and I need to copy two tables to an Azure SQL Server database; one table has over 100 million rows of data including a "geography" type column. How do I do that?

I am right now running a bulk copy:

using (SqlConnection streamsConnection = new SqlConnection(streamsConnectionString))
{
    streamsConnection.Open();

    using (SqlConnection cloudConnection = new SqlConnection(cloudConnectionString))
    {
        cloudConnection.Open();

        using (SqlCommand cmd = streamsConnection.CreateCommand())
        using (SqlBulkCopy bcp = new SqlBulkCopy(cloudConnection))
        {
            bcp.DestinationTableName = "GroundDataNodes";
            bcp.BatchSize = 200000;
            bcp.BulkCopyTimeout = 1200;
            bcp.NotifyAfter = 100000;
            bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);

            cmd.CommandText = "SELECT [Id],[nodeid],[latlon],[type],[label],[code],[lat],[lon]FROM[dbo].[GroundDataNodes]";

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                bcp.WriteToServer(reader);
            }

            Console.WriteLine("Finished!");
            Console.ReadLine();
        }
    }
}

But I'm quite new to the bulk load side and am wondering how I can improve this so it doesn't take weeks to run...

Popular Answer

so I have tried all sorts of upload stuff like the bulk upload, migration, backup etc but all had the same problem and that was my upload speed isn't up to it. It would work but take days to run. So I decided to write a server side bit of code to just populate it from there directly onto the database therefore taking my upload speeds out of the equation. I imagine if I had better upload speeds the migration tool would have worked fine even with the Geographic field etc. not quick but it would work.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why