SQL Server DBA Interview Questions and Answers

SQL Server DBA Interview Questions and Answers

In case you’re searching for SQL Server DBA Interview Questions and answers for Experienced or Freshers, you are at the correct place. Additionally, our GangBoard SQL Server DBA Online Training helps you gain proficiency in SQL architecture, DB files, transaction isolation, locks, etc.. SQL Server Admin Certification Training is provided by SQL experts of industry. SQL Server Admin can earn $80k to $90k per annum. Production, information conversion, technical fields, communication, etc. are a few industries making use of SQL Server Admin skills.

SQL Server DBA Certification Training from GangBoard will help you crack MS SQL Server DBA Certification. In this SQL Server DBA Training Course, you will gain mastery over recovery models, backup solutions, server-level roles, server profiler, traces, extended events, etc.. These hands-on exercises will prepare you to work in real-time scenarios.
There is a parcel of chances from many presumed organizations on the planet. The SQL Server DBA advertise is relied upon to develop to more than $5 billion by 2021, from just $180 million, as per SQL Server DBA industry gauges. In this way, despite everything you have the chance to push forward in your vocation in SQL Server DBA Development. GangBoard offers Advanced SQL Server DBA Interview Questions and answers that assist you in splitting your SQL Server DBA interview and procure dream vocation as SQL Server Developer.

Best SQL Server DBA Interview Questions and Answers

Do you believe that you have the right stuff to be a section in the advancement of future SQL Server DBA, the GangBoard is here to control you to sustain your vocation. Various fortune 1000 organizations around the world are utilizing the innovation of SQL Server DBA to meet the necessities of their customers. SQL Server DBA is being utilized as a part of numerous businesses. To have a great development in SQL Server DBA work, our page furnishes you with nitty-gritty data as SQL Server DBA prospective employee meeting questions and answers. SQL Server DBA Interview Questions and answers are prepared by 10+ years experienced industry experts. SQL Server DBA Interview Questions and answers are very useful to the Fresher or Experienced person who is looking for a new challenging job from the reputed company. Our SQL Server DBA Questions and answers are very simple and have more examples for your better understanding.

By this SQL Server DBA Interview Questions and answers, many students are got placed in many reputed companies with high package salary. So utilize our SQL Server DBA Interview Questions and answers to grow in your career.

Q1) MS SQL Server Vs MySQL

Answer: MS SQL Server and  MySQL both are used widely for enterprise database systems
MySql is open-source Real-time Database management system,MS SQL Server is the licensed proprietary software, MySQL is used to update the database,MySQL server is used to maintain the data

Q2) What purpose does the model database server?

Answer: It is used for the template for all kinds of databases which is created for the instance of MS SQL Server. When we started SQL Server it will create TEMPDB is create. This kind of databases should exist on SQL Servers only

Q3) How do you trace the traffic hitting a SQL Server?

Answer: It is the SQL Sever profiler to monitor the traffic. By using this utility we control the traffic on a particular instance.

Q4) What types of replication are supported in SQL Server?

Answer: It is divided into 3 types of replications. They are
Snap: It is used to capture snap short of instance
Merge: It uses the snap replication and it will  act as a central repository serve
Dealing: it will be based on both merge and snap

Q5) Why would you use SQL Agent?

Answer: SQL Agent is a mechanism of job programming, it is used to monitor the job for the events in server

Q6) What happens on checkpoint?

Answer: Checkpoints are one of the data recovery mechanisms in the SQL server. It is used to maintain the modifications and changes  in a cache of the database page

Q7) What is DBCC?

Answer: DBCC Stands for database console commands it is available in 4 types.They are

• Maintenance
• Miscellaneous
• Validation
• Informational

Q8) How can you control the amount of free space in your index pages?

Answer: This will tell the  SQL Server what proportion free house to depart within the index pages once re-indexing.
performance of profit here is some page to splits (where the SQL Server needs to rows copy from the one index page associate to other to the different} to create space for the row inserted) as a result of there is space for growth engineered into the index.

Q9) Why would you call Update Statistics?

Answer: It is employed to force the computation of question optimization statistics for a table or indexed read.
Query optimization statistics area unit mechanically re-computed, but it is some times, a query might benefit from update those kinds of statistics to more frequently, Beware although that re-compute the question statistics are causes to queries to be re-compiled.

Q10) What is a correlated sub-query?

Answer: It is the nested query which is linked to the outer side query. To the instance, we can say that needed to search out all the workers in WHO they do not enter time for the whole week.

Q11) What authentication modes does SQL Server support?

Answer: SQL Server supports Windows Authentication and mixed-mode. Mixed-mode permits you to use each 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 as sa.

Q12) Explain about your SQL Server DBA Experience.

Answer: This is a generic question typically asked by several interviewers. Explain what ar the various SQL Server Versions you have got worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the solution that lets the asker skills appropriate ar you for the position to that you’re being interviewed.

Q13) What are the different SQL Server Versions you have worked on?

Answer: It is depending the candidate worked on versions, Mainly SQL servers are 2000,2008,2005, SQL Server 7

Q14) What are the different types of Indexes available in SQL Server?

Answer: It is mainly divided into two types. They are  Non-Clustered Indexes and Clustered

Q15) What is the difference between Clustered and Non-Clustered Index?

Answer: When the clustered index is formed on a table, the data pages are arranged according to its key. There will solely be a Clustered index on a table.
In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There will multiple non-clustered indexes on one table.

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

Answer: Below is the list

• Database Snapshots
• Dynamic Management Views
• Resource Database
• SQL Server Integration Services
• Database Partitioning
• System Catalog Views
• Support for Analysis Services on a Failover Cluster.

Q17) What are the High-Availability solutions in SQL Server and differentiate them briefly.

Answer: Log Shipping, Database Mirroring, Failover agglomeration and Replication are the features  High-available in SQL Server.

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

Answer: The job activity monitor displays the present standing of all the roles in the instance. Choose the actual job that unsuccessful, right-click and choose view history from the drop-down menu. The execution history of the duty is displayed and you will select the execution time (if the duty unsuccessful multiple times throughout the constant day). There would info like the time it took to execute that Job and details concerning the error occurred.

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

Answer: 1433
Check Out SQL Server DBA Tutorials

Q20) what number files will a piece of information contain in SQL Server? How many forms of information files exist in SQL Server? How many of those files can exist for a single database?

Answer:

1. A piece of information will contain most of thirty-two,767 files.
2. There are Primarily 2 types of data files Primary data file and Secondary data file(s)
3. There are just one Primary file and multiple secondary information files as long as the total # of files is a smaller amount than thirty-two,767 files

Q21) What is DCL?

Answer: DCL stands for Data Control Language.

Q22) What are the commands used in DCL?

Answer: RANT, DENY and REVOKE.

Q23) What is Fill Factor?

Answer: Fill issue could be a setting that’s applicable to Indexes in SQL Server. The fill issue price determines what proportion information is written to the AN index page once it’s created/rebuilt.

Q24) What is the default fill factor value?

Answer: By default, the fill issue price is about to zero.

Q25) Where do you find the default Index fill factor and how to change it?

Answer: The easiest thanks to noticing and alter the default fill issue price are from Management Studio, right-click the SQL Server and choose properties. You can change to the desired value there and click OK to save the changes.
The other possibility of viewing and dynamic  this price is victimization

Q26) What is a system database and what is a user database?

Answer: System databases area unit the default databases that area unit put in once the SQL Server is put in. Basically, there area unit four system databases: Master, MSDB, TempDB, and Model. It is highly recommended that these databases are not modified or altered for the smooth functioning of the SQL System.
User info could be info that we have a tendency to produce to store information and begin operating with the info.

Q27) What are the recovery models for a database?

Answer: There area unit three recovery models on the market for info. Full, Bulk-Logged and straightforward area unit the 3 recovery models on the market.

Q28) What is the importance of a recovery model?

Answer: Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to own a token or no information loss, selecting the complete recovery model could be a good selection. Depending on the recovery model of info, the behavior of database log file changes. I would suggest you scan a lot of material on log backups and log file behavior and then on to grasp comprehensive.

Q29) What is Replication?

Answer: Replication could be a feature in SQL Server that helps America publish info objects and information and duplicate (replicate) it to at least one or a lot of destinations. It is typically thought of joined of the High-Availability choices. One of the advantages of Replication is that it can be configured on databases which are in simple recovery model.

Q30) What the different types of Replication and why are they used?

Answer: There area unit essentially three sorts of replication: photograph, Transactional and Merge Replication. The type of Replication you decide on depends on the necessities and/or the goals one is attempting to attain. For example, Snapshot Replication is useful only when the data inside the tables doesn’t modification oftentimes and also the quantity of information isn’t large, such as a monthly summary table or a product list table, etc. Transactional Replication would helpful once maintaining a duplicate of a transactional table like sales order tables etc. Merge Replication is a lot of helpful just in case of remote / distributed systems wherever the info flow is from multiple sites, for example, sales done at a promotional event that might not be connected to the central servers forever.

Q31) What the different components of Replication and what is their use?

Answer: The 3 main elements in the Replication area unit Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is chargeable for distributing the info objects to at least one or a lot of destinations. The subscriber is that the destination wherever the publisher’s knowledge is copied/replicated.

Q32) What are the different Topologies in which Replication can be configured?

Answer: Replication is often designed in any topology relying keeping visible of the quality and therefore the work of the complete Replication. It can be any of the following:
Publisher, Distributor, and Subscriber on the same SQL Instance.
Publisher and Distributor on constant SQL Instance and Subscriber on a separate Instance.
Publisher, Distributor, and Subscriber on individual SQL Instances.

Q33) 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?

Answer: 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.

Q34) What are the different Authentication modes in SQL Server and how can you change authentication mode?

Answer: SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred to as Mixed Mode.

Q35) What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

Answer: On SQL Server 2005, installing the SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or on top of this has modified, we would need to install separately on all the nodes. 2 times if it’s a two-node cluster or three times in an exceedingly three-node cluster so on.

Q36) What is meant by Active-Passive and Active-Active clustering setup?

Answer: An Active-Passive cluster may be a failover cluster designed in an exceeding manner that just one cluster node is active at any given time. The other node, called the Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point in time. That is, one Instance of SQL Server is running on every of the nodes always; once one amongst the nodes encompasses a failure, both the Instances run on the only one node till the unsuccessful node is remarked (after fixing the difficulty that caused the node failure). The instance is then unsuccessful over back to its selected node.

Q37) List out a number of the wants to setup a SQL Server failover cluster.

Answer: Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

Q38) What is Transparent Data Encryption?

Answer: Introduced in SQL Server 2008 clear coding|encoding|encryption} (TDE) may be a mechanism through that you’ll defend the SQL Server info files from unauthorized access through encryption. Also, TDE will defend the info backups of the instance on that TDE was setup.

Q39) Does Transparent Data Encryption provide encryption when transmitting data across the network?

Answer: No, clear cypherion|encoding|encryption} (TDE) doesn’t encrypt the info throughout transfer over a communication.

Q40) What are the operating modes in which Database Mirroring runs?

Answer: Database Mirroring runs in two operational modes High-Safety Mode and superior Mode.

Q41) What is the difference between the 2 operating modes of Database Mirroring (mentioned in the above answer)?

Answer:

• High-Safety Mode is to make sure that the Principal and reflected info area unit synchronal state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
• High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a small likelihood of knowledge loss and conjointly the reflected info may be insulating material behind (in terms being up so far with the Principal database) .

Q42) once setting Replication, is it potential to own a Publisher as sixty-four Bit SQL Server and Distributor or Subscribers as a thirty-two Bit SQL Server.

Answer: Yes, it’s potential to own numerous configurations in a very Replication atmosphere.

Q43) what’s the distinction between dropping info and taking info offline?

Answer: Drop info deletes the info alongside the physical files, it’s unacceptable to bring back the info unless you have got a backup of the info. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.

Q44) Which autogrowth database setting is good?

Answer: Setting associate autogrowth in multiples of MB could be a higher possibility than setting autogrowth in proportion (%).

Q45) What area unit the various kinds of info compression introduced in SQL Server 2008?

Answer:  Row compression and Page Compression.

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

Answer: In-place upgrade and Side-by-Side Upgrade.
Explore SQL Server DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Q47) On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Answer:  Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, on the right-hand side of the console, the column“Owner” gives us the information of the node on which the SQL Server group is currently active.

Q48) How do you open a Cluster Administrator?

Answer:  From begin -> Run and sort CluAdmin (case insensitive) and therefore the Cluster Administrator console is displayed otherwise you can even head to begin -> All Programs -> body Tools -> Cluster Administrator.

Q49) thanks to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?

Answer:  In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose to Take Offline.

Q50) What are the different ways you can create Databases in SQL Server?

Answer:

• T-SQL; Create Database command.
• Using Management Studio
• Restoring a database backup
• Copy Database wizard

Q51) once setting Replication, can you have a Distributor on SQL Server 2005, Publisher of SQL Server 2008?

Answer:
No, you can not have a Distributor on a previous version than the Publisher.
List of Related Microsoft Certification Courses:

• SSIS SQL Server
• SSRS Power BI
• SSAS
• BizTalk Server
• SCCM Team Foundation Server
• SharePoint BizTalk Server Adminis