Tag Archives: SQL Server

Migrate Sitecore SQL databases to Amazon RDS

Posted on by

This blog post will guide you to migrate Sitecore SQL Server databases to Amazon RDS.

Unfortunately, you won’t be able to directly use the MDF and LDF files in AWS at the moment so to migrate SQL database to Amazon RDS we are going to use SQL Azure Migration Wizard. Follow below steps for migration –

  1. Before starting migration please make sure
    1. You are able to connect to Amazon RDS. To check it you can utilize SQL Server Management Studio
  2. Down SQL Azure Migration Wizard tool
    1. Extract the downloaded zip file and open SQLAzureMW.exe file.
  3. Start migration
    1. Once you open SQLAzureMW.exe. you will see a page like shown in below image. In this step you have to select process. Since we are migrating Sitecore database so we will select Database under Analyze/Migrate. For Target Server dropdown list select Azure SQL Database and click next
      1Script Wizard<br /><br /><br /><br /><br /><br /> Select Process<br /><br /><br /><br /><br /><br /> Select the process you want the wizard to wak you through.<br /><br /><br /><br /><br /><br /> SQLAzureMW<br /><br /><br /><br /><br /><br /> This wizard gives you the options to anayzes, generate scripts,<br /><br /><br /><br /><br /><br /> and migrate database schemas and data from:<br /><br /><br /><br /><br /><br /> 1) SQL Server to Azure SQL Database.<br /><br /><br /><br /><br /><br /> 2) Azure SQL Database to SQL Server<br /><br /><br /><br /><br /><br /> 3) Azure SQL Database to Azure SQL Database<br /><br /><br /><br /><br /><br /> It will also anayze SQL Profiler trace files and TSQL script for<br /><br /><br /><br /><br /><br /> compatibiltö,' Sues with Azure SQL Database.<br /><br /><br /><br /><br /><br /> NOTE: You must be DB Owner in order to anayze or migrate a<br /><br /><br /><br /><br /><br /> database.<br /><br /><br /><br /><br /><br /> Target Server<br /><br /><br /><br /><br /><br /> Anayze Onty<br /><br /><br /><br /><br /><br /> O SQL Profiler Trace File<br /><br /><br /><br /><br /><br /> O TSQL Folder<br /><br /><br /><br /><br /><br /> Anayze / Migrate<br /><br /><br /><br /><br /><br /> @ Database<br /><br /><br /><br /><br /><br /> O TSQL File<br /><br /><br /><br /><br /><br /> Run TSQL without Anayzng<br /><br /><br /><br /><br /><br /> O TSQL File<br /><br /><br /><br /><br /><br /> Server Maintenance<br /><br /><br /><br /><br /><br /> O Maintenance<br /><br /><br /><br /><br /><br /> Azure SQL Database<br /><br /><br /><br /><br /><br /> < Back<br /><br /><br /><br /><br /><br /> Next >
  4. Choose Source database
    1. This step allows you to choose the source database. First connect to your SQL server database by providing connection details. Once database server is connected successfully select source database and click on the button that says ‘Next.’ Please see below image
      5
  5. Choose action
    1. Once source database is selected then in this step select an option named as ‘Script all database objects’. This option can enable to do the complete migration of the database. But if you don’t want to migrate entire database then you select an option that says ‘Select specific database objects.’ Please see below image
      3
  1. Create SQl script
    1. Tool will create script of selected source database for the objects selected in previous step. You should save the script on local hard drive and the move ahead by hitting a click on a button ‘Next’.                         4
  2. Select Destination Database
    1. In this step provide your Amazon RDS server details and connect it. Once connected select the target database. If you have not created any database earlier, then create a new one using Create Database option and go next
      2
  3. Final Step and Review Target server Response
    1. Once you hit next in above step migration will start and you can review result of migration in this step. You can now connect to RDs database using your SQL Server Management Studio and check all the migrated data.  This tool will save a lot of time.
      6

      You may see below errors in migration report
      StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] — sp_helpuser is not supported in current version of Azure SQL DatabaseTo make sure there is no problem in migrated database because of above error I recommend connect to RDS database and make sure Stored Procedure  aspnet_Setup_RemoveAllRoleMembers is there.