How to monitor replication in SQL Server and use the Database Mail feature to send alert messages

At the end of last month, because of the relocation of the room, a data database needs to be moved to our room, and then the customer's database

From the original room a moved to the computer room B, the original Data Information Library (DATAINFO) is placed in the room a, but in order to facilitate and prevent information leakage

We'll put it in our trusteeship room, it's called engine room C.

In the relocation of the computer room, as far as possible to reduce downtime, data can not be lost, the relocation room is really a learning ...

Although I am so busy, I still make time to write the article, to share the dry goods to everyone o (∩_∩) o

Because many systems are reading and writing room A data Information library (datainfo), I at the end of last month with the backup file initialization method to build a copy of the room A

Computer Room A Data Information library (DATAINFO) The newly inserted data is copied to the computer room C in real time, so that some system can read the data information base of the room C (datainfo),

After the relocation of all systems, and then unified all change the connection address

Of course, this article is not about my relocation process, after the establishment of replication, because I did not set the subscription library login user's permission for read-only, resulting in a few days before the development side

At the same time inserting new data into the subscription library, causing replication to fail (primary key duplication), distribution command backlog (probably 26w+), and then a lot of follow-up work ...

There are a lot of duplicate pits, because we can't stare at the Replication Monitor for 24 hours, so we need some kind of monitoring tool,

When you encounter a replication error, you can quickly know and fix it.

Monitoring conditions to be considered:

(1) Single point monitoring, multiple point monitoring

(2) Purchase, own development

(3) Compare real-time, not very real time

(4) Whether the database server is too overloaded

I only consider the simplest one: a single point, do not need to be very real-time, load is not high, if the server load is too high may not even send a message

Then consider using SQL Server's own Database Mail to send alert messages

Of course, if you need to simultaneously meet real-time, multiple point monitoring, cost enough to consider the purchase of mature solutions

For example: Microsoft's system Center R2

Or

own company to develop monitoring procedures, support SMS alarm more timely