Sarvani sweets MSSQL backup procedures

Backup process on Sarvani sweets servers:

Prerequisites

MSSQL server: MSSQL-2022
SQL server management studio: SSMS
Python 3.13: Python 3.13
- Run the installer and install the application for all users and tick the add path to env variables
pyodbc driver: pip install pyodbc
Create required directories on both servers: mkdir C:\\mssql_db_backups, mkdir C:\\scripts
Create domain user on live server node:
Step-1: Go to Windows Administrative Tools from the Start menu and click on Active Directory Administrative center, on the new window.

Windows Admin center

Click on the sarvanisweets option from the left toolbar and double-click the Users folder, on the new window as below:

Create user window

Use the below options to fill in the details and click on OK to conform.

User details

Enable RDP for the now created user:

  • Go to Settings -> Remote Desktop -> Ensure Remote desktop toggle is enabled, should be same as the below image: Remote desktop settings page

  • On the same windows, click on the setting Select users that can remotely access this PC and make sure to include the user SARVANISWEETS\devopsadmin by click on add button and adding SERVANISWEETS\devopsadmin and save the configuration.

Grant permissions to devopsadmin user

  • Login to Administrator user and Open SSMS and connect to database engine using Windows Authentication(default). And click on the Security folder then Logins folder from the Object Explorer and then double click the SARVANISWEETS\devopsadmin object, a windows similar to the below will appear:

Login object window

On this new window tick all the options as shown in the windows above.

SSH setup on live and backup nodes:

Step-1: Go to Settings -> Apps & features -> Optional features -> Add a feature and search for OpenSSH server and select install.

Step-2: Start the ssh service: Open a powershell using administrator rights and enter the following command:

Start-Service sshd

The command above shouldn’t throw errors

Verify service using:

netstat -an | findstr ":22"

Sample output:

TCP    0.0.0.0:22             0.0.0.0:0              LISTENING
TCP    91.203.132.5:22        219.91.206.250:54533   ESTABLISHED
TCP    [::]:22                [::]:0                 LISTENING

If ssh didn’t start, start the service using GUI by: Use (CMD+r) or (Windows+r) keys and enter services.msc and click enter and on the now opened window: Scroll down to OpenSSH SSH Server and right click to view start option and click on it and verify again. services_windows

Complete the above steps for both backup and live servers.

Configure SSH

On backup node:

Step-1:

After ensuring the service has started, configure SSH service parameters in C:\ProgramData\ssh\ssd_config by changing the file contents to:

For backup server:

# This is the sshd server system-wide configuration file.  See
# sshd_config(5) for more information.

# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented.  Uncommented options override the
# default value.

#Port 22
#AddressFamily any
#ListenAddress 0.0.0.0
#ListenAddress ::

#HostKey __PROGRAMDATA__/ssh/ssh_host_rsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_dsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_ecdsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_ed25519_key

# Ciphers and keying
#RekeyLimit default none

# Logging
#SyslogFacility AUTH
#LogLevel INFO

#SyslogFacility LOCAL0
#LogLevel Debug3

# Authentication:

AllowUsers SARVANISWEETS\devopsadmin

#LoginGraceTime 2m
#PermitRootLogin prohibit-password
#StrictModes yes
#MaxAuthTries 6
#MaxSessions 10

PubkeyAuthentication yes

# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2
# but this is overridden so installations will only check .ssh/authorized_keys
AuthorizedKeysFile	.ssh/authorized_keys

#AuthorizedPrincipalsFile none

# For this to work you will also need host keys in %programData%/ssh/ssh_known_hosts
#HostbasedAuthentication no
# Change to yes if you don't trust ~/.ssh/known_hosts for
# HostbasedAuthentication
#IgnoreUserKnownHosts no
# Don't read the user's ~/.rhosts and ~/.shosts files
#IgnoreRhosts yes

# To disable tunneled clear text passwords, change to no here!
PasswordAuthentication no
#PermitEmptyPasswords no

# GSSAPI options
#GSSAPIAuthentication no

#AllowAgentForwarding yes
#AllowTcpForwarding yes
#GatewayPorts no
#PermitTTY yes
#PrintMotd yes
#PrintLastLog yes
#TCPKeepAlive yes
#UseLogin no
#PermitUserEnvironment no
#ClientAliveInterval 0
#ClientAliveCountMax 3
#UseDNS no
#PidFile /var/run/sshd.pid
#MaxStartups 10:30:100
#PermitTunnel no
#ChrootDirectory none
#VersionAddendum none

# no default banner path
#Banner none

# override default of no subsystems
Subsystem	sftp	sftp-server.exe

# Example of overriding settings on a per-user basis
#Match User anoncvs
#	AllowTcpForwarding no
#	PermitTTY no
#	ForceCommand cvs server

Match Group administrators
       AuthorizedKeysFile __PROGRAMDATA__/ssh/administrators_authorized_keys

This configuration makes the ssh service to not accept connections using password based authentication and restricts to use only key based authentication.

For live server:

# This is the sshd server system-wide configuration file.  See
# sshd_config(5) for more information.

# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented.  Uncommented options override the
# default value.

#Port 22
#AddressFamily any
#ListenAddress 0.0.0.0
#ListenAddress ::

#HostKey __PROGRAMDATA__/ssh/ssh_host_rsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_dsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_ecdsa_key
#HostKey __PROGRAMDATA__/ssh/ssh_host_ed25519_key

# Ciphers and keying
#RekeyLimit default none

# Logging
#SyslogFacility AUTH
#LogLevel INFO

#SyslogFacility LOCAL0
#LogLevel Debug3

# Authentication:

AllowUsers SARVANISWEETS\devopsadmin

#LoginGraceTime 2m
#PermitRootLogin prohibit-password
#StrictModes yes
#MaxAuthTries 6
#MaxSessions 10

PubkeyAuthentication yes

# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2
# but this is overridden so installations will only check .ssh/authorized_keys
AuthorizedKeysFile	.ssh/authorized_keys

#AuthorizedPrincipalsFile none

# For this to work you will also need host keys in %programData%/ssh/ssh_known_hosts
#HostbasedAuthentication no
# Change to yes if you don't trust ~/.ssh/known_hosts for
# HostbasedAuthentication
#IgnoreUserKnownHosts no
# Don't read the user's ~/.rhosts and ~/.shosts files
#IgnoreRhosts yes

# To disable tunneled clear text passwords, change to no here!
PasswordAuthentication no
#PermitEmptyPasswords no

# GSSAPI options
#GSSAPIAuthentication no

#AllowAgentForwarding yes
#AllowTcpForwarding yes
#GatewayPorts no
#PermitTTY yes
#PrintMotd yes
#PrintLastLog yes
#TCPKeepAlive yes
#UseLogin no
#PermitUserEnvironment no
#ClientAliveInterval 0
#ClientAliveCountMax 3
#UseDNS no
#PidFile /var/run/sshd.pid
#MaxStartups 10:30:100
#PermitTunnel no
#ChrootDirectory none
#VersionAddendum none

# no default banner path
#Banner none

# override default of no subsystems
Subsystem	sftp	sftp-server.exe

# Example of overriding settings on a per-user basis
#Match User anoncvs
#	AllowTcpForwarding no
#	PermitTTY no
#	ForceCommand cvs server

Match Group administrators
       AuthorizedKeysFile __PROGRAMDATA__/ssh/administrators_authorized_keys

Step-2:

  1. Generate a common ssh key for both backup and live servers from the jenkins node:

    • Create ssh keys directory and configure its permissions:
    mkdir -p /var/lib/jenkins/ssh-keys
chown -R /var/lib/jenkins/ssh-keys
    • Log on to the jenkins server and enter the following command and save the file to : /var/lib/jenkins/ssh-keys/jenkins_ss
    • Generate the key-pairz using:
    ssh-keygen -t ed25519

    While asked to where to save the files, type in: /var/lib/jenkins/ssh-keys/jenkins_ss

    • Copy the generated public key using /var/lib/jenkins/ssh-keys/jenkins_ss.pub and proceed to next steps.

Repeat Step-3 on both nodes(live and backup)

Step-3: - Create a administrators_authorized_keys file in the directory: C:\ProgramData\ssh\administrators_authorized_keys and enter the above copied public key and save the file. - Add the backup node’s public key to the live node in the C:\ProgramData\ssh\administrators_authorized_keys, Copy the host ssh key and paste on the file: C:\ProgramData\ssh\administrators_authorized_keys on live node. Copy the contents of C:\ProgramData\ssh\ssh_host_ed25519 and follow the below steps on how to paste and modify the file’s permissions. - Change the permissions fo the administrators_authorized_keys file using: Open powershell using administrative permissions and use:

```
cd C:\ProgramData\ssh
icacls administrators_authorized_keys /inheritance:r
icacls administrators_authorized_keys /remove "NT AUTHORITY\Authenticated Users"
icacls administrators_authorized_keys /grant SYSTEM:F
icacls administrators_authorized_keys /grant BUILTIN\Administrators:F
icacls administrators_authorized_keys /grant "CREATOR OWNER":F
icacls administrators_authorized_keys
```

Setup scripts on both servers:

Step-1: Download the following backup and live node’s script using the google drive link: https://drive.google.com/drive/folders/18-eYozVOQ_WK8AXOes_vDPIOeyJxrDI4?usp=drive_link and store the backup script at C:\scripts\backup_script_bkp.py and live node’s script at: C:\scripts\backup_script_live.py

Performing operations using the above script: To take backup: python.exe C:\scripts\backup_script_live.py take

To restore backup: python.exe C:\scripts\backup_script_bkp.py restore

To count total rows across the database: python.exe C:\scripts\backup_script_live.py rows

Restore operation on live server script doesn’t work as it is skipped by default for precaution