Installing the most recent SQL Server version and migrating databases from the source(old) instance to the destination(new) instance constitutes a side-by-side upgrade/migration. Before transferring from the older version, we can use the side-by-side method to evaluate the application connection of the latest SQL Server version. The latest(new) SQL instance can be deployed on a second server that will function as the destination server, or it can be put on the same server as long as it fits the installation requirements. This side-by-side Migration allows us to test two different instances of the system, allowing us to compare and contrast the two. Both the source(old) and destination(new) systems remain operational during migration until the migration to the destination(new) instance is completed. All applications are redirected to the new instance at the conclusion of the migration, and the old instance is manually taken down or decommissioned.
There are three techniques for transferring databases to the newest version instance once we have installed the new instance of Latest SQL Server Version:
1) Copy DB Wizard
2) Database Backup and Restore
3) Detaching and Attaching
Installing the latest SQL Server version in this side-by-side approach is the same as installing it from scratch. However, when performing a side-by-side upgrade, we must consider how to move the databases to the most recent SQL Server instance.
Copy DB Wizard: The Copy DB Wizard allows us to simply relocate or copy databases and their objects from one instance to another without having to shut down the server. We can copy our database to an upgraded instance of Latest SQL Server Version using the Copy DB Wizard. This gives you the option of making a database clone or moving the database entirely. We can also select to copy the database using the detach and attach technique or SQL Management Objects in the wizard (SMO). According to the wizard Before running the wizard, make sure there are no user connections to the database if we utilise the detach and attach approach. According to the wizard If we pick the SMO option, the database(s) will remain online while the migration is in progress, which means the database will remain online while the migration is in progress. There are options for relocating database-related objects, such as jobs and logins, as well. We must point you that when using the Copy DB Wizard, the wizard establishes a login on the destination server with a random password and disables it for security reasons.
We can accomplish the following with the help of this wizard.
1) Choose a source and a destination server.
2) Choose the databases that need to be moved or copied.
3) Indicate where the databases’ files should be saved.
4) On the target server, create the logins.
5) Make copies of supporting objects, jobs, user-defined stored procedures, and error messages.
6) Make a plan for moving or copying the databases.
NOTE: The Copy Database Wizard generates a SQL Server Agent task that runs an SSIS package. Before launching the Copy Database Wizard, check sure SSIS (Integration Services) is installed and the SQL Server Agent service is functioning on the destination server.
Database Backup and Restore: The backup and restore approach is the best way to copy databases to a new instance without affecting the source database’s availability. All that is required is a thorough backup of the source database, its copy to the target location, and its restoration. Internally, SQL Server will upgrade the database while the restore operation is in progress. The database with the most recent SQL Server version, which cannot be downgraded to a previous version.
Because we can’t restore system databases to the most recent version of SQL Server, we’ll have to manually copy logins, jobs, and other data.
Detaching and Attaching: When permanently migrating databases to a new instance of SQL Server, the detach and attach technique may be preferred. The files from the source server will be transferred to the new instance. Because the database is disconnected, it will be inaccessible for any transactions, ensuring that no data is committed on the old system while the upgrade is in progress. Detaching the database allows you to double-check that all of your connections have been routed to the new instance.
The sp detach db stored procedure can be used to detach the database. Before attempting to remove the database, we must also put it to single-user mode and quickly roll back any transactions.
T-SQL Script to Detach a Database:
ALTER DATABASE [XYA] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db ‘XYA’
In the latest SQL Server version, we must use the CREATE DATABASE statement with the FOR ATTACH clause to attach a database. sp attach db is a deprecated stored procedure that will be removed in a future release. We must indicate the locations of the data files that will be attached.
T-SQL Script to Attach a Database:
CREATE DATABASE [XYA] ON
( FILENAME = N’C:\MSSQL\DATA\XYA.mdf’ ),
( FILENAME = N’C:\MSSQL\DATA\XYA_log.ldf’ )
We can also use SQL Server Management Studio’s GUI to disconnect and attach databases.
To detach a database in the GUI, right-click on the database you wish to detach—>select Tasks—>select Detach from the menu. The wizard to Detach Databases will be launched. To detach the database, click OK.
Right-click on the folder Databases and select Attach from the menu to attach a database using the GUI. The wizard for attaching databases is launched. The Locate Database Files screen will appear once we select the Add option, allowing us to go to the data file we want to attach. Select OK to exit the Locate Database Files screen and OK again on the Data Files screen once we’ve selected the data file.
Note : Not only are the methods to migrate database instances listed above, but they are all depending on database size, client requirements, downtime, and other factors.
There are also additional methods for migrating databases, such as configuring High Availability (Logshipping, Mirroring, Replication, AlwaysON), backpac, Import and Export, the SSMA tool, and so on.
As I previously stated, everything is dependent on the environment and the client’s input.
And the most recent one that I discovered when searching for migration on the internet is DBATOOLS, which is an awesome way to migrate databases or entire instances using PowerShell in a very rapid and straightforward manner.