Migrating Microsoft SQL Server 2008 to 2012
Now available for Cloud Sites - Microsoft SQL Server 2012!
Read on to learn the process of migrating your current Cloud Sites running Microsoft SQL Server 2008™ (MSSQL 2008) to begin running on a Microsoft SQL Server 2012™ database (MSSQL 2012).
- If followed correctly, site impact during the migration process should be minimal. With that said, it is highly suggested to perform this migration at a non-peak time to minimize business impact.
- You can set your default page to reflect that your site is currently under maintenance to limit the traffic that would be hitting the database.
- If you created any full text catalogs, you will have to drop them before making a backup of your 2008 database. While MSSQL 2012 does support full text catalogs, our current tools do not allow for them to be restored if they are in the backup file. Once you have restored to the new MSSQL 2012 location please add your full text catalogs. These links will assist you in completing this task:
- You can set the database to
READ_ONLYmode to ensure there are no updates done to the database while the migration is performed with the following query:
Note: Only set the database to
READ_ONLY after completing your backup, otherwise you won't be able to log into the new database
ALTER DATABASE [NumXYZ_OldDbName] SET READ_ONLY
- When the new database is restored set it to Read-Write:
ALTER DATABASE [NumXYZ_NewDbName] SET READ_WRITE
Create Your MSSQL 2012 Database:
- Create a new MSSQL 2012 database in the Control Panel under the Features tab of the domain your MSSQL 2008 database is on.
- After the database has been created, please view its properties (as shown below) and note the change in the hostname. You will need to change any connection strings you have to the database, and this information will be required. The information for your database will vary from the image depending on what data center your account is hosted in.
Backup Your MSSQL 2008 Database:
- Next use the web based admin tool MyLittleBackup to back up your MSSQL 2008 database. The link for the online tool can be found in your control panel by clicking on the database under the “Features” tab. For this backup please add "mlb" to the end of the URL. It is important to add this to the URL and not to go through the standard MyLittleAdmin link found in your control panel. Using the links instead of adding the “mlb” may cause session issues between the two MyLittleAdmin versions. For example: https://mssql.dfw1-2.websitesettings.com/mlb
- Login to your MSSQL 2008 source database. Now back up your MSSQL 2008 database using the MyLittleBackup tool. When the backup has completed click on the file link to save the backup file to your local machine.
- Log in to the MyLittleBackup link again using your MSSQL 2012 database and login. Remember the login you use to restore your database will become the new owner of the database. Choose Restore databases, and upload the backup file you just downloaded in the previous step. Click Restore to proceed.
- The restore tool may notify you that the old users have no login mapping on the new SQL 2012 cluster. At this point your new database will be ready. At this point, access is only allowed to the owner login, the one that you used to restore the database. If you need to change the owner to another login you created in the Control Panel, or remap users in your database to new logins, please read the article on How to remap database users in myLittleAdmin.
- Once the migration is complete you will need to update all connection strings to point to the new database. After you have verified that everything is working from the MSSQL 2012 database please delete your MSSQL 2008 database. This will ensure that you are not billed for the additional database in the future, and confirm with us the migration has been completed.
© 2014 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License
See license specifics and DISCLAIMER