Sp_who, Sp_who2

In order to troubleshoot performance problems or slowness we use the above stored procs.  Blocking, High I/O, High CPU and queries running parallelism can be seen in most cases. The First 50 results i.e results with SPID’s 1 to 49 represent system events and generally will not cause system slowdown. Users sessions are 50 and above. Multiple rows having the same SPID or SessionID are parallel processes.  The Blkby column is used to find out if any sessions are being blocked.

Sp_who: Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. When used with option active it gives the filtered information of those processes that are not idle.When used with userid  or sessionid the information retrieved belong to a specific user, or that belong to a specific session.

Syntax: Exec Sp_who

SP_who2: Is undocumented hence not supported by Microsoft but widely used as Sp_who2 provides extra columns compared to sp_who. Sp_who2 output results are same as sp_who with some extra columns and can be used to get more information about session id's.

Syntax: Exec Sp_who2

Common SQL Interview questions

1. What is Database?
2. Define primary key, foreign key and unique key?
3. Difference between clustered index and non clustered index?
4. What are the files associated with a database in SQL server?
5. What is normalization?
6. What is a join and what types of joins in SQL Server?
7. What are constraints in SQL Server?
8. What is a view? How to update a view in SQL Server?
9. Difference between Union and Union ALL?
10. Difference between Stored procedure and function?
11. Difference between truncate and delete?
12. What is a linked server?
13. What is difference between clustered index and non clustered index?
14. What is cursor and what are alternatives to cursor in sql server?
15. What are DBCC commands?
16. What is a trigger and types of triggers in SQL server?
17. What are isolation levels in SQL server?
18. What is SQL profiler why is it used?
19. What is Activity monitor why is it used?
20. What is SQL server agent?
21. What is SQL Server Browser?
22. What are ACID properties in a database?
23. What are DMV’s ?
24. What are the types of Backups in SQL server?
25. What is log shipping and why is it used?

Creating Mirroring in SQL Server 2012

A set by step approach of creating mirroring in SQL server 

Mirroring is one of the high availability solutions in SQL server, starting in 2005 version till date. It is a database level feature

Secondary Database  or the mirrored database is always in restoring mode (inaccessible  for users)

Step 1 :
Right click on the database, for which you are trying to set up Mirroring

Step 2 :
Click on configure security to start the  configure Database Mirroring security wizard
In this wizard you would be able to select primary server, mirror server, witness server (optional)

Note :
configuring witness server is necessary if you would need an automatic fail over enabled.

Step 3:
  Select primary server instance connect, default port select would 5022.

Step 4:
Select secondary server instance and connect, port would be 5023

Step 5:
Select witness server instance it can be express version too .

Step 6 :
you would be select service account for 3 instance if not domain account leave blank

Step 7 :
Summary page would let you to review before clicking finish

















Step 8 :
implementing of Endpoints.

click close, would pop with below screen




Start Mirroring : May warn you if computers are not associated with a domain for FQN ,, yes press yes it would move smoothly.

Status can be checked at the first screen

Interview Preparation

Behavirorial skills are as important as technical skills expecially in North American interviews. This questions are mostly part of Hr Interview. Personality or Behavior questions are used to assess how well you will fit in an environment and your likely behaviour in work enovironment.

Common Behavioral Questions:

1)    Tell me about yourselves?

This question is the first question to understand your skill set and what you can offer to this job. Answer should have your years of experience, Mention about skilled areas i.e trouble shooting, Performance tuning, security and access, Relevant education. Also talk about your recent job and explain how it helps this job.

2)    What Interests you in this job?

Talk about company/brand, Technical skills which are required for the job and skills you have and how they match, talk about the company culture and Environment.

3)    Why should we hire you?

This question is to see why you think you are good fit for the job. Talk about Your experience, skill set, how it helps them, your soft skills and team player skills, Positive attitude.

4)    What are your strengths and weakness?

Don’t say you’re a hard worker and punctual. You are expected to work hard and be punctual when you are getting paid.  Come up with something genuine. You can say you’re technically strong, Can work well in pressurized environments.  Weakness should sound like strength.

5)    Where you want to be in next 5 years?

You can say that you want to be expert or senior in the field you are working. Talk about your certification and new skill sets you think you need and how you are working to get to the position you want to be in 5 years

6)    What would your Ideal environment be?

This question is to know more about you. Some people can work well if it’s quiet environment and some people don’t. Some people can work in high pressure environments and some may not. Talk about if you are detailed or deadline oriented. Best answer would be trying to be a mix of both and choose the optimal one based on the scenario.

7)    Can you describe a scenario when you had a difference of opinion with a team member and how did you resolve it?

This question is to see how you deal with unpredictable and how best you can work in a team.  Working in a team sometimes we have difference of opinion on the best solutions, approaches and ego’s. Good answer is irrespective of your differences you communicate about the problem, analyze the solutions, address the question in terms of pro and con’s and selects the best solution/approach which is good for the team. Sometimes Choice’s made may go against your preference but we should move on with team spirit.

8)    What motivates you to do your job?

Talk about your interests and environment, challenges you are trying to find solutions for, Positive attitude.

9)    Tell us a scenario where you used your analysis skills or communication skills?

Talk about a problem why you felt it was challenging, Impact of it , Analysis on the causes of the problem and how you communicated with different parties to resolve the issue in a timely fashion.

10)   Do you have any questions for us?

Always ask this question. Ask about team size, what kind of role the job is, what kind of projects they handle.

Taking Backup's of all the databases except system databases

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Scripting the logins for recreating after Database Restoration

SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''

SELECT @sql =
'--======================================================================================' + CHAR(10) +
'--==== IMPORTANT: Before executing these scripts check the  details to ensure they  ====' + CHAR(10) +
'--==== are valid. For instance when crossing domains                                ====' + CHAR(10) +
'--======================================================================================' + CHAR(10)

PRINT @sql

SET @sql = ''
--========================================================
--script any certificates in the database
--========================================================

IF (SELECT COUNT(*) FROM sys.certificates) = 0
BEGIN
SELECT @sql = @sql + '/*No certificates found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all user certificates' + CHAR(10) +
'===================================================================================*/' + CHAR(13) + CHAR(13)
SELECT @sql = @sql + 'CREATE CERTIFICATE ' + name +
' ENCRYPTION BY PASSWORD = ''P@ssw0rd1''
WITH SUBJECT = ''' + issuer_name + ''',
EXPIRY_DATE = ''' + CONVERT(NVARCHAR(25), expiry_date, 120) + '''' + CHAR(13)
FROM sys.certificates

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--======================================================
--Script the database users
--======================================================
SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4
IF (SELECT COUNT(*) FROM #users) = 0
BEGIN
SELECT @sql = @sql + '/*No database users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @uid INT
SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the database, but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #users
SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' + dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE USER ' + QUOTENAME(dp.name) +
/*CASE
WHEN SUSER_SID(dp.name) IS NULL THEN ''
ELSE ' FOR LOGIN ' + QUOTENAME(dp.name)
END +*/
CASE
WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'
ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))
END +
CASE
WHEN dp.default_schema_name IS NULL AND dp.type <> 'G' THEN ' WITH DEFAULT_SCHEMA = [dbo]'
ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'
END + CHAR(13) + 'END'
FROM sys.database_principals dp LEFT OUTER JOIN
sys.schemas sch ON dp.principal_id = sch.principal_id
WHERE dp.principal_id = @uid AND dp.TYPE IN ('U', 'G', 'S') AND dp.principal_id > 4

PRINT @sql + CHAR(10)
DELETE FROM #users WHERE principal_id = @uid

SELECT @sql = ''
END

DROP TABLE #users
END

SELECT @sql = ''
--========================================================
--Script any users that are protected by a cert
--========================================================
IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No certificated users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name
    FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
  WHERE dp.type = 'C' AND dp.principal_id > 4
 
  PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=======================================================
--script all schemas
--=======================================================
SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)

--Script the permission grants on the schemas
SELECT @sql = @sql + CHAR(13) + dp.state_desc COLLATE latin1_general_ci_as + ' ' +
dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) +
' TO ' + QUOTENAME(dp2.name) + ' AS ' + QUOTENAME(dp3.name)
FROM sys.database_permissions dp
INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id
INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id
WHERE dp.class = 3  --dp.major_id BETWEEN 1 AND 8

PRINT @sql + CHAR(13) + CHAR(13)

SET @sql = ''

--========================================================
--script database roles from the database
--========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No database roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13)
FROM sys.database_principals dp INNER JOIN sys.database_principals dp2
ON dp.owning_principal_id = dp2.principal_id
WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--=========================================================
--script Application roles from the database
--=========================================================

IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0
BEGIN
SELECT @sql = @sql + '/*No application roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all application roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +
QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)
FROM sys.database_principals dp
WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--===============================================================
--got the roles so now we need to get any nested role permissions
--===============================================================
IF (SELECT COUNT(*) from sys.database_principals dp inner join sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id inner join sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0
BEGIN
SELECT @sql = + '/*No nested roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id
INNER JOIN sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id
WHERE dp.type = 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--================================================================
--Scripting all user connection grants
--================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0
BEGIN
SELECT @sql = + '/*No database connection GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database and connection GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +
dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id
WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=================================================================
--Now all the object level permissions
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj
ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0
BEGIN
SELECT @sql = + '/*No database user object GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO'
PRINT @sql --+ CHAR(10)
SET @sql = ''

IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL
BEGIN
DROP TABLE #objgrants
END
CREATE TABLE #objgrants(
state_desc VARCHAR(60)
, perm_name NVARCHAR(128)
, sch_name NVARCHAR(128)
, maj_ID NVARCHAR(128)
, name NVARCHAR(128)
, pr_name NVARCHAR(128)
)

DECLARE @state_desc VARCHAR(60)
DECLARE @perm_name NVARCHAR(128), @sch_name NVARCHAR(128), @maj_ID NVARCHAR(128)
DECLARE @name NVARCHAR(128), @pr_name NVARCHAR(128)

INSERT INTO #objgrants
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS maj_ID
, dbpr.name AS name
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE ']' END AS pr_name
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name

WHILE (SELECT COUNT(*) FROM #objgrants) > 0
BEGIN

SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,
@maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants

SELECT @sql = @sql + @state_desc + ' ' + @perm_name +
' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name
PRINT @sql
SET @sql = ''

DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name
AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name

END
PRINT CHAR(13)

DROP TABLE #objgrants
END

SET @sql = ''
--=================================================================
--Now script all the database roles the user have permissions to
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0
BEGIN
SELECT @sql = + '/*No database user role GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
PRINT '--Finished!'

Creating log shipping in SQL server

A step by step approach of creating log shipping in SQL server

Log shipping is one of the high availability solutions in SQL server, starting in 2005 version till date. It is a database level feature

Basic principal behind log shipping is backup and restores of database. 
Catch point in here is, process would be restoring Transaction logs to database in No recovery mode or standby mode (read only).

Requirements:

  1. Sysadmin  role is needed for the login to setup log shipping   

  2. SQL server edition other than express would work  for implement

  3. Primary database need to be in full or bulk-logged recovery model

  4. SQL user NT:service\Sqlagent need to have  access to the network folders

Step 1:

Right click on the database for which you trying to achieve log shipping 

 Step 2:

Check the box which says enables this as a primary database in a log shipping configuration

i.e would enable backup settings ..

Step 3:

Click on the backup setting to configure ,ie would pop up below screen

Step 4:

Network folder, where we would have our transaction log backups, if you are trying to achieve in same machine, do it with a share by sharing the folder   

Step 5: Configure back up option.. By clicking schedule, i.e frequency of occurrence, for minimum latency I would change it to 5 mins or leave it as 15 mins based on the requirement.

 Compression levels of the database backup.

Step 6: 

 Adding Secondary server, with log shipping we can have more than one secondary server.

Step 7:

Connect to the secondary server and chose the databases.

Initialize secondary Database:

We do have 3 options
1.      To generate a full back up on primary DB and restore it into secondary server (not recommended for large database and Prod DB’s 
2.      Restore using existing copy of full backup from Primary DB 
3.    Secondary DB is initialized (Second option  when done already by human ) 

Step 8:

Copy File tab, in here we need to provide the secondary server location where you like to have transaction logs copied.  And at the same time you have option to change the schedule of copy job

 Step 9:

Restore transaction Log tab, has the option to leave db in either no recovery mode (you cannot access the DB) or standby mode (read only DB)

Step 10:

Click on the okay to return to the main screen where you have option to set up a motoring instance, it’s like a witness server in mirroring concept, it uses a SQL Agent job to check on log shipping.

Check the box for using a monitor server instance. Settings button need to be clicked and would let you connect to SQL server which you would like to use as monitor instance (Preferable other than primary or secondary)

Set 11 : Job history retention is the option provided  

Setp 12 :

Click okay to finish, based on whether you have monitor instance or not total would vary, with monitor instance total steps would be 5 without 4

Script to check if log shipping is work

Create a table on primary db

  CREATE TABLE TEST_11

  (

   ID INT IDENTITY(1,1),

   Name varchar(20)

  )

  

Wait for 10 – 15 mins

ON THE SECONDARY DB run to script

IF (EXISTS (SELECT *

                 FROM INFORMATION_SCHEMA.TABLES

                 WHERE TABLE_SCHEMA = 'dbo'

                 AND TABLE_NAME = ' TEST_11'))

BEGIN

    Print 'working'

END