Sql-server – AlwaysOn High Availability MDF and LDF file locations

disk-spacesql serversql-server-2012

We have 4 replicas with 03 replica in read only mode with manual failover.

We have some drive issues and we need to rearrange the MDF and LDF files.
On the primary we have 2 drives I and J. The I drive houses the MDF/ndf and LDF files. On the J drive are the tempdb files.

In all other 03 replicas we have only I drive with all MDF/ndf/ldf and Temmpdb files.

Currently the setup is working without any issues.

In our primary replica the drive space is running low with 10% left. Can we move the ldf file to the J drive which is 80% free?

Please note that in all 03 read replicas we do not have a J drive.

Best Answer

In our primary replica the drive space is running low with 10% left. Can we move the ldf file to the J drive which is 80% free?

Please note that in all 03 read replicas we do not have a J drive.

In theory, you can use different disks. (Unless you are using direct seeding to seed the databases to the secondaries).

However, I would prefer to have the same disks and disk throughput for transparency and reducing the probability of I/O issues when a secondary becomes primary, among other considerations such as adding data files to your database.

a quick test:

On primary, create a database on two disks

CREATE DATABASE [test]
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'F:\DATA\test.mdf' , SIZE = 105472KB , FILEGROWTH = 102400KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'E:\LOG\test_log.ldf' , SIZE = 11264KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO

BACKUP DATABASE [test] TO DISK = '\\Share\test.bak'
WITH STATS=5;

Restore on secondary on one disk

RESTORE DATABASE [test]
FROM DISK = '\\Share\test.bak'
WITH MOVE 'test' to  'F:\DATA\test.mdf',
MOVE 'test_log' to 'F:\DATA\test_log.ldf',
NORECOVERY,STATS=5;

Add primary to ag (execute on primary)

ALTER AVAILABILITY GROUP AGName ADD DATABASE test;

Add secondary to ag (execute on secondary)

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

enter image description here

Change the data file on the primary

ALTER DATABASE test
MODIFY FILE (name='test',FILENAME = N'H:\DATA\test.mdf')


ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;  
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE

-- copy file

ALTER DATABASE test SET ONLINE


ALTER AVAILABILITY GROUP AG ADD DATABASE test;

Add the secondary to the AG again

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

enter image description here

Changing the secondary to reside on completely different disks

On the primary

ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;  
BACKUP DATABASE [test] TO DISK = '\\BEUAT0563\T$\DATA\test.bak'
WITH STATS=5;

On the secondary

RESTORE DATABASE [test]
FROM DISK = 'T:\DATA\test.bak'
WITH MOVE 'test' to  'T:\DATA\test.mdf',
MOVE 'test_log' to 'T:\DATA\test_log.ldf',
NORECOVERY,STATS=5,REPLACE;

On the primary

ALTER AVAILABILITY GROUP DA4EMS_BEUAT_AG ADD DATABASE test;

On the secondary

ALTER DATABASE test SET HADR AVAILABILITY GROUP = DA4EMS_BEUAT_AG;

Extra considerations

If you where to add extra files to the database on a disk that does not exist on the secondary, you will get an error:

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test2', FILENAME = N'F:\DATA\test2.ndf' , SIZE = 105472KB , FILEGROWTH = 102400KB ) TO FILEGROUP [PRIMARY]
GO

This attempts to create an extra database file on all replica's, expecting the disk to exist.

Related Question