Building a Virtual Machine within Azure (IaSS) – Improvements !


Those Dev’s/UX designers out there at Microsoft seems to be pretty busy as they releasing back to back updates to windows azure portal over the last couple of months.

I had to create a VM in Azure to do some testing, and this time I decided to try out the new portal. Apparently, I’m not a great fan of Windows 8 styled tiles, but on Azure portal these tile like options are not that bad.

I selected virtual machine from the add button(+) and the portal gave a nice view of the available options  :

Create VM

I’m pretty impressed with the amount of flavors you have now for the VMs. Oracle Linux, Open SUSE ! Cool !

I picked SQL2014 standard, and started the create process. When you start entering the details like host name, user name etc. everything looks same as before but as you click the pricing tier, there comes some improvements.

You now have an option to check all the pricing tiers and can get an idea on the approx. monthly costs pretty easily.

Click the image for better resolution.

Create VM2
Icing on the cake is the Max IOPS numbers. This is way cool and handy to know and have.

Create VM3

I chose something small and started exploring more. I gave the hostname and all other required things and chose check box, “Add to Startboard”. This will just add the new VM to azure portal home page.

Portal started giving me cool visualization when the VM was getting created, and the current actions were getting displayed in the notifications area.

Create VM4

As soon as the VM was created its details were made available in the startboard. There are some cool data like Disk, CPU, Network etc. which definitely will come handy for everyone.

Create VM5

The process to add an alert for a specific matric looks pretty straight forward within the portal. I tried to set an alert for disk, and here is what the portal has to offer.

Create VM6

Overall, I’m really glad the way Microsoft Azure has improved over the last couple of years and now this is shaping up to be one of the trusted platforms to host critical workloads with great management capabilities.

Thanks for reading and keep watching this space for more! (Hint: Some cool SQL2014 related posts are on the way !)

Microsoft Azure Portal Preview looks promising !


This is one of those posts which is pending for a very long time. I was pretty excited when Azure Portal Preview was announced during BUILD2014. This portal will deeply enhance developer experience.

As a SQLServer person I was interested to explore more within the portal to see what in store for me, and here are some of the cool things which the portal had to offer me.

As soon as you login, the page welcomes you with some great information such as Service Status, Billing info etc.

Portal1

If you interested to know about a service, there is great level of information which is made available in the portal.

Portal2

If you drill down to the billing details, then you are provided with some nice stats.

Portal3

You have the option to right click a particular view and you can pin it to the startboard( Yes, Windows8 theme is everywhere !)

Portal4

Portal5

You can even do customizations for the view which was added to the startboard.

I tried to create a SQLDatabase using the portal, and it allowed me to create a web edition one with 1GB size. (Remember this is still preview). Here are the steps

Portal6

Portal7

There are lot of things like creating VMs is not available in preview for now, but the layout looks neat.(Again, if you are a windows8 user, then you will love it !)

Portal8

 

One thing really excited me was the view for storage. Everything was laid out nicely. Quite impressive !

Portal9

Conclusion  :

I’m looking forward to this portal, and I’m pretty sure that it will add more value to everyone.

Keep watching this space for more.

Journey of an On-Premises DB to Azure VM – SQL Server 2014 makes it easier!


SQL Server 2014 is deeply integrated with Windows Microsoft Azure and moving an On-prem database to a Microsoft Azure VM is much easier and seamless now.

This post talks about the journey of a database named “MoveMeToCloud” which is hosted on SQLServer 2014 RTM on-prem instance to a SQL Instance which is hosted on an Azure VM.

Here are the steps (Can’t get easier than this)  -

1. Right click database “MoveMeToCloud” and choose tasks and then Deploy Database to Windows Azure VM (Oops, that named needs to be changed too!)

Move1

2.  As soon as we complete the first step, we will get the intro screen. Pretty straight forward information made available here.

Move2

Once we go pass the intro screen we will get the option to connect to the local SQL Instance.  We can then choose the db which we want to move, and also a location for the backup files.

Move3

3. Next up will be security related settings. We will need a management certificate to proceed here.

Move4

By using the Sign in option we can get the required certificate. This is a smart wizard and it will pull the certificate details for you.

Move5

We will proceed by clicking next.

4. Next up is the deployment settings part. This part is little tricky as we will have to do some authentication.

Move6

As you can see in the above screen the Cloud service name, Virtual Machine name and the Storage acct is already available as we have done a sign-in in the earlier step.

We will need to access the settings button to do an authentication again. There should be a VM end point for the specific port for this to work.

Move7

Once the authentication is done, we will get the target database details which will be automatically populated.

Move8

5. Final step is to check the summary and initiate this by saying finish. The wizard will do rest of the job for you.

Move9

Move10

 

If we check the SQL Instance on Azure we can see the database is available there, Viola !

Move11

Conclusion

This is a very smart wizard and I’m really happy that Microsoft have made great efforts to make this simple to ensure that cloud movements are seamless.

Thanks for reading and keep watching this space for more !

Data Collector in SQLServer 2014 – What’s New !


Today I noticed couple of changes(Neat changes !) for Data Collector in SQLServer 2014. This post is a detailed walk-through of setting up Data Collector in SQL2014. In the walk-through if there is a new feature or change which is specific to SQL2014,then I will highlight that.

As soon as you right click Data Collector and choose Tasks you can see 3 options(This is a change in SQL2014) -

  • Configure Management Data Warehouse
  • Configure Data Collection
  • Cleanup Data Collectors (This is a change in SQL2014)

Lets talk about each option in detail now.

Option 1

The first option will create and configure a Management Data Warehouse.

DataCollector20141

We will proceed by clicking Next and it will take you to the screen which will help you to create/choose the MDW database. I have already created a blank database, so I will select that db in this walk through and will proceed.

DataCollector20142

Next up is to grant permissions to users. Reader role is for reports, writer is for uploading data and admin has all these rights.

DataCollector20143

Final step is to finish the operation.

DataCollector20144

DataCollector20145

Option 2

Configure data collection option helps you start data collection and store collected data to the MDW.

DataCollector20146

This step lets you choose the MDW db,a directory to store the data before its uploaded to the MDW and the collection sets. There are 2 collection sets -

  • System Data Collection Sets
  • Transaction Performance Collection Sets (This is new in SQL2014)

The above selections will create 2 new data collector sets called Stored Procedure Usage Analysis and Table Usage Analysis along with regular 3 data collector sets which was available in the previous versions.

DataCollector20147

Complete the wizard by clicking finish.

DataCollector20148

DataCollector20149

Now that we have configured a MDW and started Data Collection, lets see what happens in the cached folder C:\MDW_Temp

There you go,lot of CACHE files out there and its getting loaded/cleared very fast.

DataCollector201410

 Option 3 - (This is new in SQL2014)

Cleanup Data Collectors is an option which will be enabled after you enabled data collection and this wizard will help you the clean up the collection set.

DataCollector201411

DataCollector201412

DataCollector201413

So as the 1st phase of configuring data collector is now over, we will drill down little more.

If you collapse the Data Collection tree you can see 2 new data collection sets( This is new in SQL2014)

  • Stored Procedure Usage Analysis
  • Table Usage Analysis

DataCollector201414

 

These data collectors are integrated with data collector for a purpose. The data which is collected will provide you with valuable insights on tables and stored procedures which are good candidates to migrate to In Memory OLTP(Hekaton).

I would recommend you to read a great blog post by Benjamin Nevarez(B/T) on this very topic.

Regular data collection sets which were there in the previous versions are also available and its under System Data Collector Sets

DataCollector201415

 

Lets have a quick look at the reports which data collector offers. These are pretty cool out of the box basic reports and they will help you understand some level of trending.

Query Stats History Report -

This report will give you details of Top 10 TSQL queries and you have the option to rank queries on the basis of CPU, Duration, Total I/O, Physical Reads, Logical Writes. There is also an option to do play back.

DataCollector201416

DataCollector201417

 

You also have an option to drill down to particular queries to get more details.

DataCollector201418

DataCollector201419

Server Activity History Report -

This is one of my favorite reports. Its tell you about CPU, Memory, Disk, Network usage trends. The report also gives valuable insights about Wait Stats and Server Activity. Again you have the option to drill down for some of the graphs.

DataCollector201420

Disk Usage Summary Report -

This is a great source to know the growth trends.

DataCollector201421 Conclusion

Data Collector is a great feature, and In Memory OLTP being the buzz word, the data it collects can provide you with valuable insights.

Thanks for reading and keep watching this space for more ! (Next up is some cool things about WindowsAzure(Oops !) Microsoft Azure !!!

SQL Server 2014 Install – Step by Step


Hot cake for the day is SQLServer 2014 RTM bits. Its generally available starting today, and I downloaded a fresh copy to start testing it.

I will be writing a separate post on the features I love about SQLServer 2014,but here is the step by step install process for a standalone install.

There are no changes for the GUI based install process when you compare with SQLServer 2012,but one thing I’m pretty sure that the installer is sleek and faster.

Step 1  – The landing page looks pretty similar to the earlier versions, and you can choose the installation tab to get things started.

SQL2014 RTM Install1

Step 2 – Choose the stand-alone install option, and it will take you to the product key page.

SQL2014 RTM Install2

Step 3 – This screen is where you need to accept the license terms. There is also an option to turn on select Customer Experience Improvement Program and Error Reporting.

SQL2014 RTM Install3

Step 4 – 6 These are the steps where Global Rules, Product Updates are checked and Setup files are extracted and installed. If the checks are good, then the install will proceed to the next step automatically.

SQL2014 RTM Install4

Step 7 – Install rules are checked for this step, and I have highlighted a check which was interesting to me.

SQL2014 RTM Install5

 Step 8 – This step provides you with Feature installation options. In this install I will discuss about the first option, ie SQLServer Feature Installation.

SQL2014 RTM Install6

Step 9 –  This step is where you will select the features. I’m choosing only those ones which I need for my testing.

SQL2014 RTM Install7

Step 10 – This step lets you decide if you want to go with Default Instance or a Named Instance.

SQL2014 RTM Install8

Step 11 – This step lets you select the account for the services. I’m choosing all defaults as that’s good for my test lab.

SQL2014 RTM Install9

Step 12 – This step lets you choose the Authentication mode, Data directories, and File Stream options.

SQL2014 RTM Install10 SQL2014 RTM Install11 SQL2014 RTM Install12

Step 13 – Feature Configuration Rules step. As mentioned earlier if the checks are fine, setup automatically proceeds to the next step.

SQL2014 RTM Install13

Step 14 – This is the final step of the installation, and you can pick the ConfigurationFile.ini from the path

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Date_Time\ConfigurationFile.ini

SQL2014 RTM Install14

 

If everything goes smooth, your install will succeed.

SQL2014 RTM Install15

I’m really excited about some real good features in SQLServer 2014,and I will be writing about them during the next couple of days.

Thanks for reading and keep watching this space for more !

 

Folks,Know your Outbound Data Transfer Costs – Windows Azure


The cost associated with Outbound Data Transfer is very important when you consider bridging your On-premise network with Windows Azure.

Yes, when I said cost I indeed meant the $ amount which is involved. The good news however is that all the Inbound Data sent to Azure is free. The outbound data is all what really matters.

As per Microsoft

Outbound data is charged based on the total amount of data moving out of the Windows Azure datacenters via the Internet in a given billing cycle. Data transfers between Windows Azure services located within the same datacenter are not subject to any charge. The first 5 GB of outbound data transfers per billing month are also free.

Lets talk about this simple scenario – You have deployed SQLServer on a Windows Azure Virtual Machine. Data out of the SQLServer database will be accessed by clients which are located in your On-premise data center. Below diagram will explain the scenario.

Note – This is a high level diagram and doesn’t explain the actual network intricacies which are involved while bridging your network with Azure.

Outbound Azure

All the outbound data which is flowing out of the Azure data center is a cost ($) factor for you and inbound data sent is free.

How much should I pay?  Well,that really depends on your usage.

First 5 GB a month is free and beyond that cost is like(As of today) -

Outbound Azure2

Ref – http://www.windowsazure.com/en-us/pricing/details/data-transfers/

Note – Always ref http://www.windowsazure.com for any pricing estimates as these numbers($) tend to change.

Conclusion

Analyzing and understanding various cost factors which are involved with Windows Azure is one of the key areas an Architect should focus on and comparing this costs with ongoing on premise operational costs will help management get a clear picture.

Thanks for reading and keep watching this space for more !

Rollback DBCC CHECKDB REPAIR !


I learned something new today while I was listening to one of the courses on corruption authored by the master, Paul Randal(B/T).

In the course Paul mentioned that you can actually rollback DBCC CHECKDB REPAIR by using it within a Transaction.

This is something which is absolutely new to me, and I decided to test this stuff for some fun.

Why we need to rollback a repair?

The short answer is – There is no specific need to rollback a repair, but technically this will work and its possible.

Here is a quick demo on how this works -

We will be using a database named DBCC_PLAY which is already corrupt,and the corruption is for pageID 288.  An extract from DBCC CHECKDB output is mentioned below.

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.

If we try to do a read(SELECT) from one of the tables in the database, then that will also throw an error.

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

Msg 824, Level 24, State 2, Line 35
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdf2e6f16; actual: 0xdfaeef16). It occurred during a read of page (1:288) in database ID 5 at offset 0x00000000240000 in file ‘C:\SQLDATA\DBCC_PLAY.mdf’. 

Before we try to do a repair, we will bring the database to single user mode.

--SET DB SINGLE_USER FOR REPAIR
ALTER DATABASE DBCC_PLAY SET SINGLE_USER
GO

Now we will run the repair in a transaction, but wont do a commit.

--REPAIR IN A TRAN DEMO
BEGIN TRAN
GO
DBCC CHECKDB('DBCC_PLAY',REPAIR_ALLOW_DATA_LOSS)
GO
--ROLLBACK TRAN

DBCC REPAIR does its job, and the error has been repaired

Msg 8928, Level 16, State 1, Line 65
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 65
Table error: Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data), page (1:288). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 10249 and -4.
The error has been repaired.

We can run DBCC CHECKDB to be sure.

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Command(s) completed successfully.

We can also do a SELECT from the table and see if that’s working too.

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

(832 row(s) affected)

SELECT is also working,and we now dont have any logical consistency error.

Now comes the fun part. Lets ROLLBACK the transaction which we had started for the repair.

ROLLBACK TRAN

Command(s) completed successfully.

Lets run a DBCC CHECKDB

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Gosh, corruption is back !

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.

Can we do a SELECT from the table? Lets try

--SELECT DATA FROM THE TABLE
SELECT * FROM Test_Table1
GO

Nope, there comes the logical consistency error

Msg 824, Level 24, State 2, Line 35
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdf2e6f16; actual: 0xdfaeef16). It occurred during a read of page (1:288) in database ID 5 at offset 0x00000000240000 in file ‘C:\SQLDATA\DBCC_PLAY.mdf’. 

This clearly shows that you have the ability to roll back a repair.

Lets clean up things, by repairing the database and committing it.

--REPAIR IN A TRAN DEMO
BEGIN TRAN
GO
DBCC CHECKDB('DBCC_PLAY',REPAIR_ALLOW_DATA_LOSS)
GO
COMMIT TRAN

Command(s) completed successfully.

Run a DBCC CHECKDB to ensure that there is no more corruption.

--CHECK CORRUPTION
DBCC CHECKDB('DBCC_PLAY') WITH NO_INFOMSGS;
GO

Command(s) completed successfully.

Thanks for reading and keep watching this space for more !