Email stops working on SQL Server 2016 – Part 2

SQL Server 2016 Email Issues

Recently had issues with Windows Updates causing a problem with a SQL Server 2016 installation in that it stopped SQL Server email from working. The solution to fix it is fairly easy….

https://intersect.co.uk/sql-server/email-stops-working-on-sql-server-2016/

However, if email is critical to your environment it may be worth checking with the Windows Server team when they will be deploying Windows updates so that you can check it doesn’t effect your systems. Particularly as we have seen this problem happening more than once on the same environment.

Any emails that SQL Server tries to send while this problem persists will not be lost, as they are just entered into the MSDB database and processed by the mail system. These will all be sent once email is back up and running.

To check for unsent emails…

SELECT *
FROM  msdb.dbo.sysmail_allitems
WHERE sent_status='unsent'

If after a minute or so no email has been sent, try the following command

EXECUTE msdb.dbo.sysmail_start_sp

Email stops working on SQL Server 2016

Issue

Email is configured and working on a SQL Server 2016 server and then suddenly stops working

 

Possible Cause

We have seen this twice in the last couple of weeks and it has been due to Windows Updates applying Cumulative Updates for SQL Server and these updates have broken email. (CU1)

 

Solution

Use a text editor to create a file called DatabaseMail.exe.config (ensure that there is no .TXT extension)

In the file add the following…

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>

Then save the file to the BINN directory (to make sure you are in the correct folder, it should contain the file DatabaseMail.exe)

SQL Server Email should now work. No need to reboot or stop or start SQL Server.

For further information….

https://support.microsoft.com/en-us/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe

Important notes to above.

Make sure that the quotes around the version numbers are surrounded by proper double quotes ”

When the file is saved in notepad make sure that it is saved in UTF format. This is not normally default.

In the text file, make sure that there is no space before the opening <?xml line. For other lines this doesn’t matter.


SQL Server Backups – Simple Guide Part 1

Introduction

This is a quick guide to the more popular SQL Server backup routines. It isn’t really intended for experienced professional DBA’s, but in many cases the DBA role of installing SQL Server and setting up backup routines is not actually carried out by DBA’s. But rather the responsibilities for this are managed by Server / Infrastructure Engineers or Developers.

Now we aren’t implying that people in these roles are not capable of backing up a database, but beyond the full backup schedule the waters can become a bit muddy in relation to when to use differential backups and / or transaction log backups.

Below is a summary of Full, Differential and Transaction Log backups. Part 2 of the blog will go through how to check what backups have taken place, backups carried out by third party products etc.

 

Full Backups

As the name implies a full backup backs up all of the database to a file, no great surprises there. If a conventional SQL Server backup routine is used as opposed to using third party backup software such as CommVault or Veam, then the database will be backed up to:-

  • Local disk on the database server
  • Network location such as a shared Windows folder. (The user running the SQL Server service will need write access to the shared folder)
  • tape device (although not sure how often databases are backed up directly to tape nowadays, it’s usually first to disk)
  • Azure backup location

The standard SQL Server backup file is a single file, normally with a .BAK extension. This file can then be restored over the top of the existing database or restored on another server that is running the same or later version of SQL Server.

 

Differential Backups

A differential backup or sometimes called incremental backup, works in the same way as the full backup, but it simply backs up all of the changes that have been made to the database since the last full backup took place.

For example, if a full backup is taken at 01:00 and then a differential backup at 09:00, the differential backup would contain the database changes that have been made in the last 8 hours. Likewise if a further differential backup is taken at 17:00, the backup file would contain the database changes that have occurred in the last 16 hours.

There is no relationship or dependency with the two differential backup files. So if you needed to restore the differential backup that was taken at 17:00 it would just be a case of restoring the full backup that was taken at 01:00 (with norecovery) and then restoring the 17:00 differential.

All differential backups have a relationship and dependency on the last full backup that was taken.

 

Transaction Log Backups

Transaction log backups can only be done when the database is in Full recovery mode. The other two modes are Bulk Logged which is rarely seen and Simple recovery which broadly speaking is usually used for test and development environments or other systems which would never require a point in time recovery.

The transaction log backups contain the changes that have been made to the system. But unlike differential backups the transaction log backups contain the changes that have been made since the previous transaction log backup.

Going back to the previous example of a full database backup taking place at 01:00. For the sake of illustration there is then a transaction log backup occurring at 02:00 and then again on an hourly basis until 17:00. Each backup would contain the changes to the system that have taken place in the previous hour.

If you wanted to restore the database to 17:00 you would restore the full backup (with norecovery), then restore each of the transaction logs in turn. All would be restored with norecovery except the last one which would be restored with recovery (the default setting).

Unlike differential backups, transaction log backups are dependent on each other as they form a chain. So if any of the transaction log backups were lost then the recovery would not be able to go any further.

Transaction logs also allow you to restore to a particular point in time (POITR). So for example if someone accidentally dropped or deleted from a table at 16:34, then the database could be restored on the same or different server to 16:33.

 

Backup Compression

In SQL 2008R2 Standard edition and later, all conventional SQL Server backups can be compressed. The compression ratio can vary depending on what type of data is being held in the database. Tables holding BLOB Images such as files or images will not compress as well as tables that hold text and numeric data. If you are lucky you could see the size of the backup file reduce to the region of 10 – 15% of the original backup size.

Compression can be selected as an option when the backup is done, or the default compression (is it On or Off) can be set at the SQL Server level and this setting will be used when no compression options are specified.

 

Command Line Backups

Although it is possible to carry out all of these commands by using the GUI within SSMS, once you are used to command line backups they are more convenient.

Simple full backup

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak'

 

Full backup using compression

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression

 

Backup to a network share (SQL Server Service Account needs write access to share)

BACKUP DATABASE Prod TO DISK = 'FILESERVER1BACKUPSProd.bak'

 

Full backup which also overwrites any backups with the same name

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression, format

 

Full backup which also overwrites any backups with the same name and shows progress in 1% increments

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression, format, stats=1

 

Differential Backup

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH differential

 

Transaction Log Backup

BACKUP LOG Prod TO DISK = 'C:BackupsProd.trn'

 

Transaction Log Backup with Date and Time appended to filename (i.e. Prod_2017-10-16 13_49_35.trn)

DECLARE @date VARCHAR(128)
SELECT @date = CONVERT(CHAR(128), GETDATE(), 120)
SELECT @date = 'C:BackupsProd_'+ REPLACE(@date, ':', '_') + '.trn'
BACKUP LOG Prod TO DISK = @date

Welcome to our new team member Bobby Balmer

Intersect Consulting would like to welcome on-board the newest member of our team Bobby Balmer.  Bobby has joined the company as a Junior DBA and after only one week with us, Bobby is already adding value to the team and the Company.  Bobby is a bright guy with great ideas and loads of initiative. We are all looking forward to working with him and wish him every success with Intersect Consulting.

Connect with Bobby on LinkedIn

 


ABW Milestone 7 UPDATE 01

Many organisations that use UNIT4 Business World will wait until the first update has been released before they consider upgrading.  Well UPDATE 1 for ABW Milestone 7, or to give it it’s formal name – UNIT4 Business World On! Spring 2017 Update 1 is now available for download.

https://abwupdates.agresso.com/

We have carried out several test Milestone 7 upgrades over the last few months, with two customers that upgraded from Milestone 3 and Milestone 4 going Live on Milestone 7 prior to the update 1 release.