Advanced SQL Config [Enterprise Only]

Discussion forum for Enterprise Edition.
Post Reply
rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Advanced SQL Config [Enterprise Only]

Post by rockinthesixstring » Sun Jul 06, 2008 2:10 am

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.
Last edited by rockinthesixstring on Fri Jan 14, 2011 2:34 am, edited 6 times in total.
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:12 am

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
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:20 am

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
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:30 am

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
Last edited by rockinthesixstring on Sun Jul 06, 2008 3:06 am, edited 2 times in total.
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:39 am

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
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:43 am

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
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:48 am

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

Last edited by rockinthesixstring on Sun Jul 06, 2008 2:51 am, edited 1 time in total.
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Post by rockinthesixstring » Sun Jul 06, 2008 2:51 am

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

Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

rockinthesixstring
Posts: 844
Joined: Mon Dec 05, 2005 7:51 am
Location: Canada

Re: Advances SQL Config [Enterprise Only]

Post by rockinthesixstring » Sat Jan 09, 2010 1:29 am

I'd like to know if anyone ever used any of these triggers? There has been nearly 2000 views on this thread.
Chase
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9

Post Reply