0

I have in hands a task where I'm required to copy a 300GB SQL Server 2008 R2 Standard Edition to Amazon's AWS RDS.

The issue is that a couple weeks ago, AWS finally released a feature that allows the restore of a .bak file generated by T-SQL's EXPORT command (or SSMS's one). This is a good start, but the procedure of exporting the DB, copying the .bak file to S3 and then restoring it to RDS takes about 6h.

During that time, our application servers must be down so that the databases are in sync and; as we are talking about our website's database, 6h is something very difficult to cope with.

We have already tried AWS's DMS service and RedGate's Data Compare to no avail...

Anyone has an idea how this can be done or do we really have to accept the 6h downtime?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fabio Silva
  • 1
  • 1
  • 2
    Can you elaborate why the AWS DMS service doesn't work in this situation? You could let it perform the initial 6 hour sync while your app servers were still running. Afterwards it will continue to keep things in sync. At some point you would shut down your app servers just long enough for DMS to sync the latest changes, and then restart your app servers with them pointing to your RDS server. It would be very little downtime. – Mark B Aug 14 '16 at 13:49
  • Thank you so much for your comment. The issue here is that our source DB is SQL Server 2008 R2 Standard Edition and doesn't support Replication / CDC (Change data capture) (check: http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html). I have tried that and I got the error that DMS could not activate the replication/CDC in the source DB. – Fabio Silva Aug 14 '16 at 18:49
  • Surely MS sql server must have *some* way of making an incremental dump that can update your AWS copy after the 6h are up? – Vorsprung Aug 15 '16 at 12:13
  • I would imagine so, but today I had a call with an AWS SQL server engineer that told me the only suggestion he could give me besides their DMS service was to try a service from a partner of theirs: https://aws.amazon.com/marketplace/pp/B00OU0PE5M/ref=_ptnr_blg_%E2%80%99blg2%E2%80%99 – Fabio Silva Aug 16 '16 at 23:19

1 Answers1

0

The features of SQL Server standard edition on AWS and rest environment is detailed in TaskLance blog Issues with MS SQL Server Standard edition in AWS: http://tasklance.com/index.php/2016/03/16/issues-with-ms-sql-server-standard-edition-in-aws-2/

Tasklance
  • 1
  • 1
    Could you please cite the essence of your linked information? Link only answers are not sufficient on Stackoverflow. – Peter G. Aug 27 '16 at 12:42