Disk block size with Powershell

Recently I was working on documenting SQL Servers using the SQL Powerdoc which is from codeplex. One of the interesting thing which I saw was in the output was about allocation of storage size for SQL Servers.

As a best practice storage allocation size should be 64 KB. Please refer this Microsoft article for more information about this, click here

So now the next question is how do I check my disk block size?

Let’s do the PowerShell way, here is the script which you can use to check the block size

Get-WmiObject -Class Win32_volume -Filter "FileSystem='NTFS'" | Select-Object Name, Lable, BlockSize | Format-Table -AutoSize

sample output


Now the question is how do I do it for all the SQL servers in my network?

PowerShell makes things more simple. First get the server list for which you’re going to get the block size and save it in a text file. Now open PowerShell and run the below script

#Inside the below quotes provide the complete path with the server list file name#
$ServerList = Get-Content 'C:\ServerList.txt' 
Get-WmiObject -Class Win32_volume -Filter "FileSystem='NTFS'" -ComputerName $ServerList | Select-Object PSComputerName, Name, Lable, BlockSize | Format-Table -AutoSize

sample output

Cheers,
Naveen

PowerShell on Linux and Open Source!

BIG news from Microsoft "PowerShell is now available in Linux and OS X". Microsoft has also announced that PowerShell will be an open source shell command line. So going forward with PowerShell you can automate things in different platform with same code. That's interesting !! click here to know more about this

Useful PowerShell resources
https://msdn.microsoft.com/powershell
https://blogs.msdn.microsoft.com/powershell/

Cheers,
Naveen

Getting started with SQL Server 2016

It's been a long time since I wrote a blog. There are lots of big things happening around with the technology.

SQL Server 2016 is out officially few months back and it has got lots of new features. I did get a chance to explore some of them and it was dam cool. In future blogs I will cover each topics as I learn.

Click here to download your FREE SQL 2016

Here is the list of some new features introduced in SQL 2016. For complete list click here

-Enhanced in-memory OLTP performance
-Query Store
-Temporal Tables
-Always Encrypted
-Row Level Security
-Dynamic Data Masking
-Support of JSON
-Integration with R Programming

Cheers,
Naveen

SCRIPT : To Get Disk Utilization Report

Recently I was working on a request to get a report on disk utilization for about 100 servers.
For sure I don’t want to login to each server and check the disk usage manually. This can be done using T-SQL, but still I was looking for a consolidated report which will give me a clear picture.
Powershell is the best to get this done.
Here is the script I wrote to get the consolidated disk usage report.

===================================================
#Set the path where the html report will be created
Set-Location D:\Powershell\
$a = "<style>"
$b = "<H2>Disk Usage Report</H2>"
$a = $a + "BODY{background-color:Cyan;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 2px;padding: 1px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 2px;padding: 1px;border-style: solid;border-color: black;background-color:palegoldenrod}"
$a = $a + "</style>"
#Place the list of servers for which report is required and update the text file path accordingly
$ServerList = Get-Content "D:\Powershell\serverlist.txt"
Foreach($ServerLists in $ServerList)
{
Get-WMIObject Win32_LogicalDisk -ComputerName $ServerList | Select-Object PSComputerName,DeviceID, DriveType, `
@{name='FreeSpaceInGB';Expression={[math]::truncate($_.freespace / 1GB)}}, `
@{Name='SizeInGB';Expression={[math]::Truncate($_.Size/1Gb)}}, `
@{Name="FreePercentage";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.size/1gb))}} | Where-Object DriveType -EQ 3 `
| convertto-html PSComputerName,DeviceID,FreeSpaceInGB,sizeInGB, FreePercentage -head $a -Body $b | Out-File Report.htm
}
===================================================

Cheers,
Naveen

How to change MySQL port

In this post we will see how to change MySQL instance port.
By default MySQL will be listening to port 3306. From security perspective it’s not a good idea to run your instance with default port. To change the port all you have to do is edit the configuration file.

Windows -> my.ini
Linux -> my.cnf

How to locate your configuration file?

In windows
-Open services.msc console. Look for your MySQL service
-Right click MySQL service and open ‘Properties’.
-Under ‘Path to execute’ you can find the my.ini location

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

-Open the configuration file and search for port, by default its 3306

-I have changed it to 3333.

You have to restart MySQL service to make the changes effective.
Let’s check if MySQL instance is listening to 3333 port. To check the port you can use below command from MySQL client.

SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'PORT';

In Linux

Configuration will be located in /etc/mysql/my.cnf. You have to open and follow the same process.

Note: Take backup of configuration file before making any changes

Cheers,
Naveen

How to check MySQL version in Windows

In this post I will show you different ways to get the version of currently installed MySQL in your machine.

-Using mysqladmin, run the below command (highlighted in red) in cmd

D:\>mysqladmin -u root -p version

-using MySQL client

-Using MySQL service properties (path in service properties in taken installation directory).

-Using MySQL version query

Select @@version (or) Select version()

 

Cheers,
Naveen

How to open MySQL session from any location in Windows

In this blog post I will show you how to open a MySQL session from any location using command prompt.
By default if you try to open a session, you will get below message.

The same command works from the bin directory. In my case path is <C:\Program Files\MySQL\MySQL Server 5.6\bin>.

The solution for this is simple, just go ahead and add bin directory path in system variable path.  Follow the below steps to make the entry

1) Go to ‘Computer’ properties and Select ‘Advanced system settings’.
2) Navigate to ‘Advanced’ tab and select ‘Environment Variables’.

3) Under system variables, look for ‘Path’ variable and click ‘edit’.

4) Now at the end of existing variable value use a semicolon (;) and without leaving space paste the bin directory path with a backslash (\) at the end.

I have highlighted (in red) the path below which was pasted in my case.

…..Doctrine extensions for PHP\;C:\Program Files\MySQL\MySQL Server 5.6\bin\

That’s it we are done. By adding this path we will be able to connect from any location. Open command prompt and now you can run MySQL session from any location. I tried it from D drive this time and it worked :-)

Hope this helps

Cheers,
Naveen