Migrate from SQL 2008 to SQL 2017 On-Premises

Aftab Alam Aftab Alam Published On - 12 Nov 2019

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 soon as possible. Now you will not get any security patch or support from Microsoft anymore. However, you can go for online self-help support, knowledge-based articles, troubleshooting, FAQs, and other support tools.

There are multiple methods to migrate to the desired SQL Server platform, but they differ as per your requirement.

For the present topic 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.

Benefits of Database Migration Assistant Tool

  • Migrates on-premises SQL Server to a new on-premises SQL Server or Azure Virtual Machine.
  • Migrates different components like schema of databases, data and users, server roles, SQL, and Windows logins.
  • After completion of migration, applications can connect with the databases present at target SQL Server.
  • Recognition of various issues that can affect the performance of an on-premises SQL Server. These issues are breaking changes, behavior changes, and deprecated features.
  • 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
Download Microsoft Data Migration Assistant latest version
Click Download, and it will download to your system. Then install the utility, and you will get a home screen.
Microsoft Data Migration Assistant home screen

Now Start the SQL 2008 to SQL 2017 On-Premises Migration Process

  1. Click New (+) to add a new migration project.Add 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.Select SQL server as a server as a source and target server type
  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.Input the name of both source and destination SQL servers
  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.

    Provide the location for the data and transaction log files

  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.
    Copy the database backups to a different location
    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.Select the login details for migration
  7. After successful completion, get the complete result in the ‘View Results’ tab.View Results

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.

Download Software