Log shipping with clustering

#1

We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind of
setup? Is it possible to host the log shiiping secondary database on
the inactive cluster server? Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas

#2

barbara wrote:

Quote:

We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind of
setup?

SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx

How to setup Log Shipping
http://support.microsoft.com/support...ppingFinal.asp

SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)
http://www.microsoft.com/resources/d...s/default.mspx

SQL Server High Availability Resources
http://www.microsoft.com/sql/techinf...ailability.asp

Quote:

Is it possible to host the log shiiping secondary database on
the inactive cluster server?

Yes, you can setup a stand-alone istance and ship one (or more) clustered
database to this istance but is preferable to setup an external box for log
shipping purpose

Quote:

Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas

Log Shipping can increase high availabilty because it reduce the time to
became operational when a disaster on primary server happen. In particular

Clustering
- high level of fault tolerance
- automatic failure
- high cost

Log shipping
- manual failure
- scale out (you can use secondary server as read-only server)
- low cost

Clustering increase the availability but Log Shipping reduce down-time if a
failure occur

Bye

--
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org

#3

Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote

Quote:

barbara wrote:
We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind of
setup?

SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx

How to setup Log Shipping
http://support.microsoft.com/support...ppingFinal.asp

SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)
http://www.microsoft.com/resources/d...s/default.mspx

SQL Server High Availability Resources
http://www.microsoft.com/sql/techinf...ailability.asp

Is it possible to host the log shiiping secondary database on
the inactive cluster server?

Yes, you can setup a stand-alone istance and ship one (or more) clustered
database to this istance but is preferable to setup an external box for log
shipping purpose

Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas

Log Shipping can increase high availabilty because it reduce the time to
became operational when a disaster on primary server happen. In particular

Clustering
- high level of fault tolerance
- automatic failure
- high cost

Log shipping
- manual failure
- scale out (you can use secondary server as read-only server)
- low cost

Clustering increase the availability but Log Shipping reduce down-time if a
failure occur

Bye

#4

I am confused? What would you gain in availability by log shipping to
another cluster node? I would log ship to another (possibly lower
performing) server in another location. Copying data in a tight circle
won't really gain anything in availability, which is the real goal here.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"barbara" <barbara_2003 (AT) hotmail (DOT) com> wrote

Quote:

Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote

barbara wrote:
We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind of
setup?

SQL Server 2000 Failover Clustering

http://www.microsoft.com/technet/pro.../failclus.mspx

How to setup Log Shipping

http://support.microsoft.com/support...ppingFinal.asp

SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)

http://www.microsoft.com/resources/d...s/default.mspx

SQL Server High Availability Resources

http://www.microsoft.com/sql/techinf...ailability.asp

Is it possible to host the log shiiping secondary database on
the inactive cluster server?

Yes, you can setup a stand-alone istance and ship one (or more)
clustered
database to this istance but is preferable to setup an external box for
log
shipping purpose

Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas

Log Shipping can increase high availabilty because it reduce the time to
became operational when a disaster on primary server happen. In
particular

Clustering
- high level of fault tolerance
- automatic failure
- high cost

Log shipping
- manual failure
- scale out (you can use secondary server as read-only server)
- low cost

Clustering increase the availability but Log Shipping reduce down-time
if a
failure occur

Bye

#5

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Quote:

I am confused? What would you gain in availability by log shipping to
another cluster node? I would log ship to another (possibly lower
performing) server in another location. Copying data in a tight circle
won't really gain anything in availability, which is the real goal here.

Actually I can think of a case..... physical disk corruption.

We have a DB where one block of data on teh disk has a bad CRC. This
prevents backups, etc.

We didn't realize the extent of the problem until too late so a restore from
a recent backup is not possible.

In this situation, something like what barbara's proposing might have been
very useful to us.

Having said that, log-shipping to a different machine is even better.

Quote:

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"barbara" <barbara_2003 (AT) hotmail (DOT) com> wrote in message
news:5cd01256.0409091029.5a87adcc (AT) posting (DOT) google.com...
Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote in message
news:<2qajjjFs3b8qU1 (AT) uni-berlin (DOT) de>...
barbara wrote:
We would like to implement 2 node active-passive clustering coupled
with log shipping. Clustering is for high availability and log
shipping for DR purpose. Do we have any white papers for this kind
of
setup?

SQL Server 2000 Failover Clustering

http://www.microsoft.com/technet/pro.../failclus.mspx

How to setup Log Shipping

http://support.microsoft.com/support...ppingFinal.asp

SQL Server Resource Kit (Part 4 is dedicated to Hig Availability)

http://www.microsoft.com/resources/d...s/default.mspx

SQL Server High Availability Resources

http://www.microsoft.com/sql/techinf...ailability.asp

Is it possible to host the log shiiping secondary database on
the inactive cluster server?

Yes, you can setup a stand-alone istance and ship one (or more)
clustered
database to this istance but is preferable to setup an external box
for
log
shipping purpose

Thouigh it doesn't server the DR purpose,
we are exploring all different methods. Please share your ideas

Log Shipping can increase high availabilty because it reduce the time
to
became operational when a disaster on primary server happen. In
particular

Clustering
- high level of fault tolerance
- automatic failure
- high cost

Log shipping
- manual failure
- scale out (you can use secondary server as read-only server)
- low cost

Clustering increase the availability but Log Shipping reduce down-time
if a
failure occur

Bye

#6

barbara wrote:

Quote:

Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't need a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
node). When you install the virtual server instance of SQL Server you have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
SQL Server.
For example

The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)

When you create a clustered at Windows 2000/2003 level you can assign the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)

And then you setup SQL Server in a clustered instance creating a new virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)

If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
you would connect to the stand alone instance you can connect to it without
considering the virtual server name but using NODE1.

Bye

--
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org

#7

Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote

Quote:

barbara wrote:
Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't need a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
node). When you install the virtual server instance of SQL Server you have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
SQL Server.
For example

The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)

When you create a clustered at Windows 2000/2003 level you can assign the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)

And then you setup SQL Server in a clustered instance creating a new virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)

If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
you would connect to the stand alone instance you can connect to it without
considering the virtual server name but using NODE1.

Bye

#8

That's the beauty of clustering. The client doesn't have to do anything,
except reconnect. The virtual server name and IP are both moved to the
other node. The client doesn't reference the node directly - only through
the virtual name and IP.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

"barbara" <barbara_2003 (AT) hotmail (DOT) com> wrote

Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote

Quote:

barbara wrote:
Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't need
a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of both
node). When you install the virtual server instance of SQL Server you have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS for
SQL Server.
For example

The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)

When you create a clustered at Windows 2000/2003 level you can assign the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)

And then you setup SQL Server in a clustered instance creating a new
virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)

If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4). If
you would connect to the stand alone instance you can connect to it
without
considering the virtual server name but using NODE1.

Bye

#9

Currently there is no automagic client redirection. You can handle this in
a variety of ways. You can put the server portion of the conneciotn string
in a registry entry or an XML document and push changes otu to all the
clients. You can do a DNS alias redirect, but as you noted, there is the
client caching issue. This is one reason clustering is so useful. The
clients reconnect using the same connection string after a failover.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"barbara" <barbara_2003 (AT) hotmail (DOT) com> wrote

Quote:

Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote

barbara wrote:
Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't
need a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a
clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of
both
node). When you install the virtual server instance of SQL Server you
have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS
for
SQL Server.
For example

The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)

When you create a clustered at Windows 2000/2003 level you can assign
the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)

And then you setup SQL Server in a clustered instance creating a new
virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)

If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run
on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4).
If
you would connect to the stand alone instance you can connect to it
without
considering the virtual server name but using NODE1.

Bye

#10

Does microsoft know this problem? In enterprise environment, it is
very hard to push the DNS change to all clients. When they offer log
shipping, there should be an easy way for client redirection. I feel
this is is the biggest
drawback with sql server. How do you push XML or registry to all
clients? Do we need to have list of all clinet machine IPs?

Any other ideas in this subject is well appreciated.

Thanks

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Quote:

Currently there is no automagic client redirection. You can handle this in
a variety of ways. You can put the server portion of the conneciotn string
in a registry entry or an XML document and push changes otu to all the
clients. You can do a DNS alias redirect, but as you noted, there is the
client caching issue. This is one reason clustering is so useful. The
clients reconnect using the same connection string after a failover.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"barbara" <barbara_2003 (AT) hotmail (DOT) com> wrote in message
news:5cd01256.0409100354.23e1b6a4 (AT) posting (DOT) google.com...
Thanks for the good explanation. How do we change the client
configuration to point clients from SQLCLUSTER to node1? Oracle uses
LDAP/ONAMES for name resolution which is easier to change. Do we have
any for sql server? Is DNS name change is only option available? How
does it take care of caching at client level?

"Luca Bianchi" <rightjoinREMOVE_THIS (AT) hotmail (DOT) com> wrote in message
news:<2qd1hrFueik4U1 (AT) uni-berlin (DOT) de>...
barbara wrote:
Luca,

Thanks for the info. I've looked at all these articles before. If I
create standby on same server as cluser node (business doesn't want to
spend on another server), how do I failover to this? How does the
client connects to
standby? What name does it uses because the cluster node also exists
on the same server.

I agree completely with Geoff and Greg about their consideration. High
Availability has a cost. If you don't consider it, probably you don't
need a
cluster solution.
By the way you have to refer to the clustered instance with its VIRTUAL
SERVER NAME that is different by the name of two node machine. The stand
alone instance could be referred by the traditional host name.
Imagine that you have a clustered node called NODE1 and the other node
called NODE2 (either with its own IP address). When you setup a
clustered
(at operating system level) you have to assign, for the clustered, one
VIRTUAL SERVER NAME and one VIRTUAL IP ADDRESS (of the same class of
both
node). When you install the virtual server instance of SQL Server you
have
to provide another VIRTUAL SERVER NAME and another VIRTUAL IP ADDRESS
for
SQL Server.
For example

The machine that form the cluster are:
NODE1 (10.0.0.1)
NODE2 (10.0.0.2)

When you create a clustered at Windows 2000/2003 level you can assign
the
following name and ip address to the virtual server
W2KCLUSTER (10.0.0.3)

And then you setup SQL Server in a clustered instance creating a new
virtual
server name with its virtual ip address
SQLCLUSTER (10.0.0.4)

If you install a stand-alone instance on NODE1 you can refer to the
clustered instance with the clustered instance name (that could be run
on
NODE1 or NODE2) using its name or ip address (SQLCLUSTER or 10.0.0.4).
If
you would connect to the stand alone instance you can connect to it
without
considering the virtual server name but using NODE1.

Bye