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 3. Determine how much disk space your database uses. For this you can attempt a query<\/a>, but you may have an easier time consulting the administration website for your existing database.<\/p>\n 4. Create a new database in RDS within the same datacenter, using the version number found in step 2.<\/p>\n 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<\/a>, which is difficult to restore from.<\/p>\n 6. Create a readonly account<\/a> in the source database to work with (this prevents accidental modifications)<\/p>\n 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.<\/p>\n 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.<\/p>\n 8. Download Liquibase<\/a> and the SQL Server jdbc driver<\/a>.<\/p>\n 9. Create a configuration fil to point to both databases:<\/p>\n 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.<\/p>\n 11. Have Liquibase generate a change log:<\/p>\n This produces an XML file with all the changes required to produce the new database. You will likely need to edit this file.<\/p>\n 12. You can attempt to run the change log, to determine what problems you’ll hit, but running:<\/p>\n 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:<\/p>\n 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.<\/p>\n 15. Before running a data migration, you need to disable foreign keys.<\/p>\n 16. You can then suffer through the SQL Server UI.<\/p>\n Select “SQL Server Authentication”:<\/p>\n 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.<\/p>\n 18. Run the data migration and wait.<\/p>\n 19. Remove the tables that Liquibase creates (unless you need these)<\/p>\n 20. Re-enable all the constraints:<\/p>\n 21. Verify that the application works.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … <\/p>\nSELECT\n sysobjects.*\n , sysindexes.ROWS\nFROM\n sysobjects\n INNER JOIN sysindexes\n ON sysobjects.id = sysindexes.id\nWHERE\n TYPE = 'U'\n AND sysindexes.IndId < 2\nORDER BY sysindexes.ROWS DESC\n<\/pre>\n
classpath=D:\\\\tools\\\\sqljdbc4.jar\ndriver=com.microsoft.sqlserver.jdbc.SQLServerDriver\nurl=jdbc:sqlserver:\/\/x.us-east-1.rds.amazonaws.com:1433;databaseName=db\nusername=\npassword=\nchangeLogFile=D:\\\\tools\\\\liquibase\\\\ChangeLog.xml\n\n#referenceUrl=jdbc:sqlserver:\/\/x.sqlserver.sequelizer.com\nreferenceUsername=\nreferencePassword=\n<\/pre>\n
liquibase diff\n<\/pre>\n
Unexpected Index(s): NONE\nChanged Index(s): NONE\nMissing Primary Key(s):\n PK_ABExperiments_1 on ABExperiments(UniqueId, Experiment)\n PK_AccountCredits on AccountCredits(AccountId)\nUnexpected Primary Key(s): NONE\nChanged Primary Key(s): NONE\nMissing Schema(s): NONE\nUnexpected Schema(s): NONE\nChanged Schema(s): NONE\nMissing Sequence(s): NONE\nUnexpected Sequence(s): NONE\nChanged Sequence(s): NONE\nMissing Stored Procedure(s): NONE\nUnexpected Stored Procedure(s): NONE\nChanged Stored Procedure(s): NONE\nMissing Table(s): NONE\nUnexpected Table(s): NONE\nChanged Table(s): NONE\nMissing Unique Constraint(s): NONE\nUnexpected Unique Constraint(s): NONE\nChanged Unique Constraint(s): NONE\n<\/pre>\n
liquibase diffChangeLog\n<\/pre>\n
liquibase update\n<\/pre>\n
EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"\n<\/pre>\n
![\"1\"](\"http:\/\/www.garysieling.com\/blog\/wp-content\/uploads\/2015\/08\/1-578x480.png\")
<\/p>\n![\"3\"](\"http:\/\/172.104.26.128\/wp-content\/uploads\/2015\/08\/3.png\")
<\/p>\ndrop table dbo.DATABASAECHANGELOG;\ndrop table dbo.DATABASECHANGELOGLOCK;\n<\/pre>\n
EXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"\n<\/pre>\n