The request came in to virtualise a SQL Server / SSAS / web reporting system that was provided by a third party. An initial attempt to do this was performed by p2v-ing the existing servers. Everything seemed to be fine until the SQL Server instance was started. The server appeared to start correctly, but the SQL Agent wouldn’t start, SSMS claiming that the Agent XPs were disabled. Attempting to enable the Agent XPs in the usual way:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'Agent XPs', 1 RECONFIGURE WITH OVERRIDE
This failed, badly and worryingly with the following message:
Msg 5832, Level 16, State 1, Line 2
The affinity mask specified does not match the CPU mask on this system.
What seems to have happened is that the third party provider of this system had set an Affinity Mask of 63, limiting SQL Server to using six of the available eight CPUs; unfortunately, the virtual environment was restricted by licensing to only four CPUs.
Changing the affinity mask should be relatively straightforward under normal circumstances:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'affinity mask', 1 RECONFIGURE WITH OVERRIDE
However, this fails with the error message above.
A little Googling pointed me to the accepted answer to a question on the MSDN Social Forums, the gist of which is:
- Restart SQL Server in single user mode
- Connect using the Dedicated Administrator Connection
- update the sys.configurations table directly, setting the value to 0 for the parameter “affinity mask”
- restart SQL Server
However, this misses out a couple of potential “gotchas”:
- Affinity IO mask
- 64-bit affinity masks – these exist for use in systems where there more than 32 processors
So if you find yourself in this situation, it’s worth updating all the options by using:
UPDATE sys.configurations SET value = 0 WHERE name LIKE 'affinity%'
Just in case… 😉