Fail2Ban 65535 elements in IPSET limit

Edit file /etc/fail2ban/action.d/iptables-ipset-proto4.conf

add/change maxelem parameter:

actionstart = ipset --create f2b-<name> iphash hashsize 32768 maxelem 200000

Save file, than restart Fail2Ban service. To check applied parameter execute
# ipset -L | grep "Header"

Powershell TLS

 Get-PSRepository

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force

Register-PSRepository -Default -InstallationPolicy Trusted

Перенос БД tempdab на новое место

-- Новый каталог для базы tempdb
DECLARE @Path as NVARCHAR(400) = 'E:\SQL_Temp'
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @SQLString NVARCHAR(400)

USE master;

DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR
(
SELECT
name,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
)
OPEN fnc;
FETCH fnc INTO @logicalName, @physicalName;
WHILE @@FETCH_STATUS=0

BEGIN
SET @SQLString = '
ALTER DATABASE tempdb
MODIFY FILE (NAME = ''' + @logicalName
+ ''', FILENAME = ''' + @Path + '\'
+ REVERSE(SUBSTRING(REVERSE(@physicalName), 1, CHARINDEX('\', REVERSE(@physicalName))-1))
+ ''');'
PRINT @SQLString
EXEC sp_executesql @SQLString

FETCH fnc INTO @logicalName, @physicalName;

END;

CLOSE fnc;

DEALLOCATE fnc;

RDP cert manual changing

Добавить новый сертификат в Personal Store LocalMachine

Находим отпечаток нового сертификата

Get-Childitem Cert:\LocalMachine\My

выполняем 

wmic /namespace:\\root\CIMV2\TerminalServices PATH Win32_TSGeneralSetting Set SSLCertificateSHA1Hash="полученный_отпечаток_нового_сертификата"

проверяем, что всё хорошо

Get-WmiObject -Class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices | select SSLCertificateSHA1Hash

net stop SessionEnv

net start SessionEnv

Установка Confluence на Debian 11 + MSSQL (Windows 2019)

SQL

Установка Windows и SQL 

Эти действия проводятся в лучших традициях и настройках по мануалам Best Practice. 

Server Collation для MS SQL по требованиям Atlassian - Latin1_General_CI_AI.
Не забываем поставить SSMS.

Создаём пользователя MSSQL

USE [master]

GO

MSSQL Agent XPs

 sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Bulk change ownership in MSSQL

For Jobs

USE msdb ;  
GO  
EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'old_account',  
    @new_owner_login_name = N'new_account' ;  

GO  

For Databases

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN USE [?]
    exec sp_changedbowner ''sa'' 
END' 

EXEC sp_MSforeachdb @command 

Windows+Office KMS on Debian 11

 Установка

apt-get update && apt-get upgrade -y
apt-get install -y git gcc make net-tools mc

cd ~
git clone https://github.com/Wind4/vlmcsd

MS SQL total memory usage

 select

(physical_memory_in_use_kb/1024) Physical_Memory_usedby_Sqlserver_MB,

(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,--RAM+ Pagefile

process_physical_memory_low,

process_virtual_memory_low

from sys. dm_os_process_memory

MS SQL размеры таблиц в БД

 SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Backup SQLExpress databases

SQL_batch.sql

declare @dbname varchar(50);
declare @sql varchar(max);
declare @path varchar(50);
set @path = 'F:\Backup.SQL\Backups\'
declare mycur cursor for select name  from sys.databases where database_id>4
open mycur
fetch next from mycur into @dbname;
while @@FETCH_STATUS =0 
begin
set @sql = 'backup database ['+@dbname+'] to disk='''+@path+format( getdate(), 'yyyyMMdd-hhmm')+'-'+@dbname+'.bak'';';
print @sql
exec(@sql);
fetch next from mycur into @dbname;
end
close mycur;
deallocate mycur;

CMD file

sqlcmd -E -S .\sqlexpress -i F:\Backup.SQL\Maintenance\SQL_batch.sql

forFiles /p "F:\Backup.SQL\Backups" /s /d -30 /c "cmd /c del @file"

Swap into file

In this example, we will create and activate 1G of swap. To create a bigger swap, replace 1G with the size of the desired swap space.
First create a file which will be used for swap:
sudo fallocate -l 1G /swapfile
If fallocate is not installed or you get an error message saying fallocate failed: Operation not supported you can use the following command to create the swap file:
sudo dd if=/dev/zero of=/swapfile bs=1024 count=1048576
Only the root user should be able to read and write to the swap file. Issue the command below to set the correct permissions :
sudo chmod 600 /swapfile
Use the mkswap tool to set up a Linux swap area on the file:
sudo mkswap /swapfile
Activate the swap file:
sudo swapon /swapfile
To make the change permanent open the /etc/fstab file:
sudo nano /etc/fstab
and paste the following line:
/swapfile swap swap defaults 0 0
Verify whether the swap is active using either the swapon or free command as shown below:
sudo free -h
sudo swapon --show
NAME      TYPE  SIZE   USED PRIO
/swapfile file 1024M 507.4M   -1Copy

sudo fallocate -l 1G /swapfile

sudo chmod 600 /swapfile

sudo mkswap /swapfile

sudo swapon /swapfile

mcedit /etc/fstab

/swapfile  none            swap    sw              0       0

Windows Server 2012 RDS deployment “The server has reboots pending and needs to be restarted.”

Solution #1
Reboot the server
Open Command Prompt then type “shutdown /r” and wait for the server to reboot.
Retry deployment, if the deployment fails, move to Solution #2
Solution #2
This solution requires editing the registry of the Windows 2012 Server.
Run “regedit” to open the Registry Editor
Navigate to: “HKLM\System\CurrentControlSet\Control\Session Manager
If PendingFileRenameOperations exists,  DELETE the key and reboot the server.
After reboot, you will be able to finish the deployment.

AttachAllDatabasesInDIR

 #------------------------------------------------------------------------------------------------------ 
# Name:            Attach-AllDatabasesInDir 
# Description:     This script will attach all the databases in a directory to the local SQL Instance 
# Usage:        Run the function with the -DatabaseDir parameter 
# By:             Ivan Josipovic, softlanding.ca 
#------------------------------------------------------------------------------------------------------ 
#Do not modify below here 
 
function Attach-AllDatabasesInDir{ 
param( 
[string]$DatabaseDir = $(throw "-DatabaseDir `"C:\ExampleDir`" is required.") 
) 
#Load the SQL Assembly 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null 
#Connect to the local SQL Server Instance, change the (local) parameter if needed 
$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") 
if ($server -eq $null){ 
    Write-host -foreground red "Unable to connect to the SQL Server" 
    return -1 
} 
$items = get-childitem $DatabaseDir *.mdf 
 
foreach ($item in $items){ 
    [bool]$ErrorExists = $false 
    $Item.name 
 
    try    { 
        $DBName = $server.DetachedDatabaseInfo($($item.fullname)).rows[0].value 
    }  
    catch { 
        Write-host -foregroundcolor red "File was not able to be read. It is most likely already mounted or in use by another application" 
        $ErrorExists = $true 
    } 
 
    if ($ErrorExists -eq $false){ 
        foreach ($db in $server.databases){ 
            if ($db.name.Equals($DBName)){ 
                write-host -foreground red "This database already exists on the server" 
                $ErrorExists = $true 
            } 
        } 
        if ($ErrorExists -eq $false){ 
            $DbLocation = new-object System.Collections.Specialized.StringCollection 
            $DbLocation.Add($item.fullname) 
            $attach = $server.AttachDatabase($DBName, $DbLocation) 
        } 
    } 
} 
return 
} 
#------------------------------------------------------------------------------------------------------ 
 
Attach-AllDatabasesInDir -DatabaseDir "E:\MSSQL\DATA"

SQL shrink logfiles

DECLARE @dbname VARCHAR(50)

DECLARE @fname VARCHAR(50)

DECLARE @dbid tinyint

DECLARE @lsize numeric(10,5)

DECLARE @TSQLExec VARCHAR (MAX) = '';

declare  db_cur cursor for

SELECT 

    db.name DBName, fs.name FName, db.database_id,

    fs.SIZE * 8.0 / 1024 LogFileSizeMB

FROM sys.databases db

left join sys.master_files fs on fs.database_id = db.database_id

WHERE db.database_id > 4 and db.state=0 and fs.type=1 and db.is_read_only=0

open db_cur

FETCH NEXT FROM db_cur  INTO @dbname , @fname, @dbid ,@lsize

WHILE @@FETCH_STATUS = 0  

BEGIN  

      set @TSQLExec = concat('use [', @dbname,'];CHECKPOINT;','dbcc shrinkfile(N''',@fname,''',64) with no_infomsgs;'); --, Char (13),Char (10)

  set @TSQLExec = concat(@TSQLExec,'ALTER DATABASE [',@dbname,'] MODIFY FILE ( NAME = N''',@fname,''', FILEGROWTH = 64MB, MAXSIZE = UNLIMITED );');

  --set @TSQLExec = concat(@TSQLExec,'ALTER DATABASE [',@dbname,'] MODIFY FILE ( NAME = N''',@fname,''', SIZE = 16MB);');

  

  print @TSQLExec;

  --EXEC (@TSQLExec);

      FETCH NEXT FROM db_cur  INTO @dbname , @fname, @dbid ,@lsize

END 

close db_cur

deallocate db_cur