Moving a SQL Server 2008 Database from on-premise to SQL Azure
These days I’m doing lot of proof-of-concepts around the SharePoint 2013 App Model and how an ASP.NET MVC 3 application can be consumed/integrated in the SharePoint 2013 with the help of the App Model. As a first-step, I was trying to migrate a SQL 2008 database of an ASP.NET MVC3 application to Windows Azure. When it comes to migrating the database to Azure, there are multiple options available :-
a)Generate Insert SQL Scripts (compatible for SQL Azure) using ‘Tasks —> Generate Scripts’ in SQL Management Studio
b)Directly deploy the on-premise database to SQL Azure ‘Tasks’ —> Deploy Database to SQL Azure
c)Generate .dacpac export of SQL data + Schema and import it in SQL Azure
Since my database schema is not complex and data is also very less, I’m leveraging the 1’st approach.
Right-click on SQL database — > Tasks –> Generate Scripts
Select the list of tables to be migrated
Advanced Settings –>
Select ‘Sql Azure Database’ for Database Engine Type and ‘Schema and data’ for Types of data to script.
Select the location to save generated ‘.sql’file
You’ll see successful creation of .sql files
Log in to Windows Azure Management Portal and create new instance of SQL
Specify SQL Access Account
Select ‘Manage’ option for SQL Server and open a new Query window
Click ‘Run’. Now you are all set.