Using Powershell to Bulk Copy Data between SQL Server and Azure SQL
First off, hi, I've been active in the various PASS SQL User Groups and PASS SQL Saturday events, participated on various LinkedIn, Twitter #sqlhelp, SQLServerCentral, and other community forums over the years, but never quite got around to setting up a blog. I wanted to answer a complex question on another site and figured I might as well get this up here.
I hope one day to also get some of this intel posted up on a GitHub site of a similar name. Big plans, they say.
This first post was prompted by an inquiry HERE. I had a similar question come up myself some time ago when needing to move tens and even hundreds of GB from SQL on Azure IAAS (not meaningfully different from doing so "on-prem") to Azure SQL PAAS. I didn't want to use SSIS for several reasons, but most of all wanted to control the whole process from a lightweight and quick-to-modify script. I also had the need to juggle dozens of tables across hundreds of databases and even tens of instances, so I knew I needed a scripting language like PoSH to solve the riddle.
So I Bingled for various phrases and commands, ending up HERE. It's an old article from Ed Wilson, Microsoft "Hey Scripting Guy!" Though the article is a bit aged, the value's still there! While the trusty stablemate Invoke-SqlCmd remains an option, there's also value in leveraging some of the .Net componentry available behind the scenes. The aforementioned example (and many others) are leveraging the System.Data.SqlClient library, fully documented HERE. It's a big read and assumes you understand basic Object Oriented Programming concepts.
To simplify things while giving you at least something to work with, I created this function:
This function has a dependency on my Connection String builder, provided here:
Both functions together enable you to rapidly take data from SourceA and deliver to TargetB. The ConnectionString builder can work with integrated (Windows) authentication or SQL authentication like you might expect in Azure SQL. The SQLFn-BulkInsert function leverages (4) key SQLClient classes: SQLConnection, SQLCommand, SQLDataReader, and SQLBulkCopy. There are so many other valuable classes - read through them when you get a chance, the possibilities are... endless!
Back to the presented script - you simply place both files in the same path, open a Powershell window, and call:
This is by no means a complex solution, I created it to be able to move a table at a time to a common Azure SQL location for reporting reasons in an environment where data selectivity was more costly than simply scheduling a dump of a subset of critical tables. The environment is software as a service, having hundreds of exactly the same database schema. Trying to manage more complex ETL simply wasn't happening in the time and compute constraints available!
This is my first pass at a blog post! Love it or hate it, feel free to send me a message with comments or questions. Find me at @sqlmadhadr!
I hope one day to also get some of this intel posted up on a GitHub site of a similar name. Big plans, they say.
This first post was prompted by an inquiry HERE. I had a similar question come up myself some time ago when needing to move tens and even hundreds of GB from SQL on Azure IAAS (not meaningfully different from doing so "on-prem") to Azure SQL PAAS. I didn't want to use SSIS for several reasons, but most of all wanted to control the whole process from a lightweight and quick-to-modify script. I also had the need to juggle dozens of tables across hundreds of databases and even tens of instances, so I knew I needed a scripting language like PoSH to solve the riddle.
So I Bingled for various phrases and commands, ending up HERE. It's an old article from Ed Wilson, Microsoft "Hey Scripting Guy!" Though the article is a bit aged, the value's still there! While the trusty stablemate Invoke-SqlCmd remains an option, there's also value in leveraging some of the .Net componentry available behind the scenes. The aforementioned example (and many others) are leveraging the System.Data.SqlClient library, fully documented HERE. It's a big read and assumes you understand basic Object Oriented Programming concepts.
To simplify things while giving you at least something to work with, I created this function:
This function has a dependency on my Connection String builder, provided here:
Both functions together enable you to rapidly take data from SourceA and deliver to TargetB. The ConnectionString builder can work with integrated (Windows) authentication or SQL authentication like you might expect in Azure SQL. The SQLFn-BulkInsert function leverages (4) key SQLClient classes: SQLConnection, SQLCommand, SQLDataReader, and SQLBulkCopy. There are so many other valuable classes - read through them when you get a chance, the possibilities are... endless!
Back to the presented script - you simply place both files in the same path, open a Powershell window, and call:
This is by no means a complex solution, I created it to be able to move a table at a time to a common Azure SQL location for reporting reasons in an environment where data selectivity was more costly than simply scheduling a dump of a subset of critical tables. The environment is software as a service, having hundreds of exactly the same database schema. Trying to manage more complex ETL simply wasn't happening in the time and compute constraints available!
This is my first pass at a blog post! Love it or hate it, feel free to send me a message with comments or questions. Find me at @sqlmadhadr!
Comments
Post a Comment