SQL Azure Interview Questions

Oct 13, 2011 Jinal Desai @Interview, Q & A, SQL Azure 1 Comment @Interview, Q & A, SQL Azure

1. What is SQL Azure?
Ans. Microsoft SQL Azure is a cloud-based relational database service that is built on SQL Server technologies and runs in Microsoft data centers on hardware that is owned, hosted, and maintained by Microsoft.

2. What is it’s similarity with SQL Server?
Ans. SQL Azure database exposes a Tabular Data Stream(TDS) interface for Transact-SQL-based database access similar to SQL Server instance running on your premises(i.e. Server Room). Using it it is similar to access SQL Azure database in the same way you are accessing database in SQL Server.

3. What is it’s difference with SQL Server?
Ans. It differes in the administration aspect. SQL Azure database abstracts the logical administration from the physical administration. However, you can administer databases, logins, users and roles unlike an on-premise instance of SQL Server Microsoft administers the physical hardware like hard drives, servers and storage.

4. Where actually SQL Azure Database is hosted?
Ans. SQL Azure Database is hosted on servers running SQL Server technologies in Microsoft Data Centers.

5. What are the four layers of abstraction Microsoft architectured to provide relational database through cloud platform?
Ans. They are the client layer, the services layer, the platform layer and the infrastructure layer.

6. Can you describe each of them?
Ans. The Client Layer: The client layer is closest to the application. It is used by the application to communicate directly with SQL Azure. There are two options for the client layer. It can reside on our datacenter or to be hosted in Windows Azure.

The Services Layer: It functions as a gateway between the client layer and the platform layer(where the data resides). It provides functions like provisioning, billing and metering, and connection routing.

The Platform Layer: It includes the physical servers and services that supports the services layer. It consists of many instances of SQL Server, each is managed by SQL Azure Fabric.

The Infrastructure Layer: It is the IT administration of physical hardware and operating system which supports the service layer.

7. What is SQL Azure Fabric?
Ans. It is a distributed computing system. It comprises of tightly integrated networks, servers and storage. It enables automatic failover, load balancing, and automatic replication between physical servers.

8. What is provisioning, billing and metering, and connection routing concepts in the service layer?
Ans. Provisioning: It is one of the three functions that service layer provides. The service layer provisions(provides/supplies) the database that we specify with our windows azure platform account.

Billing and Metering: It is one of the aspect of services layer which enables multi-tenant support by providing monitoring and billing for database usage based on individual Windows Azure platform accounts.

Connection Routing: SQL Azure is built on a scalable platform involving numerous physical servers. The service layer handles the connection routing between our application and the physical servers where actual data resides.

9. How to connect to SQL Azure Database by using sqlcmd?
Ans. Following is the syntax for connecting to SQL Azure Database using sqlcmd.
C:\>sqlcmd -U -P -S -d master

10. What are the advantages of SQL Azure?
Ans. The advantages of SQL Azure includes manageability, high availability, scalability, a familiar development model, and a relational data model.

Manageability: SQL Azure Database offers the scale and functionality of an enterprise data center without the administrative overheads that are associated with on-premise instances of SQL Server.

High Availability: SQL Azure Database 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 Database provides automatic failover to optimize availability for your application.

Scalability: A key advantage of SQL Azure Database is the ease with which you can scale your solution. 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 that use the tabular data stream (TDS) protocol to communicate between client and server. SQL Azure Database 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 Database.

Relational Data Model: SQL Azure Database will seem very familiar to developers and administrators because data is stored in SQL Azure Database just like it is stored in SQL Server, by 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 logical 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 Database.

11. Which are the two editions in which SQL Azure database available?
Ans. SQL Azure databases are available in two editions: the Web Edition Database and the Business Edition Database.

The Web Edition Database is best suited for small Web applications and workgroup or departmental applications. This edition supports a database with a maximum size of 1 or 5 GB of data.

The Business Edition Database is best suited for independent software vendors (ISVs), line-of-business (LOB) applications, and enterprise applications. This edition supports a database of up to 50 GB of data, in increments of 10 GB.

12. What is function of master database?
Ans. It keeps track of which logins have permission to create, alter or drop database or other logins. It also provides access to the firewall rules and SQL Azure usage metrics that we can view.

13. What is server-level principal?
Ans. It is an account same as “sa” login in SQL Server. In provisioning process, SQL Azure creates login for us which is server-level principal for our SQL Azure server. It has permission to manage all server-level and database-level security.

14. What is sharding?
Ans. It is a technique for partitioning large data sets, which improves performance and scalability. It also enables distributed querying of data across multiple tenants.

15. What is SQL Azure Federations?
Ans. It provides the ability to scale database tier of applicatoin. Federation represent a dataset that is spread over many nodes and manage connection routing and online repartitioning to help scale database tier on demand.

16. What is TDS(Tabular Data Stream) Gateway?
Ans. It is a gateway between our application and underlying platform, where actual data resides. It performs the functions of data center isolation, provisioning, billing and metering, and connection routing.

17. What do you know about SQL Azure Firewall?
Ans. Since SQL Azure provides a relational database services for Windows Azure and other Internet-based applications, to protect our data SQL Azure Firewall prevents access to our SQL Azure Server until we specify which computers have permission. It grants access by originating IP address of each request.