Sql-server – Syspolicy_purge_history connection issue

sql serversql-server-2008

I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system. The server is using a non default port number and is in a cluster. Before it was put in a cluster, it was a stand alone box and the job ran with no issues with the non default port number. It is failing at step 3, which is

(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()

I have already addressed an issue of using the computer node name instead of the virtual server name as we have other servers in clusters.

The only difference with this setup is that it is not using the default port of 1433. The server is already set to receive remote connections.

Here is the error.

The job script encountered the following errors. 
These errors did not stop the script:  
    A job step received an error at line 1 in a PowerShell script. 
    The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()'. 
    Correct the script and reschedule the job. 
    The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Could not connect to 'VirtualServer\DEFAULT'. [Failed to connect to server VirtualServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
    The server was not found or was not accessible. 
    Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

Best Answer

You will need to put the port number in there. When using the default instance the SQL Browser is not contacted in order to get the port number to use to connect. Because of this you have to specify the port number when connecting otherwise the OLE/ODBC driver doesn't know what TCP port number to connect to.

Please clarify how you took a standalone server and made it a cluster. That isn't supported unless you uninstalled SQL and reinstalled it.