Category: SQL Server


Microsoft’s file systems organize storage devices based on cluster size. Also known as the allocation unit size, cluster size represents the smallest amount of disk space that can be allocated to hold a file. Because ReFS and NTFS don’t reference files at a byte granularity, the cluster size is the smallest unit of size that each file system can reference when accessing storage. Both ReFS and NTFS support multiple cluster sizes, as different sized clusters can offer different performance benefits, depending on the deployment.

Full article from MS: https://blogs.technet.microsoft.com/filecab/2017/01/13/cluster-size-recommendations-for-refs-and-ntfs/

Summary:

ReFS cluster sizes

ReFS offers both 4K and 64K clusters. 4K is the default cluster size for ReFS, and we recommend using 4K cluster sizes for most ReFS deployments because it helps reduce costly IO amplification:

  • In general, if the cluster size exceeds the size of the IO, certain workflows can trigger unintended IOs to occur. Consider the following scenarios where a ReFS volume is formatted with 64K clusters:
    • Consider a tiered volume. If a 4K write is made to a range currently in the capacity tier, ReFS must read the entire cluster from the capacity tier into the performance tier before making the write. Because the cluster size is the smallest granularity that the file system can use, ReFS must read the entire cluster, which includes an unmodified 60K region, to be able to complete the 4K write.
    • If a cluster is shared by multiple regions after a block cloning operation occurs, ReFS must copy the entire cluster to maintain isolation between the two regions. So if a 4K write is made to this shared cluster, ReFS must copy the unmodified 60K cluster before making the write.
    • Consider a deployment that enables integrity streams. A sub-cluster granularity write will cause the entire cluster to be re-allocated and re-written, and the new checksum must be computed. This represents additional IO that ReFS must perform before completing the new write, which introduces a larger latency factor to the IO operation.
  • By choosing 4K clusters instead of 64K clusters, one can reduce the number of IOs that occur that are smaller than the cluster size, preventing costly IO amplifications from occurring as frequently.

Additionally, 4K cluster sizes offer greater compatibility with Hyper-V IO granularity, so we strongly recommend using 4K cluster sizes with Hyper-V on ReFS.  64K clusters are applicable when working with large, sequential IO, but otherwise, 4K should be the default cluster size.

NTFS cluster sizes

NTFS offers cluster sizes from 512 to 64K, but in general, we recommend a 4K cluster size on NTFS, as 4K clusters help minimize wasted space when storing small files. We also strongly discourage the usage of cluster sizes smaller than 4K. There are two cases, however, where 64K clusters could be appropriate:

  • 4K clusters limit the maximum volume and file size to be 16TB
    • 64K cluster sizes can offer increased volume and file capacity, which is relevant if you’re are hosting a large deployment on your NTFS volume, such as hosting VHDs or a SQL deployment.
  • NTFS has a fragmentation limit, and larger cluster sizes can help reduce the likelihood of reaching this limit
    • Because NTFS is backward compatible, it must use internal structures that weren’t optimized for modern storage demands. Thus, the metadata in NTFS prevents any file from having more than ~1.5 million extents.
      • One can, however, use the “format /L” option to increase the fragmentation limit to ~6 million. Read more here.
    • 64K cluster deployments are less susceptible to this fragmentation limit, so 64K clusters are a better option if the NTFS fragmentation limit is an issue. (Data deduplication, sparse files, and SQL deployments can cause a high degree of fragmentation.)
      • Unfortunately, NTFS compression only works with 4K clusters, so using 64K clusters isn’t suitable when using NTFS compression. Consider increasing the fragmentation limit instead, as described in the previous bullets.

While a 4K cluster size is the default setting for NTFS, there are many scenarios where 64K cluster sizes make sense, such as: Hyper-V, SQL, deduplication, or when most of the files on a volume are large.

Download sysmon:

NEW: Sysmon 6.0 is available ! : https://technet.microsoft.com/en-us/sysinternals/sysmon  and how to use it:

Installation and usage:

List of web resources concerning Sysmon: https://github.com/MHaggis/sysmon-dfir

Mark russinovitch’s RSA conference: https://onedrive.live.com/view.aspx?resid=D026B4699190F1E6!2843&ithint=file%2cpptx&app=PowerPoint&authkey=!AMvCRTKB_V1J5ow

Sysmon config files explained:

https://github.com/SwiftOnSecurity/sysmon-config

https://github.com/ion-storm/sysmon-config/blob/master/sysmonconfig-export.xml

https://www.bsk-consulting.de/2015/02/04/sysmon-example-config-xml/

View story at Medium.com

Else other install guides:

Sysinternals Sysmon unleashed

http://www.darkoperator.com/blog/2014/8/8/sysinternals-sysmon

 

Detecting APT with Sysmon:

https://www.rsaconference.com/writable/presentations/file_upload/hta-w05-tracking_hackers_on_your_network_with_sysinternals_sysmon.pdf

 

https://www.root9b.com/sites/default/files/whitepapers/R9B_blog_005_whitepaper_01.pdf

Sysmon with Splunk:

http://blogs.splunk.com/2014/11/24/monitoring-network-traffic-with-sysmon-and-splunk/

https://securitylogs.org/tag/sysmon/

Sysmon log analyzer/parsing sysmon event log:

https://github.com/CrowdStrike/Forensics/blob/master/sysmon_parse.cmd

https://digital-forensics.sans.org/blog/2014/08/12/sysmon-in-malware-analysis-lab

https://github.com/JamesHabben/sysmon-queries

http://blog.crowdstrike.com/sysmon-2/

logparser: http://www.microsoft.com/en-us/download/confirmation.aspx?id=24659

logparser GUI: http://lizard-labs.com/log_parser_lizard.aspx

Resources:

SQL Server performance: http://wp.me/p15Zft-8h

SQL Server Video archive: https://technet.microsoft.com/en-us/dn912438

Database tasks: https://technet.microsoft.com/en-us/library/ms165730(v=sql.105).aspx

T-SQL reference: https://technet.microsoft.com/en-us/library/ms189826(v=sql.90).aspx

SQL performance and troubleshooting: http://sqlnexus.codeplex.com/

Microsoft companion (MOC): http://www.microsoft.com/en-us/learning/companion-moc.aspx

Web sites:

Tips and tricks:

PowerShell: import-module SQLPS

Placement of tempdb in a dedicated disk (Raid 1) ,  same for log files  (RAID 1 or 10) and database files (RAID 5). Also dedicated disk for OS and dedicated disk for SQL server binaries.

Do a dbcc checkdb before each database backup

Use Buffer pool extension

Enable security: create logins, server roles, then for db: create users, database roles, database perms

Privileged the Microsoft service accounts (MSA) to run the SQL services.

Enable SQL audit

Enable DML triggers  (enable logons trigger)

Use SQL profile (but heavy in terms of performance). Else prefer to use (T-SQL) SQL trace (light footprint if well-designed).

Design a backup and restore strategy:

  • To backup: backup full + backup differential + backup transaction log + backup tail_log
  • To restore: restore first the full (with norecovery) + the last differential (with norecovery) + the latest transaction log (with recovery option) and eventually the latest Tail_log (if possible)
  • don’t forget to backup the tail log before to start a restore sequence
  • preferably use “backup device” which contains the full,differential,logs. Then you can backup the “backup device” using the OS backup software (Windows backup, Tivoli SM, Veritas Netbackup…)

Define maintenance plans:

– separate the maintenance plans to backup the system databases from the other databases (include also the check database integrity “dbcc checkdb” before each backup sequence)

– separate the maintenance plans to backup a Application Database from a maintenance plan to check only the Database health: check database integrity, reorganize indexes, update statistics.

 

 

 

 

 

 

During the hard exercise to recruit an IT Pros, you are obliged to ask questions, here are links to some Q&A:

http://www.techiebird.com/sendmail.html

https://www.brentozar.com/archive/2009/07/top-10-interview-questions-for-windows-sysadmins/

http://www.01world.in/p/windows.html

http://resources.intenseschool.com/top-interview-questions-for-system-administrators-microsoft/

https://www.toptal.com/sql/interview-questions

https://dwbi.org/database/sql/72-top-20-sql-interview-questions-with-answers

http://www.indiabix.com/technical/sql-server-common-questions/

https://www.linux.com/blog/10-job-interview-questions-linux-system-administrators

http://computernetworkingnotes.com/rhce-interview-questions/linux-interview-questions-for-experienced.html

 

 

Remotely enable PSRemoting and Unrestricted PowerShell Execution using PsExec and PSSession, then run PSRecon

Option 1 — WMI:
PS C:\> wmic /node:”10.10.10.10″ process call create “powershell -noprofile -command Enable-PsRemoting -Force” -Credential Get-Credential

Option 2 – PsExec:
PS C:\> PsExec.exe \\10.10.10.10 -u [admin account name] -p [admin account password] -h -d powershell.exe “Enable-PSRemoting -Force”

Next…

PS C:\> Test-WSMan 10.10.10.10
PS C:\> Enter-PSSession 10.10.10.10
[10.10.10.10]: PS C:\> Set-ExecutionPolicy Unrestricted -Force

Then…

Option 1 — Execute locally in-memory, push evidence to a share, and lock the host down:
[10.10.10.10]: PS C:\> IEX (New-Object Net.WebClient).DownloadString(‘https://github.com/gfoss/PSRecon/psrecon.ps1’)
[10.10.10.10]: PS C:\> Copy-Item PSRecon_* -Recurse [network share]
[10.10.10.10]: PS C:\> rm PSRecon_* -Recurse -Force
[10.10.10.10]: PS C:\> Invoke-Lockdown; exit

Option 2 — Exit PSSession, execute PSRecon remotely, send the report out via email, and lock the host down:
[10.10.10.10]: PS C:\> exit
PS C:\> .\psrecon.ps1 -remote -target 10.10.10.10 -sendEmail -smtpServer 127.0.0.1 -emailTo greg.foss[at]logrhythm.com -emailFrom psrecon[at]logrhythm.com -lockdown

Be careful! This will open the system up to unnecessary risk!!
You could also inadvertently expose administrative credentials when authenticating to a compromised host.
If the host isn’t taken offline, PSRemoting should be disabled along with disallowing Unrestricted PowerShell execution following PSRecon

Here are my recommendations to secure your computers and your domain:

Configuration\Windows Setting\Security Settings leaf.

Rename the Local Administrator Account: If the bad guy doesn’t know the name of your Administrator account, he’ll have a much harder time hacking it.

Disable the Guest Account: One of the worst things you can do is to enable this account. It grants a fair amount of access on a Windows computer and has no password. Enough said!

Disable LM and NTLM v1: The LM (LAN Manager) and NTLMv1 authentication protocols have vulnerabilities. Force the use of NTLMv2 and Kerberos. By default, most Windows systems will accept all four protocols. Unless you have really old, unpatched systems (that is, more than 10 years old), there’s rarely a reason to use the older protocols.

Disable LM hash storage: LM password hashes are easily convertible to their plaintext password equivalents. Don’t allow Windows to store them on disk, where a hacker hash dump tool would find them.

Minimum password length: Your minimum password size should be 12 characters or more. Don’t bellyache if you only have 8-character passwords (the most common size I see). Windows passwords aren’t even close to secure until they are 12 characters long — and really you want 15 characters to be truly secure. Fifteen is a magic number in the Windows authentication world. Get there, and it closes all sorts of backdoors. Anything else is accepting unnecessary risk.

Maximum password age: Most passwords should not be used longer than 90 days. But if you go to 15 characters (or longer), one year is actually acceptable. Multiple public and private studies have proven that passwords of 12 characters or longer are relatively secure against password cracking to about that length of time.

Event logs: Enable your event logs for success and failure. As I’ve covered in this column many times, the vast majority of computer crime victims might have noticed the crime had they had their logs on and been looking.

Disable anonymous SID enumeration: SIDs (Security Identifiers) are numbers assigned to each user, group, and other security subject in Windows or Active Directory. In early OS versions, non-authenticated users could query these numbers to identify important users (such as Administrators) and groups, a fact hackers loved to exploit.

Don’t let the anonymous account reside in the everyone group: Both of these settings, when set incorrectly, allow an anonymous (or null) hacker far more access on a system than should be given. These have been disabled by default since 2000, and you should make sure they stay that way.

Enable User Account Control: Lastly, since Windows Vista, UAC has been the No. 1 protection tool for people browsing the Web. I find that many clients turn it off due to old information about application compatibility problems. Most of those problems have gone away, and many of the remaining ones can be solved with Microsoft’s free application compatibility troubleshooting utility. If you disable UAC, you’re far closer to Windows NT security than you are a modern operating system.

Here’s the best part: Each of these settings is set correctly by default in Windows Vista/Server 2008 (and later). Most of my Windows security books were all about the settings I wanted you to more securely harden. These days, my best advice is don’t muck it up. When I see problems, it’s because people go out of their way to weaken them, and that’s never good.

Concretely:

  • Accounts: Rename administrator account — not highly effective but another security layer nonetheless (define a new name)
  • Accounts: Rename guest account (define a new name)
  • Interactive logon: Do not display last user name (set to “Enabled”)
  • Interactive logon: Do not require last user name (set to “Disabled”)
  • Interactive logon: Message text for users attempting to log on (define banner text for users to see – something along the lines of This is a private and monitored system…you abuse this system, you’re toast — just run it by your lawyer first)
  • Interactive logon: Message title for users attempting to log on — something along the lines of WARNING!!!
  • Network access: Do not allow enumeration of SAM accounts and shares (set to “Enabled”)
  • Network access: Let “Everyone” permissions apply to anonymous users (set to “Disabled”)
  • Network security: Do no store LAN Manager hash value on next password change (set to “Enabled”)
  • Microsoft Network client: send unencrypted password to third-party SMB servers (Set to “Disabled”)
  • Network security: LAN Manager authentication level (set to “Send NTLMv2 responses only. Refuse LM & NTLM”)
  • Shutdown: Allow system to be shut down without having to log on (set to “Disabled”)
  • Shutdown: Clear virtual memory pagefile (set to “Enabled”)

http://blogs.technet.com/b/askds/archive/2008/08/12/event-logging-policy-settings-in-windows-server-2008-and-vista.aspx

Using Powershell:

http://msexchange.me/2014/06/05/monitoring-event-id-thru-powershell/

http://community.spiceworks.com/topic/282720-powershell-event-log-monitor-email-alert-script-central-monitor

https://vijredblog.wordpress.com/2014/03/21/task-scheduler-event-log-trigger-include-event-data-in-mail/

Using SCOM:

http://jimmoldenhauer.blogspot.fr/2013/03/scom-2012-how-to-generate-alerts-from.html

http://scomandplus.blogspot.fr/2013/02/creating-rules-to-monitor-security-logs.html

http://thoughtsonopsmgr.blogspot.fr/2013/11/windows-event-log-monitoring-how-to-get.html

http://opsmgradmin.blogspot.fr/2011/05/scom-monitoring-windows-event-logs.html

 

 

 

 

Troubleshooting slow logons:

http://blogs.technet.com/b/askds/archive/2009/09/23/so-you-have-a-slow-logon-part-1.aspx

http://blogs.technet.com/b/askds/archive/2009/09/24/so-you-have-a-slow-logon-part-2.aspx

Logon process: http://fr.slideshare.net/ControlUp/understanding-troubleshooting-the-windows-logon-process

Tools for troubleshooting:

http://social.technet.microsoft.com/wiki/contents/articles/10128.tools-for-troubleshooting-slow-boots-and-slow-logons-sbsl.aspx

http://social.technet.microsoft.com/wiki/contents/articles/10123.troubleshooting-slow-operating-system-boot-times-and-slow-user-logons-sbsl.aspx

And powershell:

http://blogs.citrix.com/2015/08/05/troubleshooting-slow-logons-via-powershell/

Analyze GPOs load time: http://www.controlup.com/script-library/Analyze-GPO-Extensions-Load-Time/ee682d01-81c4-4495-85a7-4c03c88d7263/

 

How to use Xperf, Xbootmgr, Procmon, WPA?

xperf;xbootmgr;xperfview comes from Windows ADK (Windows performance toolkit sub part). Procmon is a sysinternal tool.

http://superuser.com/questions/594625/how-can-i-analyze-performance-issues-before-during-the-logon-process

http://blogs.technet.com/b/askpfeplat/archive/2012/06/09/slow-boot-slow-logon-sbsl-a-tool-called-xperf-and-links-you-need-to-read.aspx

http://social.technet.microsoft.com/wiki/contents/articles/10128.tools-for-troubleshooting-slow-boots-and-slow-logons-sbsl.aspx

Other interesting articles:

http://blogs.technet.com/b/askpfeplat/archive/2014/10/27/becoming-an-wpa-xpert-part-11-troubleshooting-long-group-policy-processing.aspx

https://www.autoitconsulting.com/site/performance/windows-performance-toolkit-simple-boot-logging/

https://randomascii.wordpress.com/2012/09/04/windows-slowdown-investigated-and-identified/

https://randomascii.wordpress.com/2013/04/20/xperf-basics-recording-a-trace-the-easy-way/

 

Windows Performance Analyzer (wpa.exe) youtube: https://www.youtube.com/watch?v=HGTlc_gWH_c

Xperf data collection tool: https://xperf123.codeplex.com/releases/view/66888

 

For boot tracing:

http://www.msfn.org/board/topic/140247-trace-windows-7-bootshutdownhibernatestandbyresume-issues/

xbootmgr -trace boot -traceFlags BASE+CSWITCH+POWER -resultPath C:\TEMP

with boot phases:
xbootmgr -trace boot -traceflags base+latency+dispatcher -stackwalk profile+cswitch+readythread 
       -notraceflagsinfilename -postbootdelay 120 -resultPath C:\TEMP
 

For shutdown tracing:

xbootmgr -trace shutdown -noPrepReboot -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

For Standby+Resume:

xbootmgr -trace standby -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

For Hibernate+Resume:

xbootmgr -trace hibernate -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

replace C:\TEMP with any temp directory on your machine as necessary to store the output files

Analyses of the boot trace:

Boot_MainPathBoot.png

To start create a summary xml file, run this command (replace the name with the name of your etl file)

xperf /tti -i boot_BASE+CSWITCH+POWER_1.etl -o summary_boot.xml -a boot

Analyses of the shutdown trace:

The shutdown is divided into this 3 parts:

Shutdown_picture.png

To generate an XML summary of shutdown, use the -a shutdown action with Xperf:

xperf /tti -i shutdown_BASE+CSWITCH+DRIVERS+POWER_1.etl -o summary_shutdown.xml -a shutdown

 

 
Finding remote session connected to your computer?
who is running a (hidden) remote PowerShell on your machine? Here’s a simple one-liner:
Get-WSManInstance -ConnectionURI (‘http://{0}:5985/wsman’ -f $env:computername) -ResourceURI shell -Enumerate
It will return anyone connecting via port 5985 to your machine. However, if you’re not running in a domain environment,
you first have to enable non-Kerberos connections
(remember that without Kerberos, you no longer know for sure that the target computer really is the computer it pretends
to be):
Set-Item WSMan:\localhost\Client\TrustedHosts * -Force