SQL Tip-12 | Using SQL Server Backup to Windows Azure Tool – Part2

In last part we discussed the installation, configuration and usage of the SQL Server Backup to windows azure tool.

In this part we’ll discuss some internals of this tool and how to restore the backups which were moved to Azure storage.

As we discussed in the last part this tool creates a login ID with the name of SQLBackup2Azure , let’s diagnose it further. The tool creates this login with a low privilege account which is used to run this service. This tool also creates a group called “TempGroup” and the login SQLBackup2Azure gets the membership of this group. The name of the group and login are hardcoded and can’t be changed. However the password of the login gets generated automatically and randomly which is not known to anyone including Microsoft.

When you start adding different rules in this tool, this group get read\write access on the specified location – that is how this tool is able to take backup and restore.

The biggest disadvantage of this tool is it doesn’t provide a mechanism to track the uploaded files and any kind of error logging information, you don’t even have any control over the schedule of the uploading of backups since there is a service it keeps on checking the files.

 

Restore a Database from the Backup file which was uploaded using this tool

This tool creates a Stub file with some metadata to use during restore. You can use this tool just like a regular backup file when you want to restore a database. SQL Server automatically reads the metadata information in the file and restore the database by getting the required file from the Azure Account. Internet connectivity is a must since the stub file contains only the metadata information about the backup file rest everything is there in the Azure Storage account.

 

Stub File

This tool uploads the specified backups from the file path mentioned in the rule but once the backup file gets uploaded it creates a Stub file for the same and leaves it in the local storage folder with metadata information to be used during restore.

 

What if Stub file is inaccessible or Deleted by mistake?

A copy of the same Stub file gets uploaded along with the backup on your Azure Storage Account. If for any reason the stub file is inaccessible you can download the copy of the stub file and place it in the same location which is configured for this tool.

 

Some known issues:

1. In the event of a loss of network connectivity, SQL Server will surface the error as not being able to write to the device. You will need to clean up the files relating to this backup operation (if any) and retry the backup.

2. If you want to create a backup using the Tool, you must create a new backup file instead of overwriting/appending to an existing backup file.

 

Thanks,
Sarabpreet Singh

SQL Tip-11 | Using SQL Server Backup to Windows Azure Tool

As you know there are so many cool benefits of using Windows Azure Storage Account, everybody want to use it to get advantage. Last month i blogged about “SQL DB Backup to URL\Cloud” feature which was first introduced in SQL Server 2012 and later in SQL 2014 it was integrated completely in every aspect.

But what if you are still running old version of SQL Server and want to take advantage of Cloud?

Microsoft is being very kind and they are providing you a workaround. They released a small tool which can help you create a one cloud backup strategy that means you will be able to take backups on cloud for prior SQL versions like SQL Server 2005, 2008 and SQL 2008 R2.

By using this tool you get all the latest features like Backup on Cloud, using Encryption and Compression in all earlier versions.  Currently these features are available only in following versions\editions.

Feature Version Edition
Backup Encryption 2014 Enterprise, Standard and Business Intelligence Edition
Backup Compression 2008 Enterprise Edition
Backup Compression 2008 R2 Enterprise and Standard Edition


Isn’t it cool you get all latest features even if you’ve not upgraded to new version\edition.

The Utility can be downloaded from this link and the utility is less than 10 MB is size. Let’s see how to install, configure and use this tool.

Installation of this tool is quite simple just like any other regular software.

1  2

3   4

The tool installs a service which takes the responsibility of taking the backups from local system to Cloud. The tool also creates a separate login for this tool with the name of “SQLBackup2Azure.

service

 

To configure this tool you’ve to provide a local path of a folder where your backups will be placed – this location will act as a source for this tool. You need to also mention the file name\extension which will be picked to be backed-up on the cloud. The best part is you can use wildcards to select the type of files.

config1 config2

 

Next you need to mention the details about your Windows Azure Storage Account, this is similar to creating a credential which will provide necessary access to this tool to backup the files on Azure. You get an option to Verify the account details right from the wizard.

config3

In the next page you also get an option to use Encryption and Compression for all these backups.

config4 config5

Read the next part here…

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

2 Days Training on SQL Server 2014 delivered for Microsoft | Bangalore | 11-12-March’14

Microsoft SQL Server 2014

Learn about Microsoft’s approach to delivering cutting edge database solutions and an analytics platform that offers agile self-service business intelligence, scalable insights across multiple data types, and robust data management and warehousing capabilities.

The training is  tailored for partner Application and Database subject matter experts to help scale out technical enablement on In-memory databases, enterprise cloud, and big data. This covers the following technologies including Microsoft SQL Server Roadmap, Azure SQL Database, and Modern Warehousing

 

11-12-March-2014 Bangalore 2 days SQL Server 2014 Training

Global Windows Azure BootCamp 2014 | 29-March-2014

Welcome to Global Windows Azure Bootcamp- 2014 !

Global windows azure bootcamp 2014We invite you to join us on Saturday, March 29, 2014 for a 1 day deep dive class on developing Cloud Computing Applications for Windows Azure. Join us for a full day session with lunch, high tea and a chance to network with fellow windows enthusiasts. 

I’ll be speaking at the Event about “Hybrid Cloud Features of SQL Server 2014

Register: http://www.eventbrite.com/e/global-windows-azure-bootcamp-2014-tickets-10829554489?aff=es2&rank=1&sid=e7a33c45b3e211e3b5731231391edcec

Register for the event above or contact: Vikas Mangla (vikas.mangla@globallogic.com) @9810219770 for more information.

Event Details:

Venue: GlobalLogic India Limited, Noida  (Tower A, Oxygen Park, Plot No.7, Sector-144, Noida Expressway, Noida, Uttar Pradesh IN) 

Timings: 9 am to 5 pm

Transport: To & Fro cab facility for all participants from Noida city centre metro station. Send in your details via email to vikas.mangla@globallogic.com for cab facility

Participant take aways: Windows Azure licenced software, Goodies powered by Windows Azure and GlobalLogic.

Here is the detailed Agenda:

Global Windows Azure Bootcamp agenda 2014

 

See you at the Event!

Cheers!

SQL Server 2014 Training delivered for Microsoft | Gurgaon | 4-5 March’14

Microsoft SQL Server 2014

Learn about Microsoft’s approach to delivering cutting edge database solutions and an analytics platform that offers agile self-service business intelligence, scalable insights across multiple data types, and robust data management and warehousing capabilities.

The training is tailored for partner Application and Database subject matter experts to help scale out technical enablement on In-memory databases, enterprise cloud, and big data. This covers the following technologies including Microsoft SQL Server Roadmap, Azure SQL Database, and Modern Warehousing

 

4-5-March-2014 - Gurgaon - 2 days SQL 2014 Training

ITPro Track – Demystifying SQL Server Backups | 19-March-2014

Microsoft MVP community India along with Microsoft is hosting an online tech conference called “Microsoft Virtual Tech Conference” for both IT Pro and Developer audience.  This is scheduled on 19th March 2014.

I’ll be presenting a Session for ITPro Track: Demystifying SQL Server Backups. In this Demo heavy session SQL Server MVP Sarabpreet Singh Anand will explain you the untold tips and tricks. Sarabpreet will Demo how to do Point-in-time recovery, what are the key things to keep in mind while planning the backup strategy of your mission critical DB. We’ll corrupt a DB live and then show you how to recover it.

I’ll also try to cover the new features of SQL Server 2014 related to Backups.

So Stay tuned and Register for this session: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032577204&culture=en-IN

 

VTCC2014 - Sarabpreet Singh Anand - Demystifying SQL Server backups

Benefits of using Windows Azure Storage

Everybody is talking about Cloud now a days, its the next big thing in the market and we are curious to know more about it. Microsoft introduced Windows Azure Storage features long time back but we are still not aware about its full potential.

Azure storage accounts provides many great features but for the sake of brevity let’s look at some really cool things you must be aware of as an end user:

  1. Nearly bottomless storage: you don’t have to worry about how much storage we have and how to manage your devices.
  2. No Provisioning is required:
  3. Relatively economical Option: It is comparatively cheaper than having on-premise storage with so many capabilities.
  4. No Device management: you don’t have to bother about device management.
  5. Geo-Redundant: Provides a capability of Geo-Redundant.
  6. Media Safety.
  7. Remote Accessibility: You can access the data anywhere.
  8. Flexible, reliable, and limitless off-site storage: no need to ship the backup tapes to Offsite location to meet Compliance and prepare for DR.

To know more about the storage accounts and its pricing, please check the below link:http://azure.microsoft.com/en-us/services/storage/

http://azure.microsoft.com/en-us/pricing/details/storage/

 

Thanks,
Sarabpreet Singh