Moving an on-prem SQL Server database to RDS
• Mark Eschbach
so we’re at a phase where we are moving we are considering moving our SQL Server database servers to production. I have done this move several times for search however this is the firs time with test data. Here is the procedure.
- HA configuration for RDS requires the database to be in the
FULLrecovery model. I believe this has something to do with shipping transaction logs to the second node to keep it up to date. Without this it will complain it completes than complain. To do so is relatively easy! To query the current mode:
SELECT name, recovery_model_desc FROM sys.databases. To change the mode into
FULLyou just simply run
ALTER DATABASE [example] SET RECOVERY FULL.
- Take a backup.
BACKUP DATABASE [example] TO DISK = "C:\example.db_native" WITH FORMAT, MEDIANAME='example', MEDIADESCRIPTION='example'. This process generally doesn’t take too long for a reasonably sized database. However it does take on the order of minutes for medium sized databases, whatever that means for your hardware.
- Transporting to S3 is the next step. In order to do this from the database host we’ll have to install the AWS PowerShell tooling, then use that tooling to push the database to S3.
Interrupt-a-suars strikes again! !