Sunday, April 13, 2014

Microsoft SQL DBA Interview Questions with FIS

FIS Global, Bangalore

1. What is the purpose of Covering index? how it is different from other indexes?


2. Tell All Fixed Server Roles

Ans: bulkadmin, dbcreator, diskadmin, processadmin, public, securityadmin, serveradmin, setupadmin, sysadmin

(Instance>Security>Server Roles)

3. Tell all Fixed Database Roles

Ans: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_dbowner, db_securityadmin, public

(Instance>Databases>Any Database>Security>Roles>Database Roles>)

4. What permissions should you have to kill a process?
' Processadmin' Fixed Server role

5. What is meant by Orphaned users? Complete understanding about it? What link is getting break while moving databases from one physical server to another?

6. How to fix and resolve orphaned users? 

7. Why should we fix orphaned users?

8. What is QUORUM? where this concepts is useful? How is it useful in Mirroring and Clustering?

http://en.wikipedia.org/wiki/Quorum
http://technet.microsoft.com/en-us/library/ms189902.aspx
http://technet.microsoft.com/en-us/library/hh270280.aspx
http://technet.microsoft.com/en-us/library/hh270278.aspx

9. Did you work on Clustering?
10. What is QUORUM in Clustering and how it works?
11. Which Server and user role permission you should have to run a job?


12. What is the purpose of 'Public' role under both Server Level and User Level roles?

13. Tell me about 10 new features introduced in 2005, 2008, 2008 R2, 2012 and 2014?

14. Tell DBCC commands

Ans: http://technet.microsoft.com/en-us/library/ms188796.aspx

15. How do you check, if you have enough memory in your server? Which object you check in 'Performance Monitor'?

16. How do you trace Deadlocks?

17. What is the difference between 1222 and 1204 Trace Flags?

18. What are the isolation levels and explain about them?




ITC InfoTech Questions

1. What are the prerequisites for DB Mail?
2. What is the purpose of Service Broker?
3. What is Virtual Log file?
4. What is 3GB Switch (AWE) and it's purpose?
5. What is DB Mail and SQL Mail?
6. What do you in Query Execution Plan?
7. How do you restrict a particular user to execute only few stored procedures?


Virtual Memory?

Processes use Secondary memory as RAM when RAM is running low. This is alswo known as Paging file.

A paging file is an area on the harddisk that windows uses as if it were RAM.



Installation Problems?




3GB switch (AWE)?

To enable 32 Bit Servers to use more than 3GB RAM...


Query Execution plan purpose?

It shows the execution cost with details such as Index/Table Scans and Key lookups.


Prerequisites for using DB mail?


SQL Server Service Broker is enabled by default in msdb database but may be deactivated if you attached an msdb database. Enabling Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.


Virtual Log Files



Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.


The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files). A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).


Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files. If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files. For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.


As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.



Service Broker


Introduction


With Service Broker, a feature in Microsoft SQL Server 2005, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.


To better understand Service Broker, familiarity with the key concepts of queues, dialogs, conversation groups, and activation is helpful. These are discussed briefly in this section.


Queues


Service Broker uses queues to provide loose coupling between the message sender and the message receiver. The sender can use the SEND command to put a message in a queue and then continue on with the application, relying on Service Broker to ensure that the message reaches its destination.


Queues permit a lot of scheduling flexibility. For example, the sender can send out multiple messages for multiple receivers to process in parallel. The receivers might not process the messages until long after they were sent, but because incoming messages are queued, the receivers can process them at their own rate and the sender doesn't have to wait for the receivers to finish before continuing.


Dialogs


Service Broker implements dialogs, which are bidirectional streams of messages between two endpoints. All messages in a dialog are ordered, and dialog messages are always delivered in the order they are sent. The order is maintained across transactions, across input threads, across output threads, and across crashes and restarts. Some message systems ensure message order for the messages sent or received in a single transaction but not across multiple transactions, making Service Broker dialogs unique in this regard.


Each message includes a conversation handle that uniquely identifies the dialog that is associated with it. For example, an order entry application might have dialogs open simultaneously with the shipping application, the inventory application, and the billing application. Because messages from each application have a unique conversation handle, it's easy to tell which application sent each message.


Conversation Groups


Service Broker provides a way of grouping all the dialogs that are used for a particular task. This method uses conversation groups. In our previous order entry example, all the dialogs associated with processing a particular order would be grouped into a single conversation group. The conversation group is implemented as a conversation group identifier, which is included with all messages in all dialogs contained in the conversation group. When a message is received from any of the dialogs in a conversation group, the conversation group is locked with a lock that is held by the receiving transaction. For the duration of the transaction, only the thread that holds the lock can receive messages from any of the dialogs in the conversation group. This makes our order entry application much easier to write because even though we use many threads for scalability, any particular order is only processed on one thread at a time. This means we don't have to make our application resilient to problems that are caused by the simultaneous processing of a single order on multiple threads.


One of the most common uses for the conversation group identifier is to label the state that is associated with a particular process. If a process involves many messages over time, it probably doesn't make sense to keep an instance of the application running through the whole process. For example, the order entry application will scale better if, between messages, any global state that is associated with processing an order is stored in the database and retrieved when the next message associated with that order is received. The conversation group identifier can be used as the primary key in the state tables to enable quick retrieval of the state associated with each message.


Activation


You use the activation feature of Service Broker to specify a stored procedure that will handle messages destined for a particular service. When messages arrive for a service, Service Broker checks whether there is a stored procedure running that can process the messages. If there isn't a running message-processing stored procedure, Service Broker starts one. The stored procedure then processes messages until the queue is empty, after which it terminates. Moreover, if Service Broker determines that messages are arriving faster than the stored procedure can process them, it starts additional instances of the stored procedure until enough are running to keep up with the incoming messages (or until the configured maximum number is reached). This ensures that the right number of resources for processing incoming messages are always available.


Why Use Asynchronous, Queued Messaging?


Queues enable the flexible scheduling of work, which can translate to big improvements in both performance and scalability. To see how, go back to the order entry example. Some parts of an order must be processed before the order can be considered complete, such as the order header, available to promise, and order lines. But other parts realistically don't have to be processed before the order is committed; for example, billing, shipping, inventory, and so on. If the "delayable" piece of the order can be processed in a guaranteed but asynchronous manner, the core part of the order can be processed faster.


Asynchronous messaging can also provide opportunities for increased parallelism. For example, if you need to check the customer's credit and check availability for ordered items, starting both processes simultaneously can improve overall response time.


Queuing can also enable systems to distribute processing more evenly, reducing the peak capacity required by a server. For example, a typical incoming order rate might look something like this:
ms345108.sql2k5_servbroker_fig1(en-US,SQL.90).gif
Figure 1
There is a peak at the beginning of the day and another one at the end. If each order is entered into the shipping system as it is created, the shipping system's load would look like this:
ms345108.sql2k5_servbroker_fig2(en-US,SQL.90).gif
Figure 2
The afternoon peak is bigger because that's when the shipping paperwork is done for outgoing shipments. If the shipping system is connected to the order entry system with a queue, the peaks can be leveled by shifting some of the work to the slack incoming order times:
ms345108.sql2k5_servbroker_fig3(en-US,SQL.90).gif
Figure 3

Why Use Transactional Messaging?
Service Broker supports transactional messaging, which means that messages are sent and received as transactions. If a transaction fails, the message sends and receives are rolled back, and don't take effect until the transaction has processed the messages successfully and committed.

Transactional messaging makes programming a messaging application much more straightforward because you can freely scatter sends and receives wherever they make sense and nothing happens until the transaction commits. If the transaction gets rolled back, none of the sends take place, and the messages that were received go back in the queue so they will be received and processed again.
How Service Broker Solves the Hard Problems in Messaging
Messaging applications have been around for a long time, and there are compelling reasons to build them. So why aren't there more of them? The answer is that messaging applications are hard to get right. SQL Server Service Broker, however, solves some of the most difficult messaging application issues: message ordering, coordination, multithreading, and receiver management.
Message Ordering
In traditional reliable messaging applications, it's easy to get messages delivered out of order. For example, application A sends messages 1, 2, and 3. Application B receives and acknowledges 1 and 3, but experiences an error with 2, so application A resends it. However, now 2 is received after 3. Traditionally, programmers dealt with this problem by writing the application so that order didn't matter, or by temporarily caching 3 until 2 arrived so the messages could be processed in order. In contrast, Service Broker handles this transparently, so all messages in a dialog are received in the order sent, with no gaps in the message sequence.
A related problem is duplicate delivery. In the previous example, if application B received message 2, but the acknowledgement message back to application A was lost, application A would resend 2 and application B would now receive 2 twice. Again, Service Broker ensures that messages are never delivered twice, even if the power is lost in the middle of a transaction.
Coordination
Messages are generally stand-alone entities, which can make it difficult to determine which conversation a message came from. For example, you may send thousands of messages to an inventory service requesting inventory updates. The inventory service may respond to some of these messages almost immediately and take a very long time to respond to others, making it difficult to decide which response message corresponds to which inventory request.

With Service Broker, by contrast, both the dialog handle and the conversation group identifier are included with every message, making it very easy to determine the order and request that each response goes with. (Some messaging systems have a correlation ID you can set to make this determination, but with dialogs, this is not necessary.)
Multithreading
One of the most difficult issues in a messaging application is making a multithreaded reader work correctly. When multiple threads are receiving from the same queue, it's always possible for messages to get processed out of order, even if they are received in order. For example, if a message containing an order header is received by thread A and a message containing an order line is later received by thread B, it's possible that the order line transaction will attempt to commit first and fail a referential constraint because the order doesn't exist yet. Although the order line message will roll back until the order header exists, it is still a waste of resources.
Service Broker solves multithreading issues by putting a lock on the conversation group when a message is read, so that no other thread can receive associated messages until the transaction commits. Service Broker makes multithreaded readers work simply and reliably.
Receiver Management
In many reliable messaging systems, the application that receives messages from a queue must be started before messages are received. In most cases, the user must decide how many application instances or threads should be running in each queue. If this is a fixed number and the message arrival rate varies, there are either too many or too few queue readers running most of the time.
Service Broker solves receiver management issues by activating queue readers as required when messages arrive. Moreover, if a reader crashes or the system is rebooted, readers are automatically started to read the messages in the queue. Service Broker does many of the same kinds of application management tasks that are typically handled by a middle-tier transaction-processing monitor.

Why Build Messaging into the Database?
Why is Service Broker part of the database engine? Wouldn't it work as well if it were an external application that used the database to store its messages? There are several reasons why the database is the right place for Service Broker to be.

Conversation Group Locking
Service Broker makes multiple queue readers possible by locking the conversation group; however, locking the conversation group with normal database commands is almost impossible to accomplish efficiently. Service Broker accordingly uses a new kind of database lock, and only Service Broker commands understand this lock type.
Remote Transactional Receive Handling
Some messaging systems restrict transactional messaging to receiving applications that are running on the same server as the queue. Service Broker, by contrast, supports remote transactional receives from any server that can connect to the database.
Common Administration
One of the issues with transactional messaging systems is that if the messages are stored in a different place than the data, it's possible for the message store and the database to get out of synch when one or the other is restored from backup. With a single database for both messages and application data in Service Broker, this is very hard to get wrong. When your data, messages, and application logic are all in the database, there is only one thing to back up, one place to set up security, and one thing to administer.
Direct Sends to Receive Queue
Because Service Broker is integrated into the database engine, a message that is addressed to another queue in any database in the same SQL Server instance can be put directly into the receive queue, bypassing the send queue and greatly improving performance.
Common Language Support

The messaging part of an application and the data part of an application use the same language and tools in a Service Broker application. This leverages the developer's familiarity with Microsoft ActiveX Data Objects (ADO) and other database programming techniques for message-based programming. With the CLR (common language runtime) stored procedures available in SQL Server 2005, stored procedures that implement a Service Broker service can be written in a variety of languages and still take advantage of the common administration benefits of Service Broker.
Single-Connection Execution Capability
Service Broker commands can be executed over the same database connection as the rest of the Transact-SQL used by the application. Using a single connection means that a messaging transaction doesn't need to be a distributed transaction, as it would have to be if the messaging system were external to the database.

Enhanced Security
Because Service Broker messages are handled internally by the database, the access permissions of the message sender can be easily checked against database objects. If the message system were an external process, a separate database connection would be required for each user who is sending messages. Having the same identity for the database and the messaging system makes security easier to set up correctly.

Transmission Queue Visibility Across Databases
Because Service Broker runs in the context of the database instance, it can maintain an aggregate view of all messages that are ready to be transmitted from all databases in the instance. This capability enables each database to maintain its own transmission queues for backup and administration while still maintaining fairness in resource usage across all the databases in the instance, something that would be very difficult, if not impossible, for an external messaging manager to do.

Conclusion
The unique features of Service Broker and its deep database integration make it an ideal platform for building a new class of loosely coupled services for database applications. Service Broker not only brings asynchronous, queued messaging to database applications but significantly expands the state of the art for reliable messaging.

Question from Other Friends

1. What is out of synch state for a Database (file)?

Ans:


2, What do you do if MSI file Error occurs while installing patch in 2008? What about 2005 behavior?

In 2005, ResourceDB can be replaced to revert the damage with Sercive Pack installation Failure

3. How do you trace Dead Locks and solutions to prevent it?
4. What is the port no for different instances in your Server (multiple instances are installed on one PC)?

 Go to ' SQL Server Configuration Manager'
For Default Instance TCP Porn# 1433

for other instances 'TCP Dynamic Ports' values is set to '0', i.e. it works on Dynamic Ports.


SQL Version Query:

Select @@version

Select serverproperty(‘productversion’)

Select serverproperty(‘edition’)

select SERVERPROPERTY('productlevel')

No comments:

Post a Comment