Advanced SQL Config [Enterprise Only]

[rockinthesixstring]

I wanted to start a thread with some advanced sql config for MailEnable Enterprise. I am going to start of with a few ideas that I have had in the past, and if anyone would like to add their stuff, please feel free!!!


------------------------------------------------------------------
NOTE:
All code provided in this ENTIRE thread is provided on an "AS-IS" basis, without warranty. Neither the authors nor MailEnable make any representation, or warranty, either express or implied.

[rockinthesixstring]

The first one I have is for all you MailEnable Enterprise AND ASSP users. This is a trigger to take any newly created domain and add it to your ASSP-Local-Domains.txt

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Update_ASSP]    Script Date: 07/05/2008 09:29:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Update_ASSP]
On [dbo].[DOMAIN]
After Insert
AS
Begin
DECLARE @DN varchar(128),
		@cmd varchar(512),
		@Filename varchar(128)

SET @Filename = 'C:\ASSP\files\Assp-Local-Domains.txt'

SELECT @DN = DomainName
FROM INSERTED

SET @cmd = 'echo ' + char(13) + @DN + char(13) + ' >> ' + @Filename
EXEC master..xp_cmdshell @cmd
End

[rockinthesixstring]

This simple trigger enables greylisting on a newly created mail account. So in your MailEnable settings, you tell it to handle greylisting on a "per mailbox" basis, and then run this trigger.

Every new user will have greylisting enabled but will be able to turn it off if they want to.

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Default_Greylisting_Per_User_Mailbox]    Script Date: 07/05/2008 19:18:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Default_Greylisting_Per_User_Mailbox] 
On [dbo].[MAILBOX] 
After Insert 
As 
BEGIN 
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '2',Postoffice + '/' + Mailbox,'SMTP-GreyListing','1' FROM INSERTED  
END

[rockinthesixstring]

This is a neat little trigger too.

By default when a new user is created, the mailbox spam filtering is disabled. This trigger enables spam filtering and delivers all spam to the junk email box... It can be changed, see notes below

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Default_Spam_Filtering_Per_User]    Script Date: 07/05/2008 19:22:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Default_Spam_Filtering_Per_User] 
On [dbo].[MAILBOX]
After Insert
As
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue)
SELECT '2', Postoffice + '/' + Mailbox, 'MailBox-SpamFilteringStatus','1' FROM INSERTED

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue)
SELECT '2', Postoffice + '/' + Mailbox, 'MailBox-SpamFilteringAction-High','JUNK' FROM INSERTED

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue)
SELECT '2', Postoffice + '/' + Mailbox, 'MailBox-SpamFilteringAction-Medium','JUNK' FROM INSERTED

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue)
SELECT '2', Postoffice + '/' + Mailbox, 'MailBox-SpamFilteringAction-Low','JUNK' FROM INSERTED

If you want "No Action" then

Code: Select all

bla bla bla,'' FROM INSERTED

If you want "Delete" then

Code: Select all

bla bla bla,'DELETE' FROM INSERTED

I could type out the other options, but I am too lazy... you can simply monitor the OPTIONS table as you make adjustments to see what the triggers would look like

[rockinthesixstring]

This trigger works in conjunction with the Per User Greylisting. It sets the postoffice up to use per user greylisting when a new postoffice is created.

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Default_Greylisting_Per_User]    Script Date: 07/05/2008 19:37:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Default_Greylisting_Per_User] 
On [dbo].[POSTOFFICE] 
After Insert 
As 
BEGIN 
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'SMTP-GreyListing','2' FROM INSERTED  
END

[rockinthesixstring]

This trigger works in conjunction with Default Junk Delivery. It sets the default action for the postoffice.

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Default_Junk_Delivery]    Script Date: 07/05/2008 19:42:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Default_Junk_Delivery] 
On [dbo].[POSTOFFICE] 
After Insert 
As 
BEGIN 
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Delivery-DeliverToStatus','1' FROM INSERTED  
END

[rockinthesixstring]

This really cool trigger sets your default postoffice cleanup agent for you. Now all of your users will be cleaned out at the same times keeping your junk storage to a minimum.

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Default_Mailbox_Cleanup]    Script Date: 07/05/2008 19:47:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Default_Mailbox_Cleanup] 
On [dbo].[POSTOFFICE] 
After Insert 
As 
BEGIN 
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Postmaster-Inbox-Enabled','1' FROM INSERTED  

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Postmaster-Inbox-Value','90' FROM INSERTED 

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Postmaster-Inbox-DeleteUnread','1' FROM INSERTED 

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Deleted-Enabled','1' FROM INSERTED 

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Deleted-Value','7' FROM INSERTED 

INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Agent-Purge-Deleted-DeleteUnread','1' FROM INSERTED 
END

[rockinthesixstring]

Here is a fun trigger that enables Public Folders for each new postoffice. This way you dont have to worry about logging in each time a client asks for this feature.

Code: Select all

USE [MailEnable]
GO
/****** Object:  Trigger [dbo].[Enable_Public_Folders_Per_Postoffice]    Script Date: 07/05/2008 19:49:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Trigger [dbo].[Enable_Public_Folders_Per_Postoffice] 
On [dbo].[POSTOFFICE] 
After Insert 
As 
BEGIN 
INSERT INTO OPTIONS(Scope, Query, OptionName, OptionValue) 
SELECT '1',[Name],'Public Folders','1' FROM INSERTED  
END

[rockinthesixstring]

I'd like to know if anyone ever used any of these triggers? There has been nearly 2000 views on this thread.