Mustafa EL-Masry SQl server Administration

How to monitor your SQL Server instances..(1/10)?


Any DBA or DB Analyst need to know two important point

How to administrate DBs in a single click..?

How to monitor your SQL Server instances..?

this from my opinion first point  How to administrate DBs in a single click..? we covered the most important subject in this point in this Series posted by SHEHAP EL-NAGAR :

How to administrate your databases in a single click..?! (1/8)

How to administrate your databases in a single click..?! (2/8)

How to administrate your databases in a single click..?! (3/8)

How to administrate your databases in a single click..?! (4/8)

How to administrate your databases in a single click..?! (5/8)

How to administrate your databases in a single click ..?! (6/8)

Follow up  SHEHAP EL-NAGAR to complete this wonderful and amazing series thanks for AMRO SELIM for his Constructive and fruitful collaboration in this Series .

but this not the end because we will start very Wonderful and Impressive Series to complete our plan to help any DBA or DB Analyst in his/here job now you can mange your database  and administrate it but How to monitor your SQL Server instances..?

1- How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

2- How to Administrate and Monitor SQL Server Agent by T-SQL..?

3- How to Administrate and Monitor SQL Server Backup and Restore by T-SQL..?

4- How to Audit your SQL Server instance and database ..?

5-How To Monitor your instance and Database by Using ( DMV , Alerts , UCP Moitor , Third party tools..Etc)..?

and most of Question and topic i will cover it in this series to be repository for any DBA to Administrate and monitor his/here SQL Sever instance and Databases it will be DBA portfolio. Let’s Start

How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

At the first the goal from this post is making repository for scripts can help any DBA or DB Analyst to do Diagnoses on any MSSQL instance or Database by fast and easy way I Collected it after more Search and from the past Experience so if anyone has good script or idea can share it with us and we will share it by his Name

First thing instance level:

SQL and OS Version information :

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [MSSQL OS info];

Installation Data for MSSQL:

SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]
FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000;

Server properties:

SELECT SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS[ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus];

SQL Server Services information :

SELECT servicename, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services OPTION (RECOMPILE);

Hardware information for SQL Server Instance:

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)],
 committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Where the SQL Server Error log is located

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION(RECOMPILE);

Information about OS Cluster :

</pre>
SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

Instance Configuration Value:

SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

I am not the owner for this Scripts only i do wide Search on the internet to Collect it Follow me in the Next post to know more Scripts About how we can Diagnose MSSQL Instance|Database.

Mustafa EL-Masry Administration

Building Your Security Audit in SQL Server 2012


Introduction :

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store, and view audits on various server and database objects.

You can record either server audit action groups’ per-instance, and database audit action groups or database audit actions per database. The audit event will occur every time that the auditable action is encountered.

All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions

How SQL Server Audit Works:

Auditing a SQL Server instance is not anything new. Previous and current versions of SQL have had C2 auditing functionality, which can incur more overhead than necessary and is not always flexible. For example, if the c2 logs fill up the disk to which they are being saved, the SQL instance will shut itself down. The data being collected by c2 gives details about both failed and successful attempts to access or modify objects and data, but the results are not returned in a way to make it easy to determine the nature of the events, simply the events themselves.

SQL 2008 Enterprise version allows for you to quickly and easily configure SQL Server Auditing to automate your auditing needs. The simple explanation for how this works is as follows:

 

  1. Create a SQL Server Audit object
  2. Create an Audit Specification (can be at the server or database level)
  3. Turn it on

It really is that easy to get it up and running. The hard part will come later on when someone stops by to ask for the details surrounding specific events, but even that is not difficult to provide due to the inclusion of an audit log reader.

Audit

Building Your Security Audit by T-SQL Script :

i will Explain Now :

How to Build SQL SERVER AUDIT SPECIFICATION  in SQL Server 2012 by T-SQL.

How to Build SQL DATABASE AUDIT SPECIFICATION in SQL Server 2012 by T-SQL.

Hot Read your SQL Server Audit file by Fn_get_Audit_File Function.

SQL SERVER Audit-Server:

Now if you Need to build Audit on All your Server to Check for Example The FAILED_LOGIN_GROUPWhat is the Steps we will do it one by one that’s what i will Explain now .

At the First We Need to Check if you have SQL Server Audit Created or no on our Servers i will Check the server_audits and the Server_audit_specificationsc by using the DMV:

Check Your Server Audits:

Select * from Sys.server_audits
SELECT * FROM sys.server_audit_specifications

before the Creating i will Check if i have Server Audit by my new audit name or no if i have i will delete it

USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit-Server-FAILED_LOGIN_GROUP')
BEGIN
 ALTER SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
 WITH ( STATE = OFF );
 DROP SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
END
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'ServerAuditSpecification-FAILED_LOGIN_GROUP')
BEGIN
 ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
 WITH ( STATE = OFF );
 DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
END
GO

CREATE SERVER AUDIT :

CREATE SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
TO FILE
( FILEPATH = N'E:\My Work\SQLServer Audit\'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO
USE [master]
GO

Enable SERVER AUDIT :

ALTER SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
WITH ( STATE = ON );
GO

CREATE SERVER AUDIT SPECIFICATION:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
FOR SERVER AUDIT [Audit-Server-FAILED_LOGIN_GROUP]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = OFF)
GO

Enable SERVER AUDIT SPECIFICATION :

ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-FAILED_LOGIN_GROUP]
WITH ( STATE = ON );
GO

Now your SQL Server Audit Building on your Server level is Completed Let’s Check it and try to login with any Failed User or with correct user but with uncorrected password to test our New Audit

Check your SQL Server Audit from your SSMS(SQL Server Mangment Studio)
Audit2

Check your Audit File under your path of ‘E:\My Work\SQLServer Audit\’

Audit3

Now i tried to login with uncorrected user name DBA let’s go to Read the SQL Server Audit file by T-SQL to See if this action is saved or no

Read all information from a specific audit file:

SELECT * FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit-Server-FAILED_LOGIN_GROUP_CC232C99-8C93-4DAD-89A6-7CE7001F9364_0_130229499734310000.sqlaudit', null, null)
GO

Audit4

DATABASE AUDIT SPECIFICATION:[Audit_Database_Backup_and_Restore]

DATABASE AUDIT SPECIFICATION is SQL Server Audit on Database level. i will work in Database [AdventureWorks2012] and my Example will be How to Audit

Create Server Audit : 

USE [AdventureWorks2012]
GO
IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'DatabaseAuditSpecification-Backup_and_Restore')
BEGIN
 ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
 WITH ( STATE = OFF );
 DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore];
END
GO
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit_Database_Backup_and_Restore')
BEGIN
 ALTER SERVER AUDIT [Audit_Database_Backup_and_Restore]
 WITH ( STATE = OFF );
DROP SERVER AUDIT [Audit_Database_Backup_and_Restore]
END
GO
CREATE SERVER AUDIT [Audit_Database_Backup_and_Restore]
TO FILE
( FILEPATH = N'E:\My Work\SQLServer Audit\'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
GO

Enable SERVER AUDIT:

USE [AdventureWorks2012]
GO
ALTER SERVER AUDIT [Audit_Database_Backup_and_Restore]
WITH ( STATE = ON );

CREATE DATABASE AUDIT SPECIFICATION : DatabaseAuditSpecification-Backup_and_Restore

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
FOR SERVER AUDIT [Audit_Database_Backup_and_Restore]
ADD (BACKUP_RESTORE_GROUP)
GO

ENABLE DATABASE AUDIT SPECIFICATION:

ALTER Database AUDIT SPECIFICATION [DatabaseAuditSpecification-Backup_and_Restore]
WITH ( STATE = ON );
GO

Now try to take backup from Database Adventurework2012 then check the Audit file in ‘E:\My Work\SQLServer Audit’

Backup Database AdventureWork2012 :

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITHNOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Read the Audit File :

SELECT * FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_and_Restore_B3D67D19-2753-4B0C-8FB1-90B732A56DDD_0_130229523138080000.sqlaudit', null, null)
GO

Audit5

How to read SQL Server Audit :

Read all information from a specific audit file :

SELECT * FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_and_Restore_B3D67D19-2753-4B0C-8FB1-90B732A56DDD_0_130229523138080000.sqlaudit', null, null)
GO

Read all audit files with a specified name :

SELECT * FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_*.sqlaudit', null, null)
GO

Read all audit files with a specified name:

SELECT * FROM sys.fn_get_audit_file
('E:\My Work\SQLServer Audit\Audit_Database_Backup_*.sqlaudit', null, null)
GO
Mustafa EL-Masry General topics

Check the status of SQL Server Setting


When we need to Work in SQL Server we Must know What the Status of SQL Server Setting is Enabled or Disabled  this Setting like

  • QUOTED_IDENTIFIER
  • ARITHABORT
  • ANSI_NULL_DFLT_ON
  • ANSI_WARNINGS
  • ANSI_PADDING
  • CONCAT_NULL_YIELDS_NULL
  • ANSI_NULLS

Now How i Can Know if (QUOTED_IDENTIFIER) is Enabled or Disabled in My SQL Server Session before i Run my Query we Can use System Function SESSIONPROPERTY to know if (QUOTED_IDENTIFIER)  is ON or OFF .

Example :


SELECT SESSIONPROPERTY('QUOTED_IDENTIFIER')

1

If the Statement Return 1 that’s meaning is QUOTED_IDENTIFIER Setting is ON  in This Session otherwise is OFF that’s meaning  The QUOTED_IDENTIFIER Setting is OFF

Mustafa EL-Masry General topics

Difference between Count() and Count_Big() Funiction


In our Query Life when we need to get the Count of any Record in our Database Mostly we Use the COUNT() Aggregate Function the

Question Now SQL Server Have another  Aggregate Function COUNT_BIG() i asked my Self SQL Server Team Created this Function Whiteout any bin fits ……….. The answer is No . COUNT() and COUNT_BIG() it’s the Same Aggregate Function in the Use The Main Different between the two Aggregate Function it :

Count  Aggregate Function Return Int data type value .

COUNT_BIG()  Aggregate Function Return BigInt data type value .

So if you Have Millions Record in your table and you need to Return the Count of this Record when you use the Count() Function it will Return Error but the Count_Big() Function it will Work with you Simply in this Case .

Count() Aggregate Function :

Select Count(Column_Name) from Table_Name

Count_Big Aggregate Function :

Select Count_Big (Column_Name) From Table Name