Top 29 Sql Azure Interview Questions You Must Prepare 06.Jul.2022

SQL Azure federation provides tools that allow developers to scale out (by sharding) in SQL Azure.

Here are some of the benefits of a sharded database: 

  • 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.

SQL Azure firewall is a security mechanism that blocks requests based on its IP address.

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

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

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 <Federation Name> SPLIT AT (<Distribution KeyName>=<script point>)

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.

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)

In this example, 

<FederationName> is the name of the federation (not the name of the physical database, which is a System-GUID). 

<DistributionKeyName> is the name for the distribution key, 

and <DistributionType> is the distribution data type that data will be sharded on. 

The valid distribution data types are int, bigint, uniqueidentifier and varbinary (up to 900).

3 replicas are maintained for each logical DB. 

Single primary is observed as the replica where actual read/write take place. Once this goes down, another replica is upgraded automatically as a single primary.

SQL Azure is a cloud based service and so it has own set of pros and cons when compared to SQL server. SQL Azure service benefits includes on demand provisioning, high availability, reduced management overhead and scalability. But SQL Azure abstracts some details from the subscriber which can be good or bad which depends on the context of the need.

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

As of now, we can create six servers under a single subscription.

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.

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

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 

where as the size of a business edition can be increased in the increments of 10 GB.

As of now, 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.

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')

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

Maximum size of a SQL Azure database is 50 GB.

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.

When database reaches its maximum size update /insert /create operations get throttled. Also there are policies in place that does not allow to a workload to exploit a shared physical server. In other words, the policies make sure that all workload get a fair share of shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also got hard throttled which means that a SQL Azure machine is out of resources and it does not accept new connections. We can know more about what happened by decoding reason codes.

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.

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

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

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.

Charges for SQL Azure consumption is based on

  1. Size
  2. Data Transfer.

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.

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

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.

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

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.