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?
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.
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