SQL Server Replication - Configuring Distributor

When setting up replication in SQL Server, the first thing to set up is Distributor.
The Distributor is a SQL server instance that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication.

Properties of Distributor

• Each Publisher can be assigned to a single Distributor instance only
• Multiple publishers can share a single Distributor

To Configure Distributor:

1. Connect to the SQL Server instance using Management Studio which you want to make as Distributor

Alert when the Scheduled job state is changed

When you create this trigger on the sysjobs table of msdb, it will send out an email alert whenever someone changes the state of the job from Enabled to Disabled or from Disabled state to Enabled.
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"

USE msdb
GO
CREATE Trigger tr_AuditJobEnable
ON sysjobs  
FOR UPDATE 
AS
DECLARE @UserName VARCHAR(50),  
@HostName VARCHAR(50),  
@JobName VARCHAR(100),  
@DeletedJobName VARCHAR(100),  
@Ins_EnabledFlag INT,  
@Del_EnabledFlag INT,  
@Body VARCHAR(200),  
@Subject VARCHAR(200), 
@Servername VARCHAR(50) 

SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()  
SELECT @Ins_EnabledFlag = Enabled FROM Inserted  
SELECT @Del_EnabledFlag = Enabled FROM Deleted  
SELECT @JobName = Name FROM Inserted  
SELECT @Servername = @@servername 

IF @Ins_EnabledFlag <> @Del_EnabledFlag  
BEGIN  

  IF @Ins_EnabledFlag = 1  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" ENABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
   END  

  IF @Ins_EnabledFlag = 0  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" DISABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)   
   END  

-- Send e-Mail 
Exec msdb..sp_send_dbmail
       @profile_name='DBA' -- Change to your Profile
      ,@recipients='segu.sandesh@gmail.com' -- Change Recipients
      ,@Subject=@Subject
      ,@Body=@Body
END

Delete Vs Truncate

Delete	Truncate
Delete is DML Command	Truncate is a DDL Command.
Delete Operation will not Reset Identity property of the column	Truncate Operation will Reset the Identity Property to the seed value that is defined for the column
Filters can be specified for Delete Operation (Where Condition)	Filters cannot be specified for Truncate Operation
Delete can be rolled back	Truncate can be rolled back if used within a Transaction
Delete statement uses row lock and each row is locked for deletion	Truncate statement uses Table or page Lock
Delete Operation is slower when compared to truncate because it logs an entry in transaction log for each deletion	Truncate Operation is faster because it is minimally Logged in transaction Log
Delete statements can activate triggers when defined	Triggers cannot be defined for Truncate Operation

Create and schedule SQL Server jobs with SSIS Packages

In my previous post, we have seen how to use SQL Server Import and export wizard to import or export data and save the package.
When you save the package that is generated using this wizard, it is actually an SSIS package.
You can also use Visual Studio to create complex SSIS packages which performs the data extraction, transformation, maintenance of database, cleanup, etc...

Import and Export Data in SQL Server

Importing or exporting data is a very frequently performed task to copy the data between different databases. When I say different databases, it can be the databases which is present in the same server or on different servers.
There are many ways to import or export data, like using Queries, in-built tools, 3rd party tools, etc...
One of the easy way using the inbuilt tools is by using the "SQL Server Import and Export Wizard"
Using SQL Server Import and Export Wizard, it is easy to select the type of source and destination and other options like Database, table, etc...