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
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
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?
ReplyDeletehttps://www.mssqltips.com/sqlservertip/4200/step-by-step-installation-of-sql-server-2014-on-a-new-failover-cluster/
DeleteMicrosoft 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