Developing a quick and dirty bulk email infrastructure to clean your lists

If you want to send an email newsletter, I can only recommend you use:

• An email marketing software: http://email-marketing-software-review.toptenreviews.com
  
• Or preferably an email marketing service: http://email-marketing-service-review.toptenreviews.com
  

In our research for the ideal tool, we have found that services are weak on editing and layout and strong on reporting, whereas software is strong on editing and weak on opt-in/opt-out use cases and cleaning contact lists. There is no perfect tool. There is in my opinion an advantage to email marketing services because there is a workaround to their weaknesses which is to compose the newsletter in an HTML editing tool like Dreamweaver.

You may choose not to use these software and services, either to spare your marketing dollars or because you need specific features that these tools do not provide. In this case you will need to assess the following requirements and you will find that developing a bulk email infrastructure is no easy task:

• Editing and layout
  
• Contact list management
  
• Mail merge
  
• Bulk send performances
  
• Opt-in/opt-out use cases
  
• Cleaning contact list (email bounces)
  
• Reporting
  

In our case, we had to find a way to clean our contact list before considering a subscription to an email marketing service because their pricing is per contact and some contacts in our list were fairly old. To achieve that, we have spent a couple of days building a quick and dirty bulk email infrastructure comprising:

1. An SMTP server with two mailboxes, newsletter@domain.tld and dsn@domain.tld, where the first one has an autoresponder;
   
2. A bulk email tool including management of bouncing emails, which is only a GUI around devMail;
   
3. An opt-in/opt-out web page;
   
4. Integration with Google Analytics for reporting (not in the sample).
   

The process to build, send and analyse a newsletter is the following:

1. Design the newsletter in Dreamweaver (use %%dbfield%% for mail merge)
   
2. Check the newsletter against the SPAM checker of iContact using a trial account
   
3. Open the newsletter in IE and save as web archive (*.MHT) with embedded images
   
4. Open the MHT file in the bulk email tool (configured to use a specific database and SMTP server)
   
5. Build the text version of the HTML newsletter
   
6. Click send
   
7. A few days later, open the tool and click Analyze to interpret delivery status notifications and tag bouncing emails
   
8. Open the mailbox in Outlook to handle manually the notifications which could not be interpreted
   

You can download the source code here. This tool does not measure up with the software and services mentioned above, but it offers a convenient way to purge a contact list before subscribing to an email marketing service.

Schedule your backup transfers

This is a follow-up of my recent article entitled "Automated SQL Server backups".

After automating SQL server backups on a remote server, you will probably want to transfer them to an FTP server and you will want this to be automatically done every day.

Using FTP

Windows command-line FTP.exe has the ability to use scripts like in FTP –script:"C:\backup.ftp" where backup.ftp is a text file containing a series of FTP commands:

open ftp://ftp.yourdomain.tld/
username
password
bin
cd /backup
put "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DBName Mondays.bak"
quit

Accordingly, you can schedule the following VB script to execute daily using windows task scheduler:

Dim ftp, dir1, dir2, s, d, bak, fso, sf, shell, cmd
ftp = ftp://ftp.exe/
dir1 = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" '—- Local dir
dir2 = "/backup" '—- FTP dir (do not include / at the end)
s= "backup.ftp"
d=weekday(date)
select case d
case 1
bak="DBName Sundays.bak"
case 2
bak="DBName Mondays.bak"
case 3
bak="DBName Tuesdays.bak"
case 4
bak="DBName Wednesdays.bak"
case 5
bak="DBName Thursdays.bak"
case 6
bak="DBName Fridays.bak"
case else
bak="DBName Saturdays.bak"
end select

Set fso = CreateObject("Scripting.FileSystemObject")
Set sf = fso.CreateTextFile(dir1 + s, True)
sf.WriteLine("open ftp.yourdomain.tld")
sf.WriteLine("username")
sf.WriteLine("password")
sf.WriteLine("bin")
sf.WriteLine("cd " + dir2)
sf.WriteLine("put " + chr(34) + dir1 + bak + chr(34))
sf.WriteLine("quit")
sf.Close

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -s:" + chr(34) + dir1 + s + chr(34)
shell.Run cmd, 8, true

Workaround when Passive FTP is required

When running the command above, you may get the following FTP error "425 – Could not open data connection to port 2512: connection refused" depending on the infrastructure.

There is a good chance that you need passive FTP. Contrary to what some people say, windows command-line FTP is capable of passive mode, but the "literal PASV" command returns a port number which has to be opened using "literal PORT" and this cannot be easily scripted.

The workaround is to use ncftp software and the following VB script instead of the script above:

Dim ftp, dir1, dir2, d, bak, shell, cmd
ftp = "C:\Program Files\NcFTP\ncftpput.exe"
dir1 = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" '—- Local dir
dir2 = "/backup" '—- FTP dir (do not include / at the end)
d=weekday(date)
select case d
case 1
bak="DBName Sundays.bak"
case 2
bak="DBName Mondays.bak"
case 3
bak="DBName Tuesdays.bak"
case 4
bak="DBName Wednesdays.bak"
case 5
bak="DBName Thursdays.bak"
case 6
bak="DBName Fridays.bak"
case else
bak="DBName Saturdays.bak"
end select

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -u username -p password ftp.yourdomain.tld " + dir2 + " " + chr(34) + dir1 + bak + chr(34)
shell.Run cmd, 8, true

Downloading backups from your FTP server to your workstation or a local server

A similar approach can be used to schedule backup downloads from the FTP server to your workstation or a local server which has tape backup.

Dim ftp, dir1, dir2, shell, cmd
ftp = "C:\Program Files\NcFTP\ncftpget.exe"
dir1 = "/backup/" '-- FTP dir
dir2 = "C:\BACKUP" '-- Local dir (do not include \ at the end)
Dim bakArray(6)
'-- Sundays
bakArray(0) = "DBName Sundays.bak"
bakArray(1) = "DBName Mondays.bak"
bakArray(2) = "DBName Tuesdays.bak"
bakArray(3) = "DBName Wednesdays.bak"
bakArray(4) = "DBName Thursdays.bak"
bakArray(5) = "DBName Fridays.bak"
bakArray(6) = "DBName Saturdays.bak"

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -u username -p password ftp.yourdomain.tld " + chr(34) + dir2 + chr(34) + " "

For Each bak In bakArray
'ncftpget is sufficiently intelligent to only download newer files
cmd = cmd + chr(34) + dir1 + bak + chr(34) + " "
Next
cmd = Trim(cmd)
shell.Run cmd, 8, true

What is next?

The above can be improved one step further by launching the script directly from SQL Server jobs using an Execute T-SQL Statement Task which would possibly generate and call the script with master.dbo.xp_cmdshell. This would secure the FTP credentials in SQL Server and launch the transfer immediately at the end of a back-up.

Automated SQL Server Backups

SQL Server 2000

1. Open SQL Server Enterprise Manager
   
2. Register your server
   
3. Right-click the node Management > Backup, then select menu Add backup devices... We create devices "DBName Mondays.bak" to "DBName Sundays.bak" corresponding to each day of the week but you can use your own naming convention. We have assumed that DBName is the name of the database to backup.
   
4. Right-click the node Databases > DBName, then select menu option All Tasks > Backup database...
   
5. The SQL Server Backup – DBName dialog box is displayed.
   
6. On the General tab, rename the backup "DBName Mondays", click the Remove button if an item appears in the backup to list, then click Add...
   
7. In the Select Backup Destination backup select Backup Device and "DBName Mondays" then click OK.
   
8. Make sure Database - complete and Overwrite existing media are selected.
   
9. Check schedule and click ... to edit. Rename the schedule "DBName Mondays" and schedule to occur weekly on Mondays. Click OK.
   
10. On the Options tab, click Verify backup upon completion and Remove inactive entries from transaction log. Keep other options unchecked.
    
11. Go through steps 4 to 10 with "DBName Tuesdays" to "DBName Sundays".
    
12. Go to the Management > SQL Server Agent > Jobs node, refresh the list and check the 7 backup jobs which you have just created.
    

SQL Server 2005

This is slightly more complicated in SQL Server 2005 because you cannot use the backup dialog which has no scheduling option. You need to use a maintenance plan. A maintenance plan is an SSIS package, which means you either need SSIS or SP2 installed, because SP2 includes a limited version of SSIS for maintenance tasks including backups.

1. Open SQL Server Management Studio (Run as Administrator on Windows Vista).
   
2. Right-click the node Server Objects > Backup Devices and select menu "Add Backup Device...".
   
3. Name your backup device "DBName Mondays" and make it a file named "DBName Mondays.bak".
   
4. Repeat steps 2 and 3 for "DBName Tuesdays" to "DBName Sundays".
   
5. Start and configure SQL Server Agent which is a requirement for maintenance plans.
   
6. Right click the node Management > Maintenance Plans and select menu "New Maintenance Plan..."
   
7. Rename your plan "DBName Plan".
   
8. Double-click "Subplan_1" to edit its properties.
   
9. Name it "DBName Mondays" and create a schedule of the same name recurring weekly every Monday.
   
10. Drop a "Back Up Database Task" from the toolbox onto the light yellow design surface.
    
11. Right-click the task on the design surface and select menu "Edit...".
    
12. Select the database(s) you want to back up, check option "Back up databases across one or more files", then click button "Add..." and select the backup device called DBName Mondays.
    
13. Select "Overwrite" if backup file exists, check "Verify backup integrity" and click OK.
    
14. On top of the list of subplans of "DBName Plan", click "Add Subplan".
    
15. Go through steps 9 to 14 with "DBName Tuesdays" to "DBName Sundays".
    
16. Go to the SQL Server Agent > Jobs , refresh the list and check the 7 backup jobs which you have just created.
    

For both SQL Server 2000 and SQL Server 2005, you can consider adding tasks to shrink databases, rebuild indexes, check database integrity in the scheduled jobs, but this goes beyond the scope of this article. In a following article, we will show you how to schedule FTP transfers to push and/or pull the backup files.