January 24, 2017 at 5:08 am
My understanding is that when performing a manual or automatic failover of an availability group in synchronous mode. Any uncommitted transactions on the primary are rolled back, committed transactions are safe. I have tested this and the ms documentation confirms my understanding is correct.
Does anyone know if there is a way to failover only once all uncommitted transactions have completed (either rolled back or committed)? If not it seems like a major limitation.
January 24, 2017 at 6:39 am
martyn.lawrenson - Tuesday, January 24, 2017 5:08 AMMy understanding is that when performing a manual or automatic failover of an availability group in synchronous mode. Any uncommitted transactions on the primary are rolled back, committed transactions are safe. I have tested this and the ms documentation confirms my understanding is correct.Does anyone know if there is a way to failover only once all uncommitted transactions have completed (either rolled back or committed)? If not it seems like a major limitation.
there will likely awlays be a number of uncommitted transactions small or large.
If you're waiting at the point where you want all to complete but the source database has gone offline\disappeared\etc then it will be a long wait
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2018 at 1:54 am
Ok but if I want to failover manually and in a controlled way, in order to perform planned maintenance on one of the nodes. I’d like to wait until all uncommitted transactions have either committed or rolled back. Does this option exist?
January 29, 2018 at 7:52 am
you can issue checkpoint command to ensure all the dirty pages are written before performing fail-over.
checkpoint
go
alter availability group [myag] failover
go
January 29, 2018 at 9:34 am
goher2000 - Monday, January 29, 2018 7:52 AMyou can issue checkpoint command to ensure all the dirty pages are written before performing fail-over.checkpoint
go
alter availability group [myag] failover
go
That does not commit all uncommitted transactions, which is what the OP wanted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2018 at 10:05 am
umm.... how about that? -- this will not commit transactions but will not failover until all the transactions are committed or rolled back
if not exists (
SELECT
er.session_id
,er.open_transaction_count
FROM sys.dm_exec_requests er where open_transaction_count >0 )
begin
print 'there is nothing to commit'
exec('alter availability group [myag] failover')
end
January 29, 2018 at 12:27 pm
Thanks goher2000
On further investigation it doesn’t even seem possible to do this on a single instance of sql server ( non clustered)
Pausing the service is close to what I need but doesn’t close sessions therefore an open session could create a new transaction.
I’m amazed this simple task is not possible without creating custom code.
Fo info , in oracle this is simply a case of issuing a shutdown transactional command.
January 30, 2018 at 12:13 am
martyn.lawrenson - Monday, January 29, 2018 12:27 PMThanks goher2000On further investigation it doesn’t even seem possible to do this on a single instance of sql server ( non clustered)Pausing the service is close to what I need but doesn’t close sessions therefore an open session could create a new transaction. I’m amazed this simple task is not possible without creating custom code. Fo info , in oracle this is simply a case of issuing a shutdown transactional command.
How it handles the transactions in progress ?
January 30, 2018 at 12:37 am
VastSQL - Tuesday, January 30, 2018 12:13 AMmartyn.lawrenson - Monday, January 29, 2018 12:27 PMThanks goher2000On further investigation it doesn’t even seem possible to do this on a single instance of sql server ( non clustered)Pausing the service is close to what I need but doesn’t close sessions therefore an open session could create a new transaction. I’m amazed this simple task is not possible without creating custom code. Fo info , in oracle this is simply a case of issuing a shutdown transactional command.How it handles the transactions in progress ?
in oracle? it waits until they have committed or rolled back.
January 30, 2018 at 12:51 am
martyn.lawrenson - Tuesday, January 30, 2018 12:37 AMVastSQL - Tuesday, January 30, 2018 12:13 AMmartyn.lawrenson - Monday, January 29, 2018 12:27 PMThanks goher2000On further investigation it doesn’t even seem possible to do this on a single instance of sql server ( non clustered)Pausing the service is close to what I need but doesn’t close sessions therefore an open session could create a new transaction. I’m amazed this simple task is not possible without creating custom code. Fo info , in oracle this is simply a case of issuing a shutdown transactional command.How it handles the transactions in progress ?
in oracle? it waits until they have committed or rolled back.
What if another transaction starts in between? if its not allowing new transaction and if the existing transaction takes more time to complete then DB wont be available till then?
January 30, 2018 at 12:54 am
VastSQL - Tuesday, January 30, 2018 12:51 AMmartyn.lawrenson - Tuesday, January 30, 2018 12:37 AMVastSQL - Tuesday, January 30, 2018 12:13 AMmartyn.lawrenson - Monday, January 29, 2018 12:27 PMThanks goher2000On further investigation it doesn’t even seem possible to do this on a single instance of sql server ( non clustered)Pausing the service is close to what I need but doesn’t close sessions therefore an open session could create a new transaction. I’m amazed this simple task is not possible without creating custom code. Fo info , in oracle this is simply a case of issuing a shutdown transactional command.How it handles the transactions in progress ?
in oracle? it waits until they have committed or rolled back.
What if another transaction starts in between? if its not allowing new transaction and if the existing transaction takes more time to complete then DB wont be available till then?
yes it might take a while to shutdown, and if someone goes to lunch and leaves an uncommitted transaction, you could be waiting a long time. Of course, you can just cancel it and do a shutdown immediate which rolls back uncomitted transactions, but at least you have the choice.
January 30, 2018 at 6:47 am
You can disable logins to prevent further activity while still keeping things online while you wait for system to quiesce.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply