SQL Server First Responder Kit

The kit supports the following environments:

• SQL Server on Windows: All Microsoft-supported versions.
• SQL Server on Linux: Fully supported, except for sp_DatabaseRestore which requires xp_cmdshell (not provided on Linux).
• Amazon RDS SQL Server: Fully supported.
• Azure SQL DB: Partial support. Some procedures work, but others are skipped due to limited access to DMVs or features like SQL Agent and Database Mail. Managed Instance and Amazon RDS are fully supported.

Note for legacy versions: If using unsupported versions like SQL Server 2008, check the Deprecated folder for older script versions.

To ensure proper diagnostic capabilities during a crash or extreme memory pressure, configure the Windows page file according to Microsoft guidelines:

1. Create a 32GB page file.
2. Ensure there is space for at least one 32GB memory dump.

Warning: Do not remove the page file entirely. Windows requires it to write diagnostic information during system crashes.

sp_BlitzPlanCompare is an optional script located in the OptionalScripts folder and is not included in the standard Install-All-Scripts.sql or Install-Azure.sql installers.

To install it:

1. Open OptionalScripts/sp_BlitzPlanCompare.sql in SQL Server Management Studio (SSMS).
2. Switch to the database where you want it installed (typically master to allow calling it from any database via EXEC master.dbo.sp_BlitzPlanCompare).
3. Run the script.

Note: For linked-server mode to work, you must install the procedure on both the local and remote servers.

To enable alerting, you must first configure Database Mail via the wizard. Once configured:

1. Create Operators in the SQL Server Agent.
2. In SQL Server Agent properties, enable Database Mail and select the appropriate profile.
3. Configure a failsafe operator.
4. Restart the SQL Server Agent Service for changes to take effect.
5. Use a script to create alerts for high severity and data corruption.

When deploying custom code and settings for an application, ensure the following components are migrated or reconfigured, typically by scripting them from an existing production server:

• Application logins: Use Microsoft's guidance to transfer logins and passwords between instances to maintain security continuity.
• SQL Agent jobs: Script these from the existing instance and execute them on the new server.
• Custom alerts: Script these from the existing instance and execute them on the new server.
• Orphaned Users: Identify and clean up users that are no longer mapped to a valid login after migration.

To provide the AI with organizational standards (e.g., naming conventions, query standards), you can set a database-level "constitution" using an extended property named CONSTITUTION.md. This text is automatically included in the AI prompts generated by sp_BlitzCache and sp_BlitzIndex.

EXECUTE sp_addextendedproperty
    @name = N'CONSTITUTION.md',
    @value = N'## Object Naming Standards
Views must always be prefixed with vw_.
Tables should never be prefixed with tbl_.

## Query Standards
Queries should prefer CTEs over temp tables.';

To verify network teaming and resilience:

1. Remote desktop into the server.
2. Initiate a large file copy (e.g., a production backup) from one network share to another.
3. While the copy is running, have a network team member change the VLAN for one of the switch ports. This simulates a network path failure without physically disabling the port.
4. Success Criteria: The Remote Desktop connection must remain active and the file copy must continue (pauses are acceptable, but errors are not).

If your databases utilize Common Language Runtime (CLR) components, you must plan for their configuration and smoke testing post-migration.

To enable CLR components in SQL Server, use the appropriate configuration commands. Note that depending on whether the assembly is signed by a certificate or requires UNSAFE or EXTERNAL_ACCESS permissions, you may need to perform additional steps such as:

• Creating certificates.
• Setting the database TRUSTWORTHY property.
• Changing the owner of the database.

Best Practice: Always test CLR assembly functionality on a restored copy of the database prior to performing the actual migration.

For iSCSI storage, avoid relying on a single network connection:

1. Redundancy: Use at least two dedicated network cards connected to two different switches.
2. Multipathing Modes:
   • Active/Active: Provides combined throughput (e.g., 20 Gbps using two 10 Gbps NICs).
   • Active/Passive: Often seen on a per-array basis where different drive letters are mapped to different NICs.
3. Testing:
   • Performance: Start simultaneous drive copies and monitor the Network tab in Task Manager. If a NIC is idle, the multipathing configuration needs tuning.
   • Failover: Start simultaneous file copies and down one storage network port. The file copy must not fail or throw Windows errors.

The First Responder Kit supports two AI modes via the @AI parameter to assist with query tuning and index advice:

• @AI = 2: Generates a pre-built AI prompt containing query text, execution plans, and performance metrics. This prompt is returned in an AI Prompt column, allowing you to manually copy and paste it into tools like ChatGPT, Gemini, or Claude. This mode works on all supported SQL Server and Azure SQL DB versions and requires no configuration.
• @AI = 1: Performs the same actions as @AI = 2 but also makes a direct API call to an AI provider (OpenAI or Google Gemini) from within SQL Server, returning the advice directly in the result set. This mode requires SQL Server 2025 or Azure SQL DB and uses sp_invoke_external_rest_endpoint.