SQL Azure Interview Questions  and Cloud Computing 

1. What is SQL Azure?

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 cloud computing?

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.

3.What is scale a cloud service:

A cloud service is scaled out by increasing the number of role instances (virtual machines) deployed for a role. A cloud service is scaled in by decreasing role instances. In the Preview Management Portal, you can also scale a linked SQL Database instance, by changing the SQL Database edition and the maximum database size, when you scale your service roles.

4.What is a cloud service role:

A cloud service role is comprised of application files and a configuration. A cloud service can have two types of role:

5.What happens when the SQL Azure database reaches Max Size?

Read operations continue to work but create/insert/update operations are throttled. You can drop/delete/truncate data.

6.What is the current limitation of the size of SQL Azure DB?

 Maximum size of a SQL Azure database is 50 GB.

7.How do you handle datasets larger than 50 GB?

We have to build custom solution at application level that can handle scale out of underlying SQL Azure databases.

But Microsoft has announced, SQL Azure Federations that will assist scaling out of SQL Azure databases. And scale out means that we are splitting the data into smaller subsets spread across multiple databases.

8. What is it’s similarity with SQL Server?

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.

9.How can we migrate from SQL server to SQL Azure?

For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.

10. What is it’s difference with SQL Server?

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.

11. Difference between Web and Worker Roles in Windows Azure?

The main difference between the two is that an instance of a web role runs IIS, while an instance of a worker role does not. Both are managed in the same way, however, and it's common for an application to use both.For example, a web role instance might accept requests from users, then pass them to a worker role instance for processing.

12.What is AutoScaling?

Scaling by adding additional instances is often referred to as scaling out. Windows Azure also supports scaling up by using larger role instances instead of more role instances.

By adding and removing role instances to your Windows Azure application while it is running, you can balance the performance of the application against its running costs. 

An autoscaling solution reduces the amount of manual work involved in dynamically scaling an application. 

13.What are Horizontal and Vertical Scaling?

Vertical scaling is adding oomph to the machine that an application is running on. Perhaps your e-commerce site is getting more traffic as your business grows, and it’s starting to creak at the seams. A common way to give the application a boost, is to add more RAM, more processors, more bandwidth, or more storage to your machine. Maybe you simply move your application to a new, more powerful machine.

Horizontal scaling, on the other hand, is adding more servers to your application to spread the load. The simplest case of horizontal scaling may be to move your database onto a separate machine from your web server. Now your web server can simply satisfy requests, and your database server can simply crunch data.

 14.Which tools are available to manage SQL Azure databases and servers?

We can manage SQL Azure database using SQL server management server 2008 R2. Also, we can manage SQL Azure databases and servers through a Silverlight app integrated in Azure management portal.

15. How many databases can we create in a single server?

150 databases (including master database) can be created in a single SQL Azure server.

16.How many servers can we create in a single subscription?

 We can create six servers under a single subscription.

17.What is the difference in accessing DB between SQL Server Vs SQL Azure?

You connect to directly DB in SQL Azure instead of connecting

to SQL Server as we do in SQL Server.

From application point of view, if you need to deal with many DBs, you have to write complete connection string again and again.

18. What encryption security is available in SQL Azure?

 Only SSL connections are supported. SET Encryption = TRUE

19. What is the Data Tier Application?

This is basically used for data deployment and started in 2008 R2. 

This is like a .rar file which is used to deploy the data.

20. What is the index requirement in SQL Azure?

All tables must have clustered index. You can't have a table without clustered index.

21. How do you migrate data from MSSQL server to Azure?

bcp data out to one text file then bcp data in to Azure. Also read migrate data in eleven steps @ Brute Force Migration of Existing SQL Server Databases to SQL Azure

22. Where actually SQL Azure Database is hosted?

SQL Azure Database is hosted on servers running SQL Server technologies in Microsoft Data Centers.

23. What are the four layers of abstraction Microsoft architectured to provide relational database through cloud platform?

The client layer,
The services layer,
The platform layer and
The infrastructure layer.

24. Can you describe each of them?

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.

25. How do we synchronize On Premise SQL server with SQL Azure?

We could use a No code solution called DATA SYNC (currently in community technology preview) to synchronize on-premise SQL server with SQL Azure. We can also develop custom solutions using SYNC framework.

26. What is SQL Azure Fabric?

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.

27. What is provisioning, billing and metering, and connection routing concepts in the service layer?

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.

28.what are the options to manage session state in Windows Azure?

• Windows Azure Caching
• SQL Azure
• Azure Tables

29.How many instances of a Role should be deployed to satisfy Azure SLA (service level agreement) ? And what's the benefit of Azure SLA?

TWO. 

the Role would have external connectivity at least 99.95% of the time.

30.what are REST-style architecture's?

REST-style architectures consist of clients and servers. Clients initiate requests to servers; servers process requests and return appropriate responses. Requests and responses are built around the transfer of representations of resources. A resource can be essentially any coherent and meaningful concept that may be addressed. A representation of a resource is typically a document that captures the current or intended state of a resource.

31. How to connect to SQL Azure Database by using sqlcmd?

 Following is the syntax for connecting to SQL Azure Database using sqlcmd.
C:>sqlcmd -U -P -S -d master.

32.What are the three main components of Windows Azure Platform?

1. Compute
2. Storage
3. AppFabric

33.How would you categorize Windows Azure? (IaaS/PaaS/SaaS)

PaaS (Platform as a Service)

34.What is Windows Azure compute emulator?

The compute emulator is a local emulator of Windows Azure that you can use to build and test your application before deploying it to Windows Azure.

35.What is Windows Azure Storage Emulator?

The Windows Azure storage emulator is as local emulator for Windows Azure storage services that you can use to build and test your application before you deploy it as a hosted service to the Windows Azure.

36.Apart from .Net framework, Name other three language/framework that can be used to develop Windows Azure applications?

php, node.js, java

37.What are the three types of roles in Compute component in Windows Azure?

1. WEB
2. Worker
3. VM
4. 
   

38.what is VM role in Windows Azure?

Virtual Machine (VM) roles, now in Beta, enable you to deploy a custom Windows Server 2008 R2 (Enterprise or Standard) image to Windows Azure. You can use the VM role when your application requires a large number of server OS customizations and cannot be automated.

The VM Role gives you full control over your application environment and lets you migrate existing applications to the cloud.

39.what is worker role in Windows Azure and how does it help?

Applications hosted within Worker roles can run asynchronous, long-running or perpetual tasks independent of user interaction or input.

And it helps, When you separate your application’s background processes in a Worker role and host the front-end in a Web role, you can better distribute your application logic and have more fine grain control over how your application scales.

40.what is web role in Windows Azure?

Web roles in Windows Azure are special purpose, and provide a dedicated Internet Information Services (IIS) web-server used for hosting front-end web applications. You can quickly and easily deploy web applications to Web Roles and then scale your Compute capabilities up or down to meet demand.

41.What are the differences between web role and worker role?

1. Web role is designed to cater to web requests. And worker role is designed to cater to background processing.
2. In the Web role, IIS gets started. That's Not the case with worker role.

42.What is a role instance:

A role instance is a virtual machine on which the application code and role configuration run. A role can have multiple instances, defined in the service configuration file.

43.What is a guest operating system:

The guest operating system for a cloud service is the operating system installed on the role instances (virtual machines) on which your application code runs.

44.What is a cloud service components:

Three components are required in order to deploy an application as a cloud service in Azure.

45. What is SQL Azure Data sync?

 This is to synchronize the data between local and cloud.

46. Which are the two editions in which SQL Azure database available?

 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.

47.What is the difference between web edition and business edition?

SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB.  The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB  whereas the size of a business edition can be increased in the increments of 10 GB.

48. What is function of master database?

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.

49. What is server-level principal?

 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.

50. What is sharding?

It is a technique for partitioning large data sets, which improves performance and scalability.

It also enables distributed querying of data across multiple tenants.

51.How do we Backup SQL Azure Data?

SQL Azure keeps three replicas of a database to tackle hardware level issues. To tackle user level errors, we can use COPY command that allows us to create a replica of a SQL Azure database. We can also backup SQL Azure data to local SQL server using BCP, SSIS, etc. but as of now, point in time recovery is not supported.

 52.What is code near application topology?

Code near application topology means that the SQL Azure database and the windows azure hosted service consuming the data are hosted in the same Azure datacenter.

[FYI: in the code far application topology, the app connects to SQL Azure from outside the Microsoft data center]

53.What is Federation?

The federation is where you define the data type (e.g., Customer ID, Product ID) you’ll shard on.  As with creating the root database, you can create a federation through the SQL Azure database management portal,  with SQLAzureMW or by using this T-SQL script while connected to your root  database: CREATE FEDERATION <FederationName>(<DistributionKeyName> <DistributionType> RANGE)

54. What is SQL Azure Federations?

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.

• Taking advantage of greater resources within the cloud on demand.


• Allowing customers to have their own database, to share databases or to access many databases.


• Reducing the exposure of a single point of failure.


• Benefiting from the lower costs of using cloud resources on an as-needed basis and releasing them when done.


• Reducing I/O bottlenecks and SQL Azure throttling.

 55.What is Federation Root Database?

The Federation root database is a SQL Azure database that contains metadata about the  federations. It’s the first thing you need to create when setting up your own federation example. CREATE DATABASE [fedRoot] COLLATE French_CI_AS (MAXSIZE = 100 GB, EDITION = 'business')

56.What is Federation Member?

The Federation member is the shard (i.e., the database containing a specific range of information). T-SQL script: USE FEDERATION ROOT WITH RESET  GO  ALTER FEDERATION <FederationName> SPLIT AT (<DistributionKeyName>=<splitpoint>)

57.How to scale out a federation by Sql statement?

ALTER FEDERATION <FederationName> SPLIT AT (<DistributionKeyName>=<value>)

58.How to connect Azure federated root database and apply federation in entity framework?

You have to open the connection before the USE FEDERATION command   using (DemoEntities db = new DemoEntities())  { db.Connection.Open(); string federationCmdText = @"USE FEDERATION ProdutosFed(ID = 110) WITH RESET, FILTERING=OFF"; db.ExecuteStoreCommand(federationCmdText);  }

59. What is TDS(Tabular Data Stream) Gateway?

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.

60. What do you know about SQL Azure Firewall?

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.

61.What is the difference between web edition and business edition?

SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB. The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB whereas the size of a business edition can be increased in the increments of 10 GB.

62.What were the latest updates to SQL Azure service?

Latest SQL Azure updates include multiple servers per subscription, SQL Azure co administrator support, creating Firewall rules for servers with IP detect.

63.How many replicas are maintained for each SQL Azure database?

For each database, three replicas are maintained for each database that one provisions. One of them is primary replica. All read/write happen on primary replica and other replicas are kept in sync with primary replica. If for some reason, primary goes down, other replica is promoted to primary. All this happens under the hood.

64.What is the index requirement in SQL Azure?

All tables must have clustered index. You can't have a table without clustered index.

65.What are the types of tables in SQLAzure? 

Federated Table
Reference Table
Centre Table

66.What is a fan-out query in SQLAzure?

Federations provide a model for partitioning parts of your schema over to multiple member databases for harnessing scalability of many nodes. However applications still need for querying all of the data across federation members.  Fan-out is a technique for querying data in your federation, across many federation members.  Fan-out queries are much like map/reduce in that it is formed in 2 parts; Member query is the piece that is sent over to all members involved in the query and Summary query is the query that is the post processing piece to allow condensing the results from the member query to desired final result-set.

67.How do you improve the performance of a SQL Azure Database?

We can tune a SQL Azure database using information available from execution plan and statistics of a query. We could use SQL Azure’s Dynamic Management views to monitor and manage SQL Azure database.

Also, SQL Azure performance is affected by network latency and bandwidth. Considering this, code near application topology gives the best performance.

68.Tell me something about security and SQL Azure.

SQL Azure service allows blocking a request based on its IP address through SQL Azure firewall. It uses SQL server Authentication mechanism to authenticate connections. Also connections to SQL Azure are SSL-encrypted by default.

69.What is deployment environments:

Azure offers two deployment environments for cloud services: a staging environment in which you can test your deployment before you promote it to the production environment. The two environments are distinguished only by the virtual IP addresses (VIPs) by which the cloud service is accessed. In the staging environment, the cloud service’s globally unique identifier (GUID) identifies it in URLs (GUID.cloudapp.net). In the production environment, the URL is based on the friendlier DNS prefix assigned to the cloud service (for example, myservice.cloudapp.net).

70.What is swap deployments:

To promote a deployment in the Azure staging environment to the production environment, you can “swap” the deployments by switching the VIPs by which the two deployments are accessed. After the deployment, the DNS name for the cloud service points to the deployment that had been in the staging environment.

71.What is minimal vs. verbose monitoring:

Minimal monitoring, which is configured by default for a cloud service, uses performance counters gathered from the host operating systems for role instances (virtual machines).

 Verbose monitoring gathers additional metrics based on performance data within the role instances to enable closer analysis of issues that occur during application processing. For more information

72.What is a service definition file:

The cloud service definition file (.csdef) defines the service model, including the number of roles.

73.What is a service configuration file:

The cloud service configuration file (.cscfg) provides configuration settings for the cloud service and individual roles, including the number of role instances.

74.What is a service package:

The service package (.cspkg) contains the application code and the service definition file.

75.What is a cloud service deployment:

A cloud service deployment is an instance of a cloud service deployed to the Azure staging or production environment. You can maintain deployments in both staging and production.

76.What is Azure Diagnostics:

Azure Diagnostics is the API that enables you to collect diagnostic data from applications running in Azure. Azure Diagnostics must be enabled for cloud service roles in order for verbose monitoring to be turned on. For more information,

77.What is Azure Service Level Agreement (SLA):

The Azure Compute SLA guarantees that, when you deploy two or more role instances for every role, access to your cloud service will be maintained at least 99.95 percent of the time. Also, detection and corrective action will be initiated 99.9 percent of the time when a role instance’s process is not running. 

78. What are the advantages of SQL Azure?

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.