Accessing database in STANDBY mode while log shipping

Hi,

I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:

Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\CI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....

To sum up:

Is there anyway to access the standby server without causing the restore process to fail?

Both the Primary and Secondary are SQL 2K5.

Thanks!

Quick answer? No.

To load the t-logs, SQL Server needs exclusive access to the DB. So:

a) you allow read only access to continue, which can potentially block the t-log load

b) allow the t-log load, knowing that users will not be able to access the DB while the t-log is loading

c) kill the connections to the DB when the t-log is about to load, effectively creating b), but making unhappy end users

d) use another reporting solution

Log shipping has always been meant for creating a standby. While you can use it for read only access, it is not the best use of log shipping, and never has been. I have never ever recommended this configuration. YOu are better off setting up another way to do reporting and leave log shipping alone.

This is just the way the SQL engine works.

|||

Thanks!

Your basicly confirming, I guess, what I already knew. Just wanted to make sure I understood correctly.

Accessing an LDF file

I need to "browse" a 2GB SQL Server Transaction Log (.ldf file) to see if I
can extract certain information about a users activity. What can I use to
brows this file?
Thanks - Seanthree words "lumigent log explorer", is a thid party tool tha can explore th
e sql server transaction log, creating a audit solution, besides, you can ge
nerate the rollback statements for an specific transaction, and many more.
http://www.lumigent.com/
enjoy it
"HelpPls" wrote:

> I need to "browse" a 2GB SQL Server Transaction Log (.ldf file) to see if
I
> can extract certain information about a users activity. What can I use to
> brows this file?
> Thanks - Sean
>
>

Accessing a package's variables from within a custom log provider...or not...

Hi,

Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx

...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?

We live in hope,

Tamim.

I have recently looked at this and would agree, there is no way to use variables in a log provider. There are no hooks available in any of the base class methods or any parameters that you are exposed to in a log provider. Would be nice if you could though, as it would give you more control to create your own message content, but as it stands this is not viable.|||

Thanks Darren - I was pretty sure there was no alternative, so it's good to have that ratified by yourself. There is however one cheeky/not-so-neat way round: the 'source' for some events, e.g. PackageStart, gives the package name. (Of course this is an exception for only 1 system variable, with no wider applicability).

Cheers,

Tamim.

Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Access to variables in Custom Log Provider?

Hello,

I found one post from the past asking this question, but didn't see an answer, so I am hoping someone can shed some more light on it:

I am writing a custom log provider in C#. Is it possible give this custom log provider access to the variables collection (via a variabledispenser object)? First glance at the log provider classes would tell me that this is not possible, though I would like confirmation. Also, if I am correct in thinking it is not possible, what was the reason for leaving this out?

Thanks!

Indeed this does not appear to be possible. What is it you are trying to acheive? Some more info may help us evaluate this as a feature request for the future.

Donald

|||

Donald,

Thanks for the response. A brief explanation of my thought process below:

I have a package with a number of variables that are evaluated during package execution via property expressions. When a component of the package logs a warning or failure, I would like to be able to identify what state each variable was in at the time of that warning/failure. I had hoped to be able to create a custom log provider to log this information to a database along with the warning/failure message. If the VariableDispenser object was passed into the Log method of my custom log provider, I would be able to accomplish this.

Thanks again.

David

access sql server across domain thru windows authentication

Hi,
I have a sql server in a domain which is different than the one I log into,
and thats server has Windows authenticatrion, I have a domain account in the domain where sql is running, now how do I provide that domain acc info en EM to connect to that sql server. If I just select windows authenication while registering that server it gives me Cannot generate SSPI context error.

Any help will be appricaited.

Tia,
ParthYou can not do this from EM, as far as I know!
Or if you can, I would like to know that, too.

Best regards!

Access Rights

I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.

I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.

I was expecting a permission problem - this did not happen. Why?

I would appreciate your help

Thanks

YogeshWhat rights does public have?|||Originally posted by ykverma
I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.

I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.

I was expecting a permission problem - this did not happen. Why?

I would appreciate your help

Thanks

Yogesh

Check if you (or your windows group) belong to db_owner role or sysadmin role on server.|||Originally posted by Brett Kaiser
What rights does public have?

My user name is in the public role. The tables which are accessed by the stored proc are also in public but not the stored proc itself.

I've tried after bouncing the server - still it did not work.

Thanks in advance for your help

Yogesh|||Originally posted by snail
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.

I am only in the public role; my id is not set up in any of the windows group

Thanks in advance

Yogesh

Access protection on deployed SQL Server

How can we control the accesses to a SQL Server that has been deployed on customer site? Basically, we do not want user to log in the database server, even as sa, except through our application.

Any recommendation?

Thanks,

Xiaodoudou

Hi!

Actually you cannot protect the data files. So although you might restrict the user / administrators from logging on to the database you cannot prevent them deatching the database and attaching it to another instance, making it possible for them to read the content of the database. YOu would need additional file security and prevent the administrator and all other users from accessing the physical files.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In your case, I assume you have no control over the customer site - if that's the case, then you cannot enforce access only through your application.

Thanks
Laurentiu

Access Logs

Hi all,
In our win2k3 event viewer there are hundreds of failed logon attempts
for our SQL Server. Unfortunately, this log doesn't supply any
connection specific information which is what we need. Are there any
other logs that MSSQL 2000 keeps on it's own? I want to keep a record
of these attempts just in case something does happen we maybe will have
something to go on. Thanks!
--
Regards,
TroyHi,
It will be there in SQL Server Error logs as well. The location of the files
will be in \Program files\Microsoft sql server\server name\Log directory.
or you can execute XP_READERRORLOG from query analyzer .
Thanks
Hari
MCDBA
"Troy" <poster@.designguide.com> wrote in message
news:MPG.1a9d757e7936be5a98968e@.msnews.microsoft.com...
> Hi all,
> In our win2k3 event viewer there are hundreds of failed logon attempts
> for our SQL Server. Unfortunately, this log doesn't supply any
> connection specific information which is what we need. Are there any
> other logs that MSSQL 2000 keeps on it's own? I want to keep a record
> of these attempts just in case something does happen we maybe will have
> something to go on. Thanks!
> --
> --
> Regards,
> Troy

Access Denied when starting Log Reader

I've been trying to set up the publisher for transactional replication.
The distribution server is sp2 the subscriber is sp3.
I have set up replication on Server1 to be its own distributor.
However I have been recieving errors such as this below:
SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT' on
server 'Server1'. 5 - (Access is denied)
and
Execute permission denied on object 'sp_mshelp_logreader_agentid'.
or
The could not access database db1 on Server1.
I also checked the services msssqlserver and sqlserveragent on the other
server and the 'log on as' is the same on both which has admin rights.
I'm really stumped, I don't know what to check next.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200603/1
Right click on the log reader agent and make the job owner sa, then right
click again and restart it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:5d9a8c1242685@.uwe...
> I've been trying to set up the publisher for transactional replication.
> The distribution server is sp2 the subscriber is sp3.
> I have set up replication on Server1 to be its own distributor.
> However I have been recieving errors such as this below:
> SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT'
> on
> server 'Server1'. 5 - (Access is denied)
> and
> Execute permission denied on object 'sp_mshelp_logreader_agentid'.
> or
> The could not access database db1 on Server1.
> I also checked the services msssqlserver and sqlserveragent on the other
> server and the 'log on as' is the same on both which has admin rights.
> I'm really stumped, I don't know what to check next.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200603/1
|||Please make sure that SQL Server Agent and the SQL Server Service both use
the same domain account (in local admins).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
>Please make sure that SQL Server Agent and the SQL Server Service both use
>the same domain account (in local admins).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
The log reader agent is set to use SA. Still can't access database.
Both the services are using the same domain account.
I'm stumped. Thanks for the help nonetheless.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200603/1
|||Is the domain account a local administrator?
Also, can you start the sql server agent service manually?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi
I had the same problem. I applied Sp3 in the distributor. It started to
work..
P. Srinivasan MCDBA
Consultant
C-13/1,GF,Tulip Garden, Ardee City,
Sector-52,
Gurgaon-122003
India
Mobile:+91-9810321551
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eN1n8ibTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Is the domain account a local administrator?
> Also, can you start the sql server agent service manually?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>