SQL Azure - Views in SQL Azure Database And Not in On-Premise SQL Database

Introduction:

In this article we are going to see the list of System View available in SQL Azure Database and not available in the On Premises SQL Server Local Database.

Overview:

Normally we can see everywhere saying that SQL Azure is a light weight SQL Server Database in the cloud which holds minimum availability of resources to process data. We have quite a small set of resources/system views which are not available in the original traditional version of SQL Server database. Here we are going to see the different views available in the SQL Azure version and not available in the SQL Server traditional version.

SQL Azure System Views:

Here is the list of system views which are specific to SQL Azure Database:

1. sys.bandwidth_usage
2. sys.dm_database_copies
3. sys.firewall_rules
4. sys.database_usage
5. sys.dm_db_objects_impacted_on_version_change

Let us see the usage of each and every view listed above to get a fair idea of how to use the view in real time to use most of its features.

sys.bandwidth_usage:

This view is used to get the bandwidth usage of each database of a Microsoft SQL Azure Server under the subscription. This view is specific to the SQL Azure database which we can connect from the SSMS (SQL Server Management Studio) and use the features of the view to pull out the information. This view is available only with the Master database with SQL Azure Server.
This view contains the fields as listed below:

• time - Specifies the hour when the database bandwidth was consumed
• database_name - Name of the database used with the bandwidth
• direction - Type of bandwidth used (Ingress/Egress)
• class - Class of bandwidth used (Internal/External)
• time_period - Time period on when it's used (Peak or Off-peak)
• quantity - Amount of bandwidth in KB's

To use this view, login to the SQL Azure view and run the following query to fetch the results.

Select * from sys.bandwidth_usage

sys.dm_database_copies:

This view is used when we copy a database; it stores the result information into this table for each database when we perform a copy operation. This view is specific to the SQL Azure database which we can connect from the SSMS (SQL Server Management Studio) and use the features of the view to pull the information of the database copy process. This view is also available only to the Master Database within the SQL Azure Server.

This view contains the fields as listed below:

• database_id - Database id in the Sys.database view
• start_date - Start time of the database copy initiated
• modify_date - End time of the database copy got completed
• percentage_complete - Percentage of the bytes of database copied and values ranges between 0 to 100
• error_code - Error code when any error occurred during copying. If value > 0 then error has occured.
• error_desc - Error description that occurred during copying
• error_severity - If database copy failed then it returns number 16
• error_state - If database copy failed then it returns number 1

To use this view, login to the SQL Azure view and run the following query to fetch the results.

Select * from  sys.dm_database_copies

sys.firewall_rules:

This view is used to check the current firewall settings with an IP range (Min to Max IP range) associated with the SQL Azure Server database. We can add or delete the IP required to provide a firewall to the server. This view is specific to the SQL Azure database which we can connect from the SSMS (SQL Server Management Studio) and use the features of the view to pull the information of the firewall settings including the IP addresses. This view is also available only to the Master Database within the SQL Azure Server.
This view contains the fields as listed below:

• id - Unique identifier for the records in the firewall setting table
• name - User friendly name to specify the firewall name
• start_ip_address - IP starting range or the minimum IP address which is used to connect to the server
• end_ip_address - IP ending range or the maximum IP address which can be used to connect to server
• create_date - Date and time on which the Firewall setting was created for the IP range
• modify_date - Date and time recorded when any update made to the IP range.

To use this view login to the SQL Azure view and run the following query to fetch the results.

Select * from  sys.firewall_rules

sys.database_usage:

This view is used to check the current database usage like the number of databases available under a server, duration of the database in the server and the type of the database created in the server. This view is specific to the SQL Azure database which we can connect from the SSMS (SQL Server Management Studio) and use the features of the view to current database details as mentioned above. This view is also available only to the Master Database with in the SQL Azure Server. This database view returns 1 row for each day of the server subscription.
This view contains the fields as listed below:

• time - Date of the database usage events
• sku - Type of the database if it's a Business or a Web Edition
• quantity - Maximum number of databases available on the particular date.

To use this view login to the SQL Azure view and run the following query to fetch the results.

Select * from  sys.database_usage

sys.dm_db_objects_impacted_on_version_change:

This system view is a warning system provided by SQL Azure if any major upgrade is going to happen in the server level. This view provides the list of table and objects that will be impacted by the upgrade. We need to use this view during the upgrade or after the upgrade to check the impacted object and design is as per the standards provided. We need to query this view in each and every database available in a server under a subscription to get the list of objects for that particular database queried. This view is specific to the SQL Azure database which we can connect from the SSMS (SQL Server Management Studio) and use the features of the view to query the impacted objects. This view is available to all the databases within the SQL Azure Server.

This view contains the fields as listed below:

• class - Provides which object is impacted as 1 is constraint and 7 is indexes
• class_desc - Provides the description of the class of object impacted
• major_id - Object ID of the impacted object
• minor_id - It can be null and it has the class name which is associated
• dependency - Description of the dependency which is causing impact for the upgrade.

So when we see an impacted object in this table we need to do an update as recommended manually like for example if an index needs to be updated and rebuild we need to write a query like below:

ALTER Index ALL on <Table Name> Rebuild

To use this view, login to the SQL Azure view and run the following query to fetch the results.

Select * from  sys.dm_db_objects_impacted_on_version_change

Conclusion:

So in this article we have seen the list of views available only in a SQL Azure database and not with the traditional SQL Server database with each view's properties.