Hi,
I have a doubt.
When the SQL server role fail over to the other node,will the SQL service restart during the fail over process?
Thanks
Javeed
Is this a Failover Cluster Instance (FCI) with shared storage - where SQL Server has been installed in the cluster, or is this an Always On Availability Group where SQL Server is not installed in the cluster?
For an FCI - the cluster manages SQL Server. In this configuration - the SQL Server services are set to manual because the cluster manages the service and they will be started when the node takes ownership of the resources in the cluster.
For an AOAG - the cluster does not manage SQL Server and the services are set to automatic and must be running on each node. SQL Server manages the availability group and controls which node in the group is set as primary or secondary.
Thanks Jeff.
Yes,this is a 2 node FCI with SQL on shared storage.
The reason I asked this is,normally when the SQL service is restarted the tempdb gets recreated.So,will the same thing happen when SQL role fail over happens?
Yes - tempdb will be recreated.
1 Like
I did a test and found that the tempdb was not recreated after role fail over.
If the files are the same size as the initial size - then no, they won't be 'recreated' as such. There can also be times when the files initial size has been changed - so the start/restart of SQL Server does not resize the files to what you believe are the initial sizes.
If the expectation is that there will be data in the files after a fail over - that won't happen. And again - if the expectation is that the files will be resized that all depends on the defined initial size of each file.
A fail over event starts the service on the new node and this is a normal SQL Server start - which will recreate tempdb (or clears it).