paschott

use SSISDB
GO

CREATE PROC dbo.usp_PurgeSSISCatalogLogs
	@RowsToDelete int = 5000
AS
BEGIN --Proc
/*
	Script name: Purge SSIS Catalog log tables
	Author: Tim Mitchell (www.TimMitchell.net)

paschott

#Create new Certificate Request for SQL Server security
# Should be made into a function at some point
# Needs to be able to handle Cluster names/IP addresses

#Set location of the server
$Location = "City"
$State = "State"
$OU = "OU"
$Company = "Organization"

paschott

#requires dbatools

$server = "servername"
$localCertPath = "C:\CertificateRequests"
$remoteCertPath = "C:\CertificateRequest\"

$adminuser = Import-Clixml C:\user.cred #stored credentials to access remote server

# Generate the CSR and download locally
$session = New-PSSession $server -Credential $adminuser

paschott

--Get SQL Agent Job names for SSRS Subscriptions
SELECT Schedule.ScheduleID AS JobName,
       [Catalog].Name AS ReportName,
       Subscriptions.Description AS Recipients,
       [Catalog].Path AS ReportPath,
       StartDate,
       Schedule.LastRunTime
  FROM [ReportServer].dbo.ReportSchedule
 INNER JOIN [ReportServer].dbo.Schedule
    ON ReportSchedule.ScheduleID     = Schedule.ScheduleID

paschott

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID

paschott

Import-Module dbatools
Import-Module sqlserver #used for Invoke-SqlCmd to run scripts w/ the "GO" batch separator

$CurrentDate = (Get-Date).ToString("yyyyMMdd_hhmmss")

$Servername = "localhost"
$databases = 'CSV_Set_Of_DB_Names'


#Get permissions for databases

paschott

import-module dbatools
import-module ImportExcel

$servers="server1"
$date = get-date #format yyyymmdd

foreach ($server in $servers) {
	$ExcelFile = "C:\path\$server_Users_And_Logins_$date.xlsx"

	Get-DbaDbRoleMember -SqlInstance $server | Export-Excel -Path $ExcelFile -WorksheetName UserRoleMembers -AutoSize -AutoFilter -ExcludeProperty @("ItemArray", "RowError", "RowState", "Table", "HasErrors") -BoldTopRow

paschott

DECLARE @CustomerID INT
SELECT @CustomerID = ?

IF NOT EXISTS (SELECT *
	FROM   WideWorldImportsDW.Dimension.Customer
	WHERE  [WWI Customer ID] = @CustomerID)
INSERT WideWorldImportersDW.Dimension.Customer
	([WWI Customer ID],
	Customer,
	[Bill To Customer],

paschott

use master
go
 
-- Adds a first step to specified job, which checks whether running on Primary replica
 
create procedure dbo.AddAGPrimaryCheckStepToAgentJob
    @jobname nvarchar(128)
as
 
set nocount on;

paschott

import-module az.sql

<# TODO
Based on:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-powershell-create?view=azuresql

* Add "Get" commants to top of script as appropriate
* Be able to loop through set of servers
* Document steps and/or separate files
* Parameterize passwords for master & job user accounts