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