I am trying to use my Standby Server as a Reporting Server so that our
resource intensive reporting queries can be run against it instead of
production.
I've been successful at automating the backups and restores of all the
databases as well as transaction logs from production to standby. However,
if someone is trying to do a select query at the time, or even just have
their reporting application connected to it, the restore of the transaction
log fails "due to not being able to gain exclusive access". If one of the
transaction log restores fail, they all fail after that because the LSNs are
off.
This is, of course, an urgent problem. Management has required that this
server be as current as possible for reporting purposes and this entire
process must be automated.
Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
Below is an example of my syntax for the transaction log restores. Works
like a charm unless something is connected to the database. I had to do it
this way because I wanted to save all the transaction log files taken
throughout the day in case of "point in time" disaster recovery. I ended up
opening a ticket with Microsoft and this is the best solution they could
suggest. They said that normally with log shipping, the TLog backups
overwrite the others in the device. I found it difficult to believe that it
was expected that every process would work correctly every time and you
would have no need to ever access a transaction log backup that was
previously taken. If it has been overwritten, and you try to restore one
that is later, SQL send the error about LSNs not being right.
Is there something in here that needs to be changed to accomplish my task?
CREATE proc RestoreApplicationLogs_TLog
as
declare @.position int
declare @.mediasetid int
set @.mediasetid = (select media_set_id
from NCNSV1015.msdb.dbo.backupmediafamily
where logical_device_name = 'ApplicationLogs_TLog')
set @.position= (select position from NCNSV1015.msdb.dbo.backupset
where Backup_set_id = (select max(backup_set_id) from
NCNSV1015.msdb.dbo.backupset where media_set_id = @.mediasetid))
Restore Log ApplicationLogs
from disk = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak'
with file = @.position,
dbo_only,
standby = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf'
waitfor delay '00:00:10'
GO
Please help.
Thank you in advance,
DeborahLog shipping is not the best way to create a reporting
server, especially with frequent transaction log loads.
To apply a transaction log, SQL Server requires that no
connections are in the database; it needs exclusive access.
You'll have to manually kick out the users for the restore
if you want to use it as a reporting server.
My recommendation is to always use another method for
reporting.
That said, the built in feature of SQL 2K EE log shipping
will apply your tran logs in order, even if it is in
standby; you will just create more latency if people are
in it. I've coded a version that does the same thing.
>--Original Message--
>I am trying to use my Standby Server as a Reporting
Server so that our
>resource intensive reporting queries can be run against
it instead of
>production.
>I've been successful at automating the backups and
restores of all the
>databases as well as transaction logs from production to
standby. However,
>if someone is trying to do a select query at the time, or
even just have
>their reporting application connected to it, the restore
of the transaction
>log fails "due to not being able to gain exclusive
access". If one of the
>transaction log restores fail, they all fail after that
because the LSNs are
>off.
>This is, of course, an urgent problem. Management has
required that this
>server be as current as possible for reporting purposes
and this entire
>process must be automated.
>Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
>Below is an example of my syntax for the transaction log
restores. Works
>like a charm unless something is connected to the
database. I had to do it
>this way because I wanted to save all the transaction log
files taken
>throughout the day in case of "point in time" disaster
recovery. I ended up
>opening a ticket with Microsoft and this is the best
solution they could
>suggest. They said that normally with log shipping, the
TLog backups
>overwrite the others in the device. I found it difficult
to believe that it
>was expected that every process would work correctly
every time and you
>would have no need to ever access a transaction log
backup that was
>previously taken. If it has been overwritten, and you
try to restore one
>that is later, SQL send the error about LSNs not being
right.
> Is there something in here that needs to be changed to
accomplish my task?
>CREATE proc RestoreApplicationLogs_TLog
>as
>declare @.position int
>declare @.mediasetid int
>set @.mediasetid = (select media_set_id
>from NCNSV1015.msdb.dbo.backupmediafamily
>where logical_device_name = 'ApplicationLogs_TLog')
>set @.position= (select position from
NCNSV1015.msdb.dbo.backupset
>where Backup_set_id = (select max(backup_set_id) from
>NCNSV1015.msdb.dbo.backupset where media_set_id =@.mediasetid))
>Restore Log ApplicationLogs
>from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
'
>with file = @.position,
>dbo_only,
>standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
'
>waitfor delay '00:00:10'
>GO
>
>Please help.
>Thank you in advance,
>Deborah
>
>.
>|||You need to be using something like replication to meet
your stated requirements. In order for a db to be restored
nobody can be accessing it. Log shipping has an option to
allow users into the db while its not being restored and
it will kick everony out when the restore is starting, but
this isnt usually a good idea for a reporting server.
>--Original Message--
>I am trying to use my Standby Server as a Reporting
Server so that our
>resource intensive reporting queries can be run against
it instead of
>production.
>I've been successful at automating the backups and
restores of all the
>databases as well as transaction logs from production to
standby. However,
>if someone is trying to do a select query at the time, or
even just have
>their reporting application connected to it, the restore
of the transaction
>log fails "due to not being able to gain exclusive
access". If one of the
>transaction log restores fail, they all fail after that
because the LSNs are
>off.
>This is, of course, an urgent problem. Management has
required that this
>server be as current as possible for reporting purposes
and this entire
>process must be automated.
>Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
>Below is an example of my syntax for the transaction log
restores. Works
>like a charm unless something is connected to the
database. I had to do it
>this way because I wanted to save all the transaction log
files taken
>throughout the day in case of "point in time" disaster
recovery. I ended up
>opening a ticket with Microsoft and this is the best
solution they could
>suggest. They said that normally with log shipping, the
TLog backups
>overwrite the others in the device. I found it difficult
to believe that it
>was expected that every process would work correctly
every time and you
>would have no need to ever access a transaction log
backup that was
>previously taken. If it has been overwritten, and you
try to restore one
>that is later, SQL send the error about LSNs not being
right.
> Is there something in here that needs to be changed to
accomplish my task?
>CREATE proc RestoreApplicationLogs_TLog
>as
>declare @.position int
>declare @.mediasetid int
>set @.mediasetid = (select media_set_id
>from NCNSV1015.msdb.dbo.backupmediafamily
>where logical_device_name = 'ApplicationLogs_TLog')
>set @.position= (select position from
NCNSV1015.msdb.dbo.backupset
>where Backup_set_id = (select max(backup_set_id) from
>NCNSV1015.msdb.dbo.backupset where media_set_id =@.mediasetid))
>Restore Log ApplicationLogs
>from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
'
>with file = @.position,
>dbo_only,
>standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
'
>waitfor delay '00:00:10'
>GO
>
>Please help.
>Thank you in advance,
>Deborah
>
>.
>|||I'm sure that the log shipping tool in the Enterprise Edition would work
great, but I do not have Enterprise Edition, nor will we be upgrading to it.
Our environment simply cannot warrant that.
Since the log restores literally take less than a couple of seconds, is
there a way to kick the users out and automatically reconnect them? So that
from their perspective the query just took a couple more seconds to run?
I have not tried to implement replication in SQL2K, and I am concerned about
possible maintenance mistakes. I've been reading up on it in BOL, but I'm
now in a time crunch and don't want to generate any new problems.
Is transactional replication a straight-forward thing to implement? Are
there any issues with adding that to the plan that I need to be aware of?
If I were to implement transactional replication, how would that change the
backup/restore strategy, other than I wouldn't have to restore the
transaction logs every 10 minutes? Can the process be completely automated?
Sorry to be asking so many questions. I have been reading BOL, but reading
the BOL, while usually helpful, is no substitute for talking to someone with
real experience.
Thanks in advance,
Deborah
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> You need to be using something like replication to meet
> your stated requirements. In order for a db to be restored
> nobody can be accessing it. Log shipping has an option to
> allow users into the db while its not being restored and
> it will kick everony out when the restore is starting, but
> this isnt usually a good idea for a reporting server.
>
> >--Original Message--
> >I am trying to use my Standby Server as a Reporting
> Server so that our
> >resource intensive reporting queries can be run against
> it instead of
> >production.
> >
> >I've been successful at automating the backups and
> restores of all the
> >databases as well as transaction logs from production to
> standby. However,
> >if someone is trying to do a select query at the time, or
> even just have
> >their reporting application connected to it, the restore
> of the transaction
> >log fails "due to not being able to gain exclusive
> access". If one of the
> >transaction log restores fail, they all fail after that
> because the LSNs are
> >off.
> >
> >This is, of course, an urgent problem. Management has
> required that this
> >server be as current as possible for reporting purposes
> and this entire
> >process must be automated.
> >
> >Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
> >
> >Below is an example of my syntax for the transaction log
> restores. Works
> >like a charm unless something is connected to the
> database. I had to do it
> >this way because I wanted to save all the transaction log
> files taken
> >throughout the day in case of "point in time" disaster
> recovery. I ended up
> >opening a ticket with Microsoft and this is the best
> solution they could
> >suggest. They said that normally with log shipping, the
> TLog backups
> >overwrite the others in the device. I found it difficult
> to believe that it
> >was expected that every process would work correctly
> every time and you
> >would have no need to ever access a transaction log
> backup that was
> >previously taken. If it has been overwritten, and you
> try to restore one
> >that is later, SQL send the error about LSNs not being
> right.
> >
> > Is there something in here that needs to be changed to
> accomplish my task?
> >
> >CREATE proc RestoreApplicationLogs_TLog
> >as
> >declare @.position int
> >declare @.mediasetid int
> >
> >set @.mediasetid = (select media_set_id
> >from NCNSV1015.msdb.dbo.backupmediafamily
> >where logical_device_name = 'ApplicationLogs_TLog')
> >
> >set @.position= (select position from
> NCNSV1015.msdb.dbo.backupset
> >where Backup_set_id = (select max(backup_set_id) from
> >NCNSV1015.msdb.dbo.backupset where media_set_id => @.mediasetid))
> >
> >Restore Log ApplicationLogs
> >from disk
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> '
> >with file = @.position,
> >dbo_only,
> >standby
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> '
> >
> >waitfor delay '00:00:10'
> >GO
> >
> >
> >Please help.
> >
> >Thank you in advance,
> >Deborah
> >
> >
> >.
> >|||You might want to check out the log shipping scripts that
come with the book SQL 2K High Availability; they are
pretty close to EE-type functionality (i.e. you will still
get them applied in the right order) and you can customize
them to terminate the users with the ALTER DATABASE dbname
SET SINGLE_USER before applying the tran log and ALTER
DATABASE dbname SET MULTI_USER after. Check BOL for the
exact syntax.
CHapter 7 is dedicated to log shipping.
>--Original Message--
>I'm sure that the log shipping tool in the Enterprise
Edition would work
>great, but I do not have Enterprise Edition, nor will we
be upgrading to it.
>Our environment simply cannot warrant that.
>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
>I have not tried to implement replication in SQL2K, and I
am concerned about
>possible maintenance mistakes. I've been reading up on
it in BOL, but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
>Sorry to be asking so many questions. I have been
reading BOL, but reading
>the BOL, while usually helpful, is no substitute for
talking to someone with
>real experience.
>Thanks in advance,
>Deborah
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> You need to be using something like replication to meet
>> your stated requirements. In order for a db to be
restored
>> nobody can be accessing it. Log shipping has an option
to
>> allow users into the db while its not being restored and
>> it will kick everony out when the restore is starting,
but
>> this isnt usually a good idea for a reporting server.
>>
>> >--Original Message--
>> >I am trying to use my Standby Server as a Reporting
>> Server so that our
>> >resource intensive reporting queries can be run against
>> it instead of
>> >production.
>> >
>> >I've been successful at automating the backups and
>> restores of all the
>> >databases as well as transaction logs from production
to
>> standby. However,
>> >if someone is trying to do a select query at the time,
or
>> even just have
>> >their reporting application connected to it, the
restore
>> of the transaction
>> >log fails "due to not being able to gain exclusive
>> access". If one of the
>> >transaction log restores fail, they all fail after that
>> because the LSNs are
>> >off.
>> >
>> >This is, of course, an urgent problem. Management has
>> required that this
>> >server be as current as possible for reporting purposes
>> and this entire
>> >process must be automated.
>> >
>> >Servers are W2K Server sp4, SQL2K Standard Edition
sp3a.
>> >
>> >Below is an example of my syntax for the transaction
log
>> restores. Works
>> >like a charm unless something is connected to the
>> database. I had to do it
>> >this way because I wanted to save all the transaction
log
>> files taken
>> >throughout the day in case of "point in time" disaster
>> recovery. I ended up
>> >opening a ticket with Microsoft and this is the best
>> solution they could
>> >suggest. They said that normally with log shipping,
the
>> TLog backups
>> >overwrite the others in the device. I found it
difficult
>> to believe that it
>> >was expected that every process would work correctly
>> every time and you
>> >would have no need to ever access a transaction log
>> backup that was
>> >previously taken. If it has been overwritten, and you
>> try to restore one
>> >that is later, SQL send the error about LSNs not being
>> right.
>> >
>> > Is there something in here that needs to be changed to
>> accomplish my task?
>> >
>> >CREATE proc RestoreApplicationLogs_TLog
>> >as
>> >declare @.position int
>> >declare @.mediasetid int
>> >
>> >set @.mediasetid = (select media_set_id
>> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >where logical_device_name = 'ApplicationLogs_TLog')
>> >
>> >set @.position= (select position from
>> NCNSV1015.msdb.dbo.backupset
>> >where Backup_set_id = (select max(backup_set_id) from
>> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> @.mediasetid))
>> >
>> >Restore Log ApplicationLogs
>> >from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> '
>> >with file = @.position,
>> >dbo_only,
>> >standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> '
>> >
>> >waitfor delay '00:00:10'
>> >GO
>> >
>> >
>> >Please help.
>> >
>> >Thank you in advance,
>> >Deborah
>> >
>> >
>> >.
>> >
>
>.
>|||>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
Not that I know of. Someone else?
>but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
Replication from my experience had somewhat of a steep
learning curve. It really depends on your circumstances
though. Security/ firewalls/ authentication/ disater
recovery scenarios/ size of initial snapshot/ transferring
of logins and jobs/ alloted maintenance time/ the
infamous "schema changes to a published table"/ and lets
not forget the all time favorite "identity colmuns in
replication"/ ect. If you are on that much of a time
crunch this may not be your best approach.
Out of curiosity, how did you suddenly wind up in this
truely bogus situation?
>--Original Message--
>I'm sure that the log shipping tool in the Enterprise
Edition would work
>great, but I do not have Enterprise Edition, nor will we
be upgrading to it.
>Our environment simply cannot warrant that.
>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
>I have not tried to implement replication in SQL2K, and I
am concerned about
>possible maintenance mistakes. I've been reading up on
it in BOL, but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
>Sorry to be asking so many questions. I have been
reading BOL, but reading
>the BOL, while usually helpful, is no substitute for
talking to someone with
>real experience.
>Thanks in advance,
>Deborah
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> You need to be using something like replication to meet
>> your stated requirements. In order for a db to be
restored
>> nobody can be accessing it. Log shipping has an option
to
>> allow users into the db while its not being restored and
>> it will kick everony out when the restore is starting,
but
>> this isnt usually a good idea for a reporting server.
>>
>> >--Original Message--
>> >I am trying to use my Standby Server as a Reporting
>> Server so that our
>> >resource intensive reporting queries can be run against
>> it instead of
>> >production.
>> >
>> >I've been successful at automating the backups and
>> restores of all the
>> >databases as well as transaction logs from production
to
>> standby. However,
>> >if someone is trying to do a select query at the time,
or
>> even just have
>> >their reporting application connected to it, the
restore
>> of the transaction
>> >log fails "due to not being able to gain exclusive
>> access". If one of the
>> >transaction log restores fail, they all fail after that
>> because the LSNs are
>> >off.
>> >
>> >This is, of course, an urgent problem. Management has
>> required that this
>> >server be as current as possible for reporting purposes
>> and this entire
>> >process must be automated.
>> >
>> >Servers are W2K Server sp4, SQL2K Standard Edition
sp3a.
>> >
>> >Below is an example of my syntax for the transaction
log
>> restores. Works
>> >like a charm unless something is connected to the
>> database. I had to do it
>> >this way because I wanted to save all the transaction
log
>> files taken
>> >throughout the day in case of "point in time" disaster
>> recovery. I ended up
>> >opening a ticket with Microsoft and this is the best
>> solution they could
>> >suggest. They said that normally with log shipping,
the
>> TLog backups
>> >overwrite the others in the device. I found it
difficult
>> to believe that it
>> >was expected that every process would work correctly
>> every time and you
>> >would have no need to ever access a transaction log
>> backup that was
>> >previously taken. If it has been overwritten, and you
>> try to restore one
>> >that is later, SQL send the error about LSNs not being
>> right.
>> >
>> > Is there something in here that needs to be changed to
>> accomplish my task?
>> >
>> >CREATE proc RestoreApplicationLogs_TLog
>> >as
>> >declare @.position int
>> >declare @.mediasetid int
>> >
>> >set @.mediasetid = (select media_set_id
>> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >where logical_device_name = 'ApplicationLogs_TLog')
>> >
>> >set @.position= (select position from
>> NCNSV1015.msdb.dbo.backupset
>> >where Backup_set_id = (select max(backup_set_id) from
>> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> @.mediasetid))
>> >
>> >Restore Log ApplicationLogs
>> >from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> '
>> >with file = @.position,
>> >dbo_only,
>> >standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> '
>> >
>> >waitfor delay '00:00:10'
>> >GO
>> >
>> >
>> >Please help.
>> >
>> >Thank you in advance,
>> >Deborah
>> >
>> >
>> >.
>> >
>
>.
>|||Well, clearly I have run into this bogus situation due to not considering
the fact the I couldn't do log restores while someone is connected to the
database. I mean, I knew that database restores required exclusive access,
but somehow I thought that a read-only database would allow transaction log
restores while others are connected to the database. This would be a major
oops on my part. I had originally suggested to management that the data be
synchronized only once or twice a day, but they firmly want a solution that
allows them to off-load some of the decision support queries/reports that
they use to be retrieved from this "standby" server, in order to reduce the
resource load on production. This has always been touted as an advantage to
having a standby server.
It has taken weeks to iron out the bugs that have popped up in automating
the backup and restore of 36 databases. Everything from permissions issues
to invalid syntax to just plain old timing problems. Now that has been
running successfully all week and they are ready to point these apps to that
server. I was testing that and began to see my restore log jobs failing, of
course.
So, at this point, I don't know quite what I'm going to have to do in order
to get the desired results. Disappointing is not quite the word I would use
to describe the situation if I have to tell them that I now have to take
some considerable time to create and test a replication scenario. But, if
it is the ONLY way, then I guess that's just what I'll have to do.
I am mentally exhausted and in desperate need of a workable solution. Its
been at least 2 years since I've used any type of replication in production,
and I am more than a bit fuzzy on what will have to be done for successful
implementation.
Ok, guess I've over-explained :-) Thanks for listening.
Deborah
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >Since the log restores literally take less than a couple
> of seconds, is
> >there a way to kick the users out and automatically
> reconnect them? So that
> >from their perspective the query just took a couple more
> seconds to run?
> Not that I know of. Someone else?
> >but I'm
> >now in a time crunch and don't want to generate any new
> problems.
> >
> >Is transactional replication a straight-forward thing to
> implement? Are
> >there any issues with adding that to the plan that I need
> to be aware of?
> >If I were to implement transactional replication, how
> would that change the
> >backup/restore strategy, other than I wouldn't have to
> restore the
> >transaction logs every 10 minutes? Can the process be
> completely automated?
>
> Replication from my experience had somewhat of a steep
> learning curve. It really depends on your circumstances
> though. Security/ firewalls/ authentication/ disater
> recovery scenarios/ size of initial snapshot/ transferring
> of logins and jobs/ alloted maintenance time/ the
> infamous "schema changes to a published table"/ and lets
> not forget the all time favorite "identity colmuns in
> replication"/ ect. If you are on that much of a time
> crunch this may not be your best approach.
>
> Out of curiosity, how did you suddenly wind up in this
> truely bogus situation?
>
> >--Original Message--
> >I'm sure that the log shipping tool in the Enterprise
> Edition would work
> >great, but I do not have Enterprise Edition, nor will we
> be upgrading to it.
> >Our environment simply cannot warrant that.
> >
> >Since the log restores literally take less than a couple
> of seconds, is
> >there a way to kick the users out and automatically
> reconnect them? So that
> >from their perspective the query just took a couple more
> seconds to run?
> >
> >I have not tried to implement replication in SQL2K, and I
> am concerned about
> >possible maintenance mistakes. I've been reading up on
> it in BOL, but I'm
> >now in a time crunch and don't want to generate any new
> problems.
> >
> >Is transactional replication a straight-forward thing to
> implement? Are
> >there any issues with adding that to the plan that I need
> to be aware of?
> >If I were to implement transactional replication, how
> would that change the
> >backup/restore strategy, other than I wouldn't have to
> restore the
> >transaction logs every 10 minutes? Can the process be
> completely automated?
> >
> >Sorry to be asking so many questions. I have been
> reading BOL, but reading
> >the BOL, while usually helpful, is no substitute for
> talking to someone with
> >real experience.
> >
> >Thanks in advance,
> >Deborah
> >
> >
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> You need to be using something like replication to meet
> >> your stated requirements. In order for a db to be
> restored
> >> nobody can be accessing it. Log shipping has an option
> to
> >> allow users into the db while its not being restored and
> >> it will kick everony out when the restore is starting,
> but
> >> this isnt usually a good idea for a reporting server.
> >>
> >>
> >> >--Original Message--
> >> >I am trying to use my Standby Server as a Reporting
> >> Server so that our
> >> >resource intensive reporting queries can be run against
> >> it instead of
> >> >production.
> >> >
> >> >I've been successful at automating the backups and
> >> restores of all the
> >> >databases as well as transaction logs from production
> to
> >> standby. However,
> >> >if someone is trying to do a select query at the time,
> or
> >> even just have
> >> >their reporting application connected to it, the
> restore
> >> of the transaction
> >> >log fails "due to not being able to gain exclusive
> >> access". If one of the
> >> >transaction log restores fail, they all fail after that
> >> because the LSNs are
> >> >off.
> >> >
> >> >This is, of course, an urgent problem. Management has
> >> required that this
> >> >server be as current as possible for reporting purposes
> >> and this entire
> >> >process must be automated.
> >> >
> >> >Servers are W2K Server sp4, SQL2K Standard Edition
> sp3a.
> >> >
> >> >Below is an example of my syntax for the transaction
> log
> >> restores. Works
> >> >like a charm unless something is connected to the
> >> database. I had to do it
> >> >this way because I wanted to save all the transaction
> log
> >> files taken
> >> >throughout the day in case of "point in time" disaster
> >> recovery. I ended up
> >> >opening a ticket with Microsoft and this is the best
> >> solution they could
> >> >suggest. They said that normally with log shipping,
> the
> >> TLog backups
> >> >overwrite the others in the device. I found it
> difficult
> >> to believe that it
> >> >was expected that every process would work correctly
> >> every time and you
> >> >would have no need to ever access a transaction log
> >> backup that was
> >> >previously taken. If it has been overwritten, and you
> >> try to restore one
> >> >that is later, SQL send the error about LSNs not being
> >> right.
> >> >
> >> > Is there something in here that needs to be changed to
> >> accomplish my task?
> >> >
> >> >CREATE proc RestoreApplicationLogs_TLog
> >> >as
> >> >declare @.position int
> >> >declare @.mediasetid int
> >> >
> >> >set @.mediasetid = (select media_set_id
> >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> >where logical_device_name = 'ApplicationLogs_TLog')
> >> >
> >> >set @.position= (select position from
> >> NCNSV1015.msdb.dbo.backupset
> >> >where Backup_set_id = (select max(backup_set_id) from
> >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> @.mediasetid))
> >> >
> >> >Restore Log ApplicationLogs
> >> >from disk
> >>
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> '
> >> >with file = @.position,
> >> >dbo_only,
> >> >standby
> >>
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> '
> >> >
> >> >waitfor delay '00:00:10'
> >> >GO
> >> >
> >> >
> >> >Please help.
> >> >
> >> >Thank you in advance,
> >> >Deborah
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||You can kick users out of a database... Tibor Karaszi has some utility
procedures on his web site... www.sqlmaint.com..
One of those will kill all connections to a given database... You can not
however re-connect automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Well, clearly I have run into this bogus situation due to not considering
> the fact the I couldn't do log restores while someone is connected to the
> database. I mean, I knew that database restores required exclusive
access,
> but somehow I thought that a read-only database would allow transaction
log
> restores while others are connected to the database. This would be a
major
> oops on my part. I had originally suggested to management that the data
be
> synchronized only once or twice a day, but they firmly want a solution
that
> allows them to off-load some of the decision support queries/reports that
> they use to be retrieved from this "standby" server, in order to reduce
the
> resource load on production. This has always been touted as an advantage
to
> having a standby server.
> It has taken weeks to iron out the bugs that have popped up in automating
> the backup and restore of 36 databases. Everything from permissions
issues
> to invalid syntax to just plain old timing problems. Now that has been
> running successfully all week and they are ready to point these apps to
that
> server. I was testing that and began to see my restore log jobs failing,
of
> course.
> So, at this point, I don't know quite what I'm going to have to do in
order
> to get the desired results. Disappointing is not quite the word I would
use
> to describe the situation if I have to tell them that I now have to take
> some considerable time to create and test a replication scenario. But, if
> it is the ONLY way, then I guess that's just what I'll have to do.
> I am mentally exhausted and in desperate need of a workable solution. Its
> been at least 2 years since I've used any type of replication in
production,
> and I am more than a bit fuzzy on what will have to be done for successful
> implementation.
> Ok, guess I've over-explained :-) Thanks for listening.
> Deborah
>
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> > >Since the log restores literally take less than a couple
> > of seconds, is
> > >there a way to kick the users out and automatically
> > reconnect them? So that
> > >from their perspective the query just took a couple more
> > seconds to run?
> >
> > Not that I know of. Someone else?
> >
> > >but I'm
> > >now in a time crunch and don't want to generate any new
> > problems.
> > >
> > >Is transactional replication a straight-forward thing to
> > implement? Are
> > >there any issues with adding that to the plan that I need
> > to be aware of?
> > >If I were to implement transactional replication, how
> > would that change the
> > >backup/restore strategy, other than I wouldn't have to
> > restore the
> > >transaction logs every 10 minutes? Can the process be
> > completely automated?
> >
> >
> > Replication from my experience had somewhat of a steep
> > learning curve. It really depends on your circumstances
> > though. Security/ firewalls/ authentication/ disater
> > recovery scenarios/ size of initial snapshot/ transferring
> > of logins and jobs/ alloted maintenance time/ the
> > infamous "schema changes to a published table"/ and lets
> > not forget the all time favorite "identity colmuns in
> > replication"/ ect. If you are on that much of a time
> > crunch this may not be your best approach.
> >
> >
> > Out of curiosity, how did you suddenly wind up in this
> > truely bogus situation?
> >
> >
> > >--Original Message--
> > >I'm sure that the log shipping tool in the Enterprise
> > Edition would work
> > >great, but I do not have Enterprise Edition, nor will we
> > be upgrading to it.
> > >Our environment simply cannot warrant that.
> > >
> > >Since the log restores literally take less than a couple
> > of seconds, is
> > >there a way to kick the users out and automatically
> > reconnect them? So that
> > >from their perspective the query just took a couple more
> > seconds to run?
> > >
> > >I have not tried to implement replication in SQL2K, and I
> > am concerned about
> > >possible maintenance mistakes. I've been reading up on
> > it in BOL, but I'm
> > >now in a time crunch and don't want to generate any new
> > problems.
> > >
> > >Is transactional replication a straight-forward thing to
> > implement? Are
> > >there any issues with adding that to the plan that I need
> > to be aware of?
> > >If I were to implement transactional replication, how
> > would that change the
> > >backup/restore strategy, other than I wouldn't have to
> > restore the
> > >transaction logs every 10 minutes? Can the process be
> > completely automated?
> > >
> > >Sorry to be asking so many questions. I have been
> > reading BOL, but reading
> > >the BOL, while usually helpful, is no substitute for
> > talking to someone with
> > >real experience.
> > >
> > >Thanks in advance,
> > >Deborah
> > >
> > >
> > >
> > >"chris" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> > >> You need to be using something like replication to meet
> > >> your stated requirements. In order for a db to be
> > restored
> > >> nobody can be accessing it. Log shipping has an option
> > to
> > >> allow users into the db while its not being restored and
> > >> it will kick everony out when the restore is starting,
> > but
> > >> this isnt usually a good idea for a reporting server.
> > >>
> > >>
> > >> >--Original Message--
> > >> >I am trying to use my Standby Server as a Reporting
> > >> Server so that our
> > >> >resource intensive reporting queries can be run against
> > >> it instead of
> > >> >production.
> > >> >
> > >> >I've been successful at automating the backups and
> > >> restores of all the
> > >> >databases as well as transaction logs from production
> > to
> > >> standby. However,
> > >> >if someone is trying to do a select query at the time,
> > or
> > >> even just have
> > >> >their reporting application connected to it, the
> > restore
> > >> of the transaction
> > >> >log fails "due to not being able to gain exclusive
> > >> access". If one of the
> > >> >transaction log restores fail, they all fail after that
> > >> because the LSNs are
> > >> >off.
> > >> >
> > >> >This is, of course, an urgent problem. Management has
> > >> required that this
> > >> >server be as current as possible for reporting purposes
> > >> and this entire
> > >> >process must be automated.
> > >> >
> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> > sp3a.
> > >> >
> > >> >Below is an example of my syntax for the transaction
> > log
> > >> restores. Works
> > >> >like a charm unless something is connected to the
> > >> database. I had to do it
> > >> >this way because I wanted to save all the transaction
> > log
> > >> files taken
> > >> >throughout the day in case of "point in time" disaster
> > >> recovery. I ended up
> > >> >opening a ticket with Microsoft and this is the best
> > >> solution they could
> > >> >suggest. They said that normally with log shipping,
> > the
> > >> TLog backups
> > >> >overwrite the others in the device. I found it
> > difficult
> > >> to believe that it
> > >> >was expected that every process would work correctly
> > >> every time and you
> > >> >would have no need to ever access a transaction log
> > >> backup that was
> > >> >previously taken. If it has been overwritten, and you
> > >> try to restore one
> > >> >that is later, SQL send the error about LSNs not being
> > >> right.
> > >> >
> > >> > Is there something in here that needs to be changed to
> > >> accomplish my task?
> > >> >
> > >> >CREATE proc RestoreApplicationLogs_TLog
> > >> >as
> > >> >declare @.position int
> > >> >declare @.mediasetid int
> > >> >
> > >> >set @.mediasetid = (select media_set_id
> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> > >> >where logical_device_name = 'ApplicationLogs_TLog')
> > >> >
> > >> >set @.position= (select position from
> > >> NCNSV1015.msdb.dbo.backupset
> > >> >where Backup_set_id = (select max(backup_set_id) from
> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => > >> @.mediasetid))
> > >> >
> > >> >Restore Log ApplicationLogs
> > >> >from disk
> > >>
> > = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> > >> '
> > >> >with file = @.position,
> > >> >dbo_only,
> > >> >standby
> > >>
> > = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> > >> '
> > >> >
> > >> >waitfor delay '00:00:10'
> > >> >GO
> > >> >
> > >> >
> > >> >Please help.
> > >> >
> > >> >Thank you in advance,
> > >> >Deborah
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>|||I posted the syntax above ... it's just switches of the
ALTER DATABASE command. True, you have the app
reconnection problem, but hey, it keeps you in business.
This is the way backup/restore has worked historically in
SQL Server. It makes sense, as you are making sure
nothing can possibly interfere with the restore process.
I understand your dilemma, and I've had this hard
discussion with many of my clients in the past. It's a
common misunderstanding that if you put your db in
STANDBY, you always have read-only access. It's true if
you never apply a tran log.
Now, if you could possibly convince them that the
reporting server will be a delta of time out (say a few
hours) and apply a bunch of TLs all at once at known
times, it may help here.
>--Original Message--
>You can kick users out of a database... Tibor Karaszi has
some utility
>procedures on his web site... www.sqlmaint.com..
>One of those will kill all connections to a given
database... You can not
>however re-connect automatically.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Deborah Bohannon"
<dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
to not considering
>> the fact the I couldn't do log restores while someone
is connected to the
>> database. I mean, I knew that database restores
required exclusive
>access,
>> but somehow I thought that a read-only database would
allow transaction
>log
>> restores while others are connected to the database.
This would be a
>major
>> oops on my part. I had originally suggested to
management that the data
>be
>> synchronized only once or twice a day, but they firmly
want a solution
>that
>> allows them to off-load some of the decision support
queries/reports that
>> they use to be retrieved from this "standby" server, in
order to reduce
>the
>> resource load on production. This has always been
touted as an advantage
>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
popped up in automating
>> the backup and restore of 36 databases. Everything
from permissions
>issues
>> to invalid syntax to just plain old timing problems.
Now that has been
>> running successfully all week and they are ready to
point these apps to
>that
>> server. I was testing that and began to see my restore
log jobs failing,
>of
>> course.
>> So, at this point, I don't know quite what I'm going to
have to do in
>order
>> to get the desired results. Disappointing is not quite
the word I would
>use
>> to describe the situation if I have to tell them that I
now have to take
>> some considerable time to create and test a replication
scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
have to do.
>> I am mentally exhausted and in desperate need of a
workable solution. Its
>> been at least 2 years since I've used any type of
replication in
>production,
>> and I am more than a bit fuzzy on what will have to be
done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a steep
>> > learning curve. It really depends on your
circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading up
on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication to
meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
option
>> > to
>> > >> allow users into the db while its not being
restored and
>> > >> it will kick everony out when the restore is
starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Perhaps I can somehow have it written into the connection of their app to
the database that it reconnects whenever they run a "query" and disconnects
after. Then the transaction log restore process can "retry" a few times
until it is successful.
I'm not sure if that is possible or what kind of performance issues that
would bring up (as pertains to their reporting), but it might be another way
to address this problem.
It seems I have led them astray with the idea that using the warm standby
server could actually be used as a reporting server. At this point, I will
need to adjust their expectations. My head is getting bruised from all the
hitting it against the wall I've done on what was supposed to be a fairly
simple project :-)
We simply cannot afford to have a 3rd server that is available for use as a
reporting server. It was a stretch to convince them we needed a standby
server at all. It was only after the production server went down and 50
people were standing around for almost a day, not being productive, that it
became a priority. The real sell for them was the idea of off-loading some
of the reporting while also having a warm backup.
Thank you all for all your help.
Deborah
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:02a301c3c02d$833cd850$a501280a@.phx.gbl...
> I posted the syntax above ... it's just switches of the
> ALTER DATABASE command. True, you have the app
> reconnection problem, but hey, it keeps you in business.
> This is the way backup/restore has worked historically in
> SQL Server. It makes sense, as you are making sure
> nothing can possibly interfere with the restore process.
> I understand your dilemma, and I've had this hard
> discussion with many of my clients in the past. It's a
> common misunderstanding that if you put your db in
> STANDBY, you always have read-only access. It's true if
> you never apply a tran log.
> Now, if you could possibly convince them that the
> reporting server will be a delta of time out (say a few
> hours) and apply a bunch of TLs all at once at known
> times, it may help here.
> >--Original Message--
> >You can kick users out of a database... Tibor Karaszi has
> some utility
> >procedures on his web site... www.sqlmaint.com..
> >
> >One of those will kill all connections to a given
> database... You can not
> >however re-connect automatically.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Deborah Bohannon"
> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> >in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> >> Well, clearly I have run into this bogus situation due
> to not considering
> >> the fact the I couldn't do log restores while someone
> is connected to the
> >> database. I mean, I knew that database restores
> required exclusive
> >access,
> >> but somehow I thought that a read-only database would
> allow transaction
> >log
> >> restores while others are connected to the database.
> This would be a
> >major
> >> oops on my part. I had originally suggested to
> management that the data
> >be
> >> synchronized only once or twice a day, but they firmly
> want a solution
> >that
> >> allows them to off-load some of the decision support
> queries/reports that
> >> they use to be retrieved from this "standby" server, in
> order to reduce
> >the
> >> resource load on production. This has always been
> touted as an advantage
> >to
> >> having a standby server.
> >>
> >> It has taken weeks to iron out the bugs that have
> popped up in automating
> >> the backup and restore of 36 databases. Everything
> from permissions
> >issues
> >> to invalid syntax to just plain old timing problems.
> Now that has been
> >> running successfully all week and they are ready to
> point these apps to
> >that
> >> server. I was testing that and began to see my restore
> log jobs failing,
> >of
> >> course.
> >>
> >> So, at this point, I don't know quite what I'm going to
> have to do in
> >order
> >> to get the desired results. Disappointing is not quite
> the word I would
> >use
> >> to describe the situation if I have to tell them that I
> now have to take
> >> some considerable time to create and test a replication
> scenario. But, if
> >> it is the ONLY way, then I guess that's just what I'll
> have to do.
> >>
> >> I am mentally exhausted and in desperate need of a
> workable solution. Its
> >> been at least 2 years since I've used any type of
> replication in
> >production,
> >> and I am more than a bit fuzzy on what will have to be
> done for successful
> >> implementation.
> >>
> >> Ok, guess I've over-explained :-) Thanks for listening.
> >> Deborah
> >>
> >>
> >> "chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> >
> >> > Not that I know of. Someone else?
> >> >
> >> > >but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> >
> >> >
> >> > Replication from my experience had somewhat of a steep
> >> > learning curve. It really depends on your
> circumstances
> >> > though. Security/ firewalls/ authentication/ disater
> >> > recovery scenarios/ size of initial snapshot/
> transferring
> >> > of logins and jobs/ alloted maintenance time/ the
> >> > infamous "schema changes to a published table"/ and
> lets
> >> > not forget the all time favorite "identity colmuns in
> >> > replication"/ ect. If you are on that much of a time
> >> > crunch this may not be your best approach.
> >> >
> >> >
> >> > Out of curiosity, how did you suddenly wind up in this
> >> > truely bogus situation?
> >> >
> >> >
> >> > >--Original Message--
> >> > >I'm sure that the log shipping tool in the Enterprise
> >> > Edition would work
> >> > >great, but I do not have Enterprise Edition, nor
> will we
> >> > be upgrading to it.
> >> > >Our environment simply cannot warrant that.
> >> > >
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> > >
> >> > >I have not tried to implement replication in SQL2K,
> and I
> >> > am concerned about
> >> > >possible maintenance mistakes. I've been reading up
> on
> >> > it in BOL, but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> > >
> >> > >Sorry to be asking so many questions. I have been
> >> > reading BOL, but reading
> >> > >the BOL, while usually helpful, is no substitute for
> >> > talking to someone with
> >> > >real experience.
> >> > >
> >> > >Thanks in advance,
> >> > >Deborah
> >> > >
> >> > >
> >> > >
> >> > >"chris" <anonymous@.discussions.microsoft.com> wrote
> in
> >> > message
> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> > >> You need to be using something like replication to
> meet
> >> > >> your stated requirements. In order for a db to be
> >> > restored
> >> > >> nobody can be accessing it. Log shipping has an
> option
> >> > to
> >> > >> allow users into the db while its not being
> restored and
> >> > >> it will kick everony out when the restore is
> starting,
> >> > but
> >> > >> this isnt usually a good idea for a reporting
> server.
> >> > >>
> >> > >>
> >> > >> >--Original Message--
> >> > >> >I am trying to use my Standby Server as a
> Reporting
> >> > >> Server so that our
> >> > >> >resource intensive reporting queries can be run
> against
> >> > >> it instead of
> >> > >> >production.
> >> > >> >
> >> > >> >I've been successful at automating the backups and
> >> > >> restores of all the
> >> > >> >databases as well as transaction logs from
> production
> >> > to
> >> > >> standby. However,
> >> > >> >if someone is trying to do a select query at the
> time,
> >> > or
> >> > >> even just have
> >> > >> >their reporting application connected to it, the
> >> > restore
> >> > >> of the transaction
> >> > >> >log fails "due to not being able to gain exclusive
> >> > >> access". If one of the
> >> > >> >transaction log restores fail, they all fail
> after that
> >> > >> because the LSNs are
> >> > >> >off.
> >> > >> >
> >> > >> >This is, of course, an urgent problem.
> Management has
> >> > >> required that this
> >> > >> >server be as current as possible for reporting
> purposes
> >> > >> and this entire
> >> > >> >process must be automated.
> >> > >> >
> >> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> >> > sp3a.
> >> > >> >
> >> > >> >Below is an example of my syntax for the
> transaction
> >> > log
> >> > >> restores. Works
> >> > >> >like a charm unless something is connected to the
> >> > >> database. I had to do it
> >> > >> >this way because I wanted to save all the
> transaction
> >> > log
> >> > >> files taken
> >> > >> >throughout the day in case of "point in time"
> disaster
> >> > >> recovery. I ended up
> >> > >> >opening a ticket with Microsoft and this is the
> best
> >> > >> solution they could
> >> > >> >suggest. They said that normally with log
> shipping,
> >> > the
> >> > >> TLog backups
> >> > >> >overwrite the others in the device. I found it
> >> > difficult
> >> > >> to believe that it
> >> > >> >was expected that every process would work
> correctly
> >> > >> every time and you
> >> > >> >would have no need to ever access a transaction
> log
> >> > >> backup that was
> >> > >> >previously taken. If it has been overwritten,
> and you
> >> > >> try to restore one
> >> > >> >that is later, SQL send the error about LSNs not
> being
> >> > >> right.
> >> > >> >
> >> > >> > Is there something in here that needs to be
> changed to
> >> > >> accomplish my task?
> >> > >> >
> >> > >> >CREATE proc RestoreApplicationLogs_TLog
> >> > >> >as
> >> > >> >declare @.position int
> >> > >> >declare @.mediasetid int
> >> > >> >
> >> > >> >set @.mediasetid = (select media_set_id
> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> > >> >where logical_device_name
> = 'ApplicationLogs_TLog')
> >> > >> >
> >> > >> >set @.position= (select position from
> >> > >> NCNSV1015.msdb.dbo.backupset
> >> > >> >where Backup_set_id = (select max(backup_set_id)
> from
> >> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> > >> @.mediasetid))
> >> > >> >
> >> > >> >Restore Log ApplicationLogs
> >> > >> >from disk
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> > >> '
> >> > >> >with file = @.position,
> >> > >> >dbo_only,
> >> > >> >standby
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> > >> '
> >> > >> >
> >> > >> >waitfor delay '00:00:10'
> >> > >> >GO
> >> > >> >
> >> > >> >
> >> > >> >Please help.
> >> > >> >
> >> > >> >Thank you in advance,
> >> > >> >Deborah
> >> > >> >
> >> > >> >
> >> > >> >.
> >> > >> >
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||While you can't get around kicking users out in order to
do the restore, here's a simple way to do it. Of course
they'd have to rerun their queries or reports...
alter database dbname set SINGLE_USER with rollback
immediate
restore log dbname from disk = 't-log file'
alter database dbname set MULTI_USER
This works great for our DR site to do log restores during
the day. Replication would have been a nightmare to
implement. If anyone has any better solutions for a DR
site, feel free to share with me. I do the restores to DR
each hr from hourly t-log backups from production. I know
management would like it to be real-time, but replication
would just be too much trouble to keep maintained. I
could however do the t-log solution every 15 min instead
of every hr....
>--Original Message--
>You can kick users out of a database... Tibor Karaszi has
some utility
>procedures on his web site... www.sqlmaint.com..
>One of those will kill all connections to a given
database... You can not
>however re-connect automatically.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Deborah Bohannon"
<dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
to not considering
>> the fact the I couldn't do log restores while someone
is connected to the
>> database. I mean, I knew that database restores
required exclusive
>access,
>> but somehow I thought that a read-only database would
allow transaction
>log
>> restores while others are connected to the database.
This would be a
>major
>> oops on my part. I had originally suggested to
management that the data
>be
>> synchronized only once or twice a day, but they firmly
want a solution
>that
>> allows them to off-load some of the decision support
queries/reports that
>> they use to be retrieved from this "standby" server, in
order to reduce
>the
>> resource load on production. This has always been
touted as an advantage
>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
popped up in automating
>> the backup and restore of 36 databases. Everything
from permissions
>issues
>> to invalid syntax to just plain old timing problems.
Now that has been
>> running successfully all week and they are ready to
point these apps to
>that
>> server. I was testing that and began to see my restore
log jobs failing,
>of
>> course.
>> So, at this point, I don't know quite what I'm going to
have to do in
>order
>> to get the desired results. Disappointing is not quite
the word I would
>use
>> to describe the situation if I have to tell them that I
now have to take
>> some considerable time to create and test a replication
scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
have to do.
>> I am mentally exhausted and in desperate need of a
workable solution. Its
>> been at least 2 years since I've used any type of
replication in
>production,
>> and I am more than a bit fuzzy on what will have to be
done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a steep
>> > learning curve. It really depends on your
circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading up
on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication to
meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
option
>> > to
>> > >> allow users into the db while its not being
restored and
>> > >> it will kick everony out when the restore is
starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Do the users reports somehow automatically re-connect
after the restore? I ask only because she has stated that
to be one of her goals.
>--Original Message--
>While you can't get around kicking users out in order to
>do the restore, here's a simple way to do it. Of course
>they'd have to rerun their queries or reports...
>alter database dbname set SINGLE_USER with rollback
>immediate
>restore log dbname from disk = 't-log file'
>alter database dbname set MULTI_USER
>This works great for our DR site to do log restores
during
>the day. Replication would have been a nightmare to
>implement. If anyone has any better solutions for a DR
>site, feel free to share with me. I do the restores to
DR
>each hr from hourly t-log backups from production. I
know
>management would like it to be real-time, but replication
>would just be too much trouble to keep maintained. I
>could however do the t-log solution every 15 min instead
>of every hr....
>>--Original Message--
>>You can kick users out of a database... Tibor Karaszi
has
>some utility
>>procedures on his web site... www.sqlmaint.com..
>>One of those will kill all connections to a given
>database... You can not
>>however re-connect automatically.
>>--
>>Wayne Snyder, MCDBA, SQL Server MVP
>>Computer Education Services Corporation (CESC),
>Charlotte, NC
>>www.computeredservices.com
>>(Please respond only to the newsgroups.)
>>I support the Professional Association of SQL Server
>(PASS) and it's
>>community of SQL Server professionals.
>>www.sqlpass.org
>>"Deborah Bohannon"
><dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
>to not considering
>> the fact the I couldn't do log restores while someone
>is connected to the
>> database. I mean, I knew that database restores
>required exclusive
>>access,
>> but somehow I thought that a read-only database would
>allow transaction
>>log
>> restores while others are connected to the database.
>This would be a
>>major
>> oops on my part. I had originally suggested to
>management that the data
>>be
>> synchronized only once or twice a day, but they firmly
>want a solution
>>that
>> allows them to off-load some of the decision support
>queries/reports that
>> they use to be retrieved from this "standby" server,
in
>order to reduce
>>the
>> resource load on production. This has always been
>touted as an advantage
>>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
>popped up in automating
>> the backup and restore of 36 databases. Everything
>from permissions
>>issues
>> to invalid syntax to just plain old timing problems.
>Now that has been
>> running successfully all week and they are ready to
>point these apps to
>>that
>> server. I was testing that and began to see my
restore
>log jobs failing,
>>of
>> course.
>> So, at this point, I don't know quite what I'm going
to
>have to do in
>>order
>> to get the desired results. Disappointing is not
quite
>the word I would
>>use
>> to describe the situation if I have to tell them that
I
>now have to take
>> some considerable time to create and test a
replication
>scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
>have to do.
>> I am mentally exhausted and in desperate need of a
>workable solution. Its
>> been at least 2 years since I've used any type of
>replication in
>>production,
>> and I am more than a bit fuzzy on what will have to be
>done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for
listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
>couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
>more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
>new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
>thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that
I
>need
>> > to be aware of?
>> > >If I were to implement transactional replication,
how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
>to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
>be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a
steep
>> > learning curve. It really depends on your
>circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
>transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
>lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in
this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the
Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
>will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
>couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
>more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
>and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading
up
>on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
>new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
>thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that
I
>need
>> > to be aware of?
>> > >If I were to implement transactional replication,
how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
>to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
>be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
>in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication
to
>meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
>option
>> > to
>> > >> allow users into the db while its not being
>restored and
>> > >> it will kick everony out when the restore is
>starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
>server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
>Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
>against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups
and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
>production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
>time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain
exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
>after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
>Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
>purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
>transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
>transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
>disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
>best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
>shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
>correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
>log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
>and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
>being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
>changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
>= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
>from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
>= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.ba
k
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
>= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ld
f
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>>
>>.
>.
>|||Van,
Thanks for your reply.
I'm going to try the alter database suggestion and see how much that would
impact a particular database app I've been testing with. If the affect is
minimal, meaning not too aggravating for those running queries/reports, then
I think that is the best way for us to go. I am the only DBA here, and
while I don't feel that it would be impossible to set up the transactional
replication scenario, I am unsure as to how much "administration" time from
me it would require. I am also the EDI Implemenation Specialist/BizTalk
guru and those duties are the "revenue" producing ones for our business.
Same old story, not enough hours in the day etc...
If anyone has any better ideas, I am certainly game to hear them.
Deborah
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0d7101c3c02f$f3bfb910$a001280a@.phx.gbl...
> While you can't get around kicking users out in order to
> do the restore, here's a simple way to do it. Of course
> they'd have to rerun their queries or reports...
> alter database dbname set SINGLE_USER with rollback
> immediate
> restore log dbname from disk = 't-log file'
> alter database dbname set MULTI_USER
> This works great for our DR site to do log restores during
> the day. Replication would have been a nightmare to
> implement. If anyone has any better solutions for a DR
> site, feel free to share with me. I do the restores to DR
> each hr from hourly t-log backups from production. I know
> management would like it to be real-time, but replication
> would just be too much trouble to keep maintained. I
> could however do the t-log solution every 15 min instead
> of every hr....
> >--Original Message--
> >You can kick users out of a database... Tibor Karaszi has
> some utility
> >procedures on his web site... www.sqlmaint.com..
> >
> >One of those will kill all connections to a given
> database... You can not
> >however re-connect automatically.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Deborah Bohannon"
> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> >in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> >> Well, clearly I have run into this bogus situation due
> to not considering
> >> the fact the I couldn't do log restores while someone
> is connected to the
> >> database. I mean, I knew that database restores
> required exclusive
> >access,
> >> but somehow I thought that a read-only database would
> allow transaction
> >log
> >> restores while others are connected to the database.
> This would be a
> >major
> >> oops on my part. I had originally suggested to
> management that the data
> >be
> >> synchronized only once or twice a day, but they firmly
> want a solution
> >that
> >> allows them to off-load some of the decision support
> queries/reports that
> >> they use to be retrieved from this "standby" server, in
> order to reduce
> >the
> >> resource load on production. This has always been
> touted as an advantage
> >to
> >> having a standby server.
> >>
> >> It has taken weeks to iron out the bugs that have
> popped up in automating
> >> the backup and restore of 36 databases. Everything
> from permissions
> >issues
> >> to invalid syntax to just plain old timing problems.
> Now that has been
> >> running successfully all week and they are ready to
> point these apps to
> >that
> >> server. I was testing that and began to see my restore
> log jobs failing,
> >of
> >> course.
> >>
> >> So, at this point, I don't know quite what I'm going to
> have to do in
> >order
> >> to get the desired results. Disappointing is not quite
> the word I would
> >use
> >> to describe the situation if I have to tell them that I
> now have to take
> >> some considerable time to create and test a replication
> scenario. But, if
> >> it is the ONLY way, then I guess that's just what I'll
> have to do.
> >>
> >> I am mentally exhausted and in desperate need of a
> workable solution. Its
> >> been at least 2 years since I've used any type of
> replication in
> >production,
> >> and I am more than a bit fuzzy on what will have to be
> done for successful
> >> implementation.
> >>
> >> Ok, guess I've over-explained :-) Thanks for listening.
> >> Deborah
> >>
> >>
> >> "chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> >
> >> > Not that I know of. Someone else?
> >> >
> >> > >but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> >
> >> >
> >> > Replication from my experience had somewhat of a steep
> >> > learning curve. It really depends on your
> circumstances
> >> > though. Security/ firewalls/ authentication/ disater
> >> > recovery scenarios/ size of initial snapshot/
> transferring
> >> > of logins and jobs/ alloted maintenance time/ the
> >> > infamous "schema changes to a published table"/ and
> lets
> >> > not forget the all time favorite "identity colmuns in
> >> > replication"/ ect. If you are on that much of a time
> >> > crunch this may not be your best approach.
> >> >
> >> >
> >> > Out of curiosity, how did you suddenly wind up in this
> >> > truely bogus situation?
> >> >
> >> >
> >> > >--Original Message--
> >> > >I'm sure that the log shipping tool in the Enterprise
> >> > Edition would work
> >> > >great, but I do not have Enterprise Edition, nor
> will we
> >> > be upgrading to it.
> >> > >Our environment simply cannot warrant that.
> >> > >
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> > >
> >> > >I have not tried to implement replication in SQL2K,
> and I
> >> > am concerned about
> >> > >possible maintenance mistakes. I've been reading up
> on
> >> > it in BOL, but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> > >
> >> > >Sorry to be asking so many questions. I have been
> >> > reading BOL, but reading
> >> > >the BOL, while usually helpful, is no substitute for
> >> > talking to someone with
> >> > >real experience.
> >> > >
> >> > >Thanks in advance,
> >> > >Deborah
> >> > >
> >> > >
> >> > >
> >> > >"chris" <anonymous@.discussions.microsoft.com> wrote
> in
> >> > message
> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> > >> You need to be using something like replication to
> meet
> >> > >> your stated requirements. In order for a db to be
> >> > restored
> >> > >> nobody can be accessing it. Log shipping has an
> option
> >> > to
> >> > >> allow users into the db while its not being
> restored and
> >> > >> it will kick everony out when the restore is
> starting,
> >> > but
> >> > >> this isnt usually a good idea for a reporting
> server.
> >> > >>
> >> > >>
> >> > >> >--Original Message--
> >> > >> >I am trying to use my Standby Server as a
> Reporting
> >> > >> Server so that our
> >> > >> >resource intensive reporting queries can be run
> against
> >> > >> it instead of
> >> > >> >production.
> >> > >> >
> >> > >> >I've been successful at automating the backups and
> >> > >> restores of all the
> >> > >> >databases as well as transaction logs from
> production
> >> > to
> >> > >> standby. However,
> >> > >> >if someone is trying to do a select query at the
> time,
> >> > or
> >> > >> even just have
> >> > >> >their reporting application connected to it, the
> >> > restore
> >> > >> of the transaction
> >> > >> >log fails "due to not being able to gain exclusive
> >> > >> access". If one of the
> >> > >> >transaction log restores fail, they all fail
> after that
> >> > >> because the LSNs are
> >> > >> >off.
> >> > >> >
> >> > >> >This is, of course, an urgent problem.
> Management has
> >> > >> required that this
> >> > >> >server be as current as possible for reporting
> purposes
> >> > >> and this entire
> >> > >> >process must be automated.
> >> > >> >
> >> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> >> > sp3a.
> >> > >> >
> >> > >> >Below is an example of my syntax for the
> transaction
> >> > log
> >> > >> restores. Works
> >> > >> >like a charm unless something is connected to the
> >> > >> database. I had to do it
> >> > >> >this way because I wanted to save all the
> transaction
> >> > log
> >> > >> files taken
> >> > >> >throughout the day in case of "point in time"
> disaster
> >> > >> recovery. I ended up
> >> > >> >opening a ticket with Microsoft and this is the
> best
> >> > >> solution they could
> >> > >> >suggest. They said that normally with log
> shipping,
> >> > the
> >> > >> TLog backups
> >> > >> >overwrite the others in the device. I found it
> >> > difficult
> >> > >> to believe that it
> >> > >> >was expected that every process would work
> correctly
> >> > >> every time and you
> >> > >> >would have no need to ever access a transaction
> log
> >> > >> backup that was
> >> > >> >previously taken. If it has been overwritten,
> and you
> >> > >> try to restore one
> >> > >> >that is later, SQL send the error about LSNs not
> being
> >> > >> right.
> >> > >> >
> >> > >> > Is there something in here that needs to be
> changed to
> >> > >> accomplish my task?
> >> > >> >
> >> > >> >CREATE proc RestoreApplicationLogs_TLog
> >> > >> >as
> >> > >> >declare @.position int
> >> > >> >declare @.mediasetid int
> >> > >> >
> >> > >> >set @.mediasetid = (select media_set_id
> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> > >> >where logical_device_name
> = 'ApplicationLogs_TLog')
> >> > >> >
> >> > >> >set @.position= (select position from
> >> > >> NCNSV1015.msdb.dbo.backupset
> >> > >> >where Backup_set_id = (select max(backup_set_id)
> from
> >> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> > >> @.mediasetid))
> >> > >> >
> >> > >> >Restore Log ApplicationLogs
> >> > >> >from disk
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> > >> '
> >> > >> >with file = @.position,
> >> > >> >dbo_only,
> >> > >> >standby
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> > >> '
> >> > >> >
> >> > >> >waitfor delay '00:00:10'
> >> > >> >GO
> >> > >> >
> >> > >> >
> >> > >> >Please help.
> >> > >> >
> >> > >> >Thank you in advance,
> >> > >> >Deborah
> >> > >> >
> >> > >> >
> >> > >> >.
> >> > >> >
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||Dont know about that last one. If you should need to end
up using replication, start posting to the replication
section of this NG. A few of us reply to questions asked
there regularly in an attempt to save others the growing
pains of using replication. Keep in mind that its like log
shipping in that once you have the bugs ironed out its
pretty easy to maintain.
Good luck.
>--Original Message--
>Perhaps I can somehow have it written into the connection
of their app to
>the database that it reconnects whenever they run
a "query" and disconnects
>after. Then the transaction log restore process
can "retry" a few times
>until it is successful.
>I'm not sure if that is possible or what kind of
performance issues that
>would bring up (as pertains to their reporting), but it
might be another way
>to address this problem.
>It seems I have led them astray with the idea that using
the warm standby
>server could actually be used as a reporting server. At
this point, I will
>need to adjust their expectations. My head is getting
bruised from all the
>hitting it against the wall I've done on what was
supposed to be a fairly
>simple project :-)
>We simply cannot afford to have a 3rd server that is
available for use as a
>reporting server. It was a stretch to convince them we
needed a standby
>server at all. It was only after the production server
went down and 50
>people were standing around for almost a day, not being
productive, that it
>became a priority. The real sell for them was the idea
of off-loading some
>of the reporting while also having a warm backup.
>Thank you all for all your help.
>Deborah
>"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
>news:02a301c3c02d$833cd850$a501280a@.phx.gbl...
>> I posted the syntax above ... it's just switches of the
>> ALTER DATABASE command. True, you have the app
>> reconnection problem, but hey, it keeps you in business.
>> This is the way backup/restore has worked historically
in
>> SQL Server. It makes sense, as you are making sure
>> nothing can possibly interfere with the restore process.
>> I understand your dilemma, and I've had this hard
>> discussion with many of my clients in the past. It's a
>> common misunderstanding that if you put your db in
>> STANDBY, you always have read-only access. It's true if
>> you never apply a tran log.
>> Now, if you could possibly convince them that the
>> reporting server will be a delta of time out (say a few
>> hours) and apply a bunch of TLs all at once at known
>> times, it may help here.
>> >--Original Message--
>> >You can kick users out of a database... Tibor Karaszi
has
>> some utility
>> >procedures on his web site... www.sqlmaint.com..
>> >
>> >One of those will kill all connections to a given
>> database... You can not
>> >however re-connect automatically.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >"Deborah Bohannon"
>> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>> >in message
news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> >> Well, clearly I have run into this bogus situation
due
>> to not considering
>> >> the fact the I couldn't do log restores while someone
>> is connected to the
>> >> database. I mean, I knew that database restores
>> required exclusive
>> >access,
>> >> but somehow I thought that a read-only database would
>> allow transaction
>> >log
>> >> restores while others are connected to the database.
>> This would be a
>> >major
>> >> oops on my part. I had originally suggested to
>> management that the data
>> >be
>> >> synchronized only once or twice a day, but they
firmly
>> want a solution
>> >that
>> >> allows them to off-load some of the decision support
>> queries/reports that
>> >> they use to be retrieved from this "standby" server,
in
>> order to reduce
>> >the
>> >> resource load on production. This has always been
>> touted as an advantage
>> >to
>> >> having a standby server.
>> >>
>> >> It has taken weeks to iron out the bugs that have
>> popped up in automating
>> >> the backup and restore of 36 databases. Everything
>> from permissions
>> >issues
>> >> to invalid syntax to just plain old timing problems.
>> Now that has been
>> >> running successfully all week and they are ready to
>> point these apps to
>> >that
>> >> server. I was testing that and began to see my
restore
>> log jobs failing,
>> >of
>> >> course.
>> >>
>> >> So, at this point, I don't know quite what I'm going
to
>> have to do in
>> >order
>> >> to get the desired results. Disappointing is not
quite
>> the word I would
>> >use
>> >> to describe the situation if I have to tell them
that I
>> now have to take
>> >> some considerable time to create and test a
replication
>> scenario. But, if
>> >> it is the ONLY way, then I guess that's just what
I'll
>> have to do.
>> >>
>> >> I am mentally exhausted and in desperate need of a
>> workable solution. Its
>> >> been at least 2 years since I've used any type of
>> replication in
>> >production,
>> >> and I am more than a bit fuzzy on what will have to
be
>> done for successful
>> >> implementation.
>> >>
>> >> Ok, guess I've over-explained :-) Thanks for
listening.
>> >> Deborah
>> >>
>> >>
>> >> "chris" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> >
>> >> > Not that I know of. Someone else?
>> >> >
>> >> > >but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> >
>> >> >
>> >> > Replication from my experience had somewhat of a
steep
>> >> > learning curve. It really depends on your
>> circumstances
>> >> > though. Security/ firewalls/ authentication/
disater
>> >> > recovery scenarios/ size of initial snapshot/
>> transferring
>> >> > of logins and jobs/ alloted maintenance time/ the
>> >> > infamous "schema changes to a published table"/ and
>> lets
>> >> > not forget the all time favorite "identity colmuns
in
>> >> > replication"/ ect. If you are on that much of a
time
>> >> > crunch this may not be your best approach.
>> >> >
>> >> >
>> >> > Out of curiosity, how did you suddenly wind up in
this
>> >> > truely bogus situation?
>> >> >
>> >> >
>> >> > >--Original Message--
>> >> > >I'm sure that the log shipping tool in the
Enterprise
>> >> > Edition would work
>> >> > >great, but I do not have Enterprise Edition, nor
>> will we
>> >> > be upgrading to it.
>> >> > >Our environment simply cannot warrant that.
>> >> > >
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> > >
>> >> > >I have not tried to implement replication in
SQL2K,
>> and I
>> >> > am concerned about
>> >> > >possible maintenance mistakes. I've been reading
up
>> on
>> >> > it in BOL, but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> > >
>> >> > >Sorry to be asking so many questions. I have been
>> >> > reading BOL, but reading
>> >> > >the BOL, while usually helpful, is no substitute
for
>> >> > talking to someone with
>> >> > >real experience.
>> >> > >
>> >> > >Thanks in advance,
>> >> > >Deborah
>> >> > >
>> >> > >
>> >> > >
>> >> > >"chris" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> > message
>> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> >> > >> You need to be using something like replication
to
>> meet
>> >> > >> your stated requirements. In order for a db to
be
>> >> > restored
>> >> > >> nobody can be accessing it. Log shipping has an
>> option
>> >> > to
>> >> > >> allow users into the db while its not being
>> restored and
>> >> > >> it will kick everony out when the restore is
>> starting,
>> >> > but
>> >> > >> this isnt usually a good idea for a reporting
>> server.
>> >> > >>
>> >> > >>
>> >> > >> >--Original Message--
>> >> > >> >I am trying to use my Standby Server as a
>> Reporting
>> >> > >> Server so that our
>> >> > >> >resource intensive reporting queries can be run
>> against
>> >> > >> it instead of
>> >> > >> >production.
>> >> > >> >
>> >> > >> >I've been successful at automating the backups
and
>> >> > >> restores of all the
>> >> > >> >databases as well as transaction logs from
>> production
>> >> > to
>> >> > >> standby. However,
>> >> > >> >if someone is trying to do a select query at
the
>> time,
>> >> > or
>> >> > >> even just have
>> >> > >> >their reporting application connected to it,
the
>> >> > restore
>> >> > >> of the transaction
>> >> > >> >log fails "due to not being able to gain
exclusive
>> >> > >> access". If one of the
>> >> > >> >transaction log restores fail, they all fail
>> after that
>> >> > >> because the LSNs are
>> >> > >> >off.
>> >> > >> >
>> >> > >> >This is, of course, an urgent problem.
>> Management has
>> >> > >> required that this
>> >> > >> >server be as current as possible for reporting
>> purposes
>> >> > >> and this entire
>> >> > >> >process must be automated.
>> >> > >> >
>> >> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> >> > sp3a.
>> >> > >> >
>> >> > >> >Below is an example of my syntax for the
>> transaction
>> >> > log
>> >> > >> restores. Works
>> >> > >> >like a charm unless something is connected to
the
>> >> > >> database. I had to do it
>> >> > >> >this way because I wanted to save all the
>> transaction
>> >> > log
>> >> > >> files taken
>> >> > >> >throughout the day in case of "point in time"
>> disaster
>> >> > >> recovery. I ended up
>> >> > >> >opening a ticket with Microsoft and this is the
>> best
>> >> > >> solution they could
>> >> > >> >suggest. They said that normally with log
>> shipping,
>> >> > the
>> >> > >> TLog backups
>> >> > >> >overwrite the others in the device. I found it
>> >> > difficult
>> >> > >> to believe that it
>> >> > >> >was expected that every process would work
>> correctly
>> >> > >> every time and you
>> >> > >> >would have no need to ever access a
transaction
>> log
>> >> > >> backup that was
>> >> > >> >previously taken. If it has been overwritten,
>> and you
>> >> > >> try to restore one
>> >> > >> >that is later, SQL send the error about LSNs
not
>> being
>> >> > >> right.
>> >> > >> >
>> >> > >> > Is there something in here that needs to be
>> changed to
>> >> > >> accomplish my task?
>> >> > >> >
>> >> > >> >CREATE proc RestoreApplicationLogs_TLog
>> >> > >> >as
>> >> > >> >declare @.position int
>> >> > >> >declare @.mediasetid int
>> >> > >> >
>> >> > >> >set @.mediasetid = (select media_set_id
>> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >> > >> >where logical_device_name
>> = 'ApplicationLogs_TLog')
>> >> > >> >
>> >> > >> >set @.position= (select position from
>> >> > >> NCNSV1015.msdb.dbo.backupset
>> >> > >> >where Backup_set_id = (select max
(backup_set_id)
>> from
>> >> > >> >NCNSV1015.msdb.dbo.backupset where
media_set_id =>> >> > >> @.mediasetid))
>> >> > >> >
>> >> > >> >Restore Log ApplicationLogs
>> >> > >> >from disk
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> >> > >> '
>> >> > >> >with file = @.position,
>> >> > >> >dbo_only,
>> >> > >> >standby
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> >> > >> '
>> >> > >> >
>> >> > >> >waitfor delay '00:00:10'
>> >> > >> >GO
>> >> > >> >
>> >> > >> >
>> >> > >> >Please help.
>> >> > >> >
>> >> > >> >Thank you in advance,
>> >> > >> >Deborah
>> >> > >> >
>> >> > >> >
>> >> > >> >.
>> >> > >> >
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Yea, the With Rollback Immediate will kick the users out
and that may be a bit of a problem, but with tlog
restores, that's about the best you can do. You have to
ask management what's more important...a reporting server,
or having the standby db up to date with production in
case of an outage with the production server. I believe
the latter is more important. Most of their reports
shouldn't take too long to run (since it's not used too
much), so kicking them out for a few seconds and having
them rerun hopefully won't bother them too much. At least
you can tell them that the data will be more current when
they rerun.
The problem I have with replication is that it doesn't
replicate structure changes to your db and tables. You
have to do that seperatly by basically reshipping a
current version of the db or table to the standby server
with the structure changes. At least that's the way I
understand it. So to me it seems like there is a great
deal of overhead with managing and keeping replication in
line. And also, it seems like you really don't know if
it's working without extensive testing each time you do a
structure change and apply it to your standby. With log
shipping, you know that all your changes (data and
structure) are being applied to the standby server.
That's the way I understand replication to work.
Van
>--Original Message--
>Van,
>Thanks for your reply.
>I'm going to try the alter database suggestion and see
how much that would
>impact a particular database app I've been testing with.
If the affect is
>minimal, meaning not too aggravating for those running
queries/reports, then
>I think that is the best way for us to go. I am the only
DBA here, and
>while I don't feel that it would be impossible to set up
the transactional
>replication scenario, I am unsure as to how
much "administration" time from
>me it would require. I am also the EDI Implemenation
Specialist/BizTalk
>guru and those duties are the "revenue" producing ones
for our business.
>Same old story, not enough hours in the day etc...
>If anyone has any better ideas, I am certainly game to
hear them.
>Deborah
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:0d7101c3c02f$f3bfb910$a001280a@.phx.gbl...
>> While you can't get around kicking users out in order to
>> do the restore, here's a simple way to do it. Of course
>> they'd have to rerun their queries or reports...
>> alter database dbname set SINGLE_USER with rollback
>> immediate
>> restore log dbname from disk = 't-log file'
>> alter database dbname set MULTI_USER
>> This works great for our DR site to do log restores
during
>> the day. Replication would have been a nightmare to
>> implement. If anyone has any better solutions for a DR
>> site, feel free to share with me. I do the restores to
DR
>> each hr from hourly t-log backups from production. I
know
>> management would like it to be real-time, but
replication
>> would just be too much trouble to keep maintained. I
>> could however do the t-log solution every 15 min instead
>> of every hr....
>> >--Original Message--
>> >You can kick users out of a database... Tibor Karaszi
has
>> some utility
>> >procedures on his web site... www.sqlmaint.com..
>> >
>> >One of those will kill all connections to a given
>> database... You can not
>> >however re-connect automatically.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >"Deborah Bohannon"
>> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>> >in message
news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> >> Well, clearly I have run into this bogus situation
due
>> to not considering
>> >> the fact the I couldn't do log restores while someone
>> is connected to the
>> >> database. I mean, I knew that database restores
>> required exclusive
>> >access,
>> >> but somehow I thought that a read-only database would
>> allow transaction
>> >log
>> >> restores while others are connected to the database.
>> This would be a
>> >major
>> >> oops on my part. I had originally suggested to
>> management that the data
>> >be
>> >> synchronized only once or twice a day, but they
firmly
>> want a solution
>> >that
>> >> allows them to off-load some of the decision support
>> queries/reports that
>> >> they use to be retrieved from this "standby" server,
in
>> order to reduce
>> >the
>> >> resource load on production. This has always been
>> touted as an advantage
>> >to
>> >> having a standby server.
>> >>
>> >> It has taken weeks to iron out the bugs that have
>> popped up in automating
>> >> the backup and restore of 36 databases. Everything
>> from permissions
>> >issues
>> >> to invalid syntax to just plain old timing problems.
>> Now that has been
>> >> running successfully all week and they are ready to
>> point these apps to
>> >that
>> >> server. I was testing that and began to see my
restore
>> log jobs failing,
>> >of
>> >> course.
>> >>
>> >> So, at this point, I don't know quite what I'm going
to
>> have to do in
>> >order
>> >> to get the desired results. Disappointing is not
quite
>> the word I would
>> >use
>> >> to describe the situation if I have to tell them
that I
>> now have to take
>> >> some considerable time to create and test a
replication
>> scenario. But, if
>> >> it is the ONLY way, then I guess that's just what
I'll
>> have to do.
>> >>
>> >> I am mentally exhausted and in desperate need of a
>> workable solution. Its
>> >> been at least 2 years since I've used any type of
>> replication in
>> >production,
>> >> and I am more than a bit fuzzy on what will have to
be
>> done for successful
>> >> implementation.
>> >>
>> >> Ok, guess I've over-explained :-) Thanks for
listening.
>> >> Deborah
>> >>
>> >>
>> >> "chris" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> >
>> >> > Not that I know of. Someone else?
>> >> >
>> >> > >but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> >
>> >> >
>> >> > Replication from my experience had somewhat of a
steep
>> >> > learning curve. It really depends on your
>> circumstances
>> >> > though. Security/ firewalls/ authentication/
disater
>> >> > recovery scenarios/ size of initial snapshot/
>> transferring
>> >> > of logins and jobs/ alloted maintenance time/ the
>> >> > infamous "schema changes to a published table"/ and
>> lets
>> >> > not forget the all time favorite "identity colmuns
in
>> >> > replication"/ ect. If you are on that much of a
time
>> >> > crunch this may not be your best approach.
>> >> >
>> >> >
>> >> > Out of curiosity, how did you suddenly wind up in
this
>> >> > truely bogus situation?
>> >> >
>> >> >
>> >> > >--Original Message--
>> >> > >I'm sure that the log shipping tool in the
Enterprise
>> >> > Edition would work
>> >> > >great, but I do not have Enterprise Edition, nor
>> will we
>> >> > be upgrading to it.
>> >> > >Our environment simply cannot warrant that.
>> >> > >
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> > >
>> >> > >I have not tried to implement replication in
SQL2K,
>> and I
>> >> > am concerned about
>> >> > >possible maintenance mistakes. I've been reading
up
>> on
>> >> > it in BOL, but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> > >
>> >> > >Sorry to be asking so many questions. I have been
>> >> > reading BOL, but reading
>> >> > >the BOL, while usually helpful, is no substitute
for
>> >> > talking to someone with
>> >> > >real experience.
>> >> > >
>> >> > >Thanks in advance,
>> >> > >Deborah
>> >> > >
>> >> > >
>> >> > >
>> >> > >"chris" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> > message
>> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> >> > >> You need to be using something like replication
to
>> meet
>> >> > >> your stated requirements. In order for a db to
be
>> >> > restored
>> >> > >> nobody can be accessing it. Log shipping has an
>> option
>> >> > to
>> >> > >> allow users into the db while its not being
>> restored and
>> >> > >> it will kick everony out when the restore is
>> starting,
>> >> > but
>> >> > >> this isnt usually a good idea for a reporting
>> server.
>> >> > >>
>> >> > >>
>> >> > >> >--Original Message--
>> >> > >> >I am trying to use my Standby Server as a
>> Reporting
>> >> > >> Server so that our
>> >> > >> >resource intensive reporting queries can be run
>> against
>> >> > >> it instead of
>> >> > >> >production.
>> >> > >> >
>> >> > >> >I've been successful at automating the backups
and
>> >> > >> restores of all the
>> >> > >> >databases as well as transaction logs from
>> production
>> >> > to
>> >> > >> standby. However,
>> >> > >> >if someone is trying to do a select query at
the
>> time,
>> >> > or
>> >> > >> even just have
>> >> > >> >their reporting application connected to it,
the
>> >> > restore
>> >> > >> of the transaction
>> >> > >> >log fails "due to not being able to gain
exclusive
>> >> > >> access". If one of the
>> >> > >> >transaction log restores fail, they all fail
>> after that
>> >> > >> because the LSNs are
>> >> > >> >off.
>> >> > >> >
>> >> > >> >This is, of course, an urgent problem.
>> Management has
>> >> > >> required that this
>> >> > >> >server be as current as possible for reporting
>> purposes
>> >> > >> and this entire
>> >> > >> >process must be automated.
>> >> > >> >
>> >> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> >> > sp3a.
>> >> > >> >
>> >> > >> >Below is an example of my syntax for the
>> transaction
>> >> > log
>> >> > >> restores. Works
>> >> > >> >like a charm unless something is connected to
the
>> >> > >> database. I had to do it
>> >> > >> >this way because I wanted to save all the
>> transaction
>> >> > log
>> >> > >> files taken
>> >> > >> >throughout the day in case of "point in time"
>> disaster
>> >> > >> recovery. I ended up
>> >> > >> >opening a ticket with Microsoft and this is the
>> best
>> >> > >> solution they could
>> >> > >> >suggest. They said that normally with log
>> shipping,
>> >> > the
>> >> > >> TLog backups
>> >> > >> >overwrite the others in the device. I found it
>> >> > difficult
>> >> > >> to believe that it
>> >> > >> >was expected that every process would work
>> correctly
>> >> > >> every time and you
>> >> > >> >would have no need to ever access a
transaction
>> log
>> >> > >> backup that was
>> >> > >> >previously taken. If it has been overwritten,
>> and you
>> >> > >> try to restore one
>> >> > >> >that is later, SQL send the error about LSNs
not
>> being
>> >> > >> right.
>> >> > >> >
>> >> > >> > Is there something in here that needs to be
>> changed to
>> >> > >> accomplish my task?
>> >> > >> >
>> >> > >> >CREATE proc RestoreApplicationLogs_TLog
>> >> > >> >as
>> >> > >> >declare @.position int
>> >> > >> >declare @.mediasetid int
>> >> > >> >
>> >> > >> >set @.mediasetid = (select media_set_id
>> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >> > >> >where logical_device_name
>> = 'ApplicationLogs_TLog')
>> >> > >> >
>> >> > >> >set @.position= (select position from
>> >> > >> NCNSV1015.msdb.dbo.backupset
>> >> > >> >where Backup_set_id = (select max
(backup_set_id)
>> from
>> >> > >> >NCNSV1015.msdb.dbo.backupset where
media_set_id =>> >> > >> @.mediasetid))
>> >> > >> >
>> >> > >> >Restore Log ApplicationLogs
>> >> > >> >from disk
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> >> > >> '
>> >> > >> >with file = @.position,
>> >> > >> >dbo_only,
>> >> > >> >standby
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> >> > >> '
>> >> > >> >
>> >> > >> >waitfor delay '00:00:10'
>> >> > >> >GO
>> >> > >> >
>> >> > >> >
>> >> > >> >Please help.
>> >> > >> >
>> >> > >> >Thank you in advance,
>> >> > >> >Deborah
>> >> > >> >
>> >> > >> >
>> >> > >> >.
>> >> > >> >
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
