Wednesday, 2 August 2017

SQL Server - To check all logins and permission and other important details of SQL Server User.

Easy script-

To check all logins and permission and other details


SELECT
p.name as 'Name',
Case p.type
When 'S' Then 'SQL login'
When 'U' Then 'Windows login'
When 'G' Then 'Windows group'
When 'R' Then 'Server role'
When 'C' Then 'Login mapped to a certificate'
When 'K' Then 'Login mapped to an asymmetric key' End as 'Type',
Case sp.type When 'COSQ' Then 'CONNECT SQL' End as 'Permission Type',
sp.[state_desc] as 'Permission State',
p.is_disabled AS IsDisabled,
LOGINPROPERTY(p.name, N'IsExpired') AS IsExpired,
LOGINPROPERTY(p.name, N'IsLocked') AS IsLocked,
LOGINPROPERTY(p.name, N'IsMustChange') AS IsMustChange,
CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,
CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,
LOGINPROPERTY(p.name, N'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(p.name, N'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(p.name, N'PasswordLastSetTime') AS PasswordLastSetTime,
LOGINPROPERTY(p.name, N'LockoutTime') AS LockoutTime,
LOGINPROPERTY(p.name, N'DaysUntilExpiration') AS DaysUntilExpiration,
p.create_date AS CreateDate,
p.modify_date AS LastModifieddate
FROM sys.server_principals AS p
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = p.principal_id
LEFT OUTER JOIN sys.sql_logins AS sl ON sl.principal_id = p.principal_id
WHERE
(sp.[type] = N'COSQ' and sp.type is not null)
and p.type not in ('R', 'C', 'K')
And p.name not like '##MS_%' --Filter out Certificate-based SQL Server Logins (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine)
Order By p.Type ASC, p.name ASC


Wednesday, 10 May 2017

Step by step Log Shipping configuration in SQL Server? OR Step by step Log Shipping configuration using SSMS window wizard and using SQL Script?


Important note and configuration details-

·         Primary Server : INHYIZLP10744

·         Secondary Server : INHYIZLP10744\Instance1

·         Database Name: Logshipping_Test_DB

·         All MSSQLServer service and SQL Agent service running with same account on both server.

·         Database in Full or bulk logged recovery model only

·         Collection should be same

·         Connect both server with same SQL Server user account (User or service account should and sysadmin permission on both server )


Please click on below link to  download word file-

Step by step Log Shipping configuration in SQL Server


This doc have below both type of configuation steps-

1. Using User interface (UI)
2 Using SQL server script

Please let me know if you know any other method to do the same - Jainendra Verma

Tuesday, 9 May 2017

Important Commands and Script of Replication in SQL Server


--Note-
--It should be noted that the data available in
--MSrepl_commands,
--MSrepl_transactions,
--sp_browsereplcmds
--is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

 select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno

-- It will show the latency in Sec

Select object_name, counter_name, instance_name, round(cntr_value/1000,0) as latency_sec 
from sys.dm_os_performance_counters 
where object_name like '%Replica%' and counter_name like '%Logreader:%latency%'
 union
Select object_name, counter_name, instance_name, round(cntr_value/1000,0) as latency_sec 
from sys.dm_os_performance_counters 
where object_name like '%Replica%' and counter_name like '%Dist%latency%'

-- To get Distribution Agent performance and history its status and time and latency and many important details

USE distribution
go
SELECT TOP 100 time,
Cast(comments AS XML) AS comments,
runstatus,
duration,
xact_seqno,
delivered_commands,
average_commands,
current_delivery_rate,
delivered_transactions,
error_id,
delivery_latency
FROM msdistribution_history WITH (nolock)
ORDER BY time DESC

-- To get log reader agent history its status and time and latency and many important details

USE distribution
GO

SELECT time,
CAST(comments AS XML) AS comments,
runstatus,
duration,
xact_seqno,
delivered_transactions,
delivered_commands,
average_commands,
delivery_time,
delivery_rate,
delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min
FROM mslogreader_history WITH (nolock)
WHERE time > '2014-10-28 16:00:00.130'
ORDER BY time DESC

-- It will give the info of table and it size in distribution database
--If you see high rowcount (like 1 or 2 million) this means there is some problem in replication. 

Clean-up job (this is in distribution server) is not running
Its taking lot of time to deliver the commands to subscriber
There may be blocking in distribution server due to clean-up job

USE distribution
GO
SELECT Getdate() AS CaptureTime,
Object_name(t.object_id) AS TableName,
st.row_count,
s.NAME
FROM sys.dm_db_partition_stats st WITH (nolock)
INNER JOIN sys.tables t WITH (nolock)
ON st.object_id = t.object_id
INNER JOIN sys.schemas s WITH (nolock)
ON t.schema_id = s.schema_id
WHERE index_id < 2
AND Object_name(t.object_id)
IN ('MSsubscriptions', --contains one row for each published article in a subscription
'MSdistribution_history', --contains history rows for the Distribution Agents associated with the local Distributor
'MSrepl_commands', --contains rows of replicated commands
'MSrepl_transactions') --contains one row for each replicated transaction
ORDER BY st.row_count DESC


-- Use the below query to identify what is going on currently in the distribution server. (You can use the same query in any server for the same purpose)

SELECT r.session_id,
s.program_name,
s.login_name,
r.start_time,
r.status,
r.command,
Object_name(sqltxt.objectid, sqltxt.dbid) AS ObjectName,
Substring(sqltxt.text, ( r.statement_start_offset / 2 ) + 1, ( (
CASE r.statement_end_offset
WHEN -1 THEN
datalength(sqltxt.text)
ELSE r.statement_end_offset
END
- r.statement_start_offset ) / 2 ) + 1) AS active_statement,
r.percent_complete,
Db_name(r.database_id) AS DatabaseName,
r.blocking_session_id,
r.wait_time,
r.wait_type,
r.wait_resource,
r.open_transaction_count,
r.cpu_time,-- in milli sec
r.reads,
r.writes,
r.logical_reads,
r.row_count,
r.prev_error,
r.granted_query_memory,
Cast(sqlplan.query_plan AS XML) AS QueryPlan,
CASE r.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS Issolation_Level,
r.sql_handle,
r.plan_handle
FROM sys.dm_exec_requests r WITH (nolock)
INNER JOIN sys.dm_exec_sessions s WITH (nolock)
ON r.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(r.sql_handle) sqltxt
CROSS apply
sys.Dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) sqlplan
WHERE r.status <> 'background'
ORDER BY r.session_id
go



-- To get Error details of Replication

Use Distribution
go

select * from dbo.MSrepl_errors where error_code in ('2601','2627','25098')

-- To get Articles details

select publisher_id,
publisher_db,
publication_id,     
article,     
article_id    ,
destination_object, 
source_owner  ,
source_object,      
description   ,
destination_owner  from distribution.dbo.MSarticles

--Both query provides, Sequence number and command id and other replicated commands details

Select 
publisher_database_id,
xact_seqno,
type,
article_id,
originator_id,
command_id,
partial_command,
command,                                                                                                                                  hashkey,    
originator_lsn from  distribution.dbo.MSrepl_commands


select publisher_database_id,     xact_id,      xact_seqno,   entry_time  from distribution.dbo.MSrepl_transactions

--To get undistributed commands (Pending cound and estimated process time)


sp_replmonitorsubscriptionpendingcmds 

 @publisher ='INHYIZPC01014A\SQL_INSTANCE3',
 @publisher_db = 'Replication_DB',
 @publication ='Enter name of publication', 
 @subscriber ='INHYIZPC01014A\SQL_INSTANCE2',
 @subscriber_db ='Replication_DB',
 @subscription_type ='0' --0 for push and 1 for pull

 -- using following SP you will get publication name --EXEC sp_helppublication;


 --************START -- To find the the issue **************--

 -- Copy "Sequence Number" form the error and use in below query.


SELECT  art.publisher_id ,
        art.publisher_db ,
        art.publication_id ,
        art.article ,
        art.article_id  ,
        art.destination_object  ,
        art.source_owner  ,
        art.source_object
FROM    distribution.dbo.MSarticles AS art
        JOIN distribution.dbo.MSrepl_commands AS com
        ON art.Article_id = com.Article_id
WHERE   com.xact_seqno = 0x00000024000002F0000400000000


--The above query will return publisher_database_id and article_id.which we will use in below query-


EXEC distribution.dbo.sp_browsereplcmds

     @xact_seqno_start = '0x00000024000002F0000400000000' ,
     @xact_seqno_end = '0x00000024000002F0000400000000' ,
     @publisher_database_id = 1 ,
     @article_id = 1 ,
     @command_id = 1

--The above query will return Command value, this is the duplicate value query which has entered in the table of subscriber end.

 --************END-- to find the the issue **************--


 --To show subscriber status for transactional publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 0

--To show subscriber status for snapshot publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 1

--To show subscriber status for merge publications:

sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 2


--********** Execute at the Distributor in the master database **********-- 

USE master; 
go 

exec sp_helparticle @publication=<publication name>

--Is the current server a Distributor? 
--Is the distribution database installed? 
--Are there other Publishers using this Distributor? 

EXEC sp_get_distributor 


--Is the current server a Distributor? 

SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername; 


--Which databases on the Distributor are distribution databases? 

SELECT name FROM sys.databases WHERE is_distributor =

--What are the Distributor and distribution database properties? 

EXEC sp_helpdistributor; 
EXEC sp_helpdistributiondb; 
EXEC sp_helpdistpublisher; 

--********** Execute at the Publisher in the master database **********-- 

--Which databases are published for replication and what type of replication? 

EXEC sp_helpreplicationdboption; 

--Which databases are published using snapshot replication or transactional replication? 

SELECT name as tran_published_db FROM sys.databases WHERE is_published = 1; 

--Which databases are published using merge replication? 

SELECT name as merge_published_db FROM sys.databases WHERE is_merge_published = 1; 

--What are the properties for Subscribers that subscribe to publications at this Publisher? 

EXEC sp_helpsubscriberinfo; 


--********** Execute at the Publisher in the publication database **********-- 

--What are the snapshot and transactional publications in this database? 
 
EXEC sp_helppublication; 

--What are the articles in snapshot and transactional publications in this database? 

--REMOVE COMMENTS FROM NEXT LINE AND REPLACE <PublicationName> with the name of a publication 
--EXEC sp_helparticle @publication='<PublicationName>'; 

--What are the merge publications in this database?  

EXEC sp_helpmergepublication; 

--What are the articles in merge publications in this database? 

EXEC sp_helpmergearticle; 
-- to return information on articles for a single publication, specify @publication='<PublicationName>' 

--Which objects in the database are published?

SELECT name AS published_object, schema_id, is_published AS is_tran_published, is_merge_published, is_schema_published 
FROM sys.tables WHERE is_published = 1 or is_merge_published = 1 or is_schema_published =
UNION 
SELECT name AS published_object, schema_id, 0, 0, is_schema_published 
FROM sys.procedures WHERE is_schema_published =
UNION 
SELECT name AS published_object, schema_id, 0, 0, is_schema_published 
FROM sys.views WHERE is_schema_published = 1; 


--Which columns are published in snapshot or transactional publications in this database? 

SELECT object_name(object_id) AS tran_published_table, name AS published_column FROM sys.columns WHERE is_replicated = 1; 

--Which columns are published in merge publications in this database? 

SELECT object_name(object_id) AS merge_published_table, name AS published_column FROM sys.columns WHERE is_merge_published = 1; 

--If we done changes on Agent profile option (Replication Monitor --> right click Agent profile ---> for Distribution agent)

--To STOP the Distribution Agent:

sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--To START the Distribution Agent:

sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db