Export recently modified SQL Stored Procedures with Powershell

The need of detecting and exporting Stored Procedures that were recently modified is a rather often database synchronization task. Powershell can work with SMOs (SQL Server Managent Objects) via .NET and extract the database objects [procedures, tables, views, etc] schemas.

Lets have a look how the recently modified Stored Procedures can be exported. Comments are inline:


# I'm wrapping everything into a function, it's just easier to reuse
function GenerateSPsScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
  # Lets load the SMO assembly first (this is basically a collection of SQL Server related objects)
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  
  # Lets initiate a SQL server object
  $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $serverName 
  
  # Lets prepare a Database object and connect to it
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database" 
  $db = $srv.Databases[$dbname] 
  
  # And finally lets create a Scripter object
  $scr = New-Object "Microsoft.SqlServer.Management.SMO.Scripter" 
  $scr.Server = $srv 
  
  # Now we can prepare the options for the Scripter object. Check this web site for the details:
  # https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
  $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions" 
  $options.AllowSystemObjects = $false 
  $options.IncludeDatabaseContext = $false 
  $options.IncludeIfNotExists = $false 
  $options.Default = $true 
  $options.IncludeHeaders = $false 
  $options.ToFileOnly = $true 
  $options.AppendToFile = $true
  
  # Lets set the option of the target file name and create a text file where the SPs code will be exported
  $options.FileName = $scriptpath + "\$($dbname)_modified_stored_procs_$(get-date -f yyyy-MM-dd).sql"
  New-Item $options.FileName -type file -force | Out-Null
  
  # now apply the above options to the SMO.Scripter object
  $scr.Options = $options 
  
  # OK, connection and settings are ready. Now we can start working with the database objects
  # We want to work with the Stored Procedures, so lets grab all the non-system SPs
  $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
  
  # Say you need to export only the SPs that were modified during the last 7 days
  $cd = Get-Date
  $d = $cd.AddDays(-7)
  
  # Now for every SP we will execute the Script method
  Foreach ($StoredProcedure in $StoredProcedures)
  {
    if ($StoredProcedures -ne $null -and $StoredProcedure.DateLastModified -ge $d)
    {   
      # Script the Stored Procedure
      $scr.Script($StoredProcedure)
    }
  }
} 
# Function is completed

# Finally we call the above function by putting our environment details
GenerateSPsScript "SQLServer\InstanceName" "DatabaseName" "D:\ExportFolder"
# If you have a default instance then just put the server name

So just copy/paste the above code into a .ps1 file, put your SQL instance details, database name, target folder and run it. A .sql file with the Stored Procedures will be generated.

Advertisements

Powershell – IE zones Protected Mode state

Internet Explorer has a notion of security zones. Security properties are accordingly configured for every zone. Internet zone is more restrictive comparing to the Intranet, which is a very trusted location.
There is one more setting called “Protected Mode”. You can see it on the screenshot:
IE1

It can be enabled or disabled. The recommended state for each of the zones is this:

  • Internet: Enabled
  • Local Itranet: Disabled
  • Trusted Sites: Disabled
  • Restricted Sites: Enabled

IE executes with more restrictive priviliges when Protected mode is Enabled.
If this tick was not disabled by the Group Policy then there is a great chance that your users made their own IE security improvements 🙂
So, the question is – can we find out how security zones are curently configured for every user in our environment? And the answer is of course yes 🙂
Continue reading

Powershell – XenApp Printer mapping issue workaround

Let me first describe the issue.
The remote office users are on Windows 7, there are several local RICOH printers configured over the local network.
When users want to connect to the enterprise they login to the XenApp Web Interface and then run a Pulished Desktop.
Office applications are used within the Published Desktop and threfore when there is a need to print a document it have to be redirected to the locally attached/configured RICOH printer. In other words, when user tries to print from the Published Desktop he/she should be able to see/select a locally configured printer.

Now, here is the problem – user can actually see the printer from within XenApp desktop but the printer can not be selected. The printer or I should probably say the printer driver is not initialized correctly. If I look at the Printers console on the server I see the printer being created for the user session, but if I would try to open the properties the following “0x00000709” error message would appear:
XPrint1
Continue reading

Powershell – Collecting installed Printer drivers

Printing on Citrix is a very intresting topic :). There are many things that you should be awair of to make it working flawlessly.
Her I’m going to show a short script that can help you in identifying inconsistent Printer Drivers accross your XenApp environment.

In a nutshell, we have to make sure that the driver version installed on a Print server or on a Client machine [depending on the type of printing redirection you use] is the same as on a XenApp server.

The manual approach requires as to open a Print Managment console on each of the machines and then do a comparison of the Driver Names and Versions:
Print1

Continue reading

Powershell – Process GPO XML files

Windows 2008 AD Group Policies has a very useful and practical feature – Drive Maps.
XML1

If you want to have a centralized place of your user network drive mappings then this option is for you.
Configuration is very flexible as you can filter targeted users by OU, security group and even by their user name.

Now, all is fine until your list is short, but when it has grown to the dosens of mappings the managability becomes an issue.
XML2

The problem is in the interface. It does not show you the filtering rules at the front, to see them you have to open the mapping, then go to “Common” tab, then select “Targeting” and only then you will see your Filtering rules. OMG 🙂
Continue reading

Powershell – Identify the busiest processes

From time to time our Citrix XenApp users would complain about slow applications and slow interactive responses, etc. XenApp is a shared environment therefore it’s not that difficult for a single application to occupy all CPU or Memory. So, I have asked myself can I detect a process or processes that are consuming most of the resourses, both CPU and Memory.
Here is the script that I’ve developed to do a simple monitoring.

It works by querying remote servers through WMI class Win32_PerfFormattedData_PerfProc_Process:

cls

# Lets make an array of servers we will be querying
$Servers = "XENAPPSERVER1","XENAPPSERVER2"
$Servers += "XENAPPSERVER3"

foreach ($Server in $Servers)
{
    $Server
    
    # Select top 5 CPU consumers [processes]
    $TopCPU = gwmi -computername $Server Win32_PerfFormattedData_PerfProc_Process | select IDProcess,Name,WorkingSet,PercentProcessorTime | 
    where { $_.Name -ne "_Total" -and $_.Name -ne "Idle"} | sort PercentProcessorTime -Descending | Select -First 5 #| ft -AutoSize

    # Select top 5 Memory consumers [processes]
    $TopMemory = gwmi -computername $Server Win32_PerfFormattedData_PerfProc_Process | select IDProcess,Name,WorkingSet,PercentProcessorTime | 
    where { $_.Name -ne "_Total" -and $_.Name -ne "Idle"} | sort WorkingSet -Descending | Select -First 5 #| ft -AutoSize

    # Lets get all running processes [This one shows ProcessID and User name]:
    $AllProcesses = Get-WmiObject -computername $Server win32_process

    # Now, we can combine information from preveous queries [that is map CPU/Memory and User through the ProcessID]
    # First filter by top CPU utilization
    ' Top CPU'
    $TopCPU | 
    %{
        $ID = $_.IDProcess
        $CPU = $_.PercentProcessorTime
        $Mem = [Math]::floor($_.WorkingSet / 1024 / 1024)
        
        #$AllProcesses | where {$_.ProcessID -eq $ID} | Select @{name="Owner"; Expression={$($_.GetOwner().User)}},`
        #Name, @{name="CPU"; Expression={$CPU}}, @{name="Memory"; Expression={$Mem}}, ProcessID | FT -Autosize
        
        # I need to extract the Owner name and Process name
        $AllProcesses | where {$_.ProcessID -eq $ID} | % {$OwnerName = $_.GetOwner().User;}
        $Proc = $AllProcesses | Select Name, ProcessID | where {$_.ProcessID -eq $ID}
        
        # Finally compiling the resulting string
        $RezultsCPU = '  ' + $CPU + ',' + $Mem + ',' + $Proc.Name + ',' + $Proc.ProcessID + ',' + $OwnerName
        if ($CPU -gt 30)
            {$ColourCPU = 'Red'}
        else
            {$ColourCPU = 'Green'}
        Write-Host $RezultsCPU -ForegroundColor $ColourCPU
    }
    ''
    # Second, filter by top Memory utilization
    ' Top Memory'
    $TopMemory | 
    %{
        $ID = $_.IDProcess
        $CPU = $_.PercentProcessorTime
        $Mem = [Math]::floor($_.WorkingSet / 1024 / 1024)
        
        #$AllProcesses | where {$_.ProcessID -eq $ID} | Select @{name="Owner"; Expression={$($_.GetOwner().User)}},`
        #Name, @{name="CPU"; Expression={$CPU}}, @{name="Memory"; Expression={$Mem}}, ProcessID | FT -Autosize
        
        # I need to extract the Owner name and Process name
        $AllProcesses | where {$_.ProcessID -eq $ID} | % {$OwnerName = $_.GetOwner().User;}
        $Proc = $AllProcesses | Select Name, ProcessID | where {$_.ProcessID -eq $ID}
        
        # Finally compiling the resulting string
        $RezultsMemory = '  ' + $Mem  + ',' + $CPU + ',' + $Proc.Name + ',' + $Proc.ProcessID + ',' + $OwnerName
        if ($Mem -gt 400)
            {$ColourMemory = 'Red'}
        else
            {$ColourMemory = 'Green'}
        Write-Host $RezultsMemory -ForegroundColor $ColourMemory
    }
    ''
}

Powershell – Process CPU Affinity

There are cases when we want to control how much CPU a particular process can consume.
Current servers are mostly having more then one processor therefore one of the quickest ways of limiting CPU allocation is by setting CPU affinity for the process.

This particular script was written for the Citrix XenApp environment where it was identified that sometimes Internet Explorer was consuming all the avilable CPU and as a result slow down the whole server. Users are not happy 🙂
By setting only one processor for the IE process we can isolate the issue [at least to some degree].

#Get the time for logging purposes
$D = Get-Date
$T = '' + $D.ToShortDateString() + ' ' + $D.ToShortTimeString()

#Select only iexplore proceses
$Proccesses = Get-Process | ? {$_.Name -eq "iexplore"}

#For each iexplore process do
foreach ($Proccess in $Proccesses)
{
    #Find current CPU utilization
    $CPUUtil = (get-wmiobject Win32_PerfFormattedData_PerfProc_Process | ? { $_.idprocess -eq $Proccess.id }).PercentProcessorTime

    '' + $CPUUtil + '% - ' + $Proccess.id 

    #If CPU is higher then 50 then change process affinity to use only second core [there must be at least two cores]
    if ($CPUUtil -gt 20)
    {
        Get-Process -id $Proccess.id | 
        %{
            #$_.ProcessorAffinity; 
            
            if ($_.ProcessorAffinity -ne 2)
            {
                #'Changing from ' + $_.ProcessorAffinity + ' to 2'
                $_.ProcessorAffinity = 2; 
                '' + $T + ': ' + $CPUUtil + '% CPU, ' + $Proccess.id + ' PID, ' + $Proccess.Name + ', CPU Affinity for the process "' + $Proccess.id + '" is set to ' + $_.ProcessorAffinity + ' because of ' + $CPUUtil + '% CPU utilization' >> C:\Temp\ControlProcessesAffinity_Log.txt
            }
            else
            {
                '' + $T + ': ' + $CPUUtil + '% CPU, ' + $Proccess.id + ' PID, ' + $Proccess.Name + ', Affinity have already been set for this process to 2, current value is: ' + $_.ProcessorAffinity >> C:\Temp\ControlProcessesAffinity_Log.txt
            }
        }
    }
}