My company decided to downgrade the SQL server instances from SQL server 2014 enterprise Edition to SQL Server 2016 Standard Edition in order to reduce costs.
as a DBA, I have several reservations and concerns:In this version I enjoy with some features that not supported on Standard Edition, for Example:
Snapshots DB's - Enterprise Edition
Online Rebuild Index - a feature the supported only by Enterprise Edition
AlwaysOn Availability(current, i have 3 Nodes in the HA), the Secondary Node can not use as read only
partitioning - a feature the supported only by Enterprise Edition
Please, tell me some other things I did not think about.
in General, i think that it's very risky to downgrade from SQL server 2014 Enterprise Edition to SQL server 2016 Standard Edition.(does it Upgrade Or downgrade????)
please advise if it's possible or Not and if so, what other concerns i need to take in advance(in other words, why not to do it....)
NOTE: currently, we have 12 cores on Each Machine (very Expensive)
It is possible. I'd do this in two steps. First I'd upgrade to SQL Server 2016 SP1. They enabled more features in Standard in that release. Unfortunately not all the ones you have mentioned Second, I'd downgrade from Enterprise to Standard. That's possible and very easy. There's an option in the installer to make the change. It is very quick. If anything breaks horribly you can always upgrade again -- though I haven't tried that.
As far as the justification for Enterprise, that's a little harder. Here are two articles that summarize the changes
AGs I'm not sure -- either they are two node or not included.
Partitioning - included
Also, please note that if you're using snapshots against a read-only replica to query it, then you need to license it.
I would read the 2016 licensing guide. A three node HA configuration means that two nodes need to be licensed. For each licensed "operating environment" you get one HADR node of equal or lesser cores. That third node becomes the challenge. I'm not sure about SQL Server 2014.
I've been doing some testing on a rather large contrived table (3.65 million rows, populated 1 row at a time for a simulated hour for 1,000 rows, for 10 hours each simulated day, for 365 days with an average row size of 123 bytes) using the worst clustered index key possible and I'm here to tell you that using REORGANIZE is only a bit better than doing no defragmentation at all. The good part about doing no defragmentation is that it builds what I call a "Natural Fill Factor" to actually decrease the number of page splits over time.
To make a much longer story shorter, I've not rebuilt any indexes since 17 Jan 2016 (almost 2 years) on our production boxes. Performance actually got better during the 3 months and has not degraded since. There was one table that grew to a quarter Terabyte and was half empty. It turned out to be the only table they did deletes from. I rebuilt it without growing the MDF file by creating a temporary file group and rebuilding the index to that and then back to the MDF. Since I temporarily move the DB to the Bulk Logged Recovery Model, it was all minimally logged, as well.
There are some other tables that have a bit more space in them (slightly less than 70% page full) but they only amount to 40GB. I say "only" because that's only 4% of a Terabyte database. Everything else is running hot, straight, and normal as if I had carefully planned Fill Factors for every index and rebuilt them just as they were starting to do page splits.
Speaking of page splits, the testing I'm doing on the contrived table shows a nice gentle slope of mostly normal page splits that you would see when an "end page" got full except most of them are mid table because of the GUID. In contrast, if you wait to do a rebuild at 30% fragmentation and rebuild to 70% Fill Factor, you end up with a couple of days of a huge number of page splits. If you have a non-ever-increasing index key and you make the mistake of ever rebuilding that index with a Fill Factor of 100, you get massive amounts of page splits immediately after you rebuild the index. And, like I said, a REORGANIZE isn't much more effective at controlling page splits than doing no index maintenance at all.
I have a bit of a personal hatred for REORGANIZE. It frequently bloats the log file and if the "Natural Fill Factor" that the table experiences over time becomes larger than the declared FILL Factor for the index, REORGANIZE will NOT make the extra space on the pages that are needed to keep it from being not much better than not rebuilding indexes as all. Its ONLY saving grace (IMHO) is that you can do "something" with the index in an online fashion even in the Standard Edition even if it turns out that a REORGANIZE is worse than doing nothing at all.
You can migrate SQL Server database from one Server to another by various methods like Copy Database Wizard , Restoring a database backup, or by using Generate Script Wizard. Here are the step by step process to migrate database from one Server to another: https://www.cybrary.it/0p3n/best-way-copy-sql-server-database-one-server-another/
Second, I'd downgrade from Enterprise to Standard. That's possible and very easy. There's an option in the installer to make the change. It is very quick. If anything breaks horribly you can always upgrade again -- though I haven't tried that.