Archive

Author Archive

New Microsoft Azure Portal | Mind = Blown

August 27, 2014 Leave a comment

Microsoft recently announced a first-of-its-kind Cloud experience that brings together cross-platform technologies, services and tools, enabling developers and businesses to innovate at startup speed via a new Microsoft Azure Preview Portal.

“Developing for a mobile-first, cloud-first world is complicated, and Microsoft is working to simplify this world without sacrificing speed, choice, cost or quality,” said Scott Guthrie, executive vice president at Microsoft. “Imagine a world where infrastructure and platform services blend together in one seamless experience, so developers and IT professionals no longer have to work in disparate environments in the cloud. Microsoft has been rapidly innovating to solve this problem, and we have taken a big step toward that vision today.”

Windows AZURE is now Microsoft Azure!

New Portal: portal.azure.com
01_Azure_Portal



–> Check in this Video the new “AZURE Portal” & how DevOps just got a whole lot more awesome:


The new “Microsoft Azure” Portal provides a fully integrated experience that will enable customers to develop and manage an application in one place, using the platform and tools of their choice. The new portal combines all the components of a cloud application into a single development and management experience.

–> New components include the following:
1. Simplified Resource Management
2. Integrated billing
3. Gallery
4. Visual Studio Online
… for more info visit Microsoft Press, [link].


–> Easily Spin-Up a new “SharePoint Farm” in minutes:

You can see here on AZURE cloud how easily and in no time you can Spin-Up a new SharePoint Farm in minutes and few Clicks:
02_Azure_Portal_SharePointFarm

Azure also provides you various options to select SQL Server Plans based upon your preference and budget:
03_Azure_Portal_SharePointFarm_SQLPlans

Otherwise in an on-Premise environment or with any other vendor solution this would take days to setup a new Server (Hardware) with Windows Server OS, SharePoint, SQL Server, etc. installed and get things configured.


–> Spin-Up a new “SQL Database” in seconds:

Creating a new SQL Server instance is also very easy where you provide all necessary information in simple steps, like SQL Instance Name:
04_Azure_Portal_SQLServerDB_onAzure
The new instance will be created in seconds and you can access the Instance Create Databases, Tables, Views, other DB Objects and easily Query them via SSMS.


–> Microsoft Azure Offerings:
Apart from these 2 offerings that we saw above, Microsoft Azure provides lot more that you can browse in Azure Gallery:
05_Azure_Gallery

I’ll be exploring the new Portal as per my needs and post my experiences in coming posts, so stay tuned!

Start a Blog | Why, What, How, Where

August 5, 2014 Leave a comment

I started Blogging almost 5 years back (early 2009). That time I didn’t thought to blog publicly, but just created a blog site in Google’s Blogger.com to create and track a library of technical articles that I can access anywhere. Suddenly I started getting hits on my posts and people started asking questions and suggestions. Then I thought to give my blog a professional look and add some quality content.

I was quiet impressed with WordPress.com so moved my blog to this new hosting site and SQLwithManoj.wordpress.com came into life.

Last week I thought to port my blog to a dedicated Domain name, so I upgraded my account in WordPress only rather than going with other domain registering sites. Now the blog’s new address is SQLwithManoj.com, which is maintained by WordPress only.

Lot of people ask me about Blogging, like: how to start blogging, what platform they should use, what content they should put, domain name, space, themes, etc. Here through this blog post I’m trying to answer some of the FAQs.

–> Main points to consider:

1. What do I blog:
– Think on a topic or subject you are passionate about, where you have deep/sound knowledge and can write about it very frequently, like Technology (All, or any Particular like SQL, .net, Java, etc.), Photography, Travel, Fashion, Politics, etc.
– Some people also blog to promote their own business/products/companies, etc.
– Make sure the content you put is genuine and will help people in reality.

2. How do I setup my blog:
– You can start with any of the free Blog Hosting sites, like WordPress, Google’s Blogger, Tumblr, etc.
– Or can go with Paid options for these site or other Hosts like GoDaddy, where you will get your own custom URL (Domain name) with more control.

3. How frequent should I blog: Depends upon the Topic, Time and your Passion.
– If it is current affairs and/or trending stuff, then daily, but you should have ample time.
– If automobiles/movies or similar then you should be the first and best (or amongst them) to blog them whenever they are launched.
– If its Technology then it could be daily/weekly/monthly, but at least one/two blog(s) per month to keep your readers connected with you.

4. Some Do’s & Don’ts:
– Do not copy other people’s blog posts.
– You can write about same topics what others have already posted, but make sure to give your post a different title.
– Its Ok to take tips from other Blogs, but the content should be genuinely written by you and should make a difference when compared by others.
– Use Widgets to provide more information and ease of access to your users to navigate on your blog site.
– Use Themes to beautify your blog, and can tweak CSS to give it a different look.
– Don’t forget to put a Disclaimer on the blog so that you do not come under any issues.
– Associate your blog URL or site map with Search Engines, like Bing, Google, etc. for SEO, so that you get max traffic/hits.
– Use inbuilt or third-party Stat counter, Comments, PingBacks, Rating systems/widgets to know the influence & popularity of your blog.
– Popularize your blog with Social media like a Facebook Page, LinkedIn, Twitter, etc.

Let me know if you have any questions or need clarifications on these points.

Happy Blogging !!!

AdventureWorks 2014 Sample Database Released! – for SQL Server 2014 (Hekaton)

August 5, 2014 Leave a comment

Sample DataBases AdventureWorks 2014 for SQL Server 2014 has been released and is ready for download, [link].

The sample includes various flavors of samples that you can use with SQL Server 2014, and are:
1. OLTP Database
2. DW Database
3. Tabular Model Database
4. MultiDimensional Model Database

So download these now and start practicing and working on 2014!!!

SQLServer2014_AdventureWorks2014_Sample_DBs

Self Service BI by using Power BI – Power Query (Part 1)


In my previous posts I discussed about Power BI [link], what is it, its components, features and capabilities in the new world of Self Service IB.

Self Service BI allows end users to design & deploy their own reports, analyse within an approved & supported architecture and tools portfolios. End users do not have to worry about maintaining databases, doing integrations, creating warehouses/marts, reports, etc. The Self Service BI tool provide features which are capable enough to do all these activities in an automated, quick and efficient way, and all you have to do is learn how to configure these tools.

Power BI is one such tool offered by Microsoft, you can read about it my previous posts, [link] and Microsoft Official blog, [link].

Power BI works ONLY with Excel & Office 365. It is nothing but a collection of different components which provide features as follows:
1. Power Query
2. Power Pivot
3. Power View (aka Crescent)
4. Power Map (aka GeoFlow)
5. Power Q & A (aka Natural Language Processing)
6. Office 365
7. Windows 8 App

–> Power Query is used to easily discover or gather data from various public or corporate sources, like:

Web page SQL Server database IBM DB2 database Windows Azure Marketplace
Excel or CSV file Windows Azure SQL Database MySQL database Active Directory
XML file HDInsight SharePoint List Facebook
Text file Access database OData feed SAP BusinessObjects BI Universe
Folder Oracle database Hadoop Distributed File System (HDFS)

–> Let’s see a small demo how you can use Power Query: All you need is Excel and Power Query add-In, which you can download from Microsoft official site, [link].

Below image shows various ways you can access public or corporate data from various sources from an Excel workbook:
1. Online Search
2. From File
3. From Database
4. From Other Sources
PowerBI_PowerQuery00

Let’s see how Online Search works. The moment you type “Olympics 2014″ it populates lot of sources from where you can fetch data from:
PowerBI_PowerQuery01

On selecting a source it tries to connect to the public portal and displays the Source URL while fetching the data:
PowerBI_PowerQuery02

And finally populates the data in tabular format in an Excel worksheet:
PowerBI_PowerQuery03

Go to Ribbon, click “Table Tools” -> Query -> Edit, this will open the “Query Editor” window where you can edit, clean the data. You can even Create Queries that you can save and use again later to refresh your data. Merge different tables in one step; rename, delete or even create fields. Transform your data before even importing it into a spreadsheet.
PowerBI_PowerQuery04

This way you can get your data ‘analysis ready’ with Power Query !!!

Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014

July 24, 2014 1 comment

Column Store indexes were introduced in SQL Server 2012 with a flavor of Non-Clustered index i.e. “Non-Clustered ColumnStore” index. However there is a big limitation that the underlying table becomes read-only as soon as you create one.

In SQL Server 2014 this behavior is unchanged and addition to this you can also create ColumnStore index as a Clustered index. And the good thing is that the table having “Clustered ColumnStore” index can also be updated. However there is one more big limitation here that there is no Clustered Key with this type if index, thus risking the Uniqueness in the table.

–> Here we will see this limitation and a workaround which can be used in some scenarios:

USE tempdb
GO

-- Create a simple table with 3 columns having 1st column to contain Unique values:
CREATE TABLE dbo.TableWithCCI
(
	PKCol int NOT NULL,
	Foo int,
	Bar int
)
GO

-- Now create a "Clustered ColumnStore" index on this table:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableWithCCI ON dbo.TableWithCCI
GO

–> Notice: While creating this index there is no provision to provided the “Clustering Key”, as this index includes all of the columns in the table, and stores the entire table by compressing the data and store by column.

On checking the metadata (by ALT+F1) of the table, you will see NULL under the index_keys column:
SQLServer2014_Unique_CCI

Now let’s check this feature of absence of Uniquenes. We will enter 2 records with same value:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

SELECT * FROM dbo.TableWithCCI
GO

You will see 2 records with same duplicate value.

Now, let’s create another Unique index to enforce this constraint:

CREATE UNIQUE INDEX UX_TableWithCCI ON dbo.TableWithCCI(PKCol)
GO

We get an error that you cannot create more indexes if you have a Clustered ColumnStore index:

Msg 35303, Level 16, State 1, Line 25
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

–> Workaround: As a workaround we can create an Indexed/Materialized View on top this table, with Clustering Key as the PK (1st column of the table/view):

CREATE VIEW dbo.vwTableWithCCI 
	WITH SCHEMABINDING
AS 
	SELECT PKCol, Foo, Bar
	FROM dbo.TableWithCCI
GO

-- Delete duplicate records entered previously:
DELETE FROM dbo.TableWithCCI
GO

-- Create a Unique Clustered Index on top of the View to Materialize it:
CREATE UNIQUE CLUSTERED INDEX IDX_vwTableWithCCI ON dbo.vwTableWithCCI(PKCol)
GO

Now let’s try to enter duplicate records again and see if these can be entered or not:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

Now, as expected we get an error after we inserted 1st records and tried to insert the 2nd duplicate record:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 48
Cannot insert duplicate key row in object ‘dbo.vwTableWithCCI’ with unique index ‘IDX_vwTableWithCCI’. The duplicate key value is (1).
The statement has been terminated.

Not sure why Microsoft has put this limitation of not maintaining the Uniqueness with these indexes. While using this workaround you need to consider this approach if possible. Like in some scenarios where the table is very big and there are frequent updates (INSERT/UPDATE/DELETES) this approach of maintaining another Indexed-View would be expensive. So this approach should be evaluated before implementing.

-- Final Cleanup:
DROP VIEW dbo.vwTableWithCCI
GO
DROP TABLE dbo.TableWithCCI
GO

I look forward in new versions of SQL Server to address this limitation.
You can also refer to MSDN BOL [here] for checking all limitations with ColumnStore Indexes.

Follow

Get every new post delivered to your Inbox.

Join 398 other followers