Funny SQL Server Interview

Tired of studies ;( and SQL Server interview preparation. Take a break and laugh for few minutes and get back to work again. All the best with Interview!

  1. Funny SQL Server Interview Part 1
  2. Funny SQL Server Interview Part 2
  3. Funny SQL server DBA Interview Part 3
18 comments:

How to store file names in SQL Server Table by using SSIS Package

Scenario:

In this blog post we are going to learn how to read the file name from a Folder and Sub Folders and insert into a SQL Server Table. This can be helpful for Audit and to know how many files we have in folders.

Solution:

We will be using For-each loop container to get the files information. Let's start step by step 

Step 1:

To save the file name we need to have a SQL Server table. Let's create a SQL Server table by using below script.

CREATE TABLE dbo.FileInformation
  (
     FileID   INT IDENTITY(1, 1),
     FileName VARCHAR(250)
  )

Step 2:

Create an SSIS Package by using BIDS or SSDT. Create a variable as shown below that is holding your files and also have sub folders with files.

Fig 1: Create FolderPath variable in SSIS Package

FileNamewithPath variable will be used in Foreach Loop Container to hold the value of file name with path that we will insert into our SQL Server table.

 Step 3:

Drag For-each loop container from SSIS Toolbox on Control Flow Pane and configure as shown below. If you are only interested to save file name then use Name only and if you are interested to save file name with extension then use Name and Extension. As I want to save file name with folder , I have chosen Fully qualified in #5.
Fig 2: Configure Foreachloop Container to read the file names with path

Map the value read by Foreach loop container to FileNamewithPath varible.
Fig 3: Map the value to Variable in Foreach loop container

Step 4:

Create OLE DB Connection to the database where you have created dbo.FileName table.

Step 5:

As now we can read the file name with path by using Foreach loop and values will be saved in variable for each iteration , we can save the value of variable in our SQL Server Table. To save the variable value in SQL Server Table, we can use Execute SQL Task. Bring the Execute SQL Task inside the Foreach Loop container and configure as shown below.

 Fig 4: Configure Execute SQL Task to insert variable value in SQL Server Table in SSIS Package


Map the variable to Execute SQL Task insert statement.
Fig 5: Map variable in Execute SQL Task in SSIS Package


Output:

Our package is ready. Let's run our SSIS Package and see if the file names are saved in SQL Server table from folder/s.

 Fig 6: SSIS Package to load file names from folder/s in a SQL Server Table


Output from SQL Server Table
 Fig 7: File Names in SQL Server table stored by SSIS Package

As we can see that the file names with folder information is successfully inserted in SQL Server table.


Video Tutorial for this post
37 comments:

What are Different ways to Execute SSIS Package

This is short post to answer one of the interview questions " What are different ways to execute SSIS Package"?

SSIS Package can be executed by multiple ways, here are some of them.

1) By using BIDS/ SSDT

You can run your SSIS Package by using Business Intelligence Development Studio ( BIDS that is available for SSIS 2005,2008/R2) or you can use SQL Server Data Tools ( SSIS 2012 and SSIS 2014). These tools you will be using while developing your SSIS Packages, testing and debugging.

2) DtExecUI

Execute Package Utility (DtExecUI) is graphical interface to run the SSIS Packages. The Utility can run packages from different locations such as MS SQL Server Database,SSIS Package Stored or packages stored in file system.

When you connect to SSIS Instance by using SSMS and then run the package , it initiates DtExecUI. The graphical interface provide you different options to change the values of variables , connection mangers etc.

If your packages are stored in file system task and you double click the .dtsx file, it opens with DtexecUI. It is stand alone utility.

DtexecUI can be started from command line as well.

3) Dtexec.exe

Dtexec.exe is command line way to run your package. You have to provide information such as package path to run the package from command line. You can also provide the values of variables or Connection managers from command line to run the package with specific requirements.

4) SQL Server Agent Job

SQL Server Agent can be used to create job that can run the SSIS Package on demand or schedule. The SQL Server Agent Job can be single Step calling a SSIS Package or it can consist of multiple steps calling more than one SSIS Packages. In most of the companies the packages are scheduled by using SQL Server Agent. SQL Server agent can access the packages those are stored in SQL Server or from folder storage.

5) Windows Scheduler or Any third party Scheduler

Once way is to create a batch file in which you use dtexec.exe with required parameters. The batch job can be executed by Windows Scheduler or any third party scheduler.

6) Run SSIS Package Programmatically

You can run the package programmatically, here is link that can provide the code.
http://msdn.microsoft.com/en-us/library/ms136090.aspx

I have written as post how to run SSIS Package from Excel.You can check here.  What I am doing in that post, I am calling dtexec.exe to execute SSIS Package on button click that I created in Excel by using VBA. You can use any program of your choice that can start dtexec.exe to run your SSIS Package, that can be custom application.


2 comments:

How to find users with sysadmin role in SQL Server

The below script can be used to find out the users with sysadmin role in SQL Server.

SELECT SP.name     AS LoginName,
       type_desc   AS LoginType,
       Cast(create_date AS DATE) DateCreated
FROM   sys.server_principals SP
inner join sys.syslogins SL
on SP.sid=SL.sid
WHERE SL.sysadmin=1
2 comments:

Msg 15404, Level 16, State 19, Procedure sp_replcmds, Line 1

Today when I was trying to drop publisher for replication that I had set up for one of the SQL Server database by using below script, got error.

use [YourDataBaseName]
exec sp_droppublication @publication = N'PublisherName'
GO

Msg 15404, Level 16, State 19, Procedure sp_replcmds, Line 1
Could not obtain information about Windows NT group/user 'username', error code 0x5.


To solve this problem you have to check the owner of database and then change the ownership to sa by using below statement.

ALTER AUTHORIZATION ON DATABASE::YourDataBaseName to sa
 

8 comments:

SSIS Video Tutorial

If you are looking for the real time scenarios/ Examples or type of work developer perform on Job as ETL developer. You are looking at the right place. The videos explain different scenarios those can answer your so many questions and also helpful if you are going for SSIS/ETL developer interview.


Starting with SSIS 

Sources

  1. How to Load Tab Delimited File To SQL Server Table in SSIS Package
  2. How to Load Pipe Delimited File into SQL Server Table in SSIS Package
  3. How to Load Multiple Comma Delimited Files to SQL Server Table in SSIS Package
  4. How to Load Fixed  Width Text File to SQL Server Table in SSIS Package
  5. How to load Data From Comma Delimited text file to SQL Server Table in SSIS Package
  6. How to convert blank values to Null in Flat File Source in SSIS Package
  7. How to use Raw File Source in SSIS Package
  8. Fast Parse Flat File Source Property in SSIS Package 
  9. How to Redirect Rows from Flat File Source for Data Conversion Error and Truncation Error?
  10. How to use Stored Procedure with Parameters in OLE DB Source in Data Flow Task in SSIS Package 

Destinations

  1. How to Create Fixed Width Text File with DateTime  from SQL Server Table in SSIS Package
  2. How to Create Pipe Delimited file with Date Time in SSIS Package from SQL Server Table
  3. How to Create Comma Delimited file with Date Time from SQL Server Table in SSIS Package
  4. How to Create Tab Delimited text file from SQL Server Table with Date Time in SSIS Package
  5. How to use Raw File Destination in SSIS Package?
  6. How to Enable/Disable Trigger while Loading Data to OLE DB Destination
  7. How to use RecordSet Destination in SSIS Package?
  1. How to Read Files according to Extension and Load them to a SQL Server Table in SSIS Package
  2. How To Load Files to Different Tables according to the File Name in SSIS Package
  3. How to load multiple Excel files to SQL Server Table and then archive them by adding date time to them?
  4. How to Execute Multiple dot SQL files by using SSIS Package 
  5. How to Extract file names from Folders and save in SQL Server Table by using SSIS Package
  6. How to Archive files with Date-time after loading into SQL Server Table in SSIS Package
  7. How to Create Multiple Files from a SQL Server Table in SSIS Package

SSIS Logging

  1. How to configure Windows Event Log type logging in SSIS Package  
  2. How to Create SQL Server Profiler Type logging in SSIS Package
  3. How to create XML file file with Datetime for each SSIS Package execution
  4. Enable SQL Server type logging in SSIS Package
  5. How to create Text Log file with Date-time on each SSIS Package execution  

Precedence Constraints

  1. Annotated Precedence Constraints in SSIS Package
  2. How to use Precedence Constraints in SSIS Package
  3. What are Precedence Constraint in SSIS and How to Write Expression on them

Variables/Parameters/Expressions

  1. What are System Variables in SSIS Package 
  2. Project and Package Parameters - How to Create Package and Project Parameters in SSIS Solution and SSIS Package
  3. How to create folder with Date in SSIS Package 
  4. How to create Monthly Archive Folders in SSIS Package 
  5. How to display variable value by using Script Task while debugging SSIS Package

Tasks

FTP Task / Script Task

  1.  FTP Task - How to Upload Single File to FTP Server from Local Folder in SSIS Package
  2.  FTP Task - How to Download Single File from FTP Server to Local Folder in SSIS Package
  3. FTP Task  - How to Upload Multiple Files from Local Folder To FTP Server Folder in SSIS Package
  4. FTP Task -  How to Download All the files from FTP Server Folder to Local Folder in SSIS Package
  5. FTP Task - Filter Files by using WildCard in FTP Task in SSIS Package for downloading
  6. FTP Task - Delete Specific Files or All Files from FTP Server Folder by using SSIS Package
  7. FTP Task - Download Only Current Day Files from FTP Server by using FTP Task in SSIS Package
  8. FTP Task - Create A Directory Folder For Each Day and Load Files on FTP Server by using SSIS Package
  9. Get File Names from FTP Server and Save to SQL Server Table in SSIS Package by using Script Task
  10. FTP Task and Script Task - How to Avoid FTP Task error when no file found on FTP Server
  11. FTP Task and Script Task - How to Delete Folder with Files from FTP Server by using SSIS Package
  12. FTP Task and Script Task  - Download a File from FTP Site and Delete after Download in SSIS Package
  13. FTP Task and Script Task - Sync Local Folder to FTP Server Folder without Upload Existing Files by SSIS Package
  14. Sync FTP Folder to Local Folder without Downloading Existing Files by using Script Task in SSIS Package
  15. FTP Task/Script Task - Rename File on FTP Server After Downloading in SSIS Package
  16. FTP Task/Script Task -How to move file from one folder to another folder on FTP server by using SSIS Package
  17. FTP Task - Create Local Folder with Date on Daily basis and load files from FTP Folder in SSIS Package
  18. FTP Task - How to Save Password,User Name,Ftp Server Name as variables/Parameters in SSIS Package to make SSIS Package Dynamic to run in SIT, UAT, QA and Prod.

Script Task

  1. Project and Package Parameters - How to use Project Level Parameters and Package Level Parameters in Script Task in SSIS Package
  2. Script Task - How to use Variables in Script Task by using C# or VB NET Scripting Language
  3. Script Task - How to Run Stored Procedure in Script Task in SSIS Package and log FTP Folder Names to SQL Server Table
  4. How to display variable value from Script Task in SSIS Package
  5. How to use ReadOnlyVariables and ReadWriteVariables feature in Script Task
  6. How to Delete Top N Rows from Flat File Source
  7. How to load file's information to SQL Server Table in SSIS Package
  8. How to Purge Old Files (Delete Old Files From Folders By using SSIS Script Task) 
Zip/Unzip in SSIS Package by using Script Task 
  1. How to change .NET Framework version in Script Task and Add Reference to Assembly(ZipFile Demo)
  2. Load Text Files,Zip them to Folder with Datetime and Delete From Input Directory in SSIS Package
  3. Extract Files From Zip Files and Delete the Zip Files once Unzipped in SSIS Package
  4. Load File/s, Zip and Delete from Source Folder in SSIS Package
  5. Zip ( Compress) Files and Add them to Folder According to Extension in SSIS Package
  6. Zip or Compress Files according to the Name of Files in SSIS Package 
  7. Get File names from Zip Files and Insert into SQL Server Table in SSIS Package
  8. How to Zip ( Compress) Multiple Folders and Delete in SSIS Package
  9. Create Zip file per day with Date Only and Add Files to it in SSIS Package
Excel Source and Destinations (Script Task- Dynamic)
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
  25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
  26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package
  27. How to read Cell Value from Excel by using Script Task in SSIS Package
Dynamic Text / CSV Files with Script Task
  1. How to Import Multiple Text or CSV files to SQL Server Table by using Script Task in SSIS Package
  2. How to Load Text /CSV files with same or less columns than destination table by using Script Task in SSIS Package
  3. How to load Data from Multiple Text / CSV Files to SQL Server Table with File Name by using Script Task in SSIS Package
  4. How to Create Tables Dynamically from Flat Files and load Data in SSIS Package
  5. How to load Flat files to SQL Server Tables according to the Name of Flat File in SSIS Package
  6. How to Create Multiple Text/CSV Files Dynamically From a SQL Server Table depending upon Distinct  Column Value in SSIS Package
  7. How to Export large table to multiple text/csv files by row count in SSIS Package
  8. How to create Text / CSV File Dynamically from Table or View in SSIS Package
  9. How to create Text / CSV File Dynamically from Stored Procedure Results in SSIS Package
  10. How to Load all text/csv files to single Text/CSV File from a folder in SSIS Package
  11. How to export all the tables from a database to CSV files with date-time in SSIS Package
  12. How to create Tables Dynamically from Tab Delimited Files and Load data in SSIS Package

Execute SQL Task

  1. Execute SQL Task Demo for Max Size of SQL Statement in Execute SQL Task Query Editor and Solution
  2. Execute SQL Task Demo- How to Build Dynamic SQL Query to Execute Multiple Stored Procedures those names are saved in a SQL Server Table
  3. Execute SQL Task Demo - How to Execute SQL Queries from a SQL Server Table in SSIS Package ( SQLStatementType= Variable)
  4. Execute SQL Task Demo - How to Execute SQL Queries from Excel file in SSIS Package ( Record Set Destination, Object Type Variable and Foreach Loop Container Demo as well)
  5. Execute SQL Task Demo - How to use Single Row Result Set in Execute SQL Task and Control the Flow Tasks by using that value ( Precedence Constraint Demo as well).
  6. Execute SQL Task Demo - How to use Insert Query in Execute SQL Task and Map the variables to Parameters ( Insert File Name and Record Count after Loading the File).
  7. Execute SQL Task Demo - How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package( Load File Name, Package Name , Record Count and File Load Time in SQL Server Table).
  8. Execute SQL Task Demo - How to build Query by using Variables in Execute SQL Task ( File Name validation and move files to Archive folder and Bad File Folder if does not validation correctly)
  9. Execute SQL Task Demo - How to use Full Result Set in Execute SQL Task ( Get Folder Path and File Names from SQL Table and Load those files)
  10. Execute SQL Task - How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package
  11. Execute SQL Task ( use Variable value in Expressions Demo) - How to Reject Already Loaded Files and Load Only New Files in SSIS Package
  12. Execute SQL Task ( Handle Single Row Result Set Blank Error) -Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package
  13. Execute SQL Task ( Save Result Set to Variable Demo) - How to Return Deleted and Update 
  14. Record Count from Execute SQL Task and Write to Flat File in SSIS Package
  15. Execute SQL Task ( Build Dynamic Query in Variable and use as Source ) - How to Save Query in Variable and Use in Execute SQL Task 
  16. Execute SQL Task Expression's Demo - Using Variable in IF Clause in Execute SQL Task in SSIS Package
  17. Execute SQL Task ( Parameter Mapping Demo for Stored Procedures) - How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters
  18. Read Single Cell Value from an Excel File in SSIS Package

File System Task

Expression Task

Transformations / Data Flow Task

 Data Flow Task

  1. How to Load Single File to Multiple Server, Change Destination Connection Dynamically
  2. How to Load Specific Excel Sheet from Excel File to SQL Server Table

OLE DB Command Transformation

  1.  Introduction to OLE DB Command Transformation( Perform Update/Delete)
  2. How to use Stored Procedure Output Parameter in OLE DB Command Transformation in SSIS Package?
  3. OLE DB Command Transformation - Insert Operation Demo
  4. OLE DB Command Transformation - Delete Operation Demo
  5. OLE DB Command Transformation - Update Operation Demo
  6. OLE DB Command Transformation - Call Stored Procedure with Input Parameters Demo
  7. OLE DB Command Transformation - Use Sub Query with Parameters in OLE DB Command Transformation to update records in a Table
  8. OLE DB Command Transformation ( Use Common Table Expressions to Delete Duplicate Records in OLE DB Command Transformation with Input Parameters)
  9. OLE DB Command Transformation ( How to Call Multiple Stored Procedure with input parameters in OLE DB Command Transformation in SSIS Package)
  10. OLE DB Command Transformation ( How to Run Multiple Statements Update/Insert/Delete with parameters in OLE DB Command Transformation in SSIS Package)
  11. OLE DB Command Transformation ( How to build Dynamic SQLCommand for OLE DB Command Transformation in SSIS Package)

Lookup Transformation

  1. How To Use Dynamic Query in Lookup Transformation in SSIS Package
  2. Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package 
  3. How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package
  4. Does Lookup Transformation perform Left Outer Join or Not in SSIS Package
  5. How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
  6. How to Handle "Row yielded no match during lookup". Error in SSIS Package
  7. How Lookup Transformation works for Duplicate Records in Reference Data Set
  8. How to Load Slowly Changing Dimension Type 1 by using Lookup Transformation ( UpSert Operation)
  9. Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler
  10. Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)

Cache Transformation

Sampling Transformations

  1. Row Sampling Transformation Demo
  2. Percentage Sampling Transformation in SSIS Package Demo

Conditional Split Transformation

  1. Intro to Merge Join Transformation in SSIS Package
  2. How to perform Cross Join in Data Flow Task in SSIS Package 

Merge Transformation

Audit Transformation

UnPivot Transformation 

Event Handlers

  1. What are Event Handlers in SSIS Package 
  2. What are the advantages of using Event Handlers in SSIS Package
  3. What Type of Tasks we can use in Event Handlers
  4. Understanding Executable in Event Handlers

Parent Child Packages

  1. Parent Child Package

Debugging SSIS Package

SSIS Package Deployment / Configuration

195 comments:

SQL Server DBA Tutorial

Below are the links that provide video learning on You Tube our channel " Tech Brothers" - These videos walk you through step by step of SQL Server 2014 DBA Tutorial/Training. Video Tutorials  provide beginner to advance level Training on SQL Server 2014 DBA Topics.


Installation

  1. How to find installed Features in windows 8.1
  2.  How to install SQL Server 2014 step by step
  3. How to Install SQL Server 2016 Step by Step
  4. How to Install Data Quality Services (DQS) in SQL Server
  5. How to Install and configure Master Data Services in SQL Server
  6. How to Install Data Quality Services Client
  7. How to Install SSIS in SQL Server 2014
  8. How to Create Integration Services Catalog
  9. How to install and configure Reporting services in SQL Server 2014
  10. How to Install SQL Server Data Tools ( SSDT )

SQL Server /Windows Cluster

  1. How to Install SQL Server 2014 in Cluster mode
  2. How to Add a Disk to Existing Cluster?
  3. Cluster Pre-requisites Checklist
  4. Setting Up Service Accounts for Cluster Nodes
  5. How to Install Clustering Services on the First Node
  6. How to Install Clustering Services on the second Node
  7. How to add a new Node to Cluster
  8. How to verify Cluster Installation
  9. Configure Distributed Transaction Coordinator(DTC) in SQL Server Cluster
  10. Active Active and Active Passive Cluster Configuration Overview
  11. How to Install SQL Server in Active Passive Cluster
  12. How to Configure Active Active Cluster
  13. Checklist before we start SQL Server Installation for Cluster
  14. How to Configure Static IP Address for SQL Server Instance/s in Cluster
  15. Fail over the Main Node Resource to Another Node
  16. How to Set Preferred Node Order for Failover
  17. Cluster Configuration Manager Demo: Manage SQL Server 2012 Failover Cluster
  18. How to Install Service Packs, Hot Fixed and Patches in Cluster
  19. Verify/Validate Cluster configuration
  20. How to Evict a Node from Cluster
  21. How to Remove SQL Server Cluster instance from a Node
  22. How to configure and use Windows Cluster Aware Updating Role - Part 1
  23. How to configure and use Windows Cluster Aware Updating Role - Part 2
  24. How to Rename Windows Cluster



Databases

  1. SQL Server Create Database Best Practices
  2. How to Create Database in SQL Server
  3. How to Attach and Detach Databases 
  4. How to Shrink Database And Database Files
  5. How to Take Database Offline and Bring it Online
  6. How to create Database Snapshot
  7. How to Script out an Entire Database in SQL Server
  8. How to change Compatibility level of a database and why do we do that?
  9. How to get Database out of Single User Mode in SQL Server?
  10. How to shrink MDF File of Database in SQL Server?
  11. How to Rename a Database in SQL Server?
  12. How to change database collation from Case Insensitive to Case Sensitive?
  13. How to Retrieve the suspect database ? 
  14. How to find out Owner of Any Database in SQL Server?
  15. How do I change the ownership of a database?
  16. How to avoid using C drive for Database Create in SQL Server?
  17. How to disable Auto growth of Database in SQL Server?
  18. How we can reduce Temp DB size with out restart Server?
  19. How to release unused space of the Tempdb to Operating System(OS)?
  20. How to update stats on all the databases on SQL Server or on Single Database in SQL Server?

Security

  1. What is Login, User, Role and Principals in SQL Server?
  2. How to create Windows Authenticated Login?
  3. How to create SQL Authenticated Login?
  4. How to create Windows Authenticated group login?
  5. How to check Login Status in SQL Server?
  6. How to disable/enable Login in sql server?
  7. How to create a Database user?
  8. How to map a user to an existing Login?
  9. How to create User define Schema in a database?
  10. How to create database role?
  11. How to create an application Role?
  12. How to provide explicit permission to specific tables to a user in a database?
  13. How to provide execute permissions to a specific store procedure?
  14. How to provide View definition permission of database objects to a user?
  15. How to create server Role in SQL Server?
  16. How to create Credentials in SQL Server?
  17. How to see the SQL Server user password in SSMS, Can we script out SQL Server user password?
  18. How to assign default Schema to AD Group in SQL Server?

Contained Database

Backup Database

Restore Database

How to Restore / Rebuild / Recover System Databases

Server Level Auditing

  1. How to Create Server Audit Specifications in SQL Server?
  2. How to create server level Audit in SQL Server?
  3. How to create server level Trigger in SQL Server?
  4. How to create Policies in SQL Server?
  5. How to create Policy Conditions in SQL Server?
  6. How to create and use Facets in SQL Server?

Data Collection and Management Data Warehouse

  1. How to configure Data collection in SQL Server?
  2. How to configure Management Data Warehouse in SQL Server?

Introduction to Extended Events 

  1. What is Extended Events in SQL Server?
  2. How to create an Extended event in SQL Server?
  3. How to Enable and Disable Extended Events in SQL Server?

SQL Server Configuration Manager

Replication

  1. What is replication, Types of replication and when to use each type
  2. Why do we use Replication, Provide couple scenarios
  3.  How to Configure Distribution in SQL Server Replication
  4. How to Create Snapshot Replication?
  5. How to Create Transactional Replication?
  6. How to create Merge Replication?
  7. When do we use snapshot replication
  8. What is re initializing means in replication
  9. What is Orphan replication And how would you cleanup replication
  10. Under what circumstances will you re initialize replication
  11. How the schema changes are handled in SQL Server Replication
  12. How would you add new tables in existing replication
  13. How would drop replicated database
  14. How will you truncate the replicated table
  15. Replication Error, can't connect to actual server, @@servername returns Null
  16. How to find out if Replicaiton is failed or broken
  17. How to setup Peer-to-Peer Transactional Replication?
  18. How to add Article in Peer-to-Peer Transactional Replication?
  19. How to create Merge Replication with Multiple Subscribers and Perform Diff Scenarios
  20. How to Change Sync Time for 60 Seconds to 0 Seconds in Merge Replication

Resource Governor

  1. What is Resouce Governor in SQL Server?
  2. How to Create Resource Pool in Resource Governor of SQL Server?
  3. How to Create WorkLoad  Group in Resource Governor of SQL Server?
  4. How to create Classifier Function in Resource Governor?
  5. How to Enable and Disable Resource Governor in SQL Server?

Mirroring

 

Linked Server

SQL Server Agent

  1. Overview of SQL Server Agent Configuration?
  2. How to create Job using SQL Server Agent?
  3. Job Categories Overview in SQL Server Agent?
  4. How to create an alert in sql server?
  5. How to create an Operator in SQL SERVER?
  6. How to run SQL Server Agent jobs in Batch file?
  7. How to Run SQL Server Agent Job from Local or Remote SQL Server Agent Job
  8. How to Enable/Disable SQL Server Agent Job, How to Enable Job after some days automatically?
  9. How To Delete ERROR LOGS IN SQL SERVER?
  10. How to get the detail for the cause of the SQL Server Agent job failure?
  11. How to send email to multiple email accounts from SQL Server Agent Job if fails?
  12. How to find out which SQL Server Agent Jobs are running at the moment on SQL Server?

Sql Server Proxies

Configure Database Mail

SQL Server Profiler

Maintenance Plan

  1. How to find blocking and deadlock in SQL Server?
  2. How to send Alter when a process is blocked in SQL Server

Server Level Settings

  1. Migration strategy for SQL Server 2008 to SQL Server 2012/ SQL Server 2014
  2. What is Difference between In place and Parallel Migration in SQL Server?
  3. Explain all the Steps or documents Or a checklist for Migration in SQL Server?
  4. Demo: In Place Migration of SQL Server 2012 to SQL Server 2014
           a) In-Place Upgrade of SQL Server 2012 to SQL Server 2014 Part 1
                    - Installation of Upgrade Advisor 2014 and Upgrade Advisor Overview
           b) In-Place Upgrade of SQL Server 2012 to SQL Server 2014 Part 2 
                   - Upgrading SQL Server 2012 Engine
                   - Upgrading SQL Server 2012 Management components
     5. Demo: Migration for SQL Server 2008 to SQL Server 2012 by using ( Backup/Restore) Method
          a)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 1
                   - Upgrade checklist overview
          b)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 2
                   - Identifying database
                   - Backing up all source databases
                   - Scripting out login
                   - Scripting out Server Roles
                   - Scripting out Backup devices
                   - Scripting out Server Level Triggers
                   - Scripting out Data Collection
                   - Script out Linked Servers
          c)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 3
                   - Mirroring Information from the database
                   - Script out All SQL Server Agent Jobs
                   - Viewing DBMail information on the source
                   - Scripting out Proxy and credentials
                   - Scripting out Operator and Alerts
                   - Scripting out SQL Server Configuration
          d)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 4
                   - Overview of Destination Checklist
                   - Restoring Databases on Destination
                   - Changing compatibility to SQL Server 2014
          e)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 5
                   - Transferring Logins (Executing Scripted Logins on destination server
                   - Transferring Replication (Executing Source Replication on destination)
                   - Transferring Linked servers (Executing Scripted Linked server)
                   - Transferring Server Level Triggers (Executing Scripted Triggers on destination server)
                   - Scripting out Operator and Alerts
                   - Server Audit and Audit Specification script execution
          f)  Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part 6
                   - Creating databases backup Maintenance plan
     6. How do Migrate Logins from SQL Server 2008 to SQL Server 2014?
     7. How to Migrate SQL Server Agent Jobs from SQL Server 2008 to SQL Server 2014?


SQL Server Database Deployment

  1. What does "Deployment" means for SQL Server DBA?
  2. What are the best practices for SQL Server Deployment?
  3. How to Perform a SQL Server Deployment Step by Step
  4. How to use Team Foundation Server (TFS) and DeploymentDocument for Database Deployment


AlwaysOn Availability Groups

  1. How to Setup/Configure AlwaysOn Availability Group in SQL Server 2014 Step by Step
  2. How to Resolve Availability Group Listner Errors
  3. Setup AlwaysOn Availability group when SQL Server instances are installed in Cluster mode Part1 ( 2 Node Scenario)
  4. Setup AlwaysOn Availability group when SQL Server instances are installed in Cluster mode Part2 ( 2 Node Scenario)
  5. AlwaysOn Availability Group on SQL Server Failover Instances  Installed in Cluster Mode ( 4 Node Scenario)
  6. How to provide Permissions to User on AlwaysOn Availability Group in SQL Server?
  7. How to Plan Backups with Always On Availability Groups in SQL Server
  8. AlwaysOn Availability Group Dashboard Introduction
  9. Setup Alerts for AlwaysOn Availability Group Failover in SQL Server
  10. How to Restore A database which is part of AlwaysOn Availability Group
  11. Patching Or Updating AlwaysOn Availability group Replicas in SQL Server Best Practices
  12. Patching or Updating Availability Group with SQL Server Failover Cluster Instance - Part 1
  13. Patching or Updating Availability Group with SQL Server Failover Cluster Instance - Part 2
  14. Patching or Updating Availability Group with SQL Server Failover Cluster Instance - Part 3
  15. Patching Or Updating SQL Server with multiple AlwaysOn Availability Groups
  16. Patching or Updating Availability Group with one Secondary
  17. How to Apply Windows Updates to Failover Clusters Hosting SQL Server AlwaysOn Availability Groups
  18. How to setup Replication on AlwaysOn Availability Groups in SQL Server Part1 Part2 

Miscellaneous

  1. What are Synonyms and How to Create Synonyms in a Database of SQL Server - DBA Tutorial
  2. How to compare these two databases and find what is the difference between these two databases?
  3. How to Scripts Users with permissions from SQL Server Database?
  4. How to find out who has deleted the Login in SQL Server?
  5. How to find out who has modified or created object in SQL Server?
  6. How to access SQL Server Instance from the network?
  7. How to know the reason why sql server restarted.
  8. How to find when and who dropped my database?
  9. How to find if a server in a cluster is running on Primary node?
  10. How to check the size of SQL Error Log in SQL Server?
  11. How to automate Back up and Restore
  12. How to migrate the integration services (SSIS)
  13. How to send Alter When Storage Drives are 80% filles from SQL Server
  14. How to Drop login with all users from multiple database in SQL Server?
  15. How to Find Users and their AD Groups in SQL Server?
  16. How to Find Port Number of SQL Server instance by using SQL Server Management Studio?

561 comments:
Subscribe to: Posts (Atom)