Sql Server Interview Questions

 

What are the recovery models for a database?

There are 3 recovery models available for a database. Full, Bulk-Logged, and Simple are the three recovery models available.

Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice.

Depending on the recovery model of a database, the behavior of the database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.


How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

What types of replication are supported in SQL Server?

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.

Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.

Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real-time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs


Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

What authentication modes does SQL Server support?

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in.


What are the new features in SQL Server 2005 when compared to SQL Server 2000?

There are quite a lot of changes and enhancements in SQL Server 2005. A few of them are listed here:

Support for Analysis Services on a Failover Cluster.

  1. Profiler being able to trace the MDX queries of the Analysis Server.
  2. Peer-to-peer Replication
  3. Database Mirroring.


What are the High-Availability solutions in SQL Server and differentiate them briefly?

Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.


How do you troubleshoot errors in a SQL Server Agent Job?

Inside SSMS, in Object Explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose a particular job that failed, right-click, and choose view history from the drop-down menu.

The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error that occurred.

 What is the default Port No on which SQL Server listens?

1433.


How many files can a Database contain in SQL Server? How many types of data files exist in SQL Server? How many of those files can exist for a single database?

  1. A Database can contain a maximum of 32,767 files.
  2. There are Primarily 2 types of data files Primary data file and Secondary data file(s)
  3. There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.


What are basic Hardening we  do  in sql server 

-- Lock za account 

-- dont use dafault port 1433 


What are the different types of Upgrades that can be performed in SQL Server?

In-place upgrade and Side-by-Side Upgrade.


Which auto-growth database setting is good?

Setting an auto-growth in multiples of MB is a better option than setting auto growth in percentage (%).


Different backup types :

Full Backup: Backs up the entire database.

Differential Backup: Backs up changes since the last full backup.

Transaction Log Backup: Backs up the transaction log, which records all transactions since the last backup.

A comprehensive backup strategy should include a combination of these types, scheduled appropriately to meet the organization’s recovery point objectives (RPO) and recovery time objectives (RTO).


How do you troubleshoot performance issues in SQL Server?

Leverage SQL Server Profiler to monitor and evaluate SQL queries, helping to identify performance issues and optimize database operations.

Examine the execution plans to identify inefficient queries.

Monitor system performance metrics such as CPU, memory, and disk usage.

Analyze wait statistics to determine system bottlenecks.

Optimize indexes and query structures.


How would you handle database corruption?

In the event of database corruption, a DBA should:

Identify the corrupted data by running DBCC CHECKDB.

Attempt to repair the corruption using DBCC REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, or REPAIR_REBUILD.

If repairs fail, restore the database from the latest clean backup.

Investigate and resolve the root cause to prevent future occurrences.



References : 

https://mindmajix.com/sql-server-dba-interview-questions


Comments

Post a Comment

Popular posts from this blog

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle database 19c Compression Types

   We  had multiple  requirement for Compression hence documenting handy notes  that can help myself and others .  To   automate    compression   we have  feature of Oracle Automatic Data Optimisation  which we will discuss later  ==> Compression types :  We can know  type of compression from  below sql  SQL> select name, currently_used from dba_feature_usage_statistics where lower(name) like ‘%compress%’;  BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor A...
Read more