How does GitHub Copilot in SSMS 22 handle database context collection before generating a response?
Hello, I am trying to better understand the internal workflow of GitHub Copilot in SSMS 22, especially for database-specific questions. From the product descriptions, it seems that Copilot can use the context of the currently connected database, such as schema, tables, columns, and possibly other metadata, when answering questions or generating T-SQL. However, I could not find clear official documentation about the actual sequence of operations. My main questions are: Before generating a response, does Copilot first collect database context/metadata from the active connection and then send that context to the LLM as grounding information? Or does it first use the LLM to interpret the user’s request, decide what information is needed, and then retrieve database metadata before generating the final answer? In some explanations, I have seen the phrase "Core SQL Copilot Infrastructure", but I cannot find any official documentation for that term. Is this an official component name? If so, what does it specifically refer to in the SSMS Copilot architecture? When Copilot answers schema-related or data-related questions, what information is retrieved automatically from the connected database, and is any SQL executed as part of that process? Is there any official architectural documentation that explains: context collection, prompt grounding, LLM invocation order, and whether query execution can occur before the final response is generated? I am asking because I want to understand the feature from both an architecture and data governance/security perspective. Any clarification from the product team or documentation links would be greatly appreciated. Thank you.Expanding Azure Arc SQL Migration with a New Target: SQL Server on Azure Virtual Machines
Modernizing a SQL Server estate is rarely a single-step effort. It typically involves multiple phases, from discovery and assessment to migration and optimization, often spanning on-premises, hybrid, and cloud environments. SQL Server enabled by Azure Arc simplifies this process by bringing all migration steps into a single, cohesive experience in the Azure portal. With the March 2026 release, this integrated experience is extended by adding SQL Server on Azure Virtual Machines as a new migration target in Azure Arc. Arc-enabled SQL Server instances can now be migrated not only to Azure SQL Managed Instance, but also to SQL Server running on Azure infrastructure, using the same unified workflow. Expanding Choice Without Adding Complexity By introducing SQL Server on Azure Virtual Machines as a migration target, Azure Arc now supports a broader range of migration strategies while preserving a single operational model. It becomes possible to choose between Azure SQL Managed Instance and SQL Server on Azure VMs without fragmenting migration tooling or processes. The result is a flexible, scalable, and consistent migration experience that supports hybrid environments, reduces operational overhead, and enables modernization at a controlled and predictable pace. One Integrated Migration Journey A core value of SQL Server migration in Azure Arc is that the entire migration lifecycle is managed from one place. Once a SQL Server instance is enabled by Azure Arc, readiness can be assessed, a migration target selected, a migration method chosen, progress monitored, and cutover completed directly in the Azure portal. This approach removes the need for disconnected tools or custom orchestration. The only prerequisite remains unchanged: the source SQL Server needs to be enabled by Azure Arc. From there, migration is fully integrated into the Azure Arc SQL experience. A Consistent Experience Across Migration Targets The migration experience for SQL Server on Azure Virtual Machines follows the same model already available for Azure SQL Managed Instance migrations in Azure Arc. The same guided workflow, migration dashboard, and monitoring capabilities are used regardless of the selected target. This consistency is intentional. It allows teams to choose the destination that best fits their technical, operational, or regulatory requirements without having to learn a new migration process. Whether migrating to a fully managed PaaS service or to SQL Server on Azure infrastructure, the experience remains predictable and familiar. Backup Log Shipping Migration to SQL Server in Azure VM Migration to SQL Server on Azure Virtual Machines is based on backup and restore, specifically using log shipping mechanism. This is a well-established approach for online migrations that minimizes downtime while maintaining control over the cutover window. In this model, database backups need to be uploaded from the source SQL Server to Azure Blob Storage. The migration engine will restore the initial full backup followed by ongoing transaction log and diff. backups. Azure Blob Storage acts as the intermediary staging location between the source and the target. The Azure Blob Storage account and the target SQL Server running on an Azure Virtual Machine must be co-located in the same Azure region. This regional alignment is required to ensure efficient data transfer, reliable restore operations, and predictable migration performance. Within the Azure Arc migration experience, a simple and guided UX is used to select the Azure Blob Storage container that holds the backup files. Both the selected storage account and the Azure VM hosting SQL Server must reside in the same Azure region. Once the migration job is started, Azure Arc automatically restores the backup files to SQL Server on the Azure VM. As new log backups are uploaded to Blob Storage, they are continuously detected and applied to the target database, keeping it closely synchronized with the source. Controlled Cutover on Your Terms This automated restore process continues until the final cutover is initiated. When the cutover command is issued, Azure Arc applies the final backup to the target SQL Server on the Azure Virtual Machine and completes the migration. The target database is then brought online, and applications can be redirected to the new environment. This controlled cutover model allows downtime to be planned precisely, rather than being dictated by long-running restore operations. Getting started To get started, Arc enable you SQL Server. Then, in the Azure portal, navigate to your Arc enabled SQL Server and select Database migration under the Migration menu on the left. For more information, see the SQL Server migration in Azure Arc documentation.Unable to install SQL Server 2022 Express (installer glitch + SSMS error)
Hi, I recently purchased a new Lenovo laptop, and I am trying to install Microsoft SQL Server 2022 Express along with SSMS. SSMS installed successfully, but SQL Server installation fails, and sometimes the installer UI glitches or does not load properly. Because of this, I am getting connection errors in SSMS like "server not found" and "error 40". I am not very familiar with technical troubleshooting. Can someone guide me step-by-step in a simple way to install SQL Server correctly? Thank you.Zero Trust for data: Make Microsoft Entra authentication for SQL your policy baseline
A policy-driven path from enabled to enforced. Why this matters now Security and compliance programs were once built on an assumption that internal networks were inherently safer. Cloud adoption, remote work, and supply-chain compromise have steadily invalidated that model. U.S. federal guidance has now formalized this shift: Executive Order 14028 calls for modernizing cybersecurity and accelerating Zero Trust adoption, and OMB Memorandum M-22-09 sets a federal Zero Trust strategy with specific objectives and timelines. Meanwhile, attacker economics are changing. Automation and AI make reconnaissance, phishing, and credential abuse cheaper and faster. That concentrates risk on identity—the control plane that sits in front of systems, applications, and data. In Zero Trust, the question is no longer “is the network trusted,” but “is this request verified, governed by policy, and least-privilege?” Why database authentication is a first‑order Zero Trust control Databases are universally treated as crown-jewel infrastructure. Yet many data estates still rely on legacy patterns: password-based SQL authentication, long-lived secrets embedded in apps, and shared administrative accounts that persist because migration feels risky. This is exactly the kind of implicit trust Zero Trust architectures aim to remove. NIST SP 800-207 defines Zero Trust as eliminating implicit trust based solely on network location or ownership and focusing controls on protecting resources. In that model, every new database connection is not “plumbing”—it is an access decision to sensitive data. If the authentication mechanism sits outside the enterprise identity plane, governance becomes fragmented and policy enforcement becomes inconsistent. What changes when SQL uses Microsoft Entra authentication Microsoft Entra authentication enables users and applications to connect to SQL using enterprise identities, instead of usernames and passwords. Across Azure SQL and SQL Server enabled by Azure Arc, Entra-based authentication helps align database access with the same identity controls organizations use elsewhere. The security and compliance outcomes that leaders care about Reduce password and secret risk: move away from static passwords and embedded credentials. Centralize governance: bring database access under the same identity policies, access reviews, and lifecycle controls used across the enterprise. Improve auditability: tie access to enterprise identities and create a consistent control surface for reporting. Enable policy enforcement at scale: move from “configured” controls to “enforced” controls through governance and tooling. This is why Entra authentication is a high-ROI modernization step: it collapses multiple security and operational objectives into one effort (identity modernization) rather than a set of ongoing compensating programs (password rotation programs, bespoke exceptions, and perpetual secret hygiene projects). Why AI makes this a high priority decision AI accelerates both reconnaissance and credential abuse, which concentrates risk on identity. As a result, policy makers increasingly treat phishing-resistant authentication and centralized identity enforcement as foundational—not optional. A practical path: from enabled to enforced Successful security programs define a clear end state, a measurable glide path, and an enforcement model. A pragmatic approach to modernizing SQL access typically includes: Discover active usage: Identify which logins and users are actively connecting and which are no longer required. Establish Entra as the identity authority: Enable Entra authentication on SQL logical servers, starting in mixed mode to reduce disruption. Recreate principals using Entra identities: Replace SQL Authentication logins/users with Entra users, groups, service principals, and managed identities. Modernize application connectivity: Update drivers and connection patterns to use Entra-based authentication and managed identities. Validate, then enforce: Confirm the absence of password‑based SQL authentication traffic, then move to Entra‑only where available and enforce via policy. By adopting this sequencing, organizations can mitigate risks at an early stage and postpone enforcement until the validation process concludes. For a comprehensive migration strategy, refer to Securing Azure SQL Database with Microsoft Entra Password-less Authentication: Migration Guide. Choosing which projects to fund — and which ones to stop When making investment decisions, priority is given to database identity projects that can demonstrate clear risk reduction and lasting security benefits. Microsoft Entra authentication as the default for new SQL workloads, with a defined migration path for the existing workloads. Managed identities for application-to-database connectivity to eliminate stored secrets. Centralized governance for privileged database access using enterprise identity controls. At the same time, organizations should explicitly de-prioritize investments that perpetuate password risk: password rotation projects that preserve SQL Authentication, bespoke scripts maintaining shared logins, and exception processes that do not scale. Security and scale are not competing goals Security is often seen as something that slows down innovation, but database identity offers unique benefits. When enterprise identity is used for access controls, bringing in new applications and users shifts from handing out credentials to overseeing policies. Compliance reporting also becomes uniform rather than customized, making it easier to grow consistently thanks to a single control framework. Modern database authentication is not solely about mitigating risk— it establishes a scalable operational framework for secure data access. A scorecard designed for leadership readiness To elevate the conversation from implementation to governance, use outcome-based metrics: Coverage: Percentage of SQL workloads with Entra authentication enabled. Enforcement: Percentage operating in Entra-only mode after validation. Secret reduction: Applications still relying on stored database passwords. Privilege hygiene: Admin access governed through enterprise identity controls. Audit evidence: Ability to produce identity-backed access reports on demand. These map directly to Zero Trust maturity expectations and provide a defensible definition of “done.” Closing Zero Trust is an operating posture, not a single control. For most organizations, the fastest way to make that posture measurable is to standardize database access on the same identity plane used everywhere else. If you are looking for a single investment that improves security, reduces audit friction, and supports responsible AI adoption, modernizing SQL access with Microsoft Entra authentication — and driving it from enabled to enforced — is one of the most durable choices you can make. References US Government sets forth Zero Trust architecture strategy and requirements (Microsoft Security Blog) Securing Azure SQL Database with Microsoft Entra Password-less Authentication: Migration Guide (Microsoft Tech Community) OMB Memorandum M-22-09: Federal Zero Trust Strategy (White House) NIST SP 800-207: Zero Trust Architecture CISA: Zero Trust Enforce Microsoft Entra-only authentication for Azure SQL Database and Azure SQL Managed Instance
Why Developers and DBAs love SQL’s Dynamic Data Masking (Series-Part 1)
Dynamic Data Masking (DDM) is one of those SQL features (available in SQL Server, Azure SQL DB, Azure SQL MI, SQL Database in Microsoft Fabric) that both developers and DBAs can rally behind. Why? Because it delivers a simple, built-in way to protect sensitive data—like phone numbers, emails, or IDs—without rewriting application logic or duplicating security rules across layers. With just a single line of T-SQL, you can configure masking directly at the column level, ensuring that non-privileged users see only obfuscated values while privileged users retain full access. This not only streamlines development but also supports compliance with data privacy regulations like GDPR and HIPAA, etc. by minimizing exposure to personally identifiable information (PII). In this first post of our DDM series, we’ll walk through a real-world scenario using the default masking function to show how easy it is to implement and how much development effort it can save. Scenario: Hiding customer phone numbers from support queries Imagine you have a support application where agents can look up customer profiles. They need to know if a phone number exists for the customer but shouldn’t see the actual digits for privacy. In a traditional approach, a developer might implement custom logic in the app (or a SQL view) to replace phone numbers with placeholders like “XXXX” for non-privileged users. This adds complexity and duplicate logic across the app. With DDM’s default masking, the database can handle this automatically. By applying a mask to the phone number column, any query by a non-privileged user will return a generic masked value (e.g. “XXXX”) instead of the real number. The support agent gets the information they need (that a number is on file) without revealing the actual phone number, and the developer writes zero masking code in the app. This not only simplifies the application codebase but also ensures consistent data protection across all query access paths. As Microsoft’s documentation puts it, DDM lets you control how much sensitive data to reveal “with minimal effect on the application layer” – exactly what our scenario achieves. Using the ‘Default’ Mask in T-SQL : The ‘Default’ masking function is the simplest mask: it fully replaces the actual value with a fixed default based on data type. For text data, that default is XXXX. Let’s apply this to our phone number example. The following T-SQL snippet works in Azure SQL Database, Azure SQL MI and SQL Server: SQL -- Step 1: Create the table with a default mask on the Phone column CREATE TABLE SupportCustomers ( CustomerID INT PRIMARY KEY, Name NVARCHAR(100), Phone NVARCHAR(15) MASKED WITH (FUNCTION = 'default()') -- Apply default masking ); GO -- Step 2: Insert sample data INSERT INTO SupportCustomers (CustomerID, Name, Phone) VALUES (1, 'Alice Johnson', '222-555-1234'); GO -- Step 3: Create a non-privileged user (no login for simplicity) CREATE USER SupportAgent WITHOUT LOGIN; GO -- Step 4: Grant SELECT permission on the table to the user GRANT SELECT ON SupportCustomers TO SupportAgent; GO -- Step 5: Execute a SELECT as the non-privileged user EXECUTE AS USER = 'SupportAgent'; SELECT Name, Phone FROM SupportCustomers WHERE CustomerID = 1 Alternatively, you can use Azure Portal to configure masking as shown in the following screenshot: Expected result: The query above would return Alice’s name and a masked phone number. Instead of seeing 222-555-1234, the Phone column would show XXXX. Alice’s actual number remains safely stored in the database, but it’s dynamically obscured for the support agent’s query. Meanwhile, privileged users such as administrator or db_owner which has CONTROL permission on the database or user with proper UNMASK permission would see the real phone number when running the same query. How this helps Developers : By pushing the masking logic down to the database, developers and DBAs avoid writing repetitive masking code in every app or report that touches this data. In our scenario, without DDM you might implement a check in the application like: If user_role == “Support”, then show “XXXX” for phone number, else show full phone. With DDM, such conditional code isn’t needed – the database takes care of it. This means: Less application code to write and maintain for masking Consistent masking everywhere (whether data is accessed via app, report, or ad-hoc query). Quick changes to masking rules in one place if requirements change, without hunting through application code. From a security standpoint, DDM reduces the risk of accidental data exposure and helps in compliance scenarios where personal data must be protected in lower environments or by certain roles, while reducing the developer effort drastically. In the next posts of this series, we’ll explore other masking functions (like Email, Partial, and Random etc) with different scenarios. By the end, you’ll see how each built-in mask can be applied to make data security and compliance more developer-friendly! Reference Links : Dynamic Data Masking - SQL Server | Microsoft Learn Dynamic Data Masking - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn
Managed Identity on SQL Server On-Prem: The End of Stored Secrets
The Problem with Credentials in SQL Server For an On-Premises SQL Server to access Azure services, you traditionally need to store secrets: Common Scenarios Requiring Credentials Scenario Required Credential Backup to URL (Azure Blob) Storage account key or SAS token Extensible Key Management (Azure Key Vault) Service principal + secret Calling Azure OpenAI from T-SQL API key PolyBase to Azure Data Lake Service principal or key Associated Risks Manual Rotation Secrets expire. You need to plan and execute rotation and not forget to update all references. Secure Storage Where to store these secrets? In SQL Server via CREATE CREDENTIAL? In a config file? Each option has its risks. Attack Surface A compromised secret gives access to associated Azure resources. The more secrets you have, the larger the attack surface. Complex Auditing Who has access to these secrets? When were they used? Tracking is difficult. The Solution: Azure Arc + Managed Identity SQL Server 2025 connected to Azure Arc can geta Managed Identity : This identity: Is managed by Microsoft Entra ID Has no secret to store or rotate Can receive RBAC permissions on Azure resources Is centrally audited in Entra ID How It Works SQL Server 2025 On-Prem Azure Arc Agent installed on the server Managed Identity (automatically created in Entra ID) RBAC assignment on Azure resources -free access to Blob Storage, Key Vault, etc Step-by-Step Configuration Step 1: Enable Azure Arc on the Server and/or Register SQL Server in Azure Arc Follow the procedure describes in this article to onboard your server in Azure Arc. Connect Your SQL Server to Azure Arc Remember that you can also evaluate Azure Arc on a Azure VM (test use only) How to evaluate Azure Arc-enabled servers with an Azure virtual machine Step 2: Retrieve the Managed Identity The Managed Identity can be enabled and retrieved from Azure Arc | SQL Servers > “SQL Server instance” > Settings > Microsoft Entra ID Note: The Managed Identity is server-wide (not at the instance level) Step 3: Assign RBAC Roles Granting access to a Storage Account for backups $sqlServerId = (az resource show --resource-group "MyRG" --name "ServerName" --resource-type "Microsoft.HybridCompute/machines" --query identity.principalId -o tsv) az role assignment create --role "Storage Blob Data Contributor" ` --assignee-object-id $sqlServerId ` --scope "/subscriptions/xxx/resourceGroups/MyRG/providers/Microsoft.Storage/storageAccounts/mybackupaccount" Ex: Backup to URL Without Credential Before (with SAS token) -- Create a credential with a SAS token (expires, must be rotated) CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2022-11-02&ss=b&srt=sco&sp=rwdlacup...' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION After (with Managed Identity --No secret anymore CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'Managed Identity' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION Extensible Key Management with Key Vault EKM Configuration with Managed Identity CREATE CREDENTIAL [MyAKV.vault.azure.net] WITH IDENTITY = 'Managed Identity' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov; How Copilot Can Help Infrastructure Configuration Walk me through setting up Azure Arc for SQL Server 2025 to use Managed Identity for backups to Azure Blob Storage @mssql Generate the PowerShell commands to register my SQL Server with Azure Arc and configure RBAC for Key Vault access Identify Existing Credentials to Migrate List all credentials in my SQL Server that use SHARED ACCESS SIGNATURE or contain secrets, so I can plan migration to Managed Identity Migration Scripts I have backup jobs using SAS token credentials. Generate a migration script to convert them to use Managed Identity Troubleshooting My backup WITH MANAGED_IDENTITY fails with "Authorization failed". What are the steps to diagnose RBAC permission issues? @mssql The Azure Arc agent shows "Disconnected" status. How do I troubleshoot connectivity and re-register the server? Audit and Compliance Generate a report showing all Azure resources my SQL Server's Managed Identity has access to, with their RBAC role assignments Prerequisites and Limitations Prerequisites Azure Arc agent installed and connected SQL Server 2025, running on Windows Azure Extension for SQL Server. Current Limitations Failover cluster instances isn't supported. Disabling not recommended Only system-assigned managed identities are supported FIDO2 method not currently supported Azure public cloud access required Documentation Overview Managed identity overview Set Up Managed Identity and Microsoft Entra Authentication for SQL Server Enabled by Azure Arc Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault
Improving Azure SQL Database reliability with accelerated database recovery in tempdb
We are pleased to announce that in Azure SQL Database, accelerated database recovery is now enabled in the tempdb database to bring instant transaction rollback and aggressive log truncation for transactions in tempdb. The same improvement is coming to SQL Server and Azure SQL Managed Instance.Azure Data Studio Retirement
We’re announcing the upcoming retirement of Azure Data Studio (ADS) on February 6, 2025, as we focus on delivering a modern, streamlined SQL development experience. ADS will remain supported until February 28, 2026, giving developers ample time to transition. This decision aligns with our commitment to simplifying SQL development by consolidating efforts on Visual Studio Code (VS Code) with the MSSQL extension, a powerful and versatile tool designed for modern developers. Why Retire Azure Data Studio? Azure Data Studio has been an essential tool for SQL developers, but evolving developer needs and the rise of more versatile platforms like VS Code have made it the right time to transition. Here’s why: Focus on innovation VS Code, widely adopted across the developer community, provides a robust platform for delivering advanced features like cutting-edge schema management and improved query execution. Streamlined tools Consolidating SQL development on VS Code eliminates duplication, reduces engineering maintenance overhead, and accelerates feature delivery, ensuring developers have access to the latest innovations. Why Transition to Visual Studio Code? VS Code is the #1 developer tool, trusted by millions worldwide. It is a modern, versatile platform that meets the evolving demands of SQL and application developers. By transitioning, you gain access to cutting-edge tools, seamless workflows, and an expansive ecosystem designed to enhance productivity and innovation. We’re committed to meeting developers where they are, providing a modern SQL development experience within VS Code. Here’s how: Modern development environment VS Code is a lightweight, extensible, and community-supported code editor trusted by millions of developers. It provides: Regular updates. An active extension marketplace. A seamless cross-platform experience for Windows, macOS, and Linux. Comprehensive SQL features With the MSSQL extension in VS Code, you can: Execute queries faster with filtering, sorting, and export options for JSON, Excel, and CSV. Manage schemas visually with Table Designer, Object Explorer, and support for keys, indexes, and constraints. Connect to SQL Server, Azure SQL (all offerings), and SQL database in Fabric using an improved Connection Dialog. Streamline development with scripting, object modifications, and a unified SQL experience. Optimize performance with an enhanced Query Results Pane and execution plans. Integrate with DevOps and CI/CD pipelines using SQL Database Projects. Stay tuned for upcoming features—we’re continuously building new experiences based on feedback from the community. Make sure to follow the MSSQL repository on GitHub to stay updated and contribute to the project! Streamlined workflow VS Code supports cloud-native development, real-time collaboration, and thousands of extensions to enhance your workflows. Transitioning to Visual Studio Code: What You Need to Know We understand that transitioning tools can raise concerns, but moving from Azure Data Studio (ADS) to Visual Studio Code (VS Code) with the MSSQL extension is designed to be straightforward and hassle-free. Here’s why you can feel confident about this transition: No Loss of Functionality If you use ADS to connect to Azure SQL databases, SQL Server, or SQL database in Fabric, you’ll find that the MSSQL extension supports these scenarios seamlessly. Your database projects, queries, and scripts created in ADS are fully compatible with VS Code and can be opened without additional migration steps. Familiar features, enhanced experience VS Code provides advanced tools like improved query execution, modern schema management, and CI/CD integration. Additionally, alternative tools and extensions are available to replace ADS capabilities like SQL Server Agent and Schema Compare. Cross-Platform and extensible Like ADS, VS Code runs on Windows, macOS, and Linux, ensuring a consistent experience across operating systems. Its extensibility allows you to adapt it to your workflow with thousands of extensions. If you have further questions or need detailed guidance, visit the ADS Retirement page. The page includes step-by-step instructions, recommended alternatives, and additional resources. Continued Support With the Azure Data Studio retirement, we’re committed to supporting you during this transition: Documentation: Find detailed guides, tutorials, and FAQs on the ADS Retirement page. Community Support: Engage with the active Visual Studio Code community for tips and solutions. You can also explore forums like Stack Overflow. GitHub Issues: If you encounter any issues, submit a request or report bugs on the MSSQL extension’s GitHub repository. Microsoft Support: For critical issues, reach out to Microsoft Support directly through your account. Transitioning to VS Code opens the door to a more modern and versatile SQL development experience. We encourage you to explore the new possibilities and start your journey today! Conclusion Azure Data Studio has served the SQL community well,but the Azure Data Studio retirement marks an opportunity to embrace the modern capabilities of Visual Studio Code. Transitioning now ensures you’re equipped with cutting-edge tools and a future-ready platform to enhance your SQL development experience. For a detailed guide on ADS retirement , visit aka.ms/ads-retirement. To get started with the MSSQL extension, check out the official documentation. We’re excited to see what you build with VS Code!SQL Server has encountered I/O requests taking longer than 15 seconds
Most of us have seen these errors in the SQL Server error log pointing to a IO subsystem problem: 2015/06/15 12:43:01 spid8s SQL Server has encountered 1016 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [N:\ABCDATA8\ [ABCDATA8.ndf] in database [ABC] (5). The OS file handle is 0x000000000000172C. The offset of the latest long I/O is: 0x0000073234 For a customer case this message has to be analyzed in more detail, because the storage group at the customer insists to get more information as they don’t see these high numbers on the storage system at that time. A deeper investigation reveals these findings: the counter is collected and reported for each file separately the message is printed by the checkpoint, the background process that kicks in every 5 seconds it is printed in a minimum interval of 300 seconds (5 minutes) per file, means only one message per 5 minutes per file it checks the amount of ticks (milliseconds) IO requests were active and if this exceeds 15000 (15 seconds) this IO request is counted it is doing this for all parallel and asynchronously IO requests (per file) The problem is that these are not the amount of IO requests that are counted, but the amount of times IO request were seen during the reporting time. Imagine you have a very long IO request going on (only one). The reporting kicks in after 5 seconds (by the checkpoint) and after 3 times (15 seconds) the IO request is the first time counted, but no message is printed yet as the five-minute minimum per file is not reached yet. The very same IO request is counted at each reporting cycle (every 5 seconds) until the 300 seconds threshold is reached and the message is printed (and the counter is reset). Until then this one IO request is counted 57 times (300 seconds / 5 seconds reporting interval = 60 occurrences – 3 time free (first 15 seconds)). So if you have 1000 occurrences means that in this 60 reporting cycles of the 5 minutes reporting interval 1000 IO requests were seen that needed more than 15 seconds. That might were only a handful very long running IO requests (e.g. minimum is (1000 / 57) = 17.5 requests) or many shorter request (e.g. 1000 requests of 15 – 19 seconds). The message is misleading as it is talking of occurrences and not of IO requests, but it still points to a storage problem. Note well, however, when you see even one such message it means you had at least one event where an I/O took more than 15 SECONDs to complete. This is VERY SLOW . This indicates a problematic I/O event. But there are not so many IO requests hanging as stated in the message, but at least there are some. I hope this sheds some light on it.SQL Agent Jobs in an AlwaysOn / Mirroring Environment
Running SQL Agent Jobs in an AlwaysOn or Database Mirroring environment can be a challenge, as the database might be not accessible at the time when the job starts. This is especially the case for all SAP collector and backup jobs. On the other hand there might be jobs that should not run on primary server (e.g. DBCC Checks), but on a readable secondary or readonly standby database (Log Shipping). In the worst case either the jobs fail or run on the wrong server. To prevent this, SAP created a small stored procedure to control the run of these kind of jobs. To use the stored procedure, use this script use master; GO IF (OBJECT_ID(N'dbo.CheckAccess') IS NOT NULL ) DROP PROCEDURE [dbo].CheckAccess GO CREATE PROCEDURE dbo.CheckAccess @DBName AS NVARCHAR(128) = N'master', @RunOnPrimary AS TINYINT = 1, @RunOnSecondary AS TINYINT = 0, @RunOnReadonly AS TINYINT = 0, RC AS TINYINT = 0 OUTPUT AS BEGIN DECLARE @IsReadOnly TINYINT; DECLARE @State TINYINT; DECLARE @StandBy TINYINT; DECLARE @UserAccess TINYINT; DECLARE @AOState TINYINT; DECLARE @AORole TINYINT; DECLARE @Message NVARCHAR(50); SET RC = 0; SET @Message = N'Everything is OK.'; IF EXISTS(SELECT TOP 1 database_id FROM sys.databases WHERE database_id = DB_ID(@DBName)) BEGIN -- Check for the normal database state SELECT @IsReadOnly = is_read_only, @State = state, @StandBy = is_in_standby, @UserAccess = user_access FROM sys.databases WHERE name = @DBName; IF NOT ((@State + @UserAccess) = 0 AND (@IsReadOnly <= @RunOnReadonly) AND (@StandBy <= @RunOnReadonly)) BEGIN SET RC = 1; SET @Message = N'Database is NOT accessible.'; END; END ELSE BEGIN SET RC = 2; SET @Message = N'Database doesn''t exists.'; END -- Check if we are running in an AlwaysOn Configuration IF EXISTS(SELECT TOP 1 database_id FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID(@DBName)) BEGIN SELECT @AOState = db.database_state, @AORole = rs.role FROM sys.dm_hadr_database_replica_states AS db INNER JOIN sys.dm_hadr_availability_replica_states AS rs ON db.group_id = rs.group_id WHERE db.is_local = 1 AND rs.is_local = 1 AND db.database_id = DB_ID(@DBName) IF NOT(@AOState = 0 AND ((@RunOnPrimary != 0 AND @RunOnPrimary = @AORole) OR (@RunOnSecondary != 0 AND @RunOnSecondary = @AORole - 1))) BEGIN SET RC = 3; SET @Message = N'The database is not in the required state for Always-ON access.'; END; END; PRINT @Message END; GO and run it on all nodes of the affected system (either AlwaysOn, Database Mirroring or Log Shipping). It will create a stored procedure CheckAccess in the master database. The procedure has 5 parameters: Parameter Data Type Default Value Description @DBName NVARCHAR(128) N'master' Database to check. @RunOnPrimary TINYINT 1 Should the job run on the primary database ? @RunOnSecondary TINYINT 0 Should the job run on the secondary database ? @RunOnReadonly TINYINT 0 Should the job run on the read only databases (Log Shipping) ? RC TINYINT 0 Return code (0 = Success, 1 = Failure) With the parameters @RunOnPrimary, @RunOnSecondary and @RunOnReadonly you can control in which state of the AlwaysOn database the Job should start. A combination of these parameters is possible, so the job can run on primary and secondary databases if you set both to 1. Even if you do not use AlwaysOn or readonly databases or if you set all the options to 0 the stored procedure will check for the availability of the database. It will return a failure (1) if the database is absent, offline, suspect, in recovery and all the other states that won't let you access the database. A valid call looks like this (Check the TST database if it is accessible and check if it is the primary database in an AlwaysOn configuration): exec dbo.CheckAccess N'TST',1,0,0,@RC OUTPUT To enable the CheckAccess stored procedure for jobs, we have to change the job a bit. In this example it is an SAP Collector Job (SAP_SCU_SAPMonitor_TST_MSSQL_COLLECTOR) for the TST SAP database. Open the properties for the job (right click -> Properties) Switch to the Steps branch on the left and edit the first step with the name DBCOLL. Change the database to master (as the stored procedure lives there) and change the script from EXECUTE sap_dbcoll to DECLARE @Msg NVARCHAR(128) DECLARE RC TINYINT exec dbo.CheckAccess N'TST',1,0,0,@RC OUTPUT IF RC = 0 exec( 'use [TST] EXECUTE sap_dbcoll' ) Ensure that you change the database accordingly (here TST) for your system. The step then should look like: On the advanced tab change the 'On failure action' field to 'Quit the job reporting failure' and save and exit the job definition. This will ensure that the DBCOLL part will only start when the database is accessible and is the primary database in the AlwaysOn Configuration. You can use this stored procedure for any job, when you follow this generic approach: DECLARE @Msg NVARCHAR(128) DECLARE RC TINYINT exec dbo.CheckAccess N'<Database in Question>',1,0,0,@RC OUTPUT IF RC = 0 exec( 'use [<Database in Question>] <Step definition>' ) Regards Clas
