Advanced SQL Config [Enterprise Only]
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
Advanced SQL Config [Enterprise Only]
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.
------------------------------------------------------------------
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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.
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
If you want "No Action" then
If you want "Delete" then
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
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
Code: Select all
bla bla bla,'DELETE' FROM INSERTED
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9
-
- Posts: 844
- Joined: Mon Dec 05, 2005 7:51 am
- Location: Canada
Re: Advances SQL Config [Enterprise Only]
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
Server 2008 Standard (x64)
ME Ent 6.51 (SQL Server 2008 Config)
ASSP 1.9