Enable or Disable Transparent Data Encryption (TDE)...

Transparent Data Encryption (TDE) is one of the options to protect your data available wih SQL2008. This encrypts your database, data files, transaction log, and the backup files created for your database.  Keep in mind, however, that this has been known to cause a 20-25% increase in processor load and a significant performance hit.  But if your number 1 priority is encrypting your data, this is one option.

Enabling TDE Encryption

The following SQL commands will get TDE setup in your SQL2008 database. You must have a certificate and private key already generated for this.  Like many sites, you can use the same certificate and private key for all of your SQL2008 Databases.

--- SET ENCRYPTION

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

GO

CREATE CERTIFICATE tdeCert

    FROM FILE = 'path to certificate file'

    WITH PRIVATE KEY (FILE = 'path to private key file',

    DECRYPTION BY PASSWORD = 'password');

GO

USE DBNAME

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE tdeCert;

GO

USE master;

ALTER DATABASE DBNAME

SET ENCRYPTION ON;

GO

After this command, you can check the status of the encryption of the database with the below query.

--- CHECK ENCRYPTION STATUS

USE master

SELECT database_id, encryption_state, percent_complete

FROM sys.dm_database_encryption_keys;

GO

Now you’re all set.

Disabling TDE Encryption

The following process will get TDE removed from your database, after you have determined the performance hit is too much, or the novelty of having an encrypted database has worn off.

Removing TDE is basically the reverse of enabling it, however you will need to wait between steps.

--- REMOVE ENCRYPTION

USE master;

ALTER DATABASE DBNAME

SET ENCRYPTION OFF;

GO

This will make the database start the decryption process. Use the query below to check the status of the decryption process.

--- CHECK ENCRYPTION STATUS

USE master

SELECT database_id, encryption_state, percent_complete

FROM sys.dm_database_encryption_keys;

GO

You may need to truncate the transaction log, or shrink the log file, if the encryption state stays at 2 with percentage 0 for a long period of time. Then stop/restart the SQL Server instance. When your server is back up re-run the above query to see the complete decryption status. This second time will run much faster.

Once the process completes and the database_id you are decrypting is no longer encrypted (will show encryption state 1 when complete), you can go on to the encryption key removal.

--- REMOVE THE ENCRYPTION KEY

Use DBNAME

DROP DATABASE ENCRYPTION KEY

GO

Now simply remove the master key.

--- REMOVE THE CERTIFICATE AND MASTER KEY

USE master

DROP CERTIFICATE tdeCert

DROP MASTER KEY

GO

The process of decrypting the database should now be complete.

Hope this helps...

Using Message Server Logon/Load Balancing with SICF Services, Including NWBC…

I know a lot of you have run into this issue in the past: You are using BSP’s or other services, or NetWeaver Business Client, and you have outgrown the single application server landscape.  The simple solution is to implement additional application servers, right? Well yes, but if you were not using logon balancing before, it may be a bit tricky to figure out what all needs to be done in order to utilize these new application servers from the end user standpoint.

Using the Message Server’s built-in logon balancing function, you can get this done in a matter of minutes.

Pre-requisites:

-        - You have already installed the new application servers and connected them to the Central Instance.

-        - Message server ports must be configured for access. 

-        - SNC must already be setup in order to utilize HTTPS load balancing

First, you will need to go into transaction SMLG and create at least one logon group and assign an application server to it. I like to have a few of these so that when a client’s system grows it is simple to spread out the load. You will want to name it something that you will be able to easily identify, like say NWBC. Once this is created, then create another one with the exact same name (case sensitive) and assign another application server to it.  In the Properties tab for the logon group you can set it to use the balancing logic Round Robin, Best Performance, Probability, or Weighted Round Robin.  Once you have all the instances you want assigned to the logon group, you are ready to configure the service to use it.

Next, go to transaction SICF and double click the service you wish to take advantage of load balancing. In the Service Data tab, select your logon group from the Load Balancing dropdown field, and save. You can double check that logon balancing is activated for this service in transaction SMMS. If you want to assign all activated services to the same logon group, you can assign the group to the top level default_host service, but I would not recommend it unless you are sure you will always use this logon group for all services.

Now all you need to do is make sure end users are logging into these services through the message server port rather than a direct link. The default message server port is 8100 (or 44400 for HTTPS), so your URL link to be used should be http(s)://message_server:8100/service_path. You will also want to make sure that the URL in the users’ NWBC desktop client is changed to point to the message server rather than a direct link to the application server.

Hope this helps…