Tag Archives: #DatabaseRecovery

Standard

Posted by

Posted on

October 31, 2023

Posted under

Setup

Comments

Leave a comment

SQL Server 2022 In-Place Upgrade fails with message install the .NET Framework 2.0 or 4.0

Introduction:
Upgrading SQL Server is a crucial task, ensuring your database stays up-to-date and secure. Recently, during an attempt to upgrade from SQL Server 2016 to SQL Server 2022, an unexpected error surfaced, hindering the process.

Error Encountered:
The upgrade process halted with the following error message: “The Microsoft .NET Framework 2.0 or 4.0 must be installed on your computer before you can install Microsoft SQL Server 2022 Setup Support Files. Please install the .NET Framework 2.0 or 4.0 and then install Microsoft SQL Server 2022 Setup Support Files.”

Below error was found on the installation summary,

Feature: Database Engine Services
Status: Failed
Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component name: SQL Server Setup Support Files
Component error code: -2147483648
Component log file: C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20231027_113110\SqlSupport_Cpu64_1.log
Error description: The Microsoft .NET Framework 2.0 or 4.0 must be installed on your computer before you can install Microsoft SQL Server 2022 Setup Support Files. Please install the .NET Framework 2.0 or 4.0 and then install Microsoft SQL Server 2022 Setup Support Files.
Error help link: https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=16.0.1000.6&EvtType=SqlSupport.msi%40CA_ErrorPrereqDotNet20Or40%40-2147483648

-> Below message found on C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20231027_113110\SqlSupport_Cpu64_1.log,

MSI (s) (74:04) [12:15:03:056]: Doing action: CA_ErrorPrereqDotNet20Or40
Action ended 12:15:03: IsDotNet20Or40Installed. Return value 1.
Action start 12:15:03: CA_ErrorPrereqDotNet20Or40.
MSI (s) (74:04) [12:18:22:923]: Product: Microsoft SQL Server 2022 Setup (English) — The Microsoft .NET Framework 2.0 or 4.0 must be installed on your computer before you can install Microsoft SQL Server 2022 Setup Support Files. Please install the .NET Framework 2.0 or 4.0 and then install Microsoft SQL Server 2022 Setup Support Files.

The Microsoft .NET Framework 2.0 or 4.0 must be installed on your computer before you can install Microsoft SQL Server 2022 Setup Support Files. Please install the .NET Framework 2.0 or 4.0 and then install Microsoft SQL Server 2022 Setup Support Files.
Action ended 12:18:22: CA_ErrorPrereqDotNet20Or40. Return value 3.
Action ended 12:18:22: INSTALL. Return value 3.
Property(S): DiskPrompt = [1]
Property(S): InstallMode = Typical
Property(S): UpgradeCode = {DFF9DA09-88B8-4A80-BE2B-78A4DA9835FA}
Property(S): WelcomeBmp = WelcomeBmp
Property(S): OPENREADME = 1
Property(S): TARGETDIR = C:\

Investigation:
Upon inspection, the system seemingly met the .NET requirements, specifically version 4.8. Perplexed by this discrepancy, a closer examination of the server configuration was initiated.

Troubleshooting Steps:

  1. Service Disabling: Two services, namely Avecto Defendpoint Service and Defendpoint BeyondInsight, were identified as potential culprits. They were promptly stopped and disabled, eliminating possible conflicts.
  2. Administrative Run: The upgrade process was restarted with elevated privileges, utilizing “Run as Administrator” mode, ensuring comprehensive access and control.

Resolution:
Executing the upgrade with administrative privileges resolved the issue. The process completed successfully, and SQL Server 2022 was installed seamlessly, guaranteeing the continuity of database operations.

Conclusion:
In the realm of system upgrades, unexpected errors can pose challenges. Swift and strategic troubleshooting, such as disabling conflicting services and employing administrative privileges, can lead to successful resolutions. This experience underscores the importance of meticulous preparation and proactive problem-solving in the face of unforeseen obstacles. 🚀

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.

Standard

Posted by

Posted on

August 28, 2023

Posted under

Azure Managed Instance

Comments

Leave a comment

Cannot connect to Azure SQL Managed Instance

-> I created an Azure SQL managed Instance and tried to connect to the public endpoint as given below,

-> Connection to the Managed Instance failed with below error,

TITLE: Connect to Server

Cannot connect to pg-mi-uks.public.780ac912e702.database.windows.net,3342.

ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-10060-database-engine-error

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

-> Public endpoint provides the ability to connect to Managed Instance from the Internet without using VPN and is for data communication (TDS) only.

-> This option requires port 3342 to be open for inbound traffic. You will need to configure NSG rule for this port separately.

-> Once port 3342 was opened for in-bound traffic. We were able to connect to the managed instance without any issues.

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.

Standard

Posted by

Posted on

May 26, 2023

Posted under

AlwaysON

Comments

Leave a comment

ALWAYS ON – Troubleshooting Availability Group Database Stuck in Initializing / In Recovery Mode

Introduction

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. Restarting SQL Server Instance on Primary Replica if there are no additional synchronous secondary replica: If there are no additional synchronous secondary replica to failover the primary replica, you need to restart the SQL Server Instance on Primary Replica a a workaround for this issue.

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. This problem has been identified as a bug and is documented here.

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.