PowerShell e o dia a dia do DBA – VII


Continuação – VI  / Checar as Policies que não passaram na validação

Todos os scripts eu criei um database chamado DBA no meu servidor central e um schema diario. As tabelas sermpre serão criadas neste schema e database.

Para todos os scripts, gerar um txt com os nomes dos servidores  envolvidos. Eu o chamei de servidores.txt e fica assim :

SERVER1\MSSQLSERVER_1
SERVER2\MSSQLSERVER_2
SERVER3\SQL2005

Os dois primeiros são 2008 e o terceiro 2005

Eu o gravo numa pasta chamada c:\dadosps, vocês irão ver esta chamada em todos os scripts

 

Policies

Checar as Policies que não passaram na validação

 

Este script eu não vou reinventar a roda. Já tem um prontinho feito pelo DAN JONES.

AS policies tem que estar num servidor central, e são aplicadas a todos os servidores que estiverem na lista servidores.txt. (2000 para frente..óbvio que respeitando a feature que cada um tem…nao vou ver se tem compressão de dados no 2000) Aqui ele usa a categoria DATABASE. Podemos criar uma categoria e colocar as policies que rodem em todos os servidores.Caso haja alguma que não passou na validação, vai pra uma tabela..detalhe..em XML.

Artigo original

http://blogs.msdn.com/sqlpbm/archive/2008/06/14/running-against-sql-server-2005-and-sql-server-2000.aspx

Criar a tabela

USE [DBA]
GO

CREATE TABLE [diario.PolicyHistory(
     [EvalServer] [nvarchar](50) NULL,
     [EvalDateTime] [datetime] NULL,
     [EvalPolicy] [nvarchar](max) NULL,
     [EvalResults] [xml] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PolicyHistory] ADD  CONSTRAINT [DF_PolicyHistory_EvalDateTime]  DEFAULT (getdate()) FOR [EvalDateTime]
GO

Script PS

#Evaluate Policies in a Particular Category against a Server list
#Uses the Invoke-PolicyEvaluation & Invoke-SqlCmd Cmdlets

function InsertPolicyHistory($ServerVariable, $DBVariable, $EvalServer, $EvalPolicy, $EvalResults)
{
    #Escape single quotes so we can insert
    $EvalResultsEscaped = $EvalResults -replace "’", "”"
    $EvalPolicyEscaped = $EvalPolicy -replace "’", "”"

    #Setup the insert statement
    $QueryText = "INSERT INTO diario.PolicyHistory (EvalServer, EvalPolicy, EvalResults) VALUES(N’$EvalServer’, ‘$EvalPolicyEscaped’, N’$EvalResultsEscaped’)"

    #Run the insert statement using the Invoke-SqlCmd Cmdlet
    Invoke-Sqlcmd -ServerInstance $ServerVariable -Database $DBVariable -Query $QueryText
}
#CONSTANTS
#Declare the Server\Instance & database to post the policy results
$HistoryServer = "myServer\myInstance"
$HistoryDatabase = "DBA"
#Declare the server\instance for the Policy store
$PolicySourceServer = $HistoryServer
#Setup the file containing the list of servers
$ServersFile = "c:\dadosps\Servidores.txt"
#Setup the location to dump the policy evaluation result output
$PolicyDir = "c:\PolicyEvaluation\"
#Setup the policy filter – only policies in this category will be processed
$PolicyCategoryFilter = "DATABASE"

#Setup a connection to the policy store
$Conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$PolicySourceServer;Trusted_Connection=true");
$PolStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn);

#Read the servers file into a variable
$Servers = Get-Content $ServersFile

#Clear out the directory where the evaluation results will go 
del c:\PolicyEvaluation\*

foreach ($TargetServer in $Servers)
{
    foreach ($Policy in $PolStore.Policies)
    {
        if ($Policy.PolicyCategory -eq $PolicyCategoryFilter)
        {
            #Clean-up any invalid file system characters
            $PolicyNameFriendly = (Encode-SqlName $Policy.Name)
            $TargetServerFriendly = (Encode-SqlName $TargetServer)

            #Setup the output file as Server_Policy.xml
            $OutputFile = $PolicyDir + ("{0}_{1}.xml" -f $TargetServerFriendly, $PolicyNameFriendly); 

            #Evaluate the policy
            Invoke-PolicyEvaluation -Policy $policy -TargetServerName $TargetServer -OutputXML > $OutputFile;

            #Read in the policy evaluation results to load it into the result table
            $PolicyResult = Get-Content $OutputFile;
            #Insert the results to our result table
            InsertPolicyHistory $HistoryServer $HistoryDatabase $TargetServer $Policy.Name $PolicyResult;
        }
    }
}
#Clean-up the evaluation result files
del c:\PolicyEvaluation\*

After you run the script you can use SQLCMD or SSMS to query the result table. The query below will return all rows where the policy was violated. To get back all of the rows where the policy passed change "false" to "true".

USE [PolicyResults]
GO
WITH XMLNAMESPACES (‘http://schemas.microsoft.com/sqlserver/DMF/2007/08′ AS Pol)
SELECT *
FROM dbo.policyhistory
WHERE EvalResults.exist(‘//Pol:EvalDetail/Pol:Result/text()[. = "false"]’) = 1

 

Prontinho…mais um

I´ll be back

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s