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.