A couple of days ago, I ran into an issue when adding a new database in direct seeding mode that led me to reconsider refreshing my skills on this feature. Going through the AG database wizard for adding database, I faced the following error message …
… and I was surprised by the required directories value (L:\SQL\Data) because the correct topology should be:
– D:\SQL\Data (SQL data files)
– L:\SQL\Logs (SQL Log files)
SQL Server 2016 required to have symmetric storage layout for both AG replicas but SQL Server 2017 and above seems to tell another story as specified to the BOL. In my context, I got the check script executed by the wizard and it became obvious that the direct seeding feature is checking if folders based on default path values exist on each replica.
In my context, I got the check script executed by the wizard and it became obvious that the direct seeding feature is checking if folders based on default path values exist on each replica.
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @SmoAuditLevel OUTPUT
declare @NumErrorLogs int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
declare @SmoLoginMode int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @SmoLoginMode OUTPUT
declare @ErrorLogSizeKb int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', @ErrorLogSizeKb OUTPUT
declare @SmoMailProfile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName', @SmoMailProfile OUTPUT
declare @BackupDirectory nvarchar(512)
if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
select @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512))
else
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT
declare @SmoPerfMonMode int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'Performance', @SmoPerfMonMode OUTPUT
if @SmoPerfMonMode is null
begin
set @SmoPerfMonMode = 1000
end
SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(SERVERPROPERTY('instancedefaultdatapath'),'') AS [DefaultFile],
SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog],
ISNULL(@ErrorLogSizeKb, 0) AS [ErrorLogSizeKb],
-1 AS [TapeLoadWaitTime],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode]
Primary replica
Secondary replica
Even if directing seeding allows asymmetric storage layout, a mistake was introduced in my context and both replicas should have been aligned. It is therefore all the more important that using direct seeding capabilities from PowerShell cmdlets like Add-DbaAgDatabase doesn’t generate any errors and fixing the default path value for data and log require restarting the SQL Server instance. See you!
Hope this tips helps!
See you!