How to Backup Azure Database within SQL Azure


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

image

You also can’t use BACKUP DATABASE command

image

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’

image

 

— Get the Status of Database Copying

SELECT * FROM SYS.DM_DATABASE_COPIES 

        WHERE DATABASE_ID = DB_ID(‘NorthWind_DB_Copy’)

image

 

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’

image

How to monitor SQL Azure bandwidth usage by yourself ?


SQL Azure provides the following System Views

sys.bandwidth_usage returns the following output columns

image

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

image

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;

image

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

Free SQL Azure Diagnostics Tool has been released !!!


You can download and install SQL Azure Diagnostics tools from

http://csssqlazure.blob.core.windows.net/csssqlazuredeploy/publish.htm

image

Click “Install” to download the setup, After download, run the setup

image

Click “Install” to proceed

image

After Install, Start “CSS SQL Azure Diagnostics”

image

Please provide SQL Azure server name, Database name, User name and Password and then click “Go”

Now you should able to view the following reports

1. Top 10 CPU Consuming Queries

image

2. Top 10 time taking Queries

image

3. Top 10 Logical I/O consuming queries

image

4. Top 10 Physical I/O Consuming Queries

image

You can export the report to Excel, PDF or Word

image

You can also print the report.

Thanks to Microsoft Customer Service and Support (CSS) SQL Support for providing such a nice tool freely !!!

%d bloggers like this: