Invoke-DbaDbAzSqlTip

Synopsis

Executes Microsoft’s Azure SQL performance recommendations script against Azure SQL Database instances.

Description

Executes Microsoft’s Azure SQL Tips script against Azure SQL Database instances to identify performance optimization opportunities and design recommendations. This function runs the get-sqldb-tips.sql script developed by the Azure SQL Product Management team, which analyzes your database configuration, query patterns, and resource utilization to provide actionable improvement suggestions.

The script examines database settings, index usage, query performance metrics, and configuration parameters to generate targeted recommendations with confidence percentages. Each tip includes detailed explanations and links to Microsoft documentation for implementation guidance.

By default, the latest version of the tips script is automatically downloaded from the Microsoft GitHub repository at https://github.com/microsoft/azure-sql-tips. You can also specify a local copy using the -LocalFile parameter if you prefer to use a cached or customized version of the script.

Syntax

Invoke-DbaDbAzSqlTip
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-AzureDomain] <String>]
    [[-Tenant] <String>]
    [[-LocalFile] <String>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [-AllUserDatabases]
    [-ReturnAllTips]
    [-Compat100]
    [[-StatementTimeout] <Int32>]
    [-EnableException]
    [-Force]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -Database ImportantDb

Runs the Azure SQL Tips script against the dbatools1.database.windows.net using the specified credentials for the ImportantDb.

Example: 2

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -Database ImportantDb -ReturnAllTips

Runs the Azure SQL Tips script against the dbatools1.database.windows.net using the specified credentials for the ImportantDb and
will return all the tips regardless of database state.

Example: 3

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -Database ImportantDb -LocalFile 'C:\temp\get-sqldb-tips.sql'

Runs the Azure SQL Tips script that is available locally at ‘C:\temp\get-sqldb-tips.sql’ against the dbatools1.database.windows.net using the specified
credentials for the ImportantDb and will return all the tips regardless of database state.

Example: 4

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -ExcludeDatabase TestDb

Runs the Azure SQL Tips script against all the databases on the dbatools1.database.windows.net using the specified credentials except for TestDb.

Example: 5

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -AllUserDatabases

Runs the Azure SQL Tips script against all the databases on the dbatools1.database.windows.net using the specified credentials.

Example: 6

PS C:\> $cred = Get-Credential
PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential $cred -Database ImportantDb

Enter Azure AD username\password into Get-Credential, and then Invoke-DbaDbAzSqlTip will runs the Azure SQL Tips
script against the ImportantDb database on the dbatools1.database.windows.net server using Azure AD.

Example: 7

PS C:\> Invoke-DbaDbAzSqlTip -SqlInstance dbatools1.database.windows.net -SqlCredential (Get-Credential) -Database ImportantDb -Tenant GUID-GUID-GUID

Run the Azure SQL Tips script against the ImportantDb database on the dbatools1.database.windows.net server specifying the Azure Tenant Id.

Required Parameters

-SqlInstance

The target Azure SQL instance or instances.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
SQL Server Authentication and Azure Active Directory are all supported.
For MFA support, please use Connect-DbaInstance.

-AzureDomain

Specifies the Azure SQL domain for connection. Defaults to database.windows.net for standard Azure SQL Database instances.
Use this when connecting to Azure SQL instances in sovereign clouds like Azure Government (.usgovcloudapi.net) or Azure China (.chinacloudapi.cn).

-Tenant

Specifies the Azure AD tenant ID (GUID) for authentication to Azure SQL Database.
Required when using Azure Active Directory authentication with multi-tenant applications or when the default tenant cannot be determined automatically.

-LocalFile

Specifies the path to a local copy of the Azure SQL Tips script files instead of downloading from GitHub.
Use this when you need to run a specific version, work in environments without internet access, or have customized the tips script for your organization.

-Database

Specifies which Azure SQL databases to analyze for performance recommendations.
Use this when you want to target specific databases rather than analyzing all user databases on the instance.

-ExcludeDatabase

Specifies which Azure SQL databases to skip when running performance analysis.
Use this with -AllUserDatabases to exclude specific databases like development or test environments from the tips analysis.

-AllUserDatabases

Analyzes all user databases on the Azure SQL instance for performance recommendations.
Excludes the master database and automatically discovers all other databases, making it ideal for comprehensive performance audits.

-ReturnAllTips

Returns all available performance tips regardless of current database state or configuration.
By default, the script only shows relevant tips based on your database’s current settings and usage patterns.

-Compat100

Uses a specialized version of the tips script designed for databases running compatibility level 100 (SQL Server 2008).
Only use this when analyzing legacy Azure SQL databases that cannot be upgraded to newer compatibility levels.

-StatementTimeout

Sets the query timeout in minutes for the Azure SQL Tips analysis script.
Increase this value when analyzing large databases or instances with heavy workloads that may cause the default timeout to be exceeded.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-Force

Forces a fresh download of the Azure SQL Tips script from GitHub, bypassing any locally cached version.
Use this when you want to ensure you’re running the latest version or when troubleshooting issues with cached files.