Skip to content

Instantly share code, notes, and snippets.

Avatar

J Rokicki DataSic

View GitHub Profile
View fn_get_audit_file.sql
SELECT *
FROM sys.fn_get_audit_file('https://StorageAccountName.blob.core.windows.net/sqldbauditlogs/ServerName/SqlDataWarehouseName/', default, default)
@DataSic
DataSic / ModifySqlDatabaseAuditing.ps1
Created Jun 3, 2018
Modify Set-AzureRmSqlDatabaseAuditing
View ModifySqlDatabaseAuditing.ps1
$AuditActionGroup = @("SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
"FAILED_DATABASE_AUTHENTICATION_GROUP",
"BATCH_COMPLETED_GROUP",
"BACKUP_RESTORE_GROUP",
"DATABASE_OPERATION_GROUP",
"USER_CHANGE_PASSWORD_GROUP")
$setAzureRmSqlDatabaseAuditingSplat = @{
ResourceGroupName = "ResourceGroupName"
ServerName = "ServerName"
View dm_audit_actions.sql
SELECT *
FROM sys.dm_audit_actions
WHERE containing_group_name IN ('SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP',
'FAILED_DATABASE_AUTHENTICATION_GROUP',
'BATCH_COMPLETED_GROUP')
View Set-AzureRmSqlDatabaseAuditing.ps1
<#
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabaseauditing
#>
$setAzureRmSqlDatabaseAuditingSplat = @{
ResourceGroupName = "ResourceGroupName"
ServerName = "ServerName"
DatabaseName = "DatabaseName"
StorageAccountName = "StorageAccountName"
State = "Enabled"
}
@DataSic
DataSic / AzureAutomation.JITaccess.ps1
Created May 29, 2018
Azure Automation Runbook - JIT 'Allow access to Azure services'
View AzureAutomation.JITaccess.ps1
<#
JIT 'Allow access to Azure services' for runbook on Azure Sql Database/Datawarehouse
#>
try
{
# Init variables
$resourceGroupName = Get-AutomationVariable -Name "ResourceGroup"
$serverName = Get-AutomationVariable -Name "ServerName"
$firewallRuleName = "AllowAllWindowsAzureIps"
@DataSic
DataSic / AzureSqlPass.Firewall.sql
Created May 29, 2018
AzureSqlPass - Add Firewall Rule tSQL
View AzureSqlPass.Firewall.sql
/*
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database
*/
--[DatabaseName] context
EXEC sp_set_database_firewall_rule @name = N'NewDatabaseFirewallRule',
@start_ip_address = 'IpAddressStart',
@end_ip_address = 'IpAddressEnd'
/*
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database
@DataSic
DataSic / AzureSqlPasS.Firewall.ps1
Last active May 31, 2018
AzureSqlPasS - Add Firewall Rule
View AzureSqlPasS.Firewall.ps1
<#
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/new-azurermsqlserverfirewallrule
#>
# Init variables
$resourceGroupName = "ResourceGroupName"
$serverName = "ServerName"
$firewallRuleName = "ServerFirewallRuleName"
# Add firewall rules
$newAzureRmSqlServerFirewallRuleSplat = @{
@DataSic
DataSic / CosmosDB.Firewall.ps1
Created May 29, 2018
CosmosDB - Add Firewall Rule
View CosmosDB.Firewall.ps1
<#
https://docs.microsoft.com/en-us/azure/templates/microsoft.documentdb/databaseaccounts
#>
# Init variables
$resourceGroupName = "ResourceGroupName"
$resourceType = "Microsoft.DocumentDB/databaseAccounts"
$apiVersion = "2015-04-08"
$cosmosDbAccountName = "CosmosDbAccountName"
$propertyObject = @{"databaseAccountOfferType"="Standard";
"ipRangeFilter"="IpAddress1,IpAddress2";}
@DataSic
DataSic / QS Wait Stats Categories
Last active Dec 19, 2017
Query Store Wait Stats Categories
View QS Wait Stats Categories
SELECT *
FROM (VALUES (0,'Unknown','Unknown'),
(1,'CPU','SOS_SCHEDULER_YIELD'),
(2,'Worker Thread','THREADPOOL'),
(3,'Lock','LCK_M_%'),
(4,'Latch','LATCH_%'),
(5,'Buffer Latch','PAGELATCH_%'),
(6,'Buffer IO','PAGEIOLATCH_%'),
(7,'Compilation*','RESOURCE_SEMAPHORE_QUERY_COMPILE'),
(8,'SQL CLR','CLR%, SQLCLR%'),
@DataSic
DataSic / QS Wait Stats aggregation
Last active Dec 18, 2017
Query Store Wait Stats aggregation
View QS Wait Stats aggregation
SELECT w.plan_id,
w.runtime_stats_interval_id,
w.execution_type,
w.wait_category,
w.wait_category_desc,
SUM(w.total_query_wait_time_ms) total_query_wait_time_ms,
AVG(w.avg_query_wait_time_ms) avg_query_wait_time_ms,
MIN(w.min_query_wait_time_ms) min_query_wait_time_ms,
MAX(w.max_query_wait_time_ms) max_query_wait_time_ms
FROM sys.query_store_wait_stats w
You can’t perform that action at this time.