EitanBlumin

<#
.SYNOPSIS
Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup

Author: Eitan Blumin
Date: 2021-01-20

.DESCRIPTION
This script is adapted from the scripts provided in the following resources:

EitanBlumin

############## Setup ##############

$AGName = ''
$AGPrimary = ''
$AGSecondary = ''

# This allows you to process just a subset of databases using the name (wildcards are possible)
$DBNamePattern = 'AdventureW*'

$TargetFolder = 'K:\Data\'

EitanBlumin

USE SentryOne
GO
DECLARE
 @SiteName		NVARCHAR(1000)	= NULL
,@SqlServerName		NVARCHAR(1000)	= NULL
,@End_date		DATETIME	= NULL
,@Start_date		DATETIME	= NULL
,@CounterID		SMALLINT	= 1858 -- CPU %
,@DefaultDaysBack	INT		= 90

EitanBlumin

/*
Retrieve Foreign Key Hierarchy Tree
===================================
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Date: 2021-01-07
Description:
Retrieve the hierarchy tree for a given table,
based on foreign key references.
Use this script to map out your entity relational structure,
see which foreign keys are dependent on a given table,

EitanBlumin

DECLARE
	@DatabaseName		SYSNAME		= 'MyDB',
	@TableName		SYSNAME		= 'MyTable',
	@DateTimeColumnName	SYSNAME		= 'MyColumn',
	@ThresholdDateTime	DATETIME	= DATEADD(DAY, -14, GETDATE()),
	@BatchSize		INT		= 10000,
	@SleepBetweenBatches	VARCHAR(17)	= '00:00:00.6'

SET NOCOUNT ON;

EitanBlumin

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
/*
Based on sample by Matteo Lorini:
https://www.mssqltips.com/sqlservertip/6622/stored-procedure-in-sql-server-with-r-code/

The readxl package needs to be installed first:

EitanBlumin

/*
Read contents of a SQLDump file's txt file
=============================================
Author: Eitan Blumin | https://www.madeiradata.com
Date: 2020-12-08
*/
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
DECLARE @FilePath NVARCHAR(4000), @CMD NVARCHAR(MAX)

-- Use below to read the contents of latest memory dump file (mdmp) generated by the current instance:

EitanBlumin

# when creating a scheduled task to run such scripts, use the following structure example:
# powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Madeira\Powershell_Template_with_Transcript.ps1"
Param
(
[string]$logFileFolderPath = "C:\Madeira\log",
[string]$logFilePrefix = "my_ps_script_",
[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
[int]$logFileRetentionDays = 30
)
Process {

EitanBlumin

SET NOCOUNT ON;
DECLARE @MinimumRowsInTable INT = 200000;
IF OBJECT_ID('tempdb..#FindOnThisDB') IS NOT NULL DROP TABLE #FindOnThisDB;
;WITH Indexes AS
(
select
sets.schema_id,
sets.table_o_id,
sets.key_column_list,
sets.include_column_list,

EitanBlumin

/*
Invalid owner for a system Role, Schema, or Database
====================================================
Author: Eitan Blumin | Madeira Data Solutions | https://www.madeiradata.com
Date: 2020-11-25
Description:

System roles and schemas must have specific owning users or roles.
 
For example, all system database roles such as db_owner, db_datawriter, db_datareader, etc. must be owned by dbo.