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) |
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" |
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') |
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" | |
} |
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" | |
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 |
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 = @{ |
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";} |
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%'), |
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