How to store the SQLBPA recommendations in a SQL database to make more use out of the recommendations data

Lot of us use SQL Best Practices Analyzer (BPA) to detect any potential errors, warnings, recommendations in your environment. It’s not possible to go through every item and fix it on the BPA GUI instantly. So we need a mechanism to export that data somewhere for future reference, so that we can fix the items one by one and can ignore the ones which are not really required in our environment.

Fortunately we have an option in BPA to export the recommendation data into an XML file. But the problem here is it is very difficult for people to go through that XML (it’s very bulky) and understand that to make a good sense out of the recommendations. Some of the customers of mine expressed an idea of materializing this XML data which is very difficult to understand into relational data (represents through tables) which is very easy to understand.

The other benefits of doing that are

· It enhances the ease of understanding

· You can run reports and determine how many alerts are there, how many errors are there, how many warnings are there etc…

· You can build your own custom applications on top of that data, prepare beautiful charts, reports and show them to your senior management

· We can prioritize our action plans like first we want to see how many warnings/errors/informational messages are there and based upon that we want to decide which to address first etc…

And the list goes on based upon your creativity…the whole point here is bringing that data into much usable format

A sample snippet from the XML file generated by the BPA looks like this

<Objs Version="1.1.0.1" xmlns="https://schemas.microsoft.com/powershell/2004/04">

<Obj RefId="0">

<TN RefId="0">

<T>Microsoft.BaselineConfigurationAnalyzer.CoreInterface.Model</T>

<T>System.Object</T>

</TN>

<ToString>Microsoft.BaselineConfigurationAnalyzer.CoreInterface.Model</ToString>

<Props>

<S N="Id">SQL2008R2BPA</S>

<S N="Company">Microsoft</S>

<S N="Name">SQL Server 2008 R2 BPA</S>

<Version N="Version">1.0</Version>

<S N="LastScanTime">10/03/2013 13:29:35</S>

<Obj N="SubModels" RefId="1">

<TN RefId="1">

<T>System.Collections.Generic.List`1[[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]</T>

<T>System.Object</T>

</TN>

<LST>

<S>AnalysisServices</S>

<S>Engine</S>

<S>IntegrationServices</S>

<S>Replication</S>

<S>ReportingServices</S>

<S>Setup</S>

</LST>

.

.

.

.

<Obj RefId="19">

<TN RefId="5">

<T>System.Object[]</T>

<T>System.Array</T>

<T>System.Object</T>

</TN>

<LST>

<Obj RefId="20">

<TN RefId="6">

<T>Microsoft.BaselineConfigurationAnalyzer.CoreInterface.Result</T>

<T>System.Object</T>

</TN>

<ToString>Microsoft.BaselineConfigurationAnalyzer.CoreInterface.Result</ToString>

<Props>

<S N="ResultNumber">1</S>

<S N="ResultId">4196529084</S>

<S N="ModelId">SQL2008R2BPA</S>

<S N="SubModelId">Engine</S>

<S N="RuleId">0</S>

<S N="ComputerName">localhost</S>

<S N="Context">SQL2008R2BPA</S>

<S N="Source">localhost</S>

<S N="Severity">Warning</S>

<S N="NeutralSeverity">Warning</S>

<S N="Category">Security</S>

<S N="Title">Engine - Authentication Mode</S>

<S N="Problem">The authentication mode is not set to recommended value</S>

<S N="Impact">Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration</S>

<S N="Resolution">Configure Authentication mode to Windows mode when possible</S>

<Nil N="Compliance" />

<S N="Help">https://go.microsoft.com/fwlink/?LinkId=184392</S>

<S N="Excluded">False</S>

</Props>

</Obj>

<Obj RefId="21">

<TNRef RefId="6" />

<ToString>Microsoft.BaselineConfigurationAnalyzer.CoreInterface.Result</ToString>

Please find the exercise I performed to push this valuable XML data into a table in the SQL server database

1). Modified the first line i.e. the Objs element – Changed it to something like this (<Objs xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema">)

2). Deleted the unnecessary content from the XML file (Till Ref ID 19 from the top)

3). Replaced "Nil" with "S". The resultant XML schema looks like this

<Objs>

<Obj>

<Props>

<S> </S>

</Props>

</Obj>

</Objs>

- Only one Objs element

- Multiple Obj elements inside Objs element

- Only one Props element Inside Obj element

- Multiple S elements inside Props

4). Create the table to put the XML data

CREATE TABLE EXPRTRESTRCT19TBL (XMLDATA XML, LOADDATETIME date)

5). Prepare the insert statement by replacing the content between quotes with your new XML content.

Insert_Code.SQL

To see the complete insert statement please click on the Insert_Code.SQL

After insert the table TEST2_CMCK_CSSPRDDB03_EXPRTRESTRCT12 contains the formatted XML data

6). Create a table temp_values using the statement below

"create table temp_values (UID integer identity,ID Integer,value NVARCHAR(MAX))"

7). Run the stored proc to populate temp_values table

declare @idoc int,

@xmldata xml

Select @xmlData = XMLDATA from dbo.EXPRTRESTRCT19TBL --MAKE SURE YOU CHANGE THE TABLE NAME TO THE ONE YOU CREATED IN STEP-4

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldata

insert into temp_values (id,value)

Select * from Openxml(@idoc,'Objs/Obj/Props/S',1)

With (

ID NVARCHAR(MAX) '../S',

Value NVARCHAR(MAX) 'text()'

)

EXEC sp_xml_removedocument @idoc

8). Create a final result table which stores all the formatted output i.e. the final output

drop table final_result_exprtresrct19

create table final_result_exprtresrct19 (ResultNumber NVARCHAR(MAX),ResultId NVARCHAR(MAX),ModelId NVARCHAR(MAX),SubModelId NVARCHAR(MAX),RuleId NVARCHAR(MAX),ComputerName NVARCHAR(MAX),Context NVARCHAR(MAX),

Source NVARCHAR(MAX),Severity NVARCHAR(MAX),NeutralSeverity NVARCHAR(MAX),Category NVARCHAR(MAX),Title NVARCHAR(MAX),Problem NVARCHAR(MAX),Impact NVARCHAR(MAX),

Resolution NVARCHAR(MAX),Compliance NVARCHAR(MAX),Help NVARCHAR(MAX),Excluded NVARCHAR(MAX));

--MAKE SURE YOU CHANGE THE TABLE NAME TO THE ONE YOU CREATED IN STEP-4

9). Execute the stored proc which takes input from temp table and formats the data and push it into the final result table we created in step-8

-- REPLACE THE Set @index = <WITH THE VALUE YOU GET FROM SELECT COUNT(*) FROM TEMP_VALUES>

Declare @counter int,

@index int

Set @counter = 1

Set @index = 1278

While @counter <= @index

Begin

insert into final_result

select

max(case when UID=@counter then value else '0' end) as ResultNumber,

max(case when UID=@counter+1 then value else '0' end) as ResultId,

max(case when UID=@counter+2 then value else '0' end) as ModelId,

max(case when UID=@counter+3 then value else '0' end) as SubModelId,

max(case when UID=@counter+4 then value else '0' end) as RuleId,

max(case when UID=@counter+5 then value else '0' end) as ComputerName,

max(case when UID=@counter+6 then value else '0' end) as Context,

max(case when UID=@counter+7 then value else '0' end) as Source,

max(case when UID=@counter+8 then value else '0' end) as Severity,

max(case when UID=@counter+9 then value else '0' end) as NeutralSeverity,

max(case when UID=@counter+10 then value else '0' end) as Category,

max(case when UID=@counter+11 then value else '0' end) as Title,

max(case when UID=@counter+12 then value else '0' end) as Problem,

max(case when UID=@counter+13 then value else '0' end) as Impact,

max(case when UID=@counter+14 then value else '0' end) as Resolution,

max(case when UID=@counter+15 then value else '0' end) as Compliance,

max(case when UID=@counter+16 then value else '0' end) as Help,

max(case when UID=@counter+17 then value else '0' end) as Excluded

from temp_values

Set @counter = @counter + 18

End

-- We have to make sure that this count i.e. 18 is the number of 'S' elements under the 'props' element

9). after executing the above stored procedure we’ll have all the XML data formatted in a nice tabular format in the final results table

A simple select statement from the final results table looks like this

For example if you want to concentrate first on all the warnings, you can issue a simple select statement like

SELECT * FROM FINAL_RESULT WHERE SEVERITY=’Warning’;

So this way we can arrange the BPA data in a more meaningful way and run reports and queries on those tables to make more sense/use of it.

As always feel free to provide your feedback/comments on the article.

Thanks,

Chandra