How to Upgrade to SQL Server 2017

One of the tasks that any DBA now can be asked to upgrade the production environment to the latest SQL Server version . and this task is more critical you can find below some tips and resources that can be helpful on this tasks

1. How to decide on an upgrade

• SQL Server 2000 and SQL Server 2005 are no longer supported by Microsoft and no updates are published. This lack of support means your databases may be vulnerable to security attacks that have been addressed by recent upgrades. Upgrade these database servers unless you can afford to lose them or allow the data they contain to fall into malicious hands.
• Because these databases are no longer supported, it is highly likely that you are not in compliance with the data protection laws in your region.
• Your company already uses Azure for other systems and may wish to migrate databases into Azure as well. You cannot migrate a database into Azure SQL Database from SQL Server 2000 or 2005.
• You can upgrade a SQL Server 2005 database server to SQL Server 2017 directly. However, to upgrade from SQL Server 2000, you must first upgrade to SQL Server 2008.

1. Discover your DB and Document anything (versions, features, editions , DB Size , DB Count , Configuration component, ..etc ) you can use (Microsoft Assessment and Planning (MAP) toolkit) in this part
2. Data Migration Assistant (DMA) : supports versions of SQL Server from 2005 upwards. Using the assistant, you connect to the instance and select the database you plan to upgrade. You tell the assistant the target version of SQL Server and it will analyze the size and schema of the database. It will also make recommendations for your upgrade. After the analysis, DMA provides a compatibility report, which highlights breaking changes, behavioral changes, and deprecated features so these problems can be fixed.
3. Database Experimentation Assistant (DEA) : To test your migration and upgrade process to test the DB performance and workload if we upgraded it You can also test performance on different hardware or with new features added

Introduction to upgrading SQL Server

• https://docs.microsoft.com/en-us/learn/modules/introduction-upgrading-sql-server/1-introduction

Microsoft Assessment and Planning Toolkit MAP :

• https://www.microsoft.com/en-us/download/details.aspx?id=7826
• https://www.sqlshack.com/how-to-use-microsoft-assessment-and-planning-map-toolkit-for-sql-server/

Microsoft® Data Migration Assistant DMA :

• https://www.microsoft.com/en-us/download/details.aspx?id=53595
• https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-2017
• https://www.mssqltips.com/sqlservertip/5454/data-migration-assistant-dma-to-check-for-sql-server-compatibility-issues-when-migrating-to-azure/

Microsoft® Database Experimentation Assistant DEA :

• https://www.microsoft.com/en-us/download/details.aspx?id=54090
• https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-get-started?view=sql-server-2017
• https://www.sqlshack.com/download-install-sql-server-database-experimentation-assistant-dea/
• https://www.sqlshack.com/how-to-use-the-sql-server-database-experimentation-assistant-dea-tool/