AG Cluster db upgrade by vendor
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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?
sql-server
add a comment |
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?
sql-server
add a comment |
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?
sql-server
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?
sql-server
sql-server
asked Mar 15 at 13:11
Mike RatliffMike Ratliff
61
61
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
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.
add a comment |
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
add a comment |
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.
add a comment |
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.)
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232251%2fag-cluster-db-upgrade-by-vendor%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
add a comment |
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.
add a comment |
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.
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.
edited Mar 15 at 13:39
answered Mar 15 at 13:27
George.PalaciosGeorge.Palacios
2,696826
2,696826
add a comment |
add a comment |
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
add a comment |
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
add a comment |
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
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
answered Mar 15 at 14:03
James JenkinsJames Jenkins
2,21022045
2,21022045
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Mar 15 at 13:31
Tony HinkleTony Hinkle
3,0901725
3,0901725
add a comment |
add a comment |
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.)
add a comment |
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.)
add a comment |
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.)
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.)
answered Mar 15 at 16:03
Ramakant DadhichiRamakant Dadhichi
1,063319
1,063319
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232251%2fag-cluster-db-upgrade-by-vendor%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown