SQL Server 2012 Interview Question Answers

SQL Server 2012 Interview Question Answers

Q: Difference Between DTS and SSIS.

DTS SSIS
DTS stands for Data Transformation Services 1.SSIS stands for Sql Server Integration Services
DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database SSIS is an ETL tool provided by Microsoft to extra data from different sources.
DTS was originally part of the Microsoft SQL Server 2000 SSIS is a component of the Microsoft SQL Server 2005
No Deployment wizard is available Deployment wizard is available Uses Scripting Language
Limited Set of Transformation available Huge of Transformations available
Does not support BI Functionality Completely supports end to end process of BI
Single Task at a time Multi task run parllelly
It is Unmanaged script It is managed by CLR
DTS can develop through Enterprise manager SSIS designer contains 4 design panes:

a) Control Flow

b) Data Flow

c) Event Handlers &

d) Package Explorer.

We can deploy only at local server It can be deployed using multiple server using BIDS

Q: Difference Between SSIS features of SQL Server 2008 R2 & SQL Server 2012?

Server Environments

SSISDB Catalog

Project Connection Managers

Offline Connection Managers

Flat File Connection Manager Changes

Parameters

Comparing and Merging Packages

Undo/Redo in SSIS Designer

Column Mapping

Script Task and Script Component

Q: What is diff between SQL Server 2008 R2 cluster and SQL Server 2012?

Dynamic Quorum

Cluster Aware Updating

Q: MSDB database is in suspect mode, what steps are required for up?

Recover SQL Server MSDB from a Backup

Grab another SQL Server Instance’s MSDB Backup

Use a SQL Server Template MSDB Database

Q: What is TUF file?

TUF file is a Microsoft SQL Server Transaction Undo file. . TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo(.TUF) file is required if a database is loaded in read-only state.

Q: Distribution database size is growing in replication, why?

http://www.sql-server-performance.com/2014/growth-distribution-database/

Q: How many database we can configure in AG in different SQL Server version?

Enterprise: N Numbers

Developer Edition:

Q: New dmv in 2012, after in place upgrade

sys.dm_server_services

sys.dm_os_windows_info

sys.dm_db_log_space_usage

sys.dm_server_registry

sys.dm_os_cluster_properties

sys.dm_tcp_listener_states

sys.dm_server_memory_dumps

Q: What is latency?

Latency is the time that elapses for changes made at the Publisher to be propagated to Subscribers.

Q: Which Counters you will use for performance?

  • Memory – Available MBytes
  • Paging File – % Usage
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec
  • Processor – % Processor Time
  • SQLServer: General Statistics – User Connections
  • SQLServer: Memory Manager – Memory Grants Pending
  • SQLServer: SQL Statistics – Batch Requests/sec
  • SQLServer: SQL Statistics – Compilations/sec
  • SQLServer: SQL Statistics – Recompilations/sec
  • System – Processor Queue Length

Q: How to Correlate with profiler?

https://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/

Q: Where we create Quorum in local or shared disk?

Shared Disk.

Q: If quorum disk is corrupt, what will happen.

https://technet.microsoft.com/en-in/library/cc783947(v=ws.10).aspx

Q: Cluster group is static or dynamic.

Dynamic

Q: What is Cardinality.

Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column.

Q: What is Datagram.

A datagram is a basic transfer unit associated with a packet-switched network. The delivery, arrival time, and order of arrival need not be guaranteed by the network.

Q: Difference between actual and estimated execution plan.

The estimated execution plan is generated based solely on the statistics that SQL Server has – without actually executing the query. 

The actual execution plan is just that – the actual execution plan that was used when actually running the query.

Q: 600 rows is shown in estimated execution plan, but not showing in actual estimated plan,why?

May be data is committed or not.

Q: Model is corrupted, can we restart SQL Server.

Restore with a backup, or got model.mdf and model.ldf from a same version of SQL Server.

Q: If tempdb is corrupted, what to do?

Restart services.

Q: Can we add disk in clustering.

Yes, https://blogs.technet.microsoft.com/askcore/2009/01/14/adding-a-new-disk-to-an-existing-windows-2008-cluster/

Q: We have backup policy like this: Sunday-full, Monday & Tuesday-diff  every 2 Hours, Tuesday database crash, now we have to restore to Tuesday after 2PM diff . back time.How?

Do a Tail log backup with CONTINUE_AFTER_ERROR:

Backup log <databasename> to disk=c:\db.trn with CONTINUE_AFTER_ERROR

Q: What is contained database?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2016 helps user to isolate their database from the instance in 4 ways. Much of the metadata that describes a database is maintained in the database

Q: Can we move to master,msdb, and model database to another system?

https://msdn.microsoft.com/en-in/library/ms345408(v=sql.110).aspx