JosiahSiegel

/*
EXECUTE AS USER = 'non-admin-user';

SELECT *
FROM masked_table

REVERT;
*/

DECLARE 

JosiahSiegel

-- Optimized Session Monitor with Query Plans (Works on both Azure SQL DB and SQL Server VM)
-- Using READ UNCOMMITTED for minimal blocking during monitoring
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
;WITH SessionMetrics AS (
    SELECT
        er.session_id AS sid,
        er.blocking_session_id AS blocked_by,
        -- Accurate issue detection based on Microsoft documentation
        STUFF(

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

 DECLARE @object_id INT = OBJECT_ID('dbo.YourTable');
  DECLARE @db_id INT = DB_ID();
  DECLARE @index_id INT;
  DECLARE @index_name NVARCHAR(128);

  DROP TABLE IF EXISTS #IndexStats
  SELECT TOP 0 *
  INTO #IndexStats
  FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'LIMITED');

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