Category Archives: Windows Azure

How many SQL Servers you run within your environment ? – MAP could be your best bet to answer this question !

Posted on by
Reply

A blog post about Microsoft Assessment Planning Toolkit was in my list for quite a while and today I decided to write about it.

Let’s assume that you are working for a pretty large organization and there are a lot of database servers running within the environment.

One fine day companies CTO or even the DBA manager walks to desk and taps your shoulder to ask this question -

“How many SQL Servers are we running here?”

In case you are maintaining a proper repository after building each and every server,then the answer could be given fast.

If you dont have any repository and you don’t maintain a registered server list,then the question can be as good as a direct punch !

A smart DBA is one who has the right set of tools to get the right information at the right time.

We have a great tool called Microsoft Assessment Planning Tool Kit which does a great job to provide you sufficient information to answer the above question and much more. There are lot many features available this tool kit and we will try to cover few of them.

A very recent copy of MAP was published last month and currently the version is 7.0.The tool kit can be downloaded from here.

Normally I prefer to run this tool on my laptop or work machine to pull information/reports and I never do this install for a server.

In case you are running the old Windows XP OS within your laptop or work machine, then there is bad news. The latest version will work only for the below mentioned OS -

  • Windows 7 with Service Pack 1 (Professional, Enterprise, and Ultimate editions only)
  • Windows Vista with Service Pack 2 (Business, Enterprise, and Ultimate editions only)
  • Windows Server 2008 R2 with Service Pack 1
  • Windows Server 2008 with Service Pack 2

Now lets do MAP install and see how cool this tool is all about.

Once the setup is downloaded we can run the same to start the whole install process. The setup will do some initial checks like disk free space availability etc and will give you this screen.

The summary clearly states that the setup will install SQL Express LocalDB too along with the tool. Later on we will see why SQL express is required for the tool.

The install is pretty fast and once the setup is completed first launch screen looks like below

The initial setup screen will prompt us to create a database.

In this walk through we will name the database as ServerInventory. Clicking on OK will create the database for us.

Once the database is created, the initial screen will show us the details of steps to be completed.

We will now do Perform Inventory step. Clicking on GO will lead us to the below screen

This window will confirm how powerful this tool is. You can collect inventory of multiple application servers and even Oracle/MySQL and Sybase.

We are interested in SQL Server and will select the option SQL Server with Database details. Clicking on next will lead us to the below screen which will ask us which method we need to use to discover the servers. For this demo we will choose a single server, hence will select the option as below

The next 2 screens will deal with adding a computer name and the credentials. Final action will be to hit Finish and the assessment will begin

Once the assessment is completed, we can close this window and can start exploring the reports under Scenario Status section.

We can click on the view option of SQL Server Discovery report to get a very neat formatted report of instances available for the particular server.

In case you are interested only in total Database Engine instances, then the section to look at is Microsoft SQL Server Database Engine Editions section.That will give you an accurate count.

Another report which looked really cool to me is the Software usage tracking report.

Another report which excites me a lot is the Virtual Machine Discover Results report -

Conclusion

MAP tool has great potential and it allows you to make crucial business decisions during consolidation efforts.

Thanks for reading.

SQLSailor is exploring(Part6) – Understanding firewall concepts for SQLDatabase on Windows Azure

Posted on by
Reply

Security is one of the most discussed topics for any cloud deployment plans and no organisation would like to compromise on security when they make that ‘final’ decision to move their database to the cloud.

In this blog post we will review how protected are our SQLDatabases within Windows Azure using firewalls.

I encountered couple of firewall related issues when I first started working with Windows Azure, and that motivated me to write this blog post.

One of the error message or a warning was

The error message was pretty much saying that the IP address (Hidden in the picture) is not configured to manage the SQLDatabase within the server.

Before we see how we can configure the IP address to grant access, we will need to review what levels of firewall rules can be set on Windows Azure.

We can set firewall rules at Server Level and Database Level

Server level rule simply means that a client within a particular IP or within a range can access all your databases within the same SQLDatabase server.

In case you would like to restrict a client with a specific IP or a range of IPs to access only specific databases within the SQLDatabase server, then the role of Database Level firewall  rules pitch in.

SQLDatabase firewall is considered very important within Windows Azure architecture. Connection attempts from the Internet and Windows Azure must first pass through the SQL Database firewall before they can reach your SQL Database server or database.

Let’s consider a simple example to learn this in a better way -

When computer A tries to connect SQLDatabase Server from web, then the originating IP address is first checked by the SQLDatabase firewall against the server level firewall and grants access if the IP is within server level range. It will check the database level firewall rule in case the IP is not found within server level firewall range and connection is granted for the specific database in case the IP falls in database level rule.

Setting up server level rules

The easiest method to create a Server level firewall is via the Windows Azure portal itself.

We will need to select the server and choose the Config option to set a range of IPs or a single IP.

We have the option to check what all IPs or IP ranges are configured at server level using view sys.firewall_rules after connecting to master database.

Setting up database level rules

The recommended approach to create a database level firewall is to use the stored procedure sp_set_database_firewall_rule

We will need to connect to the exact database for which we need to create the rule and run the stored procedure with the parameters.

[The IP mentioned above is just a test case one]

The database level IPs or range can be checked using another view sys.database_firewall_rules and we will need to connect to the database and run this view to get the accurate details.

A request with IP 10.0.0.5 will be able to access only the database for which the rule was set and not all the databases within the SQLDatabase server.

Conclusion

There is good amount of protection available on Windows Azure platform for protecting your mission critical SQLDatabases and in this post we had a quick overview of firewall level protection.

You can read more about the comprehensive security concepts for a SQLDatabase on Windows Azure here -

Windows Azure SQL Database Connection Security

Keep checking this space for more and there are lots of new and exciting stuff coming up !

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

SQLSailor is exploring(Part4) – Basic DML,DDL operations with SQL Databases on Windows Azure

SQLSailor is exploring(Part5) – SQLDatabase Dashboard on Windows Azure

Thanks for reading.

SQLSailor is exploring(Part5) – SQLDatabase Dashboard on Windows Azure

Posted on by
3

Well, that is one impressive dashboard of Audi RS4! It gives huge amount of flexibility for the driver to control the transmission with lot of ease, and get quick view of crucial information.

Windows Azure portal does provide a dashboard for SQLDatabase too!

It will give information regarding space usage, available space, status of the database, server under which the database lives, collation, edition, location etc.

One interesting option I noticed under the dashboard is Show Connection strings.

It gives you the connections strings for ADO.NET, JDBC, PHP and ODBC.

There is a dashboard view for the Server too

Next time when someone asks you a question like “How many databases can be created on each SQL Database server?”

You can answer them by saying “You can create up to 149 databases in each SQL Database server!”

Each server supports 150 databases and one among them is Master and 149 are user databases.

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

SQLSailor is exploring(Part4) – Basic DML,DDL operations with SQL Databases on Windows Azure

Conclusion

Dashboard gives you a quick overview of database as well as a server on windows azure and I hope over long run Microsoft will provide more and more features and flexibility for dashboard views.

Thanks for reading.

SQLSailor is exploring(Part4) – Basic DML,DDL operations with SQL Databases on Windows Azure

Posted on by
2

In this post we will review some basic DML,DDL capabilities of SQL Database on windows Azure.

This post is in continuation with my earlier 3 posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

For the purpose of this demo we will use a database named SQLDatabase and Table dbo.Employee.

There is already some data which is entered for this table. Using Desgin view and under Data column we can see these details.

The best part of the portal I liked is it gives a clear tree like structure of my activities and the object hierarchy is also shown pretty neat.

We can write a simple SELECT statement using the New Query option to retrieve the data.

If you run with Actual Execution plan, then the query plan details are also made available.

There are 3 options to view the Query plans and they are -

Graph, Grid and Tree

Now we will quickly do a simple DELETE statement for one of the rows. Once delete is completed we can go back to Table view under My Work and see if changes are reflected (Refresh is required).

It’s pretty easy to create your own scripts and Open them in the design view of the portal and create your objects and relations.

Conclusion

We quickly looked at simple DML and DDL operations within a SQLDatabase and for a complete list of supported TSQL Statements for Windows Azure SQL Database you can bookmark the below link.

Supported Transact-SQL Statements (Windows Azure SQL Database)

There is more exciting stuff lined up, so keep checking this space !

Thanks for reading.

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

Posted on by
3

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

In this post we will check out how to design a SQL Database which is on Windows Azure.

Once the database is created we have the Manage button to start working on administration and design of the database (As mentioned in the earlier posts)

Clicking on Manage button will route us to another page where in we will have to enter the credentials(Set during database creation)

Once the credentials are validated we will have a page where in we can choose the design option.

Let’s start building a table for the database MySQLDatabase which we have already created earlier.

We have the option called New table which will help us to start designing the tables and the columns.

We can specify the table name, start building the columns, set Primary key, set Default values, specify Is Identity, Is Required etc.

Once the required details have been filled in we have the option to Save the structure.

We have the option to create and manage the indexes too. The Indexes and Keys section will display the existing indexes and we have the option to create new ones too.

Creating an index is also pretty easy and we have some advanced options like Auto Update Stats and Rebuild Index Online.

Once the database and table is ready, we will need some data to do some DML operation. We can insert some data via the Data section.

The portal is pretty smart as it does validation the same time you are entering the data.

Conclusion


Designing the tables,indexes,relations etc are really easy via the portal and we saw some real time validations too.

I will write about some DML operations during the coming days and there some exciting stuff coming up too.Please stay tuned.

Thanks for reading.

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

Posted on by
4

Last week I had wrote about the all new windows Azure portal and started off creating the first SQL Database on Windows Azure. Please read that here.

Today we will see how we can manage the database which was created on Azure via the portal.

Managing SQL Databases

The windows azure portal gives you the flexibility to manage your SQL Databases. Once the database is created its available under the SQL Databases section and upon selecting the respective database on the right hand pane, Manage button will be visible.

The Manage option will route you to a page which will ask for your credentials to access the database. The same credentials were mentioned when we created the database initially.

After logging in, the first screen will route you directly to the administration page where you can see the summary of the SQL Database and some pretty neat Query performance results.

One of the good things I liked for the query performance result set window is you can actually filter the results using ASC or DESC order.

If you would like to further drill down to a specific query then you can do that too! That’s pretty much good and everything out of box.

Query plans! Yes, you heard that right!

Query plans are also made available for each and every query which you would like to tune.

Conclusion

The portal gives you some really good options to manage your SQL Database,and during the coming days I will write about how we can start designing the Tables and other key objects with a SQL Database.

Thanks for reading and keep checking this space for more.

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

Posted on by
7

The all new Windows Azure platform was announced yesterday by Scott Gu(CVP,Windows Azure Application Platform) and an online event was hosted here at http://www.meetwindowsazure.com/

You can watch the recorded keynote on demand here at http://www.meetwindowsazure.com/Conversations

There were lot of new features announced yesterday, and the whole event was packed with heavy and exciting demos.

You can refer the blog post written by ScottGu to get a feel of what Windows Azure is going to offer during the coming days.

Another good blog post written by Bob Kelly explains the new services and changes for the existing services can be read here

The new portal looks really cool. It’s all HTML 5 and metro style enabled. Let’s see what Paras Doshi(B/T) has to say about the portal here.

I decided to start playing with the portal to understand more about the offerings, and as always my first attention was towards SQL Database.

Windows Azure SQL Database is a relational database service offered on the Windows Azure platform.Its based on SQL Server 2012.

Here is my experience creating the first ever SQL Database on Azure -

Portal View

The interface is really neat with metro style layouts.

SQL Database Creation(Initial Steps)

There is a wizard to guide you to start creating the database, and there are two options Quick Create and Custom Create

I decided to go with Custom Create as that option will give me better control on what I am doing.

The wizard comes again with cool interface asking me to enter the Name, Edition, Max Size, Collation, Server details.

There are 2 edition selection options  WEB and BUSINESS.Web edition will allow max size of the database as 5 GB and Business edition allows max size of the database as 150 GB.

I am using the evaluation subscription, so I decided to choose the web edition with 1GB limit.

I also entered the New SQL Database Server option as this was the first time I am creating a database, and I had setup no server for this subscription.

The next option was to add a Login (Only SQL login option is available), password, confirmation and region where you want the server to be deployed.

Confirmation for SQL Database Availability

In a matter of seconds (3 or 5 !) the database was up and running on cloud.

Conclusion


The new Windows Azure platform is really powerful, flexible and friendly and over the next few days I will write more about my experience working with SQL Databases and much more.

Keep checking this space for more !

Thanks for reading.