Computed columns - Persisted Vs Non-persisted‏

http://www.sqlservercurry.com/2013/02/computed-columns-persisted-vs-non.html



There are two types of computed columns namely persisted and non-persisted. 

There are some major differences between these two

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns

A computed column is not physically stored in the table, unless the column is marked PERSISTED.

Send Job failure description details when ever a job fails

USE [msdb];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/* drop trigger trg_stepfailures */
Alter TRIGGER [dbo].[trg_stepfailures] ON [dbo].[sysjobhistory]
    FOR INSERT
AS
    DECLARE @strcmd VARCHAR(800) ,
        @strRecipient VARCHAR(500) ,
        @strMsg VARCHAR(2000) ,
        @strServer VARCHAR(255) ,
        @strTo VARCHAR(255);

    DECLARE @Subject VARCHAR(500);


    IF EXISTS ( SELECT  *
                FROM    inserted
                WHERE   run_status = 0
                        AND step_name NOT IN ('job outcome'))
        BEGIN
            SELECT  @strMsg = @@servername + '-Job: ' + sysjobs.name
                    + '. Step= ' + inserted.step_name + 'Message '
                    + inserted.message
            FROM    inserted
                    JOIN sysjobs ON inserted.job_id = sysjobs.job_id
            WHERE   inserted.run_status = 0;

            SELECT  @Subject = 'Job ' + sysjobs.name + ' Failed on Job Server'
                    + @@Servername
            FROM    inserted
                    JOIN sysjobs ON inserted.job_id = sysjobs.job_id
            WHERE   inserted.run_status = 0;

            SET @strRecipient = 'naresh.koudagani@xyz.com';
            EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DbMail', --uses the default profile
            @recipients = @strRecipient,
@subject = @Subject,
            @body = @strMsg,
@body_format = 'HTML'; --default is TEXT


        END;

How to Connect SSIS to Always on Availability Groups Listener:

 How to Connect SSIS to Always on Availability Groups Listener:

Step1: Open up Visual Studio

Step2: Tools->Connect to Database

Step3: Enter Listener name , then go to advanced

Step4: Change Multisubnetfailover=True from False

Step5: Test connection with your windows Account or SQL account

Step6:Create OLEDB Connection Manager

 From then create new OLEDB connection manager, choose FPSQL1Listener and the choose login, database name etc.

First a database connection must be made with multisubnet failover=true

Index and stats rebuild on Always on HADR VS Replication

HADR:
If I need to rebuild indexes, can I do this on the primary?
Index operations are fully logged and will be replicated to the secondaries.



Replication:
CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.
we need to rebuild stats also 

Disable or Enable all SQL Agent Jobs

declare @sql nvarchar(max) = '';
select
@sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;
' from msdb.dbo.sysjobs
where enabled = 1  --1= Enabled, 0 is disabled
order by name;

print @sql;
--exec (@sql);

Disable or enable alerts , change all alerts notification Operator

--Check All Alerts:
SELECT * from msdb.[dbo].[sysalerts]


 --Disable All Alerts
 UPDATE msdb.[dbo].[sysalerts]
SET [enabled] = 0
WHERE [enabled] = 1


  --Enable All Alerts
 UPDATE msdb.[dbo].[sysalerts]
SET [enabled] = 0
WHERE [enabled] = 1



---Change Alerts Notify operators
SET NOCOUNT ON
DECLARE @Alert_Names TABLE
(
AlertName SYSNAME NOT NULL,
Operator_name Varchar(30) NULL
)
Declare @operator_name Varchar(30)='SQLOperDBA'
 Declare @notification_method int = 1 ;
INSERT INTO @Alert_Names(AlertName,Operator_name)
SELECT s.name,@operator_name
FROM msdb.[dbo].[sysalerts] s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.name

DECLARE @Alert_name SYSNAME
DECLARE @Alert_id UNIQUEIDENTIFIER


DECLARE ChangeOperator CURSOR FOR
SELECT Alertname,operator_name
FROM @Alert_Names


OPEN ChangeOperator
FETCH NEXT FROM ChangeOperator INTO @Alert_name,@operator_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_add_notification  @alert_name, @operator_name,1

FETCH NEXT FROM ChangeOperator INTO @Alert_name,@operator_name

END

CLOSE ChangeOperator
DEALLOCATE ChangeOperator

SID in SQL Server Login and windows login

https://www.mssqltips.com/sqlservertip/2705/identifying-the-tie-between-logins-and-users/

For SQL Server-based login, the SID is generated by SQL Server.

For Windows users and groups, the SID matches the SID in Active Directory.