SQL SERVER DBA – TOPIC WISE FAQ's

SQL SERVER DBA – TOPIC WISE FAQs

SQL SERVER

1)    What is SQL Server?

2)    What is the Versions Available in SQL Server?

3)    What is the Difference between SQL Server 2000 and 2005?

4)    What are the Editions Available for SQL Server 2005?

5)    What is the Syntax used for find the SQL Server version?

6)    What are the Tools are available in SQL Server 2000 and 2005?

7)    What are the Protocols used in Networking? What is the Default Port number of TCP/IP?

8)    What are Services available in SQL Server?

9)    How many ways to run services?

10)  What is AWE? How configure Dynamic Memory Allocation?

11)  What is Linked Server? How to connect Linked server? How to test linked Server?

DATABASE

1)    What is Database? What are the files created while creating a Database?

2)    Explain about Data files (.mdf, .ndf, .ldf) (File structure)?

3)    What is Page and Page size? What is Extent and Extent size?

4)    What is Fill factor?

5)    What is Row size of a Database?

6)    Tell me about System Database and User Databases in 2000 and 2005?

7)    What is Log? What is LSN numbers?

8)    What is Check point?

9)    If Model and MSDB Database will crash what you will do? How to rebuild Master Database?

10)  What is Transaction? What is Transaction (ACID) Properties?

11)  What are Transaction Isolation levels?

12)  Explain about RAID and RAID levels?

13)  Explain about System Databases and Purpose of them?

14)  Orphand Users? How to find Orphand Users and Resolve them?

15)  If a Database in Single User mode?  How to change into Multi-user mode?

16)  Minimal Mode and Single user mode?

17)  What is Database Snapshot? Give the Syntax?

18)  What is Heap table?

19)  What are the Database States and explain them?

20)  How many ways you can move the data or databases between servers and databases?

21)  Syntax for find out the Database ID?

22)  Syntax for find out Logins? Current User?

23)  How to Create Logins and Permissions?

24)  Is it possible to take Backups and Restores of System Databases?

Recovery Models

1)    What is Recovery Model? Benefits of Recovery Models?

2)    Explain Full, Bulk-Logged & Simple Recovery Models?

3)    Which DBs can we use Simple Recovery Model?

4)    In Which Recovery Models Point-in-time recovery is possible?

5)    In System DBs what are the Recovery Models used?

6)    Why in Simple Recovery model we can’t take T.Log backups?

7)    Can we take Diff. backup in Simple recovery Model?

Backups

1)    What is Backups? Types of backups?

2)    What is Use of Backups?

3)    What is Full, Diff, T.Log Bakups? Explain Each?

4)    Can we take Diff. backups without Full backups?

5)    What are the Syntaxes for Backups?

6)    Explain about Tail of Log? Give the syntax?

Restore

1)    What is Restore? What are the options used in Restoration? Explain Each?

2)    In which edition On-line restoration is possible?

3)    What are the Syntaxes for Restore?

4)    What is Point-in-time recovery and use of that?

5)    Explain about STOPAT clause?

Logshipping

1)    What is Log shipping and purpose of Log shipping?

2)    What is Primary Server, Secondary Server & Monitor Server?

3)    What are the Jobs running for Log shipping and explain them?

4)    Requirements for Log shipping?

5)    How to configure Log shipping?

6)    What are permissions required for Log shipping?

7)    In Logshipping which Recovery Models can we used?

8)    Where you monitoring Log shipping and how?

Mirroring

1)    What is Database Mirroring? What are the benefits of that?

2)    What are the prerequisites for Database Mirroring?

3)    What are the Restrictions for Database Mirroring?

4)    Explain about Principal, Mirror and Witness Servers?

5)    In which Operations are running the Operating Modes?

6)    What are the Operating Modes and explain them?

7)    What is the default of Port numbers of Principal and Mirror servers? How to find the Port numbers?

8)    What is End Point? How u create end point?

9)    Which Trace flag is used in Mirroring?

10)  In which Recovery model we can use in Mirroring?

11)  What is Role-switching?

12)  What is the Syntax to stop the Database Mirroring?

13)  How to configure Mirroring?

14)  How to monitoring Mirroring?

15)  What is Hardening?

16)  What is Log buffer?

Replication

1)  What is Replication?

2)  What are the uses of Replication?

3)  Types of Replication and explain each?

4)  Explain about Publisher, Subscriber and Distributer?

5)  Explain about Article, Publication, Subscription?

6)  Which recovery models are used for Replication?

7)  How to monitor the Replication?

8)    What are the Agents available for Replication and explain each?

Maintenance Plans

1)  What is Maintenance Plan?

2)  What the tasks in Maintenance Plans?

PERFORMANCE TUNING

1)    Which Tools are used for Performance Tuning?

2)    How to identify longest running queries?

3)    Reasons for Slow Running Query?

4)    How to analyze query performance?

5)    How to increase Query performance?

6)    Explain about Profiler? What are the Uses of Profiler?

7)    What are the Events is captured SQL Profiler?

8)    Explain about Database Tuning Advisor? What is Workload?

9)    What is Dynamic Management Views?

10)  What is DAC? How to connect DAC?

11)  Explain about Database Console Commands (DBCC)?

12)  What is Stored Procure? What are the types of stored Procedures available in SQL server and explain each?

13)  What is Activity Monitor and use of that? What are the permissions required to

14)  What is Execution Plan and explain it?

17)  What is Trace flag? Give some Trace flags?

15)  What are the common failures occur in SQL Server 2005?

16)  What are the causes of Database Failures?

17)  How to understand Database Engine Errors?

18)  What is Severity level in Event log? At what severity levels are written to the SQL Server log?

19)  What is State Attribute of an error message in Event log? State--1

20)  What are the Causes of Physical Server Failures?

21)  If Log file is full what you will do?

22)  If Data file is full what you will do?

23)  If a Database is under Suspect mode? What you will do?

24)  If a Server performance is slow? How you can troubles shoot a problem?

25)  If a Database Performance is slow? How can you do Performance Tuning?

26)  What are the Performance Issues? Explain them?

MISCELLANEOUS

1)    What are the DBA Roles & Responsibilities?

2)    What are the Day-to-Day activities of DBA?

3)    What is Index? Types of Indexes? Explain Clustered and Non-clustered Index? How many indexes can we create in a Table?

4)    What is Stored Procedure and Function? What are the Difference between SP and Function?

5)    How can we rebuild an Index?

6)    What is a Lock?

7)    What is Blocking? How to identify and resolve the blockings?

8)    What is Deadlock?

9)    What are the Types of Locks? Explain each?