Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Tuesday, March 27, 2012
Database mirroring setup without downtime
Does anyone know if you can mirror a database while users are banging away o
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Database mirroring setup without downtime
Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
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
>
>
Database Mirgration
Hi,
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>
The database users and roles will migrate, however the SQL Server logins
will not. You will have to recreate your logins (Windows Authenticated
and/or SQL Authenticated) on the new server.
The Windows Authenticated users will automatically match the id's in the
database, the SQL Authenticated users however will not. You will need to
take a look at the sp_change_users_login in BOL to get those SQL
Authenticated users to match up with the newly created SQL Logins.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||When you attach/detach backup/restore a database, the users and database
roles are of course moved across, as they are stored within the database.
But the logins these database users are linked to, will not be moved, as
they are in the master database.
For more info, on how to troubleshoot this issue, see:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
Hi,
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed|||yes! all the users and roles are migrated to a new server.
but, the logins are not migrated
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ed" wrote:
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master databas
e.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>|||Hi,
If you migrate Master database as well then all the Logins, users , Roles
will be migrated to new server. Incase if you are migrating the
Master database then Logins will not get migrated. In that case you could
use Transfer Login Task to do login migration.
Thanks
Hari
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
> database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>sql
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>
The database users and roles will migrate, however the SQL Server logins
will not. You will have to recreate your logins (Windows Authenticated
and/or SQL Authenticated) on the new server.
The Windows Authenticated users will automatically match the id's in the
database, the SQL Authenticated users however will not. You will need to
take a look at the sp_change_users_login in BOL to get those SQL
Authenticated users to match up with the newly created SQL Logins.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||When you attach/detach backup/restore a database, the users and database
roles are of course moved across, as they are stored within the database.
But the logins these database users are linked to, will not be moved, as
they are in the master database.
For more info, on how to troubleshoot this issue, see:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
Hi,
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed|||yes! all the users and roles are migrated to a new server.
but, the logins are not migrated
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ed" wrote:
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master databas
e.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>|||Hi,
If you migrate Master database as well then all the Logins, users , Roles
will be migrated to new server. Incase if you are migrating the
Master database then Logins will not get migrated. In that case you could
use Transfer Login Task to do login migration.
Thanks
Hari
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
> database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>sql
Wednesday, March 7, 2012
Database Mainetance Plan(Backup)
I recieved the follwoing message from the backup job of my database manitean
nce plan:
What does this mean? Can I not make a backup if users are on the system? Wha
t do I change?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'P
G_PROD' as 'PG_PROD\SQLAGENT' (trusted)
Starting maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Database state cannot be changed w
hile other users are using the database 'PAYCENTRAL'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database PAYCENTRAL: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Repair statement not processed. Da
tabase needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Thanks,
ThomasLLThomas
It's not your backup. In your plan under the database integrity check you ha
ve ticked attempt to repair any minor problems. SQL Server is attempting to
set the database to single user mode so it can do this. It is not required,
untick the box. Don't forge
t to check the output for errors and if you get any arrange to run dbcc chec
kdb with applicable repair option at a time that no users are on the system.
Hope this helps
John
nce plan:
What does this mean? Can I not make a backup if users are on the system? Wha
t do I change?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'P
G_PROD' as 'PG_PROD\SQLAGENT' (trusted)
Starting maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Database state cannot be changed w
hile other users are using the database 'PAYCENTRAL'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database PAYCENTRAL: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Repair statement not processed. Da
tabase needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Thanks,
ThomasLLThomas
It's not your backup. In your plan under the database integrity check you ha
ve ticked attempt to repair any minor problems. SQL Server is attempting to
set the database to single user mode so it can do this. It is not required,
untick the box. Don't forge
t to check the output for errors and if you get any arrange to run dbcc chec
kdb with applicable repair option at a time that no users are on the system.
Hope this helps
John
Friday, February 17, 2012
Database login
I have a web based application that could have many clients/users.
Should I create a user account for each client browsing the web site.
They can only access certain data after they have been verified from a
user table. They use a common database login to access the data. This
is planned to be a public free site. So hopefully the client count
will be high.create login that the application uses to get to the DB... instead of
creating a login for each user.|||Ok thats they wey I'm approaching it now.|||Are you using an ODBC or DSN less, either way assuming the users should
have the same permissions then 1 user is created in the db with a certain
level of permissions. You may also have an admin section , therfore the
admin user may have a different set of permissions
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143477924.849156.7120@.z34g2000cwc.googlegroups.com...
> Ok thats they wey I'm approaching it now.
>|||From what you describe, it sounds like you need ASP.NET Forms
Authentication. At a minimum, you will need to implement a table with one
row for each subscriber.
ASP.NET Forms Authentication Overview
http://msdn2.microsoft.com/en-us/library/7t6b43z4.aspx
As for database (server) logins, you will need to implement one login for
each type of user (admin, subscriber, guest, etc.) and then use SQL Server
role based permissions to restrict each user's access to specific database
objects.
SQL Server 2000 Administrator's Pocket Consultant - Chapter 5: SQL Server
2000 Security
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
SQL Server 2000 Operations Guide: Security Administration
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
SQL Server 2000 Resource Kit - Chapter 10: Implementing Security
http://www.microsoft.com/technet/pr...fr=true
If this database is hosted by a 3rd party internet service provider, then
you will need to coordinate with the server administrator to setup the
logins and permissions, becuase these configurations require server admin
rights. If your ISP provides this functionality through their website
management tool, then that's great, but that would be exceptional.
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143473709.083112.37370@.i40g2000cwc.googlegroups.com...
>I have a web based application that could have many clients/users.
> Should I create a user account for each client browsing the web site.
> They can only access certain data after they have been verified from a
> user table. They use a common database login to access the data. This
> is planned to be a public free site. So hopefully the client count
> will be high.
>
Should I create a user account for each client browsing the web site.
They can only access certain data after they have been verified from a
user table. They use a common database login to access the data. This
is planned to be a public free site. So hopefully the client count
will be high.create login that the application uses to get to the DB... instead of
creating a login for each user.|||Ok thats they wey I'm approaching it now.|||Are you using an ODBC or DSN less, either way assuming the users should
have the same permissions then 1 user is created in the db with a certain
level of permissions. You may also have an admin section , therfore the
admin user may have a different set of permissions
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143477924.849156.7120@.z34g2000cwc.googlegroups.com...
> Ok thats they wey I'm approaching it now.
>|||From what you describe, it sounds like you need ASP.NET Forms
Authentication. At a minimum, you will need to implement a table with one
row for each subscriber.
ASP.NET Forms Authentication Overview
http://msdn2.microsoft.com/en-us/library/7t6b43z4.aspx
As for database (server) logins, you will need to implement one login for
each type of user (admin, subscriber, guest, etc.) and then use SQL Server
role based permissions to restrict each user's access to specific database
objects.
SQL Server 2000 Administrator's Pocket Consultant - Chapter 5: SQL Server
2000 Security
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
SQL Server 2000 Operations Guide: Security Administration
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
SQL Server 2000 Resource Kit - Chapter 10: Implementing Security
http://www.microsoft.com/technet/pr...fr=true
If this database is hosted by a 3rd party internet service provider, then
you will need to coordinate with the server administrator to setup the
logins and permissions, becuase these configurations require server admin
rights. If your ISP provides this functionality through their website
management tool, then that's great, but that would be exceptional.
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143473709.083112.37370@.i40g2000cwc.googlegroups.com...
>I have a web based application that could have many clients/users.
> Should I create a user account for each client browsing the web site.
> They can only access certain data after they have been verified from a
> user table. They use a common database login to access the data. This
> is planned to be a public free site. So hopefully the client count
> will be high.
>
Database Locking
I have a TableA and if UserA is reading and modifying some data, then I want
UserB and all the rest of the users must wait until UserA is done modifying
that row of data. How this is done in SQL DATABASE? Please advice. Please
give me an example. ThanksHi,
This is based on the ISOLATION LEVEL. By default sql server is READ
COMMITTED isolation and in this Isolation all the other users will wait
until the
USER A commits the transactions.
Just read the Isolation in books online to get more information on this.
Thanks
Hari
SQL Server MVP
"hngo01" <hngo01@.discussions.microsoft.com> wrote in message
news:38B65B95-AB0B-48E6-BF7B-D516D25D3D71@.microsoft.com...
>I have a TableA and if UserA is reading and modifying some data, then I
>want
> UserB and all the rest of the users must wait until UserA is done
> modifying
> that row of data. How this is done in SQL DATABASE? Please advice.
> Please
> give me an example. Thanks
UserB and all the rest of the users must wait until UserA is done modifying
that row of data. How this is done in SQL DATABASE? Please advice. Please
give me an example. ThanksHi,
This is based on the ISOLATION LEVEL. By default sql server is READ
COMMITTED isolation and in this Isolation all the other users will wait
until the
USER A commits the transactions.
Just read the Isolation in books online to get more information on this.
Thanks
Hari
SQL Server MVP
"hngo01" <hngo01@.discussions.microsoft.com> wrote in message
news:38B65B95-AB0B-48E6-BF7B-D516D25D3D71@.microsoft.com...
>I have a TableA and if UserA is reading and modifying some data, then I
>want
> UserB and all the rest of the users must wait until UserA is done
> modifying
> that row of data. How this is done in SQL DATABASE? Please advice.
> Please
> give me an example. Thanks
Tuesday, February 14, 2012
Database level setting : Allow user input chinese character~
Dear all,
To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?
I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..
Any Idea?
Thanks.
You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.
There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.
There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.
|||Hi,
you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)
SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Database level lock
Actually, the scenario is we have a web application and each week we need to
do data uploads (with etl). During this upload, the users accessing the
website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So will
db locking help in this case as the website can also read the data being a
abc user?
What can be done here?How do you do data upload? DTS,Bulk Insert what?
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:elzYtFttGHA.3568@.TK2MSFTNGP05.phx.gbl...
> Actually, the scenario is we have a web application and each week we need
> to do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So
> will db locking help in this case as the website can also read the data
> being a abc user?
> What can be done here?
>|||Tanmaya Kulkarni wrote:
> Actually, the scenario is we have a web application and each week we need to
> do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So will
> db locking help in this case as the website can also read the data being a
> abc user?
> What can be done here?
>
One option would be to have the web app connect as user 'abc', and the
ETL process as user 'xyz'. When starting the ETL process, revoke access
from user 'abc', and restore it after ETL finishes.
Another option would be to create a new database, do the ETL into that
database, then detach/reattach the "new" database as the "live" database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
do data uploads (with etl). During this upload, the users accessing the
website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So will
db locking help in this case as the website can also read the data being a
abc user?
What can be done here?How do you do data upload? DTS,Bulk Insert what?
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:elzYtFttGHA.3568@.TK2MSFTNGP05.phx.gbl...
> Actually, the scenario is we have a web application and each week we need
> to do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So
> will db locking help in this case as the website can also read the data
> being a abc user?
> What can be done here?
>|||Tanmaya Kulkarni wrote:
> Actually, the scenario is we have a web application and each week we need to
> do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So will
> db locking help in this case as the website can also read the data being a
> abc user?
> What can be done here?
>
One option would be to have the web app connect as user 'abc', and the
ETL process as user 'xyz'. When starting the ETL process, revoke access
from user 'abc', and restore it after ETL finishes.
Another option would be to create a new database, do the ETL into that
database, then detach/reattach the "new" database as the "live" database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Database last accessed
Hello All,
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Database last accessed
Hello All,
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
that database or not. Isn't there a systable/sysdatabase where this
information is recorded?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||are you saying where the data collected by profiler is stored? When you run
profiler you can tell it where to save the data, or you can run and save
later.
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:uvPkRRs2DHA.3416@.tk2msftngp13.phx.gbl...
for a few days to see if anyone had connected to a particular database?
What I am trying to find is a way to figure out when was the last time
someone had accessed this database. It could have been couple of months
ago that someone may have accessed it. It could have been last week,
and they may not access it again for another week. As far as the
profiler is concerned, I'll have to keep it running for a couple of
weeks to see if anyone logged on to a particular database while the
profiler was running. I hope this clarifies what I am trying to do.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
quote:|||I'll have to run profiler for quite some time to see if anyone is using
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
that database or not. Isn't there a systable/sysdatabase where this
information is recorded?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||are you saying where the data collected by profiler is stored? When you run
profiler you can tell it where to save the data, or you can run and save
later.
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:uvPkRRs2DHA.3416@.tk2msftngp13.phx.gbl...
quote:|||No, what I am saying is that won't I have to keep the profiler running
> I'll have to run profiler for quite some time to see if anyone is using
> that database or not. Isn't there a systable/sysdatabase where this
> information is recorded?
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
for a few days to see if anyone had connected to a particular database?
What I am trying to find is a way to figure out when was the last time
someone had accessed this database. It could have been couple of months
ago that someone may have accessed it. It could have been last week,
and they may not access it again for another week. As far as the
profiler is concerned, I'll have to keep it running for a couple of
weeks to see if anyone logged on to a particular database while the
profiler was running. I hope this clarifies what I am trying to do.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)