Some basic questions on SQL Server Administration

1. How will you check SQL Server is running on which port

There are several ways to check which port SQL Server is running on:

Using SQL Server Configuration Manager: 

Open SQL Server Configuration Manager, 
expand SQL Server Network Configuration, 
select Protocols for [Instance Name], and then check the properties of TCP/IP. 
The port number should be listed under the IPAll section.

Using SQL Server Management Studio: 

• Connect to the SQL Server instance using SQL Server Management Studio, 
• right-click on the server name in Object Explorer,
•  select Properties, and then check the Server Properties > Connections page. 
• The TCP Port value should be listed.

Using T-SQL: Connect to the SQL Server instance using SQL Server Management Studio, open a new query window, and run the following T-SQL command:

SELECT DISTINCT local_tcp_port

FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL;

This will return the port number that the SQL Server instance is listening on for TCP/IP connections.

Note: If SQL Server is using a dynamic port (i.e. the port is not set to a specific value), then you can check the SQL Server Error Log for the port number. Look for a message that says "Server is listening on [ 'any' <ipv4> <ipv6> <named instance>]." The port number will be listed after the named instance value.

2.How will you check SQL Server  system databases are in which drive

You can check the location of SQL Server system databases by running the following query in SQL Server Management Studio:

SELECT name, physical_name

FROM sys.master_files

WHERE database_id IN (1,2,3,4)

This query will display the names and physical paths of the system databases: master, model, msdb, and tempdb. You can identify the drive by looking at the beginning of the physical path.

3.What are the different version of SQL Server in market

There are several versions of SQL Server currently in the market. 

The most recent version as of my knowledge SQL Server 2019.

 Some of the previous versions include:

SQL Server 2017

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

Here are the version numbers for the SQL Server versions you mentioned:

SQL Server 2019: 15.x

SQL Server 2017: 14.x

SQL Server 2016: 13.x

SQL Server 2014: 12.x

SQL Server 2012: 11.x

SQL Server 2008 R2: 10.5.x

SQL Server 2008: 10.x

SQL Server 2005: 9.x

4.What are the different type of editions in sql server 2019

SQL Server 2019 offers several editions, each with different feature sets and pricing options. 

The different editions of SQL Server 2019 are:

Express: A free edition limited to one CPU, 1 GB memory usage, and a maximum database size of 10 GB.

Developer: A free edition for development and testing purposes that includes all the features of SQL Server Enterprise Edition.

Standard: An entry-level edition that includes basic features for small to medium-sized businesses.

Enterprise: A high-end edition with advanced features designed for large-scale, mission-critical applications.

Web: A low-cost edition designed for web applications.

Business Intelligence: An edition designed for business intelligence and data warehousing solutions.

Datacenter: An edition designed for highly virtualized datacenter and cloud-based environments.

Note that the availability of these editions may vary depending on the cloud service provider and licensing agreement.

5.How can you check the version of SQL Server Installed on your machine

You can check the version of SQL Server installed on your machine by following these steps:

Open SQL Server Management Studio.

Connect to the SQL Server instance.

In the Object Explorer window, right-click on the server name and select Properties.

In the Server Properties window, select the General tab.

The version information will be displayed under the Product field.

Alternatively, you can also check the version information using a T-SQL query:

SELECT @@VERSION

This will return a resultset that includes the version and edition information.

6.SQL Server supports several network protocols, including:

TCP/IP - This is the most common protocol used for SQL Server communication over a network.

Named Pipes - This protocol is used for communication between processes on the same computer.

Shared Memory - This protocol is used for communication between processes on the same computer.

VIA - This is a high-performance protocol that is used for communication over a high-speed network.

Note that some of these protocols may be disabled by default, and may need to be enabled in SQL Server Configuration Manager before they can be used.

7.How will you remotely check errorlog file of SQL Server

You can remotely check the SQL Server error log file by connecting to the server using SQL Server Configuration manager  or by using Transact-SQL (T-SQL) query.

1. Open SQL Server Configuration Manager.
2. Expand the "SQL Server Services" node and select the SQL Server instance you want to check.
3. Right-click on the instance and select "Properties".
4. In the "SQL Server Properties" window, select the "Advanced" tab.
5. Scroll down to the "Startup Parameters" property and locate the parameter named "-e".
6. The value of the "-e" parameter specifies the location of the SQL Server error log file.

1. Using Transact-SQL (T-SQL) query:

Connect to the SQL Server instance using SSMS or any other client tool.

Open a new query window and execute the following T-SQL command:

EXEC sys.sp_readerrorlog;

This will display the contents of the current SQL Server error log file in the Results pane.

Alternatively, you can use the xp_readerrorlog system stored procedure to read the SQL Server error log file. The syntax for this command is:

xp_readerrorlog [ [ @p1 = ] 'start' ] [ , [ @p2 = ] 'end' ]

[ , [ @p3 = ] { 'string' | spid } ] [ , [ @p4 = ] 'sort_order' ]

Where:

@p1 and @p2 are optional parameters that define the start and end log file number, respectively.

@p3 is an optional parameter that specifies a search string or a SQL Server process ID (spid).

@p4 is an optional parameter that defines the sort order of the results.

For example, to read the error log file and search Recovery keyword for the current SQL Server instance, you can execute the following command:

EXEC xp_readerrorlog 

    0, 

    1, 

    N'Recovery', 

    N'', 

    N'2023-01-01 00:00:01.000', 

    N'2023-12-07 09:00:01.000'

8.How to check which port and protocol is enabled

To check which port and protocol is enabled in SQL Server, you can use the SQL Server Configuration Manager or T-SQL queries. Here are the steps to check the port and protocol settings using both methods:

Using SQL Server Configuration Manager:

1. Open SQL Server Configuration Manager.
2. Expand the "SQL Server Network Configuration" section.
3. Select the SQL Server instance you want to check the settings for.
4. In the right pane, you can see the protocols that are enabled (such as TCP/IP, Named Pipes, etc.).
5. Double-click on the protocol you want to check (for example, TCP/IP).
6. In the Protocol Properties window, select the "IP Addresses" tab.
7. Under the IPAll section, you can see the TCP/IP port number that is being used.

Using T-SQL Queries:

Open SQL Server Management Studio and connect to the SQL Server instance you want to check.

Open a new query window and run the following query:

SELECT 

    DISTINCT protocol_name, 

    local_tcp_port 

FROM 

    sys.dm_exec_connections 

WHERE 

    protocol_name IN ('TCP', 'NP');

This query retrieves information about the protocols that are enabled and the port numbers that are being used. The results will show the protocol name (TCP or Named Pipes) and the local TCP port number that is being used.

Note that the specific steps or query results may vary depending on the version of SQL Server you are using.