Thursday, 28 May 2015

Microsoft Azure SQL Databases – Power and Performance

By David Postlethwaite
On the Database Scale page we can decide on the performance level that we want our database to have.
There are three performance levels Basic, Standard and Premium
  • Basic is best suited for a small size database, supporting one single operation at a time such as for development or testing, or small scale infrequently used applications.
  • Standard is the best option for most cloud applications, supporting multiple concurrent queries such as workgroup or web applications.
  • Premium is designed for high transactional volume, supporting a large number of concurrent users and requiring the highest level of business continuity such as mission critical applications.

There used to be two types called Web and Business they have now been retired but you’ll still find them mentioned in the documentation and it is still possible through SSMS to create a web level database. (Upgrade SSMS 2014 to CU5 to get the latest) They are due to be removed in September 2015.
Unlike your traditional on premises database server you cannot choose the number of CPUs, amount of memory or disc configuration with SQL Azure DB.
Each Service tier has different Power levels which are measured in Database Throughput Units or DTUs. A DTU represents the power of the database engine as a mix of CPU, memory and read and write rates.
Micrsoft Azure SQL Databases - Service Tiers
The table below show you the power levels available for each tier. Expect these to increase over the coming months and years.
The idea of a DTU is to show relative power that there is between the different levels So S1 will give you 4 times the power of Basic and P1 is 10 times more powerful than S0.
To give you some idea of what you might expect from each level Microsoft have published a benchmark transaction rate.
Bennch Mark Transaction Rate
You decide on the power level you think you require and then monitor and tune the database as required. It is very easy to change from one tier to another if you decide you need more or less power but changing the level will cause the database to stop and restart. Remember, each server can have a maximum of 150 databases and a maximum of 2000 DTUs

Visit our SQL Server and Azure Consulting page

Tuesday, 26 May 2015

Microsoft Azure SQL Database – Dashboard

By David Postlethwaite

Once you have created your database you can now start to manage and configure it.

There are six menus for a SQL Azure Database: -

  • Dashboard
  • Monitor
  • Scale
  • Configure
  • GEO-Replication
  • Auditing and Security
The Dashboard

Microsoft Azure SQL Database - Dashboard

The Dashboard gives an overview of the state of your Azure database. The most useful section is down the right hand side under “Quick Glance” where you can find your connections strings, manage the IP security, server name, status, collation and edition.


Microsoft Azure SQL Databases – Monitoring


The Database monitor screen allows us to see the performance of your database. There are various indicators that you can manage. By default there are four metrics:- deadlocks, failed and successful connections and storage size but  we can add more metrics from the Add Metrics button on  the grey bar at the bottom.

Microsoft Azure SQL Database Metrics

We can also add alerts to warn us when these metrics go over a certain threshold


Thursday, 21 May 2015

Creating an Azure SQL Database

By David Postlethwaite

Once you have logged in to the Azure Management Portal you can start to  create an Azure SQL Database.
Azure SQL Database is an example of Platform as a Service (PaaS), we don’t have to build a Windows server or install the SQL software or configure the SQL instance. We don’t have to worry about software upgrades or the disc configuration Microsoft have done all of that for us. Azure simply provides us with a platform where we can place our data.Once we have created a database we can connect to it from our pc using SQL Server Management Studio or connect our applications to the database by using ADO or ODBC.

From the management console click on the database icon image   on the left hand side of the screen then select +New from the grey section at the bottom.


We have the option of quick create, custom create or import.
Quick Create just asks for a database name and a server put it on and it then creates a standard database. You can then configure the size and performance level afterwards from the Management Portal.
Otherwise use Custom Create so you can also configure the size and performance level at the same time.


It usually takes less than a minute for the system to create the database for you.
The import option allows you can load a special type of backup file called a BACPAC file from Azure blob storage which we will discuss in a later article.
An Azure SQL database must be placed on an Azure SQL server. This server is simply a container to place your databases. There is very little that can be configured for a server other than its location. Importantly an Azure SQL server can host a maximum of 150 databases and has a maximum amount of power available to it (2000 DTUs which we’ll discuss in a later article)

Tuesday, 19 May 2015

Getting Started with Microsoft Azure

By David Postlethwaite

The URL for Microsoft Azure is

Make sure you have an up to date browser because parts of this web site don’t work properly with Internet Explorer 8.The web site is full of articles, blogs and videos on Azure to help you get started and also plenty of Free Trial links to encourage you to have a go. You will require a Microsoft Live account to use Microsoft Azure, if you have an MSDN subscription then you have between €45and €100 per month credit on Azure for development and testing.If you use all your credit your service will be disabled for that month (as I have found out) or you can opt to pay for the extra usage.

If you don’t have an MSDN account you can create a trial account, which does require a credit card number for ID purposes, which you can try for a month.
Microsoft says they won’t take any money at the end of the trial they will just disable your services unless you choose to start paying.
(There is a new interface at (still under preview as of April 2015) but personally I don’t like it)

Once you have an account you can login by clicking on “My Account” at the top of the screen
Then click on the “Management Portal” image. When you have logged in you will be presented with the main Azure screen - the Windows Azure Management Portal

The management portal is where you manage and create the different Azure services.
On the left is a list of the different services available – web site, virtual machine, SQL database, storage and many others, in the centre is the list of services that have been previously created and at the top is your credit status and a globe that allows you to change language.

One important bit that is easily missed is the grey area at the bottom. When making a change always check this area because this is where the Save button appears.

Thursday, 14 May 2015

Putting Your Head in the Clouds - Microsoft Azure

By David Postlethwaite
Microsoft Azure

Let’s take a look at the Microsoft Cloud offering. Originally called Windows Azure it was renamed in March 2014 to Microsoft Azure to reflect Microsoft now offering more than just Windows. At the time of writing Azure is available in 141 countries, including China, and supports 10 languages and 19 currencies. Microsoft has divided the world into regions.  There are currently 17 regions with more due to come online in India, Germany and South Korea soon.

image (The more observant will notice there aren’t 17 blobs on this image. Microsoft don’t show the two Chinese data centres)
There are currently two regions in Europe.
North Europe in Dublin, Ireland
West Europe in Amsterdam, The Netherlands (even though Dublin is further west than Amsterdam)
There are plans to build new data centres in Germany but no date has been published yet. Germany has much more stringent data protection laws than the rest of Europe which will make it more difficult for the NSA to get hold of data held there.
You can choose to place your cloud service in any data centre in the world (though Australia is currently limited to customers based in Australia and New Zealand) but putting your data or applications on the other side of the world may affect your customers’ performance if they are all based in Europe.
You’ll see that there are no data centres in Africa. South Africa recently was allowed to start using Azure but their nearest data centre is so far away that performance is almost unusable.
Microsoft Azure Data Centers
Microsoft has gone into the Cloud in a big way. They have spent billions of dollars on it and the data centres are huge as can be seen in this phot of the Dublin Data Centre.
The Dublin Data Centre, which was the first mega data centre built outside the USA, is now 581,000 square feet (54,000 square metres). That’s equivalent to 4 football pitches. In 2014 Microsoft announced plans to spend $1billion on a new data centre in Iowa, USA that will be more than 1.2 million square feet. (110,000 square meters)
Inside the Azure Data Centres

In a data centre the servers are housed in standard 40 foot shipping containers. Each container holds anywhere between 1,800 and 2,500 servers.
The shipping containers arrive fully built so all the onsite engineers have to do is plug them in using a  standard connector that provides power, cooling and network access. The servers remain inside their shipping containers inside the facility, where they are placed side by side and stacked two high.
The Dublin data centre has a room for over 100 containers giving a capacity of over 220,000 servers. A new container arriving at the data centre can be installed in just a few hours. That’s 2,500 servers installed and ready in less than a day.
photo credit: 4 Shipping Containers Stacked via photopin (license)
Power Usage Effectiveness PUE

One of the major benefits of this design is reduced energy consumption.  The Power Usage Effectiveness or PUE is an industry measure of the amount of energy needed to cool a computer compared to the amount of energy needed to run the computer.
PUE = Total Facility Energy/ IT Equipment Energy
The data centre for your company probably has a PUE of around 2.0. In other words it takes as much energy to cool your data centre as it does to run the computers within it. The Dublin centre has a PUE of 1.25. It achieves this by using a cooling system that uses outside air to cool the data centre and a server design that allows the servers to operate at much warmer operating temperatures than normal. (Microsoft says it runs its server rooms at temperatures of up to 35 Celsius, much warmer than most data centres, which typically run around 21 Celsius.)

Tuesday, 12 May 2015

Putting Your Head in the Clouds 3 – Cloud Service Offerings

By David Postlethwaite
Cloud Service Models
There are three main service models in the cloud. You will see these buzz words being used a lot in cloud discussions:
  • IaaS   - Infrastructure as a Service
  • PaaS  Platform as a Service
  • SaaS  Software as a Service
To get a better understanding what these mean take a look at this diagram which shows the typical layers of an IT Solution
Starting from network to application
IaaS - Infrastructure as a Service
Think of Infrastructure as a Service as Hardware to Rent. Instead of purchasing network equipment, storage space or servers you use the cloud provider’s infrastructure.
This means the cloud provider owns and maintains the housing, running and maintaining of all the hardware, not you. You will need to install the operating system and database software but you don’t have to worry about maintaining the hardware. And the cloud provider will have mechanisms in place to ensure that your hardware continues to work in the event of a failure
PaaS - Platform as a Service
Platform as a Service can be thought of as Servers to Rent. Here the cloud provider supplies you with hardware, operating system and database server. You simply place your data in the database server and connect your application to it. You do not have to worry about patching, upgrades, failover and backups. The Cloud provider will manage all that for you. With both IaaS and PaaS you have the flexibility to use more or less storage or processing power as and when you require it.
This is attractive to business which might otherwise have to maintain large amounts of storage space just for occasional peak times.
SaaS Software as a Service
With Software as a service we go whole way and simply rent applications over the Internet from the Vendor. We don’t have to worry about hardware , operating systems or software maintenance. You only have to pay for the time that you use the application on a “pay-as-you-go” basis which is why this is sometimes called “software on demand”. As well as removing the need to buy, install and manage software on your computer, it also has the advantage of the software being accessible from anywhere with an internet connection.

Thursday, 7 May 2015

Putting Your Head in the Clouds 2 –Advantages and Disadvantages of Cloud Solutions

By David Postlethwaite

If you are thinking about using Cloud Computing then it’s worth considering the pros and cons.

Advantages of the Cloud

Why would you want to use the cloud?

Quick Setup
You can provision a fully functioning database server in just a few minutes whereas on your premises it can take weeks to order hardware then build and connect a new server.

With the cloud, you have access to unlimited capacity.
You can increase the power and size of your server in times of high demand and then reduce it when demand drops.
The classic example, often quoted, is that of a ticket agency. For most of the year traffic is fairly steady but when tickets for a major artist go on sale then demand ramps up for a few days. With a cloud service, you can simply increase the power and size of your systems for those few days then when sales are complete drop the power back down. And you only pay for that extra capacity whilst you are using it.
If this was all in your own data centre you might have to buy and maintain all that extra power and storage all year round.

Cost Efficient
Aside from saving on storage and infrastructure costs, you might no longer need to update and manage software or applications, patch servers.
As we’ve already said you also only pay for what you use.

Backup and Disaster Recovery
Most cloud providers offer comprehensive backup and recovery capabilities. Backups happen automatically for you and you don’t need to check every morning that it worked. They will have huge redundancy built into their data centre and at least one other data centre in another city or even another country to ensure your system is always available.

Because it’s all on the Internet you can access it from anywhere that has  an internet connection.

Environmentally Friendly.
The cloud provider packs a lot of computing power into their data centres which are much more power efficient than your “on premises” data centre will ever be.
Their servers are utilized to the maximum whereas, if your company is anything like the norm, you have plenty of servers that are barely ticking over.
Enable IT Innovation
You have an idea and want to give it a try. Spin up a new server or database in the cloud in a few minutes, test out your idea and when you’ve finished just delete the server. You only pay for the time you had it running.
Just think how long it would take to get a server built and available in your office.

Most Cloud companies use industrial level security software and practices, which are probably more secure and better managed than you can do in your own data centre which makes it harder for hackers to get at your data. That’s harder, but not impossible.

Disadvantages of the Cloud

And why wouldn’t you want to use the cloud

Security and Privacy
Security and privacy are the two main concerns that companies always mention when talking about the cloud.
By outsourcing your infrastructure to a third party you are giving away your data that might be sensitive and confidential.
You have to rely on the provider to maintain the security of that data.
If your data was leaked or lost your company could be fined, closed down or risk bankruptcy.

Dependency is one of the major drawbacks of Cloud computing
Once you have gone with one cloud vendor it is difficult to migrate to another because of the huge data migration involved.

Loss of Control
Since the services run on a remote virtual environment, you have less control over the hardware and the software.
For instance the cloud provider may force upgrades onto you which may require a lot or rework to your application.

Reliability and Vulnerability
Since everything is now on the Internet the risk of the entire environment being vulnerable to hackers or Denial of Service attacks is always there.
Outage and downtime are two aspects that even the best Cloud provider can't completely guarantee.
Also with whole setup being Internet based if your office loses its Internet connection then you can’t work.
On the other hand, if you host everything “on premises” and your data centre loses its Internet connection then your customers may not be able to get to you to buy your products.

Government Interference
Microsoft Azure is compliant with the E.U. Data Protection laws so data in the European data centres will always stay in Europe.
However Microsoft and others have stated that under the USA Patriot Act the US government can request access to their data even if the data resides outside the USA bypassing EU law. 
Microsoft are currently fighting a case with the US authorities who are trying to get access to emails held in the Ireland data centre.

You will have to weigh up the advantages and disadvantages and decide what’s best for your company.

Wednesday, 6 May 2015

Putting Your Head in the Clouds 1 – An introduction to Cloud Computing

By David Postlethwaite
Although the concept of Cloud Computing and Microsoft Azure have been around for a number of years it is only relatively recently  that it has matured to the point where it is no longer just an interesting curiosity but a serious contender as a business solution.
Microsoft claim that 80% for fortune 500 companies are now using the cloud and that Microsoft Azure has more than a million servers with about 10 trillion objects hosted in its data centres. My company has started to look at the potential of the Cloud and I have spent quite a lot of time recently understanding and investigating Microsoft Azure as a solution for a small project.In February I presented some of what I’ve learned at SQL Saturday in Vienna which went down pretty well with those who came to listen. One attendee told me he had used my slides as the basis for recommending his company take a closer look The one thing I have found out is that it is changing almost weekly. New features are being added and current features are being improved. I found that one slide on restoring Azure SQL databases had become out of date that day. My talk will need updating before it appears at another SQL Saturday! So if your company hasn’t started looking at the potential of the Cloud then now is the time put your head in the cloud and take a look.
So to paraphrase a famous song based on Austria, let’s start at the very beginning.
What is the cloud?
Wikipedia states:
“The practice of using a network of remote servers hosted on the Internet to store, manage and process data, rather than a local server or a personal computer “ In a nutshell cloud computing is putting all or some of your data centre into a shared resource on the Internet rather than running it from your own premises.
The word “cloud” is often used simply as another name for "the Internet" so the phrase “cloud computing” really means "Internet-based computing," where different services such as servers, storage and applications are delivered to your company's computers through the Internet. The Cloud covers more than just sticking a SQL database on a shared server. The cloud, as you will see, is everywhere without you even realising it.
If you use gmail, Hotmail, Microsoft Mail, in fact any web mail where the mail server is not on your premises this could be classed as using the cloud.
We’ve been doing that for years long before the term cloud computing was invented.
Cloud Storage
Lots of us are using cloud storage such as Microsoft One Drive or Google Drive to back up our data on our hard disk to the Internet.
You can automatically sync your local data to the cloud disk so that you never lose it.
And you can access your cloud storage from other devices so you are not tied to just one pc or even one location.
There are masses of software that you can run directly on a web site without having to download and install it on your pc., for example Office 365, Skype or PhotoShop
I’m sure everyone here has used an application on the web, even if it’s just an online dictionary or language translator, a foreign exchange calculator or online banking
If the application runs on a web site rather than an application installed on your pc then it can be considered as part of the cloud.
Server Hosting
Server hosting is what we traditionally think of as cloud computing. Running databases or application servers from a data centre on the Internet
All the big players, Microsoft, Amazon and Google offer this as well has many small suppliers most offer a free trail and now is the time to take a look.

Tuesday, 5 May 2015

SQL Server 2016 Preview...Coming this Summer

It was announced yesterday at the Ignite conference that we’ll be getting a summer preview of SQL Server 2016. With it being a bank holiday in the UK … I missed it yesterday

Some the new features announced include :
Always Encrypted
Stretch Database
Real Time Operational Analytics and In Memory OLTP

You can also sign up to notified of announcements about the release

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...