اضافه کردن File به دیتابیس عضو Always On Availability Group

مقدمه

زمانی که یک دیتابیس عضویی از Always On Availability Group است، دستوراتی که بر روی دیتابیس سرور Primary اجرا می‌شوند از طریق Log Records به سرور Secondary منتقل و همان دستورات بر روی نودهای ثانویه هم اجرا می‌شوند.
بنابراین، اگر یک فایل ( Data یا Log) به دیتابیس سرور Primary اضافه کنید در این صورت بر روی سرور Secondary نیز همان دستور اجرا و فایل ساخته می‌شود.
اگر مسیر فایل‌های دیتابیس بر روی سرورهای Primary و Secondary متفاوت باشد هنگام اضافه کردن یک File جدید به دیتابیس به مشکل بر خواهید خورد. (دیتابیس بر روی سرور Secondary به حالت Suspect در خواهد آمد چون مسیر در سرور Secondary وجود ندارد).

در این آموزش قصد داریم نحوه اضافه کردن یک فایل جدید به دیتابیس که مسیر مشابه آن در سرور Secondary وجود ندارد را آموزش دهیم.
برای شبیه سازی این سناریو من از دو سرور با نام‌های NODE1 و NODE2 و یک دیتابیس با نام Shop در مسیر E:\Database استفاده کردم دیتابیس را هم عضو Availability Group ای به نام AG_Shop کردم.ایجاد دیتابیس Shop در مسیر E:\Database
این مسیر در هر دو سرور وجود دارد:

CREATE DATABASE [Shop]
ON PRIMARY
       (
           NAME = N'Shop',
           FILENAME = N'E:\Database\Shop.mdf',
           SIZE = 8192KB,
           FILEGROWTH = 65536KB
       )
LOG ON
    (
        NAME = N'Shop_log',
        FILENAME = N'E:\Database\Shop_log.ldf',
        SIZE = 8192KB,
        FILEGROWTH = 65536KB
    )
GO

برای اینکه بتوانیم دیتابیس را عضو Availability Group کنیم نیاز است که برای اولین بار یک Full Backup تهیه کنیم:

Backup Database Shop to disk = 'NUL'
GO

یک Availability Group با نام AG_Shop ایجاد می‌کنیم و دیتابیس Shop را به آن اضافه می‌کنیم:
برای اجرای دستورت باید حتما حالت SQLCMD MODE را فعال کرده باشید.

Connect NODE1\SQLSERVER2017
USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Shop] MODIFY REPLICA ON N'NODE2\SQLSERVER2017' WITH (SEEDING_MODE = MANUAL)
ALTER AVAILABILITY GROUP [AG_Shop] ADD DATABASE [Shop];
GO
BACKUP DATABASE [Shop] TO  DISK = N'\\NODE1\Share\Shop.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD,
COMPRESSION,  STATS = 5
GO
:Connect NODE2\SQLSERVER2017
RESTORE DATABASE [Shop] FROM  DISK = N'\\NODE1\Share\Shop.bak' WITH  MOVE N'Shop' TO N'E:\Database\Shop.mdf',  MOVE N'Shop_log'
TO N'E:\Database\Shop_log.ldf',  NORECOVERY,
 NOUNLOAD,  STATS = 5
GO
:Connect NODE1\SQLSERVER2017
BACKUP LOG [Shop] TO  DISK = N'\\NODE1\Share\Shop.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect NODE2\SQLSERVER2017
RESTORE LOG [Shop] FROM  DISK = N'\\NODE1\Share\Shop.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
:Connect NODE2\SQLSERVER2017
ALTER DATABASE [Shop] SET HADR AVAILABILITY GROUP = [AG_Shop];
GO

جهت بررسی مسیر فایل‌های دیتابیس بر روی سرور Primary و Secondary می‌توانید دستور زیر را اجرا کنید:

:connect NODE1\SQLSERVER2017
sp_helpdb Shop
GO
:connect NODE2\SQLSERVER2017
sp_helpdb Shop
GO

در تست اول قصد داریم یک دیتا فایل به دیتابیس Shop اضافه کنیم که مسیر مشابه در سرور دوم وجود دارد:
دستور زیر را روی سرور اصلی اجرا می‌کنیم:

USE [master]
GO
ALTER DATABASE Shop ADD FILE
 (NAME = N'Shop_File1',
 FILENAME = N'E:\Database\Shop_File1.ndf' ,
  SIZE = 8192KB ,
  FILEGROWTH = 65536KB
  ) TO FILEGROUP [PRIMARY]
GO

با دستور زیر مجدد مسیر فایل‌ها را چک می‌کنیم:

:connect NODE1\SQLSERVER2017
sp_helpdb Shop
GO
:connect NODE2\SQLSERVER2017
sp_helpdb Shop
GO

خروجی دستور بالا:
همانطور که مشاهده می‌کنید بدون مشکل فایل Shop_File1 به دیتابیس Shop در مسیر E:\Database اضافه می‌شودحالا می‌خواهیم یک دیتا فایل دیگر به دیتابیس اضافه کنیم که این بار مسیر دیتا فایل در سرور Secondary وجود ندارد!
به عنوان مثال مسیر E:\Database\NewFile در سرور Primary وجود دارد اما در سرور Secondary نه.
به محض اجرای دستور زیر، دیتابیس Shop بر روی Node2 به حالت Suspect در می‌آید:

USE [master]
GO
ALTER DATABASE Shop ADD FILE
 (NAME = N'Shop_File2',
 FILENAME = N'E:\Database\NewFile\Shop_File2.ndf' ,
  SIZE = 8192KB ,
  FILEGROWTH = 65536KB
  ) TO FILEGROUP [PRIMARY]
GO

تصویر دیتابیس Shop بر بعد از اجرای دستور ایجاد فایلی که مسیر ایجاد آن در سرورNode2 وجود ندارد:

وضعیت داشبورد

همانطور که مشاهده می‌کنید دیتابیس‌های دیگر سینک نیستند

مشاهده خطا در لاگ SQL

در خطای ثبت شده هم کاملا مشخص است که قادر به ایجاد فایل نبوده است.خب پس تا اینجا متوجه شدیم اگر مسیر فایل‌ها روی دوتا سرور یکی نباشد در سینک اطلاعات به مشکل بر خواهیم خورد حالا برویمم سراغ راه حل این مشکل:
ابتدا باید دیتابیس Shopرو از Availability Group در سرور Node2 خارج کنیم:

:connect NODE2\SQLSERVER2017
ALTER DATABASE [Shop] SET HADR OFF;

وضعیت دیتابیس Shop بر روی سرور Node2 بعد از اجرای دستور بالا:و بعد فایل Shop_File2 را بر روی دیتابیس Shop سرور Node1 بسازیم:

USE [master]
GO
ALTER DATABASE Shop ADD FILE
 (NAME = N'Shop_File2',
 FILENAME = N'E:\Database\NewFile\Shop_File2.ndf' ,
  SIZE = 8192KB ,
  FILEGROWTH = 65536KB
  ) TO FILEGROUP [PRIMARY]
GO

حالا در مرحله بعد باید به سرور Node1 وصل بشویم و یک Log Backup بگیریم:

:connect NODE1\SQLSERVER2017
backup log [Shop] to disk = 'E:\Share\Shop_LOG.trn'

و بعد Log Backup را با آپشن WITH Move و NORECOVERY در سرور Node2 ریستور کنیم :

:connect NODE2\SQLSERVER2017
 RESTORE LOG [Shop] FROM  DISK = N'\\NODE1\Share\Shop_LOG.trn' WITH  FILE = 1,
 NORECOVERY,  NOUNLOAD,  STATS = 10 , move 'Shop_File2' to 'E:\Database\Shop_File2.ndf'
 GO

و در نهایت مجدد دیتابیس را به Availability Group در سرور Node2 اضافه می‌کنیم:

:Connect NODE2\SQLSERVER2017
ALTER DATABASE [Shop] SET HADR AVAILABILITY GROUP = [AG_Shop];
GO