How Does Log Shipping / Database Mirroring Work with a SAN?

[Database Administrator]

We have two servers running Windows 2003 Server Standard Edition, SQL Server
2005 Standard Edition and a SAN.

How do we set up this configuration to automatically failover from one
server to the other in the event that the primary server goes down? I had
thought of log shipping or database mirroring, but both seem to imply that
the database resides on the servers, whereas ours will be on the SAN.

Thanks in advance for any help.

Charles

[Database Administrator]

Charles,

Of the two technologies you mention only database mirroring can provide
automatic failover to a second server (mirror) and of course you can use a
SAN for storage.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica



"Charles Law" wrote:

> We have two servers running Windows 2003 Server Standard Edition, SQL Server
> 2005 Standard Edition and a SAN.
>
> How do we set up this configuration to automatically failover from one
> server to the other in the event that the primary server goes down? I had
> thought of log shipping or database mirroring, but both seem to imply that
> the database resides on the servers, whereas ours will be on the SAN.
>
> Thanks in advance for any help.
>
> Charles
>
>
>

[Database Administrator]

Essentially, clustering would require you to have a SAN or some type of
shared storage. With Database Mirroring, you may still use a SAN for
storage, but it will not be shared between the two servers. Each server
will have a copy of the DB attached to it.

Log Shipping does not provide automatic failover as Ban pointed out, but you
could use it as a failover technology (just not automatic - more manual
steps). DB Mirroring saves many (but not all) of the steps needed for all
aspects of automatic failover. It will depend a lot on your environment.

Rick Heiges
SQL Server MVP

"Ben Nevarez" wrote in message
news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>
> Charles,
>
> Of the two technologies you mention only database mirroring can provide
> automatic failover to a second server (mirror) and of course you can use a
> SAN for storage.
>
> Hope this helps,
>
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
>
>
> "Charles Law" wrote:
>
>> We have two servers running Windows 2003 Server Standard Edition, SQL
>> Server
>> 2005 Standard Edition and a SAN.
>>
>> How do we set up this configuration to automatically failover from one
>> server to the other in the event that the primary server goes down? I had
>> thought of log shipping or database mirroring, but both seem to imply
>> that
>> the database resides on the servers, whereas ours will be on the SAN.
>>
>> Thanks in advance for any help.
>>
>> Charles
>>
>>
>>

[Database Administrator]

Hi Ben

Thanks for the quick response.

What I have read about database mirroring seems to indicate that the
database is copied. However, because we are using a SAN, it seems to me that
the database copy would be unnecessary as it is only the server box that has
failed and the storage is intact. Would database mirroring still be
applicable here, and if so is there a special way to set it up so that it
never actually performs a database mirror?

Alternatively, am I looking at the wrong technology? Is there another way
that is more appropriate given our hardware and software levels.

Cheers.

Charles


"Ben Nevarez" wrote in message
news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>
> Charles,
>
> Of the two technologies you mention only database mirroring can provide
> automatic failover to a second server (mirror) and of course you can use a
> SAN for storage.
>
> Hope this helps,
>
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
>
>
> "Charles Law" wrote:
>
>> We have two servers running Windows 2003 Server Standard Edition, SQL
>> Server
>> 2005 Standard Edition and a SAN.
>>
>> How do we set up this configuration to automatically failover from one
>> server to the other in the event that the primary server goes down? I had
>> thought of log shipping or database mirroring, but both seem to imply
>> that
>> the database resides on the servers, whereas ours will be on the SAN.
>>
>> Thanks in advance for any help.
>>
>> Charles
>>
>>
>>

[Database Administrator]

Although 2-node clustering is a feature of SQL Server 2005 Standard Edition,
It would still require Enterprise Edition of OS for clustering. Also,
Clustering needs to have hardware that is on the Windows Catalog (formerly
known as the Hardware Compatibility List or HCL). It has to be a certified
system for a clustering solution and not just parts. Check with your
manufacturer to see if it qualifies as they should know.

Hope this helps!

Rick Heiges
SQL Server MVP

"Charles Law" wrote in message
news:eEzNkevJIHA.3356@TK2MSFTNGP02.phx.gbl...
> Hi Ben
>
> Thanks for the quick response.
>
> What I have read about database mirroring seems to indicate that the
> database is copied. However, because we are using a SAN, it seems to me
> that the database copy would be unnecessary as it is only the server box
> that has failed and the storage is intact. Would database mirroring still
> be applicable here, and if so is there a special way to set it up so that
> it never actually performs a database mirror?
>
> Alternatively, am I looking at the wrong technology? Is there another way
> that is more appropriate given our hardware and software levels.
>
> Cheers.
>
> Charles
>
>
> "Ben Nevarez" wrote in message
> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>>
>> Charles,
>>
>> Of the two technologies you mention only database mirroring can provide
>> automatic failover to a second server (mirror) and of course you can use
>> a
>> SAN for storage.
>>
>> Hope this helps,
>>
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>>
>>
>> "Charles Law" wrote:
>>
>>> We have two servers running Windows 2003 Server Standard Edition, SQL
>>> Server
>>> 2005 Standard Edition and a SAN.
>>>
>>> How do we set up this configuration to automatically failover from one
>>> server to the other in the event that the primary server goes down? I
>>> had
>>> thought of log shipping or database mirroring, but both seem to imply
>>> that
>>> the database resides on the servers, whereas ours will be on the SAN.
>>>
>>> Thanks in advance for any help.
>>>
>>> Charles
>>>
>>>
>>>
>
>

[Database Administrator]

If I understand correctly you are looking for automatic failover but without
a second copy of the database. That is exactly how failover clustering works.
You can have two servers, everything will be duplicated except the storage
and the database. If you lose one server the second one will serve the users,
but using the same copy of the database.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica



"Charles Law" wrote:

> Hi Ben
>
> Thanks for the quick response.
>
> What I have read about database mirroring seems to indicate that the
> database is copied. However, because we are using a SAN, it seems to me that
> the database copy would be unnecessary as it is only the server box that has
> failed and the storage is intact. Would database mirroring still be
> applicable here, and if so is there a special way to set it up so that it
> never actually performs a database mirror?
>
> Alternatively, am I looking at the wrong technology? Is there another way
> that is more appropriate given our hardware and software levels.
>
> Cheers.
>
> Charles
>
>
> "Ben Nevarez" wrote in message
> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
> >
> > Charles,
> >
> > Of the two technologies you mention only database mirroring can provide
> > automatic failover to a second server (mirror) and of course you can use a
> > SAN for storage.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Charles Law" wrote:
> >
> >> We have two servers running Windows 2003 Server Standard Edition, SQL
> >> Server
> >> 2005 Standard Edition and a SAN.
> >>
> >> How do we set up this configuration to automatically failover from one
> >> server to the other in the event that the primary server goes down? I had
> >> thought of log shipping or database mirroring, but both seem to imply
> >> that
> >> the database resides on the servers, whereas ours will be on the SAN.
> >>
> >> Thanks in advance for any help.
> >>
> >> Charles
> >>
> >>
> >>
>
>
>

[Database Administrator]

Hi Rick

The hardware is all Dell, so hopefully this would qualify. As mentioned, we
are currently aiming at Standard Edition Server 2003, so I appreciate this
does not provide clustering (we might have to go that way though).

Thanks for all the info. It seems that to get fully automatic failover then
we probably should look at clustering. Otherwise, we can use database
mirroring, and duplicate the database on the SAN. As log shipping does not
failover automatically I suspect that we will not be able to use that idea.

Many thanks.

Charles


"Rick Heiges" wrote in message
news:eQ2DBkvJIHA.2480@TK2MSFTNGP05.phx.gbl...
> Although 2-node clustering is a feature of SQL Server 2005 Standard
> Edition, It would still require Enterprise Edition of OS for clustering.
> Also, Clustering needs to have hardware that is on the Windows Catalog
> (formerly known as the Hardware Compatibility List or HCL). It has to be
> a certified system for a clustering solution and not just parts. Check
> with your manufacturer to see if it qualifies as they should know.
>
> Hope this helps!
>
> Rick Heiges
> SQL Server MVP
>
> "Charles Law" wrote in message
> news:eEzNkevJIHA.3356@TK2MSFTNGP02.phx.gbl...
>> Hi Ben
>>
>> Thanks for the quick response.
>>
>> What I have read about database mirroring seems to indicate that the
>> database is copied. However, because we are using a SAN, it seems to me
>> that the database copy would be unnecessary as it is only the server box
>> that has failed and the storage is intact. Would database mirroring still
>> be applicable here, and if so is there a special way to set it up so that
>> it never actually performs a database mirror?
>>
>> Alternatively, am I looking at the wrong technology? Is there another way
>> that is more appropriate given our hardware and software levels.
>>
>> Cheers.
>>
>> Charles
>>
>>
>> "Ben Nevarez" wrote in message
>> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>>>
>>> Charles,
>>>
>>> Of the two technologies you mention only database mirroring can provide
>>> automatic failover to a second server (mirror) and of course you can use
>>> a
>>> SAN for storage.
>>>
>>> Hope this helps,
>>>
>>> Ben Nevarez
>>> Senior Database Administrator
>>> AIG SunAmerica
>>>
>>>
>>>
>>> "Charles Law" wrote:
>>>
>>>> We have two servers running Windows 2003 Server Standard Edition, SQL
>>>> Server
>>>> 2005 Standard Edition and a SAN.
>>>>
>>>> How do we set up this configuration to automatically failover from one
>>>> server to the other in the event that the primary server goes down? I
>>>> had
>>>> thought of log shipping or database mirroring, but both seem to imply
>>>> that
>>>> the database resides on the servers, whereas ours will be on the SAN.
>>>>
>>>> Thanks in advance for any help.
>>>>
>>>> Charles
>>>>
>>>>
>>>>
>>
>>
>
>

[Database Administrator]

Yes, that is what I was hoping for. I had considered clustering originally,
but the extra cost of two Windows Server 2003 Enterprise licences had put me
off. I now think that this might actually be the only way to go.

Thanks for your help.

Charles


"Ben Nevarez" wrote in message
news:9F00F9B1-9F86-4AD4-A4B9-35AF16B29697@microsoft.com...
>
> If I understand correctly you are looking for automatic failover but
> without
> a second copy of the database. That is exactly how failover clustering
> works.
> You can have two servers, everything will be duplicated except the storage
> and the database. If you lose one server the second one will serve the
> users,
> but using the same copy of the database.
>
> Hope this helps,
>
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
>
>
> "Charles Law" wrote:
>
>> Hi Ben
>>
>> Thanks for the quick response.
>>
>> What I have read about database mirroring seems to indicate that the
>> database is copied. However, because we are using a SAN, it seems to me
>> that
>> the database copy would be unnecessary as it is only the server box that
>> has
>> failed and the storage is intact. Would database mirroring still be
>> applicable here, and if so is there a special way to set it up so that it
>> never actually performs a database mirror?
>>
>> Alternatively, am I looking at the wrong technology? Is there another way
>> that is more appropriate given our hardware and software levels.
>>
>> Cheers.
>>
>> Charles
>>
>>
>> "Ben Nevarez" wrote in message
>> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>> >
>> > Charles,
>> >
>> > Of the two technologies you mention only database mirroring can provide
>> > automatic failover to a second server (mirror) and of course you can
>> > use a
>> > SAN for storage.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Charles Law" wrote:
>> >
>> >> We have two servers running Windows 2003 Server Standard Edition, SQL
>> >> Server
>> >> 2005 Standard Edition and a SAN.
>> >>
>> >> How do we set up this configuration to automatically failover from one
>> >> server to the other in the event that the primary server goes down? I
>> >> had
>> >> thought of log shipping or database mirroring, but both seem to imply
>> >> that
>> >> the database resides on the servers, whereas ours will be on the SAN.
>> >>
>> >> Thanks in advance for any help.
>> >>
>> >> Charles
>> >>
>> >>
>> >>
>>
>>
>>

[Database Administrator]

Hello Charles

I read all messages about your question and read all replies that my helpful
friends gave you.

However, I' d like to add some extra info here.

It seems that you are looking for High Availibility, not Disaster Recovery.

When you have High Availibility, you just keep your system up as much as
possible. However, if you do not have a Disaster Recovery system, then if
you lose your data, it would be useless if your system up or down. I still
do not know if you have a DR system implemented but I wanted to stress this
out here.

Also, there are three categories about DR and HA. These are:
Hot Standby: Ones that has automatic failover feature (Such as Failover
Clustering and Database Mirroring with Failover)
Warm Standby: Ones that has manual failover feature (Such as Log Shipping,
Database Mirroring - High Performance and Database Mirroring - High
Protection modes)
Cold Standby: Ones that has no failover feature at all. You perform all
processes manually. (Such as Backup\Restore and Attach\Detach)

You say that you data are stored in disks that are attached to your SAN.
However, those disks also may corrupt. Even you'll set up your disks as RAID
Arrays, it's a possibility that one of your RAID Arrays may corrupt. You
know, everything is possible. So, I suggest you think about a DR plan if you
do not have one yet.

P.S.
Database Mirroring have 3 modes as listed above and only one of them
provides Automatic Failover.

If you already have a SAN and it's better to think about a Failover
Clustering here if your budget let you buy new Enterprise license (if you
plan to set more than one active node in your cluster then you'll need
licenses as much as active nodes in your cluster)

--
Ekrem Önsoy



"Charles Law" wrote in message
news:ucl1ILwJIHA.4712@TK2MSFTNGP04.phx.gbl...
> Yes, that is what I was hoping for. I had considered clustering
> originally, but the extra cost of two Windows Server 2003 Enterprise
> licences had put me off. I now think that this might actually be the only
> way to go.
>
> Thanks for your help.
>
> Charles
>
>
> "Ben Nevarez" wrote in message
> news:9F00F9B1-9F86-4AD4-A4B9-35AF16B29697@microsoft.com...
>>
>> If I understand correctly you are looking for automatic failover but
>> without
>> a second copy of the database. That is exactly how failover clustering
>> works.
>> You can have two servers, everything will be duplicated except the
>> storage
>> and the database. If you lose one server the second one will serve the
>> users,
>> but using the same copy of the database.
>>
>> Hope this helps,
>>
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>>
>>
>> "Charles Law" wrote:
>>
>>> Hi Ben
>>>
>>> Thanks for the quick response.
>>>
>>> What I have read about database mirroring seems to indicate that the
>>> database is copied. However, because we are using a SAN, it seems to me
>>> that
>>> the database copy would be unnecessary as it is only the server box that
>>> has
>>> failed and the storage is intact. Would database mirroring still be
>>> applicable here, and if so is there a special way to set it up so that
>>> it
>>> never actually performs a database mirror?
>>>
>>> Alternatively, am I looking at the wrong technology? Is there another
>>> way
>>> that is more appropriate given our hardware and software levels.
>>>
>>> Cheers.
>>>
>>> Charles
>>>
>>>
>>> "Ben Nevarez" wrote in message
>>> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>>> >
>>> > Charles,
>>> >
>>> > Of the two technologies you mention only database mirroring can
>>> > provide
>>> > automatic failover to a second server (mirror) and of course you can
>>> > use a
>>> > SAN for storage.
>>> >
>>> > Hope this helps,
>>> >
>>> > Ben Nevarez
>>> > Senior Database Administrator
>>> > AIG SunAmerica
>>> >
>>> >
>>> >
>>> > "Charles Law" wrote:
>>> >
>>> >> We have two servers running Windows 2003 Server Standard Edition, SQL
>>> >> Server
>>> >> 2005 Standard Edition and a SAN.
>>> >>
>>> >> How do we set up this configuration to automatically failover from
>>> >> one
>>> >> server to the other in the event that the primary server goes down? I
>>> >> had
>>> >> thought of log shipping or database mirroring, but both seem to imply
>>> >> that
>>> >> the database resides on the servers, whereas ours will be on the SAN.
>>> >>
>>> >> Thanks in advance for any help.
>>> >>
>>> >> Charles
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

[Database Administrator]

Hi Ekrem

Thanks for the detailed explanation. You are right, my original question was
primarily aimed at solving the HA problem, but we do also need DR. We have
begun to address this.

> (if you plan to set more than one active node in your cluster then you'll
> need licenses as much as active nodes in your cluster)

I am intrigued by what you say here. This sounds like I only need one
Enterprise licence if only one server is active at a time; the other could
use Standard Edition. Is that correct? Does that mean that a Windows cluster
can be created with two servers, one running Enterprise and one running
Standard? If so, that would imply that an Enterprise server can
automatically failover to a Standard Edition server.

Thanks again.

Charles


"Ekrem Önsoy" wrote in message
news:88D2A03D-1102-4E32-BF28-E445097038AC@microsoft.com...
> Hello Charles
>
> I read all messages about your question and read all replies that my
> helpful friends gave you.
>
> However, I' d like to add some extra info here.
>
> It seems that you are looking for High Availibility, not Disaster
> Recovery.
>
> When you have High Availibility, you just keep your system up as much as
> possible. However, if you do not have a Disaster Recovery system, then if
> you lose your data, it would be useless if your system up or down. I still
> do not know if you have a DR system implemented but I wanted to stress
> this out here.
>
> Also, there are three categories about DR and HA. These are:
> Hot Standby: Ones that has automatic failover feature (Such as Failover
> Clustering and Database Mirroring with Failover)
> Warm Standby: Ones that has manual failover feature (Such as Log Shipping,
> Database Mirroring - High Performance and Database Mirroring - High
> Protection modes)
> Cold Standby: Ones that has no failover feature at all. You perform all
> processes manually. (Such as Backup\Restore and Attach\Detach)
>
> You say that you data are stored in disks that are attached to your SAN.
> However, those disks also may corrupt. Even you'll set up your disks as
> RAID Arrays, it's a possibility that one of your RAID Arrays may corrupt.
> You know, everything is possible. So, I suggest you think about a DR plan
> if you do not have one yet.
>
> P.S.
> Database Mirroring have 3 modes as listed above and only one of them
> provides Automatic Failover.
>
> If you already have a SAN and it's better to think about a Failover
> Clustering here if your budget let you buy new Enterprise license (if you
> plan to set more than one active node in your cluster then you'll need
> licenses as much as active nodes in your cluster)
>
> --
> Ekrem Önsoy
>
>
>
> "Charles Law" wrote in message
> news:ucl1ILwJIHA.4712@TK2MSFTNGP04.phx.gbl...
>> Yes, that is what I was hoping for. I had considered clustering
>> originally, but the extra cost of two Windows Server 2003 Enterprise
>> licences had put me off. I now think that this might actually be the only
>> way to go.
>>
>> Thanks for your help.
>>
>> Charles
>>
>>
>> "Ben Nevarez" wrote in message
>> news:9F00F9B1-9F86-4AD4-A4B9-35AF16B29697@microsoft.com...
>>>
>>> If I understand correctly you are looking for automatic failover but
>>> without
>>> a second copy of the database. That is exactly how failover clustering
>>> works.
>>> You can have two servers, everything will be duplicated except the
>>> storage
>>> and the database. If you lose one server the second one will serve the
>>> users,
>>> but using the same copy of the database.
>>>
>>> Hope this helps,
>>>
>>> Ben Nevarez
>>> Senior Database Administrator
>>> AIG SunAmerica
>>>
>>>
>>>
>>> "Charles Law" wrote:
>>>
>>>> Hi Ben
>>>>
>>>> Thanks for the quick response.
>>>>
>>>> What I have read about database mirroring seems to indicate that the
>>>> database is copied. However, because we are using a SAN, it seems to me
>>>> that
>>>> the database copy would be unnecessary as it is only the server box
>>>> that has
>>>> failed and the storage is intact. Would database mirroring still be
>>>> applicable here, and if so is there a special way to set it up so that
>>>> it
>>>> never actually performs a database mirror?
>>>>
>>>> Alternatively, am I looking at the wrong technology? Is there another
>>>> way
>>>> that is more appropriate given our hardware and software levels.
>>>>
>>>> Cheers.
>>>>
>>>> Charles
>>>>
>>>>
>>>> "Ben Nevarez" wrote in message
>>>> news:629ED29F-94CC-49B5-B60F-12A751811094@microsoft.com...
>>>> >
>>>> > Charles,
>>>> >
>>>> > Of the two technologies you mention only database mirroring can
>>>> > provide
>>>> > automatic failover to a second server (mirror) and of course you can
>>>> > use a
>>>> > SAN for storage.
>>>> >
>>>> > Hope this helps,
>>>> >
>>>> > Ben Nevarez
>>>> > Senior Database Administrator
>>>> > AIG SunAmerica
>>>> >
>>>> >
>>>> >
>>>> > "Charles Law" wrote:
>>>> >
>>>> >> We have two servers running Windows 2003 Server Standard Edition,
>>>> >> SQL
>>>> >> Server
>>>> >> 2005 Standard Edition and a SAN.
>>>> >>
>>>> >> How do we set up this configuration to automatically failover from
>>>> >> one
>>>> >> server to the other in the event that the primary server goes down?
>>>> >> I had
>>>> >> thought of log shipping or database mirroring, but both seem to
>>>> >> imply
>>>> >> that
>>>> >> the database resides on the servers, whereas ours will be on the
>>>> >> SAN.
>>>> >>
>>>> >> Thanks in advance for any help.
>>>> >>
>>>> >> Charles
>>>> >>
>>>> >>
>>>> >>
>>>>
>>>>
>>>>
>>
>>
>