Migration Options
There are 3 options available to migrate a SQL server database
- Backup and Restore
- Detach and Attach the Database
- Copy the Database using SQL Server Management Studio
I am simply going to use the Copy the Database using SQL Server Management Studio option as this is quickest and easiest in my opinion
Instructions
- On the Windows Server 2012 server, open the SQL Management Studio console and connect to the old SQL server instance. From the File menu choose Connect Object Explorer, or click the icon from the Object Explorer window.
- Connect to your Windows Server 2008 R2 SQL Server
- Now that we are connected to the old SQL server, right-click the database and choose Tasks > Copy Database.
- You will get the Welcome to Copy Database wizard
- The wizard will automatically knows the source server, but make sure you check it anyway. If is not the one you want, type it in the Source server box
- Select whether to use Windows or SQL Authentication
- On the destination server, the server name most likely will be wrong, so we need to type the correct one. Click Next when you’re done
- Select whether to use Windows or SQL Authentication
- Next you are on the Select a Transfer Method
- Once source & destination server details given, you need to select the way by which you are going to copy move the database.
- Detach Attach Faster methods, requires db to be offline. Users will be disconnected and physical files of the db will be copied to the destination server
- SMO Slower method, db will be in online state. This will create the db in the destination server with the same name and copy all the datas from source. I used this method
- Next Select the Database you want to move or copy. I kept mine as Copy as when it has finished copying I can simply take the original database offline
- On the Configure Destination Database Page, you need to provide the new db name and the path where CDW should place the physical files in the destination serve
- If there are any related objects to this database, select them, then press the arrow to move them to the right, to the Selected related objects section.
- On the Configure a Package
- In the next page you need to provide the package name and the log file for this process, so that you can review any failures.
- Check the details in the final wizard and click Finish
- All actions should have success next to them
- Refresh the console and you should see the database up and running on the new server.
- There is one more step that applies to all three migration methods. The database needs to be put in a 2012 mode, or the latest version of your SQL server in case you are not using SQL 2012. This is to take advantage of all the features that the latest SQL edition provides. After the database has been moved, right-click it and choose Properties.
- Click the Options page and on the Compatibility level box choose the latest edition of SQL server. In mine case is 2012. You have to be careful with this, because if you ever wanted to migrate the database to an older SQL version is not going to work. There are going to be incompatibility problems, so again…caution.
- On the vCenter server, open the ODBC Connection and adjust the connection to point to the new SQL server
- If the connection doesn’t work, check the logins on the new server as these can come across as disabled. Right click on the user account, select Properties > Status > Under Login, select Enabled
- Next go to the original database and select to take offline. if you have problems taking the database offline then follow the link below to kill existing connections to the database
How to fix a SQL Server Database stuck going offline
Considerations
- You cannot move system databases
- Selecting move option will delete the source db once it moves the db to destination server
- If you use SMO method to move full text catalogs then you need to repopulate it
- SQL Server Agent should be running or else it will fail in job creation step
- You cant move encrypted objects (like objects, certificates etc) using CDW
