50 TOP EMAIL SERVICE PROVIDERS

Attention-grabbing headlines aside, email is not dead yet. It’s true that text messaging, instant messaging and social networking provide an alternative means of communication to email for many.
Continue reading “50 TOP EMAIL SERVICE PROVIDERS”

Advertisements

Encrypting Databases with Transparent Data Encryption

Quantum-Encryption-640x353 Transparent Data Encryption (TDE) enables you to encrypt an entire database.
TDE protects the database against unauthorized third parties gaining access to the hard disks or backups on which the database is stored. TDE encrypts the database by using a Database Encryption Key (DEK) that is stored in the database boot record.
The DEK is in turn protected by the database master key, which is in turn protected by the service master key. You can use BitLocker Drive Encryption, a full-volume encryption method supported by Windows Server 2008 and Windows Server 2008 R2, although this will not ensure that database backups are encrypted.

NOTE TDE AND TEMPDB
If any database on the instance uses TDE, the tempdb system database will also be encrypted.
To use TDE to encrypt a database, you must perform the following steps:

1. Create the master encryption key.

2. Create the certificate protected by the master key.

3. Create a DEK and protect it by using the certificate.

4. Encrypt the database.

The first step in deploying TDE involves creating a master encryption key. You do this by using the CREATE MASTER KEY ENCRYPTION BY PASSWORD statement. For example, you can accomplish that by using the following query:

USE master;
GO CREATE
MASTER KEY ENCRYPTION BY PASSWORD = ”;
GO

After you have created the master encryption key, the next step involves creating the certificate that will be used to encrypt the database. You can accomplish this by using the CREATE CERTIFICATE statement. For example, to create a certificate named ServerCertificate that uses the subject name Server Certificate, use the following query:

CREATE CERTIFICATE ServerCertificate WITH SUBJECT = ‘Server Certificate’;
GO

When the master key and certificate are in place, you can create the DEK for the specific database. You do this by using the CREATE DATABASE ENCRYPTION KEY statement. For example, the following query creates a DEK for the AdventureWorks2012 database:

USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
GO

After all the appropriate keys and certificates are in place, you can encrypt the database by using the ALTER DATABASE statement. For example, to encrypt the AdventureWorks2012 database, use the following query:

ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

When using TDE, you should create a backup of the server certificate in the master database. If you lose the database server without backing this up, you cannot access data in a database protected by TDE. You can use the BACKUP CERTIFICATE statement to cre- ate a backup of the certificate and private key, both of which are required for certificate recovery. The private key password does not have to be the same as the database master key password. For example, the following code, when run from the master system database, creates a backup of the ServerCertificate certificate to a file called ServerCertExport and a PrivateKeyFile private key:

BACKUP CERTIFICATE ServerCertificate
TO FILE = ‘ServerCertExport’
WITH PRIVATE KEY (
FILE = ‘PrivateKeyFile’,
ENCRYPTION BY PASSWORD = ” );
GO

SQL Server will write these backup files to the MSSQLDATA directory of the instance.

Fireworks for Bonfire Night Safety CSS Skills

With Bonfire Night approaching in the UK, Col Morley and Igor Krestov of Blue Claw decided to put their CSS skills to work creating this fire safety infographic aimed at school-age children.

http://visual.ly/bonfire-night-safety?view=true

When viewed in a suitable browser, some elements of the infographic will animate. Rather than aiming at supersmooth, in-your-face visual effects, the flickering of the flames and the sparkling of the sparklers is simple, subtle – and, we think, all the more effective for it.

Inspired by Firefox’s homepage Olympic flame over the summer of 2012, the infographic was created using layered graphics and CSS3 to aid in the process of transition of scale, movement and speed, explains Morley. “The bonfire is made up of several layered ‘fire’ shapes then playing around with key frames enabled stretching, opacity and by adding a warm glow renders a realistic flame effect.

Inspired by Firefox’s homepage Olympic flame over the summer of 2012, the infographic was created using layered graphics and CSS3 to aid in the process of transition of scale, movement and speed, explains Morley. “The bonfire is made up of several layered ‘fire’ shapes then playing around with key frames enabled stretching, opacity and by adding a warm glow renders a realistic flame effect.

“The spark that appears throughout the page was created using a similar technique. Three images were used to pulsate at different rates of scale, similar effect to how we animated the ‘fearful cat’. The movement of the eyes on the hot dog was produced by mouse tracking code adding further interest to those who spotted that.

“The styling of the traditional blackboard theme gives the piece the right level and tone for the audience and enabled us to experiment with textures and fonts.”

bomfirea2

How to examine index fragmentation in SQL Server 2012

In this exercise, execute the following T-SQL code, examine the fragmentation reported, and defragment the indexes that show 100 percent fragmentation.

SELECT SCHEMA_NAME(so.schema_id) AS [SchemaName],
OBJECT_NAME(idx.OBJECT_ID) AS [TableName],
idx.name AS [IndexName],
idxstats.index_type_desc AS [Index_Type_Desc],
CAST(idxstats.avg_fragmentation_in_percent AS decimal(5,2)) AS [Frag_Pct],
idxstats.fragment_count,
idxstats.page_count,
idx.fill_factor
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) idxstats
INNER JOIN
sys.indexes idx
ON idx.OBJECT_ID = idxstats.OBJECT_ID
AND idx.index_id = idxstats.index_id

INNER JOIN sys.objects so
ON so.object_id = idx.object_id

WHERE idxstats.avg_fragmentation_in_percent > 20

ORDER BY idxstats.avg_fragmentation_in_percent DESC

Index fragmentation
Index fragmentation

How to create a filtered index

This is the quick-and-dirty method of creating a filtered index:

T-SQL
USE [AdventureWorks2012]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [NC_Person_Address_City_spi_pc] ON [Person].[Address] ([City] ASC) INCLUDE ([StateProvinceID], [PostalCode])
WHERE [City] = ‘Seattle’ ON [PRIMARY] GO

You can find more about filtered indexes on Introduction to SQL Server Filtered Indexes by Seth Delconte