DB Administration

Alert Feature in Microsoft SQL Server

Alert feature in Microsoft SQL server is one of the prominent feature available in SQL server. Before we understand what Alert feature is and how to make use of the feature, first let us understand about basic meaning of Alert.

Alert is a warning or notification highlighted or notified to individual, group or other subsequent action for the predefined scenarios. Having alert system will keep away any of the future hazardous or hefty loss.

In SQL server Alert work similar as the Alert system available in any of the other systems available in other systems or process. To feel the alert system how it works you can consider the smartphones or any other mobile devices which through warning when there Is a low battery or any communication received.

As simple example earlier, SQL server alert system behaves the same way where it notifies the end user about any performance issues, Starting or stopping the process, intimating the end user through mail or other any communication methods.

SQL server Alert system is managed through SQL Server agent and SQL server agent should be up and running to make use of SQL server Alert System feature.

SQL Server Alert system can notify the end users through following communication methods.

• Email
• Net share
• Pager
• In SQL server 2014 there are three type Alert system
• SQL Server Event Alert
• SQL server Performance Condition Alert
• WMI Event alert
• The following steps will instruct to set up Alert system in SQL server

• Expand SQL server agent and make sure SQL server agent is up and running.

• Right click on the alert system and click new Alert

• Post to the previous step alert configuration window will be open. In the configuration window update the properties for the alert. In our example we have considered Alert system for SQL server performance condition alert type and the properties provided for Alert system will change according to the type of alert selected.

• Name: Provide the alert Name which is going to be the identification for the Alert.
• Type: Select the type of Alert (In this case we have selected SQL server performance condition alert.)
• Counter: Configure the counter to choose the required counter ( In our Case it SQL server databases)
• Instance : Configure the Instance for which Notification has to be processed ( Note: This is a counter instance and not server instance)
• Alert: Here we will configure when the alert should be configured. In this case alert will be triggered if ‘Dageop’ log file exceed 75%

• Move to the response tab provided in the left pane of configuration window. In the response window configure the Alert either to send the mail or to trigger some process or it might be both. In the below case we have considered both to send mail and also to trigger the ‘Flush_Dageop_Log’

• The above steps will create Alert called ‘Log_Exceed_Alert’ which can be later redefined by double clicking the Alert from Alert folder in SQL server agent. This Alert will trigger Flush_Dageop_Log package to reduce the growth of Dageop database log file and also notification will be provided to the end user.

 

How to verify and then create a specific SQL users?

It is always a best practise to validate whether the logins & user exists before creating a new one in SQL Server. You can verify this by using the system tables (syslogins & sysusers). I have given below T-SQL queries to validate first and then create. To access any specific database, a login creation is a mandatory. If the login already exists, you can still verify it with syslogins and then create the user for any new databases.

–Check the logins first for the SQL Server
USE [master]
GO
SELECT name FROM sys.syslogins;
GO
SELECT name FROM sys.sysusers;
GO

–Check the users for any specific databases.
USE [AdventureWorks2012]
GO
SELECT name FROM sys.sysusers
GO

After reviewing the available logins & users for the SQL Server and specific databases, and according to your company policies in naming convention, you then can create users accordingly. But before that need to drop the user if exists with the below queries.

–IF EXISTS DROP USER & LOGINS for a SQL user named ServiceAct_App1
USE [AdventureWorks2012]
GO
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
GO
USE [master]
GO
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
GO
IF EXISTS (SELECT name FROM sys.syslogins WHERE name = N’ServiceAct_App1′)
DROP LOGIN ServiceAct_App1
GO

In here, I am creating login & user after dropping it with if exists statement.

–CREATE LOGIN, CREATE USER, ASSIGN ROLES for Applications – Service Account
USE [master]
GO
CREATE LOGIN [ServiceAct_App1] WITH PASSWORD=N’YSM2015′, DEFAULT_DATABASE=[AdventureWorks2012], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]
GO
USE [master]
GO
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]
GO

 

What is XACT_ABORT in terms of implicit or explicit transactions?

When it comes to data modification statements for OLE DB systems, the XACT_ABORT option with ON and OFF functions helps the Developer/Administrator to decide based on the requirements from the business.

For example lets take a stored procedure that will complete fully, only if the UPDATE statements are completed. In this case the XACT_ABORT option should be set to ON like below.

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION

…

…

By setting this XACT_ABORT option to ON, it does not affect trapping of any errors using TRY-CATCH, but it helps in a scenario where the commit or rollback was successful or not.

 

 

All about Storage

In DBA Best practise training, I also cover the storage bits. This plays a vital role in IO and performance of every single application or user’s ad-hoc querying. As we know a database should have one MDF (Main Database File), 1 LDF (Log Database File) and can have more than 1 NDF (Secondary Database File). These files when stored across various drives improves the performance of the applications in one way. All data stored in tables are actually getting stored in these files. SQL Server gives flexibility, to store these data in both physical and logical components.

As you can see in below slide, the basic unit of storage is stored in these MDF, NDF via File Group concept, where you dedicate one filegroup location for one database file. Also this slide covers the concept of extents and its types (Mixed & Uniform).

 

Performance Analysis

When a bottleneck occurs due to performance problem, every DBA should start with initial analysis on why and where the Performance issue have triggered. I cover these topics in my DBA Best Practises training. This process should have Monitoring Tools, Monitoring Plan and then the Performance Analysis like it says in below slide.

 

In the Monitoring tools section, I cover the following as it says in the below slide with SQL Trace, Profiler, Statistics IO, Trace Flags, System Stored Procedures, DMV & DMF, DBCC, DTA, Performance / Activity monitors.

In Monitoring Plan, I cover mainly with a performance baseline, where a DBA should have clear understanding on the environment, client applications, database applications, network architecture, available system resources, etc.,

SQL Server Instance Maintenance

What does every single installed SQL Server Instance Maintenance covers? Below slide gives information on Monitoring Disk Space usage, Adding/Removing users, Moving objects for better performance, Reviewing and verifying system / error catalogs, to check database integrity, Server monitoring, adding / removing servers, Apply patches, etc.,

Create a new job from already existing job

You can create a brand new job by duplicating already existing job within the SQL Server instance. Open SSMS and then go down to SQL Server Agent and expand to see the JOBS and again expand to see all the available Jobs. From the below screen, I have chosen a maintenance plan job and then right click on it to see the available options on it and mouse over “Script Job as” and then further mouse over to “CREATE To” and then choose the relevant options for you. I would suggest to choose “New Query Editor Window” and this gives all the settings that is available for this job. You can choose this and rename wherever required and you can execute the same by creating a new job accordingly.

Automation of taking backup of all databases within single SQL Server instance

Below scripts will enable you to take backup of every single database except Model & TempDB databases, by using cursors on it. Strongle advise not to run this with very large databases or in OLTP environments where the systems are heavily used.

 

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
‘F:\’

DECLARE All_Backup_Cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN (‘Model’,’TempDB’);
OPEN All_Backup_Cursor
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
END
CLOSE All_Backup_Cursor
DEALLOCATE All_Backup_Cursor