Get the allocation unit size of an NTFS partition in Server 2012

If you’re a SQL Server Pro you will know that the volumes the SQL log and datafiles are stored on should be formatted with a 64K cluster size. This is a SQL Server Best practice as stated in this link.

To find out what the allocation unit size is for a drive you would run the command below:

fsutil fsinfo ntfsinfo [your drive]

Running the same command on a test system on the E:\ drive I get the following result:

C:\>fsutil fsinfo ntfsinfo E:
NTFS Volume Serial Number :       0x0c76bbd176bbba32
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x00000000013fe7ff
Total Clusters :                  0x0000000000027fcf
Free Clusters  :                  0x0000000000027d5b
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000010000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000000001
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000000cca0
Resource Manager Identifier :     28DB2529-6D43-11E6-80C7-00505693BAC6

As you can see from the above, the Bytes Per Cluster is equal to 65,536 bytes or 64K.

You would have to run this command for every drive that you want to check. I found this handy piece of PowerShell code that gets just the allocation unit size for all disks in the system:

$wql = "SELECT Label, Blocksize, Name FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object Label, Blocksize, Name

The result of which would look similar to this on a system running SQL Server:

20160830081407

The results above show which disks are configured with the default 4K allocation unit size and those configured with 64K which are the SQL disks.

References:
How To Determine Your Hard Disks Cluster Size
SQL Server Best Practices Article
Disk Partition Alignment Best Practices for SQL Server
Get Cluster size for all disks and volumes on a Windows machine using powershell and wmi

PowerShell script to copy file to multiple remote servers

20160614150939I often have to copy files and folders from one server to another for various reasons. Copying stuff manually is a bore but if you are only copying to a couple of servers it is quicker to do it manually but if you have to copy to several servers it would be quicker to use a script. We can leverage PowerShell to perform this task.

We will use the Copy-Item cmdlet in PowerShell. It will copy  file and folders to a remote server using the following syntax:

Copy-Item "source" -Destination "\\server\C$"

Please note that the user you are running the commands under must also exist and work on the servers you are copying the file(s) to. All the examples in this post were performed in a test domain environment using a Domain Admin account but as long as your user has sufficient rights the commands should work fine.

Let us use an example to demonstrate how Copy-Item works. I want to copy the EMC Networker SQL agent NWVSS.exe from server vs11vdc01 to server vs11app01. To do so, I would run:

Copy-Item "C:\Software\EMC\Networker\NWVSS.exe -Destination \\vs11app01\c$\temp

As you can see from the above, I have stated the full path of the NWVSS.exe file and the destination it is going to, in this case \\vs11app01\c$\temp. Running  results in a successful file copy:

20160614132832

To copy to another server you could use the same command and change the destination accordingly but that wouldn’t good enough for the Japinator. I prefer a slightly more sophisticated solution – we can use the Get-Content cmdlet or gc for short to get the contents of a text file called servers.txt that contains a list of the servers you want to copy to. This text file simply contains a list of servers, each on a new line per the below:

20160614152653

We can pass each line of the contents of servers.txt as variables to the Copy-Item cmdlet to perform the copies to those servers. The script below will achieve this:

# This file contains the list of servers you want to copy files/folders to
$computers = gc "C:\scripts\servers.txt"

# This is the file/folder(s) you want to copy to the servers in the $computer variable
$source = "C:\Software\EMC\Networker\NWVSS.exe"

# The destination location you want the file/folder(s) to be copied to
$destination = "C$\temp\"

#The command below pulls all the variables above and performs the file copy
foreach ($computer in $computers) {Copy-Item $source -Destination "\\$computer\$destination" -Recurse}

The file copies successfully to all servers in C:\scripts\servers.txt successfully, below we see the file on vs11app02:

20160614141733

To make it a little but more sophisticated we will add a check to confirm that the remote servers’ path exists using the Test-Path cmdlet before performing the copy. If the path does not exist it will state that it “is not reachable or does not exist”:

# This file contains the list of servers you want to copy files/folders to
$computers = gc "C:\scripts\servers.txt"

# This is the file/folder(s) you want to copy to the servers in the $computer variable
$source = "C:\Software\EMC\Networker\NWVSS.exe"

# The destination location you want the file/folder(s) to be copied to
$destination = "C$\temp\"

foreach ($computer in $computers) {
if ((Test-Path -Path \\$computer\$destination)) {
Copy-Item $source -Destination \\$computer\$destination -Recurse
} else {
"\\$computer\$destination is not reachable or does not exist"
}
}

I hope this is useful to you and saves you time and energy! So kick this script off, get a coffee and put your feet up.

References:
Copy-Item
Get-Content
Test-Path
PowerShell : Copy File(s) TO / FROM remote Servers