SQL Server Express – Scheduling Jobs via Windows Task Scheduler

Background

Needing a lightweight SQL Server to use as a failover, in case of Production outage,  settled on Sql Express, v2017 no doubt.

 

Problem

SQL Server Express is free, but missing SQL Server Agent, and so no built-in scheduler.

There are some tasks that I need to schedule.

Those tasks are backing up the databases, Index Defrag, and incremental data refresh.

Like Dela potholes soon as I make my way to doorsteps.

 

Files

refreshData.cmd

Outline

  1. Logging
    • Make directory log, if not present
    • If log\refreshdata.log file exists remove it
  2. Issue sqlcmd
    • Pass along the following arguments
      • -b
        • If error occurs in SQL, set errorlevel environment variable
      • -d
        • pass along database name; in our case hrdb
      • -e
        • Pass along payload
        • Call Stored Procedure ( exec dbo.usp_RefreshData )
      • -o
        • Output file is log\refreshData.log
  3. If %errorlevel% equal (-equ ) to zero (0), exit batch file
  4. call emailNotificationFailure.cmd errorlevel

Code


if not exist log mkdir log

if exist log\refreshData.log del log\refreshData.log

ECHO Errorlevel [SQLCMD - PRE] :- %ERRORLEVEL%


sqlcmd -b -S (local) -d hrdb -Q"exec [dbo].[usp_RefreshData]" -o log\refreshData.log

ECHO  [SQLCMD - POST] :- %ERRORLEVEL%

if "%errorlevel%" equ "0" exit /b %errorlevel%

REM Email Notification Failure
call emailNotificationFailure.cmd errorlevel

 

emailNotificationFailure.cmd

Outline

  1. Set _domain name
    • Here we set our email provider domain name
  2. Invoke powershell
    • Pass along -file emailNotificationFailure.ps1

Code

set _domain=labdomain.org
powershell.exe -file emailNotificationFailure.ps1 %_domain%

 

emailNotificationFailure.ps1

Code

Set-StrictMode -Version 1

#Get domain name as first argument
$domain=$args[0]

$self="daniel.adeniji"
$computer=$env:computername

$emailFrom = "$computer@$domain"
$emailTo = "$self@$domain"

$subject = "failed to synch data"

$body = "failed to synch data"

$SMTPServer = "smtp.$domain"

$SMTPServerPort = "25"

$fileAttachment="log\refreshData.log"

"emailFrom :- $emailFrom"
"emailTo :- $emailTo"
"SMTPServer :- $SMTPServer"
"SMTPServerPort :- $SMTPServerPort"

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, $SMTPServerPort)

$objEmailMessage = New-Object System.Net.Mail.MailMessage

$objEmailMessage.From = $emailFrom;
$objEmailMessage.To.Add($emailTo);
$objEmailMessage.Subject = $subject;
$objEmailMessage.Body = $body;

$objAttachment  =$null
if (Test-Path $fileAttachment -PathType Leaf)
{

	"File Attachment ( $fileAttachment ) Added "

	$objAttachment = new-object Net.Mail.Attachment($fileAttachment)

	$objEmailMessage.Attachments.Add($objAttachment)
	
}	
else
{

	"File Attachment ( $fileAttachment ) :- Skipped"

}

$SMTPClient.Send($objEmailMessage);

if ($objAttachment )
{

	$objAttachment.Dispose()
	
}	

 

Task Scheduler – Management

Task Scheduler – Management – Add task

General

Triggers

 

Actions

Actions – Edit Action

Search for documents that contain specific contents – Using grepWin

Background

After so many years using Windows I will like to say I get how to find documents that contain specific contents.

But, in all honesty, I occasionally still stumble with the Window Explorer Interface.

 

Habits

Habits are easy to form, and bad ones are hard to break.

But, may be if I start trying out other tools, I will eventually find one, where I don’t dither as often.

 

grepWin

The first tool that I found via Googling is grepWin and so let us try it out.

 

Download

I do not have permission to install software on my work laptop and so I need a portable, no install, option.

Downloaded Portable version from here.

 

Use

Launch

To use just right click on the file an run as an Administrator.

 

Search

Details

In the Screenshot below, we are doing the following:

    1. Search In :-
      • Targeted Folder is C:\Personal\dadeniji\blog
    2. Search Type :-
      1. Options :- Regex or Text Search
      2. Choice :- Easy as Sunday morning, Text Search
    3. Search For :-
      • Proxy
        • Search for files that have the word Proxy in them
    4. Options :-
      • Search case-sensitive
        • No – Skip burden of sensitivity searches
    5. Limit Search :-
      • Include System items
        • No – Skip system files
      • Include Subfolders
          • Yes, we we want a deep search and not just the current targeted folder

    • Include Binary files
      • No – Skip Binary files
      • Binary files are compiled application code
      • Often files with extension exe, com
    • File name match
      • *.sql
        • I am a lowly DBA and so I would likely have offered up *.sql as the file extension

Image

search_20171122_0719PM

 

Query Results

Windows 10 – Create Installation Media Using “Media Creation Tool”

Background

Wifey’s Windows 10 HP Stream Laptop is no longer accessible; nothing major but she can not remember what she changed the password to.

 

Diagnosis

A lot of tools for trying to get back in relies on using a Windows Installer, Linux Boot CD, or various third party tools.

I was able to start the laptop up with Windows 7 and Windows 2008-R2, but none of them picked up the internal hard-disk.

Seemingly, MSFT has secured the hard-drive more since these OSes.

 

Resolution

It is time to try out Windows 10 Installer.

Download

Please download Media Creation Tool from here.

Prepare

Once Media Creation Tool is downloaded, please launch it.

Steps

  1. License Terms
  2. What do you want to do
    • Choices
      • Upgrade this PC now
      • Create installation media  ( USB Flash drive, DVD, or ISO File ) for another PC
    • As we preparing an Installation Media for another PC, chose the second option, “Create installation media
  3. Select Language, and Edition, and Architecture
    • Language – English ( United States )
    • Edition – Windows 10
    • Architecture ( 32 bit, 64 bit, or both )
  4. 4 / 8 GB of free disk space needed on Drive C:
    • Depending on the Architecture Choice made earlier, that is 32 bit, 64 bit, or both, specific disk space requirement on the System Drive will have to be met
    • If either or, 32 or 64 bit is desired, 4 GB will be needed
    • On the other hand, if both bitness is desired, 8 GB will be needed
  5. Processing
    • Files are downloaded from MSFT
    • The image is cached locally
  6. Availed
    • Image is availed locally on the designated folder

Image

License Terms

LicenseTerms_20171122_0105AM

What do you want to do ?

WhatDoYouWantToDo_20171122_0106AM

 

Select language, architecture, and edition

SelectLanguageArchitectureAndEdition_20171122_0107AM

 

 

4GB/8 GB of free disk space needed on (C : )

DiskSpaceRequirement_20171122_0110PM

 

 

Burn the ISO file to a DVD

BurnTheISOFileToADVD_20171122_0236AM

Java – JNLP – Error – “Weak signature algorithm MD5withRSA”

Background

Trying to access a Java Web Application, but running into an error.

Error

The following resource is signed with a weak signature algorithm MD5withRSA and is treated as unsigned

Image

Body

Detail

Textual

Unsigned application requesting unrestricted access to system
The following resource is signed with a weak signature algorithm
MD5withRSA and is treated as unsigned.

http://hod/hod/WSCachedSupporter2.jar

 

 

Remediation

Outline

  1. Identify JRE
  2. Access lib\security folder
  3. Edit java.security
    • jdk.jar.disabledAlgorithms
      • Search out jdk.jar.disabledAlgorithms
      • Comment out reference to MD5

Steps

Access Control Panel Applet – Java
Access Control Panel Applet – Java – Tab – General

Access Control Panel Applet – Java – Tab – java

Access Control Panel Applet – Java – Tab – java – view – Java Runtime Environment Settings

Image

Explanation

  1. Settings
    • C:\Program Files\Java\jre1.8.0_131\bin\javaw.exe
      • Architecture :- x86_64
    • C:\Program Files (x86)\Java\jre7\bin\javaw.exe
      • Architecture :- x86
Folder :- Lib\security

Once we know the path to our JRE, we will navigate to the lib\security folder.

In our case the JRE path is C:\Program Files\Java\jre1.8.0_131.

The version number of Java is 1.8.0_131.  Major Version is 1.8.0 and minor version is 131.

Folder :- Lib\security – Files

 

Folder :- Lib\security – File – java.security

Original

Revised

Explanation
  1. Remove MD5 from the list of disabled Algorithms
  2. Data
    • Original
      • jdk.jar.disabledAlgorithms=MD2, MD5, RSA keySize < 1024
    • Revised
      • # dadeniji 2017-11-3 2:34 PM
        #jdk.jar.disabledAlgorithms=MD2, MD5, RSA keySize < 1024
        jdk.jar.disabledAlgorithms=MD2, RSA keySize < 1024

 

Error :- Application Blocked By Java Security – Your security settings have blocked an application signed with an expired or not-yet-valid certificate from running

Image

Textual

Your security settings have blocked an application signed with an expired or not-yet-valid certificate from running

Remediate

Outline

Add Applications URL to Security Exception list

  1. Access MS Windows Control Panel
  2. Launch Java Applet
  3. Within Java Control Panel
    • Access Security Tab
      • Review “Exception Site List
      • Click the “Edit Site List” button to manage the list
    • In the “Exception Site List” window
      • Click the Add button to add new sites
      • And, the Remove button to remove existing ones
      • Please note that you can not amend existing entries
        • Go ahead and a new one
        • And, remove the former

Images

Java Control Panel – Tab – Security
Java Control Panel – Tab – Security – 01

Java Control Panel – Tab – Security – 02 [ Empty Exception Site List]

Java Control Panel – Tab – Security – 03 [ Added URL ]

 

Java Control Panel – Tab – Security – 04 [ Added URL ]

 

Java Control Panel – Tab – Security – 05 [ Confirm Security Risk ]

 

Works

Now everything is working…

We have access to mainframe 3270 terminals from any browser.

Dedicated

como siempre, nothing new here…

  1. Zhaojun’s Blog
    • a quick workaround to fix the unsigned JNLP issue after Java upgraded to version 8 update 131
      Published On :- 2017-May-5th
      Link
  2. InfoPackets.com
    • Dennis Faas
      • How to Fix: Java ‘Expired or not-yet-valid Certificate’ Error
        Link

 

Win OS – Recycle Bin Pruning Using GetFolderSize

Background

A couple of days ago received an alert about low space on one of our servers.

 

Troubleshooting

Remote desktop and started snooping around.

 

Space Sniffer

Image

SpaceSniffer_20171026_0233PM

 

Explanation

  1. Nice 57 GB in Recycle Bin

 

Windows Explorer

Folder Options

Image

Image – Hide Operating System Files

FolderOptions_20171026_0243PM

 

Image – Hide Operating System Files – Un-hiding

 

FolderOptions_20171026_0244PM

Image – Hide Operating System Files – Unhide

FolderOptions_20171026_0245PM

 

View Hidden System Folders

Reviewed D:\$RECYCLE.BIN through MS Windows Explorer, but sadly stuck at “Access is Denied”.

Explorer_SystemFilesNotHidden_AccessIsDenied_20171026_0248PM

 

Get Folder Size

Initial

Drives

Image

GetFolderSize_Folder_000_20171026_0251PM

Tabulated
Drive Total Free Free% Used Used%
C: 437.63 GB 112.95 GB 26% 324.68 GB 74%
D: 500.00 GB 93.35 GB 19% 406.65 GB 81%

 

Folders

Image

 

Name Size Percent Files Folders
D:\$RECYCLE.BIN 57.357 GB 100% 117,644 11,239
Folder #1 22.570 GB 39.35% 11 2
Folder #2 21.072 GB 36.74% 5 0
Folder #3 12.160 GB 21.20% 113,090 11,194
Folder #4 1.390 GB 2.42% 16 2
Folder #5 164 MB 0.29% 4496 10

 

Folders – Individual

Folders – Individual – 001
Image – Folders & Files – Right Side

Explanation
  1. The two biggest files are SQL Server backup files
    • They are 10 GB each
    • Had them since Feb & March 2015
Image – Folders & Files – Left Side

Image – Folders & Files – Discarding

Explanation
  1. Warning about permanent deletion

 

Folders – Individual – 002
Image – Folders & Files – Right Side

Explanation
  1. The two biggest files are SQL Server data files
    • They are 10 GB each
    • Had them since May & June 2016
Image – Folders & Files – Right Side – Tagged

 

Summary

SpaceSniffer has an easy interface to find biggest files.

And, when they are in the recycle bin, GetFolderSize makes it easy to discard them.

Apache – jMeter – Preparation – Java JRE

Background

Pardon the pun, but measuring out jMeter appropriateness for a task we have at hand.

Error

image

NotAbleToFindJavaExecutableOrVersion_20171023_0536PM

 

textual

jmeter
Not able to find Java executable or version. Please check your Java installation.
errorlevel=2
Press any key to continue . . .

 

Troubleshooting

Validation

JRE

Outline

Let us see if we have JRE installed, and if so what version.

 

Steps

  1. Launch MS Windows Control Panel
  2. If Java Applet is not present, then we do not have Java installed, and we can skip the rest of our validation steps
  3. If Java Applet is present, access the Java Applet
  4. The “Java Control Panel” window appears
  5. Access the Java Tab
  6. Click the View button
  7. The “Java Runtime Environment Settings” window appears
    • There are two tabs, User and System
      • User Tab
        • In our User tab, we noticed that we have JRE 1.7 installed
        • We also noticed that its path indicates an install path of “C:\Program Files (x86)\Java\jre7\bin\javaw.exe”
        • Having it in x86 is ominous for us, as our OS in 64-bit

 

Image

javaRuntimeEnvironmentSettings_20171023_0539PM

 

Remediation

Download and install 64-bit Java JRE if your OS is 64-bit

 

Validation

Image

javaRuntimeEnvironmentSettings_20171023_0548PM

 

Tabulated

  1. We have two versions of JRE
    • v1.8
      • Path :- C:\Program Files\java\jre1.8.0_131\bin\javaw.exe
      • Architecture :- x86_64
    • v1.7
      • C:\Program Files (x86) \ Java\jre7\bin\javaw.exe
      • Architecture :- x86

 

Explanation

It looks like we can rumble with x86_64.

 

Success

Ran jmeter.bat again and we are good!

jmeter_20171024_0903AM

Reporting Services – Max Memory

Background

Reviewing one of our SQL Server Instances and noticed this entry in the SQL Server Error Log.

ErrorLog

Image

Textual

2017-10-02 09:23:00.29 Server Detected 16371 MB of RAM. This is an informational message; no user action is required.
2017-10-02 09:23:00.29 Server Using locked pages in the memory manager.
2017-10-02 09:23:01.42 Server Large Page Allocated: 32MB
2017-10-02 09:23:01.99 Server Cannot use Large Page Extensions: Failed to allocate 32MB

 

TroubleShooting

Resource Monitor

Image

Explanation

  1. Total
    • 97% Used Physical Memory
    • 428 MB available
  2. Processes
    • ReportingServicesService.exe
      • Commit :- 2,495,860 KB ( 2.495 GB )
      • Working Set :- 2,243,072  KB ( 2.495 GB )
      • Sharable :- 52,700 KB ( 50 MB )
      • Private :- 2,190,372 KB ( 2.190 GB )
    • sqlservr.exe
      • Commit :- 795,204 KB ( 795 MB )
      • Working Set :- 123,712  KB ( 123 MB )
      • Sharable :- 32,376 KB ( 32 MB )
      • Private :- 90,378 KB ( 90 MB )

Remediation

Overview

  1. Identify location where Reporting Services is located
    • We can use Control Panel, Services applet
      • “C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe”
        • MSRS12
          • SQL Server 2014
  2. Make backup of rsreportserver.config
  3. Edit rsreportserver.config
    • Add or amend
      • WorkingSetMaximum
        • Syntax
          • <WorkingSetMaximum>ValueInKB</WorkingSetMaximum>
        • Sample
          • <WorkingSetMaximum>1000000</WorkingSetMaximum>
            • 1000000 KB is 1 GB

 

Services Applet

 

rsreportserver.config

WorkingSetMaximum

Overview

Here we set maximum memory for Reporting Services to 1000000.

Value is in KB.

And, 1000000 KB translates to 1 GB.

 

Textual


<!-- Added by dadeniji on 2017-10-05 10:18 AM -->
<WorkingSetMaximum>1000000</WorkingSetMaximum> 

Image

 

Confirm

Restart MS Reporting Services and review through Log files and Resource Monitor.

ReportServerService*.log

Image

Textual

library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.

Explanation

  1. MemorySafetyMagin
    • Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
  2. MemoryThreshold
    • Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.

 

Resource Monitor

Image

Explanation

  • Total
    • 85% Used Physical Memory
    • 2031 MB available
  • Processes
    • ReportingServicesService.exe
      • Commit :- 271,732 KB ( 270 MB )
      • Working Set :- 138,360 KB ( 140 MB )
      • Sharable :- 32,512 KB ( 34 MB )
      • Private :- 104,168 KB ( 104 GB )

Summary

We are satisfied with the change we made.

 

References

  1. CSS SQL Server Engineers
    • PSSQL
      • SQL Server and Large Pages Explained….
        Link
    • Adam W. Saxton
      • Troubleshooting Memory Issues with Reporting Services
        Link