Journey of an On-Premises DB to Azure VM – SQL Server 2014 makes it easier!


SQL Server 2014 is deeply integrated with Windows Microsoft Azure and moving an On-prem database to a Microsoft Azure VM is much easier and seamless now.

This post talks about the journey of a database named “MoveMeToCloud” which is hosted on SQLServer 2014 RTM on-prem instance to a SQL Instance which is hosted on an Azure VM.

Here are the steps (Can’t get easier than this)  -

1. Right click database “MoveMeToCloud” and choose tasks and then Deploy Database to Windows Azure VM (Oops, that named needs to be changed too!)

Move1

2.  As soon as we complete the first step, we will get the intro screen. Pretty straight forward information made available here.

Move2

Once we go pass the intro screen we will get the option to connect to the local SQL Instance.  We can then choose the db which we want to move, and also a location for the backup files.

Move3

3. Next up will be security related settings. We will need a management certificate to proceed here.

Move4

By using the Sign in option we can get the required certificate. This is a smart wizard and it will pull the certificate details for you.

Move5

We will proceed by clicking next.

4. Next up is the deployment settings part. This part is little tricky as we will have to do some authentication.

Move6

As you can see in the above screen the Cloud service name, Virtual Machine name and the Storage acct is already available as we have done a sign-in in the earlier step.

We will need to access the settings button to do an authentication again. There should be a VM end point for the specific port for this to work.

Move7

Once the authentication is done, we will get the target database details which will be automatically populated.

Move8

5. Final step is to check the summary and initiate this by saying finish. The wizard will do rest of the job for you.

Move9

Move10

 

If we check the SQL Instance on Azure we can see the database is available there, Viola !

Move11

Conclusion

This is a very smart wizard and I’m really happy that Microsoft have made great efforts to make this simple to ensure that cloud movements are seamless.

Thanks for reading and keep watching this space for more !

Microsoft + Oracle = New opportunity…Windows Azure is a happening place !


Couple of months back(To be specific in June) Microsoft and Oracle announced their partnership to help enterprise customers embrace the cloud.

It was almost clear that Oracle was going to be available in Windows Azure under the IaSS platform(Virtual Machines’),and today during one of the Oracle Open World keynotes this was officially announced to be available.

I guess Brad Anderson(Corporate Vice President, Cloud and Enterprise Engineering, Microsoft) will be the first Non Oracle person to deliver a keynote during an Oracle Conference(Read Cloud is changing the game).

MSORA2

 

MSORA3

 

As of today our Oracle friends can configure and deploy their databases on Windows Azure. Here is what the Azure VM gallery looks like -

MSORA

 

MSORA1

 

Note  - Some of them are on preview though.

This is really an exciting news. Giants like Microsoft and Oracle teaming up is definitely going to benefit enterprise business, and I’m really positive about that.

Are you excited about this ? Please share your thoughts on the comments section.

Thanks for reading and keep watching this space for more !

Backup your Windows Azure SQL Databases – Yes,you should !


Care about RPO’s and RTO’s?  Then you should be backing up your Windows Azure SQL Databases(Formerly SQLAzure).

clock

Windows Azure SQL Database is highly available and data is redundant(3 copies of your database is stored elsewhere),however that doesn’t help you to recover from the below situation  -

“Hey DBA, I deleted few records from the database accidently !!! Can you please recover the database for me ? “

You definitely need a backup of the database to recover from this situation.

One of the assumptions which I normally hear while talking about Windows Azure SQL Database is that you don’t need to backup your databases and Microsoft takes care of it under the hood ! This is wrong, and you should do it in-case you have a need to tackle situations like what was mentioned above.

You can either do a manual export of your database to the storage account or you can schedule the exports(New Update, Scroll down for details).This exported copy can be used to do the restores(Imports).

The import options are really limited. You cannot do operations like overwriting(Replace) a database etc. I’m really confident that Azure will reach that point pretty soon.

In this post we will see how the manual export process works and will also see how we can import an exported database back.

When doing this manually its always a good idea to get a transactionally consistent backup copies. For this purpose we will need to copy the database to the same server or to a different server. In this post we will do a copy to the same server.

So, we have a database called WASDRocks with a table named ChildTable. The table as 2 records as shown below

Azure backup1

We will now do a database copy to the same server using command

CREATE DATABASE WASDROCKS_Copy AS COPY OF [WASDROCKS]

There you go, we have the new database ready now which is a transactionally consistent copy.

Azure backup2

We will now export the WASDROCKS_Copy and keep it safe under our storage account. Export option is available right below the database selection.

Azure backup3

Storage account needs to be selected along with container details and once the credentials are entered correctly(Real time check of passwords !!!) the .BACPAC will be available.

Azure backup4

Azure backup5

Great, so now we have a transactionally consistent database backup. We can drop this database to avoid additional costs(Yes,its billed)

Now, lets’ do some deletes !!! We will delete a record from the ChildTable

Azure backup6

We can recover this data using the backup which we had taken earlier. All we need to do is an Import

Azure backup7

Note – In a real world situation be very careful about your RPO values. You might have to increase  or decrease the number of exported copies to achieve your SLA. More number of exported copies means, more cost overheads for the storage.

If you try to overwrite the database by giving the same database,ie WASDROCKS is our case,then there will be an error.

Azure backup8

This clearly states the limitations of import which we talked about earlier. You cannot overwrite an existing database.

We will import the backup copy as a different database named WASDROCKS_Recovery.

Azure backup9

There you go, the recovered database is ready for use now.

Azure backup10

If we connect to the recovered database and check for the table, then we can find the details of the deleted row.

Azure backup11

Yes, this is not something which is really flexible to do point -in-time restores,but it works just fine. What really matters is your RPO/RTO numbers and you can plan your exports according to that need.

Is there a way to schedule exports, so that manual intervention is limited ?

Yes,this is what I really love about Windows Azure Team. They are really aggressive and they keep adding features at a great pace.

Automated Database Exports was related last month and please refer this blog post by Scott Guthrie for more details.

Keeping backing up your databases and do random restores to ensure that they are good.

Thanks for reading and keep watching this space for more !

Deleting a Storage Account from Windows Azure – The right way !


I like to clean up things after I’m done with my testing and this morning I decided to clean up my VM’s, Databases which were created under Windows Azure.

Everything went smooth till I tried to delete my storage account. As soon as I tried a delete there was an exception.

Azure Storage Delete1

The ‘Details’ section gave me more details on why this action cant be completed.

Azure Storage Delete2

The error message is quite self explanatory. This was telling me that the storage account which I was trying to delete was having a container which was holding an active image.

This was indeed true. I decided to go ahead and explore the container for the account. I had created multiple files under this container before.Azure Storage Delete3

Then I decided to go ahead and check the blobs under the container, and there was indeed a blob available. As a matter of fact even the container itself is a blob.

Azure Storage Delete4

I decided to delete this blob, and then successfully deleted the storage account from the portal.

To sum up, the right way to delete a storage account from windows azure is to check if you have any active blobs under the container and remove that blob well before you try and remove the storage account.

Conclusion

Hope this short explanation helps you understand how storage is handled within Azure and how things are categorized with respect to Accounts, Container Blobs and Blobs within the containers.

Checkout SQL Server 2014 CTP1 and Windows 2012 R2 preview up in the cloud


You must be really busy spinning up VMs to test SQL Server 2014 CTP1 and Windows Server 2012 R2 preview.

If your on-premise desktop computer/laptop is running low on resources and if you don’t want to take the pain of handling multiple VMs locally,then you have an option to test them out right up in the cloud.

Yes, You can always spin up a Windows Azure VM and do all your testing and shut that down once you are done with your work to save some money.

Sounds like a deal right? Yes, its pretty easy and fast. It takes less than 10 minutes to provision a Windows Azure VM.

Lets look at the options we have in Azure for the test VMs -

1. SQL Server 2014 CTP1 Evaluation Edition on Windows Server 2012

This image will setup SQL2014 CTP1 for you on Windows Server 2012. Once the VM is provisioned and is in running state, you can connect it via RDP to see that SQL Server is ready to test.

VMs

2. Windows Server 2012 R2 Preview

This image will provision a VM for you with Windows Server 2012 R2 preview. You can spin up multiple VMs to do your hardcore testing with Windows.

VMs1

Conclusion

This is indeed a great way to test the latest builds with minimum efforts and you always have the option to shutdown the VMs after use to avoid a heavy bill.

Thanks for reading and keep watching this space for more.

Moving a database from on-premise to the cloud – Step by Step Walkthrough


Cloud is the future and moving there is now as easy as few mouse clicks.

Today we will see how an On-premise database can be moved to a SQL Server instance running up on a Windows Azure Virtual Machine.There are multiple ways to accomplish this and in this post we will see how this can be achieved using SSMS wizards.

Step 1

I have a database named “MoveMeToCloud”,and this is one who is ready to take the flight and move to the cloud. This is a database which is deployed on my on-premises SQL Server Instance.

The first process is to export this database. For that purpose we will choose Tasks –

Export Data-tier Application

Cloudmove1

As always an introduction wizard is opened up, and you can proceed by choosing next.(Also can say do not show this again !)

Cloudmove2

Step 2

In this step we will connect Windows Azure Storage Account and we will export the BACPAC directly to the cloud storage.

Cloudmove3

When we connect the Azure storage account we will need to provide the account name and the key. Manage Access Keys under the Azure portal(Storage section) will get this info for us.

Cloudmove33

Cloudmove4

Step 3

Once the storage account connection is established we can proceed and finish this export activity.

Cloudmove5

The wizard will do multiple checks, and the process will complete eventually(Provided all the conditions are met).

I will talk about the conditions in a different post.

Cloudmove6

Step 4

Once the export is completed successfully we can check and verify that the BACPAC is indeed available under Windows Azure Storage Account

Cloudmove7

Step 5

As the BACPAC is now local to the cloud we can now proceed and do an Import of that file.

We will connect to a Windows Azure Virtual Machine which is running SQL Server 2012 up in Azure to do the import.

We will choose Import Data Tier Application to initiate the import.

Cloudmove8

We will need to provide the storage account details under the import settings

Cloudmove9

Once the connection is established correctly we can proceed and choose the database settings

Cloudmove10

Database settings will allow us to place the data file and log file according to our requirements.

We can proceed and finish the wizard to complete the import activity.

Cloudmove11

Cloudmove12

Step 6

Once the import is completed we are good to access the database up in the cloud.

SSMS Cloud

Thanks for reading and keep watching this space for more.

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure


The all new Windows Azure platform was announced yesterday by Scott Gu(CVP,Windows Azure Application Platform) and an online event was hosted here at http://www.meetwindowsazure.com/

You can watch the recorded keynote on demand here at http://www.meetwindowsazure.com/Conversations

There were lot of new features announced yesterday, and the whole event was packed with heavy and exciting demos.

You can refer the blog post written by ScottGu to get a feel of what Windows Azure is going to offer during the coming days.

Another good blog post written by Bob Kelly explains the new services and changes for the existing services can be read here

The new portal looks really cool. It’s all HTML 5 and metro style enabled. Let’s see what Paras Doshi(B/T) has to say about the portal here.

I decided to start playing with the portal to understand more about the offerings, and as always my first attention was towards SQL Database.

Windows Azure SQL Database is a relational database service offered on the Windows Azure platform.Its based on SQL Server 2012.

Here is my experience creating the first ever SQL Database on Azure -

Portal View

The interface is really neat with metro style layouts.

SQL Database Creation(Initial Steps)

There is a wizard to guide you to start creating the database, and there are two options Quick Create and Custom Create

I decided to go with Custom Create as that option will give me better control on what I am doing.

The wizard comes again with cool interface asking me to enter the Name, Edition, Max Size, Collation, Server details.

There are 2 edition selection options  WEB and BUSINESS.Web edition will allow max size of the database as 5 GB and Business edition allows max size of the database as 150 GB.

I am using the evaluation subscription, so I decided to choose the web edition with 1GB limit.

I also entered the New SQL Database Server option as this was the first time I am creating a database, and I had setup no server for this subscription.

The next option was to add a Login (Only SQL login option is available), password, confirmation and region where you want the server to be deployed.

Confirmation for SQL Database Availability

In a matter of seconds (3 or 5 !) the database was up and running on cloud.

Conclusion


The new Windows Azure platform is really powerful, flexible and friendly and over the next few days I will write more about my experience working with SQL Databases and much more.

Keep checking this space for more !

Thanks for reading.