Category Archives: SQL Azure

Monitoring SQL Azure Database & Server

Monitoring SQL Azure Database & Server

By far I have written quite a few articles to monitor and manage SQL Server (find it here under “DBA Related Articles” section) but what about SQL Azure?

Note: if you are new to Cloud and SQL Azure, do look at “Microsoft Windows / SQL Azure:” section to get understanding from scratch.

Microsoft has very good concept of system views to provide the very important and crucial information to the admin. SQL Azure is not an exception. Herewith, I am going to explain some of common and very powerful system views which cater you with so many important information you required to accomplish your admin task in SQL Azure.

sys.dm_exec_connections : This view will return a row per each connection made toward SQL Azure database. Some of the important fields are session_id, connect_time, num_read, num_write, last_read, last_write and many more.

sys.dm_exec_sessions: This DMV will return all the active connection no matter whether it is user connection or system’s connection for internal task. Important fields from this DMV are session_id, login_time,  host_name, program_name, login_name

sys.dm_exec_requests: This system view will cater your need to know each request comes to your database. Some of the important fields are session_id, start_time, status, command, database_id, user_id, blocking_session_id, wait_type, percent_complete.

sys.dm_tran_database_transactions: This DMV will return database level transaction; here is the list of important columns from this DMV: transaction_id, database_id, database_transaction_begin_time, database_transaction_type, database_transaction_state.

sys.dm_db_partition_stats: This DMV returns page & row-count information for each partition in current database. Some of the important fields from this DMV are partition_id, object_id, used_page_count, reserve_page_count, row_count to name a few.

I will have detail follow up article which will return meaningful information by joining few of the DMVs together till then have fun !!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Error Fix: The database YourDatabaseName is not accessible while connect SQL Azure database from SQL Server Management Studio

Error connecting SSMS to SQL Azure

We have already seen that how to connect SQL Azure database from SQL Server Management Studio (SSMS) installed in your computer (for details, click here). I have received one email in which, one of the reader was asking that he is getting error something like this:

The database YourDatabaseName is not accessible

He can able to connect SSMS to SQL Azure server and also access “Master” database too but he can’t access his own database he created from online SQL Azure portal.

See the screen capture of error :

IMAGE 1:

Well, You can connect to your own database from SSMS if it is SQL Server 2008 R2 or greater. Older SSMS will not be able to connect to your user database in SQL Azure.

Enjoy Microsoft Cloud!!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Error Fix: Cannot connect to YourServerName.database.windows.net. Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)

Error Fix: Cannot connect to YourServerName.database.windows.net. Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)

I have already explained how to create database/server in SQL Azure at “Create database & Server in SQL Azure”.  You can manage your database and server from Azure online portal itself. Not only this but you can even create/drop any object with DML operation too, from online portal but what, if you are so used to with SQL Server Management Studio? Yes, there is a way to connect your Azure database from your local SSMS (SQL Server Management Studio).

Before you start connecting your SQL Server Management Studio to SQL Azure, please refer “Create database & Server in SQL Azure” if you don’t know how to get fully qualified SQL Azure server name, database name and password. If you already have these three, do proceed towards your SSMS.

1.)    Open your SSMS (SQL Server Management Studio) install in your computer

2.)    Select “Database Engine”  in “Server Type” dropdown.

3.)    Provide fully qualified server name in “Server Name” text box

4.)    “Authentication” mode should be “SQL Server Authentication”

5.)    Insert your login id / password and click on “Connect” button.

As soon as you follow the steps given above and click on “Connect” button, you will be greeted with an error something like this:

Cannot connect to YourServerName.database.windows.net. Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)

Here is the screen capture of the error:

IMAGE 1:

When you open SSMS and the login prompt you get is representing the “Object Explorer” and at the moment SQL Azure can’t be connected from “Object Explorer” but don’t worry, there is a workaround to this issue.

Close that login prompt and click on “New Query” from the standard tool bar, right below the “Edit Menu”. You will again get login prompt, insert proper credentials in the same way I saw in above steps 1 to 5 and you will be able to connect to your SQL Azure server.

Enjoy Microsoft Cloud!!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Create Database and Server in SQL Azure

Create Database and Server in SQL Azure :

Note: I have started writing articles on Microsoft Cloud technology (a.k.a Windows Cloud) recently. If you are new to Microsoft Cloud Technology and wanted to learn it from scratch, have a look at following three articles first.

  • What is Cloud Computing? (Click Here)
  • Understanding Microsoft Cloud and its essential part (Click Here)
  • Setup development environment for Windows Azure (Click Here)

Actually I have received so many requests, through different media, to give detail knowledge of SQL Azure but before I shall start demonstration of SQL Azure, it was mandatory to make everybody understand about, what Cloud is? And what is the action of Microsoft on Cloud? Above three articles was for the purpose of giving cloud details only. Now, I am going to actually start SQL Azure introduction.

SQL Azure is the relational database, built on SQL Server technology, hosted on Windows Azure Platform. SQL Azure is designed to handle heavy workloads by load balancing, replication, failover clustering, and scaling out, which are all automatically managed at the Microsoft’s data center.

If you are ready with WindowsAzure account (If you don’t have, get one for free, details given here), let us create one Server and database to start work with relational database technology as known as SQL Azure.

1.)    Once you are ready with your WindowsAzure account, login to https://manage.windowsazure.com/

2.)    Once you login to above given URL, click on “SQL Databases” -> Servers -> ADD as marked with RED rectangle in following screen capture.

IMAGE 1:

3.)    Once you click on “Add” button, it will open popup window to get details about New server you wanted to create. Give login Name (this will work as SA in SQL Server), Password and region where you wanted to create server. Choose nearest location to your area, I have selected “Southeast Asia”. Further details, refer screen capture given below:

IMAGE 2:

4.)    Once you are done with creation of Server, click on “Databases” link right before “Servers” link and click on “Add” button.

5.)    You have to fill database detail in popup windows. Give database name in “Name” text box, select the Edition “Web” or “Business”. Web will have 1GB db limit and Business will have 5GB db limit in free account of Windows Azure. Select the Collation you want and the Server we have created in step 3 above. Refer following screen shot for detail:

IMAGE 3:

6.)    We have just created “ExtremeTest” database in step # 5 above, you will get database name along with few other details in one row, click on database name which will open screen same as given below. You can find you connection string (to use in web application) from there and manage URL to manage “ExtremeTest” database.

IMAGE 4:

7.)    Click on “Manage URL” which will open web SQL Azure management studio ( must have Silverlight in your web browser). Provide your username/password you have created in Step # 3 and you will be redirected to web SQL Azure Management studio. (You must have your IP in “Allowed IP Addresses” list for the server you are trying to connect).

8.)    As soon as you login to your server for managing database, first thing you will look for is to create table/view/stored procedure. So click on “Design” button at left-bottom corner (Marked with red rectangle in following screen capture) and you will get option to make it from web UI or from query by “New Query” button.

IMAGE 5:

Enjoy Microsoft Cloud!!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Understanding Microsoft Cloud and its essential part

Understanding Microsoft Cloud and its essential part

After giving brief introduction of Cloud few days back I thought to give some light on more details of Microsoft Windows Azure.  Microsoft Windows Cloud is created by so many different services and out of which, following are the some of the important services which makes Windows Cloud more fascinating.

Microsoft Windows Cloud

Picture worth 1000 words so after looking at image given above, you must have an idea about the essential part of Cloud offering by Microsoft. Let us have a look at each in brief here.

Windows Azure :

In simple word, Windows Azure is the operating system provided by Microsoft for Cloud computing. Windows Azure is first reveled in Microsoft Developer Conference 2008 (PDC-2008). By the help of Windows Azure, Microsoft offers  service hosting, service management for Azure Platform. Windows Azure provides you on-demand computing and storage to host, scale, and manage web applications and services on the internet in Microsoft data centers.  Windows Azure is made up of following essential parts.

  • Access:
    1. Web Role: “Web Role” is one of the exciting feature of Windows Azure. It is mainly used to host the website in Window Azure. Generally after creating and deploying web application, developer always wants ability to scale out application horizontally by adding more power in application by hosting it in few different IIS and get request by load balancer and scale up vertically by adding more resources to the existing VM/Instance like CPU, memory etc. Web Role is useful for this task
    2. Worker Role: Web Role is generally used to host the web application and make it available for the user, it shouldn’t be use for long running task like some heavy aggregation, heavy report creating and many more. to decouple front-end from web role, worker role is introduced to perform heavy wait task. You can consider it as Windows Service or a Unix Daemon.
    3. VM Role: VM Role is recent advancement in the Windows Azure Platform which enables you to run a virtual hard disk (VHD) image of Windows Server on the Windows Azure Platform. You can create a VHD file
      on premises and then upload it to the Windows Azure Platform. The VM Role is unique in a sense
      that it allows you to perform a great number of customizations at the operating system level.
  • Storage:
    1. Table: Table Storage object serves as a regular table of SQL Server the only thing is, it won’t allow you to established relationship between two tables and hence it is known as NoSQL.
    2. Blob: Binary Large OBject (BLOB) is used to serve the purpose of storing image, files, audio, video and any unstructured data. This data can be accessed by HTTP or HTTPS from anywhere in the world.
    3. Queue: Windows Azure Queue storage is a service for storing large numbers of messages that can be accessed from anywhere in the world via authenticated calls using HTTP or HTTPS. A single queue message can be up to 64KB in size.

SQL Azure:

SQL Azure is a cloud based relational database management system. It provides a highly available, scalable, multi-tenant database service.

  • Data Synchronization: Microsoft SQL Data Sync is built upon the Microsoft Sync Framework. SQL Data Sync enables you to easily create and schedule bi-directional synchronizations from within the SQL Data Sync web site without the need to write a single line of code. SQL Data Sync supports synchronizations between multiple Windows Azure SQL databases and between SQL Server and SQL databases so you can create custom synchronization groups that fit your business requirements.

App Fabric:

App Fabric is any n-tier .NET application that spans the web, middle, and data tiers, composes with external services, and is inherently written to the cloud architecture for scale and availability

  • Access Control: Access Control is great step take in Windows Azure which separate identity and access control from the application development process.Access control is provided as a feature of Windows Azure and it takes care of integrating your application with commonly used identity providers like Active Directory, Windows Live, Google, Yahoo, Facebook and Twitter.
  • Service Bus: Service Bus is messaging infrastructure that sits between applications allowing them to exchange messages in a loosely coupled way for improved scale. The Service Bus provides both “relayed” and “brokered” messaging capabilities. In the relayed messaging pattern, the relay service supports direct one-way messaging, request/response messaging, and peer-to-peer messaging. Brokered messaging provides durable, asynchronous messaging components such as Queues, Topics, and Subscriptions, with features that support publish-subscribe and temporal decoupling: senders and receivers do not have to be online at the same time; the messaging infrastructure reliably stores messages until the receiving party is ready to receive them.
  • Caching: Caching service provided by Microsoft Azure is a distributed in-memory cache for Azure applications. This gives developers an alternative to the disk based caching. Developers can utilize the caching service to store data based on an individual user session or to store and share data across the applications.

Data Market:

  • Windows Data Market: Microsoft provides IaaS and PaaS but it is not enough. May user/business needs readily available software which they can use as a service (SaaS) with windows cloud. To cater the need, Microsoft has launched “Windows Azure Marketplace” which is online market place where you can sell or buy software.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations