In computing, Oracle SOA Suite is a part of the Oracle Fusion Middleware family of software products.  Oracle SOA Suite enables system developers to set up and manage services and to orchestrate them into composite applications and business processes. With Oracle SOA Suite's hot-pluggable components, organizations can easily extend and evolve their architectures instead of replacing existing investments.
Components of ORACLE SOA:
  • Oracle Service Bus (OSB)
  • Oracle BPEL Process Manager
  • Oracle Event Processing
  • Oracle Web Services Manager, a security and monitoring product for web services
  • Oracle Business Rules, contains a JSR 94 Business rules engine
  • Oracle Business Activity Monitoring
  • Oracle Enterprise Service Bus (now known as the Mediator component)
  • Oracle JDeveloper
What is SOA?
SOA (Service-Oriented Architecture) is an IT architecture strategy for business solution (and infrastructure solution) delivery based on the concept of service-orientation. It is a set of components which can be invoked, and whose interface descriptions can be published and discovered. It aims at building systems that are extensible, flexible and fit with legacy systems. It promotes the re-use of basic components called services.

Mention the SOA Principles?
These 8 principles are underlying to any good architecture that utilizes SOA design to build their products and services:
Standardized service contract: Services adhere to a communications agreement, as defined collectively by one or more service-description documents.
Service loose coupling: Services maintain a relationship that minimizes dependencies and only requires that they maintain an awareness of each other.
Service abstraction: Beyond descriptions in the service contract, services hide logic from the outside world.
Service re usability: Logic is divided into services with the intention of promoting reuse.
Service autonomy: Services have control over the logic they encapsulate.
Service statelessness: Services minimize resource consumption by deferring the management of state information when necessary
Service dis cover ability: Services are supplemented with communicative Meta data by which they can be effectively discovered and interpreted.
Service composability: Services are effective composition participants, regardless of the size and complexity of the composition.
What are the main benefits of SOA?
SOA helps create greater alignment between IT and line of business while generating more flexibility – IT flexibility to support greater business flexibility. Your business processes are changing faster and faster and global competition requires the flexibility that SOA can provide.
SOA can help you get better reuse out of your existing IT investments as well as the new services you’re developing today. SOA makes integration of your IT investments easier by making use of well-defined interfaces between services. SOA also provides an architectural model for integrating business partners’, customers’ and suppliers’ services into an enterprise’s business processes. This reduces cost and improves customer satisfaction.

Explain oracle 10g and 11g and 12c?
8i: Java, Materialized Views, RMAN, Splitting between Standard and Enterprise editions.
9i: RAC, Streams, Flashback, Data Guard, Oracle Managed Files
10g: Grid Computing, ASM, AWR/ADDM/ASH, ASMM, Data Pump, VPD
11g: Total Recall, RAT, Parallel Execute, Active Data Guard, ASO/TDE, Data Vault, Audit Vault, Invisible Indexes, Data Redaction, Data Masking
12c: Multi-Tenant, In-Memory, Edition-based Redefinition, Adaptive Optimizer
Oracle Database 10g
There are several hundred feature differences between the 9i and 10g versions, but the following offers and worth summary of the new features implemented on the Oracle 10g database version:
  • SQL optimizer internals have been majorly altered
  • Oracle grid computing
  • RAC in Oracle 10g enhanced to enable dynamic scalability on server blades
  • Oracle Enterprise Manager (OEM) in 10g completely redesigned
  • The OEM performance pack now includes ASH and AWR tables, same as the   Diagnostic Pack
  • The imp utility on the Data Pump has been replaced with impdp
  • SQLTuning Advisor
  • Automatic Database Diagnostic Monitor (ADDM)
  • SQLAccess Advisor
  • The Stripe and Mirror Everywhere Standard (SAME) introduced Automatic Storage Management (ASM)
  • Automatic Workload Repository introduced to replace the STATSPACK
  • Automated Session History (ASH)able to materialize the Oracle Wait Interface in time
  • Scheduling package dbms_job is replaced by the dbms_scheduler package
  • Introduction of the OEM Partition Manager
  • Provision for support of Multiple Temporary Tablespaces to decrease sorting stress in TEMP
  • Introduction of backup compression by RMAN
Oracle Database 11g
One outstanding enhancement in Oracle 11g is its heavy investment towards self-tuning capabilities. The design implemented this through creation of automated storage, automated memory management and creation of intelligent tuning advisors. The loop ended through the provision of intelligent automation tools, resulting in a self-healing database. The most important enhancement in this is the SQL tuning advisor that is capable of tuning SQL statements automatically.
A list of enhancements in Oracle 11g is as outlined below:
  • Automatic memory tuning
  • Fully automated SQL Tuning through the SQL Performance Analyzer
  • Automated load balancing in storage
  • Automated Diagnostic Repository
  • Enhancement of the Information Lifecycle Management (ILM) – 11g codified the approach to ILM
  • CBO statistics refresh threshold with table-level control
  • Tables interval partitioning
  • File group repository
  • Load balancing utilities
Oracle 12c:
Better upgrade experience – the process of upgrading is simpler and less prone to risks as much of the process uses Oracles automated Upgrade Assistants. A lot of resources on implementation and upgrades can also be available online.
Price differences – Oracle 12c still enjoys the uplift waive for Extended support, meaning that customers paying for the 11g support would also enjoy support for the new 12c improvements and features. An upgrade would therefore make better economic sense.
Awesome support – Oracle 12c has been on the market for over a year, and as with previous generations, comes with better user support than any of the previous versions.

What is the difference between cloud computing and grid computing?
Cloud computing and grid computing are very similar concepts that can be easily confused. Fortunately, there are a few key differences that set the two apart.
Grid computing is a loose network of computers that can be called into service for a large-scale processing task. This network is over the Internet, but only computers that have opted into the grid are called upon. Although distributed geographically, grid computing allows for parallel processing on a massive scale. In short, grid computing is what you want when you have a big processing job to tackle. Search for Extraterrestrial Intelligence (SETI), for example, uses a grid computing scheme to analyze radio frequency data, taking advantage of volunteers’ idle processing power.
Cloud computing, in contrast, usually involves accessing resources on an as-needed basis from clusters of servers. These clusters can handle large processing loads, but they are intended to provide scalable processing to users on a smaller scale. Instead of handling one huge task from a single user, cloud computing handles many smaller requests from multiple users. This allows the users to scale up their computer resources for a temporary processing spike without investing in actual servers, which may be recruited only rarely.

What is the difference between 10g and 11g?
SCA architecture was followed in 11g and not in 10g
In 11g you can put all your project SOA components in composite.xml file and deploy as a single deployment unit to single server, where in 10g you have to deploy each component to the respective server (i.e. ESB to ESB server, BPEL to BPEL Server)
Basically all the SOA components like BPEL, ESB (Called Mediator in 11g), & OWSM are brought into one place in 11g using SCA composite concept.
The major difference between 10g & 11g would be the app server container. 10g by default runs onOC4J while 11g runs on Web logic Server.
  • In 10g every BPEL is a separate project, but in 11g several components can make 1 project as SCA.
  • In 10g consoles are separate for BPEL and ESB, but in 11g Enterprise Manager contains all.
  • In 10g BAM and business rules are outside SOA Suite, but in 11g they are in SOA Suite.
What is SCA?
Service Component Architecture (SCA) provides a programming model for building applications and systems based on a Service Oriented Architecture. SCA is a model that aims to encompass a wide range of technologies for service components and for the access methods which are used to connect them.

What is Web service?
Web services are application components, which are self-contained and self-describing and provide services based on the open protocol communication (i.e SOAP UI, HTTP over the net).

What is Mediator?
The Mediator is in charge of interconnecting, within an SOA composite application, components that expose different interfaces. In addition, the Mediator can perform duties such as filtering and making routing decisions.

The composite editor in JDeveloper gives you the flexibility to define the interface now, to choose an existing interface, or to define the interface later as you wire components to the Mediator.
Transforming data from one representation to another is, along with routing, one of the key functions of the Mediator.

What is difference between ESB and Mediator?
In 10g for routing, separate router need to keep along with ESB for routing and filter expressions. Where as in 11g mediator contains routing rules and filter expressions itself.

What Are The Different Design Patterns In Soa?
  • Synchronous
  • Asynchronous Fire and Forget
  • Asynchronous Delayed Response.
What is SOA Governance? What are Its Functions?
Service-Oriented Architecture (SOA) governance is a concept used for activities related to exercising control over services in an SOA.

Difference between URI and URL?
A URI is an identifier for some resource, but a URL gives you specific information as to obtain that resource. A URI is a URL and as one commenter pointed out, it is now considered incorrect to use URL when describing applications. Generally, if the URL describes both the location and name of a resource, the term to use is URI. Since this is generally the case most of us encounter every day, URI is the correct term.

What is Decision Service?
Oracle SOA Suite provides support for Decision components that support Oracle Business Rules. A Decision component is a mechanism for publishing rules and rule sets as a reusable service that can be invoked from multiple business processes. These rules can be changed without redeploying the code.

Why we use BPEL and OSB?
OSB is the light-weight service bus wherever there is not much business logic involves and there is need to just get the message routed between the systems OSB is used where as when there is more business logic involves in the process, then BPEL will be used.

What Is MDS?
MDS –Metadata Store WSDl and Schemas to be used in the process can be published to the MDS and get it used in the code by referring the artifacts from the MDS
  • JAR (Deployment unit) size will be reduced.
  • Duplication of the artifacts can be avoided between the services.
What is the difference between concrete and abstract WSDL?
Concrete: Besides the information about how to communicate to the web service, it the information on where the service exist. It has Bindings (Protocol the message should be sent) and Services (has endpoint for each bindings) .
Abstract: It has information about how to communicate to the web service like types (Schema), Message (input and output messages service accepts) ,Operations (operation that can be performed on this service) and port Type.

What is HA File and FTP Adapters?
In the clustered environment, File and FTP adapters should be used as HA (High-Availability)
Inbound: It is controlled by Control Files and avoids the race between the manages servers in reading the files where the reference of the files read by the managed servers will be maintained in the control directory.
Outbound: It is controlled by DB Mutex table exist in the SOA dehydration store and this avoids duplicated been written to the same file when all the managed servers in the clusters process the same messages.

What is DB-Based MDS Repository?
 Database-based repositories are used in production environments where robustness is needed. These repositories are created using the Repository Creation Utility (RCU) application from Oracle. This utility helps with the creation of a new database schema with its corresponding tables and objects. Repositories can later be registered or deregistered via the Oracle Enterprise Manager Fusion Middleware Control console.

How Can We Embed or Use A Java Code in BPEL?
Using JAVA embedding activity in BPEL, Java code can be embedded in BPEL and can be used.

How Does Pick Activity Differ From A Receive Activity?
Pick activity can act as a multiple receive activity in some business scenarios. If we have
two inbound operations and both can trigger the bpel process then we will go with pick activity as we can’t have two receive activity with create Instance box checked.

How Can We Make A Partner Link Dynamic?
If we have to send the request to different service which has the same wsdl then dynamic partner link will be used and using addressing schema we can set the endpoint dynamic to send the request to the desired service.

What is a XA data source? How it differs from a non-XA data source?
An XA transaction involves a coordinating transaction manager, with one or more databases (or other resources, like JMS) all involved in a single global transaction. Non-XA transactions have no transaction coordinator, and a single resource is doing all its transaction work itself (this is sometimes called local transactions).

What is trigger file in File adapter?
 When we choose “Trigger File” checkbox, file adapter will not poll/read the file/files from specified directory unless it sees trigger file in trigger file directory. Once trigger files is there in the trigger file directory, file adapter will start polling the files.

What is Dynamic Routing in Mediator?
A dynamic routing rule lets you externalize the routing logic to an Oracle Rules Dictionary, which in turn enables dynamic modification of the routing logic in a routing rule. When you choose to create dynamic routing rule then it creates a new business rule service component that is wired to the Oracle Mediator service component within the SOA composite of the Oracle Mediator service component. The business rule service component includes a rule dictionary. The rule dictionary is a metadata container for the rule engine artifacts, such as fact types, rulesets, rules, decision tables and so on.

What are dspMaxThread and recieverThread properties? Why are they important?
Receiver Threads property specifies the maximum number of MDBs that process Async across all domains. Whereas the dspMaxThread are the maximum number of MDBs that process Async and threads that operate across a domain. So, we need to ensure that the dspMaxThread value is not greater than Receiver Threads.

Is it possible to use MS SQL Server as dehydration store with SOA Suite? If yes how?
Yes it is possible. To automatically maintain long-running asynchronous processes and their current state information in a database while they wait for asynchronous callbacks, you use a database as a dehydration store. Storing the process in a database preserves the process and prevents any loss of state or reliability if a system shuts down or a network problem occurs. This feature increases both BPEL process reliability and scalability. You can also use it to support clustering and failover.

What are DVM's and how are they helpful in SOA?
DVM-Domain Value Map is static mappings between a source and target system which can be used in transformations. The value can be changed via SOA composer.

What is the difference between XREF and DVM?
XREF- It is dynamic since the values to the XREF can be populated dynamically and it is stored in XREF_DATA table in SOA Dehydration store.
DVM- Domain Value Map is static mappings between a source and target system which can be used in transformations.

What is OSB?
An ESB is an architectural pattern, not a software product. Different software products can form an ESB. In some cases, companies use multiple products in different areas, leveraging specific functionality to meet their unique requirements. These different products can be federated together as the realization of the ESB pattern.

What is BPEL?
BPEL is used to standardize enterprise application integration as well as to extend the integration to previously isolated systems. Between enterprises, BPEL enables easier and more effective integration with business partners. BPEL stimulates enterprises to further define their business processes, which in turn leads to business process optimization, reengineering, and the selection of the most appropriate processes, thus further optimizing the organization. Definitions of business processes described in BPEL do not affect existing systems, thereby stimulating upgrades. BPEL is the key technology in environments where functionalities are already or will be exposed via Web services. With increases in the use of Web services, the importance of BPEL will increase as well.

What is orchestration? What does it mean to build composite applications? Why would anyone want to?
Existing systems are not going away. Yet enterprises need to build new applications that can leverage the functionality encapsulated in those existing systems. The notion of a composite application is based around the idea of building new applications by wiring together existing building blocks. Orchestration plays an important role in this picture because it is the glue that coordinates the execution of each discrete service. A good orchestration server needs to be reliable, scalable and render the BPEL process logic with very high fidelity.

What is Schematron Validation?
Schematron is an XML schema language, and it can be used to validate XML contents in an XML payload.

What is a flow activity? What is a flowN activity and how does it leverages the flow activity?
Flow activity is used, when parallel execution of the flow is needed and to use this property “non-blocking invoke should be set as true “at the partner link level and no. of execution of parallel flow is defined and static. Where as in Flown the no. of execution of parallel flow is not static and it is determined during run time.

What are two configuration settings for auditing?
Audit Level:
  • Off - absolutely no logging performed whatsoever; may result in a slight performance boost for processing instances.
  • Minimal - all events are logged; however, no audit details are logged.
  • Production - all events are logged. The audit details for assign activities are not logged; the details for all other nodes are logged.
  • Development - all events are logged; all audit details for all activities are logged.
The maximum size (in bytes) an audit trail details string can be before it is stored separately from the audit trail. If a details string is larger than the threshold it will not be immediately loaded when the audit trail is initially retrieved; a link will be displayed with the size of the details string. Typically, the details string will contain the contents of a BPEL variable. In cases where the variable is very large performance may be severely impacted by logging it to the audit trail. The default value is 50 kilobytes.

How can we improve the performance of an XSL file?
By avoiding use of various if statements and using choose and by using for-each group in place of for-each.

What is XML Schema?
An XML Schema describes the structure of an XML document.

Difference between Include and Import in context to XML schema?
The fundamental difference between include and import is that you must use import to refer to declarations or definitions that are in a different target namespace and you must use include to refer to declarations or definitions that are (or will be) in the same target namespace.

What is target Namespace’s function?
<schemaxmlns="         targetNamespace=""         xmlns:target="">
The targetNamespace declares a namespace for other xml and xsd documents to refer to this schema. The target prefix in this case refers to the same namespace and you would use it within this schema definition to reference other elements, attributes, types, etc. also defined in this same schema definition.

What is WSDL?
WSDL stands for Web Services Description Language
WSDL is a document written in XML. The document describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes.

What is SOAP?
SOAP is a simple XML-based protocol to let applications exchange information over HTTP.
Or more simply: SOAP is a protocol for accessing a Web Service.

How to Deploy an XSl File without Deployment of Bpel Process?
We will directly deploy the XSLT, Using ANT script by file replacement in TMP folder.
By creating a folder in BPEL PM Installation folder and specifying its location in our BPEL code with http call and replacing our xslt to that location.

When you will go for Sync process?
Whenever the services returns the response in few seconds, it is recommended to go for synchronous BPEL process if not the BPEL process should be Asynchronous the reason is calling application can’t proceed further in case of synchronous process.

What is a nonBlockingAll property?

Non- blocking invoke is used when Parallel flow needs to be executed where new thread will be created for each invoke a activity and which will execute simultaneously.
Microsoft SQL Server is a relational database management system, or RDBMS, that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It's one of the three market-leading database technologies, along with Oracle Database and IBM's DB2.
The SQL Server Database system from Microsoft Corporation has gained popularity over the last years, and it is believed to be one of the few surviving relational database systems in this marketplace (the others being Oracle and DB2 from IBM).
The original SQL Server code was developed in the 1980s by the former Sybase Inc., which is now owned by SAP. Sybase initially built the software to run on UNIX systems and minicomputer platforms. It, Microsoft and Ashton-Tate Corp., then the leading vendor of PC databases, teamed up to produce the first version of what became Microsoft SQL Server, designed for the OS/2 operating system and released in 1989.
DBA is also an abbreviation for doing business as - a term sometimes used in business and legal writing. DBA is an abbreviation for A-weighted decibels.
A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. A DBA is expected to stay abreast of emerging technologies and new design approaches. Typically, a DBA has either a degree in Computer Science or some on-the-job training with a particular database product or more extensive experience with a range of database products.
Microsoft SQL Server begins with the first Microsoft SQL Server product - SQL Server 1.0, a 16-bit server for the OS/2 operating system in 1989 - and extends to the current day 2017.

What is the work of SQL Server DBA?
SQL Server is a relational database management system (RDBMS) from Microsoft designed for the enterprise environment. SQL Server adds a number of features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

What are SQL Server DBA Responsibilities?
  • Installation, Administration and Maintenance of SQL Server Instances.
  • Setup Test, Dev, Staging and Production Environments.
  • Create Users and assign permissions based on the level of database access the user would need.
  • Create Linked Servers to SQL Servers and other databases such as Oracle, Access, and Informix etc.
  • Design database Backup and Restoration Strategy.
  • Once created the database Backups, monitor those backups are being performed regularly.
  • From time to time recover the databases to a specific point of time, as per the requests.
  • Setup High-Availability as part Disaster Recovery Strategy for the Databases.
  • Troubleshoot various problems that arise in a day-to-day work and fix the issues. Monitoring and Performance tuning; Physical Server Level, Database level and query tuning.
  • Documenting major changes to the SQL Servers
  • Apply Service Packs.
Explain the different types of Indexes available in SQL Server?
The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes.

What is the difference between Clustered and Non-Clustered Indexes in SQL Server?
Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow. There are two types of indexes.
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
You can add non key columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns. For details about index key limits see Maximum Capacity Specifications for SQL Server.
Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. For more information, see Create Unique Indexes.
Indexes are automatically maintained for a table or view whenever the table data is modified.

Why sql server is better than other databases?
I am not going to say one is better than other, but it depends on the requirements. We have number of products in market. But if I have the chance to choose one of them I will choose SQL SERVER because…..
  • According to the 2005 Survey of Wintercorp, The largest SQL Server DW database is the 19.5 terabytes. It is a database of a European Bank
  • High Security. It is offering high level of security.
  • Speed and Concurrency, SQL Server 2005 system is able to handles 5,000 transactions per second and 100,000 queries a day and can scale up to 8 million new rows of data per day,
  • Finally more technical peoples are available for SQL SERVER when we compare to any other database.
So that we can say SQL SERVER is more than enough for any type of application.

What is SQL DUMP? Have you ever dealt with this?
When SQL Server is crashed or in hung state due to a Memory/Disk/CPU problems it creates a SQL DUMP file. A DUMP files is a file containing a snapshot of the running process (in this case SQL Server) that includes all of the memory space of that process and the call stack of every thread the process has created. There are two major types of DUMP files:
Full DUMP: It contains entire process space and takes lot of time and space
Mini DUMP: It’s a smaller file contains the memory for the call stack of all threads, the CPU registers and information about which modules are loaded.

Explain how database snapshots works?
  • Let me explain what happens when we create a database snapshot
  • It creates an empty file known as sparse file for each source database data file
  • Uncommitted transactions are rolled back, thus having a consistent copy of the database
  • All dirty pages will be returned to the disk
  • The user can query the database snapshot
  • Initially the sparse file contains an empty copy of source database data file
  • Snapshot data points to the pages from source database data file
  • When any modification occurred (INSERT/DELETE/UPDATE) on source database, all modified pages are copied to the sparse file before the actual modification. That means the sparse file contains the old/point in time data (when the time the snapshot taken).
  • Now if you query the snapshot all modified pages are read from sparse file and remaining all unchanged pages are read from the original (source database) data file.
What are the primary differences between an index reorganization and an index rebuild?
  • A reorganization is an "online" operation by default; a rebuild is an "offline" operation by default
  • A reorganization only affects the leaf level of an index
  • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
  • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
  • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes.
How many different types of pages exist in SQL Server?
  • Data
  • Index
  • Text/Image (LOB, ROW_OVERFLOW, XML)
  • GAM (Global Allocation Map)
  • SGAM (Shared Global Allocation Map)
  • PFS (Page Free Space)
  • IAM (Index Allocation Map)
  • BCM (Bulk Change Map)
  • DCM (Differential Change Map)
Do you know about Resource Database?
All system objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades.

What is a trace flag?
Trace flag in SQL Server is to change certain behavior. You can think of them as an “IF” condition in SQL Server. One of the most common heard/used trace flag is 1222 – used for deadlock graph printing in ERROLROG. You can imagine it as below.
If (Trace_flag_1222_enabled == TRUE)
Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

What is RAID?
RAID is a disk system that contains multiple disk drives, called an array, to provide greater performance, fault tolerance, storage capacity, at a moderate cost. While configuring your server system, you typically have to make a choice between hardware RAID and software RAID for the server’s internal disk drives.
RAID systems are widely used as storage solutions to get the best I/O performance, depending whether the application is write intensive or read intensive. DBA RayRankins mentions in his book that for database-related applications, in order to minimize disk head movement and maximize I/O performance, it’s a good practice to spread random I/O’s (data changes) and sequential I/O’s (for the transaction log) across different disk subsystems. I agree and endorse his view, as SQL Server, or for that matter any other database, is very much an I/O intensive system.RAID is not a part of a database like SQL Server, implementing RAID can directly affect the way SQL Server performs. There are many RAID arrays available such as RAID 0, RAID 1, RAID 3, RAID 4, RAID 5, RAID 6, RAID 10 and RAID 01.

What is the difference between memory and disk storage?
Memory and disk storage both refer to internal storage space in a computer. The term “memory” usually means RAM (Random Access Memory). The terms “disk space” and “storage” usually refer to hard drive storage.

What is the Hotfixes and Patches?
Hotfixs are software patches that were applied to live i.e. still running systems. A hotfixes a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug).
In a Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.

What are the different Authentication modes in SQL Server and how can you change authentication mode?
SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode. To change the Authentication mode, read one of my blogs Changing SQL Server Authentication Mode.

What the different types of Replication and why are they used?
There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose, depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of remote / distributed systems where the data flow can be from multiple sites, for example sales done at a promotional events which might not be connected to the central servers always.

What is a system database?
System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

What is a user database?
A user database is a database that we create to store data and start working with the data.

What are the different SQL Server Versions you have worked on?
The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005, 2008 and 2012. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

How do you troubleshoot errors in a SQL Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

What are the different ways you can create Databases in SQL Server?
  • T-SQL; Create Database command.
  • Using Management Studio
  • Restoring a database backup
  • Copy Database wizard
What is Transparent Data Encryption?
Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.

What are the operating modes in which Database Mirroring runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.

Can we hot add CPU to sql server?
Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Starting with SQL Server 2008, SQL Server supports hot add CPU.
  • Requires hardware that supports hot add CPU.
  • Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
  • Requires SQL Server Enterprise.
  • SQL Server cannot be configured to use soft NUMA
  • Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

SQL Server is not responding. What is action plan?
Connect using DAC via CMD or SSMS
Connect via CMD
SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
Once you connect to the master database run the diagnostic quires to find the problem
Correct the issue and restart the server

Why Shrink file/ Shrink DB/ Auto Shrink is really bad?
In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.
The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;
The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
  • Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
  • Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased.
What are the different log files and how to access it?
  • SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs
  • SQL Server Agent Log: SQL Server’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT.OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.
  • Windows Event Log: An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server Integration Services (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.
  • SQL Server Setup Log: You might already be familiar with the SQL Server Setup log, which is located at %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.
  • SQL Server Profiler Log: SQL Server Profiler, the primary application-tracing tool in SQL Server, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory.
How to know the number of VLF created on a given database log file?
Run DBCC LOGINFO; Number of rows returned = Total number of VLF. If it is more than 50 means we need to control the Auto-growth rate. Number of times Auto Grow happens means it increases the number of VLF’s.

What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on.

What is DML, DDL, DCL and TCL in SQL Server?
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
Examples: SELECT, UPDATE, and INSERT statements
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
Examples: CREATE, ALTER, and DROP statements
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
Examples: COMMIT, ROLLBACK statements

What is fill factor?
Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

Where do you find the default Index fill factor and how to change it?
The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes. The other option of viewing and changing this value is using sp_configure.

What are the High-Availability solutions in SQL Server?
SQL Server provides several options for creating high availability for a server or database. The high-availability options include the following:
  • Availability groups
  • Database mirroring
  • Log shipping
  • Failover Cluster Instances
Explain about your SQL Server DBA Experience?
What are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable you are for the position to which you are being interviewed.

What is RDMS?
A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS's use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use.

What is the port numbers used for SQL Server services?
  • The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.
  • The browser service runs on port UDP 1434.
  • Reporting services is a web service – so its port 80, or 443 if it’s SSL enabled.
  • Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.
What is your experience with third party applications and why would you use them?
I have used some of the 3rd Party tools:
  • SQL CHECK – Idera – Monitoring server activities and memory levels
  • SQL DOC 2 – RedGate – Documenting the databases
  • SQL Backup 5 – RedGate – Automating the Backup Process
  • SQL Prompt – RedGate – Provides IntelliSense for SQL SERVER 2005/2000,
  • Lite Speed 5.0 – Quest Soft – Backup and Restore
Benefits using Third Party Tools:
  • Faster backups and restores
  • Flexible backup and recovery options
  • Secure backups with encryption
  • Enterprise view of your backup and recovery environment
  • Easily identify optimal backup settings
  • Visibility into the transaction log and transaction log backups
  • Timeline view of backup history and schedules
  • Recover individual database objects
  • Encapsulate a complete database restore into a single file to speed up restore time
  • When we need to improve upon the functionality that SQL Server offers natively
  • Save time, better information or notification