.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> Azure >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Views in SQL Azure Database and not in On-Premise SQL Database in SQL Azure

Posted By:Karthikeyan Anbarasan       Posted Date: July 27, 2011    Points: 200    Category: Azure    URL: http://www.dotnetspark.com  

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.
 

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 on to the cloud which holds minimum availability of resources to process data. We have quite a small set of resources/system view which is not available in the original traditional version of SQL Server database. Here we are going to see the different views which are available in 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 views listed above to get a fair idea on how to use this 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's. 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 below 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 with in 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 below 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 which are 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 firewall settings including the IP addresses. This view is also available only to the Master Database with in 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 below 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 database 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 below 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 which will get impacted during 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 Database with in 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 Rebuild

To use this view login to the SQL Azure view and run the below 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 different views available only in SQL Azure database and not with the traditional SQL Server database with each views properties.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend