Even though it has only been two years since the last major update of Microsoft's relational database, the SQL...
Server 2016 version offers a lot of compelling reasons to upgrade.
Microsoft enhanced SQL Server security with the new Always Encrypted, row-level security and dynamic data asking features. Added support for temporal tables improved auditing and data protection, and Stretch Database strengthened hybrid cloud integration. Microsoft also continued to boost performance and availability with updates to SQL Server features such as In-Memory OLTP (online transaction processing), columnstore indexes and Always On Availability Groups.
So, if you've decided to make the jump to SQL Server 2016, what's the best way to ensure a fast and smooth upgrade? There are two basic strategies for upgrading to the new release:
- Performing a side-by-side upgrade, which essentially involves creating a full database backup followed by a clean install of SQL Server on a new operating system
- Performing an in-place upgrade of the production database.
Each method has its advantages and disadvantages. People who prefer the clean upgrade appreciate that it eliminates the registry and system corruption that eventually creeps into all Windows installations. The end result is a more stable new installation of SQL Server. With a clean upgrade, the database also can remain available on the original system while you perform the install on a new one.
However, this method is time-consuming and tends to be more costly, because you need to back up and restore all of your databases. Plus, in many instances, you'll also be implementing a new version of Windows Server, something that requires a new operating system license. In addition, you have to restore any customized SQL Server settings and properties that have changed and then reapply all of those settings.
Alternatively, performing an in-place upgrade is simpler. You still need to perform backups before the upgrade process, but you don't need to restore your databases. You only have to install the new SQL Server 2016 version in place of the old one. All of your server and database settings will be preserved. Another downside is that there will be downtime on your databases during the upgrade process.
Upgrade paths to SQL Server 2016
The SQL Server 2016 installation process supports upgrading from earlier versions starting with these releases: SQL Server 2008 SP3, SQL Server 2008 R2 SP2 and SQL Server 2012 SP1, plus all versions of SQL Server 2014. You can't perform an in-place upgrade for versions of SQL Server older than SQL Server 2008. In particular, there are limited options for upgrading SQL Server 2005, which earlier this year reached its end of life with the expiration of Microsoft's extended support. Among them are attaching a SQL Server 2005 database to a SQL Server 2016 instance and restoring a database to an instance of the new version from a backup copy.
In general, you can upgrade from your current edition of SQL Server to an equivalent or higher edition. For instance, you can upgrade from the SQL Server 2014 Standard edition to the Standard or Enterprise editions of SQL Server 2016. But you can't upgrade from the Enterprise edition of the former to the Standard edition of the latter. If you want to change from using Enterprise edition to the Standard one, it requires a completely new installation.
In addition, you can't add new features to a database during the upgrade process. That has to be done after an instance is upgraded, using the SQL Server 2016 installation wizard and setup dialog box.
SQL Server 2016 system requirements
SQL Server 2016 has modest hardware requirements and can run on anything from a laptop to enterprise-class server systems. The minimum processing requirement is a 1.4 GHz CPU, though Microsoft's recommends a processor speed of at least 2.0 GHz. It's worth noting that Microsoft doesn't support 32-bit processors in the SQL Server 2016 release -- the database is available only on 64-bit platforms.
The minimum memory requirement is 512 MB for the SQL Server 2016 Express edition, while Microsoft recommends a minimum of 4 GB of RAM for the other editions. Disk space requirements vary depending on the features installed, but a minimum of 6 GB of free disk space is needed.
At the operating system level, SQL Server 2016 Enterprise edition requires Windows Server 2012 or higher, while the Standard and Express editions can also be installed on the Windows 8 desktop OS and above. SQL Server 2016 also has a few software requirements beyond the base operating system, including the .NET Framework 4.6, which the setup mechanism automatically installs. Also, if you install the PolyBase technology added to SQL Server 2016 to enable querying of data in Hadoop clusters, you'll need the Oracle Java SE Runtime Environment version 7.51 or higher.
One piece of good news to keep in mind as you think about a possible upgrade: Despite all the new features in SQL Server 2016, the licensing model hasn't changed from the model used in SQL Server 2014. This means you can get the SQL Server 2016 version for essentially the same price as the previous one costs.
SQL Server 2016 improves In-Memory OLTP feature
SQL Server 2016 updates old features and adds new ones
TempDB configuration is simpler in SQL Server 2016