This is part of a look back at the history of SQL Server Central as a part of our 25-year celebration in Feb 2026.

"They want how much?"

That was a question I got from Andy one afternoon as we discussed how we were going to manage the growth of SQL Server Central. I had gotten a quote from Lyris for over $200,000 for the upgrade to their software that would let us send 500,000 emails at a time. We had been using their product with (I think) a 200,000 cap and we were running out of room. The growth of the site meant that we needed to send more and more emails.

We decided to dig in a bit and think about another solution. We were making money, but not that much. Without a payment plan, we couldn't afford the software. After checking with a few other services, we realized they would be as expensive and we would also be in the business of syncing our mailing list with them, as well as getting a list of bounces/broken emails back from them and updating our database.

Eventually, we decided to experiment by sending our own emails. I had learned a lot about how to set up our networking and DNS to comply with email send requirements in the early 2000s and was confident I could keep us off of blacklists. We also were smart data professionals and software architects, so how could we approach the problem?

Our Environment

We had a single SQL Server database (SQL Server 2000) that contained our list of users and their subscription preferences. This was something along the line of this code (there were other columns, but they're not relevant).

CREATE TABLE dbo.Users
( UserID INT NOT NULL IDENTITY(1,10) CONSTRAINT UserPK PRIMARY KEY
, Username VARCHAR(50)
, UserEmail VARCHAR(200)
, Active TINYINT
, DailyNewsletter BIT
, WeeklyNewsletter BIT
)
GO

We also had a Newsletter table, where we generated the daily newsletter and stored a copy of this. That table was something like this:

CREATE TABLE Newsletter
( NewsletterID INT NOT NULL IDENTITY(1,1) CONSTRAINT NewsletterPK PRIMARY KEY 
, NewsletterDate DATETIME
, Newsletter text
)
GO

From these tables, we had a process running in a script that would read the current day's newsletter, stripping the time from the datetime field. We would then extract a list of all Users who were subscribed to the newsletter. Daily newsletters ran on Monday and Thursday back then, and the weekly one ran on Saturdays. Since we started the night before, code needed to run on Sunday, Wednesday, and Friday, calculating the proper date for the next date and getting a list of all users. Anyone with a "1" in the DailyNewsletter or WeeklyNewsletter field was included in the extract.

This info was sent to a Lyris process via an API that created a new send with a list of users. After sending, we would get a list of error emails from Lyrics in a log file. This contained hard bounces, which were used to update our Users table and set the appropriate newsletter field to 0 for that email. Unsubscribes were handled similarly, but from the website code.

A New Architecture

The system ran on Windows servers, so we wanted to keep with a technology we were familiar with at the time. C# and dotNet were relatively new, but we had been using them in our day jobs. We found a component that would let us send SMTP emails and didn't cost much, so we decided to use that and design what amounts to a high volume queueing system in SQL Server. Remember, this was SQL Server 2000, so no Service Broker.

In hindsight, I'm glad it didn't exist as we might have tried to use that and complicated the system.

We decided to start small and use some of our existing process. We created a table to hold our emails to be sent. This was a simple table with a copy of the information we used in the Lyris process. The goal was to link back to the newsletter table for the text to send and copy the email from the user table. We also wanted to know when this was sent.

CREATE TABLE EmailNewsletter
( ID INT IDENTITY(1,1) PRIMARY KEY
, SendDate DATETIME
, NewsletterID INT
, UserEmail VARCHAR(200)
, sent BIT
)

This table would be loaded with something like this. Imagine I'm sending a newsletter on Monday, Feb 9. I would run this statement to get the NewsletterID and then load that with a list of users to whom to send the newsletter.

DECLARE @id INT;
SELECT @id = NewsletterID
FROM dbo.Newsletter
WHERE NewsletterDate = '2026-02-09';

INSERT dbo.EmailNewsletter
(
    SendDate,
    NewsletterID,
    UserEmail,
    sent
)
SELECT '2026-02-09',
       @id,
       u.UserEmail,
       0
FROM Users u
WHERE u.DailyNewsletter = 1
      AND u.Active = 1;
GO

This gave me a list of people to send to with the newsletter. Our process could read a user with a query like this:

SET ROWCOUNT 1;
SELECT ID,
       SendDate,
       Newsletter UserEmail,
       sent
FROM dbo.EmailNewsletter
    INNER JOIN dbo.Newsletter
        ON Newsletter.NewsletterID = EmailNewsletter.NewsletterID
WHERE SendDate = '2026-02-09'
      AND sent = 0;

That would get me an email, the newsletter text, and I could run that through our SMTP component and voila, email sent.

Scarce Resources

As experienced DBAs and software developers, we knew this wasn't optimal, but this was a test. The first draft. We built this and it worked. We could use the system to send ourselves an email by hand-loading the emailNewsletter table with just our emails.

However, running this on our IIS/SQL Server wasn't a good idea. We could easily have our email sending use all the resources and slow the site. Or, worse, we wouldn't get emails sent in time.

We talked this through, looking for holes, and found quite a few.

First, we don't need to retrieve the newsletter text over and over. Sure, this might be in the buffer pool, but why chance things. Our first alteration was to add a column to the Newsletter table to mark when a newsletter needed to be sent.

ALTER TABLE dbo.Newsletter ADD ToBeSent TINYINT

We could mark everything with zeros until it was time to send. This way the SenderApp wouldn't need to know things and if we were sending early/late, it could track this. We had an Agent Job that was designed to just set this to 1 for tomorrow's newsletter at 7pm. This mean on Sunday at 7pm, this code ran and set tomorrow's newsletter as valid:

UPDATE dbo.Newsletter
SET tobesent = 1
WHERE NewsletterDate = DATEADD(d, DATEDIFF(DAY, 0,GETDATE()), 1)

This job also loaded the EmailNewsletter table with a list of current users. Our SenderApp could then query the newsletter table, get the test of the newsletter and then start querying the list of users.

The second issue we knew would be a problem would be the constant querying of the Users table. We like to work in batches in T-SQL, so we decided to query a batch of users. We experimented with batches of 10, 50, 100, 500, etc. to find what worked really quickly with minimal load. I think we decided on something like 500 at a time. Instead of a SET ROWCOUNT 1, we would SET ROWCOUNT 500, with the appropriate indexes on date and sent to make this run quickly.

As each email was sent, we ran an update statement to set EmailNewsletter.Sent to 1 for the appropriate email address.

Considering Failures

We're database people, so we expect things to break. That's why we have backups. So, what's the backup here?

If our SenderApp fails, how do we know where to start? We know what was sent, though potentially we have an RPO of 1 where SenderApp sends an email and then dies before it can update the database. How can we handle a failure. As we talked this through, we decided to use a technique that we've both seen in past scheduled imports of files from business partners. We'd mark a started time in the table. This code is similar to what we did:

ALTER TABLE dbo.EmailNewsletter ADD DateStarted DATETIME;

Now our code for gathering emails to send looked like this code. We update a set number of rows and then we select those back and start sending emails. The SendDate isn't really necessary, but it's a nice easy way to validate that we are sending the right newsletter for the date.

SET ROWCOUNT 500;
UPDATE dbo.EmailNewsletter
SET DateStarted = GETDATE()
WHERE SendDate = '2026-02-09'
      AND sent = 0
      AND DateStarted IS NULL;
SELECT ID,
       SendDate,
       UserEmail,
       sent
FROM dbo.EmailNewsletter
WHERE SendDate = '2026-02-09'
      AND sent = 0
      AND DateStarted IS NOT NULL;

If SenderApp failed, we could look for EmailNewsletter.Sent = 0 and DateStarted set to a date and determine what was  in process. We could easily reset EmailNewsletter.DateStarted to NULL for those rows and the app would pick them up.

Scaling This Up

We knew our growth had a peak, but we weren't sure where this was. Sending 100,000 emails a night was already a challenge and we were struggling to get this sent in 10 hours overnight with Lyris. This system seemed to work well, but how would we scale it?

The architecture that Andy and I had designed was loosely coupled. There was nothing specific about the agent that limited us to just one SenderApp. We had a few quick tests that showed this worked well in practice, all the queueing and database updates worked as expected and ran quickly, so we could easily run multiple instances of SenderApp.

However.

We think about failure as well. How would we know which emails had been abandoned by an instance of our application? After all, both might select 500 rows separately, which is what we want, but if one failed, we might accidentally mark or change rows the other instance was sending. Imagine SenderApp1 gets rows 1-500 and SenderApp2 gets rows 501-1000. They start sending and marking emails as sent. Somewhere SenverApp1 dies and rows 400-500 aren't sent. If we mark all rows where DateSent is not null and sent = 0 to be retried, we'll include whatever rows SenderApp2 hasn't yet updated, but is ready to send.

A few of you also might realize that our logic above for selecting rows is flawed. It assumes one person marks and selects the rows together, but we haven't enclosed that in a transaction. Let's fix that. First, let's add a place to mark which app is running:

ALTER TABLE dbo.EmailNewsletter ADD Sender varchar(50);

Next, our logic changes. When we update rows, we add the name of the SenderApp instance. Initially we thought about the machine name, but since we wanted to scale this and perhaps run multiple instances on one machine, we decided to use a config file and name each instance running. This was simple in that we put copies of SenderApp in a few folders, changed the config in each to have a different name (usually machine name and a number) and then started each on system start.

Our select logic was:

SET ROWCOUNT 500;
UPDATE dbo.EmailNewsletter
SET DateStarted = GETDATE(),
    Sender = 'SSC4_Sender2'
WHERE SendDate = '2026-02-09'
      AND sent = 0;
SELECT ID,
       SendDate,
       UserEmail,
       sent
FROM dbo.EmailNewsletter
WHERE SendDate = '2026-02-09'
      AND sent = 0
      AND DateStarted IS NOT NULL
      AND Sender = 'SSC4_Sender2';

Likewise our updates of the Sent column got changed as well to include not only the email, but also verify that the SenderApp name matched.

Adding Priority

Once we had this running, it worked great the first night. I think we bought two small machines to host our senders and ran two instances of the SenderApp on each. This gave us some redundancy and scale. We were sending in 3-4 hours instead of 10+.

However.

We soon realized that new people couldn't sign up for the site and newsletter at night. As a part of our registration process, we sent an email that asked them to verify their registration. We also sent notifications when people posted in the forums. These two emails were migrated to use the EmailNewsletter table as well, trying to take advantage of our structure. For those items we:

• composed an email and dropped it in Newsletter
• added a single line (for registration), or one for each thread subscriber (for forum notifications) to EmailNewsletter

The SenderApp would process these the same as anything else, as the dates, content, etc. matched, but the problem was that these emails got queued amongst the hundreds of thousands of other emails. Even at scale, waiting a few hours for a registration confirmation is really annoying.

As we revisited our architecture, we wanted to keep things simple, as well as easy to handle. We decided on a couple of things.

1. We added a field to the EmailNewsletter table that was a priority field.
2. We enhanced SenderApp to make a query for priority 1 emails first and process all of those. Then it would query for priority 2 emails and process those. Then it queried for priority 3 and processed 500 at a time.

Our structure was priority 1 for registration (immediate) and priority 2 for forum notifications to get them out quickly, but not delay registrations. We typically had less than a 100 of these in a minute or two, so processing all of these was fine. We then handled 500 newsletter sends at a time. Since each loop of SenderApp was less than a minute, this worked really well.

These were simple code changes as well. We added this field to the table.

ALTER TABLE dbo.EmailNewsletter ADD priority TINYINT

As we populated the table, each process (registration, forum post, newsletter load) added a priority to it's insert query. Each query by SenderApp likewise added an AND clause checking for priority. We defaulted this to 3, so if anyone forgot, we had a low priority as a default. However, if someone really needed to add an email, they had to specify 1 or 2 to get it out quickly.

With 4 instances, we could send 500 emails in a minute. This mean that we could do 2000 a minute with 4 instance and no reason we couldn't scale that higher. The load on the database server was pretty low. At 2000 a minute, we can send 120,000 an hour and over 1,000,000 in less than 9 hours. A good thing since we scaled up over a million emails a night.

Summary

This post covers the basic architecture of using tables in a SQL Server database as a queue system for high speed processing. Of course, what's high speed to one person isn't high speed to others. Certainly Facebook or other large scale sites would have overwhelmed this system as some of them need to send millions of emails in an hour. However, by keeping this simple, using good indexing and narrow table design, we built a system that was very inexpensive to build and run, and supported the site.

At our peak, we were sending about 9 million emails a week across 6 days. Every week, overnight, without many issues.

Our small SenderApp dot net application was simple and ran beautifully. We rarely had failures, and when we did, a simple database query reset the rows to be picked up by any other instance of the app.

We abandoned this system when Redgate rewrote and migrated the site to their own infrastructure. Email migraded to AWS SES, which was reliable and reduced the need to manage a lot of additional overhead that has come about when sending emails these days.

Still, this was a great system and worked well for us. I'm proud of what we built and glad we didn't spend that $200,000+ on Lyris.