What is NAS? Type of NAS. Advantages & Disadvantages of NAS.

What is NAS?

Network Attached Storage is another name for NAS.

Network Attached Storage (NAS Storage) is a well-known storage solution for computer networks.

In 1990s, the NAS device was released in the market for business use.

In the latest NAS device, good features & performances are available.

File-Level storage protocols are used by these devices. 

To allow multiple devices to access the same files and folders at the same time, we generally use Network File System (NFS) or Server Message Block (SMB).

NAS is very simple and easy to set up.

Anyone can access the NAS on the network easily.

The NAS system is very cost-effective and a better option for small businesses/users who really need a better & centralized storage solution.

When multiple users try to access the same file at same time, NAS may perform slower because NAS usage of file-level storage protocols.  

Advantages of NAS:-

1. Cost-Effective: NAS is more cost-effective than SAN and it requires less hardware.

2. Easy to set up: NAS devices are very easy to set up, configure and manage which can be easily deployed and managed through a web interface.

3. Good Option for file sharing: For file sharing & media streaming, NAS devices are more usable across a network.

Disadvantages of NAS:-

1. Scalability: In terms of scalability, NAS devices have limitation and is not suitable for those organizations which require large storage.

2. Performance: The performance of NAS devices is not the same as SAN solutions. The major drawback of NAS is lower performance and is a concern for those organizations which require high performance.

3. Security: At the security level, NAS devices do not provide the same level of security as SAN solutions. This is a concern for those organizations which require high security to store sensitive & important data.

Use of NAS devices

We can use NAS devices for below tasks:-

1. For streaming media across the network.

2. For file sharing.

3. Applications for development or test environment that require less storage, security, and performance.

4. To store images & media files which require frequent access over the network.

5. We can use the NAS solution for backups & DR. 

Type of NAS

Types of Network Attached Storage are given below:-

1. Desktop NAS: This type of NAS device is compact and cost-effective. This type of NAS device can be used for a home or small office and is designed to be put on a desk or attached to a shelf.

2. Rackmount NAS: This type of NAS device is designed in such a way that can be mounted in a server rack easily and are suitable for medium to large organizations. It has multiple bays for drives which can provide more & advanced features/functionality than desktop NAS devices.

3. Cloud NAS: It uses cloud features to store data and backups. It provides a subscription-based service that can be accessed from anywhere over an internet connection.

4. Hybrid NAS: It is a combination of local & cloud storage which provides benefits for both environments. It has multiple bays for drives which can be configured to take a backup of data automatically on the cloud.

5. Enterprise NAS: This type of NAS device is designed for large-scale storage organizations. It can also be used for large data management at an enterprise level. This type of NAS device provides more advanced features like virtualization, clustering, RAID, and high-capacity storage.

 

What is SAN? Type of SAN. Advantages & Disadvantages of SAN.

What is SAN?

SAN is well-known as Storage Area Network and is a block-level storage solution for multiple servers. 

A SAN consists of several storage devices connected to each other and to servers usually via a high-speed network. 

One positive point of a SAN is its speed. 

Because it provides block-level storage it can be used for applications that require high performance and low latency such as databases or virtual machines. 

Because SANs are designed to be shared between multiple servers they can also use storage resources more efficiently than local storage. 

However SANs can be complex to configure and manage and require specialized knowledge to implement and maintain. 

Additionally SANs are typically more expensive than other storage solutions so they may not be the best option for small businesses or home users.

Job Openings In Canada

 

Current Openings HCL Technologies In Canada

Job Title: Backend Junior Software Developer

Job Title: Frontend Junior Software Developer

Location: Canada 

and many more.

https://www.hcltech.com/rise-at-hcl/canada-jobs

Best Practices to Configure SQL Server on New Server

A few best practices are given below to configure SQL Server on a newly built server:

1. Choose correct hardware:- Assuming the correct hardware specification has been selected as per user requirement & estimated load on the server.

2. Sufficient Memory:- We should allocate sufficient memory to the server as well as SQL Server to provide better performance.

As per best practice, allocate :

10-20% of total physical memory as min. memory to SQL Server,

75-80% of total physical memory as min. memory to SQL Server.

Left at least 20% for OS & other processes.

What are differences between FAT and NTFS?

Differences between FAT and NTFS

FAT is also known as File Allocation Table.

NTFS is also known as New Technology File System.

Both file systems technologies are different and used by multiple operating systems to manage and store data on storage devices like HDD or flash drive.

A few key differences between FAT and NTFS are given below for your reference:

1. File Size and Volume Size: 

The maximum file size in FAT32 is 4 GB and the maximum volume size is 2 TB. 

The maximum file size in NTFS is 16 EB and the maximum volume size is 256 TB.

2. Fragmentation:

Fragmentation in FAT is more likely than in NTFS. 

When a file is scattered across different sectors of a disk is known as Fragmentation which causes slow operations like reading values from a file. But in NTFS, it uses advanced concepts & methods to manage file allocation & reduces the possibility of fragmentation.

3. Fault Tolerance:

Fault Tolerance in NTFS is better than FAT. In case of a system crash or any disaster, NTFS can recover data more easily than FAT. But in FAT, we may face some data corruption or data loss-related issues.

4. Security:

Multiple security-related advanced features are available in NTFS which are not available in FAT. For example, file and folder permissions, encryption and auditing, etc.

5. Fault Tolerance:

NTFS has a better fault tolerance than FAT. 

In case of any disaster or system crash, we can recover data easily in NTFS but in FAT, we may face data corruption or loss.

6. Compatibility:

FAT file system is widely used by multiple operating systems. 

As we aware, the default file system for Windows is NTFS but macOS or Linux OS are not supporting NTFS.

What are differences between NTFS and ReFS?

Differences Between NTFS and ReFS

NTFS stands for New Technology File System.

ReFS stands for Resilient File System.

Microsoft has developed both file systems technologies for Windows OS.

Few major differences between NTFS and ReFS are as follows:

1. File Size Limit: NTFS supports file sizes up to 16 exabytes, while ReFS supports file sizes up to 262144 exabytes.

2. Scalability: We can use NTFS for small and large storage devices but in the case of large storage devices, need to use ReFS.

3. Release: In the mid-1990s, the NTFS file system was released and currently it is widely used in many small & large organizations but the ReFS was released in 2012 with Windows Server 2012 and is a new system.

What are differences between DDL DML DCL and DQL

Differences between DDL, DML, DCL, and DQL

1. Full Name: 

DDL stands for Data Definition Language.

DML stands for Data Manipulation Language.

DCL stands for Data Control Language.

DQL stands for Data Query Language.

2. Purpose:

Use Data Definition Language to specify the structure of a database.

Use Data Manipulation Language to manipulate the data which are stored in the database.

Use Data Control Language to control access within the database.

Use Data Query Language to retrieve data from the database.

3. Type of operation:

Job Openings In US

Current Openings HCL Technologies In United States, Brazil, Canada, Mexico

Job Title: Administrator

Location: Virginia, Texas

Job Title: Support engineer - senior - packages

Location: Colorado

Job Title: Developer - senior - 2

Location: Texas

Job Title: Project lead

Location: Georgia

Job Title: Customer support associate-voice

Location: Duluth

Job Title: ERP specialist - packages

Location: Missouri

and many more.

Visit : https://www.hcltech.com/careers/explore-hcl-america

Job Openings In India for Freshers and Experienced person

LTIMindtree Recruitment Drive 2023 | Software Engineer | Freshers Job Role: Software Engineer Qualification : BE/ BTech/ ME/ MTech Salary: As Per Company Standards

Application Mode: Online 

Website: https://www.ltimindtree.com/careers

Differences between NAS vs SAN vs RAID? Uses of NAS vs SAN vs RAID.

Differences between SAN vs NAS vs DAS vs RAID

What is Dirty pages in SQL Server? Why SQL Server uses dirty pages?

A dirty page is a term used in Microsoft SQL Server. A dirty page is a data page that has been modified in the buffer cache but still not transferred to disk.

When we execute any update query, the system updates the change in memory first and the corresponding data page has been marked as dirty.

SQL Server engine runs a process to write all such dirty pages back to disk.

Flushing is the process that writes all dirty pages back to disk. Several events can trigger the Flushing process. 

A few are as below:

1. When the SQL Server engine needs to free up memory for other processes

2. When a checkpoint occurs

3. When a transaction commits

Difference Between Lazy Writer and CheckPoint

Lazy writer and Checkpoint both are very important internal process which is used in Microsoft SQL Serve & allows to use the memory properly & minimise data loss in case of any disaster or system failures. Both serve the same purpose but there are significant differences between them.

Few differences between Lazy writer and Checkpoint are given below:

1) Trigger: Lazy Writer is triggered when the system feels memory pressure and buffer pool is running out of space but Checkpoint is triggered either log buffer is full or manually issued checkpoint by user.

Lazy Writer occurs depending on the memory pressure and resource availability but Checkpoint occurs every 1 minutes approx. or as per user request.

What is Checkpoint in SQL server? What is the use of Checkpoint?

In Microsoft SQL Server, checkpoint is a very important process which helps to write all modifications made in the database from buffer cache to disk.

This process helps to reduce the possibility of data loss in case of any disaster or system failure.

Lazy Writer is also available in SQL Server to write modified data from buffer cache to disk if everything is normal.

But this approach may not work properly in case of system failure or unexpected shutdown.

To overcome this situation, SQL server has Checkpoint feature which transfers all modified pages to disk.

What is Lazy Writer in SQL Server? What is the use of Lazy Writer in SQL Server?

The Lazy Writer in SQL server is a background thread which manages allocation and deallocation of memory pages in the memory. 

It is key component of memory management subsystem of SQL Server.

It continuously runs & searches the buffer pool & try to find those pages which are least recently used (LRU).

The main & important role of Lazy Writer is to provide sufficient memory to the database to perform its operations efficiently.

An algorithm named as Least Recently Used (LRU) is used by Lazy Writer to decide which pages to evict from buffer pool. It tracks the timestamp of last access of each page. The pages with the oldest timestamp are considered as least recently used and are ready for eviction.

Introduction of SQL Server Management Studio

SQL Server Management Studio

SQL Server Management Studio (in-short SSMS) is an application that is used by developers to access objects & write T-SQL or develop code blocks for a particular task as well as administrators to perform administrative tasks.

This application is developed by Microsoft Team. 

In 2005, Microsoft launched SQL Server Management Studio which is the primary tool to managing SQL Server.

We can easily download the SSMS from Microsoft & it is totally free.

We can use this tool with all versions of SQL Server. 

SSMS is really very user-friendly & easy to use.

It provides a lot of features & in-built tools for developers as well as administrators to perform their tasks efficiently.

How to Install SSMS

First of all, we need to download Installation Media from the Microsoft website.

What is SQL Server Management Studio (SSMS)?

Microsoft has provided a GUI tool to manage SQL Server databases and perform administrative and development tasks. The name of the tool is SQL Server Management Studio or SSMS. This tool is having lots of features that help developers as well as administrators to perform their tasks easily. For example:

1. To create, modify and delete the database.

2. To manage DB objects like tables, views, sp, triggers, linked servers, logins, etc.

3. To write & execute SQL queries to fetch data from the database.

4. To handle security and permissions properly.

5. Keep a close eye on the health of server performance and tasks.

6. Import & export data from/to other data sources.

7. To create, modify, schedule, and manage SQL Agent jobs.

8. Configure DB mail & alerts.

9. Configure, monitor & manage High Availability.

After installing SSMS, we can connect any SQL Server instances and start our work.

Connecting to SQL Server instances:

When we open the SSMS, It'll automatically open the "Connect to Server" dialog box. It'll ask to put the server name or instance name to connect. Just put the server name or instance name, then select authentication mode (either Windows Authentication or SQL authentication) & then click on the "Connect" button to connect the server. 

To create, modify, and manage databases

After connecting a SQL Server instance, we'll get multiple options to manage our databases. This tool provides a lot of tools and features which help us to create a new database, set properties of the database as per the requirement, modify database property, manage the database, create, modify, and manage database objects, and use import and export wizard to import and export data.

To create a new user database, we need to right-click on the "Databases" folder in Object Explorer and select "New Database". It'll open the "New Database" dialog box. In this dialog box, just specify the Database Name, DB file location, number of data files, file size, owner, recovery model, and other settings.

Query Editor

An important part of SSMS is Query Editor which allows us to write and run SQL queries to perform specific tasks against SQL Server databases. This editor has capabilities to check & highlight syntax errors, provide  IntelliSense, and features to debug the SQL query or block of code. It also provides features to format our queries or block of code which increases the readability of code.  

Object Explorer

In SSMS, the Object Explorer allows us to select, view and manage database objects like tables, stored procedures, views, functions, and more. Using this, we can create, modify, delete, and change any property of the objects.

Recovery Model in Microsoft SQL Server

In SQL Server, the recovery model is a very important concept or we can say, it is a configuration setting in which the system defines how the transaction log is used to recover the database at the time of any issue in the database.

SQL server has three recovery models for databases: 

    i) Simple, 

    ii) Full and 

    iii) Bulk-logged. 

Every recovery model has advantages and disadvantages.

To decide on the recovery model of any new database, we need to consider specific requirements and needs for the application.

Components of SQL server / Database Engine in Microsoft SQL Server

Microsoft SQL Server is a well known RDBMS & is used to manage large amounts of data. 

The Database Engine is the core component of SQL Server.

The Database Engine stores data, process as per requirement and also prevent the data from external threads and vulnerability.

This article will give you the idea about various components of the SQL server / database engine, idea of internal processes and their work.

1. Query Optimiser:-

The responsible of query optimiser is to generate Optimised Execution Plan for the queries.

Query Optimiser analyses query and generates multiple execution plan which are evaluated on the basis of cost and performance characteristics.

After that Query Optimiser selects best Execution Plan & executes the query.  

The optimiser considers available resources, workload, statistics and indexes during the estimation of query cost.

2. Query Processor:-

Query Processor executes all SQL queries & return results to end user.

When we run a query, goes through multiple phases like cmd parsing, optimisation and execution.

    a) CMD Parshing:-

    In Parshing Phase, it checks syntax & semantics of the query and in case of     any error, throws message to the end user.

    b) Optimiser:- Query Optimiser is very important part of SQL Server.

It is also very complex and secret part of SQL server. 

This phase generates an optimised execution plan for the query.

Also known as "Cost-Based" optimiser. It means, Optimiser will check the best query plan which should be cost effective also.

Optimiser always tries to find best plan for the query to execute & fetch the result.

It works in three search phases:

    i) 0th Phase - In this phase, looks for nested loop joins.

    It stops if the cost of the plan is <0.2. The outcome plan from phase is             known as transaction processing  or TP Plan.

    ii) 1st Phase - Looks for common patterns. It stops if the cost of plan is             <1.0. The outcome plan from phase is known as quick plan.

    iii) 2nd Phase - Looks at parallelism & indexed views. 

 

   c) Query Executor:- In this phase, it executes the query & fetches the data     from database and returns the results to the end user.

    During the execution of query, it uses query plan & takes help of storage        engine.

3. Storage Engine:-

Storage Engine helps to store and retrieves data from storage devices. It also manages the data files and indexes which are used to store and organise data. Storage engine also manages concurrency which ensures multiple users can access & modify the same data without any conflicts.

It uses multiple algorithms to optimise data storage and retrieve data smoothly. 

a) Access Method:-

Access method provides storage structure for our data & indexes and also provides interface to access the data as well as modify the required data.

Actually it sends request to Buffer Manager to fetch the required data.

b) Transaction Manager:-

Basically, transaction manager is having two components which are as follows:

i) Lock Manager - Manages concurrency for the data.

ii) Log Manager - It writes the changes in the transaction log which is also known as Write-Ahead Logging.

c) Buffer Manager:-

Buffer Manager handles buffer pool.

Suppose, we want to read few rows. Buffer Manager checks whether the required data is available in buffer pool/cache or not. If it is available, the required data are returned back to access method. Suppose the requested data is not available in buffer pool/cache, Buffer Manager fetches the data from the disk & stores it in the buffer pool/cache and returns the data to Access Method.

More coming Soon.....

Pre-Requisites for Different Version of Microsoft SQL Server on Single Page

This article will give you a complete idea on the prerequisites to install Microsoft SQL Server. 

This will cover different versions of SQL Server starting from 2012 to 2022. 

It will give you knowledge of the minimum hardware & software requirements to install MS SQL Server.

The hardware requirement is given below for all major SQL Server versions:

Hardware Requirements

 

Hard Disk

Min. Memory
Express Edition

Min. Memory
All Other Editions

SQL Server 2012

Around 4 GB

512 MB

1 GB

SQL Ser. 2012 R2

Around 4 GB

512 MB

1 GB

SQL Server 2014

Around 4 GB

512 MB

1 GB

SQL Server 2016

Around 6 GB

512 MB

1 GB

SQL Server 2017

Around 6 GB

512 MB

1 GB

SQL Server 2019

Around 6 GB

512 MB

1 GB

SQL Server 2022

Around 6 GB

512 MB

1 GB

Hardware Requirement Table-1

Hardware Requirements

 

Memory Recommended
Express Edition

Memory Recommended
All Other Editions

SQL Server 2012

1 GB

At least 4 GB

SQL Ser. 2012 R2

1 GB

At least 4 GB

SQL Server 2014

1 GB

At least 4 GB

SQL Server 2016

1 GB

At least 4 GB

SQL Server 2017

1 GB

At least 4 GB

SQL Server 2019

1 GB

At least 4 GB

SQL Server 2022

1 GB

At least 4 GB

Hardware Requirement Table-2

Hardware Requirements

 

Minimum - Processor Speed

Recommended - Processor Speed

SQL Server 2012

1 GHz-32 bit & 1.4 GHz-64 bit

2.0 GHz

SQL Ser. 2012 R2

1 GHz-32 bit & 1.4 GHz-64 bit

2.0 GHz

SQL Server 2014

1 GHz-32 bit & 1.4 GHz-64 bit

2.0 GHz

SQL Server 2016

1.4 GHz-64 bit

2.0 GHz or Faster

SQL Server 2017

1.4 GHz-64 bit

2.0 GHz or Faster

SQL Server 2019

1.4 GHz-64 bit

2.0 GHz or Faster

SQL Server 2022

1.4 GHz-64 bit

2.0 GHz or Faster

Hardware Requirement Table-3

Software requirement is given below for all major SQL Server versions:

Differences between Temp Tables and Table Variable

Top 15 differences between Temporary Tables and Table Variables in SQL Server:

1. Performance: 

A temporary table works faster if we have a large dataset. 

We can create indexes that can be optimized by the query optimizer.

The table variable works faster if the dataset is small.

Faster because the table variable is stored in memory.

2. Storage: 

The TempDB database houses temporary tables.

Table variables are compact and memory-based.

3. Lifetime: 

Until we remove the table or the session expires, a temporary table is accessible.

However, after the batch or operation is finished, it is immediately erased from the table variable.

4. Indexing: 

We can create indexes on temporary tables.

But we can't create indexes on table variables.

Tips to minimise TempDB utilisation in Microsoft SQL Server

 

As we aware, Tempdb is very important system database which is used to store temporary data. Various database operations require temporary space during sorting, grouping, and joining.

Few tips are given below to minimise TempDB utilisation:

1. Optimise queries before releasing on server: The size of temporary data in TempDB will increase if the query is written poorly. So optimise your query properly before releasing on the server.

2. Avoid Using Cursors: Cursor works on row by row and one row at a time.  Avoid using cursors in your code block or SP to avoid this issue.

 

3. Data type also plays important role.  Use the data type of column properly.

4. Store Data and Log Files on different drives to get better Read-Write performance.

5. Size of tempdb: Keep close eye on TempDB size & add more space if needed. 

Top 10 best practices for SQL developers to boost query performance

 

Writing a query to fulfill the requirement is not only a task for SQL developers. Developers need to think about the performance part also.

Several best practices are available to optimize query performance which will help us to run our database smoothly. 

A few best practices are described below to boost query performance:

1. Cursors are resource intensive & degrade query performance significantly. Cursors execute the query one by one row at a time. If possible, avoid cursor & use set-based operations, While loop etc.

2. Try to minimize network traffic by avoiding unnecessary columns in the SELECT statement. Select only those columns which are really required.

3. Use SET NOCOUNT ON to suppress the message which indicates the number of rows affected by a query. It'll reduce network traffic.

4. Use stored procedures with parameters to avoid SQL injection and improve query performance because stored procedures are pre-compiled.

5. Avoid subqueries in the SELECT list, WHERE clause, or HAVING clause. Subqueries impact the performance significantly.

6. Try to use views that will really simplify complex queries and improve query performance.  

7. Avoid using SELECT DISTINCT in the query to get unique data. We can use GROUP BY to remove duplicates, improving query performance.

8. Avoid unnecessary data type conversions in the query. Converting data types in the query is more expensive and affects query performance.

9. Use UNION in place of OR to combine multiple result sets. OR will be inefficient and slow.

10. Optimise joins properly to get better performance. Ensure the join columns should be indexed properly.

FAQs:

Q: What recommended practices for SQL developers should they follow to improve query performance?

Ans: By making good use of indexes, streamlining the database structure, minimizing pointless joins, employing the right data types, and tuning queries using WHERE clauses, SQL developers may improve query performance.

Q: Why do indexes matter for the effectiveness of queries?

Ans: By enabling the database to discover and retrieve specified data fast, indexes increase query speed by eliminating the need for entire table searches.

Q: How can I improve the speed of my database schema?

Ans: We should normalize data to remove redundancy, establish suitable links between rows, and refrain from utilizing an excessive number of superfluous tables to optimize the database structure.

Q: What effect do pointless joins have on query speed?

Ans: Joins that are not required might result in a bigger result set and cause unnecessary processing, which can dramatically reduce query speed.

Q: Why is using the right data types in SQL queries so important?

Ans: Utilising the appropriate data types guarantees effective storage utilization and lowers data conversion overhead, improving query performance.

Q: What advantages do query parameters provide over explicitly encoding values in SQL statements?

Ans: Utilising query parameters enhances query performance by preventing SQL injection and enabling the database to reuse query plans.

Q: What effects may database statistics have on the speed of a query?

Ans: UtilizingThe query optimizer is aided in decision-making by accurate and current database statistics, which results in more effective execution plans.

What is Tempdb contention?

 

Tempdb contention is a performance issue which occurs when multiple sessions try to access same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.

Few common reasons of tempdb contention are given below:

1. If multiple big/heavy queries are running on the server simultaneously, that can create TempDB contention.

2. TempDB contention occurs if we use multiple temporary tables in our queries or SP. If the query is using multiple indexes, it'll also help to raise TempDB contention.

3. If multiple transactions are using snapshot isolation or performing online index operations simultaneously, one of reason to create TempDB contention.

How to address TempDB contention issue:-

1. Monitor TempDB usage using profiler, perfmon tool, DMVs etc.

2. Work on query optimisation to enhance performance of query. Use CTE in plan of Temporary Tables.

3. If possible, increase no. of tempdb data files as well as size of all files should be same. Also increasing file size will help to avoid this issue.

4. Another point is to use snapshot isolation when necessary and needed.   

Rebuild System Database in SQL Server

 

The rebuild process of system databases is a complex and risky task in the SQL server.

This should be handle with care & should be performed by experience database administrator.

so it's important to carefully follow the necessary steps. Steps to rebuild the system databases in SQL Server is given below:

1. Check whether you have latest db backup of all system & user databases.

2. If possible, take help of Windows Team to take Cohesive/Snapshot backup for safer side.

3. Stop SQL Server services as well as SQL Server Agent.

4. Change the directory which has SQL server Installation media.

5. Run the following command in command prompt.

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer

/SQLSYSADMINACCOUNTS=<AccountNameWithAdminRights> /SAPWD=password /SQLCOLLATION=collation

6. Press enter to start installation process.

7. During the installation process, select option to rebuild system databases.

8. Now need to select authentication mode and specify sa password.

Next Step.....Coming Soon...

How to Move System Database

 

To move system databases from one place to another is a complex process that requires proper planning and implementation steps. Step-by-step implementation plane to move system databases from one place to another is given below:

1. Proper Migration Plan is required.

2. If possible, include backup team to take cohesive / snapshot at their end.

3. Drop mail to application team for Pre-activity intimation.

4. Drop mail to monitoring team to include the server in maintenance mode.

5. Run the pre-health check for server as well as databases, services, drive spaces etc.

Difference between Data and Log file

Data and Log file

In a database management system, data files & log files both are used for different purposes.

The structure of the data file & log file is also different.

Data files: Data files are physical files that are located on a disk or on the network to share & keep actual data.

Data files contain tables, indexes, views, stored procedures, and other objects. 

A database may have one or more than one data file.

A database may have one or more than one filegroup.

A file group is a collection of one or more than one data file. 

Internally, the data file contains multiple pages.

As per Microsoft(MS), place data files & log files on separate drives to get better performance.

The type of data files is given below:

1) Primary Data Files:-

The system creates this type of file for a database.MDF extension.

2) Secondary Data Files:-

This type of file is optional & is also used to store data. 

We can create/add more than one secondary data file in a database that has.NDF extension.

Log Files: Although they have a distinct structure, log files are still physical files.

The log file has the.LDF file extension.

The history of every transaction that has been carried out for the database is kept in the log file.

records all inserts, updates, deletions, backups, schema modifications, etc. 

Moreover, it guarantees data consistency and offers a means of recovering from system errors.

Transaction history is sequentially stored in the log file.

A database can have several log files added to it.

 

What is Distribution database? What are use of Distribution database?

Distribution database:-

The distribution database is another system database in SQL Server.

It is very useful in the replication of objects.

The utilization of this database is to store metadata of replication & information of distribution agents & subscribers.

This database contains details of the article, meta-data of replication, and data.

What is Resource database? What are use of Resource database?

Resource database:-

The 5th system database in SQL Server is the resource database. This database is hidden from users and is read-only.

Resource databases, which are used to offer a collection of system objects that are consistent across all editions of SQL Server, were first introduced with SQL Server 2005.

Here are some important points about it:

Purpose: The read-only Resource database serves as a storage space for the system objects and metadata that the SQL Server instance needs. It keeps track of system objects like functions, stored procedures, and system catalog views.

Hidden Database: Resource databases are concealed and not immediately accessible by users or administrators, in contrast to user databases. The SQL Server engine is intended to be used internally.

Single Instance: Each SQL Server instance only has one Resource database, which is shared by all the databases that are hosted by that instance.

Patches and Upgrades: The Resource database is modified to update system objects during the installation of a service pack or a SQL Server upgrade. This aids in maintaining compatibility between the installed version and the system catalog.

Version-specific: The Resource database only supports that particular version. A new Resource database is built to correspond with the system objects of the new version whenever an SQL Server instance is updated.

Recovery Model: Because the Resource database is designed to be read-only and does not require transaction log backups, it employs the Simple recovery model.

Security: The Resource database includes stringent security controls since it is necessary for the SQL Server instance to run. It is only accessible to the SQL Server service account and highly privileged administrators.

Avoid Modifications: Playing around with the Resource database might cause serious problems and possibly ruin the database. Any direct modifications to this database must be avoided.

System Object Access: When resolving system object references in queries, the SQL Server engine checks the Resource database. It aids in effectively retrieving data about system items.

File Location: The Resource database files (MDF and LDF) are kept in the 'MSSQLBinn' folder of the SQL Server installation directory.

By default, the location of resource database files are <drive>:\Program Files\Microsoft SQL Server\MSSQL1.<instance_name>\Binn\

The resource database's ID is 32767.

The T-SQL command cannot be used to back up and restore this database.

What is MSDB database? What are use of MSDB database?

 MSDB Database:-

A key and important system database in Microsoft SQL Server is the MSDB database. In addition to other crucial functions, it is crucial for handling different administrative activities, scheduling jobs, and keeping historical data about SQL Server Agent. The MSDB database assists in streamlining and automating routine maintenance, backups, and data transfer activities by serving as a store for crucial metadata and configurations.

1. MSDB database is the 4^th system database which is used to store details of SQL Server Agent like job history, database backup & restore history, job schedules, alerts, and operators.

2. This database contains information on backups, jobs, alerts, operators, and other system-related tasks.

3. We can't drop the MSDB database.

4. We can't remove the primary filegroup, primary data file, or log file.

5. We can't rename the database or primary filegroup.

6. We can't set the database to OFFLINE.

7. We can't set the primary filegroup to READ_ONLY.

8. The Recovery mode of the MSDB database is SIMPLE. It means we can take T-Log backup.

9. The database ID of MSDB is 4.

Advantages of MSDB database:

Centralized Storage for SQL Server Jobs: SQL Server Agent tasks, including their schedules, histories, and step-by-step descriptions, are stored in a single location, the MSDB database. By streamlining job administration and monitoring, this centralized storage makes it simpler to schedule and keep track of diverse jobs and procedures.

Maintenance Plan Storage: Backups, index upkeep, and integrity checks are just a few of the maintenance operations that database administrators may schedule and manage thanks to the storage of maintenance plan data by MSDB. This assists in minimizing downtime, guaranteeing database health, and automating normal maintenance tasks.

What is Model database? What are use of Model database?

Model Database:- 

1. Model database is a third-system database.

2. This database is used as a template to create a new user database.

3. When we fire a command to create a new user database, the system just creates the replica of the Model database with a specified name which includes predefined objects, settings, and configurations.

4. Changes made in the Model database, will be inherited by all user databases. It means, that if we create an object in the Model database, it'll be included automatically in all newly created user databases.

5. We can't add any file or filegroup in the Model database.

6. We can't change the owner of the Model database from SA to any other.

7. We can't set the Model database to OFFLINE.

8. The recovery model of this database is Full. Hence, we can take full, differential, and transactional log backups.

9. The ID of the Model database is 3.

Components of the Model Database:

The model database also contains lots of components like system tables, system views, system functions system stored procedures, etc. 

Details of a few important components are given below:

System Tables: This database contains a few system tables like sys. objects, sys. columns, sys. indexes, and sys. views etc.  

System Views: The model database also contains a few system views which provide easy access to system tables. For example, sys.database_files, sys.database_permissions, and sys.database_role_members.

System Stored Procedures: A few system-stored procedures are also available in this database which helps DBA perform their administrative tasks. For example, sp_adduser, sp_helpdb and sp_addrole etc.

Functions of the Model Database:

Here are a few essential functions of the Model database for your reference:

1. Providing a Template 

2. Defining Database Properties 

3. Managing Database Security

4. Supporting Customization

Conclusion:

The model database is critical so always keep good db backup in a safe location for any unprecedented issue.

Related Articles:-

What are System Databases? What are the uses of a System database?

What is a master Database? What are the uses of a master database?

What is the TempDB database? What are the uses of the TempDB database?

What is the MSDB database? What are the uses of the MSDB database?

What is the Model database? What are the uses of the Model database?

What is a Resource database? What are the uses of the Resource database?

What is a Distribution database? What are the uses of a Distribution database?

What is TempDB database? What are use of TempDB database?

Outline of the Article:

1. Introduction

2. Advances and disadvantages

3. Components of the Tempdb Database

4. Functions of the Tempdb Database

5. Conclusion

6. FAQs

Introduction

1. TempDB database is also an important & system database. It is used to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.

2. When we restart the SQL service, the system automatically re-creates the TempDB database.

3. The Recovery Model of TempDB is also Simple.

4. TempDB database id of TempDB is 2.

5. In TempDB, not possible to add a filegroup.

6. We can’t take TempDB backup or restore.

7. We can’t change the database owner of TempDB. It should be sa always.

8. We can’t create a snapshot of the TempDB database.

9. We can’t drop the TempDB database.

10. We can’t set the TempDB database as OFFLINE.

Components of the Tempdb Database:

As we specified above, the TempDB database is also a system database that contains lots of components like system tables, system views, and system stored procedures. Sometimes, the database engine creates some Internal objects like temporary large objects (LOB) and work files for hash operations, to store Intermediate sort results and Version stores, etc.

A few essential components of the master database are given:

System Tables: This database also contains a lot of system tables that store details of temporary objects like temp tables, table variables, cursor tables, etc.

System Views: This database contains system views that provide easy access to system tables.

System Stored Procedures: The Tempdb database also has system stored procedures that help DBAs perform their tasks like sp_help, sp_rename, sp_helpindex, etc.

Functions of the Tempdb Database:

TempDB is a critical database that helps to manage temporary objects in SQL Server. 

A few critical functions of the Tempdb database are given below:

1. Storing Temporary Objects

2. Supporting Snapshot Isolation

3. Managing Transactions

4. Supporting Online Index Operations

5. Supporting Sorting and Hashing

Conclusion:

As we are aware, Tempdb is a very important database in SQL Server, so always configure TempDB properly to get good performance.

FAQs

Q. What is tempdb in SQL Server?

Ans. TempDB database is also an important & system database.

Q. Why tempdb is used in SQL Server?

Ans. TempDB is used to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.

Q. When is TempDB used in SQL Server?

Ans. The system uses TempDB to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.

Q. Can we take a backup of tempdb in SQL Server?

Ans. We can’t take TempDB backup or restore.

Q. How can you increase the size of tempdb in SQL Server?

Ans. Right-click on the TempDB, Select Properties, go to Files & change the size (MS) as per requirement.

We can use the below command also to increase the size of TempDB.

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE(NAME=N'tempdev',SIZE = 10240KB )

GO

ALTER DATABASE [tempdb] MODIFY FILE(NAME = N'templog', SIZE = 10240KB )

GO

Q. How do I restore tempdb in an SQL Server?

Ans. We can’t take TempDB backup or restore the TempDB database.

Q. What is the use of TempDB in an SQL Server?

Ans. The system needs some space to store data/results during the execution of user query/operation. Generally, the system stores temporary data like temp. tables, temp. SP, internal objects, and other temp. objects etc.

Q. How do you delete TempDB files in SQL Server?

Ans. If the SQL service is running, the system will not allow deleting any file of the TempDB database. To delete any TempDB file, need to stop SQL service & change the system configuration for TempDB.

Q. How do you free up TempDB space in SQL Server? / Tempdb drive is full in SQL Server? / If TempDB is full what to do in SQL Server? / How to clear TempDB in SQL Server? / How to shrink TempDB in SQL Server? / What to do when TempDB is full in SQL Server?

Ans. To Free up the TempDB space, follow the below steps(Before running on the Prod environment, check at the Test or Dev environment or discuss with Sr. DBA):-

1. Check whether any bulky query is running or not. If running, check with the application team to stop or restart that process.

2. Still the issue is available: If not, then connect with the Windows Team & try to get more free space on the drive which has TempDB data & log files.

3. Still the issue is available:-If not possible to add more space, try to shrink TempDB after running CHECKPOINT in TempDB.

CHECKPOINT

go

DBCC FREEPROCCACHE & then

go

USE [tempdb]

GO

--To Shrink Data File

DBCC SHRINKFILE (N'tempdev', 0, TRUNCATEONLY)

GO

--To Shrink Log File

DBCC SHRINKFILE (N'templog', 0, TRUNCATEONLY)

GO

--To Shrink Database

DBCC SHRINKDATABASE(N'tempdb' )

GO 

4. Still the issue is still available, get approval from the application owner/server owner to reboot the server. Schedule the change. Perform a Pre-health check, take the help of the Windows Team to reboot the server, and then Post-health check  

Q. What is the purpose of TempDB in SQL Server?

A. TempDB provides the space to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.

Q. What should be the initial size of TempDB in SQL Server?

A. The number of TempDB files & the size depend on multiple factors like server configuration, load from the application, etc.

Q. Where to find TempDB in SQL Server? / Where is TempDB located in SQL Server?

A. To find TempDB, Just follow the below steps:-

1. Connect with your SQL Server Instance

2. Expand Instance

3. Expand Database

4. Expand System Databases

5. Here we'll get TempDB

To get the physical location of TempDB files, use the below query to get more details

Use Master

go

SELECT * FROM sys.master_files WHERE database_id=2

Q. How to shrink TempDB in SQL Server without restarting the server?

A. Just refer above question which has all the steps. For this, refer only to steps 1, 2, and 3.

Q. How to change the initial size of TempDB in SQL Server?

A. To change the Initial size of TempDB, we have two options:

1. Specify at the SQL server installation

2. Specify in the property of TempDB (If the SQL server is already installed). 

Q. How to rebuild tempdb in SQL Server?

A. Just reboot the server. The system will automatically rebuild/recreate the TempDB database. But before restarting the SQL service, check with your Sr. DBA & application owner.  

 

Q. How to check the size of tempdb in SQL Server?

A. Use the below command to check the size of TempDB or any other database

Use TempDB

go

sp_spaceused 

Q. How to restore tempdb in SQL Server?

A. Restore TempDB is not allowed.

Q. When tempdb is full in SQL Server? / Why does tempdb size increase in SQL Server?

A. TempDB is generally full when the bulk query is running or the Index maintenance job is running etc.

Q. Can we shrink tempdb in SQL Server?

A. Yes, we can shrink tempdb.

Q. Can we take a backup of tempdb in SQL Server?

A. No, We can't.

Q. Can we shrink the tempdb data file in SQL Server?

A. Yes, we can shrink tempdb.

Q. How can I tell what is consuming tempdb space in SQL Server? / 

How to check what is consuming tempdb space in the SQL Server?

A. Use the below query to get the details of TempDB usage:

SELECT @@ServerName as ServerName, DB_Name() AS DBName,

(SUM(fsu.unallocated_extent_page_count)*1.0/128) AS [AvailableFreeSpace(MB)]

,(SUM(fsu.version_store_reserved_page_count)*1.0/128) AS [UsedByVersionStore(MB)]

,(SUM(fsu.internal_object_reserved_page_count)*1.0/128) AS [UsedByInternalObjects(MB)]

,(SUM(fsu.user_object_reserved_page_count)*1.0/128)  AS [UsedUserObjects(MB)]

FROM tempdb. sys.dm_db_file_space_usage fsu;

Q. Why TempDB is growing in SQL Server?

A. TempDB is used to store temporary objects & query results. 

TempDB is the shared resource that is heavily used by all user databases. 

Below are a few reasons for tempdb growth in SQL Server:-

1. Version Store: In Snapshot or Read Committed Snapshot Isolation level, the system uses TempDB to store older versions of rows. In the case of long-running transactions, TempDB grows very fast.

2. Multiple Active Transactions: In case of multiple active transactions, the system uses tempdb which also grows the size rapidly.

3. Sorting and Grouping: If the query is fired with ORDER BY (means result in sorted order) or GROUP BY (to get aggregated result), one of the causes is to grow TempDB size.

4. Temporary Tables: Temporary tables are stored in TempDB. If we use a lot of temporary tables in our queries, It also helps to grow the size of TempDB.

5. Index Operations: At the time of Index creation or rebuild online, SQL Server uses TempDB. It will grow the size of tempdb.

6. Query Spills: In case of large datasets or complex calculations, the system uses TempDB & this will also increase the size of tempdb.

Q. How to resize tempdb in SQL Server?

A. To resize TempDB, use the below command

ALTER DATABASE TempDB MODIFY FILE(NAME = tempted, SIZE = 50000MB);

Q. What is tempdb contention in SQL Server?

A. Tempdb contention is a performance issue that occurs when multiple sessions try to access the same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.

Q. What should be the tempdb size in SQL Server?

A. TempDB size in any environment depends on multiple factors like current resources, load on the server, facing TempDB contention issues, etc etc.

To get better performance:- 

keep data & log files on separate drives.

Keep the same size as all TempDB files.

The number of data files in TempDB should not be more than the number of logical processors. It means, if we have 8 logical processors, we can create 8 data files on TempDB. 

Q. How to check if TempDB is full in SQL Server?

A. Use the below command to check the free space on TempDB

USE tempdb

EXEC sp_spaceused

OR Use the below query

SELECT @@ServerName AS ServerName, DB_Name() AS DBName,

(SUM(unallocated_extent_page_count)*1.0/128) AS [AvailableFreeSpace(In MB)]

FROM sys.dm_db_file_space_usage;