Windows Azure Lunch event (complete cloud environment)

After successfully lunch SQL Azure services now Microsoft launch Windows Azure. A complete and comprehensive platform for developing and deploying applications. It is now integrated for Windows, Mac, Linux OS developers as well. Supporting more then one Db like SQL SERVER, MySQL, Mongo DB etc.

For detail look at the Scott Guthrie (CVP Windows Azure, Application plateform) presentation at Microsoft platform.

http://www.meetwindowsazure.com/Conversations

Windows Azure provides a Microsoft Windows Server-based computing environment for applications and persistent storage for both structured and unstructured data, as well as asynchronous messaging. Windows Azure also provides a range of services that helps you connect users and on-premises applications to cloud-hosted applications, manage authentication, use inter-service messaging, and implement data management and related features such as caching.

SQL Azure is essentially SQL Server provided as a service in the cloud.

Major features are:

Compute Environment

• Windows Azure Compute (Web and Worker Roles).
• Virtual Machine (VM role). 

Data Management

• Windows Azure Storage.
• SQL Azure Database.
• Data Synchronization.
• Caching. 

Networking Services

• Content Delivery Network (CDN). 
• Virtual Network Connect.
• Virtual Network Traffic Manager. 
• Access Control (ACS).
• Service Bus.

Other Services

• Business Intelligence Reporting. 
• Marketplace. 

Index fragmentation, rebuild and reorganize through TSQL

We need to look into index fragmentation of our database indexes on regular intervals. Once find fragmented then we need to rebuild and reorganize at some point.

to do all three steps. we need to search on many places to find how to find fragmentation and rebuild or reorganize query. I have gathered all 3 steps here

Step 1: Find fragmentation percent of your running indexes.

========================= Code segment  starts ==================================

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > 0
ORDER BY ps.OBJECT_ID
========================= Code segment ends ===================================

screen shot shows how much an index has been fragmented.

Step2: If indexes are over 10 percent to 40 percent you need to reorganize these indexes. Keep in mind it will effect performance during re-Organization.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REORGANIZE
GO
========================= Code segment ends ===================================

Step3: If indexes are over 40 percent fragmented then you need to rebuild these indexes. Keep in mind it will effect performance during re-Organization. Query used to rebuild all indexes on mentioned table. You can mention indexes name for one.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REBUILD
GO
========================= Code segment ends ===================================

Typed XML vs UnTyped XML

XML stands for EXtensible Markup Language. XML tags are not predefined. You must define your own tags. It is used so much in web designing and managing structure and data. XML introduced in SQL server as well to manage extensive data with your specific structure as you can define relational tables in database. From SQL SERVER 2005 xml introduced as data type both for column and variable.

Typed XML vs UnTyped XML

When we define an xml as a variable, parameter then it is called as UnTyped but when we assign some defined structure with it, it becomes typed. Msdn says “You can optionally associate a collection of XML schemas with a variable, parameter, or column of xml type. In this case, the xml data type instance is called typed. Otherwise, the XML instance is called untyped.”. Untyped xml can verify the opening and closing structure of XML basics like tagging etc but cannot verify structure.

 Typed xml when assigned to a column, SQL Server validates the instance. Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

To create an Untyped xml you only needs to declare an xml variable and assign an xml to it. as shows in following code and screen shot.

You can also create Untyped XML using output of any query using For XML as shown in following screen

To create typed XML you needs to declare XML schema collection first and then assign it to a column of XML data type. Then you can insert data according to assigned schema structure. XML data type stores upto 2GB of xml data. When Schema against XML or Typed XML will require more space to store data and implement Xml Indexes, discussed later. 

There are some general limitation applied to XML discussed below

The stored representation of xml data type instances cannot exceed 2 GB.

·         It cannot be used as a subtype of a sql_variant instance.

·         It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.

·         It cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.

·         It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.