Sunday, October 29, 2006
Imagine this default is still set on a production server, and you import a 50MB file when the datafile is at capacity.
The result is that 50 occurences of 1MB autogrow operations occur.
This is costly in terms of performance.
Now imagine multiple databases, all growing at varying rates (now that sounds like EVERY server doesn't it?)
As each of these datafiles 'expand' they take more disk space, grabbing the next available area on the disk. In this way, fragmentation of database files occurs.
As time goes on, simple database operations will rely more heavily on the disk as the server fetches data from logical files that are physically fragmented i.e. split.
Morals of this story...
1) Manually set data file sizes, allowing for expansion.
2) Review Disk/File fragmentation and perform disk maintenance
3) Do 1) & 2) regularly (stick reminders in your outlook!)
Saturday, October 28, 2006
Both 'Certificates' and 'Asymmetric keys' use Asymmetric encryption (RSA keys).
Certificates (sql 2000+) allow importing of keys from certificate files.
Asymmetric keys (sql 2005+) support importing keys from strong name files or sql assemblies.
Certificate files (.CER) are created using Microsoft Cerfificate Services (Windows Server 2000, 2003) or Active Directory Certificate Services (Windows 2008)
See my notes on cryptography, here.
Friday, October 27, 2006
Some Terminology -
Key - A sequence of bits, used by encryption algorithmns.
A 40 bit key > 10011110 01010101 00001010 01101001 00011100
Plaintext - The original message / content that was encrypted
Ciphertext - The encrypted version of a message
Cryptographic Strength - Time / Resources to break the ciphertext.
Cryptography works by applying a cipher or algorithm to plaintext to produce the ciphertext.
A key is used in conjunction with the algorithm. This way, the same algorithmn encrypts differently with different keys.
Cryptographic strength is dependent on a) algorithmn strength and b) key secrecy
Cryptographic Systems : Symmetric vs Asymmetric
Symmetric Cryptography (Private-key cryptography) -
Both sender & receiver have the SAME key.
Asymmetric Cryptography (Public-key cryptography)
Both sender & receiver have a PAIR of keys (public & private).
The public key is published (eg in the email signature of PGP users) whereas the private key is exactly that.
The receiver's key can decrypt messages encyrpted with the sender's key (& vice versa)
Any knowing the public key can Encrypt, but only people knowing the private key can Decrypt.
Asymmetric decryption is much slower and key sizes are bigger than symmetric keys.
Simply, this refers to the key length used to encrypt data.
1 ASCII character = 8 bits, hence 128bit encyrption with an ASCII key means (8 x 13 = 128), a 13 character key length.
1 HEX character = 4 bits, hence 128bit encryption with a HEX key means (4 x 26 = 128), a 26 character key length
The number of combinations possible (i.e sequential attempts required to crack ciphertext via brute force) can be expressed as 2^n, where n is the represents the bit strength of the key.
For 40 bit encryption >
2^40 = 1099511627776 combinations
For 64 bit encryption >
2^64 = 18446744073709551616 combinations
For 128 bit encryption >
2^128 = 3.40282367 × 10^38 combinations
For 256 bit encryption >
2^256 = 1.1579208923731619542357098500869 x 10^77 combinations
Tuesday, October 24, 2006
Demonstrated for simplicity here, but useful in the update criteria become complicated >
UPDATE person SET surname = updated.surname FROM person INNER JOIN ( SELECT correctdata.pk, correctdata.title, correctdata.surname, incorrectdata.title, incorrectdata.surname FROM person_import correctdata INNER JOIN person incorrectdata ON correctdata.pk = incorrectdata.pk WHERE incorrectdata.surname is null ) AS updated ON person.pk = updated.pk
Friday, October 13, 2006
Saturday, October 7, 2006
Thursday, October 5, 2006
Wednesday, October 4, 2006
Attempting to set up a simple backup job on another team's sql box. Went through all the wizard steps to create a backup job for them. Failed at the final hurdle, with >
Create maintenance plan failed.Additional information:Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
This is due to not having Integration Services installed.
Bizarre that maintenance plans should need them, but there you go! Mind you, it was an unpatched system that I'm not responsible for, so maybe this error becomes more obvious in a later release.http://support.microsoft.com/kb/909036