Monday, August 8, 2016

Add Databases to AlwaysOn Availability Group Error - The operating system returned the error '5(Access is denied.)

This is one of common error message While Creating a New Availability Group or Adding a Database to an availability group. Here is full error I received when I attempted to Add a database to an Availability Group

--------------------------------------------------------------------------------------------------------------------------------------------------------

Restore Database AdventureWorks fail (Microsoft.SqlServer.Management.HadrModel)


ADDITIONAL INFORMATION:

Restore failed for Server 'CGSQL02\SQLAG02'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' 
while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLAG01\MSSQL\DATA\AdventureWorks.mdf'. (Microsoft.SqlServer.Smo)
--------------------------------------------------------------------------------------------------------------------------------------------------------





























Solution

Here is my environment

OS - Windows Server 2012 R2
SQL Server - SQL 2016 Enterprise Edition ( The same solution will solve for SQL 2014/2012 too)
Primary Server - SQLAG01 
Secondary Server - SQLAG02

SQL Installation was done on Default Paths  
Data Path on SQLAG01 -  "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"
Data Path on SQLAG02- "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG02\MSSQL\DATA\"

SQL Service is running under  domain acccount ( condoso/sql_service )

it is a problem with the Permission of the specified folder to the user account that SQL Server service is running. The Path Which you have created on Secondary instance to perform synchronization of secondary server databases using backup/restore. in my scenario ( "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\"  Which is the Path Created for Back/restore on Secondary node ) , This path should have Full Permission on SQL Service Account. condoso\sql_service










3 comments:

  1. Suddenly in this article, the configuration of SQL is performed, but the actual installation of the SQLserver software is missing. I would like to know how to install SQLserver software; as new SQL server failover cluster? Or stand alone?

    ReplyDelete
    Replies
    1. https://www.mssqltips.com/sqlservertip/4200/step-by-step-installation-of-sql-server-2014-on-a-new-failover-cluster/

      Delete
  2. Microsoft SQL Server 2019 Standard provides provides additional capability and improvements database features. like SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.

    ReplyDelete