SQL Server Management studio provides a tool to migrate data between two different databases. In my testing I found that it does not copy constraints, but it is fairly fast to transfer data, as it copies tables in parallel. To resolve that, I’ve found that using Liquibase to build the new database first, and SQL Server Management Studio to transfer data works fairly well.
Preparation steps:
1. If using an AWS reseller, determine what datacenter they put the database in
2. Determine which version of SQL Server the original database is
select @@VERSION
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)
3. Determine how much disk space your database uses. For this you can attempt a query, but you may have an easier time consulting the administration website for your existing database.
4. Create a new database in RDS within the same datacenter, using the version number found in step 2.
5. Take a database backup of the original. Unfortunately SQL server is terrible in shared environments, so your best bet may be SQL Backup and FTP, which is difficult to restore from.
6. Create a readonly account in the source database to work with (this prevents accidental modifications)
7. Check which tables are the largest – if you have tables containing audit history or logs, you may be surprised at their size. If you want to make the migration faster, you’ll need to delete old entries.
If you use autoincrement fields, you will also need this information, because you will need to set the starting values in the new database on creation.
SELECT
sysobjects.*
, sysindexes.ROWS
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
TYPE = 'U'
AND sysindexes.IndId < 2
ORDER BY sysindexes.ROWS DESC
8. Download Liquibase and the SQL Server jdbc driver.
9. Create a configuration fil to point to both databases:
classpath=D:\\tools\\sqljdbc4.jar driver=com.microsoft.sqlserver.jdbc.SQLServerDriver url=jdbc:sqlserver://x.us-east-1.rds.amazonaws.com:1433;databaseName=db username= password= changeLogFile=D:\\tools\\liquibase\\ChangeLog.xml #referenceUrl=jdbc:sqlserver://x.sqlserver.sequelizer.com referenceUsername= referencePassword=
10. Have liquibase diff the “old” and “new” databases. This compares the two schemas (one being empty) and tells you how much work it’s going to do when you actually run it.
liquibase diff
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s):
PK_ABExperiments_1 on ABExperiments(UniqueId, Experiment)
PK_AccountCredits on AccountCredits(AccountId)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
11. Have Liquibase generate a change log:
liquibase diffChangeLog
This produces an XML file with all the changes required to produce the new database. You will likely need to edit this file.
12. You can attempt to run the change log, to determine what problems you’ll hit, but running:
liquibase update
13. If you use autoincrement fields, you will need to set the starting values. I picked a value that was larger than any of mine, because it’s easier to find-replace in this file:
14. You will likely need to re-create the database several times to get this right. There are a few types it struggles with (e.g. blob types) so you may want to watch for these and change them in the source system.
15. Before running a data migration, you need to disable foreign keys.
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
16. You can then suffer through the SQL Server UI.
Select “SQL Server Authentication”:
17. For each table you have to edit the migration settings and check “Set insert on” for each table. This setting is designed for migrations, because you can only set it on one table at a time – it allows you to update the values of “identity” columns.
18. Run the data migration and wait.
19. Remove the tables that Liquibase creates (unless you need these)
drop table dbo.DATABASAECHANGELOG;
drop table dbo.DATABASECHANGELOGLOCK;
20. Re-enable all the constraints:
EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
21. Verify that the application works.