Database Administrator Job l1& l2 Responsibilities

 

DBA L1& L2 ROLE & RESPONSIBILITY 

 

L1 ACTIVTIY👉👉👉

·         Monitoring (DB Services up or not) manual go to chk in services .msc or sql server configuration manager option chk sql services & sql agent services up or not. Or using sql cmd option also If any issue related this highlighted to L2 on Immediate Basic &

·         Chk error log (xp_readerrorlog ,sp_readerrorlog) that day

·         Monitor Logshiping (check status is sysn or not) using Report option or manually using query. If not in sync then try to in sync & update the same L2

·         Monitor Mirroring (check status is sysn or not) using Report option or manually using query. if not in sync then try to in sync & update the same L2

·         Monitor Replication (check status is sysn or not) using Report option or manually using query. if not in sync then try to in sync & update the same L2

·         Monitor Always on (check status is sysn or not) using Report option or manually using query. if not in sync then try to in sync & update the same L2

·         Clustering Failover/Failback & services up& down

·         Space Monitoring (Disk Monitoring/File growth Monitoring (monitor through script/alert mail systems) File Monitoring )

·         Login & User Creations

·         Backup

·         Job Monitoring  Job Creations

·         Standby Installation GUI or using INI file base.

·         Lock block deadlock monitoring.

 

L2 Activity:-👉👉👉

·         DB Creation.

·         Switch over switch back log_shiping mirroring always on  

·         Migration ,Upgradation(login,role,db,jobs,audit,extended events, dB , mails ,alert bkp are required  while migration time .

·         Logshiping Configurations.👋

·         Mirroring Configurations.

·         Always on Configurations.

·         Replication Configurations.

·         Clustering Configurations.

·         Job Creation ,Fail issue resolved,L2 Issue resolve

·         DB  Mail  Configurations

·         Alert Creation

·         DB growth  Report Creation

·         Re-Indexing  of database ,missing index fragmentation  ,statics chk  maintenance Plan Creation  

·         Daily, weekly, db maintenance activity.

·         Database consistency chk & corruption issue fix.

·         Patching

·         Database Shrink

·         Deadlock issue resolved (index checking, long running query) & submit to concern team, on mail with query.

             

 

Setting up Email Alerts for SQL Server Deadlocks

Step #1: Configuring Database Mail 

---STEP 2

EXEC master.sys.sp_altermessage 1205, 'WITH_LOG', TRUE;

GO

EXEC master.sys.sp_altermessage 3928, 'WITH_LOG', TRUE;

GO

---STEP3

DBCC TRACEON (1204, -1)

DBCC TRACEON (1222,-1)

Step #3 Configure alerts and notifications operator

*alert 1 for 1205:

USE [msdb]

GO

/****** Object:  Alert [Error #1205: Deadlock detected] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Error #1205: Deadlock detected', 

@message_id=1205, 

@severity=0, 

@enabled=1, 

@delay_between_responses=0, 

@include_event_description_in=1, 

@database_name=N'APTECH', 

@category_name=N'[Uncategorized]', 

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

*alert 2 for #3928: 

USE [msdb]

GO

/****** Object:  Alert [Error #3928: Deadlock detected]  ******/

EXEC msdb.dbo.sp_add_alert @name=N'Error #3928: Deadlock detected', 

@message_id=3928, 

@severity=0, 

@enabled=1, 

@delay_between_responses=0, 

@include_event_description_in=1, 

@database_name=N'APTECH', 

@category_name=N'[Uncategorized]', 

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

-----------for Notification create operator

USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'SQLDBA', 

@enabled=1, 

@weekday_pager_start_time=90000, 

@weekday_pager_end_time=180000, 

@saturday_pager_start_time=90000, 

@saturday_pager_end_time=180000, 

@sunday_pager_start_time=90000, 

@sunday_pager_end_time=180000, 

@pager_days=0, 

@email_address=N'mumdbateam@gmail.com;banshbasti@gmail.com', 

@category_name=N'[Uncategorized]'

GO

-----Add Alert Option Response Options:-#1205: 

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error #1205: Deadlock detected',

 @operator_name=N'SQLDBA', @notification_method = 1

Go

--------------Add Alert Option Response Options:-#3928: 

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error #1205: Deadlock detected',

 @operator_name=N'SQLDBA', @notification_method = 1

Go

-------for testing purpuse table creation 

CREATE TABLE EMPDETAILS(EMPID INT)

INSERT EMPDETAILS SELECT 1

CREATE TABLE EMPDETAILS2 (EMPID INT)

INSERT EMPDETAILS2 SELECT 1

--Tran2 in new session

BEGIN TRAN

UPDATE  EMPDETAILS1 SET  ID= 1

--Tran3

BEGIN TRAN

UPDATE  EMPDETAILS2 SET  ID= 1

UPDATE  EMPDETAILS1 SET  ID= 1

--Let's update Demodeadlock2 table in our Tran2 session

UPDATE  EMPDETAILS2 SET  ID= 1

Step #1: Configuring Database Mail

sql server create user and grant permission

Create Login, User, assign Permission: SQL Server Tutorial 

---LOGIN  IS THE 3 STEPS PROCESS

---CREATE LOGINS

--- CREATE USERS

--- SET PERMISSION

---PUBLIC ( CONNECT & VIEW ANY DATABASE )

--PUBLIC ROLES (CONNECT TO DB PERMISSION)

--create logins

CREATE LOGIN BANSH WITH PASSWORD='Bnash@123'

---chk login info

sp_helplogins 'BANSH'

---create user for login

use AdventureWorks2012

create useR BANSH FOR LOGIN BANSH

---chk USER info

sp_helpUSER 'BANSH'

---set GRANT permission

GRANT SELECT,INSERT ON PERSON.ADDRESS TO BANSH --OTJ LEVEL

WITH GRANT OPTION---(OPTIONAL)

GRANT SELECT ON SCHEMA::SALES TO BANSH  ----SCHEAM LEVEL PERMISSION

---RESTRICET PERMISSION ON SALES SCHEMA SOME TABLES 

DENY SELECT ON SALES.CreditCard TO BANSH

---TAKE BACK PERMISSION 

REVOKE INSERT ON PERSON.ADDRESS from BANSH

--Msg 4611, Level 16, State 1, Line 1

--To revoke or deny grantable privileges, specify the CASCADE option.

--Note if we are using with grant option then give the erro rest of case its working f9 

--soln

REVOKE INSERT ON PERSON.ADDRESS from BANSH CASCADE

--set db level permission 

grant backup database to BANSH

 

---GRANT PERMISSION COLUMN LEVEL ON A TABLE 

GRANT SELECT ON HumanResources.Employee(BusinessEntityID,LoginID,JobTitle,CurrentFlag) to bansh

GRANT SELECT,update ON HumanResources.Employee(BusinessEntityID,LoginID,JobTitle,CurrentFlag) to bansh

--chk permission current db user permision

sp_helprotect

--chk permission table level   user permision

sp_helprotect [Person.Address]

sp_helprotect [HumanResources.Employee]

--chk the user permission on a table

-

sp_helprotect [HumanResources.Employee],bansh

sp_helprotect [Person.Address],bansh

--chk how many table/schema hv tht user permission

sp_helprotect null ,bansh

---

--using views to chk permission

select * from sys.database_permissions

where grantee_principal_id=(select  principal_id from sys.database_principals

where name='bansh')

--to chk schema names--

select * from sys.schemas where schema_id='9'

use [AdventureWorks2012]

GO

GRANT SELECT TO [OPODBA]

GO

use [AdventureWorks2012]

GO

GRANT SELECT ON SCHEMA::[HumanResources] TO [OPODBA]

GO

list of dbcc commands in sql server

       Concept and basics of DBCC Commands in SQL Server

What is DBCC?

Database Consistency Checker will help us to check the consistency of the database, this is also known as database console commands.

DBCC SQLPERF:

• Provides Transaction log space usage for all log files on an instance
• Used to clear out data related to weight statistics, latch statistics, or spinlock statistics

·         -- clear spinlock statistics: 

·         DBCC SQLPERF ("sys.dm_os_spinlock_stats", CLEAR); 

·           

·         -- clear latch statistics: 

·         DBCC SQLPERF ("sys.dm_os_latch_stats", CLEAR); 

·           

·         --  clear wait statistics  

·         DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR); 

 

 

DBCC CHECKDB

·         Checks logical and physical integrity of all the objects in the specified database by performing the following operations:

DBCC CHECKALLOC: - Checks consistency of disk space allocation structures for a      

                                         Specified database.

DBCC CHECKTABLE: - Checks the integrity of all the pages and structures that make  

                                         Up the table or indexed view.

DBCC CHECKCATALOG: - Checks for catalog consistency within the database.

 

 

DBCC SHRINKFILE

use BANSH_DC

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type_desc = 'ROWS'

GO

 

GO

DBCC SHRINKFILE (N'BANSH_DC', 3, TRUNCATEONLY)

GO

 

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS shows the actual status for a given statistic on a table or indexed view

*DBCC LOGINFO

 

Getting information about SQL Server virtual logs using DBCC LOGINFO

DBCC OPENTRAN

 

Open transactions in your transaction log that have not completed or have not been committed

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC DROPCLEANBUFFERS

This commands remove the all the data from sql server cache (buffer)

 

DBCC ERRORLOG

DBCC CHECKALLOC

 DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

 

SELECT * FROM SYS.dm_os_sys_info

SELECT * FROM SYS.dm_os_hosts

SELECT * FROM SYS.dm_os_schedulerss

SELECT * FROM SYS.dm_io_pending_io_requests

 

select * from sys.dm_db_index_usage_stats

 

select * from  sys.dm_exec_connections

select * from  sys.dm_exec_requests

select * from  sys.dm_exec_sql_text

 

 

 

 

How to move SQL database files to another location

Moving database files to another location

The New_location of db first we need to create  a folder created on a separate drive (in this specific case, we will change from a default C to E drive on a  machine) with sufficient disk space for  database files. Specified folder must be created first, in order to be used as a new location set steps..... here we will learn using 

attach& detach db  process  inducing offline/online steps......

---How to Create database

create database AptechMum

on primary

(name='AptechMum', filename='D:\Administrator\Downloads\Video\apt.mdf') 

log on 

(name='AptechMum_Log', filename='D:\Administrator\Downloads\Video\apt.ldf') 

sp_helpdb 'aptechmum'

---move the file another drive

 --- step 1

alter database AptechMum modify file

(name='AptechMum',filename='D:\Aptech_mum\apt.mdf')

go

alter database AptechMum modify file

(name='AptechMum_Log',filename='D:\Aptech_mum\apt.ldf')

--step 2

sp_helpdb 'AptechMum'

--step 3 set database offline mode

alter database AptechMum set offline with  rollback immediate

----now copy the datafile & paste it another file then 

--step 3 set database online mode

alter database AptechMum set online 

--chk databse status 

select DATABASEPROPERTYEX('AptechMum','Status') as Status

---how to add new data file in existing database 

-- before this we will discuss how to create filegroup

alter database AptechMum add filegroup ExamUP

--- add new datafile in exam up flegroup

alter database AptechMum add file

(Name='ExamUp_Lucknow',filename='D:\Aptech_mum\ExamUp_Lucknow.ndf')

to filegroup ExamUP

sp_helpdb 'AptechMum'

or

use AptechMum

sp_helpfile

--------set growith zero or stop auto filegrowth 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',filegrowth=0)

sp_helpfile

-------

--------set  size 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',size=2)

sp_helpfile

alter database AptechMum modify file 

(name='ExamUp_Lucknow',filegrowth=1024 KB)

sp_helpfile

---change the file logical  name 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',newname='ExamUp_Lucknow_Basti')

sp_helpfile

USE master;  

GO  

EXEC sp_detach_db @dbname = 'AptechMum';  

--now rename the file  name  manualy to go tht place  ExamUp_Lucknow to ExamUp_Lucknow_Basti

-- now attach the database

EXEC sp_attach_db @dbname = 'AptechMum';  

exec sp_attach_db @dbname='AptechMum',@filename1='D:\Aptech_mum\apt.mdf'

,@filename2= 'D:\Aptech_mum\ExamUp_Lucknow_Basti.ndf'

,@filename3='D:\Aptech_mum\apt.ldf'

go

use AptechMum

sp_helpfile

select DATABASEPROPERTYEX ('AptechMum','Status')

 

sql server dba commands with examples

   sql server dba commands with examples

This article on SQL Commands will give you a comprehensive knowledge on the various commands and statements used in SQL with the help of DBA Best Practices

---How to Create database

create database AptechMum

on primary

(name='AptechMum', filename='D:\Administrator\Downloads\Video\apt.mdf') 

log on 

(name='AptechMum_Log', filename='D:\Administrator\Downloads\Video\apt.ldf') 

----------CHK SQL VERSION

SELECT @@VERSION

---------- CHK SERVER NAME

SELECT @@SERVERNAME

----CHK HOST NAME 

SELECT HOST_NAME() AS PCNAME

--- ServicePack chk 

SELECT SERVERPROPERTY('productlevel') as ServicePack

---chk sql server Edition

SELECT SERVERPROPERTY('Edition') as Edition

---How to chk no's of databases in current instance

sp_helpdb

sp_helpdb 'APTECH'

or

select * from sys.databases

--- how to use command

use APTECH

-- chk the objects in database with types

sp_help

or

select * from sys.sysobjects

------- chk all sys table

select * from sys.sysobjects where xtype='s'

------- chk all usertables table

select * from sys.sysobjects where xtype='u'

-- find no'  of sp in db

select * from sys.sysobjects where xtype='P'

--find no'  of tigger in db

select * from sys.sysobjects where xtype='T'

                                         Transfer Logins in SQL Server

1- Step :- Create login with same name of orphan user & update the user Sid 

USE [master]
GO
CREATE LOGIN [BANSH] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

How to check orphan user & update the user Sid 

sp_change_users_login 'report'

sp_change_users_login 'update_one',bansh,bansh

2- Step

Using this store procedure:- sp_help_revlogin

Script:-👉

USE master
 GO
 IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
 GO
 CREATE PROCEDURE sp_hexadecimal
     @binvalue varbinary(256),
     @hexvalue varchar (514) OUTPUT
 AS
 DECLARE @charvalue varchar (514)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)
 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF'
 WHILE (@i <= @length)
 BEGIN
   DECLARE @tempint int
   DECLARE @firstint int
   DECLARE @secondint int
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
   SELECT @firstint = FLOOR(@tempint/16)
   SELECT @secondint = @tempint - (@firstint*16)
   SELECT @charvalue = @charvalue +
     SUBSTRING(@hexstring, @firstint+1, 1) +
     SUBSTRING(@hexstring, @secondint+1, 1)
   SELECT @i = @i + 1
 END
 
 SELECT @hexvalue = @charvalue
 GO
  
 IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
 GO
 CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
 DECLARE @name sysname
 DECLARE @type varchar (1)
 DECLARE @hasaccess int
 DECLARE @denylogin int
 DECLARE @is_disabled int
 DECLARE @PWD_varbinary  varbinary (256)
 DECLARE @PWD_string  varchar (514)
 DECLARE @SID_varbinary varbinary (85)
 DECLARE @SID_string varchar (514)
 DECLARE @tmpstr  varchar (1024)
 DECLARE @is_policy_checked varchar (3)
 DECLARE @is_expiration_checked varchar (3)
 
 DECLARE @defaultdb sysname
  
 IF (@login_name IS NULL)
   DECLARE login_curs CURSOR FOR
 
       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
 sys.server_principals p LEFT JOIN sys.syslogins l
       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
 ELSE
   DECLARE login_curs CURSOR FOR
 
 
       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
 sys.server_principals p LEFT JOIN sys.syslogins l
       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
 OPEN login_curs
 
 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
 IF (@@fetch_status = -1)
 BEGIN
   PRINT 'No login(s) found.'
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN -1
 END
 SET @tmpstr = '/* sp_help_revlogin script '
 PRINT @tmpstr
 SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 PRINT @tmpstr
 PRINT ''
 WHILE (@@fetch_status <> -1)
 BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
     PRINT ''
     SET @tmpstr = '-- Login: ' + @name
     PRINT @tmpstr
     IF (@type IN ( 'G', 'U'))
     BEGIN -- NT authenticated account/group
 
       SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
     END
     ELSE BEGIN -- SQL Server authentication
         -- obtain password and sid
             SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
         EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  
         -- obtain password policy state
         SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
         SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
  
             SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
 
         IF ( @is_policy_checked IS NOT NULL )
         BEGIN
           SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
         END
         IF ( @is_expiration_checked IS NOT NULL )
         BEGIN
           SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
         END
     END
     IF (@denylogin = 1)
     BEGIN -- login is denied access
       SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
     END
     ELSE IF (@hasaccess = 0)
     BEGIN -- login exists but does not have access
       SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
     END
     IF (@is_disabled = 1)
     BEGIN -- login is disabled
       SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
     END
     PRINT @tmpstr
   END
 
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
 CLOSE login_curs
 DEALLOCATE login_curs
 RETURN 0
 GO

Transfer Logins in SQL Server