Skip to main content

Posts

Showing posts from October, 2014

Total IPs Required for fail-over clustering in SQL

How many Total ip's required build win&SQL Failover clustering Active/Active & Active/Passive? Ans. In a 2 node cluster each ip for each node and one ip for win cluster and one for msdtc and one for each cluster group .. If u use private network for heartbeat then 2 more ip .. So total of 5-7 In a two node cluster 7ip's are needed two private ip's for connecting to quarum. one for node A and other for node B, two public ip's one for node A and other one For node B and one for msdic and one for msclust and one ip for switch for connecting to san drives in which data is stored.  Total 7ip's are needed.

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles and assign server level/scope permissions. */ /* - Following actions are performed to implement this new feature: - Created Server role - Created Login and made member of server role - Granted Standard view permission After completion above steps the login has ability to make connection any database with data reader permission. */ USE master GO CREATE SERVER ROLE ALLDBREADER Go CREATE LOGIN [DBREADER] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER] GO GRANT CONNECT SQL TO ALLDBREADER GRANT VIEW ANY DATABASE TO ALLDBREADER GRANT VIEW ANY DEFINITION TO ALLDBREADER GRANT VIEW SERVER STATE to ALLDBREADER GRANT CONTROL SERVER TO ALLDBREADER DENY SHUTDOWN TO ALLDBREADER Go USE master GO CREATE SERVER ROLE ALLDBREADER Go GRANT CONTROL SERVER TO ALLDBREADER DENY ALTER ANY DATABASE TO

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you. USE Msdb Declare @lastRunDate int =replace(CAST(getdate()as date),'-','') SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ ON SJH.job_id = SJ.job_id where SJH.step_id = 0 and sjh.run_date =@lastRunDate and SJH.run_status =0