MSSQL Interview Question Clustering- Part2



Will Indian Rupee Cross 55??

Q: What will happen if you try to start the SQL service on the passive node?
Ans: This will start because error log, mdf and ldf files are possessed by active node.

Q: How exactly the SQL service starts on any node?
Ans: Below is the list of all the steps in error log at the of SQL Service starting on one node, all the logs are self-explanatory-

1-SQLServerAgent service successfully stopped.
2-[sqsrvres] OnlineThread: asked to terminate while waiting for QP.
3-17148 :SQL Server is terminating due to 'stop' request from Service Control Manager.
4-17104 :Server Process ID is 9344.
5-This instance of SQL Server last reported using a process id of 6804 at 1/23/2011 10:39:34 AM (local) 1/23/2011 6:39:34 PM (UTC).
6-SQL Server is starting at priority class 'normal'(16 CPUs detected).
7-SQL Server configured for thread mode processing.
8-17125 :Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
9-19013 :SQL server listening on 10.230.80.22: 1433.
10-19013 :SQL server listening on TCP, Shared Memory, Named Pipes.
11-17126 :SQL Server is ready for client connections
12-3455 :Analysis of database 'msnupst_sys' (5) is 100% complete (approximately 0 more seconds)
13-3455 :Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
14-SQLServerAgent service successfully started.
15-Using 'xpstar.dll' version '2000.80.2282' to execute extended stored procedure 'xp_instance_regread'.

16-MS DTC started with the following settings (OFF = 0 and ON = 1):
/*******************
  Security Configuration:
      Network Administration of Transactions = 1,
      Network Clients = 0,
      Inbound Distributed Transactions using Native MSDTC Protocol = 1,
      Outbound Distributed Transactions using Native MSDTC Protocol = 1,
      Transaction Internet Protocol (TIP) = 0,
      XA Transactions = 0
  Filtering Duplicate events = 1

17-The MS DTC service is stopping. on previous node


Q: If you run ipconfig on passive node what will you get?
Ans: Only the IP address of the passive node.

Q: If you run ipconfig on active node what you will get?
Ans: You will get 4 IP addresses--
    1- Ip adress of node
    2- Cluster IP
    3- SQL server IP
    4- MSDTC IP


Reader of This blog also showed their interest in following blogs:
  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers
Regards,
http://tuitionaffordable.webstarts.com

Comments

Popular posts from this blog

Understand Query Execution Plan- Index Scan, Index Seek And Key Lookup

MSSQL Interview Question Clustering- Part1

DBCC CheckDB The operating system returned error 21(The device is not ready.)