Category: Databases


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.

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.

 

 

 

 

 

 

SQL performance counters are missing

SQL Performance Counters are Missing

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

How to create and deploy a client certificate for MAC: http://blogs.technet.com/b/configmgrteam/archive/2013/04/05/how-to-create-and-deploy-a-client-cert-for-mac-independently-from-configmgr.aspx

Transforming .cer to .pem or vice-versa: https://www.sslshopper.com/ssl-converter.html

using openssl to convert a certificate format to another format: https://support.ssl.com/Knowledgebase/Article/View/19/0/der-vs-crt-vs-cer-vs-pem-certificates-and-how-to-convert-them

Exporting a private key: https://technet.microsoft.com/en-us/library/cc754329.aspx

 

 

Introduction: How to migrate a SQL cluster to a new SAN ?

Environment: Server 2008 r2 (failover cluster – aka MSCS) and SQL 2008 sp3

Reference about Failover clustering:

http://technet.microsoft.com/en-us/library/cc753362(v=ws.10).aspx

http://technet.microsoft.com/en-us/library/ff182338(v=ws.10).aspx

 

I selected for your this article  – how to move a SQL 2008 cluster to a new SAN:

https://gallery.technet.microsoft.com/SAN-Migration-step-for-SQL-d66dea19

 

Notes from the field:

http://blogs.technet.com/b/hugofe/archive/2012/07/04/windows-2008-2008-r2-san-migration.aspx

 

Note regarding the Quorum:

The Cluster service recognizes and identifies disks by their disk signatures (stored on the Quorum). Disk signatures are stored on the physical disk in the master boot record (MBR)

http://support.microsoft.com/kb/280425/en-us

Failover Clustering error codes: http://technet.microsoft.com/en-us/library/cc756229(v=WS.10).aspx

 

Note regarding Disk Signatures:

http://blogs.technet.com/b/markrussinovich/archive/2011/11/08/3463572.aspx
 
Disk Signatures
 
A disk signature is four-byte identifier offset 0x1B8 in a disk’s Master Boot Record, which is written to the first sector of a disk. This screenshot of a disk editor shows that the signature of my development system’s disk is 0xE9EB3AA5 (the value is stored in little-endian format, so the bytes are stored in reverse order).
 
Windows uses disk signatures internally to map objects like volumes to their underlying disks and starting with Windows Vista, Windows uses disk signatures in its Boot Configuration Database (BCD), which is where it stores the information the boot process uses to find boot files and settings.
Disk Signature Collisions
 
Windows requires the signatures to be unique, so when you attach a disk that has a signature equal to one already attached, Windows keeps the disk in “offline” mode and doesn’t read its partition table or mount its volumes. This screenshot shows how the Windows Disk Management administrative utility presents an offline disk that I caused when I attached the VHD Disk2vhd created for my development system to that system:
 
If you right-click on the disk, the utility offers an “Online” command that will cause Windows to analyze the disk’s partition table and mount its volumes:
 
When you chose the Online menu option, Windows will without warning generate a new random disk signature and assign it to the disk by writing it to the MBR. It will then be able to process the MBR and mount the volumes present, but when Windows updates the disk signature, the BCD entries become orphaned, linked with the previous disk signature, not the new one.
 
 
http://support.microsoft.com/kb/937251
 
The BIOS may or may not enumerate disks in a specific order. There is no direct relationship between the BIOS order, and the order in which Windows numbers the disks. During startup, Windows switches from using the BIOS INT13 support to native Windows drivers to access disks. Windows waits for several seconds for the system disk to enumerate through Plug and Play. When there is a match within the time-out period, normal startup will proceed. Otherwise, the system will trigger a bug check with Stop error code of 0x7B. Windows uses other mechanisms to differentiate disks, as Windows has no control over the disk-numbering process before startup. Windows has no information about any changes to hardware when the computer is turned off. Therefore, Windows initiates its own query for device enumeration.  
 
The disk numbers that are assigned by Windows after it switches to native Windows storage controller drivers during startup are dependent solely on the order in which the disks are enumerated and processed by Plug and Play. Windows will enumerate available fixed disks, followed by removable disks, assuming that the correct native Windows drivers are already present and installed on the system. Various uncontrollable timing factors may affect the enumeration order.
 

Introduction:

Event forwarding (also called SUBSCRIPTIONS) is a mean to send Windows event log entries from source computers to a collector. A same computer can be a collector or a source.

There are two methods available to complete this challenge – collector initiated and source initiated:

Parameter Collector Initiated Source Initiated
Socket direction (for firewall rules) Collector –> Source Collector –> Source
Initiating machine Collector Source
Authentication Type Kerberos Kerberos / Certificates

This technology uses WinRM (HTTP protocol on port TCP 5985 with WinRM 2.0, else TCP 80) . Be careful with the Window firewall and configure it to allow WinRM incoming requests.

WinRM is the ‘server’ component and WinRS is the ‘client’ that can remotely manage the machine with WinRM configured.

Differences you should be aware of:

WinRM 1.1
Vista and Server 2008
Port 80 for HTTP and Port 443 for HTTPS

WinRM 2.0
Windows 7 and Server 2008 R2
Port 5985 for HTTP and Port 5986 for HTTPS

WinRM 1.1 can also be downloaded and installed on pre-R2 2003 and XP from here.

Windows Server 2008 Core:

In order to forward events from a 2008 Server that is not R2, you will need to make a few changes. The first change is the default listening port, it needs to be changed from TCP 80 to TCP 5985. Additionally you may need to start the Windows Event Collector Service.

net start wecsvc
winrm set winrm/config/listener?Address=*+Transport=HTTP @{Port=”5985”}

Basic configuration:

on source computers and collector computer:  winrm quickconfig     and add the collector computer account to the local administrators group

To verify a listener has been created type winrm enumerate winrm/config/listener

WinRM Client Setup

Just to round off this quick introduction to WinRM, to delete a listener use winrm delete winrm/config/listener?address=*+Transport=HTTP

on collector computer: wecutil qc. Add the computer account of the collector computer to the Event Log Readers Group on each of the source computers

on collector computer: create a new subscription from event viewer (follow the wizard)

WinRS: WinRS (Windows Remote Shell) is the client that connects to a WinRM configured machine (as seen in the first part of this post). WinRS is pretty handy, you’ve probably used PSTools or SC for similar things in the past. Here are a few examples of what you do.

Connecting to a remote shell
winrs -r:http://hostnameofclient "cmd"
Stop / Starting remote service
winrs -r:http://hostnameofclient "net start/stop spooler"
Do a Dir on the C drive
winrs -r:http://hostnameofclient "dir c:\"

WinRS

Forwarded Event Logs:

This is configured using ‘subscribers’, which connect to WinRM enabled machines.

To configure these subscribers head over to event viewer, right click on forwarded events and select properties. Select the 2nd tab along subscriptions and press create.

This is where you’ll select the WinRM enabled machine and choose which events you would like forwarded.

Subscriptions

Right click the subscription and select show runtime status.

Error 0x80338126

Now it took me a minute or two to figure this one out. Was it a firewall issue (this gives the same error code), did I miss some configuration steps? Well no, it was something a lot more basic than that. Remember earlier on we were talking about the port changes in WinRM 1.1 to 2.0?

That’s right, I was using server 2008 R2 to set the subscriptions which automatically sets the port to 5985. The client I configured initially was server 2008 so uses version 1.1. If you right click the subscription and click properties -> advanced you’ll be able to see this. I changed this to port 80 and checked the runtime status again.

[DC2.domain.local] – Error – Last retry time: 03/02/2011 20:20:30. Code (0x5): Access is denied. Next retry time: 03/02/2011 20:25:30.”

Head back to the advanced settings and change the user account from machine account to a user with administrative rights. After making these changes the forwarded events started to flow.

Subscriptions Advanced

Additional considerations:

In a workgroup environment, you can follow the same basic procedure described above to configure computers to forward and collect events. However, there are some additional steps and considerations for workgroups:

  • You can only use Normal mode (Pull) subscriptions
  • You must add a Windows Firewall exception for Remote Event Log Management on each source computer.
  • You must add an account with administrator privileges to the Event Log Readers group on each source computer. You must specify this account in the Configure Advanced Subscription Settings dialog when creating a subscription on the collector computer.
  • Type winrm set winrm/config/client @{TrustedHosts="<sources>"} at a command prompt on the collector computer to allow all of the source computers to use NTLM authentication when communicating with WinRM on the collector computer. Run this command only once. Where <sources> appears in the command, substitute a list of the names of all of the participating source computers in the workgroup. Separate the names by commas. Alternatively, you can use wildcards to match the names of all the source computers. For example, if you want to configure a set of source computers, each with a name that begins with “msft”, you could type this command winrm set winrm/config/client @{TrustedHosts="msft*"} on the collector computer. To learn more about this command, type winrm help config.

If you configure a subscription to use the HTTPS protocol by using the HTTPS option in Advanced Subscription Settings , you must also set corresponding Windows Firewall exceptions for port 443. For a subscription that uses Normal (PULL mode) delivery optimization, you must set the exception only on the source computers. For a subscription that uses either Minimize Bandwidth or Minimize Latency (PUSH mode) delivery optimizations, you must set the exception on both the source and collector computers.

If you intend to specify a user account by using the Specific User option in Advanced Subscription Settings when creating the subscription, you must ensure that account is a member of the local Administrators group on each of the source computers in step 4 instead of adding the machine account of the collector computer. Alternatively, you can use the Windows Event Log command-line utility to grant an account access to individual logs. To learn more about this command-line utility, type wevtutil sl -? at a command prompt.

References:

http://blogs.technet.com/b/jepayne/archive/2015/11/24/monitoring-what-matters-windows-event-forwarding-for-everyone-even-if-you-already-have-a-siem.aspx

http://blogs.technet.com/b/jepayne/archive/2015/11/20/what-should-i-know-about-security-the-massive-list-of-links-post.aspx

https://technet.microsoft.com/en-us/library/cc748890.aspx

http://windowsitpro.com/security/q-what-are-some-simple-tips-testing-and-troubleshooting-windows-event-forwarding-and-collec

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

http://blogs.technet.com/b/askperf/archive/2010/09/24/an-introduction-to-winrm-basics.aspx

http://msdn.microsoft.com/en-us/library/aa384372(v=vs.85).aspx

Video:

Tutorials:

1st: Event forwarding between computers in a Domain

http://tutorial.programming4.us/windows_7/Forwarding-Events-(part-1)—How-to-Configure-Event-Forwarding-in-AD-DS-Domains.aspx

2nd: Event forwarding between computers in workgroup

http://tutorial.programming4.us/windows_7/Forwarding-Events-(part-2)—How-to-Troubleshoot-Event-Forwarding—How-to-Configure-Event-Forwarding-in-Workgroup-Environments.aspx

Additional article talking about Event forwarding too:

http://joshuadlewis.blogspot.fr/2014/10/advanced-threat-detection-with-sysmon_74.html

 

http://blogs.technet.com/b/askds/archive/2011/09/26/advanced-xml-filtering-in-the-windows-event-viewer.aspx

http://blog.oneboredadmin.com/2013/05/filtering-windows-event-log-using-xpath.html

 

 

 

 

Here are tips and tricks about AD/Azure RMS:

Azure RMS blog: http://blogs.msdn.com/b/rms/

RMS for externals: https://blogs.msdn.microsoft.com/rms/2013/11/18/rights-management-licensing-terms-for-orgs-and-isvs/

Azure RMS Documentation: http://blogs.technet.com/b/rms/archive/2016/03/31/announcement-azure-rms-documentation-library-update-for-march-2016.aspx

PowerShell for Azure RMS: https://technet.microsoft.com/en-us/library/jj585027.aspx

Comparing Azure RMS and AD RMS: https://technet.microsoft.com/en-us/library/jj739831.aspx

Whitepapers about RMS: http://www.microsoft.com/en-us/download/confirmation.aspx?id=40333

 

PREVIOUSLY:

How AD RMS works: http://technet.microsoft.com/en-us/library/how-adrms-works.aspx

AD RMS concepts: http://blogs.technet.com/b/rms/archive/2012/04/16/ad-rms-infrastructure-concepts-part-1.aspx

Hosting the AD RMS Servers in an Internal Network and Publishing Them to the Internet through a Reverse Proxy with an Application Layer Firewall: http://technet.microsoft.com/fr-fr/library/dd996632%28v=ws.10%29.aspx

Making AD RMS Available on the Internet: http://technet.microsoft.com/fr-fr/library/hh311037%28v=ws.10%29.aspx

AD RMS best practices: http://technet.microsoft.com/en-us/library/jj735304.aspx

Operations: http://technet.microsoft.com/fr-fr/library/dd772674%28v=ws.10%29.aspx

 

 

Reference: http://support.microsoft.com/kb/318785

Microsoft .net Framework is a software package that is used by many applications, it runs in a software environment as opposed to hardware environment. The Microsoft .net Framework provides language interoperability across several programming languages. Microsoft .net Framework does a lot of really cool things and is widely used, the only problem is there are several different versions of the Microsoft .net Framework, and finding out if you have the right version of the program can be a bit tricky. I would like to give you a quick and easy way to find out if you have the version you need and if you don’t a simple way to get it.

Click Start>>Type Regedit>>Hit Enter>>Click Edit>>Click Find>>Type NET Framework>>Check the installed versions

I prefer instead to download this free program called .NET Version Detector ( download here ). The reason this tool is so much more effective is that you simply have to download the file, extract it, and run it. You don’t even have to install the program, it’s completely portable. You run the program and get a nice graphical interface that will tell you what versions of the Microsoft .net Framework are installed on your system and what versions are missing from your system.

 

Note: With win7 .NET Framework 3.5 is installed by default.