Advertisements
Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

2018 blogging in review (Happy New Year – 2019 !!!)

December 31, 2018 1 comment

 

Happy New Year 2019… from SQL with Manoj !!!

As WordPress.com stats helper monkeys have stopped preparing annual report for any of their blogs, so I’ve prepared my own Annual Report for this year again.
 

–> Here are some Crunchy numbers from 2018

SQL with Manoj 2018 Stats

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 793,171 times by 542,918 unique visitors in 2018. If it were an exhibit at the Louvre Museum, it would take about 17 days for that many people to see it.

There were 68 pictures uploaded, taking up a total of ~6 MB. That’s about ~6 pictures every month.

This blog also got its highest ever hits/views per day (i.e. 3,552 hits) on Sept 25th this year.

 

–> All-time posts, views, and visitors

SQL with Manoj all time views


 

–> Posting Patterns

In 2018, there were 26 new posts, growing the total archive of this blog to 546 posts.

LONGEST STREAK: 6 post in Feb 2018

 

–> Attractions in 2018

These are the top 5 posts that got most views in 2018:

1. Download & Install SQL Server Management Studio (SSMS) 2016 (62,101 views)

2. SQL Server 2016 RTM full & final version available – Download now (31,705 views)

3. Getting started with SQL Server 2014 | Download and Install Free & Full version (20,443 views)

4. SQL Basics – Difference b/w WHERE, GROUP BY and HAVING clause (16,113 views)

5. SQL Basics – Difference b/w TRUNCATE, DELETE and DROP? (13,189 views)

 

–> How did they find me?

The top referring sites and search engines in 2018 were:

SQL with Manoj 2018 Search Engines referrers


 

–> Where did they come from?

Out of 210 countries, top 5 visitors came from India, United States, United Kingdom, Canada and Australia:

SQL with Manoj 2018 top Countries visitors


 

–> Followers: 407

WordPress.com: 160
Email: 247
Facebook Page: 1,358

 

–> Alexa Rank (lower the better)

Global Rank: 221,534
US Rank: 139,012
India Rank: 46,758
Estimated Monthly Revenue: $1,320
Actual Monthly Revenue: $300

SQL with Manoj 2018 Alexa ranking


Alexa history shows how the alexa rank of sqlwithmanoj.com has varied in the past, which in turn also tells about the site visitors.
 

–> 2019 New Year Resolution

– Write at least 1 blog post every week
– Write on new feaures in SQL Server 2017 & 2019
– Also explore and write blog post on Azure Data Platform
– Post at least 1 video every week on my YouTube channel

 

That’s all for 2018, see you in year 2019, all the best !!!
 

Connect me on Facebook, Twitter, LinkedIn, YouTube, Google, Email


Advertisements

Hadoop/HDFS storage types, formats and internals – Text, Parquet, ORC, Avro

December 30, 2018 1 comment

 
HDFS or Hadoop Distributed File System is the distributed file system provided by the Hadoop Big Data platform. The primary objective of HDFS is to store data reliably even in the presence of node failures in the cluster. This is facilitated with the help of data replication across different racks in the cluster infrastructure. These files stored in HDFS system are used for further data processing by different data processing engines like Hadoop Map-Reduce, Hive, Spark, Impala, Pig etc.
 

–> Here we will talk about different types of file formats supported in HDFS:

1. Text (CSV, TSV, JSON): These are the flat file format which could be used with the Hadoop system as a storage format. However these format do not contain the self inherited Schema. Thus with this the developer using any processing engine have to apply schema while reading these file formats.
 

2. Parquet: file format is the Columnar oriented format in the Hadoop ecosystem. Parquet stores the binary data column wise, which brings following benefits:
– Less storage, efficient Compression resulting in Storage optimization, as the same data type is residing adjacent to each other. That helps in compressing the data better hence provide storage optimization.
– Increased query performance as entire row needs not to be loaded in the memory.

Parquet file format can be used with any Hadoop ecosystem like: Hive, Impala, Pig, Spark, etc.
 

3. ORC: stands for Optimized Row Columnar, which is a Columnar oriented storage format. ORC is primarily used in the Hive world and gives better performance with Hive based data retrievals because Hive has a vectorized ORC reader. Schema is self contained in the file as part of the footer. Because of the column oriented nature it provide better compression ratio and faster reads.
 

4. Avro: is the Row oriented storage format, and make a perfect use case for write heavy applications. The schema is self contained with in the file in the form of JSON, which help in achieving efficient schema evolution.
 

–> Now, Lets take a deep dive and look at these file format through a series of videos below:


 

Author/Speaker Bio: Viresh Kumar is a v-blogger and an expert in Big Data, Hadoop and Cloud world. He has an experience of ~14 years in the Data Platform industry.

 

Book: Hadoop – The Definitive Guide: Storage and Analysis at Internet Scale
 


Powershell – Restart Azure VM and log off Users remotely

October 21, 2018 Leave a comment

 

1. Open RUN by pressing Windows + R keys, type powershell command and hit Enter.
 

2. Now on Powershell window first connect to the Azure VM that you want to remotely restart:

PS C:\Users\manojp> Enter-PSSession -ComputerName MyAzureVMName

[MyAzureVMName]: PS C:\Users\manojp\Documents>

 

3. Now try issuing the Restart command:

[MyAzureVMName]: PS C:\Users\manojp\Documents> Restart-Computer

Restart-Computer : Failed to restart the computer MyAzureVMName with the following error message: The system shutdown
cannot be initiated because there are other users logged on to the computer.
+ CategoryInfo : OperationStopped: (MyAzureVMName:String) [Restart-Computer], InvalidOperationException
+ FullyQualifiedErrorId : RestartcomputerFailed,Microsoft.PowerShell.Commands.RestartComputerCommand

So, this gave us error as few users are still logged in, thus can’t restart the VM.

 

4. Let’s check who all are logged in on this VM:

[MyAzureVMName]: PS C:\Users\manojp\Documents> quser

USERNAME    SESSIONNAME    ID    STATE    IDLE TIME    LOGON TIME
charlesl         rdp-tcp#0           2      Active    1:07            12/21/2018 08:26 AM

 

5. Let’s try kicking users out by specifying the ID which is “2”:

[MyAzureVMName]: PS C:\Users\manojp\Documents> logoff 2

 

6. We will check if that user is kicked out or anybody is still remaining:

[MyAzureVMName]: PS C:\Users\manojp\Documents> quser

quser : No User exists for *
+ CategoryInfo : NotSpecified: (No User exists for *:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

 

7. Let’s finally restart the VM:

[MyAzureVMName]: PS C:\Users\manojp\Documents> Restart-Computer

PS>


SQL Server 2019 initial “Community Technology Preview” is out – download and check the CTP 2.0 release now !

September 24, 2018 2 comments

 
Today on 24th September 2018 Microsoft announced the released of first CTP version of SQL Server vNext i.e. SQL Server 2019 CTP 2.0.
 

SQL Server 2016 & 2017 brought Machine Learning feature by adding R and Python support for Data Analytics and Scientists.

And now SQL Server 2019 has added Big Data capabilities under a unified platform by adding support for Apache Spark and HDFS, providing support for managing and querying unstructured data with structured/relational data.

Thus giving you an out-of-box end-to-end hybrid data platform with different types of workloads like OLTP, OLAP, DW/BI, Advance Analytics, Big Data and ML & AI on your data.

 
And just like the previous SQL Server 2017 the new SQL Server 2019 version will also run both on Windows & Linux.
 

Download SQL Server 2019 CTP bits:

To download the SQL Server 2019 you can Register and Download the Free evaluation version (180 days).

Or, directly download the web installer EXE (~3.7 MB): SQLServerVNext-SSEI-Eval.exe
 

Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP2.0) – 15.0.1000.34 (X64)
Sep 18 2018 02:32:04
Copyright (C) 2018 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)

 

New Features & Enhancements:

1. Deploy Big Data clusters with SQL Server & Spark by leveraging Polybase [more info]

2. Build and rebuild CCI online (Clustered ColumnStore Indexes)

3. Resumable online index create operation to pause and resume later from where the operation was paused or failed, instead of restarting from the beginning.

4. Row mode memory grant feedback

5. Approximate COUNT DISTINCT (for big data scenarios)

6. Batch mode on RowStore without ColumnStore index

7. Table variable deferred compilation (for better cardinality estimates)

8. New DMV sys.dm_db_page_info system function returns page information

9. Database scoped default setting for online and resumable DDL operations (ELEVATE_ONLINE or ELEVATE_RESUMABLE)

10. Always On Availability Groups – upto 5 synchronous replicas (3 till now)

11. New Polybase connectors or SQL Server, Oracle, Teradata, and MongoDB

12. Support for MERGE DML and Edge Constraints in SQL Graph

13. Expanded support for persistent memory devices (by bypassing the storage stack of the operating system using efficient memcpy operations)

14. SQL Data Discovery and Classification [more info]

… and many more, I’ll discuss about these features in my coming posts.
 

Feature Selection Page:

References:

>> SQL Server 2019 official Page

>> MSDN Blog announcement

>> Docs for SQL Server 2019


Migrate your SQL Server database to Azure by using SysTools “SQL to Azure Migration” Tool

September 5, 2018 Leave a comment

 
Microsoft SQL Server is the most popular database management system, which provides flexibility to the database administrator to manage their database. It is a full-featured database and designed for use in corporate applications. But, many times, many users need to migrate their SQL Server database to the Azure SQL Database. Because Azure is an intelligent and fully managed relational cloud database that offers the broadest SQL Server engine compatibility.

However, at the time of SQL database to Azure migration, there are chances that we can miss some important steps or information such as trustworthy property, dependent Jobs, Linked Server, Logins etc. The main headache of any conversion is figuring out how to get the information from database and import into the server. Therefore, users need to use a professional tool to transfer SQL Server Database to Azure SQL Database.

One of the best third-party solutions I came up is SysTools SQL to Azure Migration Tool, which allows users to move all the SQL database objects like Tables, Triggers, Stored Procedures etc. to Azure SQL Database. Apart from this, there are many other features of this tool that are discussed below:
 

Key Features of SQL Server to Azure Migration Tool

 
1. Easily Transfer SQL Database to Azure

With the help of this tool, users can perform direct migration from SQL to Azure SQL database. For this, you need to provide all the credentials like server name, database name, username and password etc. After that, one can easily transfer SQL Server database to Azure.

2. Option to Add MDF/NDF Files

The tool provides an option to add MDF & associated NDF files to the software. You can also migrate all the database objects like Tables, Triggers, and Stored Procedures etc. from SQL to Azure.

3. Migrate Corrupted Database

The best part of this utility is that it can convert the corrupted database as well as the healthy file from local SQL server to Microsoft Azure SQL Database. Before migration, it scans the corrupted database files and repair them using either quick or advanced scan options.

4. Quick and Advance Scan Option

While working with this tool, the user can choose the required scan mode depending on the level of corruption out of the following modes:

– Quick Scan: This mode scans minor corrupted SQL files

– Advance Scan: This mode scan major corrupted SQL files

5. Auto Detect SQL Server File Versions

The SQL to Azure migration tool has an option to detect the versions of added MDF or NDF files automatically. For this, you just need to click on the checkbox next to this option.

6. Export Schema Option

The tool provides a feature to save the SQL database schema into Azure in any of the following ways:

– With the Only Schema: It migrates only schema of tables, views etc.

– With Schema & Date: It allows users to transfer both schema and data of all database objects.
 

Free and Licensed versions of SysTools SQL Server to Azure Migration Tool

The tool can be availed in the following two versions:

1. Demo Version: Users can freely download the trial version of software from the SysTools official website. It is available to understand the working of the software in a much better way.

2. Licensed Version: The licensed version of the tool can migrate SQL server database to Azure from MDF or NDF files. Also, it allows you to transfer the schema and schema & data.
 

System Requirements

The licensed version of SQL to Azure migration tool has been tested by the SQL experts. It will evaluate the performance of the software in terms of quality, reliability, security etc.

However, the testing has been performed in below-mentioned environment:

– Operating System: compatible with Windows 10 and all below versions

– Processor: Intel Pentium 1 GHz processor or any equivalent processor

– RAM: Around 2 GB of RAM is necessary

– Hard Disk: At least 100 MB space is required for installation
 

Advantages:

– It can also migrate corrupted or inaccessible SQL database to the Azure database.
– The software can preview all database components before migrating to Azure.
– The SQL Server to Azure migration tool has a simple and user-friendly interface.
 

Disadvantages:

– It is required to have database created on Azure SQL Server Database before migration.
– The Demo version only exports 25 records of each table.
 

Observational Verdict

After considering all the features of SQL Server to Azure Migration tool, I can say that it is a reliable and effective tool for DBAs and Developers to transfer SQL data to Azure without causing any data loss.