Skip to content

Instantly share code, notes, and snippets.

@hd9
Created February 1, 2019 18:20
Show Gist options
  • Save hd9/6c75b7e7e7be344ab986036c5520999e to your computer and use it in GitHub Desktop.
Save hd9/6c75b7e7e7be344ab986036c5520999e to your computer and use it in GitHub Desktop.
AZURE: Migrate data between Sql Server databases using Sql and PolyBase
-- 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! =)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment