February 8, 2021 at 3:28 am
database in MS SQL Server 2008 R2 (web edition) for website
I have a nice development in the above.
MS SQL Server 2008 R2 was designed for web sites and works well
Question: What do I upgrade the above to (SQL version) to better or same performance for a heavy data intensive website?
February 8, 2021 at 11:07 am
SQL Server 2019 Editions Datasheet.pdf
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 8, 2021 at 1:02 pm
I agree with Johan, 2019 would be the way to go.
Just understand one thing though, going from 2008/R2/2012, you are hopping a bit of a gap at SQL Server 2014. The cardinality estimation engine was updated then. Most queries won't notice. Some queries will improve. A few queries will get a lot worse in performance. You're going to want to monitor carefully before and after you upgrade so you can spot the queries that are suffering. One tool that you have to help with this is the Query Store (introduced in 2016, it's fantastic with upgrades in 2017 and 2019). Monitor on 2008 as normal. Then, when you move your database over to 2019, leave it in the old compatibility mode, but turn on Query Store. Capture your workload into Query Store (day, days, week, weeks, I'm not sure, you'll figure that part out). Then, after an appropriate period of time, change the compatibility mode to the new level. Here, the new CE will start to work. Any queries that suffer unduly, you can use a technique called Plan Forcing to ensure that the old plan is used. It's a great way to ensure performance until you do the hard work of repairing the query (they're almost always problematic queries anyway).
Hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2021 at 1:28 pm
I should indeed have been a bit more thoughtful and provided the nice context as you did, Grant.
A couple of sentences providing the full set of activities that are actually a must when considering to migrate and be ready for the future.
I agree with Johan, 2019 would be the way to go.
Just understand one thing though, going from 2008/R2/2012, you are hopping a bit of a gap at SQL Server 2014. The cardinality estimation engine was updated then. Most queries won't notice. Some queries will improve. A few queries will get a lot worse in performance. You're going to want to monitor carefully before and after you upgrade so you can spot the queries that are suffering. One tool that you have to help with this is the Query Store (introduced in 2016, it's fantastic with upgrades in 2017 and 2019). Monitor on 2008 as normal. Then, when you move your database over to 2019, leave it in the old compatibility mode, but turn on Query Store. Capture your workload into Query Store (day, days, week, weeks, I'm not sure, you'll figure that part out). Then, after an appropriate period of time, change the compatibility mode to the new level. Here, the new CE will start to work. Any queries that suffer unduly, you can use a technique called Plan Forcing to ensure that the old plan is used. It's a great way to ensure performance until you do the hard work of repairing the query (they're almost always problematic queries anyway).
Hope that helps.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 8, 2021 at 5:39 pm
I would always go latest. You're going to pay for that anyway, and with the 10yr lifetime of patches, anything other than 2019 is already digging into it's lifetime. Features are not being removed, and compat modes work pretty well if you need to emulate 2016/2017.
February 8, 2021 at 5:48 pm
Thanks
Has 2019 had a few 'service packs' which make the code bugged out and stable?
Never like to be the first.
When I tried to up date 2008 r2 to 2014/2016 a basic query ran slower. Guess I did know about Query store then.
My data is financial like
SELECT
Symbol,Date,Open,HIgh,Low,Close,Volume FROM <TABLE> WHEN < conditions >
Record count can be 5,000 to 50,000
And I need it to be super fast.
QUESTION: I assume server caching is ok or better in 2019 MS SQL server???
February 8, 2021 at 6:27 pm
Overall, SQL Sever 2019 is a radically improved product over, pretty much all of them. Just using Query Store as an example, it was great in 2016 and I fell in love with it. However, the number of knobs & switches you had to control the behavior was limited. Now, there are a bunch of them introduced with 2019, making a good tool even better. However, there's enhancements across the board. Stuff like a database restore operation is radically faster than any version of SQL Server.
And yeah, you're not the first to use it. They're on CU8 I think.
It's a safe bet from a technical standpoint and from a financial one.
You are going to see differences in behaviors though, and some of them, will be bad. However, every time I've seen this happen, it's been down to the query & underlying structures being problematic already. The upgrades just bring out those problems in some ways. For standard code & structures though, I've only heard about improvements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2021 at 7:16 pm
I'll echo Grant's statements. While 2014 introduced a new cardinality optimizer, there aren't a lot of changes for many queries. They do keep trying to improve the way the optimizer works on queries, and certainly the cardinality estimator doesn't work well for some queries, but you can revert to the old one.
If you move to a new version, I'd always update stats, as that seems to help queries perform better in a new system. Also, keep in mind the first queries load cache, so they are always slower.
February 9, 2021 at 8:46 am
If I were you, I would indeed test it in SQL2019 and see how it performs.
Start with "Data migration Assistant" !
Then migrate - and follow up as Grant pointed out.
Then investigate the newer InMemory capabilities that have evolved over time and versions.
If it's got to be fast, you got to use the best tools available, right?
Now reconsider your hardware investments needed and make sure you have enough of it to support your needs.
Start here : How to Implement In-Memory OLTP Quickly and Easily
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2021 at 8:45 am
I thought it wasn't possible to upgrade SQL more than 2 version - so required multiple intermediate steps when coming from a much older version.
But on checking that I found:
"Backup and restore: A backup taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2019."
has the "two versions" restriction become a thing of the past?
February 11, 2021 at 2:00 pm
I don't recall it being a strict "two versions". Just that, at some point, they stop trying to keep the code up to date for the upgrades, forcing a two-hop process. I think when the releases were once every 5 years, two hops was a lot. Now, with every year or every other year, it's pretty incremental in lots of ways.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2021 at 2:26 pm
Thanks, that makes sense. I did bit of further reading and it looks like an in situ upgrade is restricted more than a RESTORE.
SQL Server 2016 will restore from 2005 backups ... but only upgrade SQL Server 2008 SP4 or later
(COMPATIBILITY MODE will change to lowest supported level, and some reports that Full Text catalogues from 2008 needed doing middle-man via 2008R2)
SQL Server 2019 will restore from SQL Server 2008 (or 2008 R2), but only upgrade from SQL Server 2012 SP4 or later
Looks like SQL 2000 can be restored up to 2008R2
February 11, 2021 at 2:32 pm
Anyone still running SQL Server 2000 deserves every bit of pain they get doing an upgrade on a 21 year old piece of software.
Yeah, I'm a bad person. I'm strangely OK with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2021 at 2:45 pm
Expecting an in-situ upgrade on a 21 year old server is asking a bit much too!
February 11, 2021 at 4:37 pm
I believe the direct in-situ upgrade has been restricted, but bu/restore has not been
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply