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

c# sqlbulkcopy sql-server-2017

Question

Two tables from a local SQL Server 2017 database that include over 100 million rows of data and a "geography" type field need to be copied to an Azure SQL Server database. How can I go about doing that?

I'm now operating a mass 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();
        }
    }
}

However, I'm quite new to the bulk load side, so I'm wondering how I might make this run more quickly.

1
0
8/4/2018 3:06:15 PM

Popular Answer

I've tried a variety of upload methods, including bulk upload, migration, backup, etc., but they've all run into the same issue: my upload speed isn't enough. However, it would take days to complete. In order to remove my upload speeds from the equation, I made the decision to develop a server-side piece of code that would just fill it from there straight into the database. Even with the Geographic field and other features, I assume the migration tool would have worked just fine if I had faster upload rates. It wouldn't be rapid, but it would function.

0
8/6/2018 3:40:22 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow