CPU Affinity Mask and virtualizating SQL Servers

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

Share

One Response

  1. 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]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?