SQL Server 2008 R2 BPA:

Question

Hello,

I installed:
1. W2k8 R2 as Domain Controller [ this is a test server ].
2. SQL Server 2008 R2 on it.
2. SQL Server 2008 R2 BPA [ Best Practices Analyzer ] on it.

Pre-requisites were also installed:
1. PowerShell 2.0
2. Microsoft Baseline Configuration Analyzer v2.0
3. SQL Server 2008 or SQL Server 2008 R2 Management Studio tools.

I tried to use BPA to analyze SQL Server but typical error occured:
"Analysis cannot be performed"

In collected data section:
SufficiencyCheck
IsPSRemotingEnabled = false

The above info is not correct, because:
1. User executing BPA is = Administrator of domain.
2. User has sysadmin role in SQL.
3. Commands:
       Enable-PSRemoting -f
       winrm set winrm/config/winrs `@`{MaxShellsPerUser=`"10`"`}
    were executed successfully.
4. Firewall is even disabled [ although it's local BPA scan ].

What can be wrong ?

---

best regards Janusz Such

12.12.2012:

any helpful answer will be very appreciated :)

Answer 1

Hi Janusz,

Any progress?

BPA is designed for administrators who want to determine the overall conformance of their SQL Server instances and topology with established best practices. BPA examines your SQL Server instance for system events, reviews login permissions, scans your SQL Metadata for advisable settings, and recommends solutions to items that do not conform with established Microsoft best practices.

Make sure that the pre-requisites are all be installed without problem.

Please refer to this similar thread:

http://connect.microsoft.com/SQLServer/feedback/details/667466/sql-server-2008-r2-best-practice-analyzer-bpa-problems-connecting-to-sql

http://social.msdn.microsoft.com/Forums/en-AU/sqlkjmanageability/thread/e6f5e265-3f6b-486f-b7aa-60e87c3ef7d0.

Thanks.

 

---

Maggie Luo
TechNet Community Support

Answer 2

Hello,

currently I have no time to debug BPA :)
BPA and all pre-requisites were installed without any problems.

Similar thread:
SQL 2008 R2 BPA errors out while running locally:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/5dc8ecbb-7f81-4389-a9fe-724195f7bad4

is also unanswered.

---

best regards Janusz Such

Answer 3

Hi,

The problem is with the BPA scripts, bugs have been reported with scripts, by enabling PowerShell files for BPA AND CORRECTING PATH problem you should be able run BPA,

Following directories are used

Report output directory        

%localappdata%\Microsoft\MicrosoftBaselineConfigurationAnalyzer 2\Reports\SQL2008R2BPAResults

Model configuration path   

 %Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA

Temp and log files directory    

%temp%\SQL2008R2BPA\SQL2008\<date>_<time>

Now use files for Powershell  located in

%Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA

Open PowerShell (using Admin rights) and open then the file SQL2008R2BPA.ps1

error is given from this file (IsPSRemotingEnabled = false)

To solve above issue

Set-Item WSMan:\localhost\client\trustedhosts * -force

After this step error should change,but a new error will appear to solve that below mentioned steps have to be taken

Now I open the file Engine.ps1 found in found in

%Programdata%\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA\Engine

the error is in this code:

" $SQLInstanceID = $SQLInstallPath[3] "

So simply modify Engine.ps1

from " $SQLInstanceID = $SQLInstallPath[3] "

to

" $SQLInstanceID = $SQLInstallPath[4] "

Also modify  ReportingServices.ps1

from

$RSInstance = $RSInstallPath[3]

to

$RSInstance = $RSInstallPath[4]

To make it simple use  find  and replace option, will not take more than 5 mniutes.

You may need to also modify AnalysisServices.ps1, IntegrationServices.ps1 and Replication.ps1 depending upon usage.

Without making these changes in scripts, it would be difficult to execute BPA 

---

Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

Waqas

MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
Twitter: @waqas8777
Linked In: http://www.linkedin.com/in/waqasm1

Answer 4

Great

My BPA has started to work, just took five minutes to make repalcements in BPA scripts and executing following command from power shell using admin rights

Set-Item WSMan:\localhost\client\trustedhosts * -force

Thanks Waqas

Answer 5

Hello Waqas !

thank you very much for your answer.

According to your post, I made corrections:

1. Replaced InstallPath[3] to InstallPath[4] in needed .ps1 scripts in:
    C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\SQL2008R2BPA
    and subfolders.

2. In powershell ( as administrator ):
    Enable-PSRemoting -f
    winrm set winrm/config/winrs `@`{MaxShellsPerUser=`"10`"`}
    Set-Item WSMan:\localhost\client\trustedhosts * -force

3. then run Microsoft Baseline Configuration Analyzer 2.0 ( as administrator )
    but error is the same.

I also changed: Set-ExecutionPolicy Unrestricted
but nothing changed.

Did I do something wrong ?
Perhaps, is it important that my server is also DC ?

---

best regards Janusz Such

Answer 6

Please read below article

http://blogs.msdn.com/b/psssql/archive/2010/06/21/known-issues-installing-sql-2008-r2-bpa-relating-to-remoting.aspx

---

Manish

Answer 7

Hi,

Are you getting the same error screen or error has changed , because in my experince the error changes when  you execute

Set-Item WSMan:\localhost\client\trustedhosts * -force

Did I do something wrong ?

From your post it is not clear you executed

Set-Item WSMan:\localhost\client\trustedhosts * -force

using domain admin account in power shell.

Perhaps, is it important that my server is also DC ?

 DC or no dc does not matter only DC admin must have sys admin rights on sql server.

Remoting issues wil not exist for you

---

---

Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

Waqas

MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
Twitter: @waqas8777
Linked In: http://www.linkedin.com/in/waqasm1