Multicore Awesomeness

I am currently working with a customer who is looking at deploying a bunch of HP Proliant DL785 G6 servers. These mega machines can hold up to 512 GB RAM, and support 8 AMD Opteron Processors, which have 6 cores each. That’s 48 processing cores in total. Staggering when I think back to my old 386 in the garage….

The Data Deluge

The Economist recently ran an excellent 14 page special report on the exponential growth of data. The far-reaching effects of data availability are fascinating – for example, when statistics on restaurants violating health regulations were published online, the problem largely resolved itself. Restaurants were forced to get their act together, or go out of business! Data is definitely the currency of the modern era.

The description of how Google uses feedback from users to refine its systems is also fascinating. Take for instance the spell checker – which is now accurate in over 50 languages, based on people clicking those “did you mean….” links. Compare that to Microsoft spending millions of dollars to build the conventional MS Word spellcheck, based on a complex set of rules. Sometimes an approximation based on statistics gets you close enough to the truth!

How to Change to Collation of a Database

Today I had the unpleasant task of changing the collation on several databases in a SQL 2005 environment. This essentially involves copying the data out, changing the collation of the underlying tables and then putting the data back again. Messy!

The ALTER DATABASE … COLLATE command gets you part way there. New objects will be created using the new collation, but what about all those object which were already present? Sadly they are not affected.

Luckily for me, Igor Kovalenko made this awesome script a few years back. It essentially moves your data out, changes the collation of the individual tables, and puts your data back where you expect it to be.

I then used RedGate SQLDataCompare to ensure that the data was consistent with a backup taken earlier on.

The only problem I hit with the script was with a VARCHAR(MAX) column. Changing the datatype to VARCHAR(8000) did the trick.

Server Name Mismatch

When I was configuring replication at a customer site today, I came across a problem where the internal name of a SQL Server didn’t match the machine name. I replicated (ha!) the problem on my laptop:

The message reads: “SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address or any other alternate name are not supported.”

To fix this problem, check the values of:

select serverproperty ('servername')

and

select @@SERVERNAME

As indicated in the error message, you will most likely find that they return different values. This is enough to confuse your Configure Distribution Wizard. I then used:

sp_dropserver ‘incorrect_server_name’

sp_addserver ‘correct_server_name’, local

And restarted the SQL service for good measure. More info on these procs is available in books online.

In my case, the problem was caused by the SQL Server having been built from a dodgy machine image, and the windows server having been subsequently renamed. Something to be aware of when renaming a server!