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

Stored Procedure to CRASH your SQL Instance

Few days back I leanrt about a configuration option in SQL Server called 'scan for startup procs'.
Scan for startup procs option is used to execute stored procedures during SQL Instance startup. By default this option is not enabled. You can enable it using ‘sp_configure’, below is the command. SQL Service needs to be restarted to make 'scan for startup procs' configuration effective. I will also be enabling ‘XP_CMDSHELL’ for this demonstration.

EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
EXEC SP_CONFIGURE 'scan for startup procs',1
GO
RECONFIGURE
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE

Now it’s time to do some bad things. Run the below script, which will create a stored procedure named ‘Destroy’ in master database and that will be added to scan for startup procs. This SP will get executed during the SQL Instance startup. 

USE MASTER
GO
CREATE PROCEDURE DESTROY
AS
EXEC XP_CMDSHELL 'NET STOP MSSQL$SQL2012'
GO
EXEC SP_PROCOPTION @PROCNAME = 'DESTROY',
@OPTIONNAME = 'STARTUP',
@OPTIONVALUE = 'ON'

In above script change the SQL instance name according to your instance type.

Hope you know what happens if this gets executed :-)

Now go ahead and stop/start or restart your SQL Instance. You will not be able to start your SQL Instance.  If you look at the error log, you’ll find below messages.

Once the recovery is completed scan for startup procedure is made and SP is executed.

So now how to come out from this situation?
Start SQL in single user mode (/m) and remove this procedure from startup/drop this procedure/disable this configuration.  Let’s have a look at the SQL error log.

We can see only recovery part is completed and no startup procedure is executed.

To check the list of procedures added to startup procedure use below command

SELECT NAME FROM SYS.PROCEDURES WHERE IS_AUTO_EXECUTED=1

 

 

Now we’ll remove this SP from the startup proc by running below command and check the status.

EXEC SP_PROCOPTION @PROCNAME = 'DESTROY', @OPTIONNAME = 'STARTUP', @OPTIONVALUE = 'OFF'

 

 

 

 

 

Cheers,

Naveen

PS Testing this script is at your own risk. Please test/play with this in your test environment.

How to setup database mail with your gmail account

In this blog post let’s see how to setup database mail with your gmail account.
Make sure Database Mail XPs is enabled for your instance. To check the status run the below command

EXEC SP_CONFIGURE 'show advanced options',1
RECONFIGURE
EXEC SP_CONFIGURE 'Database Mail XPs'

In my case it is disabled, let’s go ahead and enable it.

EXEC SP_CONFIGURE 'Database Mail XPs',1
RECONFIGURE
EXEC SP_CONFIGURE 'Database Mail XPs'

Now in object explorer pane
-Expand Management Node
-Right click Database Mail -> click ‘Configure Database Mail’ -> next
-In Select configuration Task, select ‘Set up Database Mail by performing the following task:’

-Enter the profile name and description.

-Now Add SMPT Account to create your database mail account. Few things which you should take care is

• Provide a valid E-mail address, SMTP Server Name and Port.
• For SMTP authentication select ‘Basic authentication’. User name will be your email address and password will be your gmail account password.

Click here, to get more information about Google SMTP Server settings

-Select whichever profile you need (Public/Private profiles available) and if required you can set it to default profile as well.

-Next window will show the system parameters. If necessary you can customize it, I will proceed with default.

-Click next and finish!

You have successfully configured database mail. Now let’s send a test mail to validate. Right click ‘Database Mail’ -> ‘Send Test E-Mail…’. Provide your E-mail ID and click ‘Send Test E-Mail’.

If you have provided a valid/correct email address you'll soon receive a test email.


Cheers,
Naveen

SCRIPT : To take backup of user permiissions, object level permissions and roles

You can use this script to to extract user permissions in a database with object level permission and roles. It is recommended to get the output in text format, so that it will be easy to have a copy.

PRINT 'DATABASE NAME : ' +DB_NAME()

SET NOCOUNT ON

/*CREATE DATABASE ROLES*/

SELECT 'CREATE ROLE [' + a.name + '] AUTHORIZATION [' + b.name + ']' from sys.database_principals a

INNER JOIN sys.database_principals b ON a.owning_principal_id = b.principal_id

where a.type = 'R' and a.is_fixed_role <> 1

GO

------------------------------------------------------------------------------------------------------------------

/*ASSIGN ROLES TO USER*/

SELECT --rm.role_principal_id,

'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')

+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'

FROM sys.database_role_members AS rm

ORDER BY rm.role_principal_id

GO

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER BY usr.name

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION FOR A OBJECT*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

Where obj.name = 'ULTIMATES_CURRENT'

ORDER BY usr.name

SET NOCOUNT OFF

Always good to know your Guest (SQL Guest User)

Few days back came across an interesting issue, which was new for me. Application owner buzzed me and raised concerns on security issue.

App.Owner: We have some serious issue with our database security. I fear wrong people having elevated access to our data.
Me: Ok, let me have quick look and get back to you.
App.Owner: Yes please. We need to fix it now.
Me: Did a quick check and everything seems to be fine. You’re ID with db_owner and other users with read access to the database.
App.Owner: I was also under the same impression. But today came to other users were also are also able to modify the data. That’s not at all good.
Me: hmm that’s strange. I will do a complete check to figure out the leakage.
App.Owner: Thanks. I will wait to hear from you.
Me: Okay. Ahh Found the suspect. All those privileges were getting transmitted from guest user. I could see someone had mistakenly provided extra permission to guest user, which is not at all required for this environment. Now things are back to normal. Please have a look from your end and let me know you if you have any questions
App.Owner: Great! Thanks for fixing this. Have a nice day
Me: You too, have a nice day.

In my case issue was with guest user permission. Let’s see the same in demo

-First we will create a new database named ‘TESTDB’. Then will check the status of guest account.

Note: If guest user is enabled in Model database, the same will get applied to newly created database

-Now we will create a SQL Login, a new table ‘EPL’ and insert 3 records.

We have just created a SQL Login it’s not mapped to any of the database and instance level also no permission is provided.

Now let’s try accessing the data in TESTDB using UserA.

As expected, ‘UserA’ is not able to fetch the data from TESTDB. Now let’s try enabling and providing read and write permission to ‘Guest’ user in TESTDB. You cannot enable/disable Guest user through GUI, it can be done only with T-SQL.

To Enable Guest Account:
 
USE [DATABASE_NAME]
GO
GRANT CONNECT TO GUEST
GO
 
To Disable Guest Account:

USE [DATABASE_NAME]
GO
REVOKE CONNECT TO GUEST
GO

Command completed successfully. Now let’s try

 
 So now UserA is able to read and insert records in TESTDB. The same task can be performed by all users associated with the instance. Guest user when enabled will authorize access for logins which are not mapped to the database.

Things to know about Guest user

• It’s always recommended to disable guest user for user database, unless you have any specific/special requirement.
• Guest user cannot be dropped, but can be disabled/enabled.
• Guest user cannot be disabled for Master and Tempdb database.
• Msdb database guest user should not be disabled. If you have any plans to do so, please read this.

Cheers,
Naveen

Automating Standalone SQL Installation

Few days back I started working on Automating or Standardizing SQL Installation. Ultimate goal is to reduce the manual work. Unattended installation using configuration file will help us to achieve this. 

First let's see how to get our configuration file. First time you have to run the setup manually and pass all the parameters.  You have move till “Ready to install” page in the installation wizard. There you will get the configurationfile.ini location.

Copy the configurationfile.ini to the location where SQL setup file is placed (It’s not mandatory to do this. If you place the configuration file in different location, same location should be passed while executing the setup).

So now your configuration file is ready. Make the below changes in configuration file to perform unattended installation.

   -Change QUIET="False" to QUIET="True"

 

   -Remove UIMODE="Normal" from configurationfile.ini

Now save and close the file. Please note, configuration file will not store/have password details of any of the accounts (SQL Service Account, SQL Agent Account and SA Password etc.). So we have to pass this information explicitly while running setup in command line. And also we should pass a parameter /IACCEPTSQLSERVERLICENSETERMS=”True” to accept the SQL Software license.

Below is the script to do unattended installation.

***********************************************************************************

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=”True” /SAPWD=”SaAccountPassword” /SQLSVCPASSWORD=”SQLServiceAccountPassword” /AGTSVCPASSWORD=”AgentAccountPassword”

***********************************************************************************

Note: SA password is not required for Windows Authentication

You can create a batch file and run it to perform unattended SQL installation. This will be advantageous if you have to in setup SQL in multiple servers with same configuration.

But what if you have a requirement to install SQL in multiple server with different instance name and collation settings?

Above script will not work in that case. However if you could go and make required changes to the configuration file every time will work. But again that will be time consuming task to make changes in all servers. Other option would be passing the parameters explicitly while running the setup. So here also lot of editing will be required.

To overcome all these difficulties, I wrote a small dynamic script where we have to define few parameters based on our requirement and those parameters will be taken as input while running the setup.

I have just added below parameters in my script to be taken as input.

      -SQL Instance Name

       -SQL Collation

       -SQL Service Account Name and Password

       -SQL Agent Account Name and Password

       -SA Account password

       - To accept SQL Server License Terms

Assuming in most of the case install directory, root instance directory and other directory will be standard. So initially whatever you have passed in the configuration file will be taken.

You may need to change the below script according to your requirement.

***********************************************************************************

@echo off

color 02

echo===========================================

echo Unattended SQL Installation

echo===========================================

color 05

set /p LI=Do You Accept SQL Server License Terms(True/False):

set /p IN=Enter SQL Instance Name:

set /p SYS=Enter Sysadmin Account Name:

set /p SA=Enter SA Account Password:

set /p SVN=Enter SQL Service Account Name:

set /p SVP=Enter SQL Service Account Password:

set /p AGN=Enter SLQ Agent Account Name:

set /p AGP=Enter SQL Agent Account Password:

set /p COL=Enter SQL Collation:

color 09

echo===========================================

Echo Starting SQL Server Installation....

echo===========================================

date/t

time/t

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=%LI% /INSTANCENAME=%IN% /INSTANCEID=%IN% /SQLSYSADMINACCOUNTS=%SYS% /SQLCOLLATION=%COL% /SAPWD=%SA% /SQLSVCACCOUNT=%SVN% /SQLSVCPASSWORD=%SVP% /AGTSVCACCOUNT=%AGN% /AGTSVCPASSWORD=%AGP%

color 02

echo Batch file completed

date /t

time /t

***********************************************************************************

Now all you have to do is prepare your configuration file and save the above script in .bat format. Place both the files where SQL Setup is placed.

That’s it, open command prompt with “Run as administrator”, run the batch file, provide required inputs and your SQL installation will start. 

Below is the screenshot of SQL installation in my system.

 

You will get the start and end time of installation. To make it bit sparkling added few colors in between the code.  So on each phase of batch execution you would notice a change in color!!!

To learn more about Unattended SQL Installation click here.

Hope this helps in Automating SQL Installation :-)

Cheers,

Naveen