In this blog post, we will discuss an issue encountered with an Always On Availability Group where a database remained stuck in the Initializing / In Recovery state on the secondary replica after a failover. We will explore the steps taken to resolve the problem and provide insights into the underlying causes.
Issue
During a failover from the primary to the secondary replica in an Always On Availability Group, one particular database entered the Initializing / In Recovery state on the secondary replica. Despite restarting SQL services and attempting to suspend data movement, the database remained stuck in this state.
Resolution
Logging off users and restarting SQL services: Initially, all users were logged off from the secondary replica, and the SQL services on the replica were restarted. However, these steps did not resolve the issue.
Suspending data movement: The next approach involved suspending the data movement of the problematic database from the SQL Server Management Studio on the secondary replica. However, this operation resulted in a “lock request time out period exceeded” error.
Identifying the blocking process: To investigate further, a query was executed to identify any blocking processes using the following query: SELECT * FROM sysprocesses WHERE blocked > 0 It was discovered that the DB Startup thread for the specific database was blocking the SPID attempting to suspend data movement.
Checking recovery status: The SQL Server error log was examined to determine if the database’s recovery had completed. The log indicated that recovery for the database had finished in 43 seconds, with no user action required. Recovery completed for database JB_DB (database ID 7) in 43 second(s) (analysis 64 ms, redo 324 ms, undo 0 ms.) This is an informational message only. No user action is required.
Analyzing DB Startup thread wait type: Monitoring the DB Startup thread revealed that it was in a wait state with the HADR_RECOVERY_WAIT_FOR_UNDO type. This wait type occurs when the secondary database is waiting for the reverting and initializing phases to synchronize with the primary database after failover.
Monitoring undo progress: The “Log remaining for undo” performance monitor counter was used to track the amount of log in kilobytes remaining for the undo phase. Surprisingly, the counter showed 0, indicating that there was nothing to undo, and the recovery completed message in the SQL Server error log confirmed that the undo took 0 milliseconds.
Failing over to another synchronous secondary replica: To further troubleshoot the issue, a failover was performed from the primary replica to another synchronous secondary replica. This time, all databases returned to a synchronized state without any issues.
Summary
The issue of a database stuck in the Initializing / In Recovery state after an Always On Availability Group failover was resolved by failing over to another synchronous secondary replica. Although the root cause and exact timing of the issue remain unknown, this blog post aims to provide guidance and assistance to others who may encounter similar challenges within the community.
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
SQL Server is a relational database management system (RDBMS) developed by Microsoft Corporation. It has been around for several years and has become one of the most popular database management systems in the world. With each new release, SQL Server has brought with it several new features and enhancements that have made it even more powerful and easier to use.
In this article, we will be taking a closer look at SQL Server 2022 and the new and exciting features it has to offer. We will discuss the performance enhancements, security features, high availability and disaster recovery, query processing and analytical features, data virtualization and integration, developer features, and tools and management improvements. Additionally, we will explore the recent changes to SQL Server licensing and pricing.
Overview
SQL Server is a database management system that stores, organizes, and manages data using a structured query language (SQL). It allows organizations to manage large amounts of data efficiently and effectively. SQL Server has evolved over the years, and each new version has brought with it several new features and enhancements.
SQL Server 2022 is the latest version of the software, and it promises to be the most exciting release yet. It includes several new features and enhancements that will help organizations manage their data more efficiently and securely.
Performance Enhancements
One of the most significant improvements in SQL Server 2022 is its performance enhancements. The following are some of the new features that have been added to SQL Server 2022 to improve performance:
Use of persistent memory to improve speed: SQL Server 2022 uses Intel Optane persistent memory to improve performance. This technology allows data to be stored in memory even after a power outage or system restart, resulting in faster access times and improved performance.
Batch mode over row mode enhancements: Batch mode processing has been improved in SQL Server 2022 to allow for faster processing of large data sets. This feature is particularly useful for data warehousing scenarios where large amounts of data need to be processed quickly.
Improved indexing and query execution: SQL Server 2022 includes several improvements to indexing and query execution, resulting in faster query processing times. These improvements include the ability to create indexes on memory-optimized tables and enhanced query execution plans.
Security Features
SQL Server 2022 includes several new security features designed to help organizations protect their data from unauthorized access. The following are some of the new security features in SQL Server 2022:
Use of trusted enclave technology for secure enclaves: SQL Server 2022 includes support for trusted enclave technology, which provides a secure environment for running sensitive code. This feature is particularly useful for scenarios where sensitive data needs to be processed.
Introduction of always encrypted feature for SQL Server: Always Encrypted is a new feature in SQL Server 2022 that allows organizations to encrypt sensitive data at rest and in transit. This feature ensures that data is always encrypted, even when it is being processed.
Row-Level Security enhancements: SQL Server 2022 includes enhancements to Row-Level Security (RLS), which allows organizations to restrict access to sensitive data at the row level. This feature is particularly useful for scenarios where different users need to access the same data but should only be able to see certain rows.
High Availability and Disaster Recovery
Ensuring high availability and disaster recovery is critical for businesses that rely on databases for their operations. With SQL Server 2022, Microsoft has made several improvements to high availability and disaster recovery features that can help businesses minimize downtime and recover quickly from any outages or disasters.
One of the key improvements in SQL Server 2022 is the enhanced failover clustering feature, which allows administrators to configure more flexible failover policies. The new Always On availability groups feature enables high availability and disaster recovery solutions with better performance and scalability. This feature also allows you to add more secondary replicas to your availability group, which provides higher availability and better workload distribution.
SQL Server 2022 also introduces the resumable online index rebuild feature, which allows you to pause an index rebuild operation and resume it later from where it left off, without starting the operation from the beginning. This feature can be particularly useful for large databases with long-running index operations that may need to be paused and resumed for maintenance or other reasons.
In addition to these features, SQL Server 2022 also includes enhancements to backup and restore capabilities. You can now take advantage of the faster restore speed of Instant File Initialization, which allows for faster restores of large databases. SQL Server 2022 also includes support for backup encryption, which encrypts database backups as they are created, providing an additional layer of protection against data breaches.
With these improvements, SQL Server 2022 offers enhanced high availability and disaster recovery capabilities, making it an ideal choice for businesses that require high levels of uptime and protection against data loss.
Query Processing and Analytical Features
SQL Server has always been a powerful platform for querying and analyzing data, and SQL Server 2022 takes this to the next level with new and enhanced features that enable faster and more efficient query processing and analysis.
One of the most exciting new features in SQL Server 2022 is the enhanced support for graph processing. With this feature, you can easily model and analyze complex relationships between data points, making it ideal for applications that deal with complex data structures, such as social networks, fraud detection, and supply chain optimization.
SQL Server 2022 also introduces new machine learning capabilities, which allow you to build and deploy machine learning models directly within the database. This feature can be used to build predictive models for a wide range of applications, from fraud detection to customer churn analysis.
In addition to these features, SQL Server 2022 includes several enhancements to query processing and performance. The new batch mode over row mode processing enables faster execution of complex queries by processing multiple rows at a time, while improved indexing and query execution algorithms help to optimize query performance and reduce query execution times.
Data Virtualization and Integration
Data virtualization is a powerful technique for integrating and accessing data from multiple sources, without the need to move or replicate the data. With SQL Server 2022, Microsoft has made several improvements to data virtualization capabilities, making it easier and more efficient to integrate and access data from a variety of sources.
One of the key improvements in SQL Server 2022 is the enhancements to PolyBase for data virtualization. PolyBase now supports more data sources, including Oracle and Teradata, making it easier to integrate data from a wide range of sources. PolyBase also includes support for intelligent query processing, which can help to optimize query performance and reduce query execution times.
SQL Server 2022 also includes enhancements to data integration with Azure Synapse Analytics, which provides a comprehensive data integration and analytics platform. The new Azure Synapse Analytics integration features allow you to easily integrate data from a wide range of sources, including on-premises and cloud-based data sources, and perform analytics and reporting on the integrated data.
Finally, SQL Server 2022 introduces a new feature called Data Services for Analytics, which allows users to easily extract insights from big data without needing to move it to a separate analytics platform. With Data Services for Analytics, users can directly query big data stored in Hadoop clusters or Azure Data Lake Storage Gen2. This feature provides the ability to scale out queries using Spark or Databricks clusters and leverage the power of distributed computing to perform complex analytics.
Data Services for Analytics simplifies the process of setting up and managing data connections between SQL Server and big data stores, allowing users to focus on analysis rather than infrastructure. It also supports popular data science languages such as Python and R, allowing data scientists to use their favorite tools to analyze big data.
Developer Features
SQL Server 2022 brings several new features aimed at developers. These features provide enhanced functionality and ease of use for developers, making it easier for them to build applications that take advantage of SQL Server’s capabilities.
One of the key developer features in SQL Server 2022 is the enhancements to JSON support. With the rise of web and mobile applications, JSON has become a popular format for data exchange. SQL Server 2022 introduces several enhancements to make working with JSON data easier and more efficient. These enhancements include support for JSON path queries, computed columns using JSON values, and improved indexing for JSON data.
In addition to JSON enhancements, SQL Server 2022 also includes improvements to In-Memory OLTP, a feature that allows users to store and process data entirely in memory for faster performance. In-Memory OLTP has been enhanced to support larger databases and provides better diagnostics and troubleshooting capabilities.
SQL Server 2022 also introduces enhancements to temporal tables, a feature introduced in SQL Server 2016 that allows users to keep a history of data changes over time. Temporal tables now support schema changes and improved query optimization, making it easier to work with historical data.
Tools and Management
SQL Server 2022 includes several new tools and management features aimed at making it easier to manage and maintain SQL Server deployments.
One of the key enhancements to tools and management is the enhanced SQL Server Management Studio (SSMS), the primary tool for managing SQL Server instances. SSMS now includes new features such as schema compare, data classification, and data discovery and classification.
SQL Server 2022 also introduces new features for Azure Data Studio, a cross-platform database tool that provides a modern, user-friendly interface for working with SQL Server. New features in Azure Data Studio include query plan diagrams, IntelliCode, and a new notebook experience for managing and sharing code snippets.
Finally, SQL Server 2022 includes enhancements to SQL Server Integration Services (SSIS), a platform for building and deploying data integration solutions. These enhancements include support for Data Flow Debugging, Azure Data Lake Storage Gen2 connectivity, and improved usability.
Conclusion
In conclusion, SQL Server 2022 is a significant update to Microsoft’s relational database management system, bringing a range of new features and improvements across all areas of the product. The performance enhancements in SQL Server 2022 are particularly exciting, with the use of persistent memory, batch mode over row mode enhancements, and improved indexing and query execution all contributing to faster and more efficient database processing.
The new security features in SQL Server 2022 also provide enhanced protection for your data, with the use of trusted enclave technology, always encrypted feature, and row-level security enhancements.
In addition, the high availability and disaster recovery features in SQL Server 2022 ensure that your data remains highly available and protected against any disruptions, with improved failover clusters and availability groups, resumable online index rebuild, and enhancements to backup and restore.
SQL Server 2022 also includes a range of new analytical and data virtualization features, making it easier to process large amounts of data and work with data from a range of different sources. Finally, the new developer features and improvements to tools and management make it easier to work with SQL Server 2022 and develop new applications that take advantage of its capabilities.
Overall, SQL Server 2022 is an exciting release that provides a range of new features and improvements for users across all areas of the product. If you’re currently using an earlier version of SQL Server, or you’re considering implementing a new database management system, upgrading to SQL Server 2022 is definitely worth considering. With its improved performance, enhanced security, and new features, SQL Server 2022 is sure to provide significant benefits for organizations of all sizes.
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
SQL Server Accelerated Database Recovery (ADR) is a feature introduced in SQL Server 2019 that enhances the database recovery process. ADR addresses the long-standing challenge in SQL Server of database recovery time increasing with the number of transactions and active connections to the database. ADR helps minimize downtime during database recovery and reduces the risk of data loss.
This article will provide an in-depth look at SQL Server Accelerated Database Recovery, including its background, challenges and opportunities, best practices, future outlook, and conclusion.
Traditionally, when SQL Server experiences a crash or an unexpected shutdown, the database goes through a recovery process that can take a long time to complete. The recovery process involves three phases: analysis, redo, and undo. During the analysis phase, SQL Server scans the transaction log to determine the point at which the database was last consistent. In the redo phase, SQL Server applies all the transactions that were committed after that point. Finally, in the undo phase, SQL Server rolls back any transactions that were not committed.
The time it takes to complete the recovery process depends on the size of the transaction log, the number of transactions, and the number of connections to the database. In some cases, the recovery process can take hours or even days to complete, causing significant downtime for the application and potentially resulting in data loss.
Explanation of database recovery Whenever a SQL Server instance restarts, the databases that were online before the restart must undergo a recovery process. During the recovery process, SQL Server ensures that all committed transactions are written to disk and any uncommitted transactions are rolled back. This ensures that the database is brought back to a consistent state.
Overview of SQL Server Accelerated Database Recovery SQL Server Accelerated Database Recovery is a feature introduced in SQL Server 2019 that improves database recovery times and availability. It accomplishes this by reducing the amount of log data that needs to be replayed during recovery and by allowing recovery from a checkpoint.
Benefits of SQL Server Accelerated Database Recovery The primary benefits of SQL Server Accelerated Database Recovery are faster recovery times and improved availability. This feature significantly reduces the downtime associated with database recovery and allows organizations to restore their systems more quickly in the event of a failure.
Understanding Traditional Database Recovery
Traditional database recovery involves restoring the database from a backup and replaying all the transactions in the log since the last backup.
How traditional database recovery works In traditional database recovery, SQL Server reads the transaction log to identify all transactions that were not committed at the time of the restart. SQL Server then rolls back all uncommitted transactions and applies all committed transactions to the database. This process can take a significant amount of time, depending on the size of the database and the number of transactions that were in progress at the time of the restart.
Limitations of traditional database recovery Traditional database recovery has several limitations. It can take a long time to complete, especially for large databases. In addition, the recovery process can cause a significant amount of I/O activity, which can impact the performance of the server. Finally, if a failure occurs during the recovery process, the entire recovery process must be restarted.
How SQL Server Accelerated Database Recovery Works
Log Sequence Number (LSN) filtering SQL Server Accelerated Database Recovery works by filtering out redundant transaction log records during the recovery process. This is accomplished through the use of Log Sequence Number (LSN) filtering, which is a feature that was introduced in SQL Server 2019.
When a database is in Accelerated Database Recovery mode, SQL Server maintains a version store, which is a collection of active and previous versions of data pages. Each version of a data page is identified by its LSN, which is a unique identifier assigned to each transaction log record.
During the recovery process, SQL Server filters out transaction log records that are already reflected in the version store. This means that only changes that occurred after the most recent checkpoint are replayed during recovery, which can significantly reduce the amount of time required for recovery.
Checkpoint process Another key aspect of SQL Server Accelerated Database Recovery is the checkpoint process. Checkpoints are a mechanism used by SQL Server to write dirty data pages (i.e., data pages that have been modified but not yet written to disk) to disk. This helps to reduce the amount of work required during recovery, as it ensures that there is less dirty data to be written to disk when the recovery process begins.
With Accelerated Database Recovery, the checkpoint process is enhanced to include a special type of checkpoint called a Accelerated Database Recovery checkpoint. These checkpoints are optimized for use with Accelerated Database Recovery, and they help to ensure that the version store is properly maintained and that redundant transaction log records are filtered out during recovery.
Recovery with Accelerated Database Recovery enabled When a database is in Accelerated Database Recovery mode, recovery is performed in a slightly different way than it is with traditional database recovery. Instead of replaying all transaction log records from the beginning of the log, SQL Server uses the version store to filter out redundant records and only replays the necessary changes.
This can result in significantly faster recovery times, particularly for large databases or databases with high transaction rates. In addition, because only necessary changes are replayed, there is minimal impact on workload during the recovery process.
Benefits of SQL Server Accelerated Database Recovery
Faster recovery times One of the primary benefits of SQL Server Accelerated Database Recovery is faster recovery times. By filtering out redundant transaction log records and replaying only necessary changes, SQL Server can significantly reduce the amount of time required to recover a database.
This is particularly beneficial for large databases or databases with high transaction rates, as traditional database recovery can take a significant amount of time in these scenarios. With Accelerated Database Recovery, recovery times can be reduced from hours or even days to minutes.
Improved availability Another benefit of SQL Server Accelerated Database Recovery is improved availability. Because recovery times are significantly reduced, databases can be back up and running more quickly after a failure.
This can help to minimize downtime and ensure that critical business processes are not impacted by database failures. In addition, because only necessary changes are replayed during recovery, there is minimal impact on workload during the recovery process, which further improves availability.
Minimal impact on workload With traditional database recovery, there is a significant impact on workload during the recovery process. This is because all transaction log records must be replayed from the beginning of the log, which can result in significant resource usage.
With SQL Server Accelerated Database Recovery, only necessary changes are replayed during recovery, which significantly reduces the impact on workload. This means that business processes can continue to operate normally during the recovery process, which is particularly important for mission-critical applications.
Implementation of SQL Server Accelerated Database Recovery
Implementing SQL Server Accelerated Database Recovery is a straightforward process, but there are a few requirements to keep in mind.
Compatibility requirements First, your SQL Server instance must be running on at least SQL Server 2019 Enterprise Edition or Azure SQL Database. Additionally, your database must be running in the compatibility level 150 or higher.
Enabling Accelerated Database Recovery To enable Accelerated Database Recovery for a specific database, use the following T-SQL command:
ALTER DATABASE [DatabaseName] SET ACCELERATED_DATABASE_RECOVERY = ON;
Once enabled, Accelerated Database Recovery is applied to all operations performed against the database. This includes all transactional operations, such as inserts, updates, and deletes, as well as DDL operations, such as table creation or index rebuilding.
Monitoring Accelerated Database Recovery SQL Server provides several mechanisms for monitoring the performance of Accelerated Database Recovery.
One useful tool is the sys.dm_tran_persistent_version_store_stats dynamic management view. This view provides detailed statistics on the size and utilization of the version store for a specific database, as well as information about any background cleanup processes that may be running.
Additionally, SQL Server Management Studio provides a graphical view of the version store in the form of a new tab in the database properties window. This tab shows real-time statistics on the version store size and utilization, as well as the total number of versions and the oldest active transaction.
Limitations of SQL Server Accelerated Database Recovery
While Accelerated Database Recovery provides many benefits, there are also a few limitations to keep in mind.
Unsupported database features Not all database features are supported with Accelerated Database Recovery. For example, databases that use memory-optimized tables, table partitioning, or stretch database are not currently supported.
Increased disk space usage Accelerated Database Recovery can result in increased disk space usage due to the version store, which stores multiple versions of each modified page. This increased disk space usage may require additional planning and monitoring for large databases with high transactional volumes.
Potential performance impact In rare cases, Accelerated Database Recovery may cause a performance impact due to increased I/O operations required for log processing. However, this impact is typically minimal and is outweighed by the benefits of faster recovery times and improved availability.
Conclusion
SQL Server Accelerated Database Recovery provides a powerful new feature for improving database recovery times and reducing downtime. By leveraging innovative technology such as Log Sequence Number (LSN) filtering and persistent versioning, Accelerated Database Recovery enables faster and more reliable database recovery with minimal impact on workload.
While there are a few limitations to keep in mind, such as unsupported database features and increased disk space usage, the benefits of Accelerated Database Recovery far outweigh the potential drawbacks. If you’re running SQL Server 2019 Enterprise Edition or Azure SQL Database, consider enabling Accelerated Database Recovery to take advantage of its powerful benefits and improve your database’s availability and performance.
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
