what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.
Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
sql
Showing posts with label shipping. Show all posts
Showing posts with label shipping. Show all posts
Tuesday, March 27, 2012
database mirroring vs log shipping
what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms o
f
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms o
f
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
database mirroring vs log shipping
what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
Sunday, March 25, 2012
Database Mirroring and Log Shipping
Hello All Database Mirroring Experts,
I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:
"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring
session is configured with an additional server instance known as the witness.
If the principal database is lost for any reason after the database is
synchronized and if the mirror server and witness can still communicate
with each other, automatic failover occurs. An automatic failover
causes mirror server to assume the principal role and bring its
database online as the principal database. For more information, see Automatic Failover
[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log
shipping backup location is accessible to the new principal/primary
server, its backup jobs begin to ship log backups to that location. The
database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored.
The secondary servers continue to copy log backups without knowing that
a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx
Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored?
Let me elaborate the situation (if anything I said is incorrect, please correct me 
Here is the time line of the failover happens:
- tn-1 - tn - tf -- tn+1 > t
-> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.
Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.
Thanks a lot,
Terence
Hi Terence,
The key point to consider in the above scenario is the fact that the principal and mirror which participate in the mirroring relationship collaborate to similulate a single database which has had no failure. Starting from that observation, if there is a failover to the mirror, the next log backup will still capture all the transactions that have not yet been backed up on the principal, preserving the log chain. In your example above, the next transaction log backup from the mirror will contain all the log records past point tn.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||Hi Matt,Thanks a lot for your information.
It helps me a lot to understand Database Mirroring and Log Shipping. : )
Regards,
Terence
Database Mirroring and Log Shipping
Hello All Database Mirroring Experts,
I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:
"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring
session is configured with an additional server instance known as the witness.
If the principal database is lost for any reason after the database is
synchronized and if the mirror server and witness can still communicate
with each other, automatic failover occurs. An automatic failover
causes mirror server to assume the principal role and bring its
database online as the principal database. For more information, see Automatic Failover
[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log
shipping backup location is accessible to the new principal/primary
server, its backup jobs begin to ship log backups to that location. The
database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored.
The secondary servers continue to copy log backups without knowing that
a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx
Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored?
Let me elaborate the situation (if anything I said is incorrect, please correct me
Here is the time line of the failover happens:
- tn-1 - tn - tf -- tn+1 > t
-> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.
Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.
Thanks a lot,
Terence
Hi Terence,
The key point to consider in the above scenario is the fact that the principal and mirror which participate in the mirroring relationship collaborate to similulate a single database which has had no failure. Starting from that observation, if there is a failover to the mirror, the next log backup will still capture all the transactions that have not yet been backed up on the principal, preserving the log chain. In your example above, the next transaction log backup from the mirror will contain all the log records past point tn.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||Hi Matt,Thanks a lot for your information.
It helps me a lot to understand Database Mirroring and Log Shipping. : )
Regards,
Terence
Wednesday, March 21, 2012
Database Mirror or Log Shipping?
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun
You cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun
|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun
You cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun
|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
Database Mirror or Log Shipping?
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
LijunYou cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
LijunYou cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
Database Mirror or Log Shipping?
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
LijunYou cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and puttin
g
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed yo
u
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A grou
p
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
LijunYou cannot use a DB that is the target of log shipping. However, you can
create a DB snapshot on a DB mirror and report off of that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
We are going to setup a new secondary server for reporting purpose. A group
of users will use the secondary database for data modeling, analysis, etc.
We have already setup two log shipping secondary servers for the primary,
one is over WAN. The logs are backup and restore on every 15 minutes. For
the past several years, log shipping has been working fine.
I would like to hear some opinion about database mirror, especially from
those who are using database mirror now. Is there any complication after
use database mirror?
Thanks!
Lijun|||Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and puttin
g
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed yo
u
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A grou
p
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>|||There are no issues with the mirror being updated by the primary while there
is a snap shot. Indeed, this is a means of using the primary as a reporting
server, thus off-loading work from the primary.
I don't believe that you can log ship to a destination and mirror off of
that.
You can take a belt and suspenders approach and use mirroring, plus log ship
to another destination. As long as you keep all logs and not the time of
the corruption, you can stop the restores just prior to the corruption.
Also, take snapshots frequently, you can revert to a snapshot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sql411@.nospam.com" <sql411nospamcom@.discussions.microsoft.com> wrote in
message news:C9685BFA-7131-40F7-AB41-2D23E1FFF592@.microsoft.com...
Are there any issues with the mirror staying updated while there is a
snapshot of it?
I seem to remember something like this, but I think it was more an issue
with log shipping. For example, logshipping to another server -- and
putting
a snapshot on the destination -- causes problems with log shipping restores
being able to be applied. probably not an issue with mirroring but i
couldn't remember.
If you would ever failover to the reporting server if the live OLTP went
down, remember that if it is a corruption -- the mirrored box might become
corrupted as well -- but with a log shipping box that is slightly delayed
you
might be able to stop the corrupted log from being applied if you catch it
ahead of time.
Robert
"Tom Moreau" wrote:
> You cannot use a DB that is the target of log shipping. However, you can
> create a DB snapshot on a DB mirror and report off of that.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
> news:O%23Zoc8NiHHA.4300@.TK2MSFTNGP05.phx.gbl...
> We are going to setup a new secondary server for reporting purpose. A
> group
> of users will use the secondary database for data modeling, analysis, etc.
> We have already setup two log shipping secondary servers for the primary,
> one is over WAN. The logs are backup and restore on every 15 minutes. For
> the past several years, log shipping has been working fine.
> I would like to hear some opinion about database mirror, especially from
> those who are using database mirror now. Is there any complication after
> use database mirror?
> Thanks!
> Lijun
>
>
Wednesday, March 7, 2012
Database Maintenance Plan
Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.Its common to bulge Tlog when db maintenance plan perform DB optimization process and its better to pause Log shipping during this operation and take full backup once Maint.plan finishes the task and apply to standby server, then re-enable LS process.|||Thanks.
Is there a way to automate the pause of the TLog backup? I hate to be up at 3 in the morning to disable/enable the jobs manually.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.Its common to bulge Tlog when db maintenance plan perform DB optimization process and its better to pause Log shipping during this operation and take full backup once Maint.plan finishes the task and apply to standby server, then re-enable LS process.|||Thanks.
Is there a way to automate the pause of the TLog backup? I hate to be up at 3 in the morning to disable/enable the jobs manually.
Subscribe to:
Posts (Atom)