Failover Clustering Queries

Question

Post reply

Failover Clustering Queries

SkyBVI

SSCoach

Points: 15128
More actions
April 8, 2022 at 5:41 am

#4012523
Hello,
We are planning to setup failover clustering on windows server. We are currently having sql server 2016 standard edition.
I have few queries in mind.
1. Do we need to move on to enterprise edition/ sql 2019 before configuring clustering nodes, in case we want to move on future so is it better to move on before configuring clustering or its independent process ?
2. What happens when main node(server/instance) goes down?
  How will the other node come up?
3. If we do maintenance/weekly updates(hardware/sql/software) on one node, how will it replicate/effect other
  node too?
4. What happens if we restart one node? How will it sync with other node?
  5. In case one node goes down, what about the transactions running on that node?
  6. Do we need to backup all data (full bckp,diff,trans log) separately or clustering takes itself in its functionality?
  I need a bit of idea so that we decide the process of setting it up.
  
  Thanks
  Regards
  Skybvi
Regards
Sushant Kumar
MCTS,MCP

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 1

Hi,

are you talking about windows failover cluster, or about always on availibility groups?

These anwsers are for the fail over cluster, not for the aoag.

1)

standard edition is able to create a 2 node cluster:

https://thycotic.force.com/support/s/article/Which-Edition-of-SQL-Server-should-I-use

2)

if the active node will die, the other node will takeover the sql server role.

there is no need to do something.

3)

everything you do inside the sql server instance, will be moved to the next node.

Jobs, User, etc..

But not Updates for SQL or the OS is replicated from one to the other node.

4)

if you restart the passive node, nothing will happen. If you restart the active node, the sql server role will failover to the next node.

5)

a running transaction will be rolled back.

6)

just take a lock at the ola hallengreen scripts, they are very nice and easy for backup.

And let us know, if you are planing a fail over cluster, or a always on availibility group

Kind regards,

Andreas

Johan Bijnens SSC Guru Points: 135353 More actions · Answer 2

Check these articles:

Windows Server Failover Clustering with SQL Server

What is a SQL Server failover clustering

And with regards to Backups: Nothing changes compared to stand-alone instances

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

SkyBVI SSCoach Points: 15128 More actions · Answer 3

Thanks.

We are planning for Failover cluster.

Regarding point 3.

You mean SQL updates(ex edition, upgrade, patches etc) and OS updates will have to be done on both nodes separately

and within sql instance changes (db) will be replicated to other node .

How about the sync then ?

Also, only SAN will be the single point of failure then?

Regards
Sushant Kumar
MCTS,MCP

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 4

Hi,

on a failover Cluster, every node got a sql server Installation. If a failover occours the Cluster Manager will Start the services.

There are patches wich are installed on the passive node, but some Updates have to be installed on the active node.

In a failover Cluster, the whole instance will be failed over, with all objects.

Kind regards.

Andreas

Michael L John One Orange Chip Points: 27053 More actions · Answer 5

SkyBVI wrote:

Thanks.
We are planning for Failover cluster.
Regarding point 3.
You mean SQL updates(ex edition, upgrade, patches etc) and OS updates will have to be done on both nodes separately
and within sql instance changes (db) will be replicated to other node .
How about the sync then ?
Also, only SAN will be the single point of failure then?

In a failover cluster, the database files live on shared storage. SQL server is a separate installation on each server. Regardless of which server is the active node. the database is the same database. There is no "syncing" of data.

Anything this is created in SQL Server will be present. Logins, objects, databases.

What will need to be "synced" would be Windows logins at the server level, OS patches, and SQL patches.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/