Active/Passive Failover or Database Mirror without a Shared disk?

This is not my expertise, so would appreciate some help.
Is it possible to create an active/passive failover setup or database
mirroring without a shared disk? Client has two sql servers that they
wanted to upgrade SQL 2000 to SQL 2005 and then use the second box for an
active/passive configuration. They do not have a shared disk which I am
being told is necessary for this.
Unfortunately, I do not have the expertise on this and am reaching out to
the gurus here.
Also if any of the SQL experts in this group want some consulting work in
NYC for this job, please contact me off the list.
Lawrence Abrams
MVP - Windows Security
http://www.bleepingcomputer.com
Thank you very much for the info. Starting to make a bit more sense? I
assume for the mirror and the witness they would need to be fully licensed?
Also does the witness need to be as powerful as the actual DB servers?
If you know of any SQL MVPs in the NY area, please have them contact me if
they are interested in possibly doing consulting for this setup.
-L
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efiHUMteHHA.5052@.TK2MSFTNGP06.phx.gbl...
> With no shared storage you rule out clustering but Database Mirroring will
> be fine.A couple of whitepapers to help get your head around DB mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx
>
|||The witness needs a license, but the mirror does not require a SQL license,
provided it is used for failover only. The witness can be a workstation
grade system since all it is doing is observing.
NOTE: Always verify licensing for your configuration with your Microsoft
rep.
While I am not based on the NY area, the company I work for does have a NY
office and would be happy to quote on this project. Email me directly and I
will put you in touch with one of our Business Directors. My posting
address is a valid email address.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lawrence Abrams" <grinler-REMOVETHIS@.bleepingcomputer.com> wrote in message
news:%23l0LVXteHHA.4604@.TK2MSFTNGP06.phx.gbl...
> Thank you very much for the info. Starting to make a bit more sense? I
> assume for the mirror and the witness they would need to be fully
> licensed? Also does the witness need to be as powerful as the actual DB
> servers?
> If you know of any SQL MVPs in the NY area, please have them contact me if
> they are interested in possibly doing consulting for this setup.
> -L
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:efiHUMteHHA.5052@.TK2MSFTNGP06.phx.gbl...
>

active/passive cluster vs log shipping

Hi all, I am trying to weigh the pros and cons vs implementing a sql
active/passive cluster & using log shipping as a failover strategy. I have
successfully setup a custom log shipping infrastructure using sql standard
edition and windows server 2003 standard edition for a client and it is
running fine.
I am in the process of specing out and implementing a sql database setup for
a new client and am faced with what method to go with?
What are the pros & cons from a failover perspective between clustering and
log shipping?
To go with clustering I would have to go with enterprise edition of both
windows and sql. Luckily the data center is providing a shared SAN, so I do
not have to invest in that. Also, they are leasing us the sql enterprise
licenses, so that should cut down some expense.
Do I get any performance gains with going with clustering vs log shipping?
To my understanding clustering a sql database is always active/passive i.e.
2 sql servers can never be load balanced for performance. So in that case if
all I need is failover wouldnt log shipping do it? I guess the difference is
between going down for 5 mins vs going down for 20 mins?
Any advice here is much appreciated.
thanks!
P.S. I do not know if this helps any, but we will start off with sql2k, but
migrate to 2005 when it releases.
Careful with the term active/passive. We now use single-instance or
multi-instance. If you have multi-instance, this means you have more than
one instance in the cluster, likely one instance per node. The cluster
basically takes care of failover on its own. With log-shipping, there is
manual intervention and users are down until you notice the problem, hop in
your car and drive to the data centre.
Log shipping is good for disaster recovery, where you need to establish a
remote DR site.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<param@.community.nospam> wrote in message
news:OkUGMI8ZFHA.3572@.TK2MSFTNGP12.phx.gbl...
Hi all, I am trying to weigh the pros and cons vs implementing a sql
active/passive cluster & using log shipping as a failover strategy. I have
successfully setup a custom log shipping infrastructure using sql standard
edition and windows server 2003 standard edition for a client and it is
running fine.
I am in the process of specing out and implementing a sql database setup for
a new client and am faced with what method to go with?
What are the pros & cons from a failover perspective between clustering and
log shipping?
To go with clustering I would have to go with enterprise edition of both
windows and sql. Luckily the data center is providing a shared SAN, so I do
not have to invest in that. Also, they are leasing us the sql enterprise
licenses, so that should cut down some expense.
Do I get any performance gains with going with clustering vs log shipping?
To my understanding clustering a sql database is always active/passive i.e.
2 sql servers can never be load balanced for performance. So in that case if
all I need is failover wouldnt log shipping do it? I guess the difference is
between going down for 5 mins vs going down for 20 mins?
Any advice here is much appreciated.
thanks!
P.S. I do not know if this helps any, but we will start off with sql2k, but
migrate to 2005 when it releases.
|||Or instead of hop in a car and drive to the data center, remote into the
network and update a dns record to point the database server to the standby
server and you are done
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uMFQyM8ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Careful with the term active/passive. We now use single-instance or
> multi-instance. If you have multi-instance, this means you have more than
> one instance in the cluster, likely one instance per node. The cluster
> basically takes care of failover on its own. With log-shipping, there is
> manual intervention and users are down until you notice the problem, hop
> in
> your car and drive to the data centre.
> Log shipping is good for disaster recovery, where you need to establish a
> remote DR site.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> <param@.community.nospam> wrote in message
> news:OkUGMI8ZFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi all, I am trying to weigh the pros and cons vs implementing a sql
> active/passive cluster & using log shipping as a failover strategy. I have
> successfully setup a custom log shipping infrastructure using sql standard
> edition and windows server 2003 standard edition for a client and it is
> running fine.
> I am in the process of specing out and implementing a sql database setup
> for
> a new client and am faced with what method to go with?
> What are the pros & cons from a failover perspective between clustering
> and
> log shipping?
> To go with clustering I would have to go with enterprise edition of both
> windows and sql. Luckily the data center is providing a shared SAN, so I
> do
> not have to invest in that. Also, they are leasing us the sql enterprise
> licenses, so that should cut down some expense.
> Do I get any performance gains with going with clustering vs log shipping?
> To my understanding clustering a sql database is always active/passive
> i.e.
> 2 sql servers can never be load balanced for performance. So in that case
> if
> all I need is failover wouldnt log shipping do it? I guess the difference
> is
> between going down for 5 mins vs going down for 20 mins?
> Any advice here is much appreciated.
> thanks!
> P.S. I do not know if this helps any, but we will start off with sql2k,
> but
> migrate to 2005 when it releases.
>
>
|||As long as you _can_ remote in...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<param@.community.nospam> wrote in message
news:%23BzVoZ8ZFHA.1088@.TK2MSFTNGP14.phx.gbl...
Or instead of hop in a car and drive to the data center, remote into the
network and update a dns record to point the database server to the standby
server and you are done
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uMFQyM8ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Careful with the term active/passive. We now use single-instance or
> multi-instance. If you have multi-instance, this means you have more than
> one instance in the cluster, likely one instance per node. The cluster
> basically takes care of failover on its own. With log-shipping, there is
> manual intervention and users are down until you notice the problem, hop
> in
> your car and drive to the data centre.
> Log shipping is good for disaster recovery, where you need to establish a
> remote DR site.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> <param@.community.nospam> wrote in message
> news:OkUGMI8ZFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi all, I am trying to weigh the pros and cons vs implementing a sql
> active/passive cluster & using log shipping as a failover strategy. I have
> successfully setup a custom log shipping infrastructure using sql standard
> edition and windows server 2003 standard edition for a client and it is
> running fine.
> I am in the process of specing out and implementing a sql database setup
> for
> a new client and am faced with what method to go with?
> What are the pros & cons from a failover perspective between clustering
> and
> log shipping?
> To go with clustering I would have to go with enterprise edition of both
> windows and sql. Luckily the data center is providing a shared SAN, so I
> do
> not have to invest in that. Also, they are leasing us the sql enterprise
> licenses, so that should cut down some expense.
> Do I get any performance gains with going with clustering vs log shipping?
> To my understanding clustering a sql database is always active/passive
> i.e.
> 2 sql servers can never be load balanced for performance. So in that case
> if
> all I need is failover wouldnt log shipping do it? I guess the difference
> is
> between going down for 5 mins vs going down for 20 mins?
> Any advice here is much appreciated.
> thanks!
> P.S. I do not know if this helps any, but we will start off with sql2k,
> but
> migrate to 2005 when it releases.
>
>
|||So assuming I could remote in, what other benefits do clustering provide me
over log shipping? Is down time the only one?
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23fQEQ18ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> As long as you _can_ remote in...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> <param@.community.nospam> wrote in message
> news:%23BzVoZ8ZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Or instead of hop in a car and drive to the data center, remote into the
> network and update a dns record to point the database server to the
> standby
> server and you are done
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uMFQyM8ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
>
|||In addition to down-time reduction, you have the benefit of faster upgrades
while keeping the system up. For example, let's say you need to apply a
service pack to the OS. Assuming that things are running on node 1, apply
the SP to node 2, move the groups to node 2 and then apply the SP to node 1.
Total down time is the time it took to move the groups (under 1 min). Also,
you could add a node and remove a node to the cluster to upgrade the
hardware (assuming Windows 2000 Data Centre or Windows 2003 Enterprise).
You can run, say, 1 SQL instance on 1 node and another instance on the
other. While not load-balancing in the purest sense of the term, this does
distribute the workload.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<param@.community.nospam> wrote in message
news:uaOhCj$ZFHA.664@.TK2MSFTNGP12.phx.gbl...
So assuming I could remote in, what other benefits do clustering provide me
over log shipping? Is down time the only one?
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23fQEQ18ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> As long as you _can_ remote in...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> <param@.community.nospam> wrote in message
> news:%23BzVoZ8ZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Or instead of hop in a car and drive to the data center, remote into the
> network and update a dns record to point the database server to the
> standby
> server and you are done
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uMFQyM8ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
>

active/active sql server cluster

Hello, Can anyone please explain how the failover processes works on a sql server 2000 2 node active/active cluster.

Given the following
You have 2 node active/active cluster NodeA & NodeB

Question
1. How many SQL instances need to be installed on each node?

2. If the answer to question #1 is one instance per node, then
say if NodeA fails NodeB will take over all the resources of NodeA
including the master database, How does that work , how can once instance (in nodeB) handle two master database i.e its own master database and the one taken over from NodeA.Check this out,

http://www.sql-server-performance.com/clustering_intro1.asp

Active/Active configuration queries

Hi,
I completely understand that SQL-Clustering is failover technology and not a
load
balancing technology.
1. What I'm wondering is when you go for Active/Active mode, then whether
both the nodes communicates with the single instance of shared device or only
1 node
communicates with it?
2. If only 1 node communicates then what is the advantage of Active/Active
configuration over Active/Passive configuration?
3. Also I guess Active/Active configuration requires 2 licenses of SQL-2000.
Is that
true?
4. I know that we can run 16 instances of SQL-2000 on a cluster. Now, I
understand that - logically, each instance data would be different than other
thats why it is a seperate instance. So how does the client application knows
which instance data to use? How does the application takes care of providing
latest data always?
Thanks in advance
Pankaj A. Chitriv
Answers inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:D7F79263-D4D3-4C74-9CBF-E88D95C3E7C7@.microsoft.com...
> Hi,
> I completely understand that SQL-Clustering is failover technology and not
a
> load
> balancing technology.
> 1. What I'm wondering is when you go for Active/Active mode, then whether
> both the nodes communicates with the single instance of shared device or
only
> 1 node
> communicates with it?
The cluster service arbitrates ownership. All host nodes are pysically
connected to the shared storage system, but only one node actually owns and
modifies the on-disk information
> 2. If only 1 node communicates then what is the advantage of Active/Active
> configuration over Active/Passive configuration?
>
Active-Active is really multi-instance. You load a second sql server
instance with a different set of physical shared disks.

> 3. Also I guess Active/Active configuration requires 2 licenses of
SQL-2000.
> Is that
> true?
Depends on your licensing model. Read the licensing page to be sure.
> 4. I know that we can run 16 instances of SQL-2000 on a cluster. Now, I
> understand that - logically, each instance data would be different than
other
> thats why it is a seperate instance. So how does the client application
knows
> which instance data to use? How does the application takes care of
providing
> latest data always?
Each instance has its own Network name and IP address. To the client, they
all look like different SQL servers.

> Thanks in advance
> Pankaj A. Chitriv
>
|||Thanks Geoff again (I remember you)
1. What I understand by "multi-instance" is the different Alias created
using the "Client Network Utility". Is that true? or Instance means- I'll
have to install SQL server and give different service name during
installation?
2. Although each instance has its own Network name and IP address, all of
them points to the same sql database on the shared device. Correct?
Regards,
Pankaj
"Geoff N. Hiten" wrote:

> Answers inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
> news:D7F79263-D4D3-4C74-9CBF-E88D95C3E7C7@.microsoft.com...
> a
> only
> The cluster service arbitrates ownership. All host nodes are pysically
> connected to the shared storage system, but only one node actually owns and
> modifies the on-disk information
> Active-Active is really multi-instance. You load a second sql server
> instance with a different set of physical shared disks.
> SQL-2000.
> Depends on your licensing model. Read the licensing page to be sure.
> other
> knows
> providing
> Each instance has its own Network name and IP address. To the client, they
> all look like different SQL servers.
>
>
>
|||More Comments Inline.
BTW, this is my last answer for about a week. Vacation time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:E0413295-17C7-4A39-A656-753AB9612811@.microsoft.com...
> Thanks Geoff again (I remember you)
> 1. What I understand by "multi-instance" is the different Alias created
> using the "Client Network Utility". Is that true? or Instance means- I'll
> have to install SQL server and give different service name during
> installation?
>
You must supply a unique Network name and IP address for each instance. You
can use the Server Network Utility to assign non-standard port addresses if
you wish.

> 2. Although each instance has its own Network name and IP address, all of
> them points to the same sql database on the shared device. Correct?
>
Wrong. No. Not that. Nada. Nope.
The physical device is "shared" in that it is physically accessable from all
host nodes. It is "shared nothing" in that only a single host actually
controls each LUN at any time. SAN storage devices present logical slices
of their storage systems (LUNS) to their connected hosts. The cluster
service arbitrates ownership so a host owns the storage resources associated
with a specific instance currently running on that host. There is NO
sharing of data storage between instances.
[vbcol=seagreen]
> Regards,
> Pankaj
>
> "Geoff N. Hiten" wrote:
not[vbcol=seagreen]
whether[vbcol=seagreen]
or[vbcol=seagreen]
and[vbcol=seagreen]
Active/Active[vbcol=seagreen]
I[vbcol=seagreen]
than[vbcol=seagreen]
application[vbcol=seagreen]
they[vbcol=seagreen]

Active/Active clustering solution for SQL SERVER

Basically, I need to have real active/active cluster
solution on SQL server(any MS platform).
The windows failover clustering tech is share nothing
clustering , so can not do the load balancing stuff(
distribute transaction traffic between servers).
Only product I know on the market is PolyServ's Matrix
server, any suggestions from microsoft?
Thanks
David Liu
MCSE/MCDBA
DBA
www.moneris.com
The purpose of MS Cluster Servers is to provide high availability not load balancing for SQL Server. If you require both you need multiple clusters as a federation of SQL Servers. Another option is a single 4-node cluster with 4 SQL Instances in a feder
ation configuration. As your 4-nodes reach peak utilitisation, scale up the four servers. To explore how federated solutions work start by searching BOL for "federated database servers".
If you only need read-only access to SQL Server data you could build a solution using replication. As your load increases add more subscribers to handle the load.
|||As I know, The federated database is only support
partition data, do not support share database( exactly the
same database, as oracle's real application cluster).
In a word, failover and load balance at the same time;
and can quickly scale out by simply adding a new server.
From BOL, microsoft say do not support this type of
clustering.
Thanks for your reply
David Liu
>--Original Message--
>The purpose of MS Cluster Servers is to provide high
availability not load balancing for SQL Server. If you
require both you need multiple clusters as a federation of
SQL Servers. Another option is a single 4-node cluster
with 4 SQL Instances in a federation configuration. As
your 4-nodes reach peak utilitisation, scale up the four
servers. To explore how federated solutions work start by
searching BOL for "federated database servers".
>If you only need read-only access to SQL Server data you
could build a solution using replication. As your load
increases add more subscribers to handle the load.
>.
>
|||Thanks David for your interest in Polyserve !
Unfortunately, SQL server itself is a shared nothing application,
which cannot benefit from a clustered file system.
A clustered file system allows all the nodes in the cluster to see
(share) the same file with full read/write access,
but unlike IIS / CIFS applications which can use a clustered file
system to load balanced access (scale out) across all the nodes in the
cluster,
a SQL server instance data and log files is not shared with other SQL
servers instances on other nodes.
But, there are some benefits to gain from the use of a shared file
system for SQL HA
1) a clustered file system is mounted on all the nodes, this
guarantee a seamless transition of the instance in case of disruption,
rather than a typical "failover" scenario with a forced unmount, etc.
2) Large, heterogeneous, node count (16 node SQL Server clusters that
can mix 2p, 4p, and 8p servers)
3) No drive letter limitations
4) Easy storage management
5) Easy management of raw data file for ETL . I.e. you can store all
your
exports in one folder which is seen by all the nodes.
6) Off host backup. All the instances can backup into one location,
and one node can copy the backup to tape.
For more information : http://www.polyserve.com/sql
Tomer Meshorer
Database Engineer
Polyserve
"David Liu" <david.z.liu@.moneris.com> wrote in message news:<197f901c41cb5$ff95c440$a501280a@.phx.gbl>...
> Basically, I need to have real active/active cluster
> solution on SQL server(any MS platform).
> The windows failover clustering tech is share nothing
> clustering , so can not do the load balancing stuff(
> distribute transaction traffic between servers).
> Only product I know on the market is PolyServ's Matrix
> server, any suggestions from microsoft?
> Thanks
> David Liu
> MCSE/MCDBA
> DBA
> www.moneris.com
|||SQL Server does not support multiple machine access to SQL Server database
files. This is a protection mechanism which ensures that processes from
different machines can not step on each other and introduce corruption into
the data files.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com