Re: SQL Server service on active/passive

> I have inherited 2 node active/passive SQL Server cluster. I have
> noticed that SQL Server service is running on active node only. I was
> wondering if this is normal behavior.

By definition, the node that runs the SQL instance is the active node. So
this cannot be any more normal. Typically, people would consider one of the
two nodes to be the preferred node, and will run the SQL instance on that
node under normal circumstances. If you have only one instance in this
cluster and yuor two nodes are identically configured, it shouldn't really
make any difference on which node the SQL instance is running. If you have
multiple instances in a cluster, it's often important to watch which
instance runs on which node because of resource consumption issues. So for
instance, if your normal configuration is to run instance 1 on node A and
instance 2 on node B, having them both run on the same node may degrade
their performance because they have to compete for CPU among other things.

> In case of failover, will SQL Server service start automatically on
> passive node?
Yes. The SQL services will be started by the cluster service on the other
node.

> What will happen if new databases created on while service on active node
> is running and failover occurs?
If the CREATE DATABASE has not completed when failover happens, the
transaction will be rolled back, and the database will not be created. This
is not different from any other in-flight transaction, and it is no
different from crashing/starting an instance on a standalone box.

> I guess, since system specific information is stored in system tables
> rather than on share SAN (in our case) drive, I am not sure how will it be
> replicated to passive node in case of failover.

All the databases, including the system databases, should reside on the
shared drives (i.e. your shared SAN). All nodes are seeing the same data,
albeit not at same time. There is only one copy of the data. So there is no
replication of the system tables from one node to another.

Now, the system info in the SQL Server registry is a different matter. The
cluster service wll replicate them among the nodes, and that is part of an
app being cluster aware.

Linchi

"imarchenko" <igormarchenko@xxxxxxxxxxx> wrote in message
news:e8rzRfb2FHA.892@xxxxxxxxxxxxxxxxxxxxxxx
> Hello,
>
> I have inherited 2 node active/passive SQL Server cluster. I have
> noticed that SQL Server service is running on active node only. I was
> wondering if this is normal behavior. In case of failover, will SQL Server
> service start automatically on passive node? What will happen if new
> databases created on while service on active node is running and failover
> occurs? I guess, since system specific information is stored in system
> tables rather than on share SAN (in our case) drive, I am not sure how
> will it be replicated to passive node in case of failover.
> I would appreciate if somebody can point me to the articles on this
> subject.
>
> Thanks,
> Igor
>


.

Relevant Pages

  • Re: Clustering with 2 active nodes and 1 passive node
    ... Mike Epprecht, Microsoft SQL Server MVP ... >> If you have 2 nodes, one sits around waiting for a failover. ... >>> we are being asked to setup a 2 active node, 1 passive node environment. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Why Cluster in a test Environment?
    ... If your apps are not failover aware, ... SQL Server 2000 then takes anywhere from 30 seconds to minutes before ... a cluster but not appear in a standalone server. ... A non-clustered environment would not allow accurate stress testing do ...
    (microsoft.public.sqlserver.clustering)
  • Re: High Availability
    ... also does failover. ... Async replication is real-time, as opposed to the scheduled Log Shipping ... > Note that one of the considerations for the active/passive GEO Cluster is ... > SQL Server 2000 High Availability Series ...
    (microsoft.public.sqlserver.clustering)
  • Re: Dear Ekrem
    ... I do not think you know the difference between Two Active Nodes in a Cluster or only one active node. ... When you have only one active node in your cluster, then you will be able to use only one SQL Server server at one time. ... So they said that they want to keep a live copy of their databases on the second node that's why they wanted two active nodes. ...
    (microsoft.public.sqlserver.setup)
  • Cluster 3 nodes
    ... I tested a failover cluster solution with Windows 2003 and SQL Server 2005 ... Now I'm interested to add a third node ...
    (microsoft.public.sqlserver.clustering)
Loading