JosiahSiegel

PostgreSQL/Citus Migrate Production Data

Big Database Migration

⚠️Recommends support request⚠️

Small Database Migration

No support request

1. Set variables

JosiahSiegel

Login and install ssh extension

az login
az extension add --name ssh

Create 24 hour auth in ssh config

az ssh config --resource-group myResourceGroup --name myVm --file ./sshconfig

JosiahSiegel

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="7zip.install" />
  <package id="adobereader" />
  <package id="azure-data-studio" />
  <package id="chocolatey" />
  <package id="chocolatey-compatibility.extension" />
  <package id="chocolatey-core.extension" />
  <package id="chocolatey-dotnetfx.extension" />
  <package id="chocolateygui" />

JosiahSiegel

/*
CREATE TABLE [dbo].[test_jsonmerge](
	[id] [int] NOT NULL,
	[people_id] [varchar](50) NOT NULL,
	[last_name] [varchar](50) NULL,
	[sms_status] [varchar](50) NULL,
 CONSTRAINT [PK_test_jsonmerge] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[people_id] ASC

JosiahSiegel

Check for errors

AzureSQLDataSyncHealthChecker

Reset table

1. Uncheck table from sync source.
2. Confirm that all resources are automatically removed via above step:

-- github.com/microsoft/sql-server-samples/blob/master/samples/features/sql-data-sync/clean_up_data_sync_objects.sql

JosiahSiegel

DECLARE
@schema SYSNAME = '',
@table SYSNAME = ''

-- Declare variables to store column name and data type
DECLARE @column_name varchar(50)
DECLARE @data_type varchar(50)

-- Declare a cursor to loop through the columns of the table
DECLARE column_cursor CURSOR FOR

JosiahSiegel

-- REQUIRES: https://gist.github.com/JosiahSiegel/2195295eb20bd865f3b8543d098f0c4d
-- Keep two tables synchronized based on the primary key 
-- and compares all columns for updates.
-- Use if unable to identify a suitable update column
ALTER PROCEDURE [sync].[sync_tables_all_columns_sp]
    @source_db SYSNAME,     -- The name of the source database
    @source_schema SYSNAME,  -- The name of the source schema
    @source_table SYSNAME,  -- The name of the source table
    @target_db SYSNAME,     -- The name of the target database
    @target_schema SYSNAME,  -- The name of the target schema

JosiahSiegel

-- Create a table variable to store the table names and synonyms
DECLARE @TableSynonyms TABLE (
    TableName NVARCHAR(128),
    TableSchema NVARCHAR(128),
    SynonymName NVARCHAR(128),
    SynonymSchema NVARCHAR(128)
);

-- Insert the table names and synonyms into the table variable
-- You can change the logic for generating the synonym names and schemas as per your requirement

JosiahSiegel

-- Used by: https://gist.github.com/JosiahSiegel/ff740303e462da2c25e53cd97cc4d380
-- Keep two tables synchronized based on the primary key
ALTER PROCEDURE [sync].[sync_tables_sp]
    @source_db SYSNAME,     -- The name of the source database
    @source_schema SYSNAME,  -- The name of the source schema
    @source_table SYSNAME,  -- The name of the source table
    @target_db SYSNAME,     -- The name of the target database
    @target_schema SYSNAME,  -- The name of the target schema
    @target_table SYSNAME,   -- The name of the target table
    @update_column SYSNAME = NULL, -- The name of the column that determines if the source row was updated

JosiahSiegel

SELECT 
	'CREATE synonym [' + s.name + '].[' + t.name + '] for [' + DB_NAME() + '].[' + s.name + '].[' + t.name + ']' 
from sys.objects t
inner join sys.schemas s
	ON t.schema_id = s.schema_id
where t.type IN ('U', 'V', 'P')