SQL SERVER DBA QUESTIONS ONLY

1. What are the Editions of SQL Server available?
2. What is SQL Server Instance?
3. What is the difference between default and Named Instance?
4. How many SQL Server Instances can be installed on one Server?
5. What is Collation? What Collation will you pick while installing SQL Server?
6. Can you have different Collation on different Databases in SQL Server Instance?
7. Can Instance Level Collation be different from Database Collation?
8. What are the best practices to place data files, log files and tempdb on Storage?
9. What are file groups? How would you relate File groups with Partitioned Table?
10. Is it best practice to have Auto Shrink enable on Database?
11. While installing SQL Server instance , you have used default accounts. Later you want to change to Service account, how would you do that?
12. What is TDE and why do we use it?
13. If TDE is enabled on Database, Do we have to write some special code on application side to access data?
14. What are the steps to enable TDE on database?
15. Is there any performance overhead because of TDE enabled on Database?
16. If you need to restore TDE Enabled database to different server, what steps are involved?
17. What are different types of data compression available in SQL Server?
18. How would you allocate minimum and maximum memory to SQL Server Instance?
19. What is the difference between Offline and Emergency state of a Database?
20. Can you set Database into ReadOnly state?
21. What is CPU Affinity?
22. What is IO Affinity?
23. What is MAXDOP , How do you change it?
24. How would you find out who has dropped database?
25. If you have to find out that how many times the backup of a database is taken, Where will you look for that information?
26. How would you find open transactions in SQL Server?
27. How would you find blocked processes in SQL Server?
28. What is locking in SQL Server? 
29. How would you trace deadlock in SQL Server?
30. Let's say you have lost the password for sa, How would you recover that?
31. What is TUF(Transaction Undo File) file and in which process it is created?
32. What is link Server? Can link server be created for Oracle from SQL Server?
33. What is sparse column? What are the advantages and disadvantages?
34. Which DBCC Commands have you used often as SQL Server DBA?
35. How would you script all SQL Server Agent Jobs?
36. How would you script entire database (Tables, SPs,Views etc.) with data? (Answer)
37. How would you migrate a database from SQL Server Instance to Another SQL Server Instance?
38. You have taken a backup of a database from SQL Server 2008R2, Can you restore this to SQL Server 2005 Instance? (Answer)
39. What will be your strategy when you need to migrate SQL Server 2008 to SQL Server 2012?
40. What is difference between Backup/Restore and Detach/Attach Database?
41. When should we update Statistics on SQL Server Database and why?
42. How would you find out how much space is allocated to Database Log file and how much is used?
43. What is Database Engine Tuning Advisor and where do you have used it?
44. If you need to Kill all processes related to a Database , how would you do that? (Answer)
45. Explain the steps to perform Table Partitioning and best practices?
46. How would you determine the version and Edition of SQL Server Instance you are working on?
47. What are the steps to Restore a SQL Server Database to a Point in Time?
48. How would you shrink the tempdb database in SQL Server?
49. Which built in tool you have used to Monitor SQL Server activity?
50. How would you identify table level locks in SQL Server?
51. What is isolation level and what are four types of them?
52. If you need to query Oracle database from SQL Server, how would you do that?
53. What are the differences between DMV's and DMF's?
54. The tran log of a database has grown huge, How would you shrink the log file?
55. What are the important points you consider before coming up with Backup strategy?
56. You have installed SQL Server Instance on Window 2008 Server. Total Memory of Server is 64 GB but you need to assign only 12 GB to SQL Server Instance, how would you do that?
57. What is the difference Between Index Rebuild and Index Reorganize?
58. How would you create a SQL Server Agent job that only should run on 5th business day of each month?
59. You have provided some permission on a Database to a users, but user is not able to connect to that Database. Where will you see the error details in SQL Server?
60. You have a big database, Before taking the full backup you want to estimate the total time for backup. How would you do that? 
61. You have received a large( 2 GB in size) .sql file, How would you execute that .sql file on SQL Server? (Answer)
62. You need to find out the Job execution history for last ten days, Where will you search for that?
63. You are planning to move database from one server to another server. But there are changes the stored procedures are using some cross database queries. How would you find out if any of the Stored Procedure is using DatabaseTest in definition?
64. Your company has found out that the account SQL Services are running is not what it should be. They want you to change to Service account, How would you do that?
65. What is the differences between differential database backup and Transaction log backup?
66. What permissions will you grant to a users in SQL Server database, so He/She can truncate table?
67. What are DDL Triggers? Where and why have you created them?
68. How would you perform Schema comparison between two databases?
69. You need to perform Data Comparison between two tables,How would you do that?
70. You need to backup all the logins and Jobs, How would you do that?
71. You have installed Name Instance of SQL Server, After installation you realized that you misspelled the instance name. Can you correct the name of SQL Server Instance without re- installation?
72. You have installed SQL Server instance on Windows 2008 (MyServerName\MySQLInstance). If your want to change the computer name to only ServerName, Will SQL Server Instance will or You have to re-install SQL Server Instance?
73. What is DAC (Dedicated Administrator Connection) ? Is it enabled or disabled by default? Why and where we use this feature?
74. You need to create full backup of all the databases from one of the SQL Server Instance every night. If any new database is created that should also become the part of backup process. What would be your approach to perform this?
75. All the sudden, users start complaining that the SQL Server is running slow. What steps you would take to analysis the problem?
76. What is by default SQL Server Agent Job history Retention? If you need to keep two week of history for each of the job what steps will you take when some of the jobs run every five minutes and some run one time a day?
77. If you need to find all the processes running by specific login, How would you do that?
78. How would you find out how many transactions/second SQL Server Instance is performing?
79. How would you find currently running queries on SQL Server Instance?
80. How would you find orphan users and fix them?
81. How would you script user with permission from a SQL Server Database?
82. Your company has a Database for which the Tran Log grows very fast. What strategies you should adopt to keep the Tran log reasonable?
83. What is the difference between Actual Execution plan and Estimated Execution plan?
84. What is the difference between Differential Backup and Full Backup?
85. What are two Server Authentication modes available in SQL Server?
86. If you have installed SQL Server Instance with Windows Authentication Mode and later you want to change to Mix Mode (SQL Server and Windows Authentication Mode), How would you do that and does it require service restart?
87. What is the difference to Restore database "Restore With NoRecovery" and "Restore With StandBy"?
88. What are major differences between SQL Server 2005 and SQL Server 2008 version?
89. What are the major difference between SQL Server 2008/R2 and SQL Server 2012 version?
90. What is the difference between Full Backup and Copy option in Full Backup?
91. How would you find out that How long SQL Server Instance is running?
92. What is index fragmentation?
93. What is the difference between Physical and Logical Reads?
94. Does SQL Server allow duplicate indexes ( indexes with different name but same definition)? If yes, How would you find them and keep only one of them?
95. What is the difference between shrinking and truncating database log file?
96. What are the best practice to configure TempDB?
97. What is Latch? What is the difference between Latch and Lock?
98. What is the difference between Schema and Database?
99. What is the Statistics in SQL Server?
100. What are Trace Flags in SQL Server?
101. What is SQL Server hash operator?
102. What is the difference between 64-Bit and 32-Bit releases of SQL Server?
103. What are the best practices to shrink a database?
104. What is Service Account in SQL Server?
105. What is SQL Server Column Encryption? What are the steps to create Column Encryption?
106. What is the difference between Service Master Key and DataBase Master Key?
107. To See encrypted data by Column Encryption, What permissions are required by user to access data?
108. If you need to Backup and Restore Column Encrypted Database, What steps has to be performed?
109. If we enable Column Level Encryption in SQL Server, Will there any impact on performance?
110. What is Encryption Hierarchy in SQL Server?
111. What is Replication in SQL Server?
112. Why do we use Replication, Provide couple scenarios?
113. What are the types of Replication?
114. When do we use snapshot replication?
115. What exactly merge replication is?
116. Can you schedule replication? And under what circumstances do we schedule replication?
117. Your team needs to know if replication breaks/fails, what exactly would you do to accomplish that?
118. What is re-initializing means in replication
119. Under what circumstances will you re-initialize replication?
120. How would you add new tables in existing replication?
121. Can you explain what would be the replication overhead on production server?
122. Transactional replication is set on production source, article schema changed on source but target is not showing that change, what exactly is the issue?
123. How will you truncate the replicated table?
124. How would delete replicated database?
125. Can you bring replicated database offline?
126. What is orphan replication? And how would you cleanup replication?
127. What is the difference between log shipping and replication?
128. Under what scenarios would you use log shipping?
129.  Have you worked on SQL server 2012?
130. What are prerequisites for SQL server 2012?
131. What is new in SQL server 2012 that's not available in previous versions of SQL servers?
132. What is AlwaysOn in sql server 2012?
133. What are prerequisites of AlwaysOn?
134. What is Availability Group?
135. How many database can be in One Availability Group?
136. What is Listener?
137. Why do we use use Listener?
138. Under what circumstances Availability Group fails over?
139. What is primary replica?
140. What is secondary replica?
141. How many secondary replica can be configured?
142. What are some advantages of using AlwaysOn feature?
143. Can you rename Availability Group?
144. You have setup AlwaysOn and send application team to connect with SQL server using Listener, application can't connect with Listener name, what could be the issue?
145. Can you configure Listener using static port?
146. Listener port is set to 1533, can you connect to SQL server using Listener name?
147. Availability Group is in resolving state, what does it mean?
148. What are SQL server Browsing services?
149. Why do we use SQL server Browsing services?
150. What is recommended configuration of SQL server browsing services?
151. Can you setup replication with AlwaysOn?
152. Can you use primary or secondary replica as a distributor?
153. What are the issues using primary or secondary replica as your main distributor?
154. What is Auto failover of primary replica?
155. What does read intention mean? 
156. What does in-memory Store procedure mean?
157. What are tempdb recommended settings in sql server 2012?
158. SQL server connection is timing out, what would you do to resolve it?
159. Tempdb log is full, how would you shrink tempdb?
160. Can you move tempdb files location without restarting sql server services?
161. What is SQL Server Clustering?
162. Why do we use clustering?
163. What are the prerequisites of sql server clustering?
164. How to add a resource in existing cluster?
165. What is failover? 
166. What is shared storage in SQL server cluster?
167. How would you find out if SQL server failover happened?
168. Applications can't connect to SQL Server after failover, what could be the issue?
169. SQL Server Agent resource is not coming online, what could be the issue?
170. What is the difference between cluster mode and standard mode sql server installation?
171. SQL server cluster installation failed, where would you look the cause of failure?
172. How many SQL server instances can you add in a cluster?
173. Can you find out using SSMS current node for SQL Server Services?
174. What is alias in sql server?
175. How can you find out sql server IP address?
176. How can you find out SQL server Port?
177. What is active-active cluster?
178. What is active-passive cluster?
179. What is cluster aware?
180. Is SSRS cluster aware?
181. Is SSIS cluster aware?
182. Is it recommended to install SSRS and SSIS during cluster mode SQL Server Installation?
183. What is scale out deployment in SSRS?
184. Can you configure SSIS as cluster aware?
185. Some of your external vendors can not connect to SQL server in intranet, what could be the cause?
186. You can't connect SQL server from client machine with in intranet, what could be the cause?
187. Client can connect to SQL server using IP address but can't connect using SQL server instance name, what could be the cause?
188. How to configure SSIS to store packages in MSDB?
189. How to configure SSIS to store package in central location?
190. What is backward compatibility means in SSIS?
191. How to configure SQL server's memory?
192. What are best practices to configure SQL server's memory?
193. If SQL server's memory is not configured, what can happen to the system?
194. System Admin sends you an email that SQL Server services are taking almost all the memory, what steps would you take to resolve it?
195. SQL server lost connection with AD, what will happen to SQL Server? 
196. SQL services are not starting, where would you look for the cause?
197. Can you restore master database?
198. You want to know if SQL services restarted or stopped via email, how would you accomplish that?
199. How do you keep up with SQL server updates?
200. Have you ever installed SQL server patches?
201. What is the latest service pack available in sql server 2012?
202. You are restoring the databases by using SQL Server agent job,How would you find that how much percentage of restoration is completed? (Answer)
203. What is deployment ? Have you been involved in deployment process?
204. If you have to deploy 20 SSIS Package to SQL Server Integration Services, How would you do that?
205. If you have to deploy SSIS Packages to File system, What steps would you follow?
206. What is Version Control? Which software you have used for version controlling?
207. What is Team Foundation Server? Why did you use it?
208. What is Check-In and Check-Out in TFS?
209. What are the best practices when you need to deploy DDL and DML scripts to Database?
210. How would you deploy SSRS report to Report Server? If you have to deploy 100 reports to Report Server how would you do that?
211. Can Data Sources, Data Sets deployed from SSRS Project to Report Server? or You have to create them manually?
212. How would you deploy SSAS Cube to SQL Server Analysis Services Server?
213. Can we deploy more than one Cube in SSAS Database?
214. What are the best practices for SQL Server Change Management?
215. A users has left the organization, How would you drop his login and user name from all databases from SQL Server?