SSIS - How To Add Task/ Transformation To SSIS Control Flow Items Or SSIS Data Flow Items ( Add Third Party Task/Transformation to SSIS )

There are scenarios where SSIS does not provide optimize solution. Think about if you have to update records in Data Flow task. To perform that you have to use OLE DB Command Transformation. OLE DB Command Transformation will take one row at a time and update in table, that means it will slow the process.
To make the process fast, we can load the records in some staging table and then use TSQL query to update records(set base query).

There are companies, who create different Tasks/Transformation which are not available in SSIS by default.
Let's consider our above scenario, After searching on internet. I found that there is on Transformation which is created by Task Factory and that can perform Batch updates in Data Flow Task.
Finally our company decided that they are willing to pay for these third party transformation so we don't have to spend time on custom coding in SSIS.

here are the steps , how we can download these Task/Transformations and then add to our project and use it.

Step 1 : 

Go to http://pragmaticworks.com/

You will see Update Batch Transformation as shown below

Fig 1: Update Batch Transform

Step 2: 

Click on Free Trial Download as we are just loading to learn about these transformations, If your company has decided to buy them Click on Buy Now

Fig 2: Download Free Trail

Step 3: 

Download Zip file according to your SQL Server version installed. As I have SQL Server 2008 installed so I am downloading related version.

Fig 3: Download 2008.zip

Step 4:

Unzip the downloaded file and double click on it to install.

Fig 4: Unzip the file

Step 5: 

Open a new SSIS Project and then go to Tools and then Choose Toolbox Items

Fig 5: Choose Toolbox Items

Step 6:

Fig 6: SSIS Data Flow Items

Step 7:

The Added Transformation will shown in the list , move to the Tab you want and then start using like other transformation which came with SSIS.

Fig 7: Custom/Third party Transformation in Data Flow Transformations

What is @@RowCount

@@RowCount returns the number of row/s affected by last statment. Let's see how it is used in real time.
Let's say that we want to insert new records from dbo.source table to our dbo.destination table. if IDs do not exist in destination table then insert otherwise update the record. To perform this we will write stored procedure.

We want to insert the row counts for updated and inserted records in dbo.Audit table in Stored procedure too for audit process.

USE TestDB
GO

--> Prepare dbo.Source Table

CREATE TABLE dbo.Source
  (
     ID   INT,
     Name VARCHAR(100)
  )

GO

--> Insert couple of records in dbo.Source Table

INSERT INTO dbo.Source 

VALUES      (1,
             'Aamir'),
            (2,
             'Raza')

GO

-->Create dbo.Destination Table.

CREATE TABLE dbo.Destination
  (
     ID   INT,
     Name VARCHAR(100)
  )

GO

--> Insert only one records in dbo.Destination table

INSERT INTO dbo.Destination

VALUES      (1,
             'Aamir Shahzad')

GO

--> Create dbo.Aduit table that will hold SourceTable,DestinationTable,updatedRowCnt,InsertRowCnt

CREATE TABLE dbo.Audit
  (
     id                   INT IDENTITY(1, 1),
     SourceTableName      VARCHAR(100),
     DestinationTableName VARCHAR(100),
     UpdatedRecords       INT,
     InsertedRecords      INT,
     LoadDate DATETIME DEFAULT GETDATE()
  )
GO

--> Create Stored Procedure for Insert/Update

CREATE PROCEDURE Dbo.Updatedestination

AS
  BEGIN
      DECLARE @UpdatedRecords INT
      DECLARE @InsertedRecprds INT

      -->Update Exisiting Records
      UPDATE DST
      SET    DST.Name = SRC.Name
      FROM   dbo.Destination DST
             INNER JOIN DBO.Source SRC
                     ON DST.ID = SRC.ID

--SET the values of @updatedRecords variable by using @@RowCount
      SET @UpdatedRecords=@@ROWCOUNT

      -->Insert New Records
      INSERT INTO dbo.Destination
                  (ID,
                   Name)
      SELECT ID,
             Name
      FROM   dbo.Source S
      WHERE  NOT EXISTS (SELECT 1
                         FROM   dbo.Destination D
                         WHERE  S.ID = D.ID)

--> Set the value of @InsertedRecords variable by using @@ROWCOUNT
      SET @InsertedRecprds=@@ROWCOUNT

      --> Insert Row Counts into dbo.Audit Table
      INSERT INTO dbo.Audit
                  (SourceTableName,
                   DestinationTableName,
                   InsertedRecords,
                   UpdatedRecords)
      SELECT 'dbo.Source',
             'dbo.Destination',
             @InsertedRecprds,
             @UpdatedRecords
  END

-->Execute Stored Procedure couple of times

EXEC dbo.Updatedestination

--> Check the Audit Table

SELECT * FROM dbo.Audit
--Drop All object which created for this scenario
--drop table dbo.Source
--drop table dbo.Destination
--drop table dbo.Audit
--drop procedure dbo.Updatedestination

Fig 1: Records affected 

How To Use Import/Export Wizard In SQL Server - SQL Server / TSQL Tutorial Part 104

We have received SourceFile.xlsx file and we have to load that to SQL server Table. We can either create SSIS Package in BIDS or we can use Import/Export Wizard to load this file in SQL Server Table. In this post, we will use Import/Export Wizard.

Fig 1: Excel Source File

Step 1:
Right Click on Database in which your table exists or you want to create it and load Excel data as shown below

Fig 2: Import Data by using Import/Export Wizard in SQL Server Table.

Choose the Data Source:

Choose the data source which you want to use as source, As we are loading data from Excel, Choose Excel file as shown below

Fig 3: Choose Excel Data Source in Import Export Wizard

Choose a Destination:

Choose the destination where you want to load the data from source. In our case we are loading our data to SQL Server Table. Configure as shown below

Fig 4: Choose SQL Server as Destination

Specify a Table Copy or Query:

You can directly choose the table from where do you want to load the data or you can write query if you are using Database as your source. As we are using Excel as source, we will choose Table(Sheet).

Fig 5: Choose Copy data from one or more tables or views 

Select Source Tables and Views:

In this part of Wizard, we have to select the Tables or Views we want to use from source and load data to destination. As we are loading data from Excel, the Excel Tabs are shown. Choose the Sheet (Tab) which do you want to load. Under Destination, it will show you same name like Source. I have changed that to CustomerData. You can choose any name of your Table you want. You can choose multiple sheets or Tables from Source.

Fig 6: Select Source Tables/Views in Import Export Wizard

Column Mappings:

Click on Edit Mappings and then you can map the source columns to destination columns , Also if you need to choose correct Data type, you can change here.

Fig 7: Column Mapping Import Export Wizard

Save and Run Package:

By Default, Run immediately is checked. I have changed the option to Save SSIS Package and provided the location where I want to save the SSIS Package. Also there is no sensitive information that I want to save in Package such as Password so I have selected Do not save sensitive data.

Fig 8: Save SSIS Package to File System

Save SSIS Package:

Provide the name of SSIS Package and File Location as shown below

Fig 10: Provide Name for SSIS Package

Complete the Wizard: 

Summary of all the steps will be shown to you in this step. You can see the source and destination etc.

Fig 11: Summary of Steps

Once you hit Finish button, The Wizard will execute all below steps and finally save the SSIS Package.

Fig 12: Save the SSIS Package to given location

The Package is created on desktop as per my given path.

Fig 13: SSIS Package created by Import/Export Wizard

To execute this package, double click on it and below window will open. If you need to change the name of File or SQL Server, you can go to Connection Managers and change it. In my case, I do not want to make any changes. Press Execute Button

Fig: 14 Execute Package Utility

Once you hit Execute, Package Execute Progress window will appear and you will be able to see the progress of execution of your SSIS Package.

Fig 15: Package Execution Progress.

Import/Export Wizard is a way to quickly load data between different sources and destinations. You can create your SSIS Package quickly by using Import/Export Wizard and then add to SSIS Project and make changes if required.

If we need to export data from SQL Server then we need to Right Click on Database-->Tasks-->Export Data and Import/Export Wizard will start.

Video Demo : How to use Import Export Wizard to load Excel file data to SQL Server Table

How To Include Comments In SSIS Package [What is Annotation]

When we create TSQL objects such as Stored Procedure, Function etc., we put comments inside our code so we do not have to dig through code later to understand it when it comes to make some changes.

By putting comments we make sure if any other developer in our team needs to understand the program to make changes or execute,He/She can use these comments as document and understand quickly.

It is good practice to include comments even you write your code in any programming language. In SSIS Package, Annotations is provided that can be used to put our comments inside SSIS Package. Annotations are available in Control Flow, Data Flow and Event Handler design surfaces of SSIS Designer.

To put Annotations, Right Click in design surface in any Pane( Control Flow, Data Flow or Event Handler)

Fig 1: Right Click to Add Annotation

Once you Click Add Annotation , Text box will appear as shown below

Fig 2: Text Box to Add Comments by Clicking Add Annotation

Let's start writing our comments about the SSIS Package those can help us and other developer to understand this Package quickly. After writing first line of comment we want to go to next line. Let's hit Enter key. Only Enter is not going to take you to next line if you are using BIDS. Press Ctrl and then hit Enter. It will take you to next line.

Fig 3: Adding multiple line of comments

Fig 4: Multiple line of comments

If we are using SQL Server Data Tools (SSDT) then Enter key will take you to next line.

Fig 5: Adding Annotation when using SSDT.

If you try to select the comments you have written and copy, The copy option is grayed out. Select the text and then press Ctrl+C to copy the text.

If you would like to watch video how to Write comments in SSIS Package, check below video

DBA - Restore SQL Server 2012 Database Backup On SQL Server 2008 /2008 R2

If you have taken the backup from SQL Server 2012 and trying to restore on SQL Server 2008 or SQL Server 2008 R2. You will get below error if tried to restore by using SSMS(GUI).

Backward compatibility is not supported. If the backups are taken from newer version then they can not be restored to old version of SQL Server. But backups taken from older versions of SQL Server can be restored to new version of SQL Server.

So what are out best options, if we have to have restore SQL Server 2012 Database to SQL Server 2008/2008 R2?

1--Generate Scripts with Data
We can generate the scripts of entire Database with Data and then run that .sql file on Target server to restore all objects. Step by step Link

If the .sql file is small, it will be easy to open the file in SSMS and execute. If you have scripted a big database, you might not be able to open the .sql file in SSMS. Here is the Link , how to run big sql file.

2--Use Import Export Wizard
Here are the steps if you are going to use Import Export Wizard
i) Create Empty Target Database
ii) Generate Scripts for all the objects from Source Database.
iii) Execute Generated Scripts in step ii.
iv) Use below script to disable all Constraints

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

v) By using Import/Export Wizard, Load the data from Source Database tables to Destination Database tables.
vi) Enable Constraints by using below script

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

3--Create SSIS Package
Create SSIS Package with specific requirements and load only data which is required instead of restoring entire database.

SSIS - How To Add Or Delete Tasks In Control Flow Item ToolBox

After a wonderful phone interview the company has invited you come in person for ETL Developer position. Upon your arrival, you noticed that they want to test you with some practical.

They have provided you a .txt file and showed you the database table in which this file has to be loaded. They already open the SQL Server Integration Services project so you can create your SSIS Package.

Great , Piece of Cake;). Easy question.  So you sit and start working on SSIS Package.

What! I do not see Data Flow Task under Control Flow Items :( Maybe it has moved up or down. You searched over and over but could not find Data Flow Task. You can load the file by using Script Task but that is also not available.

Maybe there is something missing in this SSIS Package. Let's create a new SSIS Package. Oh no, I still do not see those Items in Control Flow Items.

Fig 1: Control Flow Item Toolbox without Data Flow Task and Script Task

Your interviewer was trying to be smart and has deleted these Items from Control Flow Items list. So how we can bring them back?

Fig 2: How to Delete or Add Tasks back to Control Flow Item Toolbox in SSIS

As we want to bring the Data Flow Task and Script Task , we need to reset the Toolbox as shown above.

Fig 3 : Rest Toolbox to Default

If we have added third party Components or Tasks in our Toolbox then those will be removed. Click  "How to Add Third Party Components or Tasks in SSIS Toolbox". For now we can reset to Default.

Fig 4: Toolbox set to default and Data Flow and Script Task are available.

If you would like to see all the Tasks in Grid, Right click on Control Flow Items and then unchek the List View property.

Fig 5:Set the View to Grid for Tasks

If you are using SQL Server Data Tools (SSDT) . You will not see the options such as Delete, Rename and other properties as shown in Fig 2 for Task properties. You have new option such as move Task between different Tabs and Restore Toolbox Defaults.

Fig 6: Reset Toolbox in SSIS 2012

What Is The Difference Between The Success and The Completion Value Of Precedence Constraint In SSIS Package

Precedence Constraints are the arrows those we use in Control Flow Pane to connect the Tasks. Precedence Constraints are used to control the execution flow of Tasks as well under what condition pass execution control to which Task.

The default constraint is Success that is represented with Green Arrow between Tasks.

Fig 1: Precedence Constraint on Success

In Fig 1, The Execute SQL Task has to execute successfully to pass execution control to Data Flow Tasks. If Execute SQL task will fail then Data Flow Tasks will not execute.

Fig 2: On Successful Execution of Execute SQL Task

If Execute SQL Task task fails, then control will not pass to Data Flow Tasks as shown in Fig 3.

Fig 3: On Failure of Execute SQL Task

There could be requirements in which even Execute SQL Task executes successfully or fail, we always want to execute Data Flow Tasks. For this requirement, We need to configure Precedence Constraint to Completion.
Double click on the Green Arrow between the Tasks and then configure as shown below in Fig 4.

Fig 4: Configure Precedence Constraint for Completion

The Data Flow Tasks will execute on Completion of Execute SQL Task ( Completion can be success or failure status).

Fig 5: Execution of SSIS Package (Precedence Constraint Completion configuration)

What Is MaxConcurrentExecutables Property in SSIS Package?

MaxConcurrentExecutables is SQL Server Integration Services Pacakge level property that defines that how many tasks can run simultaneously(parallel).
By default the value of this property is set to -1 that means that It will be able to run Total Tasks=Number of processors+2.
As we can see in below Fig 1. The SSIS Package is executing 6 Data Flow Tasks as MaxConcurrentExecutables value is set to default(-1). My machine has 4 processors.

Fig 1: MaxConcurrentExecutables set to default value (-1)

If you are sharing your Server to run other application/s ,  then you might not want to use all the resources for SSIS. In that case, you can set the MaxConcurrentExecutables value to small number.Let's set the MaxConcurrentExecutables=2 in package and run the package and see if it is only executing only two tasks in parallel.

Fig 2: Setting MaxConcurrentExecutables property value to low number to share Resources

As we can see that only two Tasks are executing in parallel. If you are changing the MaxConcurrentExecutables value from default to some higher number, Test your SSIS Package to make sure you are getting better performance than default setting as well no other application/process is being effected by new SSIS Package setting.

Let's set MaxConcurrentExecutables =10 and see if all the Tasks are executing parallel.

Fig 3: Setting MaxConcurrentExecutables to higher value to run more Tasks in Parallel

What Is Parallel Execution In SSIS, How Many Tasks A SSIS Package Can Execute In Parallel?

In simple words, If you place more than one Task on Control Flow pane and do not connect them by using Precedence constraint, the Tasks will run in Parallel.

This can be helpful to speedup the process when we load data from Source Database to Staging Database and there is no dependency which table should be loaded first.

This is great , So If I need to load 100 staging tables from source database, I can run all of them in Parallel?

Yes, you can. Visit Link.

In this post, I am considering default settings, that means our SSIS Package will only be able to execute
 Total Tasks=Number of processors of machine+2.

How would I know that how many processes are on my machine?
Couple of ways to do that quickly

1-Connect to SQL Server by using SSMS if installed on the machine, Right Click on Instance Name and go to properties and then General and you will be able to see the number of processors.

Fig 1: Find Number of Processors from SQL Server Instance

2-Click on Start and then in Search write "Device Manager" and it will open Device Manager, Click on Processors and you will see them there.

Fig 2: Find out the Number of Processors on Computer by using Device Manager


My machine has 4 processors, So the max number of Tasks those can be executed by SSIS Package on my machine will be 4(processors)+2=6 with default setting.

As you can see the 6 Data Flow Tasks are executing parallel. Once any of them will complete, the next will start.

Fig 3: Parallel Execution in SSIS Package

SSIS - What Is The Difference Between Control Flow and Data Flow In SSIS ?

Control Flow: 

Control Flow is part of SQL Server Integration Services Package where you handle the flow of operations or Tasks.
Let's say you are reading a text file by using Data Flow task from a folder. If Data Flow Task completes successfully then you want to Run File System Task to move the file from Source Folder to Archive Folder. If Data Flow Task failed then you want to send email to your users by using Send Mail Task. The Precedence Constraints are used to control the execution flow.

Data Flow: 

Data Flow is the part of SQL Server Integration Services Package, where data is extracted by using Data Flow Sources ( OLE DB Source, Raw File Source, Flat File Source , Excel Source etc.). After extacting data Data Flow Transformations such as Data Conversion, Derived Column, Lookup, Multicast,Merge etc are used to implement different business logics and finally written to Data Flow Destinations (OLE DB Destination, Flat File Destination,Excel Destination,DataReader Destination ADO NET Destination etc.)

SSIS - What Is ETL (Extract, Transform, Load) ?

ETL Stands for Extract Transform and Load. Every day working at different organization/s we come across different scenarios where we have to extract data from different sources such as Excel, Text Files, XML, Database systems etc. and then clean that data or transform that data according to our required format and save it to Destinations such as Database system or in different file types.

To perform ETL task , there are different tools available in market. Some of them are listed below

• SQL Server Integration Services
• IBM Infosphere Information Server
• PowerCenter Informatica
• Talend Studio for Data Integration
• Oracle Data Integrator (ODI)
• Clover ETL
• Centerprise
• Pentaho Data Integration

SQL Server Integration Services is an ETL tool that can be used to extract data from different types of sources by using Sources (OLE DB Source, File File Source, Excel Source etc.) that are built in the tool.

To Transform data, SSIS Provide different type of transformations such as Aggregate, Lookup, Data Conversion,Derived Column etc. so the source data can be transformed in any required format. 

For Loading, It provides different types of Destinations such as OLE DB Destination, ODBC Destination, ADO Net Destination , Flat File, Raw File ,Excel File etc. where data can be loaded once transformed in required format. 

SQL SERVER DBA INTERVIEW QUESTIONS

The list consist of Basic to Advance level SQL Server DBA Interview Questions and Answers.
Please visit this link for our video answers for SQL Server DBA Interview Questions. You might not see Video Answer link for some of them. But Youtube list contains answers for most of them. If you don't see the Video Answer written in front of some of them, hover over the question, you might find the text answer.

All the best with SQL Server DBA Interview:)

1. What are the Editions of SQL Server available?
2. What is SQL Server Instance?
3. What is the difference between default and Named Instance? (Video Answer)
4. How many SQL Server Instances can be installed on one Server? (Video Answer)
5. What is Collation? What Collation will you pick while installing SQL Server?
6. Can you have different Collation on different Databases in SQL Server Instance? (Video Answer)
7. Can Instance Level Collation be different from Database Collation? (Video Answer)
8. What are the best practices to place data files, log files and tempdb on Storage?
9. What are file groups? How would you relate File groups with Partitioned Table?
10. Is it best practice to have Auto Shrink enable on Database? (Video Answer)
11. While installing SQL Server instance , you have used default accounts. Later you want to change to Service account, how would you do that? (Video Answer)
12. What is TDE and why do we use it?
13. If TDE is enabled on Database, Do we have to write some special code on application side to access data?
14. What are the steps to enable TDE on database?
15. Is there any performance overhead because of TDE enabled on Database?
16. If you need to restore TDE Enabled database to different server, what steps are involved?
17. What are different types of data compression available in SQL Server?
18. How would you allocate minimum and maximum memory to SQL Server Instance?
19. What is the difference between Offline and Emergency state of a Database?
20. Can you set Database into ReadOnly state?
21. What is CPU Affinity?
22. What is IO Affinity? (Video Answer)
23. What is MAXDOP , How do you change it?
24. How would you find out who has dropped database?
25. If you have to find out that how many times the backup of a database is taken, Where will you look for that information?
26. How would you find open transactions in SQL Server?
27. How would you find blocked processes in SQL Server?
28. What is locking in SQL Server? (Video Answer)
29. How would you trace deadlock in SQL Server? ( Video Answer)
30. Let's say you have lost the password for sa, How would you recover that? (Video Answer)
31. What is TUF(Transaction Undo File) file and in which process it is created? (Video Answer)
32. What is link Server? Can link server be created for Oracle from SQL Server? (Video Answer)
33. What is sparse column? What are the advantages and disadvantages?
34. Which DBCC Commands have you used often as SQL Server DBA?
35. How would you script all SQL Server Agent Jobs? (Video Answer)
36. How would you script entire database (Tables, SPs,Views etc.) with data?
37. How would you migrate a database from SQL Server Instance to Another SQL Server Instance?  (Video Answer)
38. You have taken a backup of a database from SQL Server 2008R2, Can you restore this to SQL Server 2005 Instance?
39. What will be your strategy when you need to migrate SQL Server 2008 to SQL Server 2012? (Video Answer)
40. What is difference between Backup/Restore and Detach/Attach Database? (Video Answer)
41. When should we update Statistics on SQL Server Database and why? (Video Answer)
42. How would you find out how much space is allocated to Database Log file and how much is used? (Video Answer)
43. What is Database Engine Tuning Advisor and where do you have used it? (Video Answer)
44. If you need to Kill all processes related to a Database , how would you do that?
45. Explain the steps to perform Table Partitioning and best practices? (Video Answer)
46. How would you determine the version and Edition of SQL Server Instance you are working on? (Video Answer)
47. What are the steps to Restore a SQL Server Database to a Point in Time? (Video Answer)
48. How would you shrink the tempdb database in SQL Server? (Video Answer)
49. Which built in tool you have used to Monitor SQL Server activity? (Video Answer)
50. How would you identify table level locks in SQL Server? (Video Answer)
51. What is isolation level and what are four types of them? (Video Answer)
52. If you need to query Oracle database from SQL Server, how would you do that? (Video Answer)
53. What are the differences between DMV's and DMF's? (Video Answer)
54. The tran log of a database has grown huge, How would you shrink the log file? (Video Answer)
55. What are the important points you consider before coming up with Backup strategy? (Video Answer)
56. You have installed SQL Server Instance on Window 2008 Server. Total Memory of Server is 64 GB but you need to assign only 12 GB to SQL Server Instance, how would you do that? (Video Answer)
57. What is the difference Between Index Rebuild and Index Reorganize? (Video Answer)
58. How would you create a SQL Server Agent job that only should run on 5th business day of each month? (Video Answer)
59. You have provided some permission on a Database to a users, but user is not able to connect to that Database. Where will you see the error details in SQL Server? (Video Answer)
60. You have a big database, Before taking the full backup you want to estimate the total time for backup. How would you do that? 
61. You have received a large( 2 GB in size) .sql file, How would you execute that .sql file on SQL Server?
62. You need to find out the Job execution history for last ten days, Where will you search for that? (Video Answer)
63. You are planning to move database from one server to another server. But there are changes the stored procedures are using some cross database queries. How would you find out if any of the Stored Procedure is using DatabaseTest in definition?
64. Your company has found out that the account SQL Services are running is not what it should be. They want you to change to Service account, How would you do that? (Video Answer)
65. What is the differences between differential database backup and Transaction log backup? (Video Answer)
66. What permissions will you grant to a users in SQL Server database, so He/She can truncate table?
67. What are DDL Triggers? Where and why have you created them? (Video Answer)
68. How would you perform Schema comparison between two databases? (Video Answer)
69. You need to perform Data Comparison between two tables,How would you do that? (Video Answer)
70. You need to backup all the logins and Jobs, How would you do that? (Video Answer)
71. You have installed Name Instance of SQL Server, After installation you realized that you misspelled the instance name. Can you correct the name of SQL Server Instance without re- installation?
72. You have installed SQL Server instance on Windows 2008 (MyServerName\MySQLInstance). If your want to change the computer name to only ServerName, Will SQL Server Instance will or You have to re-install SQL Server Instance?
73. What is DAC (Dedicated Administrator Connection) ? Is it enabled or disabled by default? Why and where we use this feature?
74. You need to create full backup of all the databases from one of the SQL Server Instance every night. If any new database is created that should also become the part of backup process. What would be your approach to perform this? (Video Answer)
75. All the sudden, users start complaining that the SQL Server is running slow. What steps you would take to analysis the problem? (Video Answer)
76. What is by default SQL Server Agent Job history Retention? If you need to keep two week of history for each of the job what steps will you take when some of the jobs run every five minutes and some run one time a day? (Video Answer)
77. If you need to find all the processes running by specific login, How would you do that? (Video Answer)
78. How would you find out how many transactions/second SQL Server Instance is performing? (Video Answer)
79. How would you find currently running queries on SQL Server Instance? (Video Answer)
80. How would you find orphan users and fix them? (Video Answer)
81. How would you script user with permission from a SQL Server Database?
82. Your company has a Database for which the Tran Log grows very fast. What strategies you should adopt to keep the Tran log reasonable? (Video Answer)
83. What is the difference between Actual Execution plan and Estimated Execution plan? (Video Answer)
84. What is the difference between Differential Backup and Full Backup? (Video Answer)
85. What are two Server Authentication modes available in SQL Server? (Video Answer)
86. If you have installed SQL Server Instance with Windows Authentication Mode and later you want to change to Mix Mode (SQL Server and Windows Authentication Mode), How would you do that and does it require service restart? (Video Answer)
87. What is the difference to Restore database "Restore With NoRecovery" and "Restore With StandBy"? (Video Answer)
88. What are major differences between SQL Server 2005 and SQL Server 2008 version?
89. What are the major difference between SQL Server 2008/R2 and SQL Server 2012 version?
90. What is the difference between Full Backup and Copy option in Full Backup? (Video Answer)
91. How would you find out that How long SQL Server Instance is running? (Video Answer)
92. What is index fragmentation? (Video Answer)
93. What is the difference between Physical and Logical Reads? (Video Answer)
94. Does SQL Server allow duplicate indexes ( indexes with different name but same definition)? If yes, How would you find them and keep only one of them? (Video Answer)
95. What is the difference between shrinking and truncating database log file? (Video Answer)
96. What are the best practice to configure TempDB? (Video Answer)
97. What is Latch? What is the difference between Latch and Lock? (Video Answer)
98. What is the difference between Schema and Database?
99. What is the Statistics in SQL Server? (Video Answer)
100. What are Trace Flags in SQL Server? (Video Answer)
101. What is SQL Server hash operator?
102. What is the difference between 64-Bit and 32-Bit releases of SQL Server? (Video Answer)
103. What are the best practices to shrink a database? 
104. What is Service Account in SQL Server? (Video Answer)
105. What is SQL Server Column Encryption? What are the steps to create Column Encryption?
106. What is the difference between Service Master Key and DataBase Master Key?
107. To See encrypted data by Column Encryption, What permissions are required by user to access data?
108. If you need to Backup and Restore Column Encrypted Database, What steps has to be performed?
109. If we enable Column Level Encryption in SQL Server, Will there any impact on performance?
110. What is Encryption Hierarchy in SQL Server?
111. What is Replication in SQL Server? (Video Answer)
112. Why do we use Replication, Provide couple scenarios? (Video Answer)
113. What are the types of Replication? (Video Answer)
114. When do we use snapshot replication? (Video Answer)
115. What exactly merge replication is? (Video Answer)
116. Can you schedule replication? And under what circumstances do we schedule replication? (Video Answer)
117. Your team needs to know if replication breaks/fails, what exactly would you do to accomplish that? ( Video Answer)
118. What is re-initializing means in replication? (Video Answer)
119. Under what circumstances will you re-initialize replication? (Video Answer)
120. How would you add new tables in existing replication? (Video Answer)
121. Can you explain what would be the replication overhead on production server? (Video Answer)
122. Transactional replication is set on production source, article schema changed on source but target is not showing that change, what exactly is the issue? (Video Answer)
123. How will you truncate the replicated table? (Video Answer)
124. How would delete replicated database? (Video Answer)
125. Can you bring replicated database offline? (Video Answer)
126. What is orphan replication? And how would you cleanup replication? (Video Answer)
127. What is the difference between log shipping and replication? (Video Answer)
128. Under what scenarios would you use log shipping? (Video Answer)
129. What is new in SQL server 2012 that's not available in previous versions of SQL servers? (Video Answer)
130. What is AlwaysOn in sql server 2012? (Video Answer)
131. What are prerequisites of AlwaysOn? (Video Answer)
132. What is Availability Group? (Video Answer)
133. How many databases can be in One Availability Group? (Video Answer)
134. What is Listener in AG? (Video Answer)
135. Why do we use Listener in Availability Group? (Video Answer)
136. Under what circumstances Availability Group fails over? (Video Answer)
137. What is primary replica in AG? (Video Answer)
138. What is secondary replica in AG? (Video Answer)
139. How many secondary replica can be configured? (Video Answer)
140. What are some advantages of using AlwaysOn feature? (Video Answer)
141. Can you rename Availability Group? (Video Answer)
142. You have setup AlwaysOn and send application team to connect with SQL server using Listener, application can't connect with Listener name, what could be the issue?
143. Can you configure Listener using static port? (Video Answer)
144. Listener port is set to 1533, can you connect to SQL server using Listener name? (Video Answer)
145. Availability Group is in resolving state, what does it mean? (Video Answer)
146. What are SQL server Browsing services? (Video Answer)
147. Why do we use SQL server Browsing services? (Video Answer)
148. What is recommended configuration of SQL server browsing services? (Video Answer)
149. Can you setup replication with AlwaysOn? (Video Answer)
150. Can you use primary or secondary replica as a distributor? (Video Answer)
151. What are the issues using primary or secondary replica as your main distributor? (Video Answer)
152. What is Auto failover of primary replica? (Video Answer)
153. What does read intention mean in AG? (Video Answer)
154. What does in-memory Store procedure mean? (Video Answer)
155. What are tempdb recommended settings in sql server 2012?
156. SQL server connection is timing out, what would you do to resolve it?
157. Tempdb log is full, how would you shrink tempdb? (Video Answer)
158. Can you move tempdb files location without restarting sql server services?
159. What is SQL Server Clustering? (Video Answer)
160. Why do we use clustering? (Video Answer)
161. What are the prerequisites of sql server clustering?
162. How to add a resource in existing cluster? (Video Answer)
163. What is failover? 
164. What is shared storage in SQL server cluster?
165. How would you find out if SQL server failover happened? (Video Answer)
166. Applications can't connect to SQL Server after failover, what could be the issue? (Video Answer)
167. SQL Server Agent resource is not coming online, what could be the issue?
168. What is the difference between cluster mode and standard mode sql server installation? (Video Answer)
169. SQL server cluster installation failed, where would you look the cause of failure? (Video Answer)
170. How many SQL server instances can you add in a cluster? (Video Answer)
171. Can you find out using SSMS current node for SQL Server Services? (Video Answer)
172. What is alias in sql server?
173. How can you find out sql server IP address?
174. How can you find out SQL server Port? 
175. What is active-active cluster? (Video Answer)
176. What is active-passive cluster? (Video Answer)
177. What is cluster aware? (Video Answer)
178. Is SSRS cluster aware? (Video Answer)
179. Is SSIS cluster aware? (Video Answer)
180. Is it recommended to install SSRS and SSIS during cluster mode SQL Server Installation? (Video Answer)
181. What is scale out deployment in SSRS? (Video Answer)
182. Can you configure SSIS as cluster aware?
183. Some of your external vendors can not connect to SQL server in intranet, what could be the cause? (Video Answer)
184. You can't connect SQL server from client machine with in intranet, what could be the cause? (Video Answer)
185. Client can connect to SQL server using IP address but can't connect using SQL server instance name, what could be the cause? (Video Answer)
186. How to configure SSIS to store packages in MSDB? (Video Answer)
187. How to configure SSIS to store package in central location?
188. What is backward compatibility means in SSIS?
189. How to configure SQL server's memory? (Video Answer)
190. What are best practices to configure SQL server's memory?
191. If SQL server's memory is not configured, what can happen to the system? (Video Answer)
192. System Admin sends you an email that SQL Server services are taking almost all the memory, what steps would you take to resolve it? (Video Answer)
193. SQL server lost connection with AD, what will happen to SQL Server?  (Video Answer)
194. SQL services are not starting, where would you look for the cause? (Video Answer)
195. Can you restore master database? (Video Answer)
196. You want to know if SQL services restarted or stopped via email, how would you accomplish that? (Video Answer)
197. How do you keep up with SQL server updates?
198. Have you ever installed SQL server patches?
199. What is the latest service pack available in sql server 2012? (Video Answer)
200. You are restoring the databases by using SQL Server agent job,How would you find that how much percentage of restoration is completed?
201. What is deployment ? Have you been involved in deployment process?
202. If you have to deploy 20 SSIS Package to SQL Server Integration Services, How would you do that?
203. If you have to deploy SSIS Packages to File system, What steps would you follow?
204. What is Version Control? Which software you have used for version controlling?
205. What is Team Foundation Server? Why did you use it?
206. What is Check-In and Check-Out in TFS?
207. What are the best practices when you need to deploy DDL and DML scripts to Database?
208. How would you deploy SSRS report to Report Server? If you have to deploy 100 reports to Report Server how would you do that?
209. Can Data Sources, Data Sets deployed from SSRS Project to Report Server? or You have to create them manually?
210. How would you deploy SSAS Cube to SQL Server Analysis Services Server?
211. Can we deploy more than one Cube in SSAS Database?
212. What are the best practices for SQL Server Change Management?
213. A users has left the organization, How would you drop his login and user name from all databases from SQL Server?
214. You need to set Recovery mode =Simple for all the databases on development machine. How would you do that quickly? 
215. How would you rename logical files of SQL Server Database? 
216. How would you move data or log file of a Database from one drive to another? 
217. What are the types of Database files? How would you get the information about them?
218. What is Contained Database? In which version of SQL Server they were introduced?  (Video Answer)
219. What is Policy- Based Management in SQL Server? (Video Answer)
220. How would you identify the isolation level used by the query when dead lock occurs? (Video Answer)
221. What is FileTable Feature in SQL Server, How does it works? (Video Answer)
222. You need to import large amount of data to tables in a database, you want to keep the transaction log size small while import the data, How would you do that? (Video Answer)
223. You need to save passwords in SQL Server Table, What type of encryption would you use?
224. For what purpose do you use Dynamic management views?
225. You have 35  Databases on one of the SQL Server instance, You need to create process to take full backup nightly and transaction log backup every hour, how would you do that? (Video Answer)
226. You need to store and manager unstructured data in SQL Server, Which approach you would use it? (Video Answer)
227. What is FileStream in SQL Server? (Video Answer)
228. What are advantages of FileStream?
229. What is the difference between FileStream and FileTable?
230. You have a Server that has 24 processors, You need to install multiple SQL Server instances on it.What method you will use to allocate processors to each instance depending upon the requirement? (Video Answer)
231. Suppose you have a big table with million of rows. You need to provide a solution in which the most recent data should be stored on fastest storage and old data on slow storage, What solution would you provide for this scenario? (Video Answer)
232. Why do you use Resource Governor? (Video Answer)
233. Can you use Resource Governor for SSAS,SSIS and SSRS services? (Video Answer)
234. How would you find out when SQL Server was restarted last time?
235. You have CDC enabled on a database, If you take a backup of CDC enabled Database and restore to another instance, Will CDC Tables available?
236. How would you restore CDC enable Database to different instance in a way that CDC tables should be available?
237. If You have to include new article ( table, view, Stored Procedure or function) in Replication, how would you do that?
238. What are different type of indexes available in SQL Server? (Video Answer)
239. What are filtered Index in SQL Server? (Video Answer)
240. You need to save credit card information in SQL Sever, What would be your suggestion for encryption?
241. You need to save some image file for your application, What would you suggest? You are using SQL Server 2014.
242. Can you create two tables with different collation in a database? (Video Answer)
243. You get the request to create ER( Entity Relationship Diagram) for the tables in a database, Which tool/s would you use to create that? (Video Answer)
244. What is Full Text Search in SQL Server? Where do you need to use this feature? (Video Answer)
245. Do you recommend auto growth for Database files? If not why? (Video Answer)
246. ANSI explain 4 types of isolation, SQL Server has  one more type in SQL Server , What is the name of that? (Video Answer)
247. What is default Isolation level set for SQL Server Instance? (Video Answer)
248. Can you have In memory Table without Index or without Primary key? If not then Why? (Video Answer)
249. What's your experience with Backup Compression, It does compress the backup file, Does it take long to restore compressed backup?
250. How Database Backup Encryption in SQL Server 2014 is different from TDE ( Transparent Data Encryption).
251. Is it recommended to Run DBCC CheckDB on Production servers? If not then what solution you suggest? (Video Answer)
252. What is mount point? Why do we need to use it? (Video Answer)
253. What are data pages in SQL Server? (Video Answer)
254. What are Extends in SQL Server? (Video Answer)
255. How many files do you need for Tempdb? (Video Answer)
256. Can SQL Server Instance be up without TempDB? (Video Answer)
257. Is TempDB created based on Model database properties?
258. How would you find out if CDC ( Change Data Capture ) is enable or disable on a database?
259. How would you enable CDC on a Database?
260. How would you disable CDC on a Database?
261. How would you configure CPU Affinity?
262. How would you track changes of a particular period by using Change Data Capture function?
263. How would you disable CDC ( Change Data Capture ) on a table?
264. How to Configure existing standalone sql server named instance if Machine names changes? (Video Answer)
265. Replication Error, can't connect to actual server, @@servername returns Null (Video Answer) 
266. How to give read permission to non administrative accounts to Event Viewer in Windows 2008R2/2012 ? (Video Answer)
267. How to resolve Availability Group Listener Errors?

Common Interview Questions and Answers for SQL Server DBA

Real Time Interview Questions with Answers for SQL Server DBA

Top 100 Interview Questions for SQL Server DBA

SQL Server Database Administration interview questions with answers

Fresher and Experienced SQL Server DBA Interview Questions

Microsoft interview Questions for DBA

SQL Server DBA interview Q and A

Top 10 SQL Server DBA Interview Questions and Answers

SQL Server - Interview Questions and Answers

Top 5 Interview Questions for Microsoft SQL Server DBA