Calculate SQL Azure Database Size

I was looking out for a correct way to programmatically determine the size of a SQL Azure database. After searching many solutions, I finally found one shared by Dimitri Furman of the SQL Server team.

His solution involves using the sys.database_files dmv and the FILEPROPERTY function with the ‘SpaceUsed’ argument.

To those new to sys.database_files, this system catalog view stores information and properties about each file for a database. Since it is a db-level view, it gives information about files in the current database only. Five properties that could be of interest are: logical filename, physical filename, initial size, maximum size and a growth increment.

To determine how much space is used in a file, you can use FILEPROPERTY with SpaceUsed.

Here’s an example: SELECT FILEPROPERTY(‘SomeFile’, ‘SpaceUsed’); I have often used the FILEPROPERTY function in the past while monitoring the progress of a SHRINK operation.

Query for calculating Size of a SQL Azure Database

Here’s how to combine sys.database_files with FILEPROPERTY to programmatically calculate the size of a SQL Azure database.

SELECT 
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) 
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024 
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Let’s understand this query.

FILEPROPERTY() returns an int value for a file name stored within sys.database_files. Since sys.database_files is a db-level view, it gives information about files in the current database only. If a file is not present, null value is returned.

Since SpaceUsed represents "pages" and a page is 8 KB in SQL Server, so multiplying by 8192 gets the total bytes. Then dividing two times by 1024 converts the output to MB, and dividing by three times by 1024 converts the output to GB.

CAST is for casting the value to type bigint

If anybody is wondering about the dot (.) after an 8192, then it is to convert the result implicitly to a decimal value.

Please note that logs are excluded for the purposes of determining database size.

To know about Azure SQL Database resource limits, check https://docs.microsoft.com/en-in/azure/sql-database/sql-database-resource-limits

SQL Azure Migration Wizard

For those who do not know about this tool, the SQL Azure Migration Wizard is an open source tool that helps you migrate your SQL Server 2005/2008 databases to and from SQL Azure. The tools analyzes your database for any compatibility issues and allows you to either fully or partially migrate your database schema and data to SQL Azure.

Note: A couple of days ago, I had also blogged about the useful SQL Server Migration Assistant v5.0 tool which automates the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure.

You can use tools of SQL Azure Migration Wizard from command line too.

Download the SQL Azure Migration Wizard Tool

Synchronize SQL Azure with SQL Server using Sync Framework

SQL Azure Database is a cloud database service from Microsoft. Microsoft Sync Framework is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices.

Microsoft recently released a document on best practices on synchronizing SQL Azure with SQL Server using Sync Framework.

You can download the document here Sync Framework for SQL Azure

SQL Azure – Free Technical Documents

Microsoft recently released a set of document that provides guidelines on how to sign up for SQL Azure, how to get started creating SQL Azure servers and databases, how to develop and deploy solutions with Azure, Security Guidelines, Query Troubleshooting, Performance and Scalability, SLA’s, Pricing and so on. Here are the download links for your reference:

Getting Started with SQL Azure

Accounts and Billing in SQL Azure

Developing and Deploying with SQL Azure

Security Guidelines for SQL Azure

Scaling out with SQL Azure

SQL Azure vs. SQL Server

SQL Azure SLA document

Troubleshooting and Optimizing Queries with SQL Azure

Windows Azure Platform Training Kit

SQL Azure: Troubleshoot and Optimize Queries using DMV’s – Free Whitepaper

Microsoft recently published a whitepaper that provides guidelines on the Dynamic Management Views that are available in SQL Azure, and how they can be used for troubleshooting purposes.

Quoted from the document:

SQL Server generates an optimized query plan for all the queries that are executed. This allows the SQL Server optimizer to reuse the query plan when the same or similar query is executed to retrieve the data in the fastest time possible. As the data and the statistics on that data change, the query plans become out of date and can become inefficient. It is important to identify these queries and tune them for optimal performance of the application and consistent user experience. The DMVs just discussed directly help in identifying the problematic queries

Download the whitepaper: Troubleshooting and Optimizing Queries with SQL Azure

SQL Data Services (SDS) New Functionality with SQL Server 2008 R2

Previously when connecting to SQL Azure with SQL Server Management Studio, we had to cancel the default connection to database engine and later choose the New Query option. Refer to post Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

Not anymore. With SQL Server 2008 R2 (Nov CTP) we can connect to the database engine and see the list of objects (databases, tables etc) in the object explorer for SQL Azure.

The figure shows the list of databases, tables within a database and a new user ‘Smita’ added to NewDB database.

For adding a Login and a User, we need to specify the T-SQL Statements as follows:

CREATE LOGIN Smita
    WITH PASSWORD = 'Pa$$w0rd'
GO
CREATE USER Smita
    FOR LOGIN Smita
    WITH DEFAULT_SCHEMA = MySchema
GO

As the user is with the default schema, the table created automatically gets MySchema as schema name

CREATE TABLE Names
(id int IDENTITY ,
[Name] nvarchar(50))

If we issue the command

SELECT * FROM Names

by any other user whose default schema is not MySchema, we get error as follows:

Hence the query needs to be given as:

SELECT * FROM MySchema.Names  

so as to avoid errors.

Some Changes in the latest CTP of SQL Azure Service

Some new features have been introduced to the latest October CTP 2 of SQL Azure Service. The updated CTP still uses previous accounts, so you do not need the new invitation code again.

Following are some changes made to the new CTP:

With SQL Azure Services, you can now specify the maximum size for a database at the time of creation.

In addition, you now need to specify a list of IP addresses using the firewall feature which will connect to SQL Azure.

Once the IP addresses are specified and firewall settings in place, you can give the following command

CREATE DATABASE MyDB (MAXSIZE = 1GB)

You can read more on the list of changes over here

SQL Data Services (SDS) Series

SDS is a part of the Azure platform. If you have missed out on the SQL Data Services (SDS) series written by Gouri Sohoni, here’s the link list for you:

SQL Data Services (SDS)

SQL Data Services (SDS) Part II

SQL Data Services (SDS) Part III

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

SQL Data Services (SDS) Part IV

SQL Data Services (SDS) Part V

SQL Data Services (SDS) Part VI

SQL Data Services (SDS) Part VI

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query. We have also seen how to write T-SQL statements with SQL Azure.

In this post, we will discuss how XML data type works with SQL Azure using some sample queries:

How let clause works with FLOWR expression in SQL Azure

--Let clause with FLOWR expression for XML is supported in SQL Azure

declare @x xml = '';
select @x.query('
for $i in (1,2,3,4)
return $i')
go
-- returns 1 2 3 4

declare @x xml = '';
select @x.query('
for $i in ("A","B","C")
order by $i descending
return $i')
go
-- returns  C B A

declare @x xml = '';
select @x.query('
let $x := 1
return $x')
go
-- returns 1

declare @x xml = '';
select @x.query('
let $x := ( <one>2</one> )
return $x')
go
-- error:
-- XQuery [query()]: let  is not supported with constructed XML

-- When we use let inside a loop, it is evaluated each time for the loop

declare @x xml = '';
select @x.query('
for $i in (1,2)
let $j := "try"
return ($i, $j)')

-- returns 1 try 2 try
-- $j is evaluated 2 times

How XQuery works with SQL Azure

CREATE TABLE #Depts
(DeptID integer IDENTITY PRIMARY KEY,
 DeptName nvarchar(40),
 Manager nvarchar(40),
 Names xml)

INSERT INTO #Depts
VALUES
('SQL zure','Sane','<Names>
<Name FirstName="Geeta" LastName="Sohoni"/>
<Name FirstName="Mani" LastName="Raje"/>
<Name FirstName="Raja" LastName="Tembhe"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server','Dani','<Names>
<Name FirstName="Suruchi" LastName="Risbud"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server 2005','Kulkarni',NULL)

SELECT * FROM #Depts

The result is as follows:

The following query gives similar results:

SELECT DeptID, DeptName,Manager,Names.query('
/Names/Name')
FROM #Depts

The result of following query:

SELECT DeptID, DeptName,Manager,Names.value('
(/Names/Name/@FirstName)[2]','char(10)') SecondPerson
FROM #Depts

is as follows:

as the last 2 records have a single person

To fetch the Manager Name

SELECT DeptName,Manager FROM #Depts
WHERE Names.exist('/Names/Name/@FirstName[1]') = 1

The following 2 queries gives exclusive results -- one returns data where there are no people under manager and the other where at least one person has a manager

SELECT DeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name/@FirstName[1]') = 0

--using exist

SELECTDeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name') = 1

The following query will insert one of the relational column in XML as though it is an XML tag

SELECT DeptName, Names.query('<Names>
    <Mgr>{sql:column("Manager")}</Mgr>
    {
     for $i in /Names/Name
     return $i
    }
    </Names>')
FROM #Depts

--use modify method and insert a column
UPDATE #Depts
SET Names.modify('insert element Peon {"Raju"}
  as first
  into (/Names)[1]')
WHERE DeptID = 1

--delete the newly added tag
UPDATE #Depts
SET Names.modify('delete (/Names/Peon)[1]')
WHERE DeptID = 1

SQL Data Services (SDS) Part V

We discussed some T-SQL statements in my previous post SQL Data Services (SDS) Part IV. In this post, I will show some more T-SQL statements with SQL Azure and discuss the compulsion of using clustered index, and how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause

Note that SQL Azure does not support heap tables. You need to create clustered index. If a table is created without a clustered index, you must create one before inserting data. If you have no clustered index for a table and you try entering data in that table, you get following error in SQL Azure

Following is the example with Try Catch and SET XACT_ABORT_OFF or ON

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (id int primary key identity,a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SELECT * FROM t2

Even if we use Transaction, we see that the statement which gives error is not executed. The remaining 2 inserts for which there was no error, are successful

as we see with the select statement

When we use the following statement, none of the records are inserted as required:

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
SELECT * FROM t1
SELECT *
    FROM t2;
GO

And SELECT statement for table t2 shows same result as what was before the insert statements

In this post, we discussed the compulsion of using clustered index, how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause.

In next article we will discuss how to use the XML data type with SQL Azure

SQL Data Services (SDS) Part IV

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query and also programmatically. When working with SQL Azure T-SQL provided is a subset of T-SQL for SQL Server.

Using T-SQL with SQL Azure

1. When referring to an object in SQL Azure following are the conventions. Note that server name is not allowed in the reference

schema name.object name

i. Create a new query in SQL Server Management Studio (SSMS)
Enter query

CREATE SCHEMA MySchema

ii. Create table as follows

CREATE TABLE MySchema.MyTable
(Id int identity primary key,
UserName nvarchar(15))

iii. Insert rows as follows

INSERT INTO MySchema.MyTable
VALUES (Name1),('Name2')

Note you need to specify the schema name with the object. Currently using database name along with schema and object name is not supported

2. All normal data types are supported in SQL Azure. SQL Azure does not support User Defined Data type. It supports XML data type.

3. SQL Azure database does not support any of the SQL system table

a. There is no provision and requirement of backup and restore

b. There is no log shipping or replication requirement

4. A lot of T-SQL statements like ALTER SCHEMA, ALTER ROLE, DROP LOGIN, DROP USER, CAST, CONVERT, and SET @variable are supported. We will discuss some of them

a.    --create a stored proc
CREATE PROC DispNames
AS
SELECT * FROM Names

-- execute the stored proc
EXEC DispNames

--alter the existing stored proc
ALTER PROC DispNames
AS
SELECT UserId, [User Name] FROM Names

--execute changed stored proc
EXEC DispNames
--delete the proc
DROP PROC DispNames

b.

Notice the first variable data is truncated due to wrong length

c. Merge statement works with SQL Azure in the same manner as SQL Server as follows

CREATE TABLE t1
(Id int NOT NULL primary key, FullName varchar(100))

CREATE TABLE t2
(Id int NOT NULL primary key, FullName varchar(100))

INSERT INTO t1 VALUES
(1,'Smita Sane'),
(5,'Sarita Bhave'),
(6,'John')

INSERT INTO t2 VALUES
(1,'Smita Sohoni'),
(5,'Sarita sonu Bhave'),
(7,'Danny')

SELECT * FROM T1

SELECT * FROM T2

The result for this is as follows

MERGE t1
USING
(SELECT * from t2) target
ON t1.Id=target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName=target.FullName
WHEN  NOT MATCHED by target
THEN INSERT VALUES (target.Id,target.FullName);

SELECT * FROM t1
SELECT * FROM t2

After merge the result is as follows

So we see that in table T1 records are updated as well as inserted depending upon the condition

DROP TABLE T1
DROP TABLE T2

In next article we will discuss some more T-SQL statements with SQL Azure

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

In order to connect to SQL Data Services, you should have an invitation for working with SQL Azure CTP (discussed in previous article working with SQL Data Services –SDS)

1. Start SSMS 2008. When it asks for a connection to the services, click on Cancel. If by mistake you try connecting using this window, you get following error

2. Click on New query and enter following details. We can only connect using SQL Server authentication as that is the only mode supported in SQL Azure.

3. Click on Options and enter the database name as follows

If you select <default> option or <browse Server> option from Select Database we get errors

You will get a warning as shown below:

Click OK and you are connected to SQL Azure.

SQL Data Services (SDS) Part III

In my previous article SQL Data Services (SDS) Part II, we discussed how to create database and tables with the help of a query in SQL Server Management Studio (SSMS).

While writing the application we have the choice of using ADO.NET provider or SQL Server 2008 ODBC driver. In this example, we are using ADO.NET in the code snippet.

Following code is for a console application which creates a table named T1 with 3 columns in it.

// Provide the following information to connect to server
private static string userName = "<administrator name>";
private static string password = "<password>";
private static string dataSource = "<data source name";
// data source will be server name we gave for SSMS query in last article
private static string sampleDatabaseName = "<name of database we created>";

static void Main(string[] args)on we hav
{

    // Create a connection string for the existing database
    SqlConnectionStringBuilder connStringBuilder;
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dataSource;
    connStringBuilder.InitialCatalog = sampleDatabaseName;
    connStringBuilder.Encrypt = true;
    connStringBuilder.TrustServerCertificate = true;
    connStringBuilder.UserID = userName;
    connStringBuilder.Password = password;

    // Connect to the existing database, create table and insert records
    using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (SqlCommand command = conn.CreateCommand())
        {
            conn.Open();

            // Create a table
            command.CommandText = "CREATE TABLE T1([id] int primary key," +
                "FirstName varchar(20), LastName varchar(20))";
            command.ExecuteNonQuery();

            // Insert sample records
            command.CommandText = "INSERT INTO T1 ([id],FirstName, LastName)" +
            "values (1, 'Name 1','Last1'), (2, 'Name 2','Last2')," +
            " (3, 'Name 3','Last3')";
            int rowsAdded = command.ExecuteNonQuery();

            // Query table and view data in while loop
            command.CommandText = "SELECT * FROM T1";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Id: {0}, First Name: {1}, Last Name: {2}",
                                    reader["Id"].ToString(),
                                    reader["FirstName"].ToString(),
                                    reader["LastName"].ToString());
                }
            // Update a record
            command.CommandText =
                "UPDATE T1 SET [FirstName]='change name 2' WHERE [Id]=2";
            command.ExecuteNonQuery();

            // Delete a record
            command.CommandText = "DELETE FROM T1 WHERE [Id]=1";
            command.ExecuteNonQuery();

            }
        }
    }
    Console.WriteLine("Press enter to continue");
    Console.ReadLine();
 }

After you run this application, you can verify the creation of table, insertion, modification and deletion of records by connecting to SQL Azure with the help of SQL Server Management Studio as discussed in last article.

SQL Data Services (SDS) Part II

In the last article, we discussed the overview of SDS and how to start using the CTP by asking for an invitation.

Once you receive the invitation, you will have to go to URL https://sql.azure.com and login with your Windows Live Id where you have received the invitation. You will be shown a single project in My Projects tab with “SDS – only CTP project” and provided with ‘Manage’ as action.When you click on ‘Manage’ you will be shown Server name, Server administrator and the location for the server. You will need this information for further usage. You will observe that master database already exists. You can create a database of your choice by selecting Create Database button and enter name of the database.

In this article we will create our first table in the newly created database. Currently you cannot connect to SQL Azure by using SQL Server Management Studio (SSMS). This feature will be made available later. We can still make use of ‘New’ query, enter the server name and specify SQL Server authentication we created. SQL Azure only allows SQL Server authentication.

Make sure that you have selected the proper window for connection. Do not specify credentials in connect window for SSMS, as you will get an error similar to the one shown below

Make sure you click on Options tab and specify the database you need to connect to as shown. Do not select browse on server but enter name of the database. Keep the remaining properties as default.

You will get a warning as follows

Click on OK and continue.

Enter first query as SELECT @@ version

Let us create a table in the database with the name as Names and 3 columns as UserId , User Name and password. Make sure you are connected to the newly create database.

Add some records to the table by writing T-SQL as

INSERT INTO Names VALUES (1,’Name’,’Password’)

And finally enter the query

SELECT * FROM Names 

to view the added rows.

In this post, we created a database, created a new table in the database, added rows to the table and even retrieved the newly added rows. In the next post, we will view and edit the table programmatically.

SQL Data Services (SDS)

SDS is a part of Azure platform. Azure platform offers the following services: Windows Azure (Operating System in cloud), Microsoft .NET Services (set of WCF based services), SQL Azure (relational database in cloud)

SQL Azure

This service provides SQL server capabilities in cloud. We can create a database which is currently in the CTP (Community Technology Preview) form. We can have all the benefits of working with SQL Server plus no hazards of doing the administration. Thus administration tasks like replication, availability will be automatically made available to us. There will be 3 copies of data maintained out of which 2 will be synchronized and the third one may lag a bit. This also leads to limiting the size of the database to 10 GB. The size limitation is for 2 reasons, one for availability (in order to replicate the data in cloud, it needs to be within limit) and the second being shared database box for multiple users (as the same box will be used for storing the data from many users. If the size of data is huge for a single user, then the box will not be scalable to other users hence limit in size)

When we are maintaining data in the cloud again, we have 2 options of creating applications. One in which data is near the code, which means we have application running on the same box where we have data. Another will be where data is far from the code, in which we will be always manipulating data from the cloud (in case of web based application client).

How is the service provided?

The service is made available with the help of TDS (Tabular Data Stream) protocol. There are accounts available for billing purpose which will own one or more servers as per requirement. Each server will have one or more databases. These servers will use SQL Server authentication model. The databases will have one or more SQL users with respective permissions. Thus maintaining logical administration in the form of the creating views, creating triggers, tuning queries, tuning indexes etc. will be the job requirement of DBA rather than physical management in the form of how many file groups will be required, taking backup, recovery etc.

As of now, functionality like service broker, CLR (Common Language Runtime) functionality is not available with SDS, but will be subsequently made available.

How can SDS be used?

There can be various scenarios in which this service can be used.

- In small organizations, IT groups for maintaining relational database may not be available.
- In big organizations, if there is an inter department requirement for maintaining another copy of relational database, proving IT support becomes very difficult as IT staff is already overburdened.
- For creating web applications in which maintaining SQL Server for the web application may be very costly.

How can I start using SQL Azure?

You will have to register for using the current CTP over here http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

You will receive the invitation for using SQL Azure which can be used with your Windows Live ID.