Collation in SQL Server

“SQL Server is not case sensitive” ….. I am sure DBA’s have heard this phrase uttered on many an occasion. The truth is ….that depends!

The case sensitivity among other things is determined in SQL Server by the collation setting. The collation assigned in SQL Server determines the sorting rules, case, and accent sensitivity of the data residing inside a SQL Server database. This property is defined at the server, database, column, and expression level. Each level defaults to the setting of the parent if not explicitly defined.

Specifying the collation setting in SQL Server determines which codepage is utilized to represent non-unicode character data. A codepage is a set of characters that represent different languages and locales. When installing SQL Server, the machine locale setting determines the default collation setting used by the SQL Server being installed. If the locale setting for the machine is set to “English (United States)” the default collation setting is SQL_Latin1_General_CP1_CI_AS which is case insensitive. Not changing this during install results in the server collation setting being case insensitive.

Collation names are comprised using the following naming standard.
SQL_SortRule_pref_CodePage_ {CaseSensitivity_AccentSensitivity | BIN}

SortRule - identifies the language to be used for sorting

CodePage - identifies the code page

CaseSensitivity - either CI or CS for Case Insensitive or Case Sensitive

AccesntSensitivity - either AI or AS for Accent Insensitive or Accent Sensitive

BIN – designates that binary sort order is to be used.

A list of available collations for SQL Server 2008 can be found here.

Collation is not just defined for the SQL Server instance however. One can control collations to a finer degree. The T-SQL command COLLATE will apply the selected collation to a database during the execution of CREATE DATABASE command. The following command will result in a new database with using the “English (United States)” code page with case sensitivity.

CREATE DATABASE TestDB_2 COLLATE SQL_Latin1_General_Cp1_CS_AS

COLLATE can also be applied in the column definition of CREATE and ALTER Table. The following example will create a table “MOVIES” with the titles being case insensitive and accent sensitive.

CREATE TABLE MOVIES
(Mov_ID INT,
 MOV_TITLE  VARCHAR(1024)  COLLATE SQL_Latin1_General_Cp1_CI_AS NOT NULL,
 MOV_RELEASE_DATE DATE
)

Since the TestDB_2 database was created using a case sensitive collation the object names in the database then also become case sensitive. So after creating the table “MOVIES” the following statement will fail.

 DROP TABLE dbo.movies;

And Lastly, COLLATE can also be applied to a string expression. This will result in a cast of the result into the specified collation.

So, “SQL Server is not case sensitive”…. Just depends.

SQL.CLR Profession Edition Released!

SQL.CLR Professional Edition and SQL.CLR Professional 2008 Edition have now been released.

Version 1.0.3.62

Added features:

Supports multiple object generation

Template generator

Included Deployment Script


Features

SnipStorm Explorer Released!

SQL Server T-SQL Script Addin All your scripts a click away...



Many database developers have T-SQL scripts scattered across directories and folders. Finding that one script that you need can be frustrating and time consuming. Enter SnipStorm Explorer, a Windows explorer for T-SQL scripts, or snippets. This tool only displays snippets that are stored on your hard drive for easy access and quick viewing.

Online storage and access with SnipStorm.com

Available as Windows desktop app and SSMS addin
















From now to October 1 ,2009...SnipStorm Explorer is 50% off!!!

Just enter the discount code: SnipStormRelease at checkout.

Change Data Capture - Part 2

As I mention in my previous post on Change Data Capture in SQL Server 2008, Microsoft provides table valued functions (TVF's) to access changed data. The main TVFs used to query the change data are:

cdc.fn_cdc_get_all_changes_capture_instance
and
cdc.fn_cdc_get_net_changes_capture_instance

Each of these TVF's require two LSN's or log sequence numbers to identify the beginning and ending of the range of changes to be queried. Two functions are provided to aid in determining the min and max LSN available. They are:

sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn


Get All Changes

The first TVF, cdc.fn_cdc_get_all_changes_capture_instance
,will return one row per changed row on the source table. This means that if more than one column is modified in a transaction then one row is returned in the result set for the given data modification.

The TVF accepts the following paramters when utilized.

• Min LSN


• Max LSN


• Row Filter option

The min LSN is obtained using the sys.fn_cdc_get_min_lsn function. Using Employees table from the previous post as an example, the min lsn can be obtained as follows

SELECT sys.fn_cdc_get_min_lsn ('dbo_Employees')AS min_lsn;

The max LSN is obtained using the sys.fn_cdc_get_max_lsn function. This function does not require any inputs and returns the max lsn for the captured data of the source table. To get the max LSN from the Employees table:

SELECT sys.fn_cdc_get_max_lsn()AS max_lsn;

The row filter option has two settings, All and All Update Old. The All option will return all changes made to the source table in one row. The All Update Old option will return one row for an update showing the previous value and one row showing the changed value.

Putting it all together to get the changes:

DECLARE @minLSN BINARY(10),@maxLSN BINARY(10)
SELECT @minLSN=sys.fn_cdc_get_min_lsn ('dbo_Employees');
SELECT @maxLSN=sys.fn_cdc_get_max_lsn();
select * from cdc.fn_cdc_get_all_changes_dbo_Employees(@minLSN,@maxLSN,N'All');

Get Net Changes

The second TVF available to work with captured change data is sys.fn_cdc_get_min_lsn. This TVF returns a row that specifies the whole change to a source row if more than one transaction has made modifications to that data. To use this feature the @supports_net_changes parameter must be set to 1 when enabling CDC on a given table.


The input parameters are the same as cdc.fn_cdc_get_all_changes_capture_instance
however the row filter option is slightly different. The options available are All, All With Mask, and All With Merge. All will return all net changes. All With Mask will return the final row plus the operation needed to apply the row. The All With Merge will return the final row with the resturn column _$oprtation being either a 1 ( indicating a delete ) or a 5 ( indicating either an insert or update is needed to apply the change )


Here is an example utilizing the net changes.

DECLARE @minLSN BINARY(10),@maxLSN BINARY(10)
SELECT @minLSN=sys.fn_cdc_get_min_lsn ('dbo_Employees');
SELECT @maxLSN=sys.fn_cdc_get_max_lsn();
select * from cdc.fn_cdc_get_net_changes_dbo_Employees(@minLSN,@maxLSN,N'all');

One last item to mention is that Microsoft provides a function that correlate time to LSN's. This is useful if you have a specific time period in which you would like to capture changes made to data. The function is sys.fn_cdc_map_time_to_lsn and can be used to determine lsn boundries based on time to be used in acquiring the change data.

Change Data Capture in SQL Server 2008

Some applications require the ability to understand the full history of how data has been modified over time. A new feature included in SQL Server 2008 is Change Data Capture. Change Data Capture (CDC) allows for the ability to query data that has been modified through INSERT, UPDATE, or DELETE statements on a database table.

The SQL Server 2008 database engine provides the necessary framework to implement Change Data Capture. It is currently available in SQL Server 2008 Enterprise, Developer and Evaluation editions. The CDC process is accomplished through the use of the SQL Server transaction logs. As data modifications are recorded in the database transaction log, the CDC process reads the log files and captures the changes for those tables that have been identified to participate in CDC. It then writes those modifications to the Change Capture Data tables. Each table participating in CDC has a corresponding capture table that follows the naming standard and is owned by the database user cdc to hold the modified data. SQL Server allows for the placement of the table containing the modified data to be placed on a separate filegroup. This is a recommended configuration however the default location for the table will be the default filegroup for the database.

Once a database has been enabled for CDC, two SQL Agent jobs are created to manage the capture process. One job is for the purpose of capturing data on those tables that have been identified for CDC. The other job is created for the purpose of cleaning up the collected change data. Two procedures are provided to view and modify configuration parameters of these jobs. They are named sys.sp_cdc_change_job and sys.sp_cdc_help_jobs. Changes made through these procedures do not go into effect until the job is stopped and restarted.

The following is a simple demo that walks through enabling CDC and querying changes made.

Set Up

Create a test table to execute INSERT, UPDATE, and DELETE.

CREATE TABLE [dbo].[EMPLOYEES](
    [EMP_ID] [int] IDENTITY(1,1) NOT NULL,
    [EMP_NAME] [varchar](100) NOT NULL,
    [EMP_NUMBER] [int] NOT NULL,
    [EMP_TITLE] [varchar](50) NOT NULL,
    [EMP_HIRE_DATE] [date] NOT NULL,
    [EMP_TERM_DATE] [date] NULL,
 CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED
(
    [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY])

Create database role for the management of the cdc tables.

CREATE ROLE [ChangeData_Admin] AUTHORIZATION [cdc] 

To utilize CDC, it must be enabled for the database. This is accomplished using the following stored procedure.

EXECUTE sys.sp_cdc_enable_db;

Once CDC has been enabled for the database, the tables you wish to participate in CDC can then be identified through sys.sp_cdc_enable_table.

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'
  , @source_name = N'Employees'
  , @role_name = N'ChangeData_Admin';

The cdc agent jobs are created as a result of this procedure

Results
------------
Job 'cdc.TestDB_1_capture' started successfully.
Job 'cdc.TestDB_1_cleanup' started successfully.

Now we are ready to insert data into our table.

INSERT INTO [Test_db1].[dbo].[EMPLOYEES]
           ([EMP_NAME]
           ,[EMP_NUMBER]
           ,[EMP_HIRE_DATE]
           ,[EMP_TERM_DATE]
           ,[EMP_TITLE])
     VALUES
           ('Steve Stark'
           ,1001
           ,'05/15/2008'
           ,null
           ,'Associate Developer')
GO

INSERT INTO [Test_db1].[dbo].[EMPLOYEES]
           ([EMP_NAME]
           ,[EMP_NUMBER]
           ,[EMP_HIRE_DATE]
           ,[EMP_TERM_DATE]
           ,[EMP_TITLE])
     VALUES
           ('Mary Doe'
           ,1002
           ,'10/24/2003'
           ,null
           ,'Database Administrator')
GO

To see the changes we can query the cdc.dbo_employees_CT table. Microsoft does provide table valued functions to work with captured change data. For the sake of this post, I will directly query the capture table to view the results. For more information on how to use the provided table value functions, go here.

SELECT * FROM cdc.dbo_employees_CT

Here you will see two rows for the previous insert statements.

Now update some data:

 UPDATE dbo.EMPLOYEES
 SET EMP_TITLE = 'Development Director'
 WHERE EMP_ID = 1

Again query cdc.dbo_employees_CT to observe the changes. Since we performed an update there will be two rows captured. The first is the old value and the second is the new value. Captured delete data will also only contain one row displaying the value prior to the deletion.


Clean Up


DROP TABLE dbo.EMPLOYEES;
EXECUTE sys.sp_cdc_disable_db;

UPDATE: SQL Encryption Assistant v.1.0.6.238

Devenius has just released an update to SQL Encryption Assistant. The latest version is now 1.0.6.238.

This update applies to the following editions:


Standard
Standard 2008
Professional
Professional 2008

Some of the new features include:

- Expanded EKM functionality
- "Color-Coded" Encryption Status
- Extensible Key Management device support or asymmetric and symmetric keys

To apply the update, please see the post "How do I get the latest version of SQL Encryption Assistant?".

The DBA vs. Service Broker Assistant Part II

So being the DBA portion of Devenius ( see About ) I figured I would try to test my developer partner's statistics from Part 1 using Server Broker Assistant in implementing the event notification logging example. I did deviate slightly from the plan by creating the tables and stored procedure prior to beginning the time study. My test went as follows.

1) Enable service broker for the test database.
2) Create the queue for notification events.
3) Create the service to manage the queue.
4) Alter the queue to use the previously created stored procedure.


I suppose I could have just as easily skipped step 4 and hooked in the stored procedure upon queue creation. However, I figured I would try to get close to the same process I am attempting to validate.

Test Case A - scripted

Using Management Studio's Query window, I wrote and executed the T-SQL manually. I too had the syntax available to me and wasn't required to perform any lookups on MSDN or BOL. Here is a copy of the sytax:

1. ALTER DATABASE TestDB_1 SET ENABLE_BROKER;

2. CREATE QUEUE NotifyQueue;

3. CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);

4. ALTER QUEUE [dbo].[NotifyQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[LogEventsProc] ,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF
);

Test Case B - Service Broker Assistant

In this scenario, I used the Service Broker Assistant to perform the same actions in Test Case A.


And the results:

Test Case A = 3 min. 54 sec.
Test Case B = 1 min. 46 sec.

Service Broker Assistant showed quite an improvement in time to execute over the traditional method of manual T-SQL.

The DBA vs. Service Broker Assistant Part I

A while back I posted the MSDN example for event logging with SQL Service Broker using our latest product SQL Service Broker Assistant. The example is a Service Broker implementation for logging event notifications. Then I got to thinking...how long would it have taken me to script the application in T-SQL? Good question. I have no idea.

So, I decided to have a little competition.

The DBA vs. Service Broker Assistant

A couple notes for the test. The example has several steps and for the sake of simplicity the dependent tables and stored procedure are deployed ahead of time. This test will be performed using the Standard edition of Service Broker Assistant. The professional edition is in the final stages of development and once it is released I will attempt the same test. The professional edition will allow you to create event notifications for Service Broker, the final step in the example.

Steps

1. Enable the Service Broker on the instance.

2. Create the service queue for logging.

3. Create the logging service.

4. Create logging tables, one in relational format the other to handle XML.

5. Create the stored procedure for executing upon activation.

6. Alter the queue to add the stored procedure created in step 5.

Ready...set...GO!!!

The completely unscientific results:

DBA 2 minutes 55 seconds
SBA 1 minute 25 seconds

Over a 50% decrease in time! Now that assumes I remembered all the syntax for each step, while possible, I don't know about you but most of the time I need to look things up. Remember, I'm a developer by trade not a DBA...so wait maybe it should be Developer vs. Service Broker Assistant?

I...need...a...DBA. Hmmm...to be continued.

SnipStorm.com is Unleashed

We have just finished the finishing touches on a companion site for Devenius.com...

SnipStorm is a community where you can find the latest SQL stories to learn about new features, new ideas and meet new people.

Share your opinions with others, ask questions, answer questions and earn Bolts from the community.

We plan on extending the site to include more features aimed at DBAs and developers, so check back soon!

900 Bytes - Index Limit

SQL Server 2005 and 2008 place a limit on the maximum key size when creating a non-clustered index. The current limitation is 900 bytes. This means that the total size of the keyed columns included in the index definition cannot exceed 900 bytes. SQL Server will issue an error or a warning at the time of index creation depending on the columns specified.

Fortunately there are some options available to overcome this limitation.

INCLUDE COLUMNS

The first is to leverage the INCLUDE statement in CREATE INDEX. This feature enables us to remove some of the columns in the index key but still allows them to participate in the index. SQL Server places the data of these columns in the leaf level of the index. The benefits here are:
1) The Index can fully cover a query if the SELECT statement contains columns that could otherwise not participate in the index key.
2) The overall size of the index would be reduced.

HASHBYTES

The second option would be to add a column to the table of interest that would contain the hash value of the column to be searched on. This new column can then be indexed to provide better performance for searching. SQL Server provides a function, HASHBYTES, that returns the hash of the input provided. Algorithms supported by HASHBYTES include MD2, MD4, MD5, SHA, and SHA1. More detail on hash functions can be found here from RSA Labs

Updating the new column with the hash value can be accomplished like this:

UPDATE Table XYZ set ColA_Hash = HASHBYTE('SHA1',ColA)

With an index in place, filtering in the WHERE clause using the HASHBYTE of the interested text should result in improved performance.

WHERE
HASHBYTE('SHA1',text) = ColA_Hash

The use of HASHBYTES is an alternative but it should be noted that this will not provide a solution to searches filtered using the LIKE operator.

HOW TO: Enable Service Broker Using SBA

The first step in creating Service Broker applications is to make sure the Service Broker instance for the database is enabled. This means that message delivery is activated and available.

To enable the instance using Service Broker Assistant, right-click 'Service Broker' in the object explorer. Select 'Configure Service Broker...'. The configuration window will open and you will see 'Enable Service Broker' as the first option under the 'Service Broker Activation' section.



If 'Enable' is not selected, select it and click 'OK'. That's it. Service Broker is now enabled for that database.

Note: If the database has been restored from a file, there is the possibility that it will try to use the same Service Broker instance GUID. You will get an error message similar to the following:

The Service Broker in database [DB_NAME] cannot be enabled because there is already an enabled Service Broker with the same ID.

If this happens, choose the 'Reset Service Broker' option to obtain a new GUID, then try again to enable the Service Broker.

Service Broker Assistant Event Logging Example

The 'Event Logging' example provides a simple application that registers for, receives, and archives event notification messages.

Enable Service Broker

Check that the Service Broker is enabled in the database by selecting 'Configure Service Broker...'


Create Queue

Next, create a queue to store the notification messages. Name it 'NotifyQueue' and set it's 'Status' to available selecting the check box.


Create Service

Now, we can create a service, which is the component that ties together a contract and a queue together for creating the application. Name the service 'NotifyService' and select the queue created above. Choose the 'PostEventNotification' contract, a default contract that is in each database.


Create Log Tables

For this example, we will use 2 tables. The first holds event notification information in relational form:

CREATE TABLE [dbo].[LoggedEvents] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
LoginName sysname NULL,
UserName sysname NULL,
ServerName sysname NULL,
DatabaseName sysname NULL,
SchemaName sysname NULL,
ObjectName sysname NULL,
ObjectType sysname NULL,
TSQLCmdText NVARCHAR(MAX) NULL
) ;

The second stores the event type and time along with the entire raw XML message:

CREATE TABLE [LoggedEventsXML] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
EventData XML
) ;

Create Activation Procedure

The stored procedure that will be activated with each event notification is next.

CREATE PROCEDURE [dbo].[LogEventsProc]
AS
SET NOCOUNT ON;
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_type_name=message_type_name, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- identifier of the dialog this message was received on
FROM NotifyQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;
-- Check to see if the message is an end dialog message.
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + CAST(@dialog AS NVARCHAR(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE
BEGIN
-- Extract the event information using XQuery.
-- Use XQuery to extract XML values to be inserted into the log table
INSERT INTO [dbo].[LoggedEvents] (
EventType,
EventTime,
LoginName,
UserName,
ServerName,
DatabaseName,
SchemaName,
ObjectName,
ObjectType,
TSQLCmdText
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/UserName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ServerName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/DatabaseName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/SchemaName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectType/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/TSQLCommand/CommandText/text()') AS NVARCHAR(MAX))
) ;
-- Insert the message body as XML into the loggedeventsXML table
INSERT INTO [dbo].[LoggedEventsXML] (
EventType,
EventTime,
EventData
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS datetime),
@message_body
) ;
END ;
COMMIT TRANSACTION ;
END ;


Alter Queue

Now, we have to go back and alter the queue we created to use that procedure. Select the 'Activation' check box. The activation options are now enabled.

• Select the stored procedure, LogEventsProc
  
• Set the 'Max Queue Readers' to 2
• Select 'Execute as Self'
• Check 'Activate stored procedure'
  

Click 'OK'

Create Event Notifications

With Service Broker, we can setup event notifications to alert our logging application when certain events take place. We can choose from server, database or queue level events. In this example, we will choose the server level event 'CREATE_DATABASE' and the database level event 'CREATE_TABLE'.

Right-click 'Service Broker' in the SSMS object explorer and select 'Create Event Notifications...'


Create the server level event 'CreateDatabaseNotification'. Select the 'Server' scope. Click the '...' next to 'DDL event type' and select 'CREATE_DATABASE' event.



Click the '...' next to 'Remote service name' and select the service we created earlier, 'NotifyService'. You can leave the 'Broker service GUID' as 'current database', or browse to the Service Broker instance where the service was created.

Click 'OK'.

Follow the same steps to create the database level event, but choose the 'CREATE_TABLE' event instead.



You will now see the 2 event notifications listed in the grid below.


We are now ready to test the application. Execute a script to create a database and a table. Nothing fancy, we just want to see the results of the application.


Run 2 select statements to view the contents of the tables.

The first result set is from the relational table log and the second is the message orientated log (with the raw XML posted below it.)




<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2009-06-15T19:37:42.313</PostTime>
<SPID>51</SPID>
<ServerName>IRON\CARBON</ServerName>
<LoginName>sa</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Application</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>TestNotificationTable</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE TestNotificationTable (Column1 int, Cloumn1 int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

Now Available - Service Broker Assistant Standard Edition

We are excited to announce the release of our latest product Service Broker Assistant. Initially available in Standard Edition, Service Broker Assistant is an SSMS add-in that eases the development of creating SQL Server Service Broker objects. Common tasks normally accomplished through T-SQL can now be created through an easy to use UI that is made available directly in object explorer.

Service Broker Assistant Standard Edition is available for $99 and is compatible with both SQL Server 2005 and 2008. We are currently offering a 20% discount for the next 7 days. Enter "LaunchDiscount" in the Discount Code box at checkout to receive 20% off Service Broker Assistant.

With Service Broker Assistant you can accomplish the following tasks:

Service Broker Configuration
Create and Alter:

Message Types
Contracts
Services
Queues
Routes

Service Endpoints
XML Schema Collections
Remote Service Bindings

To learn more about Service Broker Assistant:
Service Broker Assistant

Here are some sample screen shots:

Configuration

Create Contract

Create XML Schema Collection

The professional version of Service Broker Assistant is coming soon. To learn more about the added features in professional edition check out our feature comparison page.

Sneak Peek: Service Broker Assistant

Below are some screen shots of the soon-to-be-released Standard edition of SBA.

Creating a Service Broker Endpoint...



Adding A Route...


New Service...

SQL.CLR Standard Edition v.1.0.2.801 Released

SQL.CLR Standard Edition v.1.0.2.801 has been released. Included in this release is support for the output parameter type and SQL Server project integration.

SQL.CLR Standard Edition
SQL.CLR Standard 2008 Edition

You’ve Generated a CLR Procedure, Now What?

SQL.CLR makes it easy to generate CLR stored procedures and user defined functions. Just select then object and click go! One nice feature is that you can select the options to create a project and/or compile an assembly for deploying to SQL Server. This offers 2 ways to deploy the CLR object.

First, by creating a SQL Server project, the code generated from the existing T-SQL object can be modified to add, edit or remove any custom business logic. This is where CLR really provides us with tremendous flexibility when designing our logic on the data. We have the entire .NET base class library, not to mention any custom libraries we have developed, to leverage in our project. Once we have made the changes to our code, we can simply right-click our project and select “Deploy.”


The second option is to have SQL.CLR compile an assembly from the existing object. This means that we will not modify any existing logic in the code. From here we need to open SSMS and run a few lines of T-SQL to continue the deployment. Create an assembly in the database you wish to store the code for the object.

CREATE ASSEMBLY [Assembly_Name] FROM '\\[Path_to_assembly]\[Assembly_Name].dll' WITH PERMISSION_SET = SAFE

Next create the object, a stored procedure in this example with the format of:

CREATE PROCEDURE [Procedure_Name] AS EXTERNAL NAME [AssemblyName].[Namespace.ClassName].[MethodName]

Now that we have deployed our assembly, created the stored procedure all that’s left is to execute the stored procedure…

EXEC [Procedure_Name]

Code Validation with Digital Signatures

One of the advantages of digital signatures is to authenticate certain information. How this is normally accomplished is that a person would use their private key to encrypt a given document or the document's fingerprint. Initially a hash function would be applied to the document generating a message digest. The user would then encrypt the message digest using their private key. That way if the recipient of the document wishes to authenticate that it has not been tampered with they could use the public key of the sender to decrypt the message digest. Then using the same hash function on the message the two results could be compared to determine if they match. If so, then the recipient can be ensured that the document has not been altered.

Situations can occur where two different messages result in the same message digest. This is called a collision.

Adding a digital signature to code in SQL Server provides a way to ensure that the code has not been tampered with. To check if a stored procedure, trigger, or assembly is authentic one can query the sys.crypt_properties table. The thumbprint column will contain the value of the signing certificate's thumbprint for the object that has been signed.

For Example, I have a stored procedure getEmployees that has been signed by Certificate Cert_01.

The following query shows that getEmployees has been signed.

SELECT
OBJECT_NAME(crp.major_id) 'Procedure',
cer.name 'Certificate',
crp.thumbprint
FROM
sys.crypt_properties crp INNER JOIN sys.certificates cer
ON crp.thumbprint = cer.thumbprint

Results:

Procedure Certificate thumbprint
--------- ----------- -----------------------------
getEmployees Cert_01 0xE879E7ECF3702DE6D27A9A28E8B7052D77695E41

Now if I alter the stored procedure getEmployees no rows are returned.

Results:
Procedure Certificate thumbprint
--------- ----------- -----------------------------
(0 row(s) affected)


Given that the procedures was changed, the digital signature has been dropped by SQL Server.

SEA: Did You Know?

Cool feature even I forget about!

Did you know...that from the object explorer in SEA, you can select an object and drag-and-drop it to the query window? Nice!

Case of the SQL Encryption Mondays

While we were developing SQL Encryption Assistant (SEA), we discovered an interesting if not scary tidbit with the encryption process. It involves encrypting a column of data with a symmetric key. Here’s the setup: A simple 3 column table, we’ll call it CUSTOMER_DATA. The columns are as follows:

• CUS_ID – INT identity column
• CUS_NAME – VARCHAR customer name
• CUS_SSN – NVARCHAR(MAX) customer social security number

CREATE TABLE [dbo].[CUSTOMER_DATA]
(
[CUS_ID] [int] IDENTITY(1,1) NOT NULL,
[CUS_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_SSN] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CUSTOMER_DATA] PRIMARY KEY CLUSTERED
(
[CUS_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Add a row of data to the table.

INSERT INTO dbo.CUSTOMER_DATA (CUS_Name,CUS_SSN,CUS_DOB)
VALUES ('New Customer','123-456-789','11/12/1970')

Using SEA, we created a password-encrypted symmetric key named ColumnEncryptionKey. This key will be used to encrypt a column of data in the table. (Can you guess which one?) Here’s what the T-SQL would look like:

CREATE SYMMETRIC KEY [ColumnEncryptionKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = N'1234qwer'

Now, using that symmetric key we encrypt the column of data. To do this we need the decryption password of the key. No problem. But, as usual, It’s a Monday and I haven’t had enough “fill-in-the-blank with your favorite caffeinated concoction”, and I ham-sandwiched the password. Doh! No problem, I’ll just enter it again, c-o-r-r-e-c-t-l-y and our column of SSNs will be safe. But wait, there’s more! There’s a red message from SSMS. What could it be saying…


Msg 15313, Level 16, State 1, Line 1 The key is not encrypted using the specified decryptor.

Right, got it. I know. Wrong password. But, that’s not the interesting piece. Next line, please (drumroll)...

(1 row(s) affected)

WHAT!?!?! It didn’t update the…I entered the wrong…what the…who the...why would it do that? Let me check the table.

SELECT * FROM dbo.CUSTOMER_DATA



Noooooo! It nulled out the data! Yep, turns out if you enter the wrong decryptor, SQL Server goes ahead anyway with the failed encryption. Your data is gone. So, how do we fix this? One, don’t enter the wrong decryptor. Excellent, I’ll try and be more perfect next time. Or, use SEA. It will not let this happen and save you from yourself when you have a case of the “Mondays.”

SQL.CLR Gets a Fresh Coat of Paint

Along with some minor tweaks, SQL.CLR, our CLR procedure generator for SQL Server, got a makeover.
SQL.CLR Product Page

SQL.CLR now has a more "wizard" look-and-feel to it that is common to most tasks in SSMS. The window size was reduced to make it more manageable, added a welcome screen, changed some of the option locations to group them into a more logical order. Little things to make the app easier to use.

But, the new paint job was the big one...hope you like it!

Service Broker Assistant : The Feature List

As we get closer to our initial release date, I wanted to put together a preliminary feature set for the product. Service Broker is a rich and flexible architecture, so what we are attempting to do is separate some of the basic object creation from some of the more interesting application building functionality.

Both standard and professional editions will have basic creation features i.e. CREATE SERVICE, CREATE QUEUE, etc., as well as ALTER functionality. Plus, they will each be able to configure service broker.

Some of the more advanced options will come in the professional edition. Things like "Common Task Automation" and "Poison Message Detection and Removal". These would include deactivating Service Broker message delivery, pausing service broker networking and configuring initiating and targeting service security.

An important part of building service broker applications is the monitoring of the services. The professional edition will supply some basic monitoring features, performance monitoring and event tracing.

As I said earlier, this is a preliminary sketch of the features we hope to include in the coming release of Service Broker Assistant, but check back for more updates as the release approaches.

FYI: We are still targeting late Q2 for the release :)

Digital Signatures with SEA

One of the features of SQL Encryption Assistant is providing an easy mechanism to digitally sign programmables in a SQL Server Database. For this example I have created the following

1. A table named EMPLOYEES


2. A procedure to select all rows from EMPLOYEES named getEmployees


3. Digital Certificate named CERT_01

I can now launch SQL Encryption Assistant inside Management Studio. Navigate to the certificate you wish to use for signing and right click to get to the Add Signature menu item.



From here I can sign procedures, functions, triggers, or assemblies. In the interest of keeping this simple, I will have a follow up post on counter signing on a later date.


The Add Signature window will open upon selecting the menu item. As you can see below, I have selected the procedure getEmployees to be signed. I have also entered the password that was used to protect Cert_01.



Click OK to sign the procedure. I also would like to mention that once a programmable is signed, it will show up in the dependencies window for the certificate used in the signing.

SEA Pro 1.0.5.908 Update

SQL Encryption Assistant Professional Edition 1.0.5.908 has been released.

To obtain the latest version see the FAQ post in our forum:
How do I get the latest version of SQL Encryption Assistant?

Release Details

Issue
Encrypt data by symmetric key protected by a password. Enter invalid password and click "OK". Data will be null in selected column due to incorrect decryptor.

Resolution
Encrypting and decrypting data by symmetric key protected by a password will not allow incorrect password to be entered. If an incorrect decryptor is entered the encryption or decryption process will be terminated and the data will not be null.

The mISV Toolbox

Starting a mISV is challenging on so many levels, once you have an idea, a master plan or just some molecules scribbled on a napkin, the next step becomes a litany of questions.

What platform will I develop on?
What language?
How will I deploy?
Where do I host everything?
What about e-Commerce? Marketing? Taxes?

...it goes on and on.

As a mISV we needed to take advantage of anything and everything with 'free' attached to it. One of the areas I want to focus on in this post are the tools we have found indispensable to starting, building and growing the company. While some of these do cost money they are at the lower end of the cost spectrum in that space. Also worth noting is the "build v. buy" dilemma. We have in some cases decided to build tools ourselves in order to fill in the gaps. (more on this in a later post)

First and foremost...

Microsoft BizSpark
BizSpark is uniquely designed to accelerate your success by providing fast, affordable access to current, full-featured Microsoft tools and technologies, plus production licensing for hosted solutions.

Development
DebugView
Reflector

HelpMaker
HelpMaker is RTF-based, page-layout Help Authoring tool. It generates WinHelp, HTML_Help, Website-Help and PDF.

TortoiseSVN
TortoiseSVN is a really easy to use Revision control / version control / source control software for Windows.
Since it's not an integration for a specific IDE you can use it with whatever development tools you like.

Inno Setup
Inno Setup is a free installer for Windows programs. Versatile and flexible. Love this tool.

Unfuddle
Unfuddle is a secure, hosted project management solution for software development teams.

Code Obfuscation Software($)

Media
Sizer
Allows you to resize any window to an exact, predefined size. This is extremely useful when designing web pages, as it allows you to see how the page will look when viewed at a smaller size. The utility is also handy when compiling screen-shots for documentation, using Sizer allows you to easily maintain the same window size across screen grabs.

Paint.NET
Wow! What a tool. So easy a developer can use it! We have used Paint.NET for all our graphics.

Wink
A Tutorial and Presentation creation software, primarily aimed at creating tutorials on how to use software (like a tutor for MS-Word/Excel etc). Using Wink you can capture screen shots, add explanations boxes, buttons, titles etc and generate a highly effective tutorial for your users. Very easy to use, had 2-3 demos up in a couple of hours.


Marketing
Google Analytics
Google Adwords ($)

HiStats
Adds a different view on site analytics.

ClickTale
ClickTale is an in-page web analytics tool that allows you to record your users as they browse your site. Very powerful tool for us as it has helped us to design and redesign our site several times from this "virtual feedback".

Kampyle
Online feedback app to intake user feedback. Easy to install and customize.

Squidoo
A publishing platform and community that makes it easy for you to create "lenses" online. Lenses are pages, kind of like flyers or signposts or overview articles, that gather everything you know about your topic of interest—and snap it all into focus.

eCommerce
e-Junkie ($)
E-junkie provides shopping cart and buy now button functionality for site selling products online. Great service, low cost and feature rich.

PayPal

Communication
TeamViewer
Wonderful app to communicate, share destops, remote support.

FileZilla
Messenger

Yet Another Forum.net
YetAnotherForum.NET (YAF) is a Open Source discussion forum or bulletin board system for web sites running ASP.NET.

.Blog
Open source ASP.NET blog software.

Twitter
Facebook

Misc
KeePass
Free open source password manager, something you will definitely need as you open accounts.

PADGen
PAD is the Portable Application Description, and it helps authors provide product descriptions and specifications to online sources in a standard way, using a standard data format that will allow webmasters and program librarians to automate program listings. PAD saves time for both authors and webmasters

VCdControlTool
Mount ISOimages as virtual CD drives.

SEA Pro 2008 1.0.5.908 Update

SQL Encryption Assistant Professional 2008 Edition 1.0.5.908 has been released.

To obtain the latest version see the FAQ post in our forum:
How do I get the latest version of SQL Encryption Assistant?

Release Details

Issue
Encrypt data by symmetric key protected by a password. Enter invalid password and click "OK". Data will be null in selected column due to incorrect decryptor.

Resolution
Encrypting and decrypting data by symmetric key protected by a password will not allow incorrect password to be entered. If an incorrect decryptor is entered the encryption or decryption process will be terminated and the data will not be null.

Announcing Devenius Social Connections

Here at Devenius, we want to be accessible to our customers as much as possible. To achieve this level of openness, we have created several avenues to converse with us:


Twitter


Facebook


Online Forum


Blog

Feel free to follow us, become our friend our just visit us!

---

Sincerely,
Tim Rowan
Co-Founder
Devenius, Inc.

3 New Demo Videos Added

We have added product demo videos for SQL Encryption Assistant and SQL.CLR.

The demos can be found here: Product Demos.

Check back often as we will be adding more videos!

HOW TO: Enable Transparent Data Encryption (TDE)

To enable TDE, follow these steps.

1. Create a certificate protected by the master key.
2. Create a database encryption key and protect it by the certificate.

Select the database that will implement TDE. Right-click and select 'Database Key -> Create'.



Select the certificate created in step 2.

3. Enable TDE by selecting the database, right-click and select 'TDE -> Enable'

HOW TO: Encrypt data with SQL Encryption Assistant

1. First let's setup a sample table in the database. This table will have 4 columns:

Code:
CREATE TABLE [dbo].[CUSTOMER_DATA](
[CUS_ID] [int] IDENTITY(1,1) NOT NULL,
[CUS_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_SSN] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_DOB] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_CUSTOMER_DATA] PRIMARY KEY CLUSTERED
(
[CUS_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2. Prime the table with some data.

3. Open SQL Encryption Assistant.

4. Create a symmetric key named 'EncryptKey', protected by a password.

5. Select the key created in step 4, right-click and select Tasks -> Encrypt Data



6. Choose 'Password' for the key decryption and enter the key's password.

7. In the 'Select Table' group box, select the table created in step 1, 'TEST'.

8. Select the column 'TST_Data' and click '>>'. This is the data we will encrypt.

9. Click 'Ok'.

The data is now encrypted using a symmetric key.