Encryption of trasaction logs

Is it possible to encrypt the transaction log during log shipping? How?

While there is no explicit log encryption in SQL Server 2005, any entries that are encrypted will be treated as binary data so it will remain encrypted during log shipping as well.

We do plan on offering enhanced encryption features for SQL Server 2008, we will post any further info as soon as they are publicly available.

Hope this helps, please let us know if you have any further questions.

Sung

|||is there any third party utilites that support encryption that are supported by Microsoft ?|||

Hi,

I'm not currently aware of any third party utilities that are supported by Microsoft. We may have a few partners that we perhaps recommend. I am currently checking up on this and will post any info that I find.

UPDATE: While we don't have any specific recommedations in this space, we do have a large number of third parties who have developed solutions in this area. They would be the ones who would directly support any solutions that they may have. Please contact software vendors to discuss any options that they may have or perhaps may provide.

Thanks,

Sung

Encryption of trasaction logs

Is it possible to encrypt the transaction log during log shipping? How?

While there is no explicit log encryption in SQL Server 2005, any entries that are encrypted will be treated as binary data so it will remain encrypted during log shipping as well.

We do plan on offering enhanced encryption features for SQL Server 2008, we will post any further info as soon as they are publicly available.

Hope this helps, please let us know if you have any further questions.

Sung

|||is there any third party utilites that support encryption that are supported by Microsoft ?|||

Hi,

I'm not currently aware of any third party utilities that are supported by Microsoft. We may have a few partners that we perhaps recommend. I am currently checking up on this and will post any info that I find.

UPDATE: While we don't have any specific recommedations in this space, we do have a large number of third parties who have developed solutions in this area. They would be the ones who would directly support any solutions that they may have. Please contact software vendors to discuss any options that they may have or perhaps may provide.

Thanks,

Sung

Enabling "not for replication" contraint on a mass level

HI,
sql server 2000,uni directional transaction replication with initial
snapshot . Can not be simpler than this.
My database has around 100 tables and ever time i setup replication, i have
to make sure that none of the referential integrity constraints are copied
over to the subscriber because they are not set for "not for replication".
And if i want to set it up , i might have to go to each and every table and
specify this contraint. My questions are
1. is there any way i can have all the referential integrity contraints on
the subscriber and still not have "not for replication" checked?
2. if the answer is NO , then is there any way , any script i could use to
mass update all the PKs\ FKs with "Not For Replication" constraint?
Thanks
Muzammil
AFAIK - there is no way of doing this - but why do you want to enforce DRI
on the subscriber? Normally the subscriber is read only.
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
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:34ACBAE1-9F82-46CB-B844-B5B68F2B4798@.microsoft.com...
> HI,
> sql server 2000,uni directional transaction replication with initial
> snapshot . Can not be simpler than this.
> My database has around 100 tables and ever time i setup replication, i
have
> to make sure that none of the referential integrity constraints are copied
> over to the subscriber because they are not set for "not for replication".
> And if i want to set it up , i might have to go to each and every table
and
> specify this contraint. My questions are
> 1. is there any way i can have all the referential integrity contraints on
> the subscriber and still not have "not for replication" checked?
> 2. if the answer is NO , then is there any way , any script i could use to
> mass update all the PKs\ FKs with "Not For Replication" constraint?
> Thanks
> Muzammil
|||Hilary, thanks for you response.
Yes even though the subscriber is read only , but the client just can not
accept that the database does not have any referencial integrity contraints.
I still can not beleive that there is no way i could write down a script to
get every table from Sysobjects , find the referential integrity contraints
and append the "not for replication" contraints on them. Please suggest.
Thanks
Muzammil
"Hilary Cotter" wrote:

> AFAIK - there is no way of doing this - but why do you want to enforce DRI
> on the subscriber? Normally the subscriber is read only.
> --
> 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
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:34ACBAE1-9F82-46CB-B844-B5B68F2B4798@.microsoft.com...
> have
> and
>
>

Empty trans log in Simple model

I have a database SQL Server 2005 Express, that's
using the Simple recovery model.
There's quite a big transaction log for this database,
how do I empty it when it's the Simple model?
Besides, the log file hasn't been updated (acc. to
the Windows file date) since yesterday while the MDF
file has. Is this normal?Run DBCC SQLPERF (logspace) on the instance to see if any of the space is actually being used. If it is, run DBCC OPENTRAN in the database to see who has the open transaction.

If the space is genuinely empty, and you do not suspect there are any giant transactions that run on this thing, you can safely shrink back the log. I find the Windows last updated dates generally doesn't mean a lot when it comes to database files.|||I couldn't manage to shrink the file. I tried setting the
initial file size down to 2MB but it remained 15MB as
it was (without any error message).
Finally, I re-created the database instead, it had
very little content anyway so it was quite easy.

Empty the transaction log

Hi i would like to know how to empty the transaction log programmaticly.

Someone got a clue? :)Maybe create a stored procedure that runs a transaction log backup and then calls DBCC ShrinkDatabase (If you want to shrink it)? The user you use to connect to the database will need the proper permissions.|||I think backing up the transaction log automatically truncates the inactive portion of the log. Or is that incorrect...?

-Ian|||i sure would like to know because i have a program that uses transactions and uses it extremely frquently so the log grows in size in a geometric rate :)|||Every change to the database is written to the log first whether it's within a transaction or not. The log grows depending on the write activity against the database, not whether or not a transaction is used.|||ok, but how do i empty it programmaticly?
i wanna build a page where the user can empty it.|||When you backup the transaction log it truncates it. You can even do a BACKUP LOG <dbname> WITH TRUNCATE_ONLY which just removes the extra log entries, but its not recommended. Either option will truncate the log, so it most likely will not grow for a while, but it does not shrink the actual log, which you need to do a DBCC SHRINKDATABASE or DBCC SHRINKFILE to do. The SQL Server Books Online tells you all about it onShrinking the Transaction Log andTruncating the Transaction Log.