Copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue

Hi guys, in this blog I am going to explain you about copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue.

Issue-

Client Disk Slowness Issue.

Solution-

These guys set up a 1TB SSD in the server and made partitions from it as well.

In SQL Server, we can copy user and system database files from the old drive to the new drive by using the Robocopy command.

 

Step 1-

• Take the snapshot of the Server.
• Down the SQL Services of the Server.
• Open the SQL server configuration Manager-> SQL Server Services->SQL Server (MSSQLSERVER)->Right Click ->Stop the Services.
• The Agent of the server will immediately quit if you stop the SQL Server Services.

Step 2-

• Next, launch CMD, select Run as Administrator, and use the CMD shell's Robocopy command.

• then create a command to copy files to the new drive from the old one.

Syntax-

Robocopy source destination [files] [options]

 

Example-

Robocopy E:\ Z:\ /E /COPYALL

E:\- Old drive name

Z:\- New drive name

/E- Copies subdirectories, including empty ones.

/COPYALL- Copies all file information, including attributes, timestamps, and security information.

Step 3-

• After the copying process is complete, rename the new drive using the older drive letter. If you don't rename the new drive using the older drive letter, SQL services won't start and an error will appear.

Check the error log for Windows.

• Event Viewer->Windows Logs-> Application-> Check the error

Step 4 –

To avoid this error, rename the new drive letter with older drive letter.

• Server Manager->Tools->Computer Management->Storage-> Disk Management->Right Click on disk->Change drive letter->select change->change the drive letter->click ok.

Step 5-

Start the SQL Server Services.

Step 6-

Once SQL Services is started then Start the Agent also of the server.

Step 7-

Now Check the SQL error logs and windows error logs of the Server.

To Check the SQL Error Logs-

• Connect to server through SSMS ->Management->SQL Error Logs->check the current error logs.

To check the Windows Error Logs-

• Login on the Server through RDP -> Event Viewer->Window logs->Application-> check the latest windows error logs.

PostgreSQL Architecture and Back-end Process

Hey guys, in this blog I am going to explain to you about PostgreSQL Architecture and Back-end Process.

PostgreSQL-

PostgreSQL is an open-source general-purpose object-relational database management system that is one of the most advanced. Because it is open-source software, its source code is available under the PostgreSQL license, which is a broad open-source license. Anyone with the necessary knowledge can use, edit, and distribute PostgreSQL in any form. This DBMS requires extremely little work to maintain because of its great stability.

Why should you want to use PostgreSQL-

• Customized types
• Inheritance in tables
• Complex locking mechanism
• Integrity of foreign key references
• Rules, views, and subquery
• Save points transactions that are nested.
• MVCC or multi-version concurrency control
• In-between-time replication
• Native Version of Microsoft Windows Server
• Tablespaces
• Moment-by-moment recovery.  

Why is PostgreSQL special in its own right?

• PostgreSQL introduced the multi-version concurrency control (MVCC) capability first.
• PostgreSQL can be expanded using custom functions created in languages like C/C++, Python, Java, etc.
• One can define their own data types, index types, functional languages, etc. because PostgreSQL is expandable by design.
• One can always create a unique plugin to customize the system to match their own needs if they choose to delete any aspect of it. 

Large Scale users of PostgreSQL-

PostgreSQL has been used by a lot of businesses to create goods and solutions. Apple, Fujitsu, Red Hat, Cisco, Juniper Network, and a few other companies are among them.

PostgreSQL's architecture is client-server. A PostgreSQL service consists of two processes-

• Server-side process
• Client-side process (Front-end applications)

Server-side process-

The "Postgres" programmers manage connections, operations, and static and dynamic assets.

Client-side process (Front-end applications)-

Users utilize these programmers to communicate with the database. It often has a straightforward user interface (UI) and uses APIs to facilitate communication between the user and the database.

Client-side Process –

When a user runs PostgreSQL queries, the client application can communicate with the Postmaster Daemon Process (the PostgreSQL server) and submit queries using one of the many Databases Client Application Programmed Interfaces supported by PostgreSQL, such as JDBC, Perl DBD, ODBC, etc., which helps to provide client-side libraries. As seen in the following diagram, the Client Process uses Library API to facilitate communication between the Client Application and Client Application library.

PostgreSQL System Architecture

Postmaster Daemon Process –

PostgreSQL's system design is built on the Process-Per-Transaction Model (Client/Server Model). Postmaster, a central coordinating procedure, manages a functioning PostgreSQL site. It is sometimes referred to as the Server Process.

The postmaster daemon process is in charge of-

• Setting up the server
• Turning off the server
• Handling new client connection requests.
• Carry out Recovery.
• Execute Background Processes.

Shared Memory-

Shared memory is memory that is accessible by numerous programmers at the same time in order to give fast and efficient results with minimal redundancy. This RAM is set aside for database caching and transactional log caching. In PostgreSQL, shared disc buffers and shared tables are used, and their operation is described below.

Shared Disc Buffer-

The shared disc buffer's aim is to reduce disc input/output. If it is not used, disc input/output takes longer, resulting in redundancy and an inefficient system. The following are the benefits of employing a shared buffer:

• Time should be reduced.
• Can easily access a big amount of data.
• Reduce heating when numerous users access the system at the same time.

Shared Tables-

This method involves using the same set of tables to hold data from numerous clients. The following are the primary benefits of employing this approach:

• The Most Affordable Hardware
• The Most Affordable Backup
• It enables working with massive amounts of data in a single database.

UNIX System-

The Kernel Disc Buffer in UNIX System maintains a Memory buffer and provides physical storage to data in disc storage. Also, PostgreSQL instructions are evaluated to ensure that the syntax is valid and produce an error message indicating what is lacking in the command, etc.

Back-end process-

The Postmaster is in charge of initial client connections. As a known port, it constantly waits for new connections. Following an initialization process, such as user authentication, the postmaster will launch a new backend server process to service the new client. The client solely interacts with the Backend server process, such as query submission and query results. This demonstrates that PostgreSQL employs the Process-per-Transaction model.

The Backend Server is in charge of executing client-submitted queries by conducting particular processes. Each backend server will only handle one query at a time. Many clients are connected to the system at the same time, resulting in many backend servers running queries concurrently. The back-end server accesses data from the shared memory main-memory buffer pool.

The obtained result is then supplied to the Client Process by the Back-end Process.

WAL(Write Ahead Log)Writer	This procedure WAL data is written to and flushed from the WAL buffer.
logging collector	This procedure is also known as logger. An error notice will be written to the log file.
Auto vacuum launcher	When auto vacuum is enabled, this process is in charge of the auto vacuum daemon performing vacuum operations on bloated tables. For perfect table analysis, this process relies on the stats collector process.
Archiver	When Achiever is enabled, the process is in charge of copying the WAL log file to the specified directory.
stats collector	This statistics collects data such as pg_stat_activity and pg_stat_all_tables.
check pointer	The dirty buffer is written to the file when a checkpoint occurs.
writer	It will write the dirty buffer to the file on a regular basis.

Shared Pool-

A shared pool is a RAM region within the RAM Heap that is established at startup. SGA (System Global Area) includes a common pool. If the Shared Pool is not present in RAM or is not used, high library cache reloads and high row cache reloads occur.

Why did PostgreSQL not employ Shared Pool?

PostgreSQL does not have a shared pool, although in most database systems, such as Oracle, the shared pool is a key component of the structure. It does not have it since, unlike Shared Pool, PostgreSQL will give a mechanism to share SQL information at the process level. Simply put, if a user executes the same SQL query multiple times in one process, it will hard-parse only once, which is advantageous over other Database systems because, in another database system that uses a shared pool, hard-parsing occurs for a single SQL statement that is loaded from the Shared pool. More loads will be generated if the user executes a single SQL query numerous times at the same time.

OID in PostgreSQL -

In PostgreSQL, OID stands for Object Identifier types. PostgreSQL uses OID as a Primary Key in several system tables. It's implemented as an unsigned four-byte integer. We can also utilize OID in the user-defined table as "WITH OIDS ", but this is discouraged because it is not large enough to give uniqueness in a large user-defined table. OIDs are typically appropriate for system tables. It essentially provides a constructed id for each row contained in the system column.

The feature of OID for User tables has been removed indirectly in PostgreSQL 12, which means that we can utilize OID explicitly.

 

PostgreSQL Advantages-

• PostgreSQL is a highly risk-tolerant database with low maintenance costs.
• To run dynamic websites and web applications, it employs the LAMP (Linux, Apache, MySQL, PHP) stack.
• The source code for PostgreSQL is publicly available under an open-source license. This gives you the ability to use, adapt, and implement it as needed for your business.
• Postgres requires little training to learn because it is simple to use.
• Geographic objects are supported by PostgreSQL, allowing it to be used for location-based services and geographic information systems.

PostgreSQL Disadvantages-

• It is a little slower than a commercial database.
• In comparison to MYSQL, it does not support many open-source apps.
• Many open-source applications support MySQL, but not PostgreSQL.
• Changes for performance improvement necessitate more work than in MySQL since PostgreSQL focuses on compatibility.

SQL Server's in-memory OLTP technology

Hey guys, In this blog I am going to explain you about SQL Server's in-memory OLTP technology.

Introduction-

A native stored procedure compiler and specialized, memory-optimized relational data management engine are both built into SQL Server as part of In-Memory OLTP. For the most demanding OLTP workloads, Microsoft created In-Memory OLTP. In many instances, the DURABILITY = SCHEMA ONLY option can be used to generate memory-optimized tables in order to completely avoid logging and I/O.

The following principles are introduced by in-memory OLTP:

1. Optimized tables and indexes for in-memory
2. Traditional temporary tables and non-durable tables
3. Memory-optimized table type for table variables and natively built stored procedures - This can take the place of transient objects.

In-Memory OLTP systems' effects-

1. Quicker response time, greater throughput, and low latency
2. High performance
3. extreme performance
4. An optimistic concurrency paradigm, along with improved concurrency management, can result in zero or no lock escalation management.

How is in-memory OLTP recommended?

Consider the possible advantages of switching to in-memory OLTP if the system meets one or more of the following conditions.

1. Applications that currently use SQL Server (or other relational databases) and need to scale up and perform better
2. RDBMS with database bottlenecks, primarily related to locking/latching or code execution

PostgreSQL Index and Types

Hey guys, In this blog I am going to explain you about PostgreSQL Index and Types.

PostgreSQL Index-

The database search engine can use indexes, which are specialized lookup tables, to speed up data retrieval. An index is simply a pointer to information in a table. An index in a database resembles a book's back matter index quite a bit.

For example, you must first consult the index, which lists all themes alphabetically, in order to find the page numbers that correspond to each discussion of a particular topic in the book.

Types of PostgreSQL Index--

There are six main index types in PostgreSQL:

1.      B-tree indexes
2.      Hash indexes
3.     GIN indexes
4.     BRIN
5.     GiST Indexes
6.     SP-GiST Indexes

B-tree indexes-

The B-tree is a self-balancing tree that preserves sorted data and permits logarithmic-time searches, insertions, removals, and sequential access.

Hash indexes-

Only simple equality comparisons (=) can be handled using hash indexes. It indicates that the query planner will take a hash index into account if an indexed column is used in a comparison using the equal(=) operator.

GIN indexes-

Generalized Inverted Indexes are referred to as GIN. It is frequently called GIN. When several values are kept in a single column, such as with the hstore, array, jsonb, and range types, GIN indexes are most helpful.

BRIN indexes-

Block Range Indexes is what BRIN stands for. Comparing BRIN to a B-tree index reveals how much smaller and less expensive it is to maintain.

With B-tree without horizontal partitioning, it was previously impossible to utilize an index on a very big table. A column with a linear sort order, like the generated date column in the sales order table, is frequently used with BRIN.

 GiST Indexes-

GiST, or generalized search tree, is its name. GiST indexes enable the construction of broad tree topologies. GiST indexes are helpful for full-text search and indexing geometric data types.

 SP-GiST Indexes-

Space-partitioned GiST is referred to as SP-GiST. Partitioned search trees are supported by SP-GiST, enabling the creation of a variety of unique non-balanced data structures. The best uses for SP-GiST indexes are for data that naturally clusters and is not a perfectly balanced tree, such as GIS, multimedia, phone routing, and IP routing.

SQL Interview Questions

You can benefit from this article's popular SQL Server DBA interview questions and answers. To learn more about SQL interview questions, I would advise you to read this post as well as the SQL Server Interview questions and answers. I made an effort to be very specific in my responses to each question in this article as well.

What is SQLOS?

The SQL Server Operating System is referred to as SQLOS. The SQL Server Database Engine's lowest layer, SQLOS, is in charge of carrying out crucial internal duties like allocating CPU threads for SQL Server transactions, memory management, deadlock detection, and IO completion activities. For SQL Server operations, it functions exactly like another little operating system.

 

What you know about tempdb?

Temporary user objects that are specifically created are stored in the tempdb system database, which is a global resource. They consist of temporary stored procedures, global or local temporary tables and indexes, table variables, tables returned in table-valued functions, and cursors.

 

What happens if TempDB is full?

The TempDB database is unique in many ways, but one intriguing feature is that when its files expand automatically when they reach capacity, this expansion is not stored and will be undone upon the subsequent restart of the SQL Server service.

Why are database statistics important?

The performance of a query depends on statistics. Without them, the optimiser can only make educated guesses about the most effective combination of data entry points. Every table access becomes equivalent to a table scan. Because of how crucial they are, SQL Server automatically builds them for ad hoc queries.

 

What's the use of File Group in DB?

The principal data file and any secondary files that aren't included in other file groups are both included in the file group. To organise data files for administrative, data allocation, and placement needs, user-defined file groups can be formed.

 

What are pages in SQL Server?

Pages, the fundamental unit of data storage in SQL Server, are conceptually separated into portions of the disc space allotted to a data file. The size of a database page is 8 KB. A series of 8 KB pages in the data file are created each time you put data into a SQL Server database.

What can the possible cause of SQL Server connection issues be?

There are many reasons why you might be unable to connect to the SQL Server instance, including:

1. Be careful you input the correct connection information, including the server name and port number.
2. Make that the computer from which you are connecting to the instance has the SQL Server port open.
3. Ensure that the TCP or named pipe protocols are enabled in the SQL Server configuration manager and that the SQL Server instance is functioning.
4. No SQL browser service is active.

What does the Always On Availability Group's Automatic Seeding mean?

In order to set up the secondary replica, a function called automatic seeding was introduced in SQL Server 2016. Prior to this, there was only one way to backup, copy, and restore databases in order to initialize the secondary replica. When establishing the always-on availability group, we make use of this functionality. Despite the fact that Microsoft has provided this functionality, it is not appropriate for very large databases or if you have numerous databases on your SQL Server instance because it is a single-threaded procedure that degrades performance and can take many hours to setup your secondary replica.

 

Can you elaborate on the Basic availability groups' restrictions?

Database mirroring functionalities are replaced with basic availability groups. It allows us to keep a single secondary copy. When compared to conventional availability groups, its key drawback is listed below.

1. There can only be 2 replicas made. There is one major replica and one secondary replica.
2. Similar to database mirroring, only one database will function as an availability database.
3. Reading transactions, database backups, and other database maintenance tasks cannot be performed on the secondary replica.
4. Distributed availability groups cannot contain basic availability groups.
5. Basic availability groups cannot be upgraded to regular availability groups. They must be removed, and new standard availability groups must be configured.

Can a secondary database in the availability group have database snapshots created?

Yes, databases with the role status of either PRIMARY or SECONDARY can have database snapshots made. The role state of RESOLVING prevents the creation of database snapshots.

 

What effect will the SQL Server Agent job stopping to run have on log shipping?

All of the operations involved in sending logs rely on agent jobs. Log shipping cannot execute backup, copy, or restore operations if SQL Server Agent is suspended, and it may go out of sync if the agent is stopped over an extended period of time.

What distinguishes MS SQL Server from MySQL?

Name	MS SQL Server	MySQL
Define	Microsoft Relational Database Management System	Wide usage of Relational Database Management system
Primary DB Model	Relational DB Management System	Relational DB Management System
Secondary DB Model	Graph DBMS, Document & Value store	Document & Key-Value store
Developer	It is developed by Microsoft	It is developed by Oracle
Server OS	It supports Windows, Linux	It supports Linux, Solar winds, Windows, OS X
Supporting Languages	Java, PHP, Ruby, C#, C++, R, etc	Python, TCL, Scheme, Haskell, JavaScript, Ruby, C, C++, C# few more
FK(Foreign Key)	Yes they support	Yes they support
API's	JDBC, ODBC, OLE DB, TDS	ODBC, ADO.NET, JDBC
License Permit	Only Commercial	Open Source (Free)

What functions do the various replication components serve?

Publisher, Distributor, and Subscriber make up the three primary parts of replication. A publication's data source is the publisher. The distribution of the database objects to one or more destinations is the distributor's responsibility. The recipient of the publisher's data's copies and replications is the subscriber.

 

What are the various replication techniques, and why are they implemented?

Snapshot, Transactional, and Merge Replication are the three main methods of replication. The kind of replication you select will depend on the conditions and/or objectives you're aiming to meet. Like a monthly summary table or a product list table, etc., Snapshot Replication is only helpful when the data inside the tables does not change frequently and the amount of data is not too vast.

When keeping a copy of a transactional table, such as sales order tables, etc., transactional replication is helpful. Merge Replication is more helpful in remote or dispersed systems where data flow can come from multiple locations, such as sales made at a retail store.

 

What is Transparent Data Encryption?

Transparent Data Encryption (TDE), which was first introduced in SQL Server 2008, is a way through which you can encrypt the SQL Server Database files to prevent unwanted access. Additionally, TDE has the ability to safeguard the instance's database backups.