January 4, 2021 at 12:00 am
Comments posted to this topic are about the item Do *not* place TEMPDB on a local disk in a SQL failover cluster
January 4, 2021 at 9:29 am
You can create an Agent SQL alert that forces the failover(s)...
January 4, 2021 at 10:02 am
Thank you for sharing this estonishing "story from the trenches".
Happy new year.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 4, 2021 at 11:46 am
@Frédéric BROUARD,
Yup, we mentioned a few options at the end of the article, but I'm sure there are many more workarounds that could work. I'm hoping more people will share their thoughts so we can pick the optimal solution for each specific setup 🙂
January 4, 2021 at 1:00 pm
@Frédéric Brouard,
Good suggestion.
It did cross my mind. I still have to test if it is possible to initiate a failover of the sql server instance from sql agent since the sql agent will stop before the sql instance in a failover scenario. From a remote sql agent from another instance it should definitely work.
January 4, 2021 at 2:27 pm
Hmmm .. I did this very successfully about 6 years ago - the important point for any dba is always have a backup strategy, I deployed 2 pci-e flash cards per node and split my tempdb files across the two cards.
I did extensive failure testing on what happened if a card failed and just as you would never deploy your sql server or databases to a non raid solution why would you even consider deploying tempdb to a single device?
I used this strategy on both failover clusters and Availability Groups, I never had an any issues and yes all my storage was monitored with suitable alerts.
I did write a white paper on this but i never published it generally as I assumed it wasn't really a big deal. It was for me a critical performance enhancement for some very busy 24 x 7 systems.
interesting that I don't ever recollect anyone writing about this previously. good article though.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 5, 2021 at 11:46 am
For those DBA's that would like to see a traceflag added to alter the behaviour of SQL on this problem, please upvote on this link
January 5, 2021 at 2:11 pm
I kept trying to understand if this applied to our setup or not. I do use a dedicated TempDB drive for each node in a Failover cluster of SQL Servers. I finally realized that you are talking about physical servers, and all of our SQL Servers are virtual now. So even though each of mine have their own "local disk", that disk is just another vhdx on the very redundant hyperconverged system. I will certainly check to see if the TempDB drive is monitored by the Failover Manager. Great article overall, and I certainly agree that I would consider a SQL Server with no usable TempDB drive a failed system, and such a server should fail over by default, or have an option to have that considered a failed condition.
Student of SQL and Golf, Master of Neither
January 5, 2021 at 2:23 pm
Hi Bob.
This article also applies to VM's, even the cloud ones. If for some reason the underlying infrastructure has an issue that impacts your "local" drive this scenario will happen.
I.E, Azure LS32 VM's have 4 NVME drives... if you stripe those for performance and one fails...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply