SQL Azure Admin – Page 3

Backup SQL Azure Database using Red Gate SQL Azure Backup Tool

After reading my last article one of my friend asked, whether there is any easy way to Backup SQL Azure databases, without writing scripts or custom programs, so I was searching for 3rd party products and found the Red Gate SQL Azure Backup Tool, its still in Beta, but works pretty good.

You can download it from the following link

http://www.red-gate.com/products/dba/sql-azure-backup/

Currently its available for Free to download

After downloading, extract the zip file and run the program

Specify the name of the SQL Azure Server name and provide username and password on click on Database selection to pick the database you want to backup

Next Enter the Local SQL Server Name (Destination Backup Server) and specify Credentials, then enter the name of the Local database to be created for backup from Azure

Warning: Existing database cannot be used for backup. You need to use a new database name only

Click “Next” to proceed

Click on “Start Backup” to start the backup process

Status of Backup process will be displayed in the progress bar, depending upon the size of the database

Backup has completed successfully !!!

Some of the features, I would like to see in this product is

1. Integration with SQL Server Management Studio or Red Gate SQL Backup Pro

2. Option to run from Command Line, so that we can it as Windows Task and automate it

3. Option to run backup for multiple databases

Advantages:

Easy to Use

Fast

Small and Lightweight

Disadvantages:

Need to create a new database every time to create a backup

How to Backup Azure Database within SQL Azure

Currently there is no built in Backup option to backup SQL Azure databases from Management Studio

You also can’t use BACKUP DATABASE command

There is a new option to create a copy of your database quickly from SQL Azure within the same server using

CREATE DATABASE <TargetDB> AS COPY OF <SourceDB>

Ex.

CREATE DATABASE NorthWind_DB_Copy AS COPY OF Northwind_DB;

By running this SQL Command in Master Database, you can create a Copy of NorthWind_DB to Northwind_DB_Copy

Using the following query you can get the status of Copy Status

— Query to fetch the state of the new database

SELECT

DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

— Get the Status of Database Copying

SELECT * FROM SYS.DM_DATABASE_COPIES

WHERE DATABASE_ID = DB_ID(‘NorthWind_DB_Copy’)

Once the copying is complete, Database will be Online and available for access

SELECT

DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

How to monitor SQL Azure bandwidth usage by yourself ?

SQL Azure provides the following System Views

sys.bandwidth_usage returns the following output columns

Using this System view you can calculate the Bandwidth usage and cost using the following queries

DECLARE @Cost AS MONEY = 0.15;

SELECT CAST (time AS DATE) AS DateofUse,

direction,

class,

time_period,

sum(Quantity) AS QuantityUsed,

sum(Quantity) * @Cost / (1048576) AS [CostOfUsage (in $)]

FROM sys.bandwidth_usage

WHERE class = ‘External’

GROUP BY CAST (time AS DATE), direction, class, time_period;

This query gives you the following detailed output

Using the following Pivot query, you can get date wise InBound and OutBound traffic and its associated cost

DECLARE @Cost AS MONEY = 0.15;

SELECT DateOfUse,

[Egress] AS [OutBoundTraffic],

[Egress] * @Cost / 1048576 AS [OutBoundCost],

[Ingress] AS [InBoundTraffic],

[Ingress] * @Cost / 1048576 AS [InBoundCost],

[Egress] + [Ingress] AS [TotalTraffic],

([Egress] + [Ingress]) * @Cost / 1048576 AS [TotalCost]

FROM (SELECT CAST (time AS DATE) AS DateofUse,

direction, quantity

FROM sys.bandwidth_usage

WHERE class = ‘External’) AS BU PIVOT (SUM (Quantity) FOR Direction IN ([Egress], [Ingress])) AS Pvt;

You can use these queries to collect data from SQL Azure and monitor usage from your application itself on day to day basis.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: