Cluster parameter checklist before installation

Trigger : Reject all login from mixed authentication except 'test' login from one server name 'Server1'

/*


Created by : Aditya Jha

Created On : 22/06/20012

Purpose : Reject all login from mixed authentication except 'test' login from one server name 'Server1'

*/

Create TRIGGER tr_reject_mixauth_logon ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @ClientHost nvarchar(max);

SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

IF (host_name() !='SUNMOSSSQL01' and suser_name() in ('test')) or
suser_name() in (select loginname from sys.syslogins where password is not null and loginname <> 'test')
ROLLBACK;
END;

--DROP TRIGGER tr_reject_mixauth_logon ON ALL SERVER;

IFI : Instant file initialization in sql server 2005 and 2008

Instant File initialization - pros and cons


http://www.sqlservercentral.com/articles/Recovery/89392/

http://www.networkworld.com/community/blog/instant-file-initialization-ifi-faster-databa



It is a very interesting topic, which has brought quite some discussions at times.

Here is how it works - the Instant File Initialization(IFI) was introduced in SQL 2005 and allows us to grow large files quickly without waiting for minutes (depending on the file size and the speed of disks). Basically, when the file is created initially, it is not 'zeroed' out if the IFI is used, which in turn diminishes the total time of file creation.

If you wanted to enable this feature you would go to the Local Security Policy MMC (run the following command to get there: "%windir%system32secpol.msc /s"). Then go to 'Local Policies' and then to 'User Rights Assignments'. From there you would find the 'Perform volume maintenance tasks' item and add the account under which the SQL Server Service is running.

There are some caveats, of course: if the SQL Server service is running under local system account, then you cannot revoke this right. If it is running under a domain account or as a local account which is not part of the Administrators group, then you will need to add this right.

Why is the topic debatable, though? Because, if you use the Instant File Initialization, then you will be able to grow the files easily, however when SQL Server Storage engine gets to write the data to the pages, as the pages are accessed, they need to be zeroed before the data can be written. The latter means a write delay, of course.
Also, there is a security risk, since all pages from the disc system are included in the backup and could be potentially read if the backup is lost. (If I were to 'misplace' a database backup, I would worry about a few other things before I think of the possibility to read the non-zeroed pages :) )
By the way, a SQL Server Service restart is required to get the policy working for SQL Server.

So, what would be the solution? Well, it depends: either use the IFI or don't use it. A good middle ground is to find the golden value of your data file growth value - a number which is not too big and not too small, so the SQL Server can grow the files and zero them out fast enough.

Search string or object used in all databases

USE [master]

GO



CREATE Procedure [dbo].[sp_Find2]

@SearchText1 varchar(100) = ''

, @SearchText2 varchar(100) = ''

, @SearchText3 varchar(100) = ''

, @SearchText4 varchar(100) = ''

, @DBName sysname = Null

-- , @PreviewTextSize int = 200

, @SearchDBsFlag char(1) = 'Y'

, @SearchJobsFlag char(1) = 'Y'

, @SearchSSISFlag char(1) = 'Y'

As

/*

* Test: sp_Find4 'KEYWORD 1' -- Search for 1 keyword across all objects/DBs/SSIS/Jobs

* sp_Find4 'KEYWORD 1', 'KEYWORD2' -- Search for 2 keywords across all objects/DBs/SSIS/Jobs

* sp_Find4 'track', NULL, NULL, NULL, 'Common'

* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'Y', 'N', 'N' --DB Only

* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'N', 'N', 'Y' --SSIS Only

*/

Set Transaction Isolation Level Read Uncommitted;

Set Nocount On;



DECLARE @PreviewTextSize int

SET @PreviewTextSize = 200



Create Table #FoundObject (

DatabaseName sysname

, ObjectName sysname

, ObjectTypeDesc nvarchar(60)

, PreviewText varchar(max))--To show a little bit of the code and other info



Declare @SQL as nvarchar(max);



/**************************

* Database Search

***************************/

If @SearchDBsFlag = 'Y'

Begin

If @DBName Is Null --Loop through all normal user databases

Begin

Declare ObjCursor Cursor Local Fast_Forward For

Select [Name]

From Master.sys.Databases

Where [Name] Not In ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Model', 'TempDB')

and state_desc = 'ONLINE'

-- ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Master', 'MSDB', 'Model', 'TempDB');



Open ObjCursor;



Fetch Next From ObjCursor Into @DBName;

While @@Fetch_Status = 0

Begin

Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject (DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select Distinct

''' + @DBName + '''

, sch.[Name] + ''.'' + obj.[Name] as ObjectName

, obj.Type_Desc

, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')

From sys.objects obj

Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id

Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id

Where mod.Definition Like ''%' + @SearchText1 + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';

-- Order By ObjectName';



Exec dbo.sp_executesql @SQL;



Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject ( DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME, TABLE_TYPE, ''Table definitions are not set up yet''

From information_schema.tables

Where TABLE_NAME like ''%' + @SearchText1 + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_executesql @SQL;





Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject ( DatabaseName , ObjectName, ObjectTypeDesc, PreviewText)

Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME+''.''+COLUMN_NAME, ''COLUMN'', DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) + '')''

From information_schema.columns

Where COLUMN_NAME like ''%' + @SearchText1 + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_executesql @SQL;





Fetch Next From ObjCursor Into @DBName;

End;



Close ObjCursor;



Deallocate ObjCursor;

End

Else --Only look through given database

Begin

Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject (DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select Distinct

''' + @DBName + '''

, sch.[Name] + ''.'' + obj.[Name] as ObjectName

, obj.Type_Desc

, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')

From sys.objects obj

Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id

Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id

Where mod.Definition Like ''%' + @SearchText1 + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_ExecuteSQL @SQL;

End;



Select 'Database Objects' As SearchType;



Select

DatabaseName

, ObjectName

, ObjectTypeDesc As ObjectType

, PreviewText

From #FoundObject

Order By DatabaseName, ObjectName;

End



/**************************

* Job Search

***************************/

If @SearchJobsFlag = 'Y'

Begin

Select 'Job Steps' As SearchType;





Select j.[Name] As [Job Name], s.Step_Id As [Step #]

, Replace(Replace(SubString(s.Command, CharIndex(@SearchText1, s.Command) - @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As Command

From MSDB.dbo.sysJobs j

Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id

Where s.Command Like '%' + @SearchText1 + '%'

AND s.Command Like '%' + COALESCE(@SearchText2, '') + '%'

AND s.Command Like '%' + COALESCE(@SearchText3, '') + '%'

AND s.Command Like '%' + COALESCE(@SearchText4, '') + '%';



End



/**************************

* SSIS Search
***************************/

If @SearchSSISFlag = 'Y'

Begin

Select 'SSIS Packages' As SearchType;



Select [Name] As [SSIS Name]

, Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@SearchText1, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -

@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As [SSIS XML]

From MSDB.dbo.sysDTSPackages90

Where Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + @SearchText1 + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText2, '') + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText3, '') + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText4, '') + '%';

End
GO

Trigger to block host ips

-- This will create a trigger to stop host ips to connect sql server




CREATE TRIGGER tr_logon_hostname_blacklist

ON

ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

DECLARE @ClientHost nvarchar(max);

SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

-- ClientHost gives IP except if the connecting to SQL Server from instance machine.

-- Do NOT put '' in this otherwise you will block client access from instance machine.

IF @ClientHost IN ('10.168.178.71','10.168.178.7','10.168.178.8','10.168.178.4','10.168.178.70')

ROLLBACK;

END;





-- To drop the trigger.



DROP TRIGGER tr_logon_hostname_blacklist ON ALL SERVER;

DBCC LOGINFO

Follow the link for better knowledge on log file information
http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/

Deeper Look at DBCC LOGINFO

Run the DBCC LOGINFO and each one representing a VLF

FileId   FileSize      StartOffset      FSeqNo    Status      Parity CreateLSN
-------- ------------- ---------------- ---------- ---------- ------ ---------
2        253952         8192            30        2           64     0
2        253952         262144          0         0           0      0
2        253952         516096          0         0           0      0
2        278528         770048          0         0           0      0

These columns have the following meaning:

• FileID – the FileID number as found in sysfiles
• FileSize – the size of the VLF in bytes
• StartOffset – the start of the VLF in bytes, from the front of the transaction log
• FSeqNo – indicates the order in which transactions have been written to the different VLF files. The VLF with the highest number is the VLF to which log records are currently being written.
• Status – identifies whether or not a VLF contains part of the active log. A value of 2 indicates an active VLF that can't be overwritten.
• Parity – the Parity Value, which can be 0, 64 or 128 (see the Additional Resources section at the end of this article for more information)
• CreateLSN – Identifies the LSN when the VLF was created. A value of zero indicates that the VLF was created when the database was created. If two VLFs have the same number then they were created at the same time, via an auto-grow event.

Find the list of server on Network

Method 1 :

Set SQLCMD mode => Use below query

!!SQLCMD -L

Method 2 :

use below query:

EXEC master..XP_CMDShell 'OSQL -L'