Tag Archives: azure

Introduction of SQL Server on Azure VM (IaaS)

SQL Server on top of a VM running in Azure is yet another example of Infrastructure-as-a-Service (IaaS). This is just like running SQL Server on a VM in your own data center, the only difference here is you don’t have to own all that hardware and maintain it, all the hardware is owned, hosted & maintained by Microsoft only. The best part is you still get an option to bring your own license of SQL Server if you have one – means if you already have a SQL Server license you can (migrate your license to Azure) use the same license on cloud. If you use this model you’ll be only charged for (compute + storage cost)

Even if you don’t have a SQL License don’t you worry, you will get an option to select a pre-installed SQL VM image from gallery while creating your VM and you’ll be charged for your (compute + storage cost + SQL License cost) means you pay only what you use – As per your usage J; the billing is per minute basis.

Just like any other service here also you’ll get multiple service tiers, each with different capabilities and SLAs. I’ll discuss more on this in my later blogs.

While you will start using SQL Server on Azure VM, but it will be same On-Premise SQL Server product (Edition & Version of your own choice) with the same features and capabilities. You don’t have to compromise on any feature.

You have multiple ways of migrating data from on-premise to SQL Server running on Azure VM.

The icing of the cake is getting an option to configure AlwaysOn Availability group by using multiple VMs in Virtual Network.

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

What is SQLAzure (Azure SQL Database)

SQLAzure or Azure SQL Database is a database-as-a-service cloud based offering (Microsoft Azure); this offers a fully managed relational Database solution which is highly compatible with current tools to manage SQL Server. This also offers built-in high availability & scalability based on requirements. You don’t have to worry anymore about the infrastructure or the management of the same, just create a Database and start using its features – that means rapid development and at the same time it accelerates your time to market.

Let me walk you through couple of features\offerings which will help you understand it better:

SQLAzure DB is available in Basic, Standard and Premium tiers. Each tier offers its own level of performance and capabilities to support tiniest to highly transactional database workload. The amazing part is you can change service tiers as your database requirements grow that too without any downtime; isn’t it amazing! This makes more sense to those scenarios where you can predict the peak usage and off-peak usage and based on the demand you can turn the knobs and manage cost.

Let’s say you can’t predict the actual usage or the usage pattern is not defined & you don’t want to waste resources\increase cost by over-provisioning then don’t you worry Elastic database pools are there to help you out.

When it comes to performance measure you can very well do that in SQLAzure and the unit of measure is a Database Transaction Unit (DTU) which represents the relative power of database to complete a database transaction.

You get all of these things without compromising anything over security; yes like On-premise SQL Server SQLAzure provides solid database security from limiting access to protecting data at a granular level. This gives you peace of mind & you can put your most confidential data over SQLAzure without any doubt.

How can we forget to mention the SLA (Service level agreement) and uptime; like any other service vendor Microsoft provides SLA which describes its commitments for uptime and connectivity; Azure provides 99.9% availability for its Web/Business tiers and 99.99% availability SLA for Basic, standard and premium tiers; each service tier has its own set of Business continuity features to choose.

To know more about SQLAzure stay tuned!

Feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

SQL Server data files on Azure | Configure Azure security

Before we even start discussing about this feature we must have an idea about Azure Storage accounts and how the security works in Azure.

You can access your Azure storage account linked container because you are the owner, but let’s say you want to share this access (full or Partial) for a blob/container with someone else; to do this you must configure security keys.

In Azure portal security works on two types of Keys and you can use these keys in order to fulfill your security requirements; these are:

a. Storage Access Key (Shared Storage Key)

b. Shared Access Signature Key

Storage Access Key (Shared Storage Key): This is an Admin key which provides full access on your storage account and with this key the person can do any operation on underlying container\blob.

Shared Access Signature Key: This is another key which can be used to provide granular access on the container and helps you enforce restricted permissions (read only, read\write with expiry etc.) this key is also known as SAS Key. SAS key normally looks like a URL which defines the resources you can access along with permissions and the validity of the SAS Key.

How to get these keys:

 

Storage Access Key (Shared Storage Key)

Azure portal provides the Shared Storage Key and you don’t need to use any other tool. All you need to do is login to your Azure Portal, select the Storage Account and select Manage Access Keys from the lower bottom of the portal. Refer the screenshots below:

AzureStorageAccount-sarabpreet

AzureStorageAccessKey-sarabpreet

You can easily copy the access key by using the copy link, marked in the image above.

 

Shared Access Signature Key

As of now unfortunately Azure portal doesn’t have a GUI for this, if you want to generate a SAS key you can do that either by Programming (by calling REST APIs or by using PowerShell cmdlets) or by using Freeware or third party tools. Let me show you a way to generate this key by using an awesome freeware codeplex (open source) project named “Azure Storage Explorer” which is contributed by David.

You can download the tool from here….. It’s a small 3 MB file. The new version is very intuitive and has many new features….Once the tool has been installed you must add your storage account; in order to add your account you have to provide your Storage Access Key. Refer the screenshot below:

AzureStorageExplorer-add_new_account_sarabpreet

 

Once the Storage account has been added you can easily navigate the Storage account and containers, select the container where you want to configure the SAS key and select Security from the upper tab.

StorageExplorer-container

 

It will popup a window with two Tabs: Access Level and Shared Access Signature. You can change the default behavior of your storage container or configure a SAS key by using the tabs respectively. Refer the screenshots below to get more clarity.

AzureStorageExplorer-AccessLevel  AzureStorageExplorer-Create_SAS_Key_sarabpreet

You can read more about the tool on David’s website….here

 

Thanks,
Sarabpreet Singh

SQL Server Data Files in Windows Azure | Part-1

This is a brand new technology which is introduced in SQL Server 2014, having said that believe me this is not something which is new to us – we’ve been using this technology since a long time in our daily life like: Streaming music files through internet or saving all your data on One Drive.

So just like the examples mentioned above now starting from SQL Server 2014 you can take advantage of Azure & place your Data and Log files in Azure no matter if your SQL Server Instance is running on-Premise or on Windows Azure Virtual Machine.

It lets you create & place your data and log files directly on windows azure storage, by doing this you get all the advantages of Azure storage – Bottomless storage, offsite location, Geo-Redundant, No provisioning and No need to do Device Management, its decay free (Full Media Safety) and always accessible remotely from anywhere. To know more read this.

 

Why do you want to place your data\log files on Azure?

Don’t you think this is counter intuitive to keep the files on Cloud? Yes at first even I was thinking the same. This looks odd but can be used in many circumstances. So essentially all you need to do is re-attach your (data and log) files from cloud to your SQL Instance, SQL instance can be on-premise or on Cloud.

The next question will obviously be regarding Performance…. In case you are skeptical about the performance you can host your SQL Server also on Cloud (windows Azure VM) this way your SQL Server & data\log files both will be on cloud.

This feature gives you power of a DR, even if your Data Centre goes down you can bring back the SQL instance in minutes without any special technique all you need is a SQL Instance which you can use just like your production server because all data is still intact & placed on Cloud – you just have to re-attach the files to make it working.

 

Is it Secure?

Obviously it is very much secure and your data is protected in many ways:

a) Windows Azure itself provides a mechanism to secure & protect your data; it’s not like a public folder which is open to all. To know more you can read this blog which will help you understand how the security works in Azure Storage and the importance of each type of key.

b) Granular Encryption encrypts the data (Cell by cell encryption) at all times & the key to decrypt is only available with SQL Server instance and never on the cloud along with data\log files.

c) If you are still not convinced you may still use TDE (transparent data encryption) to further encrypt it. It is fully supported.

 

Ok, I am convinced but what about my RTO & RPO?

You don’t have to worry about these two because inherently this technology take care of these two; It saves a lot of your time – you don’t have to restore the Database from a backup even if your server got crashed because your data files are intact in Cloud, you just need to re-attach them on a new Instance and your database is available right from the point where you left (you lose only uncommitted transactions) so it gives you better RPO (Recovery Point Objective) along with RTO (Recovery Time Objective)

Enough of talking now, let me know how to use this amazing feature….

 

Thanks,
Sarabpreet Singh

SQL Tip-10 | SQL Server Database Backup to Cloud (Microsoft Azure)

This feature can help you leverage the power of Cloud with your SQL instance and taking your backups directly on Cloud.

Backup to URL is not new to SQL 2014, it was first introduced in SQL Server 2012 SP1 CU2, but you were allowed to use the URL in query only. Starting with SQL Server 2014 this feature has been totally integrated, now you can specify URL while taking Backup or doing Restore no matter if you are performing the task via Query, Backup\Restore GUI, Powershell cmdlets or using SMO.

To be able to take backups or restore from Microsoft Azure (earlier named as Windows Azure) you must create a Credential. Before creating a credential you must already have a Storage account created on Azure. Once you create your storage account you’ll be able to get Storage Access Key from Azure portal. This Storage access key will provide full Admin control on your storage account.

Here is the script to create the Credential:

Create credential Storage_4Backup

with identity = ‘dbbackup12’,

SECRET = ‘H61bUxq5ld+AUFGa3Zc30CKYGSOyMDGEb/MKcCjvssdrgF34PZt+TV/42/HoJpOgnrM82KQpuCYYHEudjg==’

go

 

You need to replace the Secret key with your storage access key, the screenshot below will tell you from where you need to copy this.

clip_image002

Once the credential is created on your SQL instance you are now all set to be able to take backups or do restore from Windows Azure storage account.

Here is the T-SQL Script for the same:

Backup database azure_test

to URL = ‘http://dbbackup12.blob.core.windows.net/sqlprod/azure_test.bak’

with credential = ‘Storage_4Backup’, format, compression, stats=10, MediaName = ‘azure_test_22_02_2014’,

mediadescription=‘Backup_of_azure_test’

 

Note: To get the complete script bundled with step-by-step demo follow this link.

You can also do the backup to windows azure using Backup UI – but the credential is a must, here is the screenshot.

If the credential already exists you can select from the drop down menu otherwise the wizard itself gives you an option to create one.

clip_image004

Rest everything is self-explanatory. 😉

 

Thanks,
Sarabpreet Singh

Microsoft Cloud OS Roadshow-Gurgaon | 22-Feb-2014

This February MS is organizing Cloud OS Roadshows and I’ve got a chance to be a speaker in Microsoft Cloud OS Roadshow which is happening on 22nd Feb’14 in Gurgaon.

I’ll be sharing the stage with two amazing speakers and MVPs: Gaurav Mantri (Microsoft Azure MVP) and Shantanu Kaushik (Windows Expert IT Pro MVP)

Join this exciting full day event to learn:

  • Breakthrough Data Performance Platform
  • Virtualization
  • Building Modern Apps and Windows Azure
  • Introduction to Cloud Computing and Windows Azure Overview
  • Windows Azure Compute – IaaS, Websites and Cloud Services
  • Windows Azure Storage – SQL Database, Blob Storage, Table Storage

Lunch will be provided.

Register today: http://bit.ly/KkS1cL

MS Cloud OS Roadshow - Infopage- sarabpreet singh anand

Cheers!

 

Update: Covered by a few bloggers: https://plus.google.com/+DronacharyaInfo/posts/XHxSzkUDRK8