AZURE: Migrate data between Sql Server databases using Sql and PolyBase

Azure-migrate-data-between-servers.sql

-- Script to migrate data between Azure Databases
-- Author: Bruno Hildenbrand (https://github.com/hd9)
-- Full post detailed here: https://blog.hildenco.com
-- Note: replace everything between <> with your own configuration

print '--- STARTING MIGRATION ---'

-- create master key
print 'Creating master key...'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<super-strong-password>';
go

-- store credentials
print 'Creating credentials...'
CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = '<username>', SECRET = '<password>';
go

-- create remote data source
print 'Creating remote data source...'
CREATE EXTERNAL DATA SOURCE RemoteDataSource
WITH
(
    TYPE=RDBMS,
    LOCATION='<your-remote-server-url>',
    DATABASE_NAME='<remote-database>',
    CREDENTIAL= RemoteCred
);
go

-- create remote table locally
print 'Creating remote remote table locally...'
CREATE EXTERNAL TABLE [dbo].[RemoteTable] (
    [ID]        BIGINT          ,--IDENTITY (1, 1) NOT NULL,  # identities not allowed
    [FieldA]    NVARCHAR (200)  NULL,
    [FieldB]    NVARCHAR (2000) NULL,
    [FieldC]    NVARCHAR (MAX)  NULL
)
WITH
(
		DATA_SOURCE = RemoteDataSource,
		SCHEMA_NAME = '<remote-schema>',
		OBJECT_NAME = '<remote-table-name>'
)
go

-- drop local table if exists (uncomment if necessary)
-- if exists(select * from information_schema.tables where table_schema = '<local-schema>' and  table_name = '<local-table-name>')
-- begin
-- 	print 'Droping local table to reinitialize...'
-- 	drop table [<local-schema>].[<local-table-name>]
-- 	print 'Done'
-- end

-- insert recs
print 'Downloading and inserting recs. Please wait...'
select * 
into [dbo].[<local-table>]
from [dbo].[RemoteTable]
go

-- and remove artifacts no longer needed
print 'Droping no longer needed artifacts...'
drop EXTERNAL TABLE [dbo].[RemoteTable]
drop EXTERNAL DATA SOURCE RemoteDataSource
drop DATABASE SCOPED CREDENTIAL RemoteCred
drop MASTER KEY
go

print 'Getting # of migrated rows...'
declare @count int
select @count = count(0) from [dbo].[<local-table>]
print concat('  ', @count, ' records migrated', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10))

print '--- MIGRATION COMPLETED SUCCESSFULLY ---'

-- Good Luck! =)