Bad Autogrowth

The default Autogrowth for a new database is 'By 1 MB, unrestricted growth'

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!)

Cryptographic Keys in SQL 2005

Certificates and Asymmetric keys (#updated June 2008)


Basics :
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.

Cryptography & Keys

Cryptography is securing (encrypting and ultimately decrypting) data so that sensitive information can be stored safely or transmitted across insecure networks.

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.

n-bit Encryption
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

UPDATE using inner SELECT

Using an UPDATE with an inner joined results set.
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

SQL Code Searching - Views

-- view search

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION LIKE '%q=%' 

SQL Code Searching - Stored Procedures

-- sp search

select routine_name
    from information_schema.routines
    where routine_definition like '%code%'
    and routine_type = 'procedure'

SQL Code Searching - Views

-- view search

select table_name
    from information_schema.views
    where view_definition like '%code%'

SQL Schema Searching - Columns

-- column search

select table_name
from information_schema.columns
where column_name = 'columnname'

UltraEdit Macro - HTML Formatting

InsertMode
ColumnModeOff
HexOff
UnixReOn
TabsToSpaces
Find "&"
Replace All "&"
Find ">"
Replace All ">"
Find "<"
Replace All "<"
Find "  "
Replace All "  "
Find "^p"
Replace All "
^p" 

2005 Maintenance Plan - Error 14234

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