About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Sunday, March 20, 2011

How SQL Azure different from my SQL Server ??.. SQL Azure vs SQL Server

 Similarities and Differences

Similar to an instance of SQL Server on your premises, SQL Azure exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use SQL Azure in the same way that they use SQL Server. Since SQL Azure is a service, administration in SQL Azure is slightly different.
Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; you continue to administer databases, logins, users, and roles, but Microsoft administers and configures the physical hardware such as hard drives, servers, and storage. This approach helps SQL Azure provide a large-scale multi-tenant database service that offers enterprise-class availability, scalability, security, and self-healing.
Since Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features. For more information, see Guidelines and Limitations (SQL Azure Database).

 

Logical Administration vs. Physical Administration

Although SQL Azure plays an active role in managing the physical resources of the database, the DBA plays a very important role in administering SQL Azure-based database applications. Using SQL Azure, DBAs manage schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles, etc.). For more information about security administration in SQL Azure, see Managing Logins and Users in SQL Azure.
Database administration in SQL Azure differs most from SQL Server in terms of physical administration. SQL Azure automatically replicates all data to provide high availability. SQL Azure also manages load balancing and, in case of a server failure, transparent fail-over to a healthy machine hosting one of the backup copies of your database.
To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure. The SQL Azure service still backs up all databases; however they are not accessible to regular users. This is a feature that may be offered in future.


Starting with SQL Azure Service Update 4, SQL Azure allows you to back up your database by copying it to a new database in SQL Azure. For more information, see Copying Databases in SQL Azure.
For more information on the available options to transfer data to SQL Azure, see Migrating Databases to SQL Azure.

 

Provisioning

When preparing an on-premises SQL Server deployment, it may be the role of the DBA or IT department to prepare and configure the required hardware and software. When using SQL Azure, these tasks are performed by the SQL Azure provisioning process.
You can begin provisioning your SQL Azure databases after you create a Windows Azure Platform account. This account allows you to access all the services, such as Windows Azure, AppFabric, and SQL Azure, and is used to set up and manage your subscriptions.
Each SQL Azure subscription is bound to one SQL Azure server within one of the Microsoft data centers. Your SQL Azure server is an abstraction that defines a grouping of databases. To enable load-balancing and high availability, databases associated with your SQL Azure server may reside on separate physical computers within the Microsoft data center.

For more information about provisioning, see SQL Azure Provisioning Model.

 

 

Transact-SQL Support

Transact-SQL is a language that contains commands used to administer instances of SQL Server including creating and managing all objects in an instance of SQL Server, and inserting, retrieving, modifying, and deleting all data in tables. Applications can communicate with an instance of SQL Server by sending Transact-SQL statements to the server. Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server. For more information about Transact-SQL support, see Transact-SQL Support (SQL Azure Database).

 

Features and Types

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, and Service Broker are not currently provided as services on the SQL Azure. You can connect from on-premises Analysis Server to SQL Azure and SQL Azure can be used either as a data source or destination. When this article is being updated, the Customer Technology Preview of SQL Azure Reporting is also available. Microsoft SQL Azure Reporting  is a cloud-based reporting service built on SQL Azure, SQL Server, and SQL Server Reporting Services technologies. You can publish, view, and manage reports that display data from SQL Azure data sources. 
Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

 

Key Benefits of the Service

The benefits of using SQL Azure include manageability, high availability, scalability, a familiar development model, and a relational data model.

 

Self-Managing

SQL Azure offers the scale and functionality of an enterprise data center without the administrative overhead that is associated with on-premise instances of SQL Server. This self-managing capability enables organizations to provision data services for applications throughout the enterprise without adding to the support burden of the central IT department or distracting technology-savvy employees from their core tasks in order to maintain a departmental database application.
With SQL Azure, you can provision your data storage in minutes. This reduces the initial costs of data services by enabling you to provision only what you need. When your needs change, you can easily extend your cloud-based data storage to meet those needs.

 

High Availability

SQL Azure is built on proven Windows Server and SQL Server technologies, and is flexible enough to cope with any variations in usage and load. The service replicates multiple redundant copies of your data to multiple physical servers to maintain data availability and business continuity. In the case of a hardware failure, SQL Azure provides automatic failover to ensure availability for your application.

 

Scalability

A key advantage of SQL Azure is the ease with which you can scale your solution. As data grows, databases need to either scale up or scale out. Scale up always has a ceiling whereas scale out has no virtual limits. A common scale out technique is data-partitioning. After partitioning your data, the service scales as your data grows. A pay-as-you-grow pricing model makes sure that you only pay for the storage that you use, so that you can also scale down the service when you do not need it.

 

Familiar Development Model

When developers create on-premise applications that use SQL Server, they use client libraries like ADO.NET, ODBC that use the tabular data stream (TDS) protocol to communicate between client and server. SQL Azure provides the same TDS interface as SQL Server so that you can use the same tools and libraries to build client applications for data that is stored in SQL Azure. For more about TDS, see Network Protocols and TDS Endpoints.

 

Relational Data Model

SQL Azure will seem very familiar to developers and administrators because data is stored in SQL Azure just like it is stored in SQL Server, by using using Transact-SQL. Conceptually similar to an on-premise instance of SQL Server, a SQL Azure server is logical group of databases that acts as an authorization boundary.
Within each SQL Azure server, you can create multiple databases that have tables, views, stored procedures, indices, and other familiar database objects. This data model makes good use of your existing relational database design and Transact-SQL programming skills, and simplifies the process of migrating existing on-premise database applications to SQL Azure. For more about Transact-SQL and its relationship to SQL Azure, see Transact-SQL Support (SQL Azure Database).
SQL Azure servers and databases are virtual objects that do not correspond to physical servers and databases. By insulating you from the physical implementation, SQL Azure enables you to spend time on your database design and adding additional value to the business.
The following table provides a high-level comparison between SQL Azure and SQL Server. For the most up-to-date and detailed information, see the following topics on MSDN:
Taken from : http://social.technet.microsoft.com/wiki/contents/articles/comparing-sql-server-with-sql-azure.aspx

Post Reference: Vikram Aristocratic Elfin Share 

BurlesqueKindle 3G Wireless Reading Device, Free 3G + Wi-Fi, 3G Works Globally, Graphite, 6" Display with New E Ink Pearl Technology 

What SQL Azure Database ??

Microsoft SQL Azure Database is a database service provided as part of the Windows Azure Platform. It is based on Microsoft SQL Server, and provides many of the same features. Unlike SQL Server, which is provided as a software package that you install and maintain, SQL Azure Database is provided as a service that is hosted on hardware in Microsoft datacenters. SQL Azure takes care of maintaining the hardware and software layer, freeing you to concentrate on database design.
Since SQL Azure maintains the hardware and software for your database, provisioning a new database is very straightforward and easy; you request a new database and Microsoft provisions one automatically for you. Data stored in SQL Azure Database is also highly available, as each database hosted in the Microsoft data center has three copies: one primary and two secondary replicas. All reads and writes go through the primary, and any changes are replicated to the secondary replicas asynchronously.

Kindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology

Post Reference: Vikram Aristocratic Elfin Share

Thursday, March 17, 2011

When to TRUNCATE and when to DELETE ??


Difference between Truncate and Delete in SQL

3-Pack HTC EVO 4G Sprint Combo Screen Protector for HTC EVO 4G Sprint

  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

  • You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .

  • You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

  • Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.

  • TRUNCATE is faster than DELETE.



  • Post Reference: Vikram Aristocratic Elfin Share