SQL Server service on active/passive

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

imarchenko - 26 Oct 2005 00:01 GMT

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

Reply to this Message

Geoff N. Hiten - 26 Oct 2005 02:47 GMT

The behavior you observed is normal. The cluster service controls the
stop/start of the actual SQL Services on each node. The SAN drives are
logically connected to both nost nodes. The cluster service arbitrates
ownership so that only the node currently hosting the SQL Instance can
access the disks. If the first node fails, the cluster service shifts the
entire resource group (Virtual Server) to the other node and starts
everything up again. If you examine the system databases, you will notice
that they live on the shared disks. The cluster service runs as a
distributed service using ownership of the Quorum disk to break tie
decisions.

Here is a good white paper to get you started learning about SQL Clustering.

SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx

I would build a test cluster using Virtual Server and get used to managing.
That way, you won't break anything important on a live system while you
clustering

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> Hello,
>
[quoted text clipped - 11 lines]
> Thanks,
> Igor

Reply to this Message

Anthony Thomas - 26 Oct 2005 14:09 GMT

Actually, a clarification, just like the Software Services are turned off on
the passive node, the disks are hardware services that are also turned off
on the passive nodes.

All clustered resources, IPs, Network Names, Disks, SQL Server, are mapped
to resource drivers, that are mapped to server services, software and
hardware. The sole purpose of the cluster is to monitor the Looks Alive and
Is Alive checks, manage replication between nodes, and to take failover
action. That action is nothing more than NET STOP and NET START calls, one
on each node for each resource, in dependency order as defined by the
cluster configuration.

Sincerely,

Anthony Thomas

> The behavior you observed is normal. The cluster service controls the
> stop/start of the actual SQL Services on each node. The SAN drives are
[quoted text clipped - 10 lines]
>
> SQL Server 2000 Failover Clustering

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx

> I would build a test cluster using Virtual Server and get used to managing.
> That way, you won't break anything important on a live system while you
[quoted text clipped - 19 lines]
> > Thanks,
> > Igor

Reply to this Message

Linchi Shea - 26 Oct 2005 03:43 GMT

> 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

> Hello,
>
[quoted text clipped - 11 lines]
> Thanks,
> Igor

Reply to this Message