Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.
Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>
|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.
|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Showing posts with label secondary. Show all posts
Showing posts with label secondary. Show all posts
Sunday, March 25, 2012
Database Mirroring Problems - Can't drop database
Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Database Mirroring Problems - Can't drop database
Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
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
>
>
Subscribe to:
Posts (Atom)