Author: Abhishek Yadav
-
Azure Azure SQL Database DBA Worries Continued High Availability Installation and Configuration SQL Server
Cloud Witness in Azure
Cloud Witness is a type of Fail-over Cluster quorum witness that uses Microsoft Azure to provide a vote on cluster quorum. There are significant benefits which this approach: Problem with traditional cluster setup Lets consider a typical cluster setup which is shown in below diagram. Here we have file share witness in primary DC and unfortunately…
//
-
Provisioning a SQL Managed Instance Using the Azure Portal
Azure SQL Managed Instance is intelligent, scalable cloud database service. SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. To create a SQL Managed Instance, follow these steps: 1. Log in to https://portal.azure.com using your Azure credentials. 2. In the top search box, type SQL Managed…
//
-
Connect to SQL Managed Instance from an on-premises client computer by using a point-to-site connection
In this section, you will learn how to connect SQL managed instance from an on-premises client computer by using a “point-to-site” connection. Point-to-Site VPN connections are useful when you want to connect to your VNet from a remote location. Prerequisites: Follow below sequence steps by step to connect SQL Managed Instance from an on-premises client machine (wherein will…
//
-
DBCC (Database Console Command) Commands
It’s a common question of DBA interview. I already have faced millions of time. HaHa Ha. DBCC is a series of statements in Transact-SQL programming language to check the physical and logical consistency of SQL Server database. We can divide in 4 major category or type… DBCC command type… 1. Informational DBCC command 2. Validational DBCC command 3. Maintenance…
//
-
Export one table from one MySQL instance to other MySQL instance
Syntax Export one table: $ mysqldump -u root -p database_name table_name > dump.txt password ***** Import one table: $ mysqldump -u root -p database_name table_name < dump.txt password ***** Below was the actual request… Please copy MySQL table charge_master from clt-mysqlP_11.b_Rore to clt-mysqlD_61.SMS Step by step implementation… –Connect MySQL on clt-mysqlP_11 Step 1. Take the row count. mysql> select count(*) from…
//
-
InnoDB Architecture
Buffer Pool In MySQL, buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. For efficiency…
//
-
PostgreSQL Backup
pg_basebackup is used to take base backups of a running PostgreSQL database cluster. We can take hot-backup and the backup can be used for… 1. point-in-time recovery as the starting point for a log shipping or 2. streaming replication standby servers It makes a binary copy of the database cluster files, while making sure the system is…
//
-
Streaming Replication Step by step notes
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘******’; echo “host replication replicator 192.168.0.126/32 md5” >> pg_hba.conf \ /u01/app/postgres/bin/psql -c “select pg_reload_conf()” /u01/app/postgres/pgsql/bin/pg_basebackup -h 192.168.0.117 -U replicator -p 5432 -D /u05/pgsql/database -Fp -Xs -P -R NOTE:- In my case, I have installed PostgreSQL (PostgreSQL 12) in /u01/app/postgres/pgsql/bin/ directory and data directory is /u05/pgsql/database
//
-
Table Level Replication in MySQL on Windows OS
Hello guys, Hope you are going great. In this article, we are going to talk about configuring MySQL replication between two servers. Here there will be one master and one slave server. It’s simple setup there are basically 6 major steps which are below… Step 1 –> ============================================================================ /*Take dump of the tables which we…
//
-
CAP Theorem
In theoretical computer science, the CAP theorem, also named Brewer’s theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees: Consistency Every read receives the most recent write or an error. Availability Every request receives a (non-error)…
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
//