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
>
>
Showing posts with label purpose. Show all posts
Showing posts with label purpose. Show all posts
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!
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
>
>
Friday, February 24, 2012
Database mail does not work from Sql Server Agent Job after upgrade to SP1
Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.
Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.
Message in Error Log: [264] An attempt was made to send an email when no email session has been established.
Send test Email does work on both Servers.
Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?
I had the exact same problem with the same error on 2005. To fix it, I did the following:
1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).
2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".
|||I am still having issues after working through all these steps. Any other suggestions.
In general I seem to be having a lot of issues with the 64 Bit on 2005.
|||I'm having the exact same issue.
I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:
"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"
Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.
|||I just started working on the mail on my test server and I have the same problem.
I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.
|||Ok I found another test I could run to see if I have it configured correctly.
EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'User@.Place.com',
@.subject=N'just another mail test.',
@.body=N'just another mail test.',
@.profile_name = 'EMail';
When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.
|||Ok it looks like I might have solve it for my self. Follow this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1
Database mail does not work from Sql Server Agent Job after upgrade to SP1
Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.
Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.
Message in Error Log: [264] An attempt was made to send an email when no email session has been established.
Send test Email does work on both Servers.
Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?
I had the exact same problem with the same error on 2005. To fix it, I did the following:
1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).
2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".
|||I am still having issues after working through all these steps. Any other suggestions.
In general I seem to be having a lot of issues with the 64 Bit on 2005.
|||
I'm having the exact same issue.
I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:
"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"
Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.
|||I just started working on the mail on my test server and I have the same problem.
I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.
|||Ok I found another test I could run to see if I have it configured correctly.
EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'User@.Place.com',
@.subject=N'just another mail test.',
@.body=N'just another mail test.',
@.profile_name = 'EMail';
When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.
|||Ok it looks like I might have solve it for my self. Follow this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1
Subscribe to:
Posts (Atom)