Primary Database Backup in Log Shipping Scenario

I've setup a Log Shipping scenario for one of our databases. This works fine.
Also, every night a full database backup of the primary database is made to a
disk location. Normally, the transaction log would be truncated after a full
database backup. This behavior is unwanted in a Log Shipping scenario since
the content of the transaction log needs to be applied to the secondary
database. We've seen that since the Log Shipping scenario is in place, the
log is no longer truncated after the full database backup of the primary
database. How does the primary database "know" not to truncate the
transaction log? In other words, I'd like to know how the full database
backup mechanism works in a Log Shipping environment.
Are you saying you with issue a backup log with truncate_only statement
after your backup has finished?
ie
backup log fulltext with truncate_only
If so, this will break your log shipping chain and should not be done.
The way a log shipping works is a backup is done. Embedded in the backup is
the LSN (Log Sequence Number) for the transaction log. The next log backup
you do contains log enteries starting at the lsn for this last backup. So
you can restore the log to the database backup where the lsn matches the lsn
embedded in the backup.
If you then deploy log shipping and do a backup the backup entry is in the
log but it is ignored, so you can continue to apply subsequent log dumps
without breaking the chain.
Does this answer your question?
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
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:542507D8-50F4-4CEC-BEE5-36DF1EBBA607@.microsoft.com...
> I've setup a Log Shipping scenario for one of our databases. This works
> fine.
> Also, every night a full database backup of the primary database is made
> to a
> disk location. Normally, the transaction log would be truncated after a
> full
> database backup. This behavior is unwanted in a Log Shipping scenario
> since
> the content of the transaction log needs to be applied to the secondary
> database. We've seen that since the Log Shipping scenario is in place, the
> log is no longer truncated after the full database backup of the primary
> database. How does the primary database "know" not to truncate the
> transaction log? In other words, I'd like to know how the full database
> backup mechanism works in a Log Shipping environment.
|||Hello Hilary,
I'll clarify my question: I'd like to know how SQL Server "knows" whether a
Log Shipping scenario is in place, and I'd like an explanation to the
subsequent difference in behavior when performing a Full Database backup
(truncate the log in "normal" operation and not truncating the log in a Log
Shipping scenario).
Best regards, Wilbert
|||SQL Server does not know if a log is being shipped or not. Log shipping fits
into the existing point in time recovery scheme that sql server uses to
recover databases.
If you truncate a log it will remove all committed entries in that log.
Logged open transaction and other non-committed commands will remain in the
log since the last checkpoint. There is no log truncation after a back up in
full recovery model, not in bulk logged. The problem with bulk logged is
that minimally logged operations are minimally logged and will destroy your
log shipping chain.
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
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:00B5F549-71CE-4089-A81A-A537B6BA212F@.microsoft.com...
> Hello Hilary,
> I'll clarify my question: I'd like to know how SQL Server "knows" whether
> a
> Log Shipping scenario is in place, and I'd like an explanation to the
> subsequent difference in behavior when performing a Full Database backup
> (truncate the log in "normal" operation and not truncating the log in a
> Log
> Shipping scenario).
> Best regards, Wilbert
|||Hello Hilary,
Isn't it so that after a full database backup the committed entries in the
log are removed?
That's the origin of my question: in "normal" operation these entries are
removed from the log, while in a log shipping scenario this doesn't seem to
be the case. This led me to believe that SQL Server somehow "know" that log
shipping is taking place and that the entries in the log should be retained
until the next log backup.
Best regards, Wilbert
|||No, the log is insert and read only. What happens is that it is a record of
all of your database activity. Before anything hits disk it is written to
your log. When you issue a checkpoint the checkpoint is written to the log
and all dirty pages (including uncommitted pages are written to disk).
Should a failure occur the log is consulted to find the last checkpoint and
uncommitted transactions which occurred after the last checkpoint are
removed from disk and committed transactions which committed after the last
checkpoint are written to disk.
When you do a db backup nothing happens to the log other than the backup
happened. When you do a log backup, the internal vlf's in the log buffer I
believe have their status changed to 0 if there are no active transactions
in them. But the log itself does not have anything happen to it.
The above applies to full recovery model.
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
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:668C79A5-01E2-4336-94D9-EB76B8D23866@.microsoft.com...
> Hello Hilary,
> Isn't it so that after a full database backup the committed entries in the
> log are removed?
> That's the origin of my question: in "normal" operation these entries are
> removed from the log, while in a log shipping scenario this doesn't seem
> to
> be the case. This led me to believe that SQL Server somehow "know" that
> log
> shipping is taking place and that the entries in the log should be
> retained
> until the next log backup.
> Best regards, Wilbert

Preventing Enterprise Manager access from Windows users

Hello,
Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
as SQL logins, and those logins are created as database users with datareade
r
and/or datawriter roles to a specific database. An intranet web site is
created to allow the ADS users in those ADS groups access to the data.
Problem: How do I prevent the ADS users in those ADS groups from running
Enterprise Manager (or creating their own ODBC connections, etc.) and messin
g
with my database? Or, put another way, how do I prevent the users from using
their own interface to the database?
Thanks much,
-TonyA common practice for something like this would be too...
1. Create Stored Procedures that the application uses to manipulate data.
2. Assign those groups Execute Permissions on the Stored Procedures.
3. Deny Data Reader and Data Writer to those groups.
If you are using ADHOC in your program this is going to be a little tougher.
What do you think?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
> Hello,
> Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
> as SQL logins, and those logins are created as database users with
> datareader
> and/or datawriter roles to a specific database. An intranet web site is
> created to allow the ADS users in those ADS groups access to the data.
> Problem: How do I prevent the ADS users in those ADS groups from running
> Enterprise Manager (or creating their own ODBC connections, etc.) and
> messing
> with my database? Or, put another way, how do I prevent the users from
> using
> their own interface to the database?
> Thanks much,
> -Tony
>|||Warren,
Thanks for the info. That does sound like a good solution. I'm assuming this
would allow users to run Enterprise Manager and see my databases and objects
listed, but not interact with them.
BTW, these applications are web apps using .net. So, perhaps another
alternative would be for me to autherize and authenticate users to the web
site using IIS and ADS groups, then use a SQL login and password (encrypted
in the web.config file) to handle db access. Adding your suggestion to this,
I could restrict the SQL login to execute only the needed sprocs.
Seems like this would prevent any access to my database by those ADS users
via Enterprise Manager or whatever. If the SQL login should be hacked, at
least it would only allow execute on the sprocs and nothing else.
Thanks,
-Tony
"Warren Brunk" wrote:

> A common practice for something like this would be too...
> 1. Create Stored Procedures that the application uses to manipulate data.
> 2. Assign those groups Execute Permissions on the Stored Procedures.
> 3. Deny Data Reader and Data Writer to those groups.
> If you are using ADHOC in your program this is going to be a little toughe
r.
> What do you think?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
>
>|||Hello Tony
Please drop me a note if the following description sounds interesting to
you.
Best regards
Adrian
Here we go:
Corrupt users and compromised user-accounts, cf Phishing, account for the
majority of attacks in the commercial world. We present a two-stage
anti-corruption system (ACS) for database servers that, in the first stage,
makes it very hard to gain access to a database with an unauthorised client
application or from an unauthorised client PC and, in the second stage,
provides
early precise hints on users performing suspicious activities. Our first
implementation of the ACS is for the MS SQL Server 2000. It uses only
documented
functions and relies completely on mechanisms and data already provided by
the
database server. Its smooth and efficient operation for several months in a
middle-sized decentralised company proves it a valuable addition to the pool
of
security measures.
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:5D786B85-382F-49DD-8CE8-3B8A77DB8EF4@.microsoft.com...[vbcol=seagreen]
> Warren,
> Thanks for the info. That does sound like a good solution. I'm assuming
> this
> would allow users to run Enterprise Manager and see my databases and
> objects
> listed, but not interact with them.
> BTW, these applications are web apps using .net. So, perhaps another
> alternative would be for me to autherize and authenticate users to the web
> site using IIS and ADS groups, then use a SQL login and password
> (encrypted
> in the web.config file) to handle db access. Adding your suggestion to
> this,
> I could restrict the SQL login to execute only the needed sprocs.
> Seems like this would prevent any access to my database by those ADS users
> via Enterprise Manager or whatever. If the SQL login should be hacked, at
> least it would only allow execute on the sprocs and nothing else.
> Thanks,
> -Tony
>
> "Warren Brunk" wrote:
>