CODEWITHSUNDEEP
sqlhigh-availabilityalwayson
Nguyễn Đình Tĩnh
Nguyễn Đình Tĩnh

Reputation: 1

Adding database file (mdf) to a Database in AlwaysOn Availability Group

I have a trouble with Adding database file (mdf) to a Database in AlwaysOn Availability Group, detail is bellow:

Currently, I have a AlwaysOn Availability Group have a database name DB_1, DB_1 have 2 DataFile is DB_1.mdf and DB_1.ldf. Server hard disk is almost full. I attach 1 new HDD on each SQL Node.

On SQL Node 1, the new HDD has created a new Partition named E:\, I created a new directory on this, named SQL. On SQL Node 2, I did the same things. Now, on the Primary Node (Node 1), I added a new DataFile named DB_1_Ex.mdf and locate in in E:\SQL\DB_1_Ex.mdf.

After I did these. The DB_1 database on primary node (SQL Node 1) still in Synchronized state. But one the secondary node (SQL Node 2), the DB_1 database shown Not Synchronizing (Suspected).

So, How do I can add the new dataFile to Node 1 and allocate it in (E:\SQL) and then, The AlwaysOn will auto create new database file on node 2 and allocate in the same path on node 2 (E:\SQL too) and not effect to the AlwaysOn Availability Group synchronized.

Upvotes: 0

Views: 1833

Answers (1)

adil ameen
adil ameen

Reputation: 459

  1. Remove the DB_1 database from AG on the secondary server that do not have the same drive configuration as the primary server. This puts the database on the secondary server in restoring state.
  2. Add the E:\SQL\DB_1_Ex.ndf file on the primary server.
  3. Take a log backup on primary server.
  4. Restore the log backup on secondary using the with move option including the new file details and provide a folder that exists on the secondary server.
  5. Add the DB_1 database back to AG on secondary server.

Upvotes: 0

Related Questions