CLUSTERING INTERVIEW QUESTIONS.

1 )What is Clustering?
          Clustering is a technology in which one or more computers work together as a single instance by sharing their resources. Clustering is used mainly for redundancy of the services hosted on it. In other words, in a cluster all the hosts are work together, if one of the host fails all the services hosted on it comes online immediately on to another host with out any downtime. Apart from the redundancy a cluster is used for load balancing also, in this case what ever the load comes to the cluster is shared by all the hosts in it.
2 ) What are the types of Clusters and How do we configure them ?
           Clustering is two types, Active/Active and Active/Passive.
Active/Active : In this scenario all the hosts configured in the cluster takes all the load comes to that cluster, and if any one of the host fails the remaining hosts share the requests to make it online all the time. If you have same copies of application configured on multiple hosts this type of clustering is applicable.
Active/Passive : In this scenario load balancing is not works, as at any point of time only one host receives all the requests, and if any of the host fails the remaining hosts takes those services and make them available online. That means, only one host is active at any point of time and the other one is passive.
3) What are the requirements to configure a cluster and How it works?
There are so many requirements to configure the cluster, but all those requirements are inter dependable on each other. And these requirements (we call them as resources in clustering), are shared between all the hosts, that means these should be available to all the hosts in the cluster. for example, SAN storage resource, the storage which configured in network and is accessible by all hosts .In the same way all the resources are accessible by all the hosts in a cluster. If one of the host fails, all these resources are moved to another host. In detail, at a particular time one host is accessing these resources, at that time those are locked by that particular host, whenever this host fails, the lock was released and those resources are locked by another host. But in active/active clusters these resources have multiple copies as multiple instances on all hosts.
4) what is quorum ?

5) Why Quorum is necessary ?
6) Different types of Quorum in Windows server 2008 ?
7 ) how many ips used in clustering &  what are them?
8). List out some of the requirements to setup a SQL Server failover cluster.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
9). On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Using Cluster Administrator, connect to the cluster and select the SQL Server cluster.  Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.

10). How do you open a Cluster Administrator?

From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.
29. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?
In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.

RESTORE INTERVIEW QUESTIONS

1)    What is Restore? What are the recovery states? Explain Each?

Restore means recover the data from backups. There are three recovery states for Restore.    (1) With Recovery (2) With No-Recovery (3) Standby.

a)  With Recovery:- With Recovery option the Database will comes to Online and get ready to using Database. But further backups cannot restored. Generally while restoring recent (last) T.Log we can choose WITH RECOVERY option.

b)  With No-Recovery:- With No-Recovery option the Databases will comes to restoring mode and further backups can restore, but users can’t access DB. Generally use this option for all backups (except recent log) for restore further backups.

c)   Standby:- With Standby option the Databases will comes to Restoring/Read-only mode. In this mode further (next) backups can restore, but users have read-only access DB. Generally use this option for all backups (except recent log) for restore further backups.

2)    What are the common scenarios to Restoring the Data?

1)  To restore the lost and corrupted data after a system failure

2)  To restore a database onto a development system for user by application developers while developing and testing new code.

3)  To restore a database onto a test system to load test applications and database features.

4)  To restore a database on separate server as a read-only database to perform queries for reports.

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

On-line restoration is possible only in Enterprise edition.

4)    What are the Syntaxes for Restore?

a)  With Recovery:-Restore database dbname from disk = “Path” with recovery

b)  With No-Recovery:- Restore database dbname from disk = “Path” with no recovery

c)   Standby:- Restore database dbname from disk = “Path” with no standby

5)    What is Point-in-time restore and use of that?

Point-in-time restore is used to restore the Database to a particular time just before a failure has occurred or before corrupt data.

To Restore a Database to a Point-in-time:-

STOPAT is used to restore a database to specific time.

6)    Restore Strategy?

1.     First restore recent last full backup

2.     Then restore last Diff. backup

3.     Then restore all Transaction Log backups since recent Full/Diff backups.

RECOVERY MODEL INTERVIEW QUESTIONS.

Recovery Models

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

“A recovery model is a Database property that control the basic behavior of the backup and restore operations for a DB”. Recovery models are designed to control transaction log maintenance.

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

Based on the importance of the data, recovery models configured to the Database.

a)  Full:- Every transaction logged into transaction log file.we can recover the data up to point in time.all production should be full.growth rate should be dragstic.

a)  Bulk-Logged:- Every transaction log file logged into transaction log file except bulk operations.point in time recovery possible bulk operations  are not done. bulk operations are minimally logged.

b)  Simple:- Each and every transaction file logged into the transaction log file when ever check point occur it truncates the transaction log file . transaction log back backup not possible.

3)    What are the differences between Full and Simple Recovery models?

Sl	Full	Simple
1	T. Logs are maintained and can recover the data up to the point of failure	T. Logs are not maintained and can recover the data up to recent full/diff backup.
2	Supported for Logshipping, Database mirroring and Replication	Not supported for Logshipping, Database mirroring and Replication
3	Maintenance will be huge	Maintenance will be less
4	Support for OLTP systems	Support for Data warehouses or not often changed databases

4)    Which Databases can we use Simple Recovery Model?

Simple  Recovery  Model  is  useful  for Development, Test databases, Data warehouses or not often changed Databases.

5)    In which recovery models Point-in-time recovery is possible?

Point-in-Time Recovery is Possible only in Full and Bulk-Logged Recovery Models, but in Bulk-Logged Recovery model Point-in-time recovery may or may not possible.

6)    What is the default recovery model for system databases?

Master              - Simple

Model                - Full

MSDB                - Simple

TempDB            - Simple

7)    Why can’t take T.Log backups in simple recovery model?

In Simple Recovery Model the Transaction Logs are truncated.

8)    How to set Recovery models using T_SQL?

a)    To Check current recovery model:- SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') As [Recovery Model]

b)    To set Simple Recovery model:- ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE

c)    To set Bulk-Logged Recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY BULK_LOGGED

d)    To set Full recovery model ALTER DATABASE ADVENTUREWORKS SET RECOVERY FULL

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.