PostgreSQL – .pgpass file

.pgpass file in a user’s home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user’s profile).

This file should contain lines of the following format:
hostname:port:database:username:password

You can follow below steps to connect to PostgreSQL or PostgreSQL compatible tool or database systems.

Step 1: Created the .pgpass file. Below command will create the hidden .pgpass file in the home directory.

vi ~/.pgpass

Step 2: Add the connection details with the instnace, port, database, user and password information in the below format. You can also use the wild card character like * as well.

PostgreSQLInstance1:5432:mydatabase:myuser:mypassword
*:*:mydatabase:myuser:mypassword

Step 3: On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. Changed file mode to 600 as below

chmod 600 ~/.pgpass

Step 4: Export the PGPASSFILE file
export PGPASSFILE=~/.pgpass

Step 5: Test the connection. PGSQL -w (lower case) option will not prompt for password and will connect using the password from the .pgpass file.

Example:
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”

id
—-
3
1
2

(3 rows)

PGSQL -W (upper case) will prompt for the password even specified in .pgpass file.
Example :
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”
Password for user imuser:

id
—-
3
1
2

Advertisements

SQL Server 2016 : DBCC CheckDB with MaxDop Option

What is Max Degree of Parallelism?
Degree of parallelism is the number of processors that are assigned to run a single SQL statement. SQL Server manages to detect the best degree of parallelism for each query to generate the execution plan. SQL Server may use all the procedure in case if it required. If you do not want SQL Server to use all of the installed processors in your operations, you can limit the use of processors by configuring the processor value to a lower number than the actual number of processors installed. For instance, if a server has four processors and you want to use only three for parallelism, then you should set the max degree of parallelism option to 3.

The default option, 0, uses the actual number of processors installed in the system for the parallel execution plans of queries and index operations.

sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

MAXDOP
Maxdop is a query hint. When you want to use a specific number of processors for an individual query or index operation, you can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to the execution of a query that contains the MAXDOP query hint or index option.

SQL Server 2016 introduces a new option to limit the number of processors assigned for the DBCC CHECKDB statement, overriding the instance level MAXDOP configuration. You can use this option in case if you want to use all the compute power of the server or you want to use the minimum compute power of the server.

In the below example I am specifying the MAXDOP hint as 5 which will internally asks Database Engine to generate the execution plan to use the 5 processors.

select @@SPID
DBCC CHECKDB (AdventureWorks2012) WITH MAXDOP = 5;

You can check the number of processors user by SQL Server by executing the below query.

select Session_id , scheduler_id  from sys.dm_os_tasks
where session_id = <SPID>

You can execute the below script to identify the optimal MAX DOP setting and set the MAX DOP as using SP_Configure procedure.

select case
      when cpu_count / hyperthread_ratio > 8 then 8
      else cpu_count / hyperthread_ratio
      end as optimal_maxdop_setting
      from sys.dm_os_sys_info;
 
-- Script to set the maxDOP
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', <OutPut of the above script>;
GO
RECONFIGURE WITH OVERRIDE;
GO

 

T-SQL Script to get the Symantec NETBACKUP Version and Patch Information

You can execute below script to get Symantec NETBACKUP version and patch information.

set nocount on

CREATE TABLE #OUTPUT(
	[SQLInstanceName] [nvarchar](128) NULL,
	[WindowsServerName] [varchar](30) NULL,
	[NetBackupClientVersion] [varchar](255) NULL,
	[NetBackupPatchVersion] [varchar](255) NULL,
	[NetbackupStatusCheck] [varchar](28) NULL
) 

declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Veritas\NetBackup\CurrentVersion\',
  @value_name='VERSION',
  @regkeyval=@value output

set @value =isnull(@value,'-')

declare @Pvalue varchar(255)
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Veritas\Patches\NetBackup\',
  @value_name='PatchLevel',
  @regkeyval=@Pvalue output

set @Pvalue =isnull(@Pvalue,'-')

if @value <> '-'
insert into #OUTPUT(SQLInstanceName,WindowsServerName,NetBackupClientVersion,NetBackupPatchVersion,NetbackupStatusCheck)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as NetBackupClientVersion 
,@Pvalue as NetBackupPatchVersion
,'NetBkup is installed' as NetbackupStatusCheck 
else
insert into #OUTPUT(SQLInstanceName,WindowsServerName,NetbackupStatusCheck,NetBackupClientVersion,NetBackupPatchVersion)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'NetBkup nt installed' as NetbackupStatusCheck
,'0' as NetBackupClientVersion
,'0' as NetBackupPatchVersion 

select * from #OUTPUT

T-SQL Script to check Powershell Version

You can write $host command on powershell prompt to check powershell version or you can execute the below T-SQL script to check the powershell version.

declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\PowerShell\1\PowerShellEngine',
  @value_name='PowerShellVersion',
  @regkeyval=@value output


set @value =isnull(@value,'-') 

if @value <> '-'
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as PSClientVersion 
,'PS is installed' as PSStatusCheck 
else
insert into #OUTPUT(SQLInstanceName,WindowsServerName,PSStatusCheck)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'PS nt installed' as PSStatusCheck 

MongoDB Post Installation Configuration

On the completion of MongoDB installation https://sqldbpool.com/2015/02/24/mongodb-installation-on-windows-server/, next step is to configure the MongoDB.

Create a MongoDB config file using any text editor (e.g. NOTEPAD) and save it as MONGO.config into bin directory.

##to store data
dbpath=C:\Program Files\MongoDB 2.6 Standard\Data
 
##all output loging
logpath=C:\Program Files\MongoDB 2.6 Standard\Log\mongo.log
 
##log read and write operations
diaglog=3

Start MongoDB Server, make sure to open the command prompt as Administrator. Go to MongoDB bin directory and write the below command. It will start the MongoDB Server and keep the command prompt running, don’t close it.

mongod.exe --config mongo.config

MongoDBServer

To connect to MongoDB open another window of command prompt, browse the bin directory and type the  MONGO command.
startmongo

You can also create the window service of MongoDB by executing below command. Make sure to open the command prompt as Aadministrator, otherwise you will get the access denied error.

mongod --config mongo.config --install

mongoservice

Script to list out SQL Logins and Database User Mappings

You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings.

Syntax
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0

use master
go
exec sp_msloginmappings 'sa', 0

Image2

use master
go
exec sp_msloginmappings 'sa', 1

Image1

In case you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or powershell. You can use the below script.

 create table #loginmappings(  
  LoginName  nvarchar(128) NULL,  
  DBName     nvarchar(128) NULL,  
  UserName   nvarchar(128) NULL,  
  AliasName  nvarchar(128) NULL  
 )  
 
 insert into #loginmappings
 EXEC master..sp_msloginmappings
 
 select * from #loginmappings
 
 drop table #loginmappings