Import-Module SqlPs may take longer to load but always returns warnings when the Azure PowerShell module is also installed. - by MatticusAu

Status : 


Sign in
to vote
ID 1420992 Comments
Status Active Workarounds
Type Bug Repros 18
Opened 6/10/2015 8:34:49 PM
Access Restriction Public


When importing the SqlPs module into a PowerShell session on a system with the Azure module also installed, there is a significant delay during import and eventually WMI connection warnings returned. This is due to the fact that the Azure PowerShell module contains a folder structure that includes /SQL which contains a PowerShell script to load the sql data types from, which due to this folder structure is treated like a SQL Server connection due to the logic within the SQL Server Providers implementation of Get-Item and the check that if the root of the path is /SQL it should be checked to see if it is a SQL Server.  I have checked this in the source code so if you need further details please contact me through internal channels.
Sign in to post a comment.
Posted by Samson J. Loo on 7/14/2016 at 11:30 AM
On my laptop I have SQL Server 2014 Developer SP1 and am running PowerShell 5.0 with Azure PowerShell as well. Once I upgraded to SQL2014 SP1 CU7 the warnings went away. On our DBA SQL Server box we upgraded from SQL 2014 to 2016 RTM but that is running PowerShell 4.0 and am still seeing the warnings. I assume once I upgrade our DBA box to Windows Management Framework 5.0 that should resolve the issue as PowerShell will be upgraded to 5.0.
Posted by Microsoft on 6/29/2016 at 8:09 PM
The issue has been fixed in SQL2016 RTM. The fix is also available for SQL2014 SP1 (in CU7), which was released in June 2016.

Posted by EFD7887 on 6/2/2016 at 6:38 AM
Same thing for SQL Server 2012....very annoying
Posted by Microsoft on 5/3/2016 at 4:39 PM
I just wanted to follow up on this fairly old connect issue.

First off, I apologize for the delay.

There are a couple of issues at play here:
1) At PowerShell bug where PowerShell logic to do "Get-Module -ListAvailable" incorrectly loads files (e.g. DLLs, .ps1xml, etc) in the context of the current provider, as opposed to the context of the "file system" provider (this causes the SQL Provider to try to connect to entities that may look like SQL Server, but they are not - as stated in the description of this issue; there isn't anything intrinsically wrong that the SQL Provider is doing here.)
2) A sub-optimal logic implemented in the SQLPS module to load the SQLASCmdlets module (as part of loading the SQLPS module)

In SQL2016, we addressed a couple of issues that should make this issue go away. The logic that load the SQLPS module has been updated in a couple of places:
A) The expensive enumeration of all the existing modules on the machine to import the SQLASCmdlets module has been removed.
B) The user is no longer put in the context of the SQL Provider (SQLSERVER:) after importing the SQLPS module.

As a result of #A and #B above, the issue reported here goes away. #A gives a nice overall boost when there are a lot of modules on the machine; #B is beneficial as well, in that is mitigates the PowerShell bug mentioned in #1 (this results in the SQL Provider not being called, which in turn results in no need to try to connect (via WMI/DCOM) to a remote machine that does not exist)

Few other things worth mentioning:
- I've followed up with the PowerShell Team to have their bug fixed
- I've also followed up with the Azure PowerShell folks with yet another simple workaroud that they may be able to put in place to mitigate the issue - pull request almost ready...
- We (SQL Team) are looking into backporting this change to the SQL2014 as well (#A mentioned above), to help folks who cannot upgrade to SQL2016 right away

Hope this helps!
Posted by Brett Jacobson on 3/29/2016 at 5:09 AM
We need to use SQLPS inside TFS build scripts, and this problem is causing nearly a 5 minute delay to our build process for no good reason :(
Posted by MatticusAu on 6/24/2015 at 5:30 AM
@KeyRey if you are using PowerShell 3+ and haven't run Import-Module yet then yes this is likely to be the same issue as the first time you execution Invoke-SqlCmd it will perform the Import-Module functionality. Any further calls to Invoke-SqlCmd shouldn't cause the errors if this is the same issue. Can you confirm this to help us drive the fix?
Posted by KeRey on 6/19/2015 at 9:19 AM
I am seeing the same issue when running Invoke-Sqlcmd in powershell. Is this the same root issue or should I log a separate bug?