Create SQL Server on Windows Azure Virtual Machine

In the last post we saw the two different offering of SQL server on cloud services by Microsoft, Today we will go through the Step by Step Procedure on how to create a SQL 2014 SQL server on Windows Azure, As mentioned in the last post to create any Microsoft Cloud based service you need a Azure Subscription http://azure.microsoft.com/en-us/pricing/purchase-options/ . There are various Tiers of Servers offered based on the performance and Scalability. Let’s I assume you have purchased the required subscription which should grant you access to you own customized Azure Portal page when you login to https://manage.windowsazure.com as below,

In the Portal Page select on Virtual Machine and click Create a virtual Machine, 

As you can see in the above screen it will prompt you for configuration details, either you can select the configuration image from gallery(Pre-Define images) or select your own if you have subscribed for the configuration and Select Create Virtual Machine.

I have Chose "From Gallery" for our example,

As you can see in the above image you will be presented with various images of different products of Microsoft, As I want a Windows server Hosting SQL Server 2014 I Choose SQL Server->SQL Server 2014 RTM enterprise Image.

Note:- You can Still just create a Windows Machine and install SQL Server as in On-Premises Machines if you have the Licence for SQL Server Already.

Now It will Prompt you select Virtual Machine Name and Server Configuration/Server Tier Along with a Local Administrator and Password.(Above Image)

Next is if your creating a Azure for the first time it will prompt you create a Cloud Service Account and a Storage Account.(Above Image)

Next is if you want to Install any Anti-Virus or other Agents to your base Windows build, Select Appropriately. (Above Image). Once you click ok Microsoft will start provisioning your server

(Below Image)

Once the server is provisioned and ready to use you should see the Status as Running and at the bottom of the portal you should see the Connect option enabled.(Image Below)

Click Connect, It will download a RDP File, Click the RDP file to Remote Desktop to the new server(Image Below)

As you can see in the above figure, You can now access the server as a usual VM server with SQL Server 2014 Installed and it is no different from your On-Premises server.

Now Lets go back to the portal again and see what option we have from the Azure Portal, First Lets see the home page(Image Below)

As you can see you have Virtual server created for your account, let see what happens when you click the virtual server.

Which we select the virtual server from the home page we will be displayed with a dashboard with Server details and Performance. As you can see in the top Tabs as the name suggests you can also monitor the performance separately, configure endpoints for the server.

The Last page is the Configure Tab in the server setting page, In this page you change the server configuration to different Tier for scalability and also select something called Availability Set with the High availability option for the server which is not in the scope of this post.

In the next post we will see how we can create a SQL Azure from the Portal with Screen Shots.

Understanding SQL Server on Windows Azure VS Azure SQL database

Often people get confused with the two different types of SQL Server service available on Azure or Microsoft cloud services,

1.       SQL Server on Windows Azure

2.       Azure  SQL Server Databases

SQL Server on Windows Azure:-

SQL Server on Windows Azure is nothing but a regular instance of SQL Server installed on a Virtual Windows machine hosted on Cloud (Windows Azure), a virtual server hosted in Microsoft data centre instead of On-Premises Data Centre. You can use the Windows and SQL Server similar to your on-premises system. The Windows and SQL will still Admin to manage the environment. Microsoft calls the category of SQL Server in Azure Virtual Machine (VM) as Infrastructure as a Service (IaaS) 

More details http://msdn.microsoft.com/en-us/library/azure/dn133151.aspx

Microsoft has come up with many pre-defined SQL Server configured images so when you provision your virtual Machine in Azure you can straight away request for a SQL Server Image which will have your SQL server installed when the system is delivered.

There are various purchase option http://azure.microsoft.com/en-us/pricing/purchase-options/ for cloud based SQL Services available, without going much into various option broadly you can either subscribe a licence for SQL Server Image which holds both windows or SQL Server Licence or you can just subscribe a Windows Azure image and install the SQL Server yourself and use if have any existing licence.

Azure SQL Server Databases:-

Azure SQL Database is a relational database-as-a-service, where Microsoft offers Database as a service. These databases are hosted in servers maintained by Microsoft and the server layer is not visible to the users or admins. In this configuration database maintenance requirements are very minimal as we do not control any physical features of the database and you do not have to maintain the server infrastructure.Microsoft calls the category of Azure SQL Databases as Platform as a Service (PaaS)

More detail http://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-iaas/

purchase option http://azure.microsoft.com/en-us/pricing/purchase-options/

Since Azure SQL databases works on a slight different architecture to standard SQL databases there also some limitation with this type of databases. All the server features are in accessible as only database is offered as service and almost all system objects are not visible making it difficult to manage the database.

Find more details on features not supported by Azure SQL databases.

http://msdn.microsoft.com/en-us/library/azure/ee336281.aspx

Below is an image of a SQL Instance hosted of Windows Azure and Azure SQL database just to have look and feel difference when accessed through SSMS. The First Image is the Windows Portal for Azure which has two sections highlighted Virtual Machine section is where we create Windows Azure and SQL Databases is the section we can create Azure SQL databases. The second Image shows how SQL database on Windows Azure and Azure SQL databases looks like in SSMS.

In my next post we will discuss how to create  SQL Server on Windows Azure and Azure SQL databases in detail with some screen shots.

Transaction Log Backups on Availability databases and its effect on database shrink

With the introduction of availability databases, I have been queried frequently about how to schedule a T-Log backup on Availability databases, do we need to schedule the backup on both the servers or only on Primary. To clear these doubts I planned to do some tests to explain the effect of T-Log backup on Availability databases. The architecture of T-Log has been clearly explained with respect to Availability databases in the below article, All I am going to do is do some testing to prove this.

http://blogs.msdn.com/b/alwaysonpro/archive/2013/09/27/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part-1.aspx

I am going to use a availability database called AGTest for this testing,

Create the test Table on Primary database,

USE [AGTest]

GO

CREATE TABLE [dbo].[TLogTest](

       [Cnt] [int] NULL

) ON [PRIMARY]

GO

Get the T-Log usage before data updates

DBCC SQLPERF(LOGSPACE)

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	37.20472	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	12.79528	0

Do a logged operation on Primary database for T-Log usage,

DECLARE @intLimit AS INT = 1000000

DECLARE @intCounter AS INT = 1

WHILE @intCounter <= @intLimit

BEGIN

INSERT INTO TLogTest VALUES (@intCounter)

SET @intCounter = @intCounter + 1

END

Get the T-Log usage after logged operation

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	98.73397	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	98.21497	0

The above metric looks logical as per the HA architecture, now I am going to Take T-Log backup of the AGTest Database in Primary alone,

BACKUP LOG [AGTest] TO DISK = 't:\Test.bkp'

Now let’s look at the T-Log size,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	3.325828	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	2.814456	0

So the a T-Log backup on the primary does truncate the log records on both Primary and the secondary HA database. Technically backup on either of the database should be enough but there is a catch here, Now I try to shrink the T-Log on primary let’s see what happens.

Now let’s look at the Log File size,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	6.031522	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	51.17969	5.566898	0

Whatever I try to do I was not able to shrink the file, Now I decided to the only step I missed BACKUP Secondary HA database.

BACKUP LOG [AGTest] TO DISK = 'P:\Test.bkp'

Now I ran the shrink again on the primary database, Let’s see the Log file size now,

Primary Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	30.01969	0

Secondary HA Database:-

Database Name	Log Size (MB)	Log Space Used (%)	Status
AGTest	0.9921875	5.659449	0

:-) And there it goes log file was shrunk in both Primary and secondary databases. Hope this article gives you an idea of how to schedule a log backup on HA databases.