Is it possible to migrate whatever I have created for my site in my local copy database under SQL Server Compact to SQL Server db? Do I need to redo my site from scratch again? Appreciate if there's any tutorial/guidance on how to perform this.
Yes you can copy your local Orchard CMS site to your Hosting server.
1. Open your Orchard CMS site in Webmatrix, go to the database tab and then click Migrate this will migrate your SQL Server Compact database to SQL Server express or Developer edition whatever you have.
2. Create a .bak file of that migrated database and upload it your hosting database server.
3. Inside your \App_Data\Sites\Default folder you will have to change the database connection string within the settings file.
4. Upload your Orchard CMS site folder to the server using ftp.