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
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
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.
