BLOGS

ByHariharan Rajendran

SQL Azure Database Table Size

Identifying the table size of the azure SQL database is not possible through GUI since the properties of the table not providing the size information.

The different between the table properties in on-premises SQL database and Azure SQL database.

Right-Click the table and go to Storage,

On-premises  SQL Server

image

 

Azure SQL database,

image

As you can see, SQL Azure database table doesn’t have storage information of the table.

To identify the table size and number of records, we need to use SQL scripts.

Script 1 : It is using “sys.dm_db_partition_stats” & “sys.objects” system tables.

SELECT   

      o.name AS [table_name],

      sum(p.reserved_page_count) * 8.0 / 1024  AS [size_in_mb],

      p.row_count AS [records]

FROM  

      sys.dm_db_partition_stats AS p,

      sys.objects AS o

WHERE   

      p.object_id = o.object_id

      AND o.is_ms_shipped = 0 

GROUP BY o.name , p.row_count

ORDER BY o.name DESC

 

image

Script 2: Using sp_spaceused stored procedure.

Exec sp_spaceused [Users] –Table Name

image

ByHariharan Rajendran

Connect Azure Storage in SQL Server Management Studio

It is very simple to connect your azure storage in SSMS.

Make sure that you are not using full name of  your storage account.

For example: If you have storage account named “Test”. Usually, the full name of the storage account will be “https://Test.blob.core.windows.net”.

To connect your storage account in SSMS, use only the name of your account (here it is “Test”). Then provide the storage key.

To get the storage key, follow the below steps.

Visit Management portal and choose your storage account.

image

Select the Manage Access Keys and copy your key.

image

 

Use your storage account name and key in SSMS.

image

image

image

ByHariharan Rajendran

Limitations in Stretch DB in SQL Server 2016 CTP 3

As per Microsoft article, we have many limitations that we need to understand before start enabling the stretch database in your environment.

I have discussed few limitations here and also what will happen when we override those limitations.

Limitations

1. We cannot perform UPDATE and DELETE operations on stretch enabled tables

image

2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.

image

3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.

image

ByHariharan Rajendran

[Step by Step] Create Database from .bacpac File

We discussed before about how to export the database from azure and stored the bacpac file in azure storage container.

This article about how to create a database in on-premises  SQL Server using bacpac file.

Step 1: Download the bacpac file from azure storage container

image

Step 2: Use data tier application to import the bacpac file in SQL Server

image

Step 3: Follow the wizard instructions. You can able to create database using your azure storage container as well.

image

image

image

image

Now, you have successfully created a database using bacpac file.

ByHariharan Rajendran

SAS Token for Azure Storage

Creating a Shared Access Signature for your azure storage is bit complex. Microsoft suggesting to use console application to generate the SAS (article here).

If you are not familiar with ADO.Net application then very difficult to get the SAS key.

To make it easy, Azure Storage Explorer providing the GUI based SAS generator.

Download the Azure Storage Explorer, here.

in storage explorer, add your azure storage account by using “Add Account” option.

image

Once you have configured then select your container and choose “Security”.

image

Select the options as per your wish and generate the signature.

image

ByHariharan Rajendran

[Step by Step] Export Azure SQL Database

Follow the below steps to export the database. This database can used again in azure SQL database with different server also in VM SQL Server or on-premise SQL Server.

Step 1 : As usual log in to your portal.azure.com

Step 2: Select your database which you want to export.

Step 3: choose export option.

image

Step 4: Provide the Azure storage container where you want to store the backup file.

image

Step 5: Select OK. Finished. You have successfully back up your database.

Step 6: Verify the back up in your storage

image

Note: The extension of the back up file is not .bak as a typical format. You cannot restore this file in your SQL Server Management studio just like .bak restore.

ByHariharan Rajendran

Named SQL Server in Azure

There is no option to create a your own named server in Azure management portal.

While creating a SQL database, azure will create a SQL Server with random name. This is not a good practice in real time environment as we are maintaining the proper naming conventions.

We can create a  our own named SQL Server in Portal.azure.com

Create a new database and select the “Create a new Server” option to create your own named server.

image

ByHariharan Rajendran

Steps to Upgrade Azure SQL Server

Azure has released a new version of SQL Server V12.

If you are using V2 SQL Server then you can upgrade to V12. This upgrade option is available in Preview Portal.

Follow the below steps to upgrade your SQL Server

Step 1: Visit Preview Portal and open your SQL Server then select server version.

image

Step 2: select the “Upgrade this server” in right hand side window of your SQL Server

image

Step 3: New window will ask you to enter your SQL Server name

image

Step 4: Enter your SQL Server name and select “OK”. Upgrade process will be initiated.

image

 

Wait for sometime and your SQL Server will be upgraded to newer version V12.

To compare the feature with older and newer version, visit my previous article.