Moving On-Premise SQL Server Database to SQL Azure

I have found a very helpful article explaining step by step process for how to move SQL Server database from on-premise/local environment to SQL Azure portal, for complete details refer below link:

For this process, you will require a SQL Azure migration wizard client tool which is free, you can download it from http://sqlazuremw.codeplex.com/

Step by step explanation of how to move SQL Server database from an on-premise environment to SQL Azure:

http://timheuer.com/blog/archive/2012/02/20/migrating-existing-sql-server-to-sql-azure.aspx

Configure SQL Azure to Access from Local SSMS

I have found 2 different ways for how to access and work in SQL Azure database:

1)  On Windows Azure Portal:

=> Open portal and go to SQL Databases section => Select database => click on Manage URL in quick glance section, right side of the panel.

This will redirect to Management Portal-SQL Azure, login into the database by inputting required fields then start work into the database.

As it directly accessible from a browser, so the approach helpful while SSMS is not installed on the machine or accessing SQL Azure database.

But as a developer, considering the SSMS tool always installed on the machine and most of the developers are much comfortable for accessing SQL server objects through SSMS than a browser.

So to make it work, first we have to configure SQL Azure in SSMS, for it follows below steps:

2) Configure SQL Azure to open in local SSMS, which gives a feeling like working SQL Server of on-premises:

2.1) Configurations required on Windows Azure Portal:

  • Allow public IP address into SQL Azure environment:

Move into SQL databases section on portal => select Servers in the main panel.

  • Click on -> right side in Server Name cell
  • Move into CONFIGURE section and add new Rule with start IP address 0.0.0.0 and End IP 255.255.255.255 and save it by click on SAVE button in the bottom of the screen.

You can set rule by adding current client IP address, but it changes frequently so each and every time you have to add the current public IP address into firewall rules on Windows Azure.

2.2) Local machine level configurations:
Open SQL server configuration manager from Start Menu.

  • Navigate to Protocols for MSSQLServer node => enable TCP/IP protocol.
  • Open TCP/IP properties by right click it and then properties or by double click, and mentioned Port no: 1433 in IPAll section. Apply & close window.
  • Open Windows firewall => Advanced settings
  • Navigate at Inbound Rules => Create new Rule
  • Select Rule type => Port
  • Move into Protocols and Ports section and mentioned Port no: 1433 for TCP.
  • Do not make any changes in the Action and Profile section.
  • Set Name for the rule in the last section and finish.

2.2) Connect to SQL Azure in SSMS:

  • Provide below information into Connect to Server (this is sample information, you have to mention as per your configurations)

Server Name: tcp:<ServerName>.database.windows.net,1433 (it should be fully qualified server name, pick from the windows azure portal)

Login: <UserId>@<Servername>

Password: <Password> which you set for a database in the portal

For example:

Server Name: tcp:qm1qbebehe.database.windows.net,1433

Login: [email protected]

Password: ********

Windows Azure Benefits – Business prospective

Reason to move from a traditional hosting environment/on-premise to a cloud-based platform like Windows Azure.

Main 3 reasons which can help to understand the benefits of Windows Azure over On-Premise infrastructure as the business perspective.

a)    Cost – Pay for exactly what you need.

=> The cost of cloud computing varies based on implementation, workload, and usage.

There are no absolutes in hosting costs, but in general, the rich flexibility in resource sizing that Azure allows for (in terms of both size and number of computing resources) helps to ensure that you pay for exactly what you need, rather than paying for resources you don’t need in a co-located or traditional hosting situation.

=> There are hidden savings to hosting in the cloud as well.

For example, in comparison to hosting on-premise,

· Avoid manpower costs associated with staff to manage and maintain your infrastructure.
· Even if you’re already using a traditional hosting arrangement you’ll likely save work (and costs) since you avoid the remote maintenance tasks that are typically associated with keeping your systems up, patched and running).
· Supply-side economies of scale, such as geolocating data centers where power is plentiful and cheap, allow companies like Microsoft to offer these services at extremely low rates.
· Additionally, demand-side aggregation, which mixes in your utilization side by side with other customers, and multi-tenancy efficiencies, which help to reduce overall management costs, all help to push cloud computing costs down.

b)   Focus
=> Issue of focus:

·   What business are they in, and what sort of tasks do they want to work on to drive their business forward?
·   Typically, these folks aren’t in the hosting or IT business. A cloud hosting solution allows you to minimize the resources you need to expend in maintaining your technical infrastructures and take back that time to work on business objectives that drive their business forward.
·   A cloud story like Azure lets someone else procure hardware and operating systems and make sure that they are properly maintained and patched.
·   Also note that resourcing flexibility adds up to business agility, a key to keeping your business moving forward.

=> It’s simple, really.

·   Speed (of innovation, of time to market, of competitive response) is a key differentiator between businesses that thrive and those that don’t.
·   Cloud computing is a key ingredient of future “fast” companies.
·   Finally, wouldn’t you rather focus on what you like to do, versus have to do?

For example.

=>  For a developer, it is about the code, not the security patch;
=> For DBAs can focus on tuning a data solution, not standing up a database server;
=>  IT Pros work on getting a business enabling strategy implemented, and not about another OS update.

In all of these cases, “cloud” lets you focus on the career you signed up to undertake.

c)    Capabilities

=>  Finally, there are multiple capabilities that a cloud service like Azure can provide that are unavailable with on-premise or traditional hosting.

Maybe you have lots of data to store and distribute – Azure blob storage combined with CDN capabilities bring you to fault tolerance, and distributed performance, not to mention geo-replication of your data at two data centers hundreds of miles apart to give you peace of mind at no extra cost.

=>  Another cloud capability that’s impossible to duplicate in a non-virtual environment is dynamic capacity.
Cloud services like Azure allow you to grab extra capacity when you need it.

For example, in the holiday season your business is high then you can manage for more resources, and once the business goes regularly you can release resources so you don’t have to pay for it when you don’t.

=>  On the other hand, with a self-hosted or traditional hosting arrangement, you need to pay for the maximum capacity you’ll use at any point in time in order to ensure that it’s available when you need it.

For example, if you need 10 physical servers worth of capacity from Thanksgiving until the new year, but only 2 physical servers the rest of the year, you’ll need to pay for 10 physical servers and waste 80% of their capacity for most of the year.

With a dynamically scalable cloud solution like Azure, you can turn on and off capacity in very short order, with zero lead time.