Dear Professional,

I would really appreciate if anyone can help me out.

Currently I have database mirroring but I am using Asynchronous mode and
using manual failover strategy but going forward I have to implement
following strategy.

1. Automate failover

Question: if I go with automate failover that means I have to configure
separate Witness Server and application will point to Witness Server right?
and witness will responsible for redirecting to active server right?

2. Disaster Recovery:

Question: For disaster recovery I have to implement Log shipping offsite but
the problem is if I go with Automate failover then I can't implement Log
shipping for DR, can you tell me any alternate solution that provide both
solution: Automate Failover as well as DR offsite.

Thanks in advance.

You are using a high performance mode (asynchronously) of database mirroring.
The witness serves serve no purpose in high performance mode—only the
principal and mirror are needed. In fact, adding the witness to a high
performance mode configuration will add risk since its addition to this setup
will enforce quorum, which requires two or more SQL Server instances. I would
not use a witness in this scenario, as it provides no value. If quorum is
lost, there are two scenarios: when the principal is down, you will only be
able to manually force the mirror to become the new primary if it can connect
to the witness; and when the mirror is down, the principal will be taken
offline if it cannot connect to the witness. Therefore, it is recommended
that if you plan on implementing high performance mode, you do not configure
a witness.

You can use High Safety Mode (synchronously) for automatic failover.
You need to code your application to specifically know about database
mirroring to be able to handle the primary and mirror. First, you will need
the version of MDAC (or later) that is compatible with SQL Server 2005 as
well as either SQL Server Native Client or ADO.NET. If you are coding your
own application, use the SqlConnection object’s ConnectionString. Also, the
connection string has to contain not only the primary but the mirror. The
connection string also assumes the same database name on each partner. The
following is a sample connection string:

"Server=My_Instance; Failover
Partner=fo_my_Instance;Database=myDB;Network=dbmss ocn"


As per your 2nd question, you can use SQL server clustering and log shipping
together to achieve automatic failover and disaster recovery at one time. The
only new thing you have to do is to configure file share for log shipping as
a resource in windows cluster service.


Manpreet Singh
http://crazysql.wordpress.com/
MCITP –DBA, DD - SQL server 2005
MCTS – SQL server 2005
MCTS –SharePoint Server 2007


"Noor" wrote:

Thanks Manpreet.

As you mentioned SQL server clustering and log shipping
together to achieve automatic failover and disaster recovery at one time.
Can I use Database Mirroring and Log Shipping together to achieve automatic
failover and disaster recovery at one time?

Thanks

"Manpreet Singh" wrote:

No, database mirroring and log shipping both works on transaction logs.
Which, means that you can use only one method at a time. Clustering works on
OS level so u can use clustering and log shipping together.

Manpreet Singh
http://crazysql.wordpress.com/
MCITP –DBA, DD - SQL server 2005
MCTS – SQL server 2005
MCTS –SharePoint Server 2007


"Rogers" wrote:

Are you sure about that? Note that even though they both works on
transaction logs, log shipping works on the transaction log backups, which
are independent from database mirroring.

Linchi

"Manpreet Singh" wrote:

Why would you want to do that regardless whether it can or cannot be done? I
can see reasons for doing both if they are for different destinations. But
you seem to talking about the same destination.

Note that failover clustering protects you from failed host, whereas
database mirroring can also protect you from failed storage. Because they
protect against different kinds of disasters, it may make sense to use both.

Linchi

"Rogers" wrote:

Linchi Shea is right. Maybe I get confused somewhere. Its perfectly safe to
take transaction log backup in database mirroring scenario. which means that
you can use log shipping with database mirroring.
..
Manpreet Singh
http://crazysql.wordpress.com/
MCITP –DBA, DD - SQL server 2005
MCTS – SQL server 2005
MCTS –SharePoint Server 2007


"Linchi Shea" wrote:

Hello Manpreet & Linchi Shea

Thanks for your help, I would like to add couple of points over here. I have
implemented databse mirroring plus log shipping but the problem is if my
Primary Server goes down the log shipping also stop working. So the
requirement is in database mirroring if primary server goes down the log
shipping shouldn't break that means log shipping should continue shipping
the log from secondary server but I think it's not possible in database
mirroring, pelase advice.

I would really appreciate if you can help us how I can implement SQL Server
clustering (primary data center) and Database Mirroring on the DR site and
it's possible right?

Please advice what you guys think.. my requirement is Auto Failover on
Primary Data Center and Disaster Recovery is on the other Data Center.

Really appreciate of your help

Thanks

"Manpreet Singh" <ManpreetSingh (AT) discussions (DOT) microsoft.com> wrote

Hello Manpreet & Linchi Shea

Thanks for your help, I would like to add couple of points over here. I have
implemented databse mirroring plus log shipping but the problem is if my
Primary Server goes down the log shipping also stop working. So the
requirement is in database mirroring if primary server goes down the log
shipping shouldn't break that means log shipping should continue shipping
the log from secondary server but I think it's not possible in database
mirroring, pelase advice.

I would really appreciate if you can help us how I can implement SQL Server
clustering (primary data center) and Database Mirroring on the DR site and
it's possible right?

Please advice what you guys think.. my requirement is Auto Failover on
Primary Data Center and Disaster Recovery is on the other Data Center.

Really appreciate of your help

Thanks

"Manpreet Singh" <ManpreetSingh (AT) discussions (DOT) microsoft.com> wrote


"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote