SQLGeeksPro

Hey there, today we are going to discuss another set of Junior SQL DBA Interview Question Answers. Here basically we’ll be concentrating on SQL Server Installation related interview questions. I would suggest you to go through SQL Interview Q&A Part1 , Part2 and Part3 as well.

1.What are the system requirements for installing SQL Server?

Ans: It is always good to refer the Microsoft documentation for specific version and edition before you plan to Install SQL Server. I can      mention few basic requirements which are as follows:

• Minimum 4  GB RAM.
• 64 bit Processor with 2 GHz or faster
• Microsoft recommends to have at least 6 GB free hard-disk space.
• SQL Server 2016 (13.x) and later require .NET Framework 4.6 for the Database Engine. SQL Server setup automatically installs .NET Framework.
• A supported operating system.
• Moreover, You cannot run SQL Server services on a domain controller.

2. What permissions the user needs to install SQL Server? 

Ans: The user or the login should have Local Administrator access on the server.

3. What is a SQL Server Instance?

Ans: A Database Engine instance is a version of the sqlservr.exe program that runs as a service on the operating system. It is responsible for managing several system databases, as well as one or more user-created databases. A single server can have multiple instances running on it, and applications connect to a specific instance to access and work with the databases it manages.

4. What are the different types of Instances?

Ans: There are basically two types of SQL Server Instances.

• Default Instance
• Named Instance

The Default Instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance. In other words you can connect to the SQL Server by the computer name or even by just mentioning a dot “.” if you are connecting to the local SQL Server default Instance. e.g. the below screen shot shows local default Instance connecting using a dot.

A Named Instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.

Example of named Instance:

Moreover there is no requirement to install a default instance; all of the instances running on a computer can be named instances.

5. What is the Install/setup log file name and the path?

Ans: “Summary.txt” is the setup log file name and the path is as follows:

%Program Files%\Microsoft SQL Server\nnn\Setup Bootstrap\Log.  “nnn” represents the version number. Below example screen shot shows the setup file path for SQL Server 2019

6. Is it possible to Install Multiple Instances on the same drive?

Ans: Yes, it is possible as it will create it’s own folder for the instance.

7. What is the default port number of SQL Instance?

Ans: The default port is 1433. You can refer TCP and UDP Ports explained

8.  What is SQL Server Browser Services Port number?

Ans: 1434

9. How to check which port SQL Service is listening to?

Ans: There are couple of ways you can check it.

• SQL Server Configuration Manager
• SQL Server Error log
• Application Event Log

The most common one is SQL Server Configuration Manager if you can login to the server.

Open SQL Server Configuration Manager > SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. Right click on TCP/IP and select Properties from the drop down  and check the port mentioned against TCP Port under IPAll as shown below:

If you want to use SSMS (SQL Server Management Studio), you can open Error log and search for “Server is listening on” and check for the message as shown below having <ipv4>.

You can search for same text in Application event log.

10. Is it possible to change the default port and if yes, then how?

Ans: Yes, it is possible to change the default or existing port and that can be done though the SQL Server Configuration Manager. You can refer the answer to question no. 9 above. Navigate to the same path:

Open SQL Server Configuration Manager > SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. Right click on TCP/IP > select Properties and click on port mentioned against TCP Port under IPAll. Here you can specify the new port number.

You need to restart the SQL Service to take affect of the new port number.

Hello there! As the title depicts this article is 3rd in the series of Junior SQL DBA Interview Questions Answers. Please go through the first two articles Junior SQL DBA Interview Q&A and Junior SQL DBA Q&A – Part2 .

By the way though it is about Junior SQL DBA Interview, you may get these questions for mid to senior level DBAs as well. In the first place Interviewer generally starts with very basic questions.

1. What are the different types of SQL Server database Backups?

Ans: SQL Server supports the following backup types:

1. Full
2. Differential
3. Transaction log
4. Tail log
5. Copy Only
6. File and Filegroup backup
7. Partial backup

Full Backup: A full database backup backs up the whole database. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. This includes part of the transaction log required to fully recover the database.

Differential Backup: Backup all changed extents since last Full backup. Basically it contains all changes since the last full backup.

Transaction log Backup: Also usually called as Log backup, backups all transactions in the database transaction logs since the last log backup. Important to realize that the transaction log backups are incremental and differential backups are cumulative in nature.

Tail log Backup: A tail-log backup captures all log records that have not yet been backed up to prevent work loss.

Copy Only Backup: A copy-only backup is a special type of SQL Server backup that is independent of the sequence of conventional backups. In other words it does not change the differential base and hence does not affects the database restore sequence.

File Backup: This allows you to backup each file independently instead of having to backup the entire database. This is useful when you have a very large database.

Filegroup Backup: This type of backups allows you to backup all files that are in a particular filegroup.

Partial Backup: This allows you to backup only Read-Write Filegroups and exclude Read-Only Filegroups. This is useful for very large database.

2. How can you verify your database backups and make sure they are restorable?

Ans: Using RESTORE VERIFYONLY command we can verify.

RESTORE VERIFYONLY FROM DISK = ‘C:\AdventureWorks.bak’    — Backup path including Filename GO Output:
3. Is it possible to backup RESOURCE database?

Ans: NO! it is not possible to backup RESOURCE database using conventional methods. However you can take physical file level backup of .mdf and .ldf files.

4. What is the password policy of SQL Server?

Ans: SQL Server is able to utilize the password policy mechanisms of Windows. SQL Server login authentication uses this password policy.

SQL Server can apply the same complexity and expiration policies used in Windows to passwords used inside SQL Server.

5. What is  SQL Server Login?

Ans: A person or application can connect to a SQL Server instance using a login, which is a user account used for authentication.

6. What is a database user in SQL Server?

Ans: To connect to the database, a user account called a database user is created at the database level in SQL Server. A login created at the server level is typically mapped to a database user.

7. What is a schema in SQL Server?

Ans: A schema in SQL Server is a collection of objects, such as tables and views, that are owned by a specific user. Schemas can be used to group objects together and to provide an additional level of security.

8. What is a database role in SQL Server?

Ans: A specific set of permissions within a particular database is granted to a group of users known as a database role in SQL Server. You can use database roles to simplify the management of permissions and enhance security. SQL Server has fixed database-level roles built in, and you can create additional roles as needed.

9. What is SQL Server role?

Ans: SQL Server level roles lets group user logins together and manage server-level permissions. Server-level roles help manage permissions for the entire SQL Server instance. SQL Server provides several built in server roles, but you should add your own specific roles if possible. You can’t change fixed server roles.

10. What is the difference between SQL Server login and user?

Ans:

Microsoft SQL Server uses logins to authenticate users to the server. These logins function as security principals that connect users to the server and associate them with server-level roles that allow certain activities.

A user is a security principal that authenticates access to a database. It associates with a database-level role that grants permission for specific activities within the database.

In summary, logins authenticate users at the server level and users authenticate at the database level.

Hey there, this article is about Junior SQL Server DBA Interview Questions and Answers. This will help you learn and prepare for Interview. If you are an Interviewer then also it will help you to access a Junior DBA’s skill. You can ask these questions for 0 – 2 years of experience.

Please do not mug up the answers, always understand the concepts. That will help you to grow your technical skills. I have taken many interviews and it is easy to understand if the candidate has just read a blog without understanding the concepts. The below questions are from real Interviews hence learn well and all the best.

1. What is a Database?

Ans: A database is an collection of structured data. Electronically stored and organized in a computer system. The data can be easily accessed, managed, modified, updated and controlled.

Know more: https://en.wikipedia.org/wiki/Database

2.  What are the main differences between DBMS and RDBMS?

Ans: DBMS stands for Database Management System and RDBMS is the achroname for Relational Database Management System.

DBMS is basically a software to maintain/manage a database with ability to provide controlled access to the data. DBMS stores data as a file.

RDBMS is a an advanced version of DBMS where the data is organized in tables which are related to each other or linked together.

DBMS has low software and hardware requirements whereas RDBMS has higher hardware and software requirements

Any DBMS supports single users, while RDBMS supports multiple users.

DBMS does not support client-server architecture but RDBMS supports client-server architecture.

Examples of DBMS — MS Access, FoxPro, dBase etc.

Example of RDBMS – SQL Server, Oracle, MySQL etc.

3. What is a Transaction in SQL Server?

Ans: Transaction is a set of SQL statements/commands. i.e. Batch of SQL Statement(s) performing a single or multiple activities on a database. It can be a single SQL Statement as well.

Example:

4. What are ACID properties?

Ans: ACID stand for Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties of a transaction.

Atomicity:

Either all or none. Either the entire transaction takes place at once or doesn’t happen at all. There is nothing like partial completion of transactions.

e.g.  You are withdrawing money from ATM. The action (Transaction at the background) either has to complete or does not happen at all.

Consistency:

The database must be in consistent state before and after the transaction.

e.g. If you are transferring money from one account to another account. The total amount of both the accounts should be same before and after the transfer.

Example:
A transferring 100 dollars to B. A’s initial balance is 200 and B’s initial balance is 500.

Before the transaction:
Total of A+B = 200 + 500 = 700$

After the transaction:
Total of A+B = 100 + 6000 = 700$

Isolation: 

A transaction occurs independently. It shouldn’t interfere with the execution of another transaction. Isolation ensures the occurrence of multiple transactions concurrently without a database state leading to a state of inconsistency.

e.g. An account  A is having a balance of 500$ and two transactions started simultaneously to transfer 100$ each to account B and C.  Let’s say these transactions run concurrently and both the transactions read 500$ balance, in that case the final balance of A would be 400$ instead of 300$. So data will become inconsistent.

Isolation makes sure one of the transactions complete first followed by the second one. Hence second transaction would read the correct balance of A as 400$ then deduct 100$.

Durability:

Once a transaction completes successfully, the changes/updates it has made into the database is permanent even if there is a system failure.

e.g: Consider any of the above examples. Once the transaction completes even if the system fails/power down, the account balance will remain same.

5. What is DDL and DML statements?

Ans: DDL – Data Definition Language consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema. It is used to create and modify the structure of database objects in the database.

e.g. CREATE, DROP, ALTER, RENAME etc.

DML – Data Manipulation Language deals with the manipulation of data present in the database.

e.g. INSERT, UPDATE, DELETE etc.

6. What is the difference between Implicit, Explicit and Auto Commit transactions?

Ans: In the implicit transaction mode, SQL Server begins the transactions implicitly but it waits for the commit or rollback commands from the user.

e.g.
SET IMPLICIT_TRANSACTIONS ON
UPDATE T1
SET i=2 WHERE i=3
COMMIT;

Until you run the COMMIT statement the transaction will not complete.

In Explicit transaction mode, user must start the transaction with the BEGIN TRANSACTION statement and end with either COMMIT TRANSACTION or ROLLBACK TRANSACTION statements.

e.g.
BEGIN TRAN
UPDATE T1
SET i=2 WHERE i=3
COMMIT TRAN;


Auto Commit: This is the Default mode of SQL Server. When you run a query without specifying BEGIN TRAN/SET IMPLICIT_TRANSACTIONS, the transaction opens internally and closed automatically.

e.g.
UPDATE T1
SET i=2 WHERE i=3


7. What are system databases and User databases?

Ans: The databases which got installed during the Installation of SQL Server are called as system databases. These are basically Master, Model, Msdb, Tempdb and Resource db.

System databases are used to manage SQL Server and contain information about the SQL Server system like logins, databases, linked servers, jobs, schedules, reports, etc.

User databases are created by users/application. These databases contain the data of application.

8. Explain the use of system databases in SQL Server?

Ans:

Master Database:

Contains all the system-level information for an instance of SQL Server. The master database is the most important as it is necessary for starting the SQL Server.

This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database: 

The model database is used as a template for other databases created in SQL Server, especially when creating user-defined databases.

The entire configuration of the model database, including database options, are copied to the newly created database.

Every time SQL Server is started, it creates tempdb from model hence if model is corrupted, SQL will not come up normally.

Msdb Database: 

The msdb database is used mainly by the SQL Server Agent, and also by other features like SQL Server Management Studio, Service Broker, and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.

Tempdb Database:

TempDB is a global resource that is accessed by all the users in the SQL Server Instance

This stores temporary user objects that you create like temporary tables (Global and Local), temporary variables, temporary stored procedures that hold intermediate results during the sorting or query processing, and cursors.

Internal objects like Work Tables, Work files. Intermediate sort results for operations such as creating or rebuilding indexes.

Version stores, which are collections of data pages that hold the data rows that support features for row versioning.

Resource database:

The Resource database is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The Resource database does not contain user data or user metadata.

Continue reading Junior DBA Interview Q&A -Part2