datablogs

Friday, February 24, 2023

How to Automate MongoDB Database Backups in Linux

Selva | February 24, 2023 | No comments | Automation, Backup, Database Administration, MongoDB, NoSQL

We have setup of One Primary with Multiple Secondary

Even if we configured highly available setup and backups , native backup are so special to take it and keep it somewhere in the cloud

using the below script we can easily schedule backup in Linux environments

https://github.com/selvackp/MongoNativeBackup-/blob/main/mongo_dump.sh

export PATH=/bin:/usr/bin:/usr/local/bin
#Decalre Today Date
TODAY=`date +"%d%b%Y"`

#Declare Variables Required to pass for mongo dump command
DB_BACKUP_PATH='/mnt/mongobackup'
MONGO_HOST='localhost'
MONGO_PORT='27017'
MONGO_USER='xxxxxxxxxxx'
MONGO_PASSWD='xxxxxxxxxxxxx'
DATABASE_NAMES='ALL'

#Remove Old Backup Files 
find ${DB_BACKUP_PATH} -name "*.zip" -type f -mtime +3 -delete

find ${DB_BACKUP_PATH} -type d -mtime +3 -exec rm -rf {} \;

#Create Directory for Backup
mkdir -p ${DB_BACKUP_PATH}/${TODAY}
cd ${DB_BACKUP_PATH}/${TODAY}/

if [ ${DATABASE_NAMES} = "ALL" ]; then
	echo "You have choose to backup all database"
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}"
else
	echo "Running backup for selected databases"
	for DB_NAME in ${DATABASE_NAMES}
	do
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}/${DB_NAME}"
	done
fi

#Compress The Backup
cd ${DB_BACKUP_PATH}/${TODAY}

zip -r ${DB_BACKUP_PATH}_${TODAY}.zip ${DB_BACKUP_PATH}/${TODAY}

cd ${DB_BACKUP_PATH}/${TODAY}

#Copy the Compressed file into Azure Container using Shared Access Token
azcopy cp ${DB_BACKUP_PATH}_${TODAY}.zip "https://xxxxxxxxxxx.blob.core.windows.net/xxxxxxxxxxxx?sp=w&st=xxxxxTxxxxxxxZ&se=xxxxxxZ&spr=https&sv=2021-06-08&sr=c&sig=csdfcdsxxxxxxxxxxxxxxx" --recursive=true

#Send Mail with Backup Logs 
if [ $? -ne 0 ]
then
        echo "Mongo Native backup Failed in $(hostname) $(date). Please contact administrator." | mail -r mail@datablogs.com -s "Mongo Native backup Failed $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log

        else

        echo "Mongo Native backup completed in $(hostname)." | mail -r mail@datablogs.com -s "Mongo Native backup completed in $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log
fi

Friday, February 17, 2023

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

Selva | February 17, 2023 | No comments | Database Administration, medium, MongoDB, NoSQL, percona, Point In Time Restore

Its easy to recover MongoDB Backup using Percona Backup for MongoDB

https://medium.com/@datablogs/restore-and-point-in-time-restore-with-percona-backup-of-mongodb-on-another-instance-4766f4468443

How to tune long running Queries on Azure Synapse DW (Dedicated Pool) - Part 2

Selva | February 17, 2023 | No comments | Azure Cloud, Azure Synapse Analytics, Database Administration, Performance Tunning, SQL Server

Its took little long time to derive our approach tunning on azure data factory with Azure Synapse why because we need to run the system atleast two months validate our approach is smooth

Yes its all running good as expected performance on the ETL loads and Processes

Here are the major things we need to take care on Azure Synapse Dedicated Pool ,

Dedicated SQL Pool Scaling
Dedicated SQL Pool Workload Management

Dedicated SQL Pool Scaling :

We have decide below metrices to optimize the Azure Synapse

Data Load and Processing timelines
Critical Process running timelines
Minimal Processing timelines
Reporting and Visualtions Timelines

Based on above requirements , we can easily scalable the Dedicated SQL Pool based on the load

Consider we need to load the data and process with in 2 hours , we can upscale the Dedicated SQL Pool to as like we want

Key Part to increase the DW is concurrency , Instance Capacity and allocated resource for each queries

Before start our critical process , we can automate upscale process with ADF Pipelines itself . So many blogs available to configure that

Best method configure the authentication method with service principle ,

Dedicated SQL Pool Workload Management :

We have decide below metrices to prepare workload management

Short Queries
Critical Queries
High Intensive Queries
Reporting Queries

Based on the classifications , we have to split workload group for above queries

Step 1 :

We need to create login and user for workload management in Dedicated SQL Pool

--CREATE LOGIN [Analyticsprocess] WITH PASSWORD='xxxxxxxxx'
--CREATE USER [Analyticsprocess] FOR LOGIN [Analyticsprocess]
--GRANT CONTROL ON DATABASE::[sql-datablogs-dw] TO
Analyticsprocess

Step 2 :

Consider you have upscaled instance into DW400c below are the resources allocation for the DW400c instance concurrency requirements

In the workload group --> New workload group --> Click ELT

Consider analytics process user is used for high intensive queries we have to allocate as much as minimum resource for workload group

Click Classifiers --> Add Classifiers --> Name it as ELT --> and specify Member should be the login and Label is important to mention

Once click Add , we will get below concurrency range based on DW400c

By Default , its having system level workload group to handle the queries but its not effective we have to force our workload group

Step 3 :

This is very important to utilize the workload group properly . We need to specify Label on the heavy processing queries so that it will utilized properly

CREATE TABLE rpt_datablogs_finalreport.smgreport WITH (HEAP,DISTRIBUTION = REPLICATE) AS select * into rpt_datablogs_finalreport.smgreport_vw from rpt_datablogs_finalreport.vw_smgreport OPTION (LABEL='highintensiveprocess')