mercredi 4 mars 2015

How can I speed SQLPS connection when I don't have WMI permissions at the server


When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and it's VERY slow to connect.


It's possible to suppress the warning messages with $WarningPreference = 'SilentlyContinue', but is there any way to disable the actual checks, in order to speed performance?


Example:


PS SQLSERVER:\> set-location \sql\myserver\default WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))


Scenario: SQL Agent job using a PowerShell job step takes 30+ seconds just to spin up and connect to the local SQL Server, before doing any work.





Aucun commentaire:

Enregistrer un commentaire