hMail server and SQL Server database compatibility level

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
flyingsquirrel
Normal user
Normal user
Posts: 30
Joined: 2022-03-03 19:57

hMail server and SQL Server database compatibility level

Post by flyingsquirrel » 2024-05-06 17:14

We have hMail version 5.6.6-B2383 with SQL Server 2016 running without any issues. The current SQL compatibility level of the database is set to 100 (SQL Server 2008 R2). As part of the security upgrade, our database team wanted to upgrade the SQL Compatibility level to 130 (SQL Server 2016) or above. I don't find any documentation on it.
If anyone using SQL Server in the community, may I know what compatibility level are you guys using? Please share your thoughts.

User avatar
mattg
Moderator
Moderator
Posts: 22448
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: hMail server and SQL Server database compatibility level

Post by mattg » 2024-05-08 07:44

We've seen people use SQL Server 2019 I reckon, and perhaps newer
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

flyingsquirrel
Normal user
Normal user
Posts: 30
Joined: 2022-03-03 19:57

Re: hMail server and SQL Server database compatibility level

Post by flyingsquirrel » 2024-05-10 20:23

mattg wrote:
2024-05-08 07:44
We've seen people use SQL Server 2019 I reckon, and perhaps newer

Here is the query that I would use which compatibility level the DB is on.
SELECT compatibility_level FROM sys.databases WHERE name = 'hMailDB';

@Mattg,
Just for my clarification purpose. You meant compatibility level version not the SQL server version correct?

User avatar
mattg
Moderator
Moderator
Posts: 22448
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: hMail server and SQL Server database compatibility level

Post by mattg » 2024-05-11 08:33

Nope I meant SQL server version

Most of use MySQL or MariaDB
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

gotspatel
Senior user
Senior user
Posts: 350
Joined: 2013-10-08 05:42
Location: INDIA

Re: hMail server and SQL Server database compatibility level

Post by gotspatel » 2024-05-12 06:05

Hello @flyingsquirrel

I use MSSQL for other purpose then hmail but process is same. :D

I have tested myself and to change the compatibility level if you use SSMS simply go to database properties --> Options --> Change compatiblity level as desired (1 Recommendation though do not downgrade compatibility level from here, it sometimes (i said Sometimes) created issues while restoring database backup)

once you restore your database of SQL Server 2008 R2 onto SQL Server 2016 even if you keep the compatibility level of the database to 100 you can't just go back to SQL Server 2008 R2 and restore the backup.

for downgrade of compatibility level OR downgrade to lower version MSSQL Server I recommend create a bacpac file if DB is NOT TOO LARGE (lessthen 10 GB) or use Generate Scripts method for database to downgrade/restore, create a new database by setting compatibility level you desire and restore the bacpac file onto it or the scripts.

1. CREATE BACKPAC
2. GENERATE SCRIPTS

For Large size of Scripts or multiple scripts to be restored SSMS will not work use below tool

BIG SQL RUNNER I have tested it to work from Sql 2008 till SQL 2022 any cross reference versions of backup and restore

this is 100% tested by me since i have been doing all these testings since past 2 months, (I am creating a Powershell Script to backup databases for MSSQL or MYSQL from Windows or Linux, (Inspiration and copy paste form @Palinkas Hms Backup Script :mrgreen: ) Will share here with permission from Mods and on Github once complete.


HTH

Regards

palinka
Senior user
Senior user
Posts: 4558
Joined: 2017-09-12 17:57

Re: hMail server and SQL Server database compatibility level

Post by palinka » 2024-05-12 08:16

gotspatel wrote:
2024-05-12 06:05
this is 100% tested by me since i have been doing all these testings since past 2 months, (I am creating a Powershell Script to backup databases for MSSQL or MYSQL from Windows or Linux, (Inspiration and copy paste form @Palinkas Hms Backup Script :mrgreen: ) Will share here with permission from Mods and on Github once complete.
No permission required. :D

User avatar
SorenR
Senior user
Senior user
Posts: 6333
Joined: 2006-08-21 15:38
Location: Denmark

Re: hMail server and SQL Server database compatibility level

Post by SorenR » 2024-05-12 13:14

With this in mind ... WHY would ANYONE ever use MSSQL for a mission critical application? It seems to be built for applications and not for servers!
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

flyingsquirrel
Normal user
Normal user
Posts: 30
Joined: 2022-03-03 19:57

Re: hMail server and SQL Server database compatibility level

Post by flyingsquirrel » 2024-05-13 17:41

gotspatel wrote:
2024-05-12 06:05
Hello @flyingsquirrel

I use MSSQL for other purpose then hmail but process is same. :D

I have tested myself and to change the compatibility level if you use SSMS simply go to database properties --> Options --> Change compatiblity level as desired (1 Recommendation though do not downgrade compatibility level from here, it sometimes (i said Sometimes) created issues while restoring database backup)

once you restore your database of SQL Server 2008 R2 onto SQL Server 2016 even if you keep the compatibility level of the database to 100 you can't just go back to SQL Server 2008 R2 and restore the backup.

for downgrade of compatibility level OR downgrade to lower version MSSQL Server I recommend create a bacpac file if DB is NOT TOO LARGE (lessthen 10 GB) or use Generate Scripts method for database to downgrade/restore, create a new database by setting compatibility level you desire and restore the bacpac file onto it or the scripts.

1. CREATE BACKPAC
2. GENERATE SCRIPTS

For Large size of Scripts or multiple scripts to be restored SSMS will not work use below tool

BIG SQL RUNNER I have tested it to work from Sql 2008 till SQL 2022 any cross reference versions of backup and restore

this is 100% tested by me since i have been doing all these testings since past 2 months, (I am creating a Powershell Script to backup databases for MSSQL or MYSQL from Windows or Linux, (Inspiration and copy paste form @Palinkas Hms Backup Script :mrgreen: ) Will share here with permission from Mods and on Github once complete.


HTH

Regards



Our hMail is very small and we could easily update in SSMS. We have done it for other apps but never did for hMail. Thought I would ask the community about the version if anyone aware. If you perform any tests please share. I appreciate you sharing the knowledge.

gotspatel
Senior user
Senior user
Posts: 350
Joined: 2013-10-08 05:42
Location: INDIA

Re: hMail server and SQL Server database compatibility level

Post by gotspatel » 2024-05-14 11:03

IMHO

1. Upgrade your SQL Server 2008 R2 to 2016, DO NOT CHANGE COMPATIBILITY LEVEL YET for Hmail Database

2. use for a few days if you are comfortable then change the COMPATIBILITY LEVEL of Database, if not you can check the details of downgrade in my earlier post.

I think there is no change for hmailserver be it 2008 R2 or 2016 or 2019 or 2022 for the SQL Server Version it is concerned only for its internal database version 5601, 5602 etc that way it know the database structure since hmail server database doesn't use any stored procedure or view or functions inside database etc (these are the items where the compatibility level matters most not simply storing and retrieving data in/from database)

SorenR wrote:
2024-05-12 13:14
With this in mind ... WHY would ANYONE ever use MSSQL for a mission critical application? It seems to be built for applications and not for servers!
:lol: Some time we don't have a choice and have to use the MSSQL unwillingly (Bartender Barcode Software, Salesforce CRM I use and many more :roll: )

flyingsquirrel
Normal user
Normal user
Posts: 30
Joined: 2022-03-03 19:57

Re: hMail server and SQL Server database compatibility level

Post by flyingsquirrel » 2024-05-14 15:25

All, thanks for the input. I will keep you posted how it goes.

User avatar
SorenR
Senior user
Senior user
Posts: 6333
Joined: 2006-08-21 15:38
Location: Denmark

Re: hMail server and SQL Server database compatibility level

Post by SorenR » 2024-05-14 18:40

gotspatel wrote:
2024-05-14 11:03
IMHO

1. Upgrade your SQL Server 2008 R2 to 2016, DO NOT CHANGE COMPATIBILITY LEVEL YET for Hmail Database

2. use for a few days if you are comfortable then change the COMPATIBILITY LEVEL of Database, if not you can check the details of downgrade in my earlier post.

I think there is no change for hmailserver be it 2008 R2 or 2016 or 2019 or 2022 for the SQL Server Version it is concerned only for its internal database version 5601, 5602 etc that way it know the database structure since hmail server database doesn't use any stored procedure or view or functions inside database etc (these are the items where the compatibility level matters most not simply storing and retrieving data in/from database)

SorenR wrote:
2024-05-12 13:14
With this in mind ... WHY would ANYONE ever use MSSQL for a mission critical application? It seems to be built for applications and not for servers!
:lol: Some time we don't have a choice and have to use the MSSQL unwillingly (Bartender Barcode Software, Salesforce CRM I use and many more :roll: )
You _always_ have a choice! If a service only supports MSSQL it is pretty obvious it is a crap product that is only there for the money and they don't care about their customers!

Granted I did most of my experience with SQL on Oracle "growing up" around late 1990's and early 2000's but then again, nothing else could support the major ISP and Telco landscapes. Then again the machines we used rarely had less than 16 CPU's and a truckload of SCSI drives in all sorts of RAID solutions on top of server/site failover/mirror.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 350
Joined: 2013-10-08 05:42
Location: INDIA

Re: hMail server and SQL Server database compatibility level

Post by gotspatel » 2024-05-14 18:47

SorenR wrote:
2024-05-14 18:40

it is a crap product that is only there for the money and they don't care about their customers!
100% correct :D

DrmCa
Normal user
Normal user
Posts: 178
Joined: 2011-02-14 21:30

Re: hMail server and SQL Server database compatibility level

Post by DrmCa » 2024-05-19 03:17

flyingsquirrel wrote:
2024-05-06 17:14
As part of the security upgrade, our database team wanted to upgrade the SQL Compatibility level to 130 (SQL Server 2016) or above.
The DB compatibility level has absolutely nothing to do with security. It only restricts/expands SQL command/variable/function sets.

Post Reply