Migrate from SQL 2008 to SQL 2017 On-Premises

Read time 6 minutes

Microsoft ended the extended support for SQL Server 2008/2008 R2 on July 9, 2019. If you are still sticking to SQL Server 2008, then you need to upgrade it as early as possible. Now you will not get any security patch or support from Microsoft. However, you can go for online support forums, knowledge-based articles, troubleshooting, FAQs, and other support tools. Meanwhile, many SQL server users have upgraded to recent SQL versions and many are planning to do so.

There are multiple methods to migrate from an older version to the desired SQL Server version, but they differ as per your requirement.

To migrate from SQL Server 2008 to SQL Server 2017 on-premises, the Data Migration Assistant tool is perfectly suitable, and we will follow the steps required to complete the migration.

What is the Data Migration Assistant Tool?

Microsoft Data Migration Assistant is a free utility that allows you to upgrade to the latest version of SQL Server, and it also checks for any compatibility issue that can hamper the functionality of the data at the new SQL Server. It further recommends improvements at the target Server. The tool not only moves the database schema and data but the uncontained items from the source server also.

• Migrates on-premises SQL Server to a new on-premises SQL Server or Azure SQL.
• Migrates different components like schema of databases, data and users, server roles, and SQL/Windows logins.
• After completion of the migration, applications can connect with the databases in the target SQL Server.
• Recognition of various issues that can affect the performance of an on-premises SQL Server.
• The tool finds new features that can help the database to work smoothly in various ways like performance, security, and storage.

You can download the utility from –
https://www.microsoft.com/en-us/download/details.aspx?id=53595

Click Download, and it will download to your system. Then install the utility, and you will get its home screen.

1. Click New (+) to add a new migration project.
2. Select Project Type as Migration. Provide a meaningful project name. Select SQL Server as the Source server type, and SQL Server as the target server type. Click Create.
3. The ‘Specify source & target’ tab, input the name of both source and destination servers. Choose the Authentication type suitable for the server, and the enhanced security check the option for Encrypt Connection. Then Click Next.
4. When the tool makes a successful connection with both servers, it goes to the next tab, which is Add databases. Here, all the source databases are already selected by default. You can uncheck them easily and leave only the required ones.
   • As soon as you select a database, you will see the migration options present for this database.
   • Next, provide a network shared location that is accessible by both source and destination servers for the backup operation. Please note that the service account of the source server should have write permission on the network shared folder, and the service account of the destination server should have the read permission for the same folder.
   • Provide the location for the data and transaction log files at the destination SQL Server.

   

5. If you are unable to provide a network-shared folder, then you can check the option ‘Copy the database backups to a different location that the target server can read and restore them.’ Then input a path for the backups for the restore operation. Then click Next.
   
   The Data Migration Assistant tool will validate the location for the backup folder, data, and transaction logs. Finally, click Next.
6. Select the login details for migration. Click Start Migration.
7. After successful completion, get the complete result in the ‘View Results’ tab.

You can choose the ‘Export report’ option to save the migration report.

Conclusion

When you have to migrate SQL Server to a new version, you cannot take any chance regarding its safety. Although the Data Migration Assistant tool is quite capable of managing the migration process, many more features will be quite helpful in migration, like a filter option or a preview of the table. That’s why you can go for a professional tool called Kernel for SQL Database Recovery software that can not only recover the corrupt MDF files but also save them to any version of on-premises SQL Server. You can use it as migration software for your healthy MDF files from SQL Server 2008 and save them directly to SQL Server 2017. Moreover, it supports SQL Server migrations involving any SQL Server version.