Future DBA – Hadoop Big Data

As we discussed on earlier blog that in future its more than SQL-(R)DBMS, as data is growing we have to know BIGDATA as well, and when we talk about BIGDATA we should be aware of HADOOP.

I have gone through many blogs and webcasts but its little complicated on hadoop related stuff. but I want to go for concept and easy way of learning. this blog is not an deep dive but an overview of Hadoop. Hadoop works on HDFS(Hadoop Distributed File System), where it has Named node and Data node. Named node contains the metadata information where which data stores, and Data node contains actual data.Name node should be high capacity cluster with big configuration and data nodes can be a multiple (Many..Many) stand alone system to distribute the data on multiple servers… that’s call “Distributed File System”.

Working on HDFS is quite difficult requires MapReduce programming and retrieving and saving data on it is requires expertise in programming, so to overcome it there are several supported tools been used, Some of them are as follows:

  • Apache Pig
    Apache Hive
    Apache HBase
    Apache Phoenix
    Apache Spark
    Apache ZooKeeper
    Cloudera Impala
    Apache Flume
    Apache Sqoop
    Apache Oozie
    Apache Storm

All the above products are open source (Apache) and do not have vendor support.

 

There are 3 Vendors who has worked on this open source and build the enterprise product and they provide support to HDFS system they are as follows:

Cloudera – using the Cloudera Director Plugin for Google Cloud Platform

Hortonworks – using bdutil support for Hortonworks HDP

MapR – using bdutil support for MapR

This is the basic and quite important information if you want to go with Hadoop system. so when we see these tools we should know that these are based on Hadoop file system-HDFS.

I will talk more on these in future blogs also will write on other NoSQL technologies, Like MongoDB which doesnot use HDFS.

I just started writing on BIG Data/NoSQL. so Appriciate your comment/feedback

Reference:

https://en.wikipedia.org/wiki/Apache_Hadoop#cite_note-8

 

Posted in Future DBA, NoSQL, Others | Tagged , | Leave a comment

Operational Analytics -SQL Server 2016

What is Operational Analytics:-

Operational Analytics is a combination of two words “Operational” and “Analytics”. so your OLTP system is a operational system where day to day task eg order table keeps on updating. and Analytics is a OLAP where analysis of the order table can be done after ETL – moving the operational data with the help of nightly jobs… or other ways to OLAP system eg. Analysis services or BI system and analyse OLTP data, which is used by manager or decision maker and decide.

So earlier days when we have to analyse the data we have to wait for some time as querying on the OLTP system is quite expensive and cost a lot and makes system hung due to Locking and un-compatible locks.

Now Management team wanted to analyse the data as soon as any order takes place to decide how things are happening and understand the system and decide on it.

hence “Operationsl Analytics” is place and both system or task can be done at a time. this has been incorporated by other system, so as SQL Server.

SQL Server can achieve this in SQL Server 2016 with the help of :

  • In-Memory System.
  • Updateable Non cluster Column store Index (NCCI)
  • Compression Delay (Filtered Indexes)

So considering the critical/hot data in in-memory tables and use those tables as a NCCI and use compression delay so that the column store data will be compressed after that delay to maintain if that data is getting changes.

the detail is in following blog:

https://msdn.microsoft.com/en-IN/library/dn817827.aspx

this is happening things and would like to write more on it.

*btw: Sunil Agarwal has written/webcast quite more on this.

https://social.technet.microsoft.com/Profile/Sunil%2bAgarwal/activity

https://blogs.technet.microsoft.com/dataplatforminsider/

 

 

 

 

 

Posted in Future DBA, Others, sql 2016, Whats New | Tagged , | Leave a comment

Distributed AG

going though Allan Hirt’s 24 SQLPASS  recording, he has explained the new feature of SQL Server Availability group you can get it here.

http://www.sqlpass.org/24hours/2016/summitpreview/Sessions.aspx

he is expert in Clustering /AG/ and published several blogs and books.

Distributed AG is advance and extended to AG its like AG to AG

one AG which will be on WSFC between two instances.

eg.

WSFC 1 :Instance A is AG with Instance B and

WSFC 2 :Instance C is AG with Instance D

so we can perform Distributed AG from Instance A to Instance C

then all four instances are in sync with each other. but it has some issues if we make it sync. so its recommendation to make WSFC 1 to WSFC 2 with async so that things will be working efficiently else Instance A will be quite slow to get sync all.

It has its limitations:

important limitation is we can configure it only with T-SQL queries. not with GUI and Powershell(this may overcome in future release)

supported in Enterprise edition only.

Automatic fail over to secondary AG is not supported for now

Thanks Allan 🙂

reference:

https://msdn.microsoft.com/en-us/library/mt651673.aspx?f=255&MSPPError=-2147217396

Posted in High Avaliability, Others, sql 2016 | Tagged , , , , | Leave a comment

Future DBA part 2

I do not say you should be expert in other technologies but you should be aware of what it is and whats the purpose and whats its characteristics and basics.

when we talk of RDBMS (DBMS) like SQL most of the things are common unlike that for NoSQL has different categories.

Its important to understand the different types of NoSQL and its purpose if you want to learn it.

From wiki (https://en.wikipedia.org/wiki/NoSQL)

more on NoSQL

http://www.nosql-database.org/

and

A funny picture.

nosql

More coming next

Posted in Future DBA, Others | Tagged | Leave a comment

Future DBA Part 1

As I stated in my earlier blog on future DBA, we should learn many things as we can to be stand strong in the market.. Being DBA from last around 15 Years. and still learning… so cool. So far I blog only on SQL Server but now I am learning other technologies as well and I will be blogging about it. initially i was thinking to have a separate site for it but later realize that this blog has my name and not specific to MS SQL so decided to continue blogging in here. I have been blogging from last 8 Years…(July 2008) canot believe it,  and I love it very much. Thanks all..

I will be blogging more about data now with continue on Microsoft SQL Server(My first love). includes SQL Standard, NoSQL,

Wiki has a very nice comparison about RDBMS is available :

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

and here is the NOSQL

http://www.nosql-database.org/

sky is the limit… so its important to understand that things are huge.

 

 

 

 

Posted in Future DBA, Others | Tagged | Leave a comment

World is changing so DBA should

I was going through SQLPASS recording and on first time I could see a session as “MongoDB for the SQL Server Professional” which make me think that even community is also realizing that things are changing and DBA has to be more than DBA (should having knowledge of other technologies) and and to learn new things. So do I.

Yes, I am exploring new things, I have to , to stand in the market, because things are not the same. I could see that now a days if you are expert like me SQL DBA. it is not enough you have to know so many other things a well how it works and what all it has and related, and if you learn you may realize that the similar thing is already exists in your and other technology as well.

for example, if you observed Microsoft has invested many $(Dollar)  in SQL Server 2016 and when you explore it all are their in the marking and to stand Microsoft has to be capable of delivering what market demands. like in-memory, support of R, JSON, Polybase, Azure. StretchDB, SSDT, PowerBI…. SQL on Linux, open Powershell and so on… that makes SQL Server stand one of the best in market.

I blog on SQL 2016 here … https://thakurvinay.wordpress.com/category/sql-2016/

BUT….. as stated earlier things are not same. we have to explore more to be stand strong. when you learn other technologies you will realize it.

I would like to make this as a series in category “Future DBA” which includes NOSQL, BIGDATA, Oper source, …. Stay tuned…

Posted in Future DBA, Others | Tagged | Leave a comment

24 Hours of SQLPASS

Like every years before PASS summit, this years also SQLpass has 24 hrs sessions… it has great sessions every time, and this time as well. During this 24 hrs SQLPASS invites 24 expertise to present a audio session for an hr with their related topic(s) for DBA, BI, Developer and Others…

Now this years 24hrs sessions recording is available. its awesome please use it . here.

http://www.sqlpass.org/24hours/2016/summitpreview/Sessions.aspx

I am also going through it.this site is having wonderful information. Thanks SQL PASS I learned a lot from you. appreciate. I wish I will attend SQLPASS one day and meet all those expertise in person.

Happy Learning.

 

Posted in Free Stuff, Others, Training, What I learned today | Tagged | Leave a comment

SQL 2016-Always Encrypted

As you know this has been introduced in sql server 2016, I am not going in details on it but would like to highlights some points which I learned today:

YOU WILL NEVER GET/READ THE REAL DATA FROM “SQL SERVER MANAGEMENT STUDIO”. IT WILL ALWAYS BEEN ENCRYPTED. THOSE COLUMNS HAS BE READ/WRITE USING APPLICATION CODE ONLY(having higher privilege)

  1. It intern uses self signed certificate for reading the data.
  2. Must required .NET FRAMEWORK 4.6
  3. Need to create COLUMN MASTER KEY and that has to be registered with COLUMN ENCRYPTION KEY.
  4. Presently Microsoft supports only 1 ALGORITHM as ‘AEAD_AES_256_CBC_HMAC_SHA_256’.
  5. Encryption type can be DETERMINISTIC support indexes,RANDOMIZED not supported/good for indexing.

Happy Learning.

 

ref:

https://msdn.microsoft.com/en-us/library/mt147923.aspx

limitations and errors related to always encryption:

T-SQL Tuesday #69 : Always Encrypted Limitations

 

 

 

Posted in Limitation, sql 2016, What I learned today, Whats New | Tagged , , , | Leave a comment

SQL Server 2016 …whats new…WOW!!!

Hey there…. I wrote on SQL Server 2016 in my earlier blog Lets See and Whats new, during that time I knew that SQL Server 2016 will be good. but when I explore and see it now looks like it is a REVOLUTION for RDBMS and Microsoft has spent a lot more on this to be the TOP on Database industry.

I have gone though the Deep Dive sessions and it was amazing the vision about SQL Server is quite clear here that it would no longer a small servers, it is now becomes the enterprise system and can be the leader on RDBMS world. It has consider all the expects of market requirement and make sure that SQL Server is fulfilling all the requirement with up to the expectations and more.

Considering this looks like SQL Server 2014 was just a add-on on (in-memory and some other stuff) of the SQL Server 2012 and the real product is SQL Server 2016.

It would be too early to decide but the deep dive sessions are so impressive, if it goes this way then we could see Microsoft will reach to new high.

Microsoft has consider all the aspects of Database on this as you know CLOUD is happening things and things are moving to cloud and Microsoft is already having AZURE. so to extend the Cloud usage StretchDB more usage of cloud will be coming to empower … more details later.

For Always Available with High availability and reliable with Always On.

For Performance improvement to make SQL per industry standard usage of using in-memory and column store and more advance query tuning stuff … more detail later.

SQL Server is achieving awards to be most secure RDBMS from last 6 years…  it become more secure now with always encrypted, row encryption, dynamic data masking and more.

On OLAP it has extended its arms and now could see a different SQL server on this with SSDT and great performance with in-memory and column store and POWER BI provides you an excellent analytic skill where decision and reporting will be fantastic at the same time quick… more to come

Now its out of the box… as you know when you say Microsoft you say the integration with Microsoft OS and restricted to Microsoft product… now with this release Microsoft has explore the world and would like to be up to customer requirement so this year they have done an acquire more than 16 vendor support product/companies which is one of the biggest investment they have made.

As you know now the market is open and data is increasing like never before and industry is looking for granular/micro analysis of the system BIG DATA is the future but we cannot achieve everything with BIG DATA it has its own limitation with respect to OLTP but for large size of data BIG DATA is happening in the market… with HADOOP (I am learning:)). so to accommodate it Microsoft has introduced several new things like POLYBASE, JSON and more to come.

Finally as I said its a customer prospect if industry demands to keep their OS same (LINUX/UNIX) and would like to make SQL Server on their OS Microsoft has announces SQL Server would compatible with LINUX a great and surprising announcement happened last month which shock everyone … but to see the acquisition of R Language and other stuff it could be quite possible. lets see by mid 2017…

*I know this is one of the longest blog I have written and I would like to explore more on this and write lots about SQL Server 2016 as I cannot keep away myself of writing…

 

Happy learning.

Reference:

Twitter: MyBest Friend….

You Tube: Deep drive sessions: Becoming another best friend.

https://www.microsoft.com/en-in/server-cloud/products/sql-server-2016/

 

 

 

 

 

 

 

 

 

 

Posted in Basic, Others, sql 2016, Whats New | Tagged , | Leave a comment

Index Rebuild VS Reorganize

Posted in Basic, Index, Others, Troubleshooting | Tagged | Leave a comment