November 11, 2016 at 11:51 am
ryanbesko (11/11/2016)
From a programmer's point of view, I couldn't live without these features, which were not available in SQL Server 2005:Common Table Expressions: https://msdn.microsoft.com/en-us/library/ms175972.aspx?f=255&MSPPError=-2147217396
MERGE: https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396
ROW_NUMBER(): https://msdn.microsoft.com/en-us/library/ms186734.aspx
Only MERGE wasn't available in 2005.
November 11, 2016 at 12:42 pm
There are many different features introduced in later versions of the SQL language. A couple of very notable ones for me where the LEAD and LAG windowing functions introduced in SQL 2012. The full list is pretty extensive.
In comparing the OSes, we're putting our new SQL instances on Windows 2012 R2. The WSFC is so very much nicer than Windows Server 2003 or 2008. The TLS protocol support is another big deal. Overall, I think it's a better OS, despite the metro interface. 😛
On the topic of hardware, I understand that it can be a pretty hefty cash layout to get new hardware. I've been in jobs in the past where we've kept servers limping along way, way past the point where they should have been retired. I don't know what hardware you have, but it gets more and more expensive to replace components when they fail...and they do fail. Eventually, someone decides to bite the bullet and usually wonders later why the didn't do it sooner. Honestly, the cost of hardware is probably going to pale compared to the cost of SQL licensing.
If I could offer a single piece of advice on buying hardware, don't be afraid to invest in it. Get some decent CPUs. Whatever SE version you're going to get, make sure you get more memory than it will use to leave plenty for the OS. Also, don't forget to include storage for your backups that's not in the same server as your SQL Server.
I've seen some very thoughtful words of wisdom from Glen Berry on selecting SQL Server hardware. There's a book (free PDF) available at http://www.red-gate.com/library/sql-server-hardware.
November 11, 2016 at 12:54 pm
homebrew01 (11/10/2016)
Eirikur Eiriksson (11/10/2016)
Think this is the wrong question, should be "what are the justifications for running an unsupported version of SQL Server"
😎
Doesn't cost any money ?? $10,000 to upgrade is a lot for us.
If you're wanting to sell business owners on the idea of a $$,$$$ upgrade, then highlight the security and performance enhancements. You'll come across as a dork if you try to explain stuff about cool new T-SQL functions, despite how much merit they have in terms of programming value. :unsure:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 11, 2016 at 2:39 pm
We are migrating to VM servers at a hosting facility, away from old hardware in our own rack.
November 11, 2016 at 2:40 pm
One thought. Is there any SQL 2005 syntax or other features that will fail on SQL 2016 ??
For instance, we have some tables with datatype TEXT, which I think is still supported in 2016, but perhaps other features are no longer supported ?
November 11, 2016 at 2:55 pm
homebrew01 (11/11/2016)
One thought. Is there any SQL 2005 syntax or other features that will fail on SQL 2016 ??For instance, we have some tables with datatype TEXT, which I think is still supported in 2016, but perhaps other features are no longer supported ?
Search web - there is an upgrade advisor, although I don't know if it will run against 2005 or not.
IIRC there is also a "breaking change" document for 2016 available.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2016 at 3:24 pm
The Breaking Change is located at https://msdn.microsoft.com/en-us/library/ms143179.aspx. It also contains links to the same page for other versions, so you can go through them for SQL 2008, 2012 and 2014 as well.
November 12, 2016 at 3:33 pm
Luis Cazares (11/10/2016)
Eirikur Eiriksson (11/10/2016)
homebrew01 (11/10/2016)
Eirikur Eiriksson (11/10/2016)
Think this is the wrong question, should be "what are the justifications for running an unsupported version of SQL Server"
😎
Doesn't cost any money ?? $10,000 to upgrade is a lot for us.
MySQL, MariaDB, PostgreSql?
😎
The costs for those are on training and getting people up to speed.
And migration and regression testing and lost learning and fixing stuff because nothing is truly portable and...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2016 at 10:52 am
If you needed more benefits, there are many Enterprise features that have been made available for 2016 SP1 Standard edition.
https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/
November 17, 2016 at 12:07 pm
Looks like I got budget approval for SQL 2016 !!
Can I restore SQL 2005 Master, model, msdb databases to 2016 ?
https://msdn.microsoft.com/en-us/library/ms190679.aspx
Or are there other considerations since 2005 is so old ?
November 17, 2016 at 12:13 pm
As of last night the killer reason to move to 2016 is to get SP1 which makes the previously Enterprise-only programability features now available in all editions. Eg In-memory OLTP, columnstore, always encrypted, dynamic data masking, compression, partitioning and more.
Plus nice things like CREATE OR ALTER for views, procedures, functions, triggers and a whole pile more features added.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2016 at 12:14 pm
homebrew01 (11/17/2016)
Can I restore SQL 2005 Master, model, msdb databases to 2016 ?
You do not want to do that (master won't, the others shouldn't). Script things, apply on new instance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2016 at 12:35 pm
homebrew01 (11/17/2016)
Looks like I got budget approval for SQL 2016 !!Can I restore SQL 2005 Master, model, msdb databases to 2016 ?
https://msdn.microsoft.com/en-us/library/ms190679.aspx
Or are there other considerations since 2005 is so old ?
That link details restoring the master database which you may need to do in the event of a disaster. It does not promote restoring the database to a new instance, especially an instance of a higher version.
The master database will not go through the usual internal database upgrade procedure that user databases go through.
Script the objects you require and apply them to the new instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2016 at 12:43 pm
While the original approach of excluding "enterprise" features from Standard Edition was to encourage folks to splurge on the Enterprise Edition license, as we see now, including these features in Standard Edition also presents a strong and compelling case for folks to upgrade from prior Standard versions to Standard Edition 2016 SP1.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 17, 2016 at 12:48 pm
Time to do some studying. Haven't done any migrations in a while.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply