MySQL ARCHITECTURE INTERVIEW QUESTIONS.

1) What is SQL Server?

Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.

2)   What is the Versions Available in SQL Server?

SQL Server was developed by Microsoft in 1987 and the versions are 6.0, 6.5, 7.0, 8.0(2000), 9.0(2005), 10(2008),2008r2.

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

4) What is the Difference between SQL Server 2005 and 2008?

5)   What is the Difference between SQL Server 2008 and 2008 R2 ?

6)   What are the Editions available for SQL Server 2005?

SQL Server 2005 is available in 32-bit and 64-bit editions.

SQL Server 2005 Enterprise Edition (32-bit and 64-bit)

SQL Server 2005 Standard Edition (32-bit and 64-bit)

SQL Server 2005 Work group Edition (32-bit only)

SQL Server 2005 Developer Edition (32-bit and 64-bit)

SQL Server 2005 Express Edition (32-bit only)

7 ) What are the tools are available in SQL Server 2000 and 2005?

Sl	2000	2005
1	Enterprise manager & Query Analyzer	Management Studio
2	Network Client Utilities	Configuration manager
3	SQL Profiler	Profiler
4	Service manager	Surface area configuration
5		Reporting Services Configuration
6		Database Tuning Advisor
7		Command Prompt Utilities

8 ) What are the new features available in SQL Server 2005?
  (a) Online Restore (b) Online Index Operations (c) Database snapshot (d) Fast recovery(e) Mirrored Backups (f) Database mirroring (g) Read committed snapshot and Snapshot isolation level (h) Data partitioning (i) Dynamic management views

9 ) What are services are created while installing SQL Server? 
       There are three services are created in SQL Server 2005.

     a)  SQL server service:- It is responsible for running Databases, System Stored Procedures, System Indexes, User Login information and Passwords 

    b)  SQL server agent service:- It is responsible for running Jobs, Alerts, Notifying the operators 
    c)   Distributed Transaction Coordinator:- It is responsible for moving data between different physical servers (Clustering or Replication)

10 ) How many ways to run services?
       The following methods can use to run services
       Methods:-

        1)    At task pane > Right click on server and click start / stop

        2)    Management Studio > Right click on Server and click start / stop / restart

        3)    Surface Area Configuration

        4)    Command Prompt > Net start/stop mssqlserver 
      5)    SQL Server configuration manager > Right click on server and click start / stop / restart.

11 ) What is Windows Authentication?

When Windows authentication is used to connect SQL Server, Microsoft Windows is completely responsible for authenticating the client by identifying its windows user account.

12 )What is SQL Server Authentication?

When SQL Server authentication is used, SQL Server authenticates the client by comparing user name and password with user names and passwords maintained within SQL Server.

13 ) What is the difference between Windows Authentication and SQL Server authentication?

1)    Windows authentication is highly secure than SQL Server authentication why because we need not provide any login details. So the chance of tracing login details from code by the unauthorized persons will be less. Windows authentication uses the default windows login credentials.

2)    Windows authentication will uses the credentials of the currently logged user to login in to the SQL Server database. Your application need not to supply the user name and password.

3)    For SQL Server authentication we have to supply the SQL database user name and password. That will be used to get the privileges and right of the user.

14 ) What is AWE? How configure Dynamic Memory Allocation? (i)

Generally SQL Server reserves only a small portion of memory. Sometimes reserved memory is not sufficient to SQL server and required additional memory. AWE is used to support very large amounts of physical memory. When AWE is enabled the operating system dynamically allocates memory to SQL Server based on the Min server memory and max server memory settings. (1) Win. Server 2003 SE supports utpo 4GB (2) EE upto 32GB. (3) DC upto 64GB)

To Configure AWE:- Right click on instance > Properties > Memory > Enable AWE > Set Min and Max memory etc.

Note:- AWE is not needed and cannot be configured on 64-bit operating systems.

15 )What is Linked Server? How to connect Linked server? How to test linked Server? (i)

One server connected with another server to execute queries against OLE DB data sources on remote servers.

OLEDB Provider:- An OLE DB provider is a DLL that manages and interacts with a specific data sources such as SQL Server 7.0, Access, Excel, ODBC, Oracle, DB2, Local file system, Exchange Server etc. SQL Native Client (PROGID: SQLNCLI) is the official OLE DB provider for SQL Server.

To connect linked server using OLE DB provider:-

sp_addlinkedserver @server=’servername’, @srvproduct=’SQL Server/Oracle’

Tests the connection to a linked server:-

sp_testlinkedserver <servername>

16 ) What is purpose of Registered Servers?

Registering a server to store the server connection information for future connections.

17 )What do you mean by Collation?

      Collation is basically the sort order. There are three types of sort orders

(1) Dictionary case sensitive , (2) Dictionary case insensitive, (3) Binary

18  ) What are the protocols used in networking? What is the default port number of TCP/IP? (i)

The protocols used in networking are TCP/IP, NAMED PIPPES, VIA, SHARED MEMORY. The default port no of TCP/IP is 1433.

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

1)    Select @@version

2)    Click on Server > Summary Reports > Server Dashboard > configuration details > See product version.

20) What is blocking? How to identify and resolve the blockings? ®

Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait, block on the first.

Determine Blocking sessions:- Activity Monitor, sp_who2, sp_lock, sys.sysprocess, sys.dm_exec_requests, sys.dm_os_waiting_tasks

Resolve Blocking Session:- (1) Right click on session and Kill in Activity Monitor (2) Kill Session_id

21 ) What is Deadlock? ®

A deadlock occurs when users try to place exclusive locks on each other’s objects.

Ex:- User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on table2, and User2 tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.

            The Database engine picks one of the victim (users) and kills their query and send a error message to users “You are the victim of a deadlock and try again later”.

Deadlock Information Tools

1)  Trace Flags:- DBCC TRACEON (1204) & DBCC TRACEON (1222). When these trace flags is enabling, the deadlock information captured by the SQL Server error log.

2)  Deadlock graph event in SQL Profiler:- SQL Server Profiler graphically representation of tasks and resources involved in a deadlock. (Lock:Deadlock and Lock:Deadlock chain events in the Locks events)

3)  System View:- We can find the blocking sessions by writing the following query

Select session_id, status, blocking_session_id from sys.dm_exec_requests

where blocking_session_id > 0

Resolving Deadlock:- After find the session causing the problem we can use KILL command.

      > KILL process_id

22 ) What are the Types of Locks? Explain each? (i)

There are 7 locks types are available in SQL Server 2005.

a)    Shared Lock:- Shared locks allows concurrent transactions to read (SELECT) the same resource at the same time, but it does not allow any transaction to modify that resource.

b)    Update Lock:- Only one transaction at a time can obtain an update lock on a resource. This lock helps avoid deadlocks for concurrent updates in the case when repeatable read or serializable isolation levels are used.

c)     Exclusive Lock:- When a exclusive lock is held on a resource by a transaction, no other transaction can read or modify that resource. (Others may read the data without blocking on the exclusive lock if a locking hint, read uncommitted isolation level, or read committed snapshot isolation level).

d)    Intent Lock:- Used to protect low level resource locks such as page and row locks that may be needed by a transaction.

e)    Schema:- Used when a table DDL operation is performed and this lock is held, no users can access the table.

f)     Bulk Update:- Used when bulk copying data into a table with TABLOCK hint is specified. This lock allows multiple threads to bulk copy data concurrently into the same table.

g)    Key-Range:- This protects the rows so that the transaction can read repeatable data later in the transaction.

23 ) How can find the locks on a resource?

We can use sys_dm_tran_locks system view (sp_lock can use in previous versions)

Ex:- Select resource_type, resource_mode, request_status, request_session_id from sys.dm_tran_locks.

24 ) What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).

25 )What are the components can be installed in SQL Server 2005?

We can install the following components with Microsoft SQL Server 2005

a)    SQL Server Database Engine

b)    Analysis Services

c)     Reporting Services

d)    Notification Services

e)    Integration Services

f)     Management Tools

g)    Documentation and Samples

26 ) What is the process of Installation?

a)  Prepare Your Computer to Install SQL Server 2005:- To prepare your computer for SQL Server 2005, review hardware and software requirements, System Configuration Checker requirements and blocking issues, and security considerations.

b)  Install SQL Server 2005:- To install SQL Server 2005, run Setup using the SQL Server 2005 Installation Wizard or install from the command prompt. You can also add components to an instance of SQL Server 2005, or upgrade to SQL Server 2005 from a previous SQL Server version.

c)  Configure Your SQL Server 2005 Installation:- After Setup completes the installation of SQL Server 2005, you can configure SQL Server using graphical and command prompt utilities

27 )What is the Virtual Memory? How to assign virtual memory & how much space required for Virtual memory?

A reserved disk space to maintain transactions whenever Memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.

To Set Virtual memory:- Right click on System > Select System Properties > Advanced > Performance Settings > Advanced > Virtual memory > Change > Select directory > Provide min and max values.