PowerSQL – Get SQL TraceFlag SQL Service and Config Information

Get SQL Server configuration, service info, start type along with enabled Trace Flags of local or remote servers.

This script outputs sql server configuration, service, start type, Service Account along with startup trace flags. The script will work on SQL Server 2005 and up.

function Get-SQLAndGlobalTraceFlagsDetails { 
<# 
.SYNOPSIS 
Returns SQL Server Configuration, Service and global TraceFlag details 
 
.DESCRIPTION 
Get-SQLAndGlobalTraceFlagsDetails is a function that returns server high level server configuration information for 
one or more Microsoft SQL Server 
 
.PARAMETER ComputerName 
The computer that is running Microsoft SQL Server that you’re targeting 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName sql01 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName HQDBSP18 
 
.EXAMPLE 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails -ServerName $_ } 
 
.EXAMPLE 
 
ForEach ($server in Get-ChildItem C:\server.txt) 
{ 
Get-SQLAndGlobalTraceFlagsDetails -ServerName $server 
 
} 
 
.INPUTS 
String 
 
.OUTPUTS 
PSCustomObject , console or CSV 
#> 
[CmdletBinding()] 
param ( 
[Parameter(Mandatory=$true, 
ValueFromPipelineByPropertyName=$true)] 
[Alias('ServerName','PSComputerName','ComputerName')] 
[string[]]$Ser 
) 
 
 
# Used to store the result 
 
$Results = @() 
 
# The below array used to concatenate the Trace flag details 
 
$TraceFlag=@() 
 
#create an smo object for the SQL Server 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null 
 
$SQLServer = new-object ('Microsoft.SQLServer.Management.Smo.Server') $Ser 
$SQL= $SQLServer | select Name, Edition, BuildNumber, Product, ProductLevel, Version, Processors, PhysicalMemory, DefaultFile, DefaultLog, MasterDBPath, MasterDBLogPath, BackupDirectory, ServiceAccount,ServiceStartMode,State 
 
$SQLServer.EnumActiveGlobalTraceFlags()|%{ 
[string]$b=$_.status 
[string]$c=$_.TraceFlag 
$TraceFlag+=$c.ToString() +'->'+ $b.ToString() 
 
} 
 
$Properties = @{ 
Name = $SQL.name 
Edition = $SQL.Edition 
BuildNumber = $SQL.BuildNumber 
Product=$SQL.Product 
ProductLevel=$SQL.ProductLevel 
Version=$SQL.Version 
Processors=$SQL.Processors 
DefaultLog=$SQL.DefaultLog 
MasterDBPath=$SQL.MasterDBPath 
MasterDBLogPath=$SQL.MasterDBLogPath 
BackupDirectory=$SQL.BackupDirectory 
ServiceAccount=$SQL.ServiceAccount 
StartupMode=$SQL.ServiceStartMode 
State=$SQL.State 
TraceAndStatus=($TraceFlag -join ',')} 
 
 
 
$Results += New-Object psobject -Property $properties  
$Results | Select-Object Name, Edition, BuildNumber,TraceAndStatus,Product,ProductLevel,Version,Processors,DefaultLog,MasterDBPath,MasterDBLogPath,BackupDirectory,ServiceAccount,StartupMode,State 
 
 
} 
 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails1 -ServerName $_ }
 SQLTraceFlag
Posted in SQL, PowerShell | Tagged , , | Leave a comment

SQL – MultiServer SQL Inventory – PowerShell

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy to forget that the inventory has to be updated as well. For this reason, I like to have a script that creates the inventory, rather than have to keep a manual log of changes as they occur.

It has seven parameters

  • Mandatory -InputFileName – Text File contains a list of SQL Servers – C:\Server.txt(Example)
  • Mandatory -DirectoryToSave – Folder where you want to store the file
  • Mandatory -OutputType – CSV or Excel
  • Mandatory – Email – Yes or No – If Yes, Requires below three parameter entries
  • ToID – To Email Address – One ore more recipients
  • FromID – From Email
  • SMTP – Mail Server Name

 

Inventory_2

Pre-requisites are –

  1. Permission to access all SQL instances
  2. Permission to create a output file
  3. You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically.The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features.
  4. Replication Management Objects

The advantage of this implementation

  • Excel or CSV
  • Auto email option for one ore more recipients.
  • Console output

Examples

# EXAMPLE 1 :Output CSV

#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  

# EXAMPLE 2 :Output Excel 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  

# EXAMPLE 3 : One or More recipients 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com 
-SMTP mail.app.com

# EXAMPLE 4 :  Save the Copy under c:\
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'No' 

I usually recommend to generate CSV file from the server where you have loaded all sqlps modules(SMO and RMO). While generating Excel, make sure excel components are installed on the machine from which the code is being called.

It gathers 41 very useful information such as Server Configuration(SMO),DB Count(SMO), Traceflag(EnumActiveGlobalTraceFlags()), Replication(RMO) and Mirroring, Service Account Details.

Partial display of items in the console is due the property of FormatEnumerationLimit. The $FormatEnumerationLimit preference variable has a value of 4, and it determines how many items are displayed when a property contains more than a single item. You can set it as per your requirement. If you set it to -1 then it’s unlimited, it will display all the items.
If you wish to display the result in the console, then select output type csv, refer EXAMPLE 2

The traceflags items are concatenated into a single array variable. To Avoid System.Object[] (or Similar Output) using Export-Csv have used join.

  1. Name
  2. ComputerNamePhysicalNetBIOS
  3. NetName
  4. OSVer
  5. OSVersion
  6. Platform
  7. Product
  8. edition
  9. SQLServer
  10. VersionString
  11. ProductLevel
  12. DbCount
  13. HasNullSaPassword
  14. IsCaseSensitive
  15. IsFullTextInstalled
  16. Language
  17. LoginMode
  18. Processors
  19. PhysicalMemory
  20. MaxMem
  21. MinMem
  22. IsSingleUser
  23. IsClustered
  24. Collation
  25. MasterDBLogPath
  26. MasterDBPath
  27. ErrorLogPath
  28. BackupDirectory
  29. DefaultLog
  30. ResourceLastUpdateDateTime
  31. AuditLevel
  32. DefaultFile
  33. xp_cmdshell
  34. FQDN
  35. IPAddress
  36. Traceflag
  37. Replication
  38. Mirroring
  39. ServiceAccount
  40. ServiceStartMode
  41. State

 

Code:  Input SQL Server

To get 41 desired configuration details of server by replacing the $instancename parameter in the below code

$instanceName ='<InstanceName>'
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value
$st=$server1.Settings.Properties |Select Name, Value
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
$dbs=$server1.Databases.count
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value
$edition=$s | where {$_.name -eq "edition"}|select value
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value
$Platform =$s | where {$_.name -eq "Platform"}|select value
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value
$Language =$s | where {$_.name -eq "Language"}|select value
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value
$NetName =$s | where {$_.name -eq "NetName"}|select value
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value
$Processors =$s | where {$_.name -eq "Processors"}|select value
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value
$Product =$s | where {$_.name -eq "Product"}|select value
$VersionString =$s | where {$_.name -eq "VersionString"}|select value
$Collation =$s | where {$_.name -eq "Collation"}|select value
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {  
    [string]$b=$_.status
    [string]$c=$_.TraceFlag
    $a+=$c.ToString() +'->'+ $b.ToString()+ '  '
    
      }
      
     
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State
 
 
Foreach($Database in $server1.Databases)
   {
       if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
   }
  
if ($HasNullSaPassword.value -eq $NULL)
{
    $HasNullSaPassword.value='No'
}
if($DefaultFile.value -eq '')
{
    $DefaultFile.value='NA'
}
if ($VersionString.value -like '8*')
{
    $SQLServer='SQL SERVER 2000'
}
elseif ($VersionString.value -like '9*')
{
    $SQLServer='SQL SERVER 2005'
}
elseif ($VersionString.value -like '10.0*')
{
    $SQLServer='SQL SERVER 2008'
}
elseif ($VersionString.value -like '10.5*')
{
    $SQLServer='SQL SERVER 2008 R2'
}
elseif ($VersionString.value -like '11*')
{
    $SQLServer='SQL SERVER 2012'
}
else
{
    $SQLServer='Invalid'
}
  
  
if ($OSVersion.value -like '5.0*')
{
    $OSVer='Windows 2000'
}
elseif ($OSVersion.value -like '5.1*')
{
    $OSVer='Windows XP'
}
elseif ($OSVersion.value -like '5.2*')
{
    $OSVer='Windows Server 2003'
}
elseif ($OSVersion.value -like '6.0*')
{
    $OSVer='Windows Server 2008'
}
elseif ($OSVersion.value -like '6.1*')
{
    $OSVer='Windows Server 2008 R2'
}
elseif ($OSVersion.value -like '6.2*')
{
    $OSVer='Windows Server 2012'
}
else
{
    $OSVer='NA'
}
 
 
  $Properties = @{Name=$instanceName
        ComputerNamePhysicalNetBIOS   =$ComputerNamePhysicalNetBIOS.value
        NetName   =$NetName.value
        OSVer   =$OSVer
        OSVersion   =$OSVersion.value
        Platform   = $Platform.value
        Product   = $Product.value
        edition   = $edition.value
        SQLServer   = $SQLServer
        VersionString = $VersionString.value
        ProductLevel  = $ProductLevel.value
        DbCount  = $Dbs
        HasNullSaPassword  = $HasNullSaPassword.value
        IsCaseSensitive  = $IsCaseSensitive.value
       IsFullTextInstalled  = $IsFullTextInstalled.value
        Language  = $Language.value
        LoginMode  = $LoginMode.value
        Processors  = $Processors.value
        PhysicalMemory  = $PhysicalMemory.value
        MaxMem  = $Max.Configvalue
        MinMem  = $Min.Configvalue
        IsSingleUser  = $IsSingleUser.value
        IsClustered  = $IsClustered.value
        Collation  = $Collation.value
        MasterDBLogPath  = $MasterDBLogPath.value
        MasterDBPath  = $MasterDBPath.value
        ErrorLogPath  = $ErrorLogPath.value
        BackupDirectory  = $BackupDirectory.value
        DefaultLog  = $DefaultLog.value
        ResourceLastUpdateDateTime  = $ResourceLastUpdateDateTime.value
        AuditLevel  = $AuditLevel.value
        DefaultFile = $DefaultFile.value
        xp_cmdshell = $xp_cmdshell.Configvalue
        FQDN = $FQDN
        IPAddress = ($IPAddress  -join ',')
        Traceflag = ($a -join ',')
        Replication = $replication
        Mirroring = $isMirror
        ServiceAccount = $SQL.ServiceAccount
        ServiceStartMode = $SQL.ServiceStartMode
        State = $SQL.State
        }
 
$Results += New-Object psobject -Property $properties
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation

Inventory_1

Code

<#================================= 
# Generated On: 07/18/2016 
# Generated By: Prashanth Jayaram 
# Version : 1.1 
# Desc : SQL Inventory Generation 
# EXAMPLE 1 :Output CSV
#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\PowerSQL\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 2 :Output Excel
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 3 :Multiple receipients
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\Test\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 4 :No Email
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt 
-DirectoryToSaveTo C:\123\ -OutputType 'csv' -Email 'No'


#================================= 
#> 
[CmdletBinding()] 
Param( 
 [Parameter(Mandatory=$True,Position=1)] 
 [string]$InputFileName, 
 
 [Parameter(Mandatory=$True,Position=2)] 
 [string]$DirectoryToSaveTo, 
 
 [Parameter(Mandatory=$True,Position=3)] 
 [string]$OutputType,
 
 [Parameter(Mandatory=$True,Position=4)] 
 [string]$Email,
 
 [Parameter(Position=5)] 
 [string]$To, 
 
 [Parameter(Position=6)] 
 [string]$From, 
 
 [Parameter(Position=7)] 
 [string]$SMTP 
 
) 
 
$Filename='SQLInventory' 

$Results = @()
 
 $a=@()

# before we do anything else, are we likely to be able to save the file? 
# if the directory doesn't exist, then create it 
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing 
 { 
 New-Item "$DirectoryToSaveTo" -type directory | out-null 
 } 
 
 If( $OutputType -eq 'Excel')
 {
 
#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add() 
$Sheet = $Excel.Worksheets.Item(1) 
 
#Counter variable for rows 
$intRow = 1 
$xlOpenXMLWorkbook=[int]51 
 
#Read thru the contents of the SQL_Servers.txt file 
$Sheet.Cells.Item($intRow,1) ="InstanceName" 
$Sheet.Cells.Item($intRow,2) ="ComputerNamePhysicalNetBIOS" 
$Sheet.Cells.Item($intRow,3) ="NetName" 
$Sheet.Cells.Item($intRow,4) ="OS" 
$Sheet.Cells.Item($intRow,5) ="OSVersion" 
$Sheet.Cells.Item($intRow,6) ="Platform" 
$Sheet.Cells.Item($intRow,7) ="Product" 
$Sheet.Cells.Item($intRow,8) ="edition" 
$Sheet.Cells.Item($intRow,9) ="Version" 
$Sheet.Cells.Item($intRow,10) ="VersionString" 
$Sheet.Cells.Item($intRow,11) ="ProductLevel" 
$Sheet.Cells.Item($intRow,12) ="DatabaseCount" 
$Sheet.Cells.Item($intRow,13) ="HasNullSaPassword" 
$Sheet.Cells.Item($intRow,14) ="IsCaseSensitive" 
$Sheet.Cells.Item($intRow,15) ="IsFullTextInstalled" 
$Sheet.Cells.Item($intRow,16) ="Language" 
$Sheet.Cells.Item($intRow,17) ="LoginMode" 
$Sheet.Cells.Item($intRow,18) ="Processors" 
$Sheet.Cells.Item($intRow,19) ="PhysicalMemory" 
$Sheet.Cells.Item($intRow,10) ="MaxMemory" 
$Sheet.Cells.Item($intRow,21) ="MinMemory" 
$Sheet.Cells.Item($intRow,22) ="IsSingleUser" 
$Sheet.Cells.Item($intRow,23) ="IsClustered" 
$Sheet.Cells.Item($intRow,24) ="Collation" 
$Sheet.Cells.Item($intRow,25) ="MasterDBLogPath" 
$Sheet.Cells.Item($intRow,26) ="MasterDBPath" 
$Sheet.Cells.Item($intRow,27) ="ErrorLogPath" 
$Sheet.Cells.Item($intRow,28) ="BackupDirectory" 
$Sheet.Cells.Item($intRow,29) ="DefaultLog" 
$Sheet.Cells.Item($intRow,20) ="ResourceLastUpdatetime" 
$Sheet.Cells.Item($intRow,31) ="AuditLevel" 
$Sheet.Cells.Item($intRow,32) ="DefaultFile" 
$Sheet.Cells.Item($intRow,33) ="xp_cmdshell" 
$Sheet.Cells.Item($intRow,34) ="Domain" 
$Sheet.Cells.Item($intRow,35) ="IPAddress" 
$Sheet.Cells.Item($intRow,36) ="TraceFlag" 
$Sheet.Cells.Item($intRow,37) ="Mirror" 
$Sheet.Cells.Item($intRow,38) ="Replication" 
$Sheet.Cells.Item($intRow,39) ="ServiceAccount" 
$Sheet.Cells.Item($intRow,40) ="ServiceStartMode" 
$Sheet.Cells.Item($intRow,41) ="State" 



 
 for ($col = 1; $col –le 41; $col++) 
 { 
 $Sheet.Cells.Item($intRow,$col).Font.Bold = $True 
 $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 
 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 
 } 
 
$intRow++ 
 
foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null

$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) 
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count 
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value 
$edition=$s | where {$_.name -eq "edition"}|select value 
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value 
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value 
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value 
$Platform =$s | where {$_.name -eq "Platform"}|select value 
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value 
$Language =$s | where {$_.name -eq "Language"}|select value 
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value 
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value 
$NetName =$s | where {$_.name -eq "NetName"}|select value 
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value 
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value 
$Processors =$s | where {$_.name -eq "Processors"}|select value 
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value 
$Product =$s | where {$_.name -eq "Product"}|select value 
$VersionString =$s | where {$_.name -eq "VersionString"}|select value 
$Collation =$s | where {$_.name -eq "Collation"}|select value 
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value 
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value 
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value 
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value 
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value 
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value 
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value 
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value 
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value 
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue 
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue 
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString() + ' '
 
 }
 
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State



Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
 
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '') 
{ 
 $DefaultFile.value='NA' 
} 
if ($VersionString.value -like '8*') 
{ 
 $SQLServer='SQL SERVER 2000' 
} 
elseif ($VersionString.value -like '9*') 
{ 
 $SQLServer='SQL SERVER 2005' 
} 
elseif ($VersionString.value -like '10.0*') 
{ 
 $SQLServer='SQL SERVER 2008' 
} 
elseif ($VersionString.value -like '10.5*') 
{ 
 $SQLServer='SQL SERVER 2008 R2' 
} 
elseif ($VersionString.value -like '11*') 
{ 
 $SQLServer='SQL SERVER 2012' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
 
 
if ($OSVersion.value -like '5.0*') 
{ 
 $OSVer='Windows 2000' 
} 
elseif ($OSVersion.value -like '5.1*') 
{ 
 $OSVer='Windows XP' 
} 
elseif ($OSVersion.value -like '5.2*') 
{ 
 $OSVer='Windows Server 2003' 
} 
elseif ($OSVersion.value -like '6.0*') 
{ 
 $OSVer='Windows Server 2008' 
} 
elseif ($OSVersion.value -like '6.1*') 
{ 
 $OSVer='Windows Server 2008 R2' 
} 
elseif ($OSVersion.value -like '6.2*') 
{ 
 $OSVer='Windows Server 2012' 
} 
else 
{ 
 $OSVer='NA' 
} 
 $Sheet.Cells.Item($intRow,1) =$instanceName 
 $Sheet.Cells.Item($intRow,2) =$ComputerNamePhysicalNetBIOS.value 
 $Sheet.Cells.Item($intRow,3) =$NetName.value 
 $Sheet.Cells.Item($intRow,4) =$OSVer 
 $Sheet.Cells.Item($intRow,5) =$OSVersion.value 
 $Sheet.Cells.Item($intRow,6) = $Platform.value 
 $Sheet.Cells.Item($intRow,7) = $Product.value 
 $Sheet.Cells.Item($intRow,8) = $edition.value 
 $Sheet.Cells.Item($intRow,9) = $SQLServer 
 $Sheet.Cells.Item($intRow,10) = $VersionString.value 
 $Sheet.Cells.Item($intRow,11) = $ProductLevel.value 
 $Sheet.Cells.Item($intRow,12) = $Dbs 
 $Sheet.Cells.Item($intRow,13) = $HasNullSaPassword.value 
 $Sheet.Cells.Item($intRow,14) = $IsCaseSensitive.value 
 $Sheet.Cells.Item($intRow,15) = $IsFullTextInstalled.value 
 $Sheet.Cells.Item($intRow,16) = $Language.value 
 $Sheet.Cells.Item($intRow,17) = $LoginMode.value 
 $Sheet.Cells.Item($intRow,18) = $Processors.value 
 $Sheet.Cells.Item($intRow,19) = $PhysicalMemory.value 
 $Sheet.Cells.Item($intRow,10) = $Max.Configvalue 
 $Sheet.Cells.Item($intRow,21) = $Min.Configvalue 
 $Sheet.Cells.Item($intRow,22) = $IsSingleUser.value 
 $Sheet.Cells.Item($intRow,23) = $IsClustered.value 
 $Sheet.Cells.Item($intRow,24) = $Collation.value 
 $Sheet.Cells.Item($intRow,25) = $MasterDBLogPath.value 
 $Sheet.Cells.Item($intRow,26) = $MasterDBPath.value 
 $Sheet.Cells.Item($intRow,27) = $ErrorLogPath.value 
 $Sheet.Cells.Item($intRow,28) = $BackupDirectory.value 
 $Sheet.Cells.Item($intRow,29) = $DefaultLog.value 
 $Sheet.Cells.Item($intRow,20) = $ResourceLastUpdateDateTime.value 
 $Sheet.Cells.Item($intRow,31) = $AuditLevel.value 
 $Sheet.Cells.Item($intRow,32) = $DefaultFile.value 
 $Sheet.Cells.Item($intRow,33) = $xp_cmdshell.Configvalue 
 $Sheet.Cells.Item($intRow,34) = $FQDN 
 $Sheet.Cells.Item($intRow,35) = ($IPAddress -join ',') 
 $Sheet.Cells.Item($intRow,36) = ($a -join ',')
 $Sheet.Cells.Item($intRow,37) = $replication 
 $Sheet.Cells.Item($intRow,38) = $isMirror
 $Sheet.Cells.Item($intRow,39) = $SQL.ServiceAccount 
 $Sheet.Cells.Item($intRow,40) = $SQL.ServiceStartMode
 $Sheet.Cells.Item($intRow,41) = $SQL.State
 
$intRow ++ 
 
} 
 
$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Sheet.UsedRange.EntireColumn.AutoFit() 
cls 
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$Excel.Saved = $True 
$Excel.Close() 
 
 }
 ElseIf( $OutputType -eq 'csv')
 {
 $filename = "$DirectoryToSaveTo$filename.csv" 
 if (test-path $filename ) { rm $filename } #delete the file if it already exists 
 foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction 'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count 
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value 
$edition=$s | where {$_.name -eq "edition"}|select value 
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value 
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value 
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value 
$Platform =$s | where {$_.name -eq "Platform"}|select value 
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value 
$Language =$s | where {$_.name -eq "Language"}|select value 
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value 
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value 
$NetName =$s | where {$_.name -eq "NetName"}|select value 
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value 
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value 
$Processors =$s | where {$_.name -eq "Processors"}|select value 
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value 
$Product =$s | where {$_.name -eq "Product"}|select value 
$VersionString =$s | where {$_.name -eq "VersionString"}|select value 
$Collation =$s | where {$_.name -eq "Collation"}|select value 
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value 
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value 
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value 
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value 
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value 
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value 
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value 
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value 
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value 
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue 
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue 
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % { 
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString()+ ' '
 
 }
 
 
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State


Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
 
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '') 
{ 
 $DefaultFile.value='NA' 
} 
if ($VersionString.value -like '8*') 
{ 
 $SQLServer='SQL SERVER 2000' 
} 
elseif ($VersionString.value -like '9*') 
{ 
 $SQLServer='SQL SERVER 2005' 
} 
elseif ($VersionString.value -like '10.0*') 
{ 
 $SQLServer='SQL SERVER 2008' 
} 
elseif ($VersionString.value -like '10.5*') 
{ 
 $SQLServer='SQL SERVER 2008 R2' 
} 
elseif ($VersionString.value -like '11*') 
{ 
 $SQLServer='SQL SERVER 2012' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
 
 
if ($OSVersion.value -like '5.0*') 
{ 
 $OSVer='Windows 2000' 
} 
elseif ($OSVersion.value -like '5.1*') 
{ 
 $OSVer='Windows XP' 
} 
elseif ($OSVersion.value -like '5.2*') 
{ 
 $OSVer='Windows Server 2003' 
} 
elseif ($OSVersion.value -like '6.0*') 
{ 
 $OSVer='Windows Server 2008' 
} 
elseif ($OSVersion.value -like '6.1*') 
{ 
 $OSVer='Windows Server 2008 R2' 
} 
elseif ($OSVersion.value -like '6.2*') 
{ 
 $OSVer='Windows Server 2012' 
} 
else 
{ 
 $OSVer='NA' 
} 


 $Properties = @{Name=$instanceName 
 ComputerNamePhysicalNetBIOS =$ComputerNamePhysicalNetBIOS.value 
 NetName =$NetName.value 
 OSVer =$OSVer 
 OSVersion =$OSVersion.value 
 Platform = $Platform.value 
 Product = $Product.value 
 edition = $edition.value 
 SQLServer = $SQLServer 
 VersionString = $VersionString.value 
 ProductLevel = $ProductLevel.value 
 DbCount = $Dbs 
 HasNullSaPassword = $HasNullSaPassword.value 
 IsCaseSensitive = $IsCaseSensitive.value 
 IsFullTextInstalled = $IsFullTextInstalled.value 
 Language = $Language.value 
 LoginMode = $LoginMode.value 
 Processors = $Processors.value 
 PhysicalMemory = $PhysicalMemory.value 
 MaxMem = $Max.Configvalue 
 MinMem = $Min.Configvalue 
 IsSingleUser = $IsSingleUser.value 
 IsClustered = $IsClustered.value 
 Collation = $Collation.value 
 MasterDBLogPath = $MasterDBLogPath.value 
 MasterDBPath = $MasterDBPath.value 
 ErrorLogPath = $ErrorLogPath.value 
 BackupDirectory = $BackupDirectory.value 
 DefaultLog = $DefaultLog.value 
 ResourceLastUpdateDateTime = $ResourceLastUpdateDateTime.value 
 AuditLevel = $AuditLevel.value 
 DefaultFile = $DefaultFile.value 
 xp_cmdshell = $xp_cmdshell.Configvalue 
 FQDN = $FQDN 
 IPAddress = ($IPAddress -join ',')
 Traceflag = ($a -join ',')
 Replication = $replication 
 Mirroring = $isMirror
 ServiceAccount = $SQL.ServiceAccount 
 ServiceStartMode = $SQL.ServiceStartMode
 State = $SQL.State
 }

$Results += New-Object psobject -Property $properties 
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation

 }
 
 }
 
 
 
 
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
{ 
#initate message 
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom 
$email.To.Add($emailTo) 
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
$email.Attachments.Add($emailAttach) 
#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$smtp.Send($email) 
} 


If( $Email -eq 'Yes')
{
If (!($to) -and !($smtp))
{ 
#Call Function 
sendEmail -emailFrom $from -emailTo $to "SQL INVENTORY" "SQL INVENTORY DETAILS - COMPLETE DETAILS" -smtpServer $SMTP -filePath $filename 
}
else
{
write-host 'To or SMTP Invalid'
}
}




 

Output

CSV:

Inventory

 

CSV output to saved under f:\PowerSQL folder

Inventory_1

 

Excel

Inventory_3

Conclusion

  • Effectively perform administrative tasks
  • Effective Asset Management
  • Inventory is important for DB Migration/Upgradation
  • Maintain Corporate standards
  • License Validation
  • Easy to provide road-map
Posted in PowerShell, SQL, Uncategorized | Tagged , , , , , | Leave a comment

SQL and NoSQL

Despite the traditional relational DB world, a new trend is emerging which is called NoSQL. Its really hard to define NoSQL. You will get lot of information, opinions on what NoSQL is all about. This is just my understanding and research on NoSQL.

Its all about

  • Non-relational,
  • Open source,
  • Cluster friendly,
  • Schema Independent,
  • No Join,
  • Heterogeneous,
  • Humongous Volume of data,
  • Easy Development,
  • Easy Administration

and other factor such as License cost, Business Process, Organization decision plays a major role in opting for this aggregated brand of the NoSQL umbrella but relational DB’s are still very good for various scenarios and answers all our long lasting questions.

NoSQL, without ACID it is very difficult to write reliable software though it follows CAP theorem , it is clear that without ACID, the developers have to spent a significant fraction of their time building extremely complex and error-prone systems.Also, this is totally depend on business decision and use cases.

There is lot of new development and research is taking place to come out with a stable NoSQL products.There is always pros and Cons while choosing the database product but its all driven by business case, cost involved and decision taken at the management level.

There is a new mindset in my organization to try and go for NoSQL products thinking of lower expense and maintenance overhead. Most of the time during Project life cycle process may give us an insight about success or failure of the implementation.

The problem is that when you had a tremendous amount of data scattered across multiple systems it’s nearly impossible to keep SQL and ACID in this scenario. But as technology grows and matures, they are working on implementing and providing various features to provide data consistency and integrity. Also, adding SQL support to underlying data layer gives an flexibility to query the data.This is the only reason and hard to say it’s a schema-less design as it’s indirectly bound to schema.

NoSQL, the Non-Relational db is classified under Key-Value pair, Document Oriented type, Column family and Graph.

Further Reading:-

http://highscalability.com/blog/2010/12/6/what-the-heck-are-you-actually-using-nosql-for.

Thanks for reading.

Posted in MongoDB, NoSQL | Tagged , , , | Leave a comment

Tips to Fix Database Consistency Errors – Reported By DBCC CHECKDB

Overview

While working on SQL Server, sometimes users get stuck in annoying situations when database become inaccessible. After executing DBCC CHECKDB command to check the problem, user gets database consistency errors.

Causes Behind DBCC CHECKDB Consistency Errors

The reason behind the database consistency error can be varied from file system corruption, corrupted pages in memory, underlying the hardware system issues, drive issues, or some problem with SQL Server Engine.

How to Fix Database Consistency Errors Reported By DBCC CHECKDB ?

DBCC CHECKDB checks the physical as well as logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fails then, errors will be described as part of command. To resolve the inconsistency errors, users first need restore the data from the backup of data. However, if users are not able restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so user should correct these first before restoring and running repair. There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, user can use the chdsk command. This command helps to create and displays a status report for disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.

I/O Requirements

There are various counters, which are related to I/O and all are located under Logical and Physical Disk. The physical Disk performance object consists of counters that monitors hard or fixed disk on system. The logical disk performance of an object consists of counters, which monitors the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports the consistency errors. SQLIOSim is a tool, which is independent of SQL Server Engine for testing the integrity of I/O for the disk system. It can be downloaded from the web for utilization.

Note: SQLIOSim ships on SQL Server 2008 does not require separate download.

Verify Checksum Option

Make sure that the database, which is using PAGE_VERIFY CHECKSUM option. If the checksum error is reported then, it indicates that the consistency error has occurred. After writing the SQL Server, pages to disk the error occurred so that the disk system should be checked thoroughly. User gets 824 error in SQL Server, which is caused due to some external conditions. Some external conditions have caused the modification on database page outside the SQL Server engine code. It can be resolved if the user by running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. User can update all system drivers, Operating system, or hardware if required on the system.

Minimum Repair Option

When user runs DBCC CHECKDB, a recommendation is used to indicate the minimum repair option, which is essential to repair all errors. Its message appears as mentioned below:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'adventureworks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adventureworks

The repair recommendation is the minimum level of repair to attempt to resolve all the errors from CHECKDB. It does not mean that all the errors are fixed by repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended will result in loss of data. Repair must be run to determine if there is resolution of an error that will result in data loss. It is a technique to help narrow down if the repair level for each table is to access DBCC CHECKTABLE for any table that is reporting an error. It will display the minimum level of repair for given table.

Conclusion

In the above discussion, the problem and cause of the consistency error is described. Along with this, solution on to how to troubleshoot database consistency errors reported by DBCC CHECKB is discussed. It makes easy for users the error occurrence.

Posted in Backup and Restore | Leave a comment

Why MongoDB?

MongoDB is Object-Oriented, simple, dynamic and scalable NoSQL database. It is based on the NoSQL document store model, in which data objects are stored as separate documents inside a collection instead of storing the data into columns and rows of a traditional relational database. The motivation of the MongoDB language is to implement a data store that provides high performance, high availability, and automatic scaling. MongoDB is extremely simple to install and implement. The core of MongoDB storage is documents and its stored as  JSON or BSON objects.  General distributions for MongoDB support Windows, Linux, Mac OS X, and Solaris

Terminology and Concepts

SQL Server MongoDB
Database Database
Table Collection
Index Index
Row Document
Column Field
Joining Linking & Embedding
Partition Sharding (Range Partition)
Replication ReplSet

Choice of database is always a decision based pros and cons.

Pros:

  • Document oriented
  • High performance
  • High availability -Replication
  • High scalability – Sharding
  • Dynamic- No Rigid Schema.
  • Flexible – field addition/deletion have less or no impact on the application
  • Heterogeneous Data
  • No Joins
  • Distributed
  • Data Representation in JSON or BSON
  • Geospatial support
  • Easy Integration with BigData Hadoop
  • Document-based query language that’s nearly as powerful as SQL
  • Cloud distributions such as AWS, Microsoft, RedHat,dotCloud and SoftLayer etc:-. In fact, MongoDB is built for the cloud. Its native scale-out architecture, enabled by ‘sharding,’ aligns well with the horizontal scaling and agility afforded by cloud computing.

Cons:

  • A downside of NoSQL is that most solutions are not as strong in ACID (Atomic, Consistency, Isolation, Durability) as in the more well-established RDBMS systems.
  • Complex transaction
  • No function or Stored Procedure exists where you can bind the logic

 

Good for:

  • Ecommerce product catalog
  • Blogs and Content Management
  • Real-time analytics and high-speed logging, caching and high scalability
  • Configuration Management
  • Maintain location based data – Geospatial data
  • Mobile and Social networking sites
  • Evolving data requirements
  • Loosely coupled objectives – the design may change by over time

Not good for:

  • Highly transactional system and data model is designed upfront
  • Tightly coupled systems

Reference

https://docs.mongodb.com/ecosystem/use-cases/

 

 

 

 

 

 

 

 

 

 

Posted in MongoDB | Tagged , | Leave a comment

Securing MongoDB – User Administration

The db.createUser(user, writeConcern) method used to create users.We need to provide the username, password and roles

The definition of createUser as follows

{ 
user: "<name>",
pwd: "password>",
customData: { <User Tag> },
roles: [
    { role: "<role>", db: "<database>" },
    { role: "<role>", db: "<database>"},    ...
        ]
}

Role

Role is an approach to restricting system/DB access to authorized users.The security hierarchy is similar to various DB technologies. There are various roles are

Database User Roles

  • read
  • readWrite

Database Administration Roles

  • dbAdmin
  • dbOwner
  • userAdmin

Cluster Administration Roles

  • clusterAdmin
  • clusterManager
  • clusterMonitor
  • hostManager

Backup and Restoration Roles

  • backup
  • restore

All-Database Roles

  • readAnyDatabase
  • readWriteAnyDatabase
  • userAdminAnyDatabase
  • dbAdminAnyDatabase

Superuser Roles

  • root

Internal Role

  • –system

The Roles are a self explanatory. For further reading, read the following MongoDB reference manual Roles

Create User

 

db.createUser(
 {
 user: "reportUser",
 pwd: "12345678",
 roles: [
             {role: "read", db :"northwind"},
             {role: "readWrite", db: "records"},
             {role: "backup", db: "admin"},
             {role:"clusterAdmin", db: "admin"},
             {role:"readAnyDatabase", db: "admin"}
         ]
 }
)

 

Identify the user roles by using db.getUser()

db.getUser("reportUser")

CreateUser1

Change Password

>db.changeUserPassword("reportUser","!@#$1234Mongo")

Drop a user from mongodb using the db.dropUser()

>db.dropUser("reportUser")

Revoke a role from the user using revokeRolesFromUser()

>db.revokRolesFromUser(
"reportUser",
[
{role: "readWrite", db:" northwind"},
{role: "backup", db: "admin"}
]
)

CreateUser2.jpg
Posted in MongoDB | Tagged , , , , | Leave a comment

PowerShell – Install PowerShell

Recently got a question regarding PowerShell installation hence this post took an existence.

How to Install PowerShell various versions

http://technet.microsoft.com/en-us/library/hh847837.aspx

http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx

Can we have more than one version of Powershell?

Yes, You can have multiple versions PowerShell.

How to switch between Powershell version?

ps:\>Powershell -version 4

ps:\>Powershell -version 3

ps:\>Powershell -version 2

Hope this simple post solve various doubts.

Happy Learning:-)

 

 

 

Posted in PowerShell | Leave a comment

PowerShell : Script Method Error -Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.

I was working on generating scripts for logins, users and server roles and I could able to to generate scripts for users and logins but where as for server roles, the script() method is not working.

The script is throwing following error

Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.
At line:3 char:23
+ $srv.Roles | foreach {$_.Script()+ “GO”}
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

In any such cases the easiest way to identify the problem by issuing the Get-Member cmdlet. It tells you the property and method for the current object.

Using Get-Member, It din’t list script() method that gives me an answer to look for a workaround.

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'

Function get-roles {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Roles|Get-Member| Where-Object {$_.name -like 'script*'} 
#foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "_roles.sql") 
}
#get-roles

Function get-userlogins {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Logins |Get-Member |Where-Object {$_.name -like 'script*'}
#| foreach {$_.Script()+ "GO"}

#| Out-File $($directoryname + $serverfilename + "_logins.sql") 
}

scriptMethod1

Workaround for generating script for server roles

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($sqlserver)
$srv.Logins|Where-Object {$_.IsSystemObject -eq $false -and $_.loginType -like  "windows*"} | foreach { $_.Script() +"GO" | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
#|Out-File $($directoryname + $sqlserver + "_logins.sql") 
}

 #| Out-File $($directoryname + $sqlserver + "_logins.sql") 
foreach ($Role in $srv.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne "sa"} |% {"EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'{1}'{2}" -f ($_,$Role.Name,"`r`nGO");}
#|Out-File $($directoryname + $sqlserver + "_roles.sql") 
};

 

scriptMethod2

Posted in PowerShell | Tagged , , , | Leave a comment

Restore a SQL Server Database with Different Methods

Database backups are generated as part of the business continuity planning procedure. Disasters with server or its database are a common scenario that takes place every now and then lead to the loss of an enormous amount of data. In order to sustain the same, administrative ensure backup plans as part of their administrative roles apart from handling the server. There are different ways to restore a SQL Server database, which have been discussed in the segment below for a better understanding of the concept and fulfilment of different user needs.

Discussing Different Ways to Restore A SQL Server Database

Servers deal with not just one but multiple databases which requires their proper maintenance in an automated manner. Automated backup procedure puts least amount of responsibilities on an administrators shoulder.

Following is a diagram represents the methods of restoring SQL DB in multiple ways, which has been further explained individually for a better understanding.

Point in Time Recovery

The diagram represents 3 types of SQL backups:

  • Full DB backup – F’x’
  • Differential backup – D’x’
  • Transaction log backup – T’x’

In this diagram, X defines an incrementing number that is associated to a point-in-time when a particular backup was taken. Meanwhile, the prefixed characters (D, F, and T) represent the type of backup procedures whereas; the point-in-time (p) represents the point in time when a backup took place.

For Instance First Full Backup F1 has taken at P1 point-in-time whereas, the second one, i.e. F2 took place at a later point-in-time which is P9. Similarly, rest of the backup procedures has been specified in the same way.

NOTE: In the figure shown above, P13 is a point-in-time that represents a committed-transaction taking place. However, the backup of transaction log hasn’t taken place until P14. Therefore, recovering to point-in-time P13 becomes a little complicated in order to simplify which method 3 of database restoration has been discussed in the segment below.

Simplifying the Restoration of SQL Server Database

Discussed below are top three different methods of database restoration that can be implemented in respective circumstances to fulfill certain restoration needs.

Method 1: Restoring to the Point in Time – P8

Differential backups increase by successive addition, i.e. the last differential backup after a full database backup consists of all the changes taken place in the database. Therefore, only one can be restored once the full database backup restoration.

Method 2: Restore to Point in Time – P10

Just in case the F2 full backup goes missing, it is still possible for an SQL Administrator to perform a restore with the help of F1. Only difference is that a combination of differential and transaction backup will have to be used to reach P10.

A full database backup does not act as a break to the chain of a transaction log backup. Therefore, restoration of transaction logs can be performed for previously taken place full database backups.

The most important factor for having a full database backup is to have the best of Recovery Time Objective (RTO). The representation here only shows a few transact log backups but a lot more are involved in reality. Thus, in such a case, the restoration of an enormous amount of transaction log backup is excessively time consuming and can result in largely affecting the RTO.

Method 3: Restore to Point in Time – P13

In here, we are discussing situations pointing out the restoration of exact point in time. For instance, transactions need to be restored to a point in time P13 however; the log backup is only available at P14.

DBA are not given exact point in times in real-time situations, which is what complicates the entire situation a lot more. In case a bug happens to take place in a code updating the entire table without a WHERE clause, the DBA is told just to simply restore the database to a point right before the update took place without any specifications made about the point in time.

The only way to tackle a situation like this is by using a transaction log backup with a STOPAT clause specified.

NOTE: Performing a point in time recovery is only permitted to transaction log backups.

Therefore, the restore technique to be used here is by utilizing an UNDO file so that you can restore a Server database in STANDBY mode and mention the STOPAT clause.

TIP: The trick is to know that you are allowed to carry out a RESTORE from the very same transaction log DB repeatedly until the desired results are achieved.

Conclusion:

Some of the possible different ways to restore a SQL Server database in predefined conditions have been discussed. These set of methods can be used as tips to handle similar situations for restoring a database in disaster conditions to a previous state without loss. In addition, you can also restore database with NORECOVERY option.

Posted in Backup and Restore | Tagged , | Leave a comment

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

“The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)” error may occurs when executing PowerShell scripts using WMI query.

There can be a few reasons for this error:

  1. The remote computer is blocked by the firewall.
    Solution: Open the Group Policy Object Editor snap-in (gpedit.msc) to edit the Group Policy object (GPO) that is used to manage Windows Firewall settings in your organization. OpenComputer Configuration, open Administrative Templates, open Network, open Network Connections, open Windows Firewall, and then open either Domain Profile or Standard Profile, depending on which profile you want to configure. Enable the following exception: “Allow Remote Administration Exception” and “Allow File and Printer Sharing Exception“.
  2. Host name or IP address is wrong or the remote computer is shutdown.
    Solution: Verify correct host name or IP address.
  3. The “TCP/IP NetBIOS Helper” service isn’t running.
    Solution: Verity that “TCP/IP NetBIOS Helper” is running and set to auto start after restart.
  4. The “Remote Procedure Call (RPC)” service is not running on the remote computer.
    Solution: Verity that “Remote Procedure Call (RPC)” is running and set to auto start after restart.
  5. The “Windows Management Instrumentation” service is not running on the remote computer.
    Solution: Verity that “Windows Management Instrumentation” is running and set to auto start after restart
Posted in PowerShell | Tagged , , | Leave a comment