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
Azure SQL database,
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.
o.name AS [table_name],
sum(p.reserved_page_count) * 8.0 / 1024 AS [size_in_mb],
p.row_count AS [records]
sys.dm_db_partition_stats AS p,
sys.objects AS o
p.object_id = o.object_id
AND o.is_ms_shipped = 0
GROUP BY o.name , p.row_count
ORDER BY o.name DESC
Script 2: Using sp_spaceused stored procedure.
Exec sp_spaceused [Users] –Table Name
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.
Select the Manage Access Keys and copy your key.
Use your storage account name and key in SSMS.
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.
1. We cannot perform UPDATE and DELETE operations on stretch enabled tables
2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.
3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.
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
Step 2: Use data tier application to import the bacpac file in SQL Server
Step 3: Follow the wizard instructions. You can able to create database using your azure storage container as well.
Now, you have successfully created a database using bacpac file.
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.
Once you have configured then select your container and choose “Security”.
Select the options as per your wish and generate the signature.
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.
Step 4: Provide the Azure storage container where you want to store the backup file.
Step 5: Select OK. Finished. You have successfully back up your database.
Step 6: Verify the back up in your storage
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.
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.
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.
Step 2: select the “Upgrade this server” in right hand side window of your SQL Server
Step 3: New window will ask you to enter your SQL Server name
Step 4: Enter your SQL Server name and select “OK”. Upgrade process will be initiated.
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.