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.

Monitoring Memory Usage of Memory Optimized Objects – SQL Server 2014


Starting SQL 2014 monitoring memory usage of the memory optimized objects is super important to ensure that your instance don’t run out of memory and cause real bad situations.

One way of ensuring that the memory optimized objects wont utilize a certain amount of memory is by setting up resource governor. This is a great level of control when you have multiple databases running on the same instance and you don’t want memory optimized tables eating up the whole memory.

Is there an easy way to get a quick overview of memory usage of the memory optimized tables in SQL2014?

Yes, you have a real good SSMS report just for this purpose. This one report utilizes DMVs under the hood and provides you with some valuable information.

InmemOLTP Reports

Lets do a quick walk through of what this report gives -

InmemOLTP Reports1

At any point of time I will be interested to see the usage of Table Used Memory counter.

Index Used Memory is based on the bucket count which you mention during non clustered hash index creation.

The query which the report runs under the hood to give you the usage related value is -

SELECT t.object_id, t.name, 
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_unused_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_unused_memory_in_mb
FROM sys.tables t JOIN sys.dm_db_xtp_table_memory_stats TMS 
ON (t.object_id = TMS.object_id)

This query leverages the power of a new DMV dm_db_xtp_table_memory_stats

Conclusion

SSMS reports are great ways to get a quick overview of what is happening, and I expect more and more powerful reports getting incorporated to SQL 2014 during the coming days.

Thanks for reading and keep watching this space for more.

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.

Installing SQL Server 2014 CTP1 – Step by Step


Great news !!! SQL Server 2014 CTP1 is out and the wait is over. Drop everything(Except the databases !) and download your fresh copy from here.

SQL2014Install16

Here is the official announcement with some high level details.

In this post we will do a quick step by step walk through of the whole install process and see if there are any changes from the prior installs.

* Note  - The below point from the download page should be noted

Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012.

Below is the step by step install process -

[Please click on the images for a better resolution]

Step 1 –  

We will run the setup.exe to get the regular install screen.

SQL2014Install1

Step 2 - 

We will choose stand-alone install and the wizard will do the initial checks

SQL2014Install2

Step 3 - 

We will specify the product key and accept the terms

SQL2014Install3

SQL2014Install4

Step 4 - 

The wizard will now do some real checks and will provide you with a list of details which are either passed or failed.

SQL2014Install5

Step 5 -

We will proceed with Feature install selection once the check phase is completed.

SQL2014Install6

SQL2014Install7

The wizard will do couple more checks as we proceed

SQL2014Install

Step 6 - 

In this step we will do the Instance configuration,Server configuration and the Database Engine configuration.

SQL2014Install8

SQL2014Install9

SQL2014Install10

SQL2014Install11

SQL2014Install12

Step 7  - 

Wizard will do one final round of checks before doing the install

SQL2014Install13

And finally we are ready to do the install

SQL2014Install14

Its always a pleasure to get the below set of messages once the wizard completes its work

SQL2014Install15 Thats it and SQL Server 2014 CTP1 is all yours.
/* Microsoft SQL Server 2014 (CTP1) – 11.0.9120.5 (X64)
Jun 10 2013 20:09:10 
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) */

Conclusion

SQL Server 2014 installation wizard doesn’t have any major changes,and everything looks just same as the earlier version.

Thanks for reading and keep watching this space for some real fun with SQL2014.

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.

Windows Azure pay per minute – Time is money !


One of the most exciting news which got announced yesterday was related to Windows Azure. When Scott Guthrie mentioned that billing model for Windows Azure is going to be per minute basis from now on, then there was a great cheer from the crowd.

Time is money

This is indeed a game changing announcement. I definitely foresee great amount of Windows Azure usage in the future, and this announcement is a great deal for the cloud adopters.

Earlier if I had used my cloud service for 20 minutes,and then turned that off I was charged still for the full hour. Lot many cloud providers still operate with this policy.

Going forward I will be charged for what I useI will be charged for just 20 minutes, no questions asked.

Another super news which was really exciting for me was related to no charge for stopped VMs.

Think about a situation -

Your Dev/Test folks works from 9 AM – 5 PM window. On premise Dev/Test boxes sits idle after 5PM utilizing all the power and other compute resources in your data center. This is sheer waste of money and resources, and in 2013 you should definitely think about moving these to the cloud.

Dev/Test environments can be easily moved to Windows Azure VMs and you can just stop them after 5PM and just don’t have to pay for anything. This is a big deal isn’t? I have no second thoughts about it.

Cloud is the future and these great announcements are making it more affordable to the public.

Thanks for reading and keep watching this space for more.