What is SQL Azure

  • Article

SQL Azure

  1. A cloud based SQL Server database on clustered servers.
  2. Servers are load balanced, replicated databases and failure detection. Which provides high availability, scalability and reliability.
  3. Each user database is having 3 replicated databases. Before a transaction gets committed, one primary and one secondary replicated database should also be updated.
  4. It supports .net framework 3.5 onwards and OData (a WCF data service) interface to transfer data. OLEDB is not supported.
  5. Storage account ConnectionStringBuilder for cloud configuration can be used to create a connection string.
  6. Federations – It is the way to achieve high scalability and performance through horizontal partitioning (row split) of one or more tables data. It is a database object in database and there can be multiple federations in a database. Each federation has a single key for partitioning data. The database contains the federation database object is known as federation root. Applications connect to federation root.

 Set Up

  1. Get a windows Azure subscription. Get the SQL Server service.
  2. We can create database either accessing the Azure database server on SSMS or Azure service portal.
  3. There are 2 version of sql azure subscriptions web and business editions. In web edition a user can create 1 master and 149 user databases.
  4. Only SQL Server login is allowed. The ip address from which the database to be accessed should be configured in firewall. The user can configure the range of ip addresses. The connections are encrypted and
    managed by firewall rules.
  5. REST based APIs are used to manage database. These include create, drop database servers and manage firewall rules.


  
  
   If user selects Custom Create option of database create, user has to provide an existing or new server name (user can select the preferred location of the server). 
  
  

Once server is created, we can configure the firewall setting for server access. The ip of the local system (info can be get from ipconfig) may be different from the global ip (https://www.whatismyip.com/).

Once setting are completed, user can access the database. The user has to append the server name while accessing the desired database.

The user can also login to the azure database from SSMS (SQL Server 2008 R2 onwards). While accessing from SSMS, the user needs to provide the SQL authentication and credential like above picture.

How SQL Azure is different from traditional SQL world?

 

Traditional SQL Server

SQL Azure

1. Supports all system databases.

1. Only Master and some tempdb functionality supported.

2. Supports very large size databases.

2. Based on edition (web and business) the max size per database is 5  and 50 GB respectively.

3. Both windows and Sql Server authentications are allowed.

3. Only Sql Server authentication allowed. Login names like sa,  administrator are not available.

4. SQL server server roles are available.

4. Database roles dbManager and loginmanager are available.

5. No need to add server name while login.

5. The user name should be  appended with @Server name while login. The password should be minimum 8  character length and strong i.e. numeric and special characters must.

6. Collation can be set per database.

6. Collation cannot be set per database and can set on columns and  expressions.

7. Supports full functionality of database objects.

7. Does not support all functionalities. Index properties cannot be  changed and cannot be created on specified filegroup. DDL triggers are not  supported.

8. We can refer cross databases.

8. Other database reference is not allowed.

9. Data compression is allowed.

9. Data compression is not allowed.

10. Indexes on table are not mandatory.

10. Clustered index is compulsory. Even we create primary key on  nonclustered index of a table, clustered index in mandatory. Temp table  (#temp) can be created on heap.

11. Service broker, Encryption, SQL Agent, Linked Servers are  allowed.

11. Service broker, Encryption, SQL Agent, Linked Servers, full text  search and xml indexes are not allowed.

12. Backup, restore, Replication, Mirroring, Log shipping are  supported.

12. Backup, restore, Replication, Mirroring, Log shipping are not  supported. Index re-org is not allowed.

13. SQL Profiler is available.

13. SQL Profiler is not available.

14. FileStream, Distributed transaction, Global temp table Resource  governor are supported.

14. FileStream, Distributed transaction, Global temp table, Resource governor,  Linked server are not supported. Data and log file access is not there.

15. SSIS is available.

15. SSIS tool is not available. We can use SQL Azure database as  destination in on-premise SSIS packages.

16. SELECT INTO syntax of data load supported.

16. SELECT INTO syntax of data load not supported.

17. Query Hints are supported.

17. Query Hints should be used with key word WITH. It supports Query,  Table and Join hints. PAGELOCK is not supported and it doesn’t support query  parallelism (MAXDOP).

18. Read committed Isolation is the default one.

18. Read committed snapshot isolation is the default one.