Cfxtrjtrk

Vendor wants to put db into simple mode prior to upgrade then back into full recovery mode after upgrade. I do not want to do this because I am not sure what impact it will have on the db cluster. Won't have to drop the db from the AG then put it back in after this process?

As seen in the availability group prerequisites the database must be in full recovery mode.

To change the recovery model, the database must be removed from the Availability Group, and it must be changed back to FULL before adding back into the Availability Group.

Obviously be very careful here - changing the database to single mode will break your log backup chains so you will be without that recovery mechanism if it goes wrong.

There are good answer by George.Palacios & Tony Hinkle

I suspect the vendor wants to switch to simple because of the amount of log space the upgrade is going to take. Obviously make the upgrade in your test environment first while in full recovery would answer some questions.

If you do make the upgrade while in full recovery. You can monitor things with these two queries. When the log drive is getting full, run a t-log backup.

-- Look for locks and waits 

-- !!!!!Be sure to put your database name in the where clause!!!!!!-----------------

Select session_ID

, Start_time

, [Status]

, command

, user_id

, blocking_session_id as 'blocking ID'

, wait_type

, wait_time

--, estimated_completion_time  as 'est comp time'--Values can fluctuate wildly, When it is smaller the cpu_time and decreasing can suddendly finish.  

, cpu_time

, percent_complete as '%conmplete'

, lock_timeout

, deadlock_priority

, last_wait_type

, SDB.name as 'DB_name'

, SDB.state_desc as 'DB_Status'

--, * 



From sys.dm_exec_requests



left join sys.databases as SDB

on sys.dm_exec_requests.database_id = SDB.database_id



where [status] not in ('background','sleeping')

and SDB.name = 'DB_name'-- The database I am working on ---------<<<<<<<<<<<<<<< Change this value------------





--Identifies used space on files, how much data has moved.

-- Taken from my DataFileFreeSpace Query

select file_id

, type_desc

, name

, substring([physical_name],1,3) AS [Drive]

, physical_name

, state_desc

, size / 128 as 'AllocatedSizeMB'

, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1

, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'

, growth / 128 as 'GrowthSettingMB'



 from sys.database_files

 order by type_desc Desc, name

Yes, you will have to remove it from the availability group in order to put the database in simple recovery mode. The AG replication mechanism requires full recovery mode and it depends on the transactions being fully logged.

If the database can be synchronized quickly (i.e., it's not too big), removing it from the AG and adding it back is not difficult or time consuming. So whether you want to fight this battle with the vendor probably just depends on how long it would take to resynch.

My 2 cents :

Advantage of removing the database from AG Group :

Just make sure they are fully synchronised before removing it from AG. This gives you a copy of the database in secondary which will not be affected by the upgrade in case of a failure.

Also since you will make the recovery model 'Simple' the log growth will be under control.

In case of a failure or data corruption in Primary, the secondary can be used for rollback.(In case the database is huge and restoring backup takes too long.)