SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migration of SQL Server 2008 R2 to AWS cloud


Migration of SQL Server 2008 R2 to AWS cloud

Author
Message
muth_51
muth_51
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9003 Visits: 2987
Hi,

I am new to AWS cloud- can any one tell me what would be the steps to migrate SQL Server instances into AWS.
I already have SQLinstance created on AWS cloud

I am thinking of below steps to move the databases:

1. Moving SQL job, Windows batch jobs
2. Moving SQL logins, permissions
3. Moving Application roles
4.Does backup restore of database works? from SQL server instance to AWS SQL instance? or i need to first create the schema and then export the data in the tables?

Does any one has done this migration earlier. Please share your experience.

Thanks.
YeeHwua
YeeHwua
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 277

1. Moving SQL job, Windows batch jobs
2. Moving SQL logins, permissions
3. Moving Application roles

yes, these are need to be done prior moving.
I was using Visual Studio integration service to transfer login and jobs


4.Does backup restore of database works? from SQL server instance to AWS SQL instance? or i need to first create the schema and then export the data in the tables?

No, backup restore doesn't work.
you need do as you mentioned.
muth_51
muth_51
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9003 Visits: 2987
Thanks.
Any particular ports need to be opened inorder to import data?
As from local SSMS i cannot connect to AWS SQL instance? then how can we import data into tables?
muth_51
muth_51
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9003 Visits: 2987
I have tried Backup and restore from my local instance to AWS SQL instance and it worked.
Is i am missing something here?
YeeHwua
YeeHwua
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 277
That's interesting point. since AWS RDS doesn't have a "file system behind"( of course it has but as end users, we couldn't see it).

I guess you are backing up .bak on your local machine, and open SSMS query window to run restore stmt against the .bak file.

is this what you have tried, could you please post your script that backup and restore to AWS here?

thanks,
YeeHwua
YeeHwua
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 277
Perhaps I misunderstanding what environment you have in AWS, are you using AWS EC2 instance to host your SQL Server?

If this is the case, then backup and restore should work.
muth_51
muth_51
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9003 Visits: 2987
Thanks for the reply. Can i know what is the difference between AWS EC2 and AWS RDS?

RIght now i am using Bastion server to RDP to SQL server.
FOr backup and restore- i have performed sql native full backup on my local machine SQL instance and moved that .bak file to Bastion via REmote desktop local shared resource and from Bastion server i moved the file to AWS SQL instance.

From there i was able to restore the database backup.

but moving the files is taking more time to Bastion server.
Is there any special way to move move the files?
YeeHwua
YeeHwua
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 277
Thanks for the reply. Can i know what is the difference between AWS EC2 and AWS RDS?

AWS RDS is a web service that allow you to create a sql server database on it. It is designed to minimize the administrative work, for instance you don't have to create any Maintenance Plan to execute full backup and log backup. For more details you can see the feature of RDS on its website

RIght now i am using Bastion server to RDP to SQL server.
FOr backup and restore- i have performed sql native full backup on my local machine SQL instance and moved that .bak file to Bastion via REmote desktop local shared resource and from Bastion server i moved the file to AWS SQL instance.

From there i was able to restore the database backup.

but moving the files is taking more time to Bastion server.
Is there any special way to move move the files?

Copy from local to RDP, it really depends on the networking speed and the size of your .bak file, how big is your .bak file, have you think about compressing it then move to AWS server?

I will consider the backup and restore is the best way to move your local DB to AWS sql server.



Kurt W. Zimmerman
Kurt W. Zimmerman
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7612 Visits: 1411
Have you done this? Can you further explain where I can move the database backup then access it via SSMS to restore it? It was my understanding one doesn't have access to the file system in Amazon.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7612 Visits: 1411
Greetings all;
Well this past weekend I was able to successfully move a VLD from a VM environment to the Amazon RDS.
To highlight the tool set, I tried to keep things real simple. I used BCP along with Red-Gate's SQL Data Compare. I stuck with BCP with tables > 1 million rows and SQL Data Compare with the remaining tables. I also wrote custom merge scripts when dealing with tables with RI and were cumbersome to strip them down to load the data.

The effort wasn't 100% flawless requiring some quick thinking during the wee hours of the morning to move things along.

Looking back I would seem to think that Amazon should seriously consider opening up the file system for their SQL Server platform so VLD backups can be moved to the server and restored locally. That would save a lot of aggravation. Even if temporary access was given to initialize the database would make this process much easier.

The DBA is left with finding creative ways to push the data to RDS. This process was met with an initial failure. Our first attempt we ran out of time. I had developed a special sync process that would push the data through a Linked Server. Although this worked, the process was very very slow.

The other critical aspect to this was to make sure I didn't run out of room, which happened to me twice on one of the largest tables within the database.

Things I didn't think about until 2-3:00 am was to change the database recovery model to Bulk Load instead of FULL.

All in all I faced the challenge and accomplished the goal. It took about 2 days for me to catch up on sleep, but it was well worth the experience.

All the best.
Kurt Zimmerman

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search