How to add data file to a mirrored database? RRS feed

  • General discussion

  •  Ans:

    If data files path is identical on both the servers then we can add the data file during a mirroring session without impacting the mirroring.
     But if data files path is not identical on both the servers then SQL Server will not be able to create the secondary data file on the mirrored server
     instance and the databases will go out of sync and it will force database mirroring configuration to go into a suspended state.
     Steps to resolve the issue when data files path is not identical on both the servers are: 

    1. First Check the database mirroring configuration and partner status: To Make sure mirrored database is in synchronized state.

      SQL >  SELECT (SELECT DB_NAME(5)) AS DBName,database_id,mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,
      mirroring_partner_instance FROM sys.database_mirroring WHERE database_id=5

      SELECT db_name(database_id) as database_name, mirroring_state_desc,
       mirroring_role_desc,  mirroring_partner_name,  mirroring_partner_instance
       FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'

     2.  Disable backup jobs at principal server if any to reduce any complexity. If any log backup occurs in between, then the same needs to be restored
         on the mirrored server before re-establishing the database mirror.

     3. Remove the mirror partner to continue with secondary database file creation.

     4. Add the secondary file group / file on the principal server
      SQL >  ALTER DATABASE aspdb
       ADD FILE (NAME = 'aspdbTFH2',
       FILENAME = 'D:\DatabaseMirroring\Data\aspdbTFH2.ndf',

     5. Backup Transaction log & restore it on the mirror server using WITH MOVE option
      SQL >  BACKUP LOG aspdb
       TO DISK = 'D:\DatabaseMirroring\Data\aspdb_Tran.trn'
       WITH INIT
      Optional: Confirm that the transaction log has captured secondary file creation at primary by restoring the file list
       FROM DISK = 'D:\DatabaseMirroring\Data\aspdb_Tran.trn'

      Restore the log backup on the mirrored server using NORECOVERY and MOVE options. MOVE option is required to place the file in a different location on
      the mirrored server.

      SQL >  RESTORE LOG aspdb
       FROM DISK = 'D:\DatabaseMirroring\Data\aspdb_Tran.trn'
       MOVE 'aspnetdbTFH2'
       TO 'D:\DatabaseMirroring\Data\aspdbTFH2_M.ndf'

     6. Re-establish mirroring session. First set the partner on the mirror & then the principal.

      At Mirror:

      SQL >  ALTER DATABASE aspdb
       SET PARTNER = 'TCP://'

      At Principal

      SQL >  ALTER DATABASE aspndb
       SET PARTNER = 'TCP://'

    Saturday, November 11, 2017 7:52 AM

All replies