Wednesday, October 28, 2009

MySQL vs MS SQL Server

I know MySQL's query engine isn't as powerful as MSSQL, but there are things in the MySQL syntax that just run circles around MSSQL. For example, MySQL has a great option to the INSERT command called IGNORE.

It is very common to construct a many to many table using just two columns for the relationship and making those two columns the primary key. This implies each row is unique. A common problem is how to update this relationship.

Say you have a web site where a user can choose from a list of options. When the user wants to update their settings, they check or uncheck some new settings. On the backend, we need to update the relationship.

A common approach is to delete all the old relationships, then reinsert them. This to me is an expensive and brute force approach. A less "heavy handed" approach would be to select all the current mappings, then figure out what is new and what should be removed. However, we've now made a rather complex difference engine on the client and it's not concurrent, unless we lock the database while doing the work on the client.

So, welcome MySQL's INSERT IGNORE command.

Here's how it works:

INSERT IGNORE INTO table (field1,field2) VALUES (val1, val2)

Simple, if the row already exists (a unique constraint is violated), MySQL simply skips the insert.


However, what if we want to do the same thing in MS SQL? Not nearly as easy, and a lot more resource intensive..

T-SQL:
insert into table (field1,field2)
select val1, val2
where
not exists(select 1 from table where field1=val1 and field2=val2)

Another command I love in MySQL, but absent from MSSQL is the INSERT .. ON DUPLICATE syntax.. It solves a common problem, do you need to add a new record or update an existing one.

Most people have some code that looks like this:

SELECT COUNT(*) FROM table WHERE id=val1

IF COUNT(*) = 0 THEN
INSERT ....
ELSE
UPDATE ....

Straight from the MySQL manual:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

is equivalent to

UPDATE table SET c=c+1 WHERE a=1;

but if no value exists for a=1, then

INSERT table (a,b,c) VALUES (1,2,3)

Sometimes, it's those little extra I just love.

Wednesday, June 24, 2009

Service principal name when using WCF, net.tcp binding, and a domain service account

Service Principal Names (SPN) allow you to assign a name to a service and it will be used as a simple authentication mechanism. Basically, when the client connects to the service, the service sends the client it's SPN. The client also knows what SPN it expects. If they match, things are good.

In order to use the WCF NetTcpBinding with a Windows Service, it is necessary to create a SPN.

Here's the scenario:

We have a Windows Service that exposes a WCF NetTcpBinding service. The Windows service runs as a domain user account instead of LOCAL SYSTEM, NETWORK SERVICE, etc. Let's say the service is running under the account MyDomain\MyServiceAccount.

We have a client on the domain that will access the WCF service.

The problem arises when the client tries to talk to the server. Without an SPN, there is no way for the client to verify it has reached the appropriate service.

So, how do you use an SPN...

  1. We need to create an SPN and associate it with the service account. This is done using a utility called setspn.exe that is located in the Windows 2003 Support Tools.
  2. We need to create the SPN. An SPN consists of two parts, service and host. The format is \. In our case, the host really doesn't matter because we will attach the SPN to a username, not a computer. Here's the command:
    setspn -A MyServiceClass\MyHostName MyDomain\MyServiceAccount
  3. Next, on the client, we need to specify the SPN when we create an endpoint mapping. This can be done using the app.config file or programmatically. In the app.config file, in the section, add

    <identity>
       <serviceprincipalname value="MyServiceClass\MyHostName"/>
    </identity>

    Or we create the endpoint with the SPN..

    EndpointIdentity id = EndpointIdentity.CreateSpnIdentity("MyServiceClass\MyHostName");
    EndpointAddress addr = new EndpointAddress(new Uri(URI), id);




That's it. Now your client will be able to verify the service it is connecting to.