View fn_get_audit_file.sql
SELECT * | |
FROM sys.fn_get_audit_file('https://StorageAccountName.blob.core.windows.net/sqldbauditlogs/ServerName/SqlDataWarehouseName/', default, default) |
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" | |
} |
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" | |
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 |
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 = @{ |
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";} |
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%'), |
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 |
NewerOlder