JosiahSiegel

-- Elastic Agent Jobs
-- Official doc: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage?view=azuresql
-- Unofficial doc: https://sqlkitty.com/elastic-jobs-azure-sql-db/

-- Add target group
EXEC jobs.sp_add_target_group 'AzureSQLDBs';

-- Add single database (or server/elastic pool) to target group
EXEC jobs.sp_add_target_group_member
@target_group_name = 'AzureSQLDBs',

JosiahSiegel

SELECT TOP 100
    t.NAME AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    MAX([data_compression_desc]) AS [Compression],
    MAX(p.rows) AS [RowCounts],
    SUM(a.total_pages) AS [TotalPages], 
    SUM(a.used_pages) AS [UsedPages], 
    SUM(a.data_pages) AS [DataPages],
    (SUM(a.total_pages) * 8) / 1024 AS [TotalSpaceMB], 
    (SUM(a.used_pages) * 8) / 1024 AS [UsedSpaceMB], 

JosiahSiegel

SELECT
    [System.State],
    [System.Id],
    [System.Title],
    [System.IterationLevel2],
    [Microsoft.VSTS.Common.ClosedDate]
FROM workitemLinks
WHERE
    (
        [Source].[System.TeamProject] = @project

JosiahSiegel

SSH Key Authentication

Generate key pair

ssh-keygen -t ed25519 -b 4096

Restrict private key access

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