Create a Batch file containing the following:
@echo off
@echo 100...
@powershell "Get-ChildItem -Path \\192.168.1.1\Data\Splitting\100_*.* | Select-Object FullName"
@echo Alle anderen (ausser excluded)...
@powershell "Get-ChildItem -Path \\192.168.1.1\Data\Splitting\*.* -Exclude @('100_*.*','_a file to exclude containing Spaces.bat') | Select-Object FullName"
pauseWhat is the “best” PacketSize argument to establish a connection to a SQL Server?
The answer is: It depends (on your very specific network)!
In our case a setting of a value higher than 1500 Bytes does not have a positive outcome. That is because the MTU of the Network has a maximum of 1.500 Bytes per Packet. This is often the case, because the implementation of the IP Protocol v2 uses this as the maximum packet size.
To figure out, what is the maximum value in your network, use the following Power Shell Script, originally authored by Thomas Stringer (http://sqlsalt.com), found in this great article by Robert L Davies (http://sqlsoldier.net/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith):
$UpperBoundPacketSize = 1500
if ($args -eq "-Q") {$QuietMode = 1}
else {$QuietMode = 0}
$IpToPing = "mp-bi"
do {
if ($QuietMode -eq 0) {Write-Host "Testing packet size $UpperBoundPacketSize"}
$PingOut = ping $IpToPing -n 1 -l $UpperBoundPacketSize -f
$UpperBoundPacketSize -= 1
} while ($PingOut[2] -like "*fragment*")
$UpperBoundPacketSize += 1
$Mtu = $UpperBoundPacketSize + 28
Write-Host "MTU: $Mtu" -ForegroundColor Green
When you change the SQL Server Account or attach Database Files from another computer you may notice the Access Denied error message in SQL Server Management Studio.
To overcome this issue, you may run the following Power Shell script to add the SQL Service User and a userdefined Local Admins Group to each single .mdf, .ldf, .ndf file. Manually setting permissions via Windows Explorer on the parent directory does not change the permissions for each file within.
It might be required to run he Script with elevated Administrative Rights.
function GetFiles($path, [string[]]$exclude)
{
foreach ($item in Get-ChildItem $path)
{
if ($exclude | Where {$item -like $_}) { continue }
if (Test-Path $item.FullName -PathType Container)
{
$item.FullName
GetFiles $item.FullName $exclude
}
else
{
$item.FullName
}
}
}
function SetPermissions($file)
{
if ($file -like "*.mdf" -or $file -like "*.ndf" -or $file -like "*.ldf" -or $file -like "*.ndf*")
{
$acl = Get-Acl $file
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("{YOURDOMAIN}\{SQLSERVERDATABASEENGINESERVICEACCOUNT}","FullControl","Allow")
$acl.SetAccessRule($AccessRule)
$acl | Set-Acl $file
$acl = Get-Acl $file
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("{LOCALSERVER}\{LOCALSERVERADMINSGROUP}","FullControl","Allow")
$acl.SetAccessRule($AccessRule)
$acl | Set-Acl $file
}
else
{
$file
}
}
# First Directory to iterate recursively...
foreach ($file in GetFiles("G:\DWH"))
{
SetPermissions($file)
}
# Second Directory to iterate recursively...
foreach ($file in GetFiles("P:\DWH"))
{
SetPermissions($file)
}