SQL Server Interview Questions

1.what is use of SQL browser service ?  if Sql browser is down will sql server run ?

2.How many authentication modes in SQL Server ? and whom we give  Windows authentication 
    and to whom we give SQL Authentication ?

3.How to restrict Wintel admins to access SQL Server ? 
   (My client is saying only SQL team should have access no one else especially Wintel/Windows
     Admins  bcz there is a chance of misusing/removing any logins/DB's by wintel admins )

4. How many types of backups? 

5.When we create a DB what are the files will be created ? 

6. can we add more than one log file and mdf file to database ?

7. what is the use of MSDB system database ?

8. what is the differences bw SQL server 2000 and 2005 ?

9. when you upgrade  SQL server from 2000 to 2005 what is the process/ steps you followed ?
  
10. Client raised P1(high priority) incident that one of the application is  running very slowly how    
     will you identify the reason (or) where you see the logs and how to  solve that issue?
  (Here there are no transactions locked on this application ?

11. How/where will you identify the  Tcp/Ip network error for failed backup job ?   

12. when you are applying service packs on Cluster what are the steps/process you followed ?

13. How load balancing works in Clustering when Instance1 is installed in NodeA  and Instance2 is
      installed in NodeB ?

 14.  Do you know how to rebuild indexes ?
15. How Query optimization will works ?
16. Do you know about SQL Express ? and SSIS ? 
17. what is the Incident management and problem management ?