non logged transactions with transactional replication

I know that if you're doing log shipping and someone runs a fast bcp ( non
logged ) activity against the database that subsequent transaction log dumps
won't load until you do another full dump and load. This can be a problem,
because of the time involved in dumping and loading. How does transactional
replication handle non logged activity? The same way as log shipping? Which
would be bad, or does the data that was inserted or changed as a result of
the non logged transaction make it to the target server?
John,
it is my understanding that in the full recovery model, every change to the
database is logged, so there's no issue with transactional replication. In
SQL Server 2005 the transaction log may be backed up after minimally logged
operations (eg Bulk load operations in the bulk-logged model) so log
shipping is also possible.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But, if you run a transaction that is truly minimally logged (BCP in
bulk-logged recovery model), you will have to reinitialize.
I don't understand why that is considered "bad". A minimally logged
transaction does not write everything to the tran log. Therefore, if you
were allowed to backup the tran log and restore it, you would wind up with a
corrupted database. With respect to replication, a minimally logged
transaction doesn't even hit the replication engine, so your publisher and
subscriber will completely out of synch and since there is no track of the
data, there is no ability to resynch them except by reinitializing.
(Replication doesn't even detect that a minimally logged transaction
occured.)
This is not an issue for either replication or log shipping. It is a side
effect of the way the transaction was executed. The replication and log
shipping features are simply protecting the integrity of the databases
against something they can not control.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OMFC2BDLGHA.648@.TK2MSFTNGP14.phx.gbl...
> John,
> it is my understanding that in the full recovery model, every change to
> the database is logged, so there's no issue with transactional
> replication. In SQL Server 2005 the transaction log may be backed up after
> minimally logged operations (eg Bulk load operations in the bulk-logged
> model) so log shipping is also possible.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It doesn't handle not logged activity because it is based on logged events
and you can't do non-logged events on tables you are replicating.
Non-logged activity will occur when the bulk copy recovery model is selected
and
1) you do certain operations, i.e. create index, select into, etc
2) fast bcp - which requires no indexes on the table among other things
transactional replication requires a table with an index, so you can't do
fast bcp with it. It also requires publications built on preexisting tables
so you won't be able to replicate a table you are selecting into.
Hilary Cotter
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
"John" <John@.discussions.microsoft.com> wrote in message
news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>I know that if you're doing log shipping and someone runs a fast bcp ( non
> logged ) activity against the database that subsequent transaction log
> dumps
> won't load until you do another full dump and load. This can be a problem,
> because of the time involved in dumping and loading. How does
> transactional
> replication handle non logged activity? The same way as log shipping?
> Which
> would be bad, or does the data that was inserted or changed as a result of
> the non logged transaction make it to the target server?
|||Yes, you can in fact perform minimally logged operations on tables that are
being replicated. Writetext and updatetext come to mind.
Also there is no such thing as a "non-logged" event. Every single operation
that manipulates data will place some kind of entry into the log.
Therefore, the correct term is "minimally logged" and it has been for about
a decade now.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLcsW4vLGHA.1192@.TK2MSFTNGP11.phx.gbl...
> It doesn't handle not logged activity because it is based on logged
> events and you can't do non-logged events on tables you are replicating.
> Non-logged activity will occur when the bulk copy recovery model is
> selected and
> 1) you do certain operations, i.e. create index, select into, etc
> 2) fast bcp - which requires no indexes on the table among other things
> transactional replication requires a table with an index, so you can't do
> fast bcp with it. It also requires publications built on preexisting
> tables so you won't be able to replicate a table you are selecting into.
> --
> Hilary Cotter
> 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
> "John" <John@.discussions.microsoft.com> wrote in message
> news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>

Non domain user receive access denied error

I am trying to run a VBScript that uses a SQL user to access a 2000 SQL SP4
clustered instance.
The script runs successfully only when I am logged on with a domain account.
What can the reason for this be?
Thanks in advance,
Ido Friedman
My first thought is that the VBScript is using NT authentication instead of
SQL Authentication.
Can you post the script?
Keith Kratochvil
"Ido friedman" <Idofriedman@.discussions.microsoft.com> wrote in message
news:A1B97509-42C7-4EBB-9BA3-786FA81E682F@.microsoft.com...
>I am trying to run a VBScript that uses a SQL user to access a 2000 SQL SP4
> clustered instance.
> The script runs successfully only when I am logged on with a domain
> account.
> What can the reason for this be?
>
> Thanks in advance,
> Ido Friedman

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. Sam
Are you sure that it is related to admin rights? Maybe you just need to add a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin rights
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis backup
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>
|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark
|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. SamAre you sure that it is related to admin rights? Maybe you just need to add
a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin righ
ts
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis back
up
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

No trusted SQl connection

Hello,
I am using the MSDE from Visual Studio.NET. I can access the SQL Server from
the server explorer only if I am logged into 2000 as administrator (I
installed MSDE using admin). If I try to connect from another login on the
same machine, I get the following error "Login failed. Not associated with a
trusted SQL Server connection". Any ideas?
Thanks,
Rick
hi Rick,
"rick_md" <rick_mc_806@.msn.com> ha scritto nel messaggio
news:CrqdnTC2aJqMiPLcRVn-qA@.comcast.com
> Hello,
> I am using the MSDE from Visual Studio.NET. I can access the SQL
> Server from the server explorer only if I am logged into 2000 as
> administrator (I installed MSDE using admin). If I try to connect
> from another login on the same machine, I get the following error
> "Login failed. Not associated with a trusted SQL Server connection".
> Any ideas?
that means that your non admin Windows account is not a regitered and
authorizated login to access SQL Server...
you have to log in as administrator and grant all non admin Windows account
privilege to access SQL Server
have a look at
http://support.microsoft.com/default...N-US;q325003#8
a good article about security you should read can be found at
http://support.microsoft.com/default...b;en-us;325022
we are here referring to Windows authentication...
if you like to log in in your MSDE instance using SQL Server authentication,
providing "User" and "pasword" credential, you have to modify the
authentication mode to Mixed mode..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply