Auto Failover (Primary Data Center) + DR (Secondar Data Center)

#1

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

Quote:

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:

No, database mirroring and log shipping both works on transaction logs.
Which, means that you can use only one method at a time.

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:

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:

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:

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:

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.

#2

I have never tried whether you can make log sipping to continue working after
a database mirroring failover. There was never such a requirement. But you
could easily find out. The very first thing to find out is whether you can
continue to restore from the log backups after the mirroring failover.

Try this to find if the log backup chain is broken by the database mirroring
failover:

1. Backup the database on ServerA (db.bak)
2. Backup the database log on ServerA (db_log1.bak)
3. Execute a manual failover from ServerA to ServerB.
4. Backup the database log on ServerB (db_log2.bak, not sure if this will
work though. And if this works, your chance is good.)
5. On the log shiiping secondary server, restore from db.bak with
norecovery, restore from db_log1.bak with norecovery, and restore from
db_log2.bak.

Someone else who has already tried tried may be able to chime in.

Now, if the transaction log backup chain is not broken, then you may have to
use a custom log shipping script to deal with the database mirroring
failover. I could be wrong, but I doubt the built-in log shipping setup will
handle this out of box.

Linchi

"Noor" wrote:

Quote:

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 in message
news:35230A55-908A-4466-98DF-38F788F64889 (AT) microsoft (DOT) com...

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:

No, database mirroring and log shipping both works on transaction logs.
Which, means that you can use only one method at a time.

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:

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:

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:

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:

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Auto Failover (Primary Data Center) + DR (Secondar Data Center)

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Auto Failover (Primary Data Center) + DR (Secondar Data Center) - 08-16-2009 , 08:54 PM

RE: Auto Failover (Primary Data Center) + DR (Secondar Data Center) - 08-17-2009 , 09:04 AM