Assymetric Encryption – Remove TDE

ALTER DATABASE DatabaseName
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a
value of  1 in the query below. */

SELECT encryption_state, d.name, percent_complete
FROM sys.dm_database_encryption_keys e
INNER JOIN
sys.databases d ON
d.database_id = e.database_id;
GO
USE DatabaseName;
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE master
go
DROP CERTIFICATE TDE_Certificate
go
DROP MASTER KEY
go

Note: IF All of tde is removed from the server, TEMPDB will still show up as encrypted. This is by design to make sure any residual data from an encrypted database within the TempDB is still encrypted and secured – even if the actual database no longer resides on that server. Tempdb will be free from any encryption on a restart of sql server.

 

Assymmetric Encryption – Useful DMVs

–Retrieving a list of certificates installed in the current database
SELECT
name,
certificate_id,
pvt_key_encryption_type_desc,
subject,
cert_serial_number,
start_date,
expiry_date,
thumbprint
FROM sys.certificates;

–Listing encrypted databases
SELECT
DB_NAME(database_id) AS database_name,
database_id,
CASE encryption_state
WHEN 0 THEN N’No database encryption key present, no encryption’
WHEN 1 THEN N’Unencrypted’
WHEN 2 THEN N’Encryption in progress’
WHEN 3 THEN N’Encrypted’
WHEN 4 THEN N’Key change in progress’
WHEN 5 THEN N’Decryption in progress’
END AS encryption_state,
key_algorithm,
key_length,
percent_complete
FROM sys.dm_database_encryption_keys;

 

Assymetric Encryption – Moving an encrypted database to another server

Step 1: Create Master Key - Only if one doesn't already exist in the 
destination.
Note: One Master Key per Instance
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
Step 2: Restore/Recreate the certificate used to encrypt the database that 
needs to be moved.
-- Recreate the server certificate by using the original server certificate 
    backup file. 
-- The password must be the same as the password that was used when the backup
    was created.

CREATE CERTIFICATE TestSQLServerCert 
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO

Step 3:
Create the database on the destination server using the mdf file or restore 
from a backup.

From 
http://technet.microsoft.com/en-us/library/ff773063.aspx

 

Assymmetric encrpytion – Enabling TDE

What is TDE
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

TDE and TempDB
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.

Enabling TDE
The TDE model uses a new specialized symmetric key called the database encryption key (DEK) to encrypt your database. The DEK is protected by a server certificate—a certificate created in the master database. The server certificate is protected by the master database DMK, which must be protected in turn by the SMK.

The first step to enabling TDE is to create a DMK in the master database (if one doesn’t already exist).
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N’StrongPassword’;
GO

After you’ve created the master database DMK you need to create a server certificate in the master database. This is the certificate that will protect the DEK that you’ll create in subsequent steps.

USE master;
GO
CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT = N’TDE Encryption Server Certificate’;
GO

After you create your server certificate you should immediately back it up using the BACKUP CERTIFICATE statement. Immediately store the certificate backup in a secure facility. You’ll need the server certificate if you want to restore the encrypted database from a backup or attach the database files to a different server in the future.

USE master;
GO
BACKUP CERTIFICATE TDE_Certificate
TO FILE = N’c:\Server_Certificate.cer’
WITH PRIVATE KEY
(
FILE = N’c:\Server_Certificate.pvk’,
ENCRYPTION BY PASSWORD = N’AnyStrongPassword’
);
GO
Now that you have a server certificate you can switch back to your user database and create a DEK with the CREATE DATABASE ENCRYPTION KEY statement. You can only create one DEK per user database.

USE AdventureWorksLT2008;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

Finally enable encryption

ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO

 

Switching databases dynamically

DECLARE @vsql NVARCHAR(MAX)

DECLARE @sp_executesql NVARCHAR(50) = QUOTENAME(‘Database Name here’) + ‘.sys.sp_executesql’;

SET @vsql = ‘—enter code here —‘

EXEC @sp_executesql @vsql;

 

 

Indexes

Heap: A crude stack of data with a row identifier as a pointer to the storage location. This data is not ordered or searchable except by walking through the adta, row by row- scan.

When a clustered index is placed on a table, the key values of the index establish an order for the data. Further, with a clustered index, the data is stored with the index so that the data itself is now ordered. When a clustered index is present, the pointer on the non clustered index consists of the values that define the clustered index.

Tables with indexes require more storage and memory space for the index pages in addition to the data pages of the table. DML statements take longer and more processing time is required to maintain the indexes of constantly changing tables. This is because, unlike a SELECT statement, data manipulation queries modify the data content of a table. If an INSERT statement adds a row to a table, then it also has to add a row in the index structure. If the index is a clustered index, the overhead is greater still, because the row has to be added to the data pages themselves in thr right order, which may require other data rows to be repositioned below the entry position of the new row.

CLUSTERED INDEX
The main difference between the two types (clustered and non clustered index) is that the leaf pages in a clustered index are the data pages of the table and are therefore in the same order as the data to which they point. This means that the clustered index IS the table.The leaf pages of a clustered index and the data pages of the table the index is on, are one and the same. Because of this, table rows are physically sorted on the clustered index column, and since there can only be one physical order of the table data, a table can have only one clustered index.

Very good article about indexes and covering indexhttp://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

 

Spatial datatypes and index

• Spatial index can be based on only a single column of spatial data type
• The table must have a primary key before you can create a spatial index
• Geography and geometry are spatial data types
• STDistance function is a geography function that returns the shortest distance between a point in a geography instance and a point in another geography instance – benefits from a spatial index

 

DBCC CHECKFILEGROUP

Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

DBCC CHECKFILEGROUP performs the following commands:

• DBCC CHECKALLOC of the filegroup.
• DBCC CHECKTABLE of every table and indexed view in the filegroup.

Nonclustered Indexes on Separate Filegroups

If a nonclustered index in the specified filegroup is associated with a table in another filegroup, the index is not checked because the base table is not available for validation. This is a change in behavior in SQL Server 2005. In earlier versions of SQL Server, the nonclustered index and the base table in the other filegroup are checked. To check both the nonclustered indexes and the base tables, run DBCC CHECKDB.

If a table in the specified filegroup has a nonclustered index in another filegroup, the nonclustered index is not checked because of the following:

• The base table structure is not dependent on the structure of a nonclustered index. Nonclustered indexes do not have to be scanned to validate the base table.
• The DBCC CHECKFILEGROUP command validates objects only in the specified filegroup.

A clustered index and a table cannot be on different filegroups; therefore, the previous considerations apply only to nonclustered indexes.

Partitioned Tables on Separate Filegroups

In versions of SQL Server 2005 earlier than Service Pack 2 (SP2), DBCC CHECKFILEGROUP checks a partitioned table only if the entire table is on the specified filegroup. If the table is spread across multiple filegroups, the entire table is ignored. In SP2 and higher, when a partitioned table exists on multiple filegroups, DBCC CHECKFILEGROUP checks the partition rowsets that exist on the specified filegroup and ignores the rowsets in the other filegroups. Informational message 2594 indicates the partitions that were not checked. Nonclustered indexes not resident on the specified filegroup are not checked.

 

Dedicated Administrator Connection (DAC)

• SQL Server provides a special Diagnostic connection for administrators when standard connections to the server are not possible.
• This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
• This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server.
• The DAC only allows changing the user context to another admin user.
• SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
• By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.
• Only members of the SQL Server sysadmin role can connect using the DAC.
• The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). For more information about using sqlcmd, see Using sqlcmd with Scripting Variables. You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<instance_name>.
  Example

  In this example, an administrator notices that server URAN123 is not responding and wants to diagnose the problem. To do this, the user activates the sqlcmd command prompt utility and connects to server URAN123 using -A to indicate the DAC.

  sqlcmd -S URAN123 -U sa -P <xxx> –A

 

BCP vs. BULK Insert

BCP

• If you are exporting data using BCP, you need SELECT permission on the table
• If you are importing data using BCP, you need SELECT, INSERT, ALTER TABLE (if you want to enforce check constraints/triggers)
• Triggers and check constraints are disabled by default.
• If you want to enforce check constraints/triggers during import, -h flag has to be used, and you need ALTER TABLE permissions.
• this is a command line utility

BULK INSERT

• Cannot export data
• t-sql command and doesn’t need to specify the instance name or login credentials
• To import data by using the BULK INSERT command, you need to have INSERT permission on the table you want to load. You also need to have the ADMINISTER BULK OPERATIONS instance-wide permission. Being a member of the bulkadmin fixed server role grants you exactly that permission, so both permission settings are valid.
• ALTER TABLE permission is needed only if one of the following conditions is true: you are using the -E option (KEEPIDENTITY option) to import identity values, the table has constraints but constraint checking is disabled, or the table has triggers and trigger execution is disabled.
• The BULK INSERT statement runs under the security context of the user executing the command