845 reputation
717
bio website improve.dk
location Aarhus, Denmark
age 29
visits member for 3 years, 2 months
seen Aug 19 at 13:28

CTO at iPaper A/S, speaker, consultant, SQL Server MVP & C# groupie.

@improvedk on Twitter


Jun
4
awarded  Yearling
Oct
23
comment Securely deleting data from SQL Server logs
Your method for removing old data won't actually be fully secure. Rebuilding does not necessarily overwrite the unallocated pages, so they may still be left behind. Dropping the old file won't zero it out, it'll just remove the SQL + NTFS metadata for the file, so it can still be restored by most recovery tools, after which you could go about retrieving non-overwritten data from the MDF file itself. If you do copy to a new disk and wipe the old, sure, that should get rid of it :)
Oct
23
comment Securely deleting data from SQL Server logs
@Doct0rZ Yes - if someone were to find the certificates from the master database, the data encrypted by TDE would be retrievable. This is however way more complicated and at some point you'll need to consider the level of security needed. Just adding TDE will make it so much more difficult to retrieve the data compared to raw unencrypted MDF files.
Oct
22
comment Securely deleting data from SQL Server logs
If you are not using Transparent Data Encryption, deleted data is easily recovered from MDF files. When you drop a table or delete/truncate the data, the data is actually left behind, only metadata is deleted. Data isn't gone until those data pages are overwritten with new data, and seeing as you can't determine where new data is written, you can't be sure it has been overwritten.
Jul
26
awarded  Good Question
Jun
4
awarded  Yearling
Mar
28
comment Which database could handle storage of billions/trillions of records?
Given that this seems to track continuous data, I'd guess a single datastream would generate multiple entries? In that case one could probably save enormous amounts of storage by normalizing the design and splitting stuff like (source, target, etc) into its own entity and then referring that, perhaps by a connection_id value. For this to perform it would require you'd be able to cache the concurrent connection_id's on the collector side though.
Mar
28
comment Which database could handle storage of billions/trillions of records?
I can't get the 7 bytes of overhead to add up. Each record would require status bytes A & B, the null bitmap pointer, two bytes for the null bitmap count and four bytes for the actual null bitmap. Furthermore you'll also need two bytes for the record offset array - adding up to a total overhead of 12 bytes per record. Also note that you won't be able to fill up the 8096 bytes in the body as SQL Server reserves part of it beyond the header. I wouldn't count on using more than 8054 body bytes per page.
Mar
26
comment Would I lose data if I change a column from varbinary(8000) to varbinary(max)?
@TomTom You can read about how both the varbinary(8000) and varbinary(MAX) will store it in row, or overflow using a BLOB INLINE ROOT structure for too large values in What is the size of the LOB pointer for MAX types. Or if you want to look at source, feel free to look at the complex column handling for types 2 & 4 in OrcaMDF's Record source
Mar
26
comment Would I lose data if I change a column from varbinary(8000) to varbinary(max)?
Sorry @TomTom but that's not correct. Varbinary(MAX) and varbinary(8000) can both be stored in-row as well as out-of-row. Image (the classic varbinary(MAX)) type on the other hand, is always stored out of row.
Mar
26
comment Would I lose data if I change a column from varbinary(8000) to varbinary(max)?
Are you sure this isn't just metadata? However varbinary(8000) is stored, varbinary(max) can store it in the exact same format, so I can't see why it'd need to make any physical changes. I'm not sure though - haven't tested.
Mar
7
comment Is it possible to make one column readonly?
I do not believe there is any way around this other than implementing triggers and only allowing create/update/delete statements through procedures.
Jan
24
awarded  Necromancer
Jan
19
comment Insert with 0 Writes?
Excellent test, good answer. Seems like the Write column documentation might be somewhat simplified compared to the real life values.
Jan
19
answered Insert with 0 Writes?
Jan
15
comment Page Header DB Frag ID Property
I've not stumbled upon "DB Frag ID" before, it would seem that's new to 2012. However, looking at the header for 2008R2 and earlier, only bytes 0-63 are in use: github.com/improvedk/OrcaMDF/blob/master/src/OrcaMDF.Core/… Thus, if they added a new property, it would make sense for it to be in bytes 64+
Dec
4
awarded  Revival
Oct
26
awarded  Notable Question
Sep
28
awarded  Custodian
Sep
28
reviewed Reviewed What can cause a mirroring session to timeout then failover? SQL Server 2005