View fn_get_audit_file.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * | |
FROM sys.fn_get_audit_file('https://StorageAccountName.blob.core.windows.net/sqldbauditlogs/ServerName/SqlDataWarehouseName/', default, default) |
View ModifySqlDatabaseAuditing.ps1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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