Troubleshooting SQL Mirror errors

I was setting up a new database server and I had some difficulty in setting up the mirroring, so I thought I would post here just on the off chance it helps someone. We are still using SQL 2005, but I think the same ideas apply to SQL 2008.

When I tried to start mirroring I would get an error like this:

The server network address "tcp://[server name]:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

First and foremost the single most important thing I learned was this: If you get an error while trying to start mirroring check the application event logs on both servers. The error messages you get back from the UI are not helpful at all. The event log messages will lead you very quickly to the error.

In my case, the first error I got was:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'. [CLIENT: ]

In my case, this error indicated that the two database endpoints did not have the same encryption algorithm selected. I was working with an existing server that has mirroring already running and a new server that did not yet have mirroring running. I didn't pay attention in the mirror setup wizard and did not check the box to indicate that the connection should be encrypted. This left the original server requiring encryption and the new server without encryption. You can check this by running the following query on both servers:

SELECT * FROM sys.database_mirroring_endpoints

Compare the values of the encryption_algorithm_desc column from both servers. In my case I had one server with RC4 and one server with None. They have to match. The solution was to drop the endpoint on the new server ('DROP ENDPOINT Mirroring') and re-run the mirror setup wizard and checking that the mirror server required encryption.

Once I figured out that error, the second error I got was:

Database Mirroring login attempt by user '' failed with error: 'Connection handshake failed. The login '' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: ]

Again, it was a configuration problem. In this situation it was because I had forgotten to set the user that the SQL service was running as. I believe SQL has to be running as the same domain user on both servers or, I believe there is some way to use certificates. In my case, my existing server was using the domain ID, but on the new server the service was running as 'Local System'. You can check this by examining the credentials used by the the 'SQL Server' service in the server administrator tools. Once I set the new server to use the same domain ID, mirroring started up successfully.

I hope this saves you some time.

Understanding Mercurial Subrepositories

UPDATE: Subrepositories proved to be a bit unwieldy. They worked, but it wasn't worth the hassle. What finally made all our pieces fall into place was nuget. We keep the repositories separate from each other and publish any shared binaries to our own internal nuget server. We then use nuget to pull in dependencies. It is working quite well. (We have also switched to git, but that doesn't matter too much for this post.)

My client has started slowly moving towards using Mercurial for source control. I've only used Hg for small personal projects in the past so it will be interesting to see how things work.

One hurdle that we hit pretty early was how to best structure our repository. The general recommendation is to avoid one large monolithic repository and break your source into multiple subrepositories. The suggestion is to use one subrepository per "project", but what exactly is a project?

Our approach is to divide up and organize our source code into medium size groups. Each of the groups can contains several visual studio projects -- some groups will only output one or two assemblies when built, some groups could be as large as 20 or so assemblies. Each group is its own repository. These repositories can be organized into larger main repositories.

For example, we have some shared assemblies that contain simple POCO objects that represent message classes used in different layers of our application. The visual studio projects for these message classes are in one repository. This repository is cloned as a subrepo to other repositories where needed, for example in the windows client repository, or in the silverlight client.

The whole concept of subrepositories did not quite make sense to me at first, but I think I am beginning to understand it. You have to think of each subrepository as a completely isolated versioning concept.

For example, say you have a directory/repository structure somewhat like this:
\client\src\
\client\shared\
\client\shared\Messages\

If you make a change to \client\shared\Messages\test.txt

There are two steps that occur:
1. You commit the change to the Messaging subrepository.
2. You commit that the parent repository should point at a new version of the subrepository.

(This works automatically when you commit from the command line. For some reason, I can't get it to work when using TortoiseHg.)

Step 2 was the one that was confusing to me at first. The parent repository has a list of all subrepositories and what version of that subrepo is being used. This is in a file called '.hgsubstate'. The hg tooling updates this file for you and this is how the parent repository points to a specific version of the child repository. So when you commit the parent repository in step 2, this is what is being committed. It is a little hidden from you by the tooling.

Please keep in mind I am not an expert in Mercurial, this is really the first time I am using it in a real scenario. I am still learning and if I have time I will post as we go forward with this change.

Best Joke I've been a part of: AromaCard

First a little disclaimer: I was not the mastermind behind this joke. I was only barely involved.

After graduating from college I worked for a consulting company ("sweat shop") and I was placed on a project at large electronics retail company. This was several years ago and a lot of people were not as computer literate as they are today.

I can't actually remember the name of the person that came up with the joke. (I do remember what he looked like. Does that count?) I think his name was Steve and he was a practical joke genius. Steve was surfing the web one day. He found a picture of one of those tree shaped air fresheners and hatched a brilliant plan.



The joke:
Step 1: Buy several car air fresheners (pine scented) and hide them in someone's cubicle.
Step 2: Change the victim's computer background to be the picture of the air freshener.
Step 3: Leave a note on the victim's desk that he or she had been selected by the IT department to participate in a trial of a new computer technology called an AromaCard. The IT staff would return in a couple weeks and interview the victim about his or her experience with the AromaCard.

Keep in mind, these car air fresheners come with instructions to only open a small amount of the package covering the air freshener each week. They are very strong. So, imagine a handful of these things fully opened in an standard cubical. The place absolutely reeked.

As far as I know, this joke was only played on two people. The first person was immediately angry. She called the IT help desk and DEMANDED they come and remove the AromaCard from her computer. I can't even imagine what the guy at the help desk was thinking. So, the first attempt ended quickly.

The second person was a different story. He was proud to have been selected for this trial. He was proud to the point that he might have been bragging about it a little bit. After a couple days everyone knew about the joke but him.

The joke went on for several weeks. One day the victim left work early and Steve decided to up the ante a little bit. They swapped all the pine air fresheners in his cubicle with lemon scented ones and changed the picture on his desktop to a yellow air freshener. They left him a note that he had been upgraded to the new lemon scent.

Several more weeks passed and it was time to let the guy in on the joke. This is where I came in. Because I had an IT email address I was in charge of communicating with this guy. After several unsuccessful attempts to schedule an appointment to "uninstall" his AromaCard, the plan evolved a little more.

An urgent note from the IT department was left at his desk. He had to immediately uninstall the AromaCard or the cost would be deducted from his pay. The memo then had a bunch of steps, like turning his computer around and unplugging all the wires, etc. For a non-computer person this can be pretty scary. The very last step was to look under his chair.

This is the voice mail he left me. I've muted out his name and phone number.

AromaCard Voice Mail

So, that is the best joke I've ever been remotely involved in. (Very remotely.) I remember writing a little application that would sit in the system tray and allow the victim to adjust the scent volume of the aroma card. However, I don't remember if we ever put it on his computer.

WCF - AccessDeniedException HTTP could not register URL

I was fooling around with WCF today and started getting the following exception:

AddressAccessDeniedException: HTTP could not register URL http://+:8370/TestService/. Your process does not have access rights to this namespace.

The problem is pretty simple, but it took me a little while to figure out. To host the WCF service, you need to have the ability to register the URL. I run visual studio as a non-administrator, and this requires administrator privileges.

But wait just one second, this service was running fine just a couple minutes ago and I didn't have administrator privileges then. How did that work? Well, it seems that VS automatically registers any URLs in the form of http://[server]:[port]/Design_Time_Addresses/[Rest of Service Url] which allows them to be hosted.

So, I was trying to be too fancy and I was trying to change the URL to get rid of the "Design_Time_Addresses" part of the URL. I added that back to the URL and I was able to host the service again.

If you want to host the URL without the "Design_Time_Addresses" bit, you will have to register (as admin) the URL. Follow the link in the exception details to see how to register the URL.

WCF blank page - some fixes

I was having a bit of trouble getting my WCF service to behave nicely. Specifically, I kept getting a blank page instead of the friendly WCF service page when I used a browser to hit the service. I stumbled on a couple things.

1. The URL you are using in your browser has to end with a slash, for example http://servername:8080/TestService/ will result in a WCF service page. If you forget the slash at the end, you get a blank page.

2. In the config file, base addresses and relative addresses in the endpoint do not seem to play nicely. I had to put the full URL (with a slash at the end) in the base address and leave the endpoint address blank (address="").

I must be missing something. Configuration files should not be this finicky.

SQL Server error: Conversion of XML characters

I hit an interesting error in SQL Server today. A user had somehow entered a unicode character that could not be converted to ASCII. In a stored procedure, the XML column was being converted to a varchar and this error occurred:

"Conversion of one or more characters from XML to target collation impossible"

You can recreate the scenario with the following TSQL code:

DECLARE @xml xml, @nvc nvarchar(64)
SELECT @nvc = 'Coptic letter: ' + nchar(997) + ''
SELECT @nvc as 'before conversion'
SELECT @xml = @nvc
SELECT convert(varchar, @xml) -- fails with conversion error

The conversion from XML -> varchar fails because the Unicode value could not be represented. There are a couple options here. You can use nvarchar instead of varchar. This would support the unicode value:

SELECT convert(nvarchar, @xml) as 'xml -> nvarchar conversion'

You can also convert it to nvarchar and then to varchar. This results in any non-mappable characters being changed to a question mark, like so:

SELECT convert(varchar, convert(nvarchar, @xml)) as 'xml -> nvarchar -> varchar conversion'

In my particular situation I added some code to clean up the data before it is sent to the database. I specified that the stream reader that was producing the data to be inserted into the XML column should use the ASCII encoding. This results in the non-mappable characters being translated to question marks before it goes to the database. That is just what I did in this particular case. It may not apply in your situation.

EF error: Unable to update the EntitySet 'tablename' because it has a DefiningQuery and no

I got a strange Entity Framework error today at runtime. I was trying to insert a new entity/row into the database and I got the following error:

"Unable to update the EntitySet '[tablename]' because it has a DefiningQuery and no element exists in the element to support the current operation."

It turns out the table did not have a primary key defined. Whoops. Because of this, EF did something a little different. It basically treated the table as a view and it seemed to use a combination of all the foreign keys in the table as a composite key, I think.

The solution was to define the primary key for the table and then update the model. This fixed most of the problem, but it also introduced a compile time error about the model. It turned out I then had to go and manually mark the foreign key fields as not part of the primary key.