-1

I have database created on SQL SERVER 2008. I want to upgrade it to SQL SERVER 2016. what is the best way to upgrade the whole database?

Thanks in advance.

gbn
  • 422,506
  • 82
  • 585
  • 676
Jai
  • 416
  • 6
  • 20

2 Answers2

1

Migration can be done many ways from 2008 to 2016. In-house upgrade, backup restore, etc. Different approaches are explained here:

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-database-engine

But simple backup/restore approach is the always better in my practical understanding. Also It has links for SSIS/SSAS/SSRS if required plan for those. Each has different approaches...

Major things to consider are how to remove deprecated features and how to make use of new features:

Deprecated features: 2012: https://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx

2014: https://msdn.microsoft.com/en-us/library/ms144262(v=sql.120)

2016: https://learn.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server-2016

You can use new AMR tool to identify what tables you can use for in-memory also you can do analysis of columnstore indexes etc using below:

https://blogs.technet.microsoft.com/dataplatforminsider/2013/09/17/new-amr-tool-simplifying-the-migration-to-in-memory-oltp/

Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0

I had big problems upgrading from 2008 to 20014 as it complained about 2008 SP2 missing, but there where additional messages about missing instance ID's or nothing needed to be installed.

The upgrade from 2008 to 2012 was a better move as the 2012 is probaly better designed to deal with a previous release.

Once 2012 was installed I moved to 2014 with no problem and did not have to care about attached databases. It just upgraded.

I had to use the "SQL Server Configuration Manager" to enable Named Pipes or TCP/IP connection in the "SQL Server Network Configuration" section, and enable the new SQL service .exe in the windows firewall.

To add it to the firewall the service exe "sqlsrvr.exe" was identified using the Windows task manager and using "properties" optaning the path to the .exe.

The installation makes you nervous but once installed you know SQL Server is one of the few Microsft products that does not bother you. It just works.

Instead of downloading missing servicepacks manually it was easier to run Windows update and have it suggest SQL server sevicepacks. Winows update actually helped before migrating to the next version.

Here is a nice chart of sql server editions with links to downloads: Microsoft SQL Server Version List

flodis
  • 1,123
  • 13
  • 9