Tag Archives: Microsoft Cloud

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 .

Setup development environment for Windows Azure

Windows Azure Setup

Windows Azure Setup

Setup development environment for Windows Azure

After explaining what is cloud computing? and essential part of Windows Azure in last few days, many people asked me how they can setup development environment for Windows Azure and how they can test or deploy the application they have created. I got overwhelming response, in form of messages, from the Facebook page of “Extreme-Advice” after keeping above two cloud articles there.

Well, there are many different tools and kits provided by Microsoft to setup your development environment for Windows Azure. Some of the tools are coming in one bundle at the same time you can install it individually as per your requirement. Most of the tools given here, would comes under one bundle itself, now a days , even I have mentioned few of the important part separately just to demonstrate which exact tools comes in use for which purpose.

Here, I will be providing with few of the mandatory tools which you will need to develop Windows Azure application.

1.) Microsoft Visual Studio 2010 SP1 or greater: Microsoft announced Windows Azure in Microsoft Developer Conference 2008 (PDC-2008). Microsoft Visual Studio 2008 Professional or greater version can be used to develop your Windows Azure application. Right now Visual Studio 2012 is already available but if you can’t make it, I highly recommend at least grab Microsoft Visual Studio 2010.

2.) Microsoft SQL Server 2008 or greater: In Microsoft cloud environment, you have to use “SQL Azure”. You can access SQL Azure database either from your online azure account or from SQL Server Management Studio installed in your computer.

3.) Windows Azure Storage Emulator: The Windows Azure storage emulator provides local instances of the Blob, Queue, and Table services that are available in the Windows Azure. If you are developing an application that uses storage services, you can test it on your local computer by using the storage emulator. download “WindowsAzureEmulator

4.) Windows Azure Library for .NET: This is a bundle of useful class library which can be used to develop application for windows azure cloud. download “Windows Azure Library

5.) Windows Azure SDK: If you install full SDK either for Visual Studio 2010 or for Visual Studio 2012, you don’t need to install step 3 and step 4 manually. This SDK is a bundle of all required tools that you need for setting up Windows Azure Environment for .NET or JAVA or Python or php or Node.js. download “Windows Azure SDK“. You have a choice here based on your Visual Studio Version (Visual Studio 2010 or Visual Studio 2012).

6.) Windows Azure Account : Microsoft Provides Free trial for 90 days for Windows Azure account. You can host 1 relational database, 10 websites and unlimited inbound bandwidth with this account for 90 days. You need a credit card to setup this account to prove your identity, though it is free. If you are interested, register here.

7.)  Office 365 : Office 365 is a Microsoft’s way to provide Share Point, Ms-Office, Exchange Server & Lync Server combo as SaaS. You can get more details about Office 365 from here. You can get 30 days free trial of Office 365, if interested then register here. BTW, credit card is not needed for registering for free preview.

Enjoy 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 but examples and explanations