Steps for Adding a Secondary Datafile When Paths are Not Identical

Introduction

The database administrator (DBA) sometimes must add additional data files for many reasons. In this blog post, we will see the detailed steps on how to add the secondary data file when the database (DB) has log shipping (LS), mirroring and Always On Availability Group (AOAG).

In all the following scenarios, when you add a secondary data file on the primary server and the same identical path is available on a secondary server, a DBA has nothing to do because the files will be auto-created through their data transfer process.

Note: The scenarios we’re discussing here are only followed when the paths are not identical.

Scenario 1: Adding datafile to a DB configured in LS

1. Check the LS report and make sure the backup, copy and restore has the same file name. Once LS is in sync, proceed with step 2.

2. Stop and disable the LS backup, copy and restore jobs related to the DB that you’re adding a secondary file.

3. Add the secondary data file on the primary using ALTER DATABASE db_name ADD FILE.

4. Run the LS backup job manually on the primary server to generate a T-log backup.

5. Run the copy job manually so that the T-log with the above changes is shipped to the secondary server.

6. Manually restore the log file from step 5 using NoRecovery and Move parameters.

7. Once these steps are successful, enable LS backup, copy and restore jobs previously disabled in step 2.

8. Validate that the new datafile has been created on a secondary server using this t-sql:

USE master;

SELECT name 'Logical Name', physical name 'File Location' FROM sys.master_files;

9. Validated to ensure all LS backup, copy and restore jobs are successful in the next run.

Scenario 2: Adding datafile to DB configured in mirroring:

1. Principal server: Ensure mirrored DB is in synchronized state.

2. Principal server: Disable any T-log backup job.

3. Principal server: Stop the mirroring: Alter DB [Mirror DB] set partner off. You’ll notice that DB mirroring has been removed and mirror DB will be restoring.

4. Principal server: Add secondary data file to the principal server DB.

5. Principal server: Take T-log backup of principal DB.

6. Principal server: Verify if the log backup has a newly added file: restore file is only from disk=’path of the .trn file taken in step 5.

7. Copy the log backup to the mirror server.

8. Mirror server: Restore the log file on the mirror server DB using the move option.

9. Mirror server: Verify newly created file added to the mirror DB: select * from sysaltfiles where dbid = DB_ID ('Mirror database')

10. Mirror server: Reinitiate the mirroring from the mirror server: alter database [Mirror DB] set partner ='tcp://PrinciapServerName:5022'

11. Principal server: Initiate mirroring step from principal server: alter database [Mirror DB] set partner='tcp://MirrorServerName:5022'

12. Ensure the mirrored DB is in synchronized state.

Scenario 3: Adding datafile to the DB configured in AG

1. Primary replica: Ensure AOAG health is verified, and DB is in synchronized state.

2. Secondary replica: Remove the DB from the secondary replica.

3. Secondary replica: Secondary DB will be showing in the restoring state.

4. Primary replica: Add secondary data file to the availability DB.

5. Primary replica: Trigger a T-log backup of the availability DB.

6. Copy the log backup to the secondary replica.

7. Secondary replica: Restore the copied log backup on its corresponding secondary replica: database with NORECOVERY and WITH MOVE option.

8. Secondary replica: Later add the DB back to the AG group.

9. Primary replica: Ensure DB is in synchronized state.