How to install SQL Configuration Manager

When moving Sharepoint Foundation 2010 databases, the official advice is to set up a SQL Alias so that the Web Application Servers point at the new SQL server while using the old SQL Server name.

In order to do this, you need to have installed SQL Server Configuration Manager on the Web and Application Servers.

It's very easy to install SQL Server Configuration Manager without installing all the rest of SQL Server: just install the 'Client Connectivity Tools'. Ensure you install the version of the tools which matches the version of SQL Server installed on your database server.

• Choose Install -> New installation or add features to an existing installation.
• Choose all defaults until you get to the Feature Selection tab
• Select Shared Features -> Client Tools Connectivity
• Choose all remaining defaults and confirm installation

And that's it. No restart required. I did find that CPU on the server shot up to 100% for much of the install in our QA environment, so I don't recommend doing this in anger during office hours. Leave it to out of hours if you can.

About concurrency....

Twice, in the last week, concurrency issues have jumped up to bite me.

The first time round, the server had 'NO COUNT' set to on. This confused the .Net software, which couldn't tell if any rows had actually changed before it updated the table: LINQ to SQL uses @@ROWCOUNT after updates to issue an optimistic automated concurrency check. If 'NO COUNT' is on, the @@ROWCOUNT value is always zero, and the concurrency check fails.

The second occurred while using the SSMS GUI to try and edit data in a table: right click on the table, pick 'edit top 200 rows'. The error we got was

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

Again: concurrency issues. So, the first thing I did was to check whether 'NO COUNT' was on. It wasn't. Then I went in for a bit of google. It turns out the problem was in the data.

Several rows in the table were identical, and contained text fields. Despite having an 'id' column, the table didn't have a primary key. Via the GUI, SQL couldn't work out which row to delete, and, of course, running a DELETE T-SQL statement would have deleted all the rows. We wanted to keep one of them (natch).

To tidy up the data, I ran an INSERT, based upon a SELECT TOP 1 T-SQL statement (it was not possible to SELECT DISTINCT - there were text fields involved), but with a new ID value. Then I DELETEd the duplicate data, and finally UPDATEd the new row to the original ID value.

At this point, we tried changing some other data in the table. We had the same error message, despite the fact that the data was, this time, unique.

Unique, but without a primary key. Redesigning the table to include a primary key on the 'id' column (and why there wasn't one on there in the first place is anyone's guess) has made it possible to update the data via the GUI.

There's one last reason why this particular concurrency issue may happen: data. Columns containing '%', '_' or '[' can also cause issues.