Auto Failover (Primary Data Center) + DR (Secondar Data Center)
Noor
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" <Manpre...@discussions.microsoft.com> wrote in message
news:35230A55-908A-4466...@microsoft.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=dbmssocn"
>> > > >
>> > > >
>> > > > 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.
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
Linchi Shea
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