Veeam – Upgrade to SQL Express 2016 SP1 for improved performance
Upgrade SQL Express for improved performance with Veeam
UPDATE 22/10/2018
I had a comment on here stating that in-memory OLTP is not used by Veeam which prompted me to research this issue a little more. After a brief chat with @Gostev on the matter, this is indeed the case. The Word from Gostev weekly e-mail from November 2016 states the following:
By the way, some of you may have a question why 9.5 still ships with SQL Server 2012 Express, when back 1.5 years ago I explicitly recommended hosting Veeam configuration database on SQL Server 2014 to all users with large environments (for its new features like cardinality estimation). Well, the answer is pretty simple – in all these years, we could not find SQL Server 2014 package that we are actually able to install reliably in an unattended manner! And SQL Server 2016 is just too young to jump, especially when we’re reporting its bugs to Microsoft at the same time. So, the only change in 9.5 is that we’re now shipping with the latest and greatest SQL Server 2012 Express *SP3* – and we are hoping to switch to SQL Server 2016 Express in future releases
So, cardinality estimation is where it is at. You can check out more information about this here.
Original Article
One of the take away for me from VeeamON was that upgrading the version of SQL backing Veeam to either 2014 or 2016 will yield great improvements in performance. Now even the latest version of Veeam 9.5 only ships with SQL 2012 Express. Issues cited by Veeam with deploying newer versions of SQL express at install time of backup and replication are the reason why it does not ship with it. So let’s take a look at some of the enhancements and reasons why you should upgrade. These features are available in Express edition.
In memory OLTP (Online Transaction Processing)
I’m not going to lie, I am no SQL expert. However, moving anything from disk to in memory is going to improve its speed. OLTP is moved to in memory starting with SQL server 2014. You can read more about this feature here
Advanced Security – Always encrypted data
Encrypted data, gotta be a good thing right? Now from what I can gather this won’t be enabled out the box but essentially it allows for a demarcation of data access between application owners and database owners. Read more here
You can find a full breakdown of features here
The upgrade process
Run up services.msc and take a look at what version of SQL you are currently running
Search the interwebs for SQL Express 2016 download and grab a copy of it. It downloads as an update launcher which will download the required binaries as you step through the installation wizard.
Choose Custom install
Choose media location to download the product binaries
Make a cup of covfefe whilst you wait for the download to complete
NOTE – Before starting the upgrade, Disable all Veeam jobs from the Veeam console and stop all Veeam services. Make sure you have a backup of the database. You have been warned!
Launch the upgrade wizard
Accept the EULA
Check for updates during install
Choose the instance of SQL you would like to upgrade, this is the instance we took at look at the beginning of this process. Note that version 11 = SQL 2012
Grab more covfefe
And done, reboot the server
Take a look at the SQL server version following the reboot. V13 is SQL 2016. Remember it was V11 prior to the upgrade.
Remember to enable the Veeam services at this point.
And that’s it, easy as pie, piece of cake, jobs a goodun.
Ian
Download here: https://www.microsoft.com/en-au/sql-server/sql-server-editions-express
Hi, thanks for this cool post. But: How do you enable OLTP? In my opinion veeam don’t uses this feature.
Hi Chris, good question and as I confessed in the post originally, I am no SQL expert :). I did however find this https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios?view=sql-server-2017. It explains in a little more detail when in-memory OLTP is advantageous and there is a script at the bottom of the post to enable in-memory OLTP on existing user databases.
FYI, Veeam 9.5 U4 should ship with SQL Express 2016, not 2012.
If you know how to verify that OLTP is in use or not, it would be great to hear back from you!
Thanks,
Ian
Thank you for the confirmation that its a good idea to upgrade from 2012 to 2014 or higher.