If you have physical SQL Servers that you plan on moving into a virtual environment you’ll want to double check your affinity mask settings before actually moving the machine from a physical server to a VM when using P2V software. The reason for this is that if the affinity mask is set for specific CPUs and the number of CPU cores changes the affinity mask won’t be correct and you won’t be able to get into the advanced settings of sp_configure without getting an invalid settings error like that shown below.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
If you haven’t P2V’ed the system before you do simply change the various affinity masks to 0 which sets them for all processors. If you have P2V’ed the system your best option is to log into the SQL Server using the dedicated admin connection and manually change the value in the system table by using the following query.
[sql]update sys.configurations
set value=0
Where Name = ‘affinity mask'[/sql]
Hopefully you never run across this problem, but if you do there’s the solution for you.
UPDATE: Paul Randal reminded me that CPU Affinity has been deprecated as of SQL Server 2008 R2 so you’ll probably not want to be configuring the CPU Affinity anyway.
Denny
One Response
There’s also the I/O affinity mask… and the 64-bit versions of both. Perhaps it might be better to use:
[CODE]WHERE Name LIKE ‘%affinity%'[/CODE]