0

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.

1

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.

  • Thanks . Will that mean, it will work or not. i am confused here. – Pavan Reddy Aug 27 at 9:01
  • @PavanReddy no problem, it is possible to change your log file to a different disk but issues can occur. See here is a different Q/A on the issues that could happen when doing this setup. – Randi Vertongen Aug 27 at 9:32
  • When further explored it is found that to alter database we need to take that database out of the AG which is risky and time consuming here as the db size is 6.2 TB. Exploring other options. – Pavan Reddy Aug 29 at 5:52
0

Can we move the ldf file to the J drive which is 80% free?

Yes.

Should you?

Probably not.

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

As mentioned in existing answers. optimally each replica should be simulary configured and capable of working as the primary on failover

Comparable systems: For a given availability group, all the availability replicas should run on comparable systems that can handle identical workloads.

Sufficient disk space: Every computer on which a server instance hosts an availability replica must possess sufficient disk space for all the databases in the availability group. Keep in mind that as primary databases grow, their corresponding secondary databases grow the same amount. Source

If you are running on virtual servers, adding disks should not be difficult. Optimally using four (4) seperate disks for one each for * mdf/ndf * ldf * tempdb mdf/ndf/ldf * backups bak/trn

0

Fundamentally, you may achieve your scenario either Backup - Restore or Detach - Attach method, before proceeding which method to use, consider following recommendations:

General Recommendation:

  1. Keeping Tempdb files along with user/system databases is not good practice
  2. Keep always .ldf into separate drive other than .MDF and .LDF

Your scenario Recommendation:

First, identity what causing Drive Ito be full in Primary replica, probably the .log files, if not so, proceed with following steps. Otherwise, you must focus on LOG Backup before doing or concluding any action plan.

With following steps you can achieve your requirement (login must be sysadmin or these specific permissions to perform following activities):

Let me name your replicas as follows (assumption):

SERVER01 - Primary SERVER02 - Secondary (Read-only, Manual fail-over)
SERVER03 - Secondary (Read-only, Manual fail-over)
SERVER04 - Secondary (Read-only, Manual fail-over)

  1. Connect Primary Replica (SERVER01), make anyone of secondary replica (example: SERVER02) Availability Mode = Synchronous commit you can do this via SSMS -> Always On High Availability -> Availability Groups -> GroupName (Right Click) Properties

  2. Connect Primary Replica (SERVER01), once secondary replica ready for fail-over without data loss, fail-over targeted database into anyone of secondary replica (SERVER02) or connect secondary (SERVER02) and run following command:

alter availability group YourAGName failover;

  1. Remove database from old primary replica (SERVER01), you may use following commands:
ALTER DATABASE YourDatabase SET HADR OFF; 

Drop database YourDatabase;
  1. Connect new primary replica (SERVER02), take full and log backup
  2. Connect old Primary replica (SERVER01), restore the backup that been taken on Step4, it must be With NORECOVERY and into desired new location (Drive J) - Look at screenshots for the reference.
  3. Connect old primary replica (SERVER01), join the newly restored database into availability group
ALTER DATABASE YourDatabase SET HADR AVAILABILITY GROUP = YourAGName; 
  1. Once synchronization is finished, you may fail-back from SERVER02 to SERVER01

Step 5 References:

General Page

enter image description here

Files Page

enter image description here

  • Thanks. We are using log backups and only cause our db is growing rapidly with business needs. Database size is 7 TB. Here the issue is w eare using Service Broker in our environment and when we failover to second replica and make that replica as Primary, service Broker won't work. Taking backup of 7 TB and transfer/restore will cost us at least 6 hours which is worried one. – Pavan Reddy Aug 27 at 9:00
  • 1
    You may want to switch service broker to point on Listener before doing fail-over, this would make a real Always On HA not only current scenario, but also in future maintenance. Agree with worried point, but BACKUP-RESTORE would be safest method in your case. You may think of DETACH-COPY-ATTACH but that required down-time. aside from all, hoping that same and recommended storage specs being applied to all replicas. – Shekar Kola Aug 27 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.