Proxmox storage tips/HDD pass through

Check/list hdd hw

ls /dev/disk/by-id/
lsblk

Check MD raid status

cat /proc/mdstat

Remove a hdd from software raid md0

mdadm –fail /dev/md0 /dev/sdx1
mdadm –remove /dev/md0 /dev/sdx1

mdadm –remove /dev/md0 /dev/sdx2

udevadm info –query=all –name=/dev/sdx | grep ID_SERIAL

zfs list
zpool status zfstorage

Remove a hdd from zpool

zpool detach zfstorage /dev/disk/by-id/….
zpool status zfstorage

clear data

mdadm –misc –zero-supperblock /dev/sdx1
mdadm –misc –zero-supperblock /dev/sdx2
wipefs -a /dev/sdx

clear all left part on sdx

fdisk /dev/sdx
wipefs -a /dev/sdx

check disk status

lsblk

Pass through sdx to proxmox vm

First get hdd ID via ls /dev/disk/by-id/

ls /dev/disk/by-id/
ata-SAMSUNG_HD163GJ_S2FXJ9CZ901590
ata-SAMSUNG_HD163GJ_S2FXJ9CZ901590-part1
ata-TOSHIBA_DT01ACA100_3760GKPMS
ata-TOSHIBA_DT01ACA100_3760GKPMS-part1
ata-TOSHIBA_DT01ACA100_3760GKPMS-part2
ata-TOSHIBA_DT01ACA100_3760GKPMS-part3
dm-name-pve-root
dm-name-pve-swap
dm-name-pve-vm–100–disk–0
dm-name-pve-vm–120–disk–0
dm-uuid-LVM-jsdRy3n7YEN9lUjDu7eJrm2gDt4Rglc6b3YrMsfnXrYnge2iBD83bRQYJsa69nUR
dm-uuid-LVM-jsdRy3n7YEN9lUjDu7eJrm2gDt4Rglc6cqZGFVeXAN3LtZRd8Zn6YIFKfnBeoNcP
dm-uuid-LVM-jsdRy3n7YEN9lUjDu7eJrm2gDt4Rglc6fM0HYcbqNOaaxUyeJ1czczSJmMz52cfS
dm-uuid-LVM-jsdRy3n7YEN9lUjDu7eJrm2gDt4Rglc6sAQBR4OvFDoNpE2dePEveFWcg5tfLORK
lvm-pv-uuid-sKwOBA-sEun-ifF5-Mv78-xRbX-fRSu-m3LGbF
wwn-0x5000039fefc036b2
wwn-0x5000039fefc036b2-part1
wwn-0x5000039fefc036b2-part2
wwn-0x5000039fefc036b2-part3
wwn-0x50024e92034cfe22
wwn-0x50024e92034cfe22-part1

Prepare a HDD to pass through

fdisk /dev/sdx
lsblk
wipefs -a /dev/sdx

Get list/ID of KVM VM

qm list

  VMID NAME                 STATUS     MEM(MB)    BOOTDISK(GB) PID
   106 server              stopped    2512               0.00 0

Pass through sdx to proxmox vm

qm set 106 -virtio0 /dev/disk/by-id/ata-SAMSUNG_HD163GJ_S2FXJ9CZ901590

https://www.youtube.com/watch?reload=9&v=Qumdkf3etGU

Adding a second HDD to Proxmox

https://forum.level1techs.com/t/adding-a-second-hdd-to-proxmox/116019

This really is an odd failing that has been around since Proxmox moved to their new interface. So here are three ways you can do this, all of which involve ssh’ing into your Proxmox box. Assuming /dev/sdb is your new disk.

If you want to use LVM.
ssh into your Proxmox box and do,

pvcreate /dev/sdb
vgcreate myvolgroup /dev/sdb

At this point you can go into the Proxmox GUI, Datacenter -> Storage -> Add: LVM, and you will see your volume group show up in the drop down menu.

If you want to use ZFS (my own personal recommendation).
ssh into your Proxmox box and do,

zpool create -f myzpool /dev/sdb

At this point you can go into the Proxmox GUI, Datacenter -> Storage -> Add: ZFS, and you will see your zpool in the ZFS Pool drop down menu.

If you want to just straight up use disk partitions 1995 style
ssh into your Proxmox box and do,

cfdisk /dev/sdb
Follow the instructions to use cfdisk to create a partition on /dev/sdb so you get /dev/sdb1. Make sure to write your changes!
mkfs.ext4 /dev/sdb1
mkdir /mnt/myseconddisk
ls -lah /dev/disk/by-uuid/
Locate sdb1’s UUID and copy it
Edit /etc/fstab and add an entry similar to,
UUID=yourlonguuidhere /mnt/myseconddisk ext4 defaults 0 0
Write your changes to /etc/fstab and then,
mount -a

At this point /dev/sdb1 should be mounted to /mnt/myseconddisk, and will remount automatically on reboot. Now go into your Proxmox GUI, Datacenter -> Storage -> Add: Directory. You will want to put /mnt/myseconddisk into the Directory text box, and you will now be able to put the stuff you choose onto this disk.

SQL SERVER – Unable to Start SQL When Service Account is Removed From Local Administrators Group. Why?

https://blog.sqlauthority.com/2017/07/12/sql-server-unable-start-sql-service-account-removed-local-administrators-group/

One of my clients wanted to secure their SQL Server. One of the rules was that wanted to avoid running SQL Server service with an account which is part of local “Administrators” group. They wanted to provide the minimal permissions needed for the SQL server service account for SQL Server to function.

Below are the respective articles for each version that outline the permissions assigned to the service accounts:

Configure Windows Service Accounts and Permissions (SQL Server 2016 and later)
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions

Configure Windows Service Accounts and Permissions (SQL Server 2014)
https://msdn.microsoft.com/en-US/library/ms143504(SQL.120).aspx#Windows

Configure Windows Service Accounts and Permissions (SQL Server 2012)
https://msdn.microsoft.com/en-US/library/ms143504(SQL.110).aspx#Windows

Setting Up Windows Service Accounts (SQL Server 2008 R2)
http://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx#Review_NT_rights

Setting Up Windows Service Accounts (SQL Server 2008)
http://msdn.microsoft.com/en-us/library/ms143504(v=sql.100).aspx#Review_NT_rights

Setting Up Windows Service Accounts (SQL Server 2005)
http://msdn.microsoft.com/en-us/library/ms143504(v=sql.90).aspx#Review_NT_rights

When I spoke to my client, they already removed service account from the Administrators group. As per article, we have permissions in security policy, but still we were not able to start SQL Service. By looking further into the Event Viewer, under Application Log we see the error :

“initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.)”.

SQL SERVER – Event 17058 – initerrlog: Could not Open Error Log File

If we provide incorrect path, we generally see below message.

initerrlog: Could not open error log file ‘C:\BadPath\Errorlog.txt’. Operating system error = 3(The system cannot find the path specified.).”

Notice the blank path in an error message.  Later, we used one of my favorite tools called as Process Monitor and ran the tool to look for registry read activity. We knew that the service was queried for the for the start-up parameters which are saved in the registry and we wanted to check how it does it. We saw we were getting Access Denied for the Account: Americas\SAPPROD_SQLSvc on the registry hive:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\Parameters

Seeing that, we went to the registry hive and checked if the service account had permission and found that the user did not have permission, we also saw that the there was a group, SQLServerMSSQLUser$MPNA01$MSSQLSERVER

WORKAORUND/SOLUTION

We were able to fix the issue by adding Service Account Americas\SAPPROD_SQLSvc to the group SQLServerMSSQLUser$MPNA01$MSSQLSERVER

Reference: Pinal Dave (https://blog.sqlauthority.com)

How to change the default MSSQL directory for database files?

https://support.plesk.com/hc/en-us/articles/115002175809-How-to-change-the-default-MSSQL-directory-for-database-files-


Question
The default directory for storing database files of MS SQL is changed in SQL Management Studio > Database Settings > Database default locations to D:\MSSQL\DATA .
However, new database files are being created and stored in %plesk_dir%\Databases\MSSQL\MSSQLXXX.MSSQLSERVER\MSSQL\DATAanyway.
How to change the default directory for MS SQL?
How to move SQL databases to other disk drive?
Answer
By default, the location for new databases in SQL is defined by the location of the  master.mdf database file, this database is to be moved to the desired path (e.g, D:\MSSQL\DATA) and SQL server is to be reconfigured accordingly:

  • Connect to the server via RDP;
  • Open SQL Server Configuration Manager.
  • Expand Services;
  • Click SQL Server;
  • In the results pane, right-click the named instance of SQL Server, and then click Stop
  • A red box on the icon next to the server name and on the toolbar indicates that the server stopped successfully;

  • Move master.mdf and mastlog.ldf files from %plesk_dir%\Databases\MSSQL\MSSQLXXX.MSSQLSERVERXXX\MSSQL\DATA to D:\MSSQL\DATA.Note: XXX should be replaced by the actual path, it depends on the version of the MS SQL server;
  • Set Full Control permissions for the MS SQL service account, for an example NT Service\MSSQL$MSSQLSERVERXXXX, to the new DATA directory. Detailed steps are described here.
  • In SQL Server Configuration Manager > right-click SQL Server > Properties > Advanced > Startup Parameters specify new paths for the master database:-dD:\MSSQL\DATA\master.mdf;
-eC:\Program Files (x86)\Parallels\Plesk\Databases\MSSQL\MSSQLXXX.MSSQLSERVERXXX\MSSQL\Log\ERRORLOG;
-lD:\MSSQL\DATA\mastlog.ldf
  • Start MS SQL Server service by right-clicking on the corresponding SQL Server > Start.

How to make a transparent background/selection on an image using GIMP

  1. Open your image.
  2. Select the area you want to make transparent.
    Select the appropriate selection tool from the Tool window or the Tools – Selection Tools menu on the Layer window. I usually use the magic wand/fuzzy select (Select contiguous region) tool or the Select regions by color tool.
    Click in the region you want selected. Use shift-click to add more regions/colors.Tip: It’s easier if you zoom in (View – Zoom menu) to see finer details of exactly what you’re selecting.
  3. In the Layer window (the one showing your image), select Layer – Transparency – Add Alpha Channel.If this is blanked out then it’s already done. This makes sure your image can store transparency data
  4. Select Edit – Clear. This makes the selection transparent.
  5. Save the file. Note: If you save it as a PNG file, be sure to select the ‘Save colour values from transparent pixels’ option in the Save as PNG dialog box.

Check MSSQL status


CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
— Add any filtering of the results here :
WHERE       DBName <> ‘master’
— Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2

Source: https://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx