SQL – Run SQL Server BLOG

14th February 2024

Add read rights to all existing and new user databases

The following script add read user rights for every user databases and system model databases for the new databases to readeruser login.

USE [master]
GO
CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'


EXEC sp_executesql @statement


FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

USE [master]

CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

DECLARE @dbname VARCHAR(50)

DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR

LOCAL FAST_FORWARD

FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','msdb','tempdb','distribution')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname

END

CLOSE db_cursor

DEALLOCATE db_cursor

19th October 202319th October 2023

Export a huge table to csv text file

The most easiest and quick method even for large table using the bcp:

bcp dbname.dbo.tablename out T:\temp\table5.txt -S localhost -T -c -t, -a 32768

1	bcp dbname.dbo.tablename out T:\temp\table5.txt -S localhost -T -c -t, -a 32768

if you use unicode add: -w

11th June 202311th June 2023

DBATools Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.

If you get the error:

Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.

You can set in dbatools:

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register

1	Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register

# Set these defaults for all future sessions on this machine
Set-DbatoolsInsecureConnection

# Set defaults just for this session
Set-DbatoolsInsecureConnection -SessionOnly

# Set these defaults for all future sessions on this machine

Set-DbatoolsInsecureConnection

# Set defaults just for this session

Set-DbatoolsInsecureConnection -SessionOnly

dbatools sql The certificate chain was issued by an authority that is not trusted.

14th April 202314th April 2023

SQL Server using Active Directory Kerberos authentication on RedHat linux

This is an SSMS screenshot where a SQL user and two (windows) domain users connected to SQL Server:

SQL Server on Red Hat Linux using kerberos AD authentication

some hint if you want to deploy it:
– at first you have to join the linux server to the Active Directory
sudo realm join SQLPLAYGROUND.COM -U ‘azureadmin’ -v
– kinit and klist help you to check kerberos tickets
– you can use create -k /var/opt/mssql/secrets/mssql.keytab only in Ubuntu
– you have to use ktpass /princ MSSQLSvc/REDHATSQL2019.SQLPLAYGROUND.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SQLPLAYGROUND\sqluser /out mssql.keytab -setpass -setupn /kvno /pass in Red-Hat linux

7th December 20227th December 2022

Index usage statistics

Do you have unused index? Do you have ineffective index? How many times was it used for select or update?
Is it used by scan , seeks or lookups?
Index usage statistics :

-- index usage statistics
SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE d.database_id = (DB_ID(N'DataBaseName'))   and t.name='TablesName'    -- Database Name and Table
ORDER BY user_updates DESC

-- index usage statistics

SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*

FROM sys.dm_db_index_usage_stats ius

JOIN sys.databases d ON d.database_id = ius.database_id

JOIN sys.tables t ON t.object_id = ius.object_id

JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id

WHERE d.database_id = (DB_ID(N'DataBaseName')) and t.name='TablesName' -- Database Name and Table

ORDER BY user_updates DESC

14th September 202216th September 2022

How to start a stored procedure when SQL server start?

You can start a stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.

EXEC sp_procoption @ProcName = N'<procedure name>'   
    , @OptionName = 'startup'   /*  The only value for option is startup. */
    , @OptionValue = 'on';      /* (true or on) or off (false or off)     */


EXEC sp_procoption @ProcName = N'<procedure name>'     
    , @OptionName = 'startup'
    , @OptionValue = 'off';

EXEC sp_procoption @ProcName = N'<procedure name>'

, @OptionName = 'startup' /* The only value for option is startup. */

, @OptionValue = 'on'; /* (true or on) or off (false or off) */

EXEC sp_procoption @ProcName = N'<procedure name>'

, @OptionName = 'startup'

, @OptionValue = 'off';

Is there any autostart stored procedure on your SQL Server?
You can query:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

SELECT ROUTINE_NAME

FROM MASTER.INFORMATION_SCHEMA.ROUTINES

WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

25th April 202225th April 2022

Replace a MS SQL Database in AlwaysOn AG with DBATools

There is a database in AG and you need to drop it and restore from backup (which is sometimes a transition from developer to prod )
You don’t need any prerequisite for cluster nodes and AG names.
The script find the instance in local windows server and find the db AG nodes and listener.
So finally you need the database name and the location of the backup folder which can be an Ola backup solution folder.

$db="AdventureWorks"
$BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks"

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }
#find AG:
$AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db}
$SQLListener = Get-DbaAgListener -SqlInstance  $SqlServersOnline[0]  | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup}

# connect to listener to get primary and secondary
$availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup
$PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name
$SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name
$AvailabilityGroup = $AGGroup.AvailabilityGroup
# remove db from AG-s

Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db
Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false

# check restore script with -OutputScriptOnly 
# Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace   -MaintenanceSolution -OutputScriptOnly   
# usefull paratmeters if needed:  -RestoreTime '2022-01-26 07:15:00' -WithReplace  -OutputScriptOnly -MaintenanceSolution
Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace  -MaintenanceSolution # -OutputScriptOnly 


# check and set recovery model if needed
if ( (Get-DbaDbRecoveryModel  -SqlInstance $PrimaryReplica  -Database $db).recoverymodel -eq "Simple")   `
    {  Set-DbaDbRecoveryModel  -SqlInstance $PrimaryReplica  -Database $db  -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL } 

# add database to primary AG 
Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic

$db="AdventureWorks"

$BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks"

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }

#find AG:

$AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db}

$SQLListener = Get-DbaAgListener -SqlInstance $SqlServersOnline[0] | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup}

# connect to listener to get primary and secondary

$availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup

$PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name

$SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name

$AvailabilityGroup = $AGGroup.AvailabilityGroup

# remove db from AG-s

Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db

Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false

# check restore script with -OutputScriptOnly

# Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution -OutputScriptOnly

# usefull paratmeters if needed: -RestoreTime '2022-01-26 07:15:00' -WithReplace -OutputScriptOnly -MaintenanceSolution

Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution # -OutputScriptOnly

# check and set recovery model if needed

if ( (Get-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db).recoverymodel -eq "Simple") `

{ Set-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL }

# add database to primary AG

Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic

17th March 2022

Setup.exe error with using ConfigurationFile.ini

SQL server setup error with using setup.exe /CONFIGURATIONFILE=D:\Install\ConfigurationFile.ini

Error message:
Error result: -2054422506
Result facility code: 1420
Result error code: 22

Please check the ini file:

Reason:
; Setup will not display any user interface.
QUIET=”True”
; Setup will display progress only, without any user interaction.
QUIETSIMPLE=”False”
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
UIMODE=”Normal”

Possible Solution :
QUIET=”False”

27th January 202210th May 2022

SQL AO AG failover in cluster group and check the synchronization status

Get info from Cluster:

Get-Cluster
Get-ClusterNode
Get-ClusterGroup
Get-ClusterResource
Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView
Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics  | Sort-Object cluster,ownernode,name | Out-GridView

Get-Cluster

Get-ClusterNode

Get-ClusterGroup

Get-ClusterResource

Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView

Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics | Sort-Object cluster,ownernode,name | Out-GridView

Failover general example, you can use Invoke-DbaAgFailover for SQL server:

# Invoke-DbaAgFailover dbatools powershell command to failover

#  Switch the all cluster group to the another node in verbose:
# do NOT do in production environment:
Get-ClusterGroup | Move-ClusterGroup -verbose


#  Switch the cluster to the particular Node:
# do NOT do in production environment:
#  Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve


# move one group example:
Move-ClusterGroup "Available Storage"
Move-ClusterGroup "Cluster Group"

# Invoke-DbaAgFailover dbatools powershell command to failover

# Switch the all cluster group to the another node in verbose:

# do NOT do in production environment:

Get-ClusterGroup | Move-ClusterGroup -verbose

# Switch the cluster to the particular Node:

# do NOT do in production environment:

# Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve

# move one group example:

Move-ClusterGroup "Available Storage"

Move-ClusterGroup "Cluster Group"

DBATools makes your life happy, you can find the SQL server instances and you can failover them.

To find all of the SQL instances in a cluster server . To Check AG before and after the failover, and failover the SQL Server AO AG :

# cluster::::::
# (Get-ClusterNode).name | Find-DbaInstance
# find SQL server instances in the cluster nodes
$SqlServers = (Get-ClusterNode).name | Find-DbaInstance
# remote cluster server example:
# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance
$SqlServers
$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }
$SqlServersOnline
if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red  -BackgroundColor Yellow}
# failover GUI
Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif
# instance ellenorzes
Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

# cluster::::::

# (Get-ClusterNode).name | Find-DbaInstance

# find SQL server instances in the cluster nodes

$SqlServers = (Get-ClusterNode).name | Find-DbaInstance

# remote cluster server example:

# $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance

$SqlServers

$SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" }

$SqlServersOnline

if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red -BackgroundColor Yellow}

# failover GUI

Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif

# instance ellenorzes

Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView

if you have to wait a lot for synchronization you can check how many servers are synchronized.

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }
do { Write-Host; Write-Host "*********************************************************************************************";`
    $Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline;     Start-Sleep -Seconds 1.0 ; `
    Start-Sleep -Seconds 0.5 ; `
    Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. "  ; `
    Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString();  `
    Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `
} `
while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" }

do { Write-Host; Write-Host "*********************************************************************************************";`

$Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline; Start-Sleep -Seconds 1.0 ; `

Start-Sleep -Seconds 0.5 ; `

Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. " ; `

Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString(); `

Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; `

} `

while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 );

6th January 20226th January 2022

What is my default backup BUFFERCOUNT and MAXTRANSFERSIZE Data Transfer Options?

Error message: BackupIoRequest::ReportIoError: write failure on backup device \\filer\sharename\.. bak Operating system error 8(Not enough memory resources are available to process this command.).

It may you want a better performance for backup (less cpu and shorter backup time, so you can tune the BUFFERCOUNT and MAXTRANSFERSIZE

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
–Data Transfer Options BUFFERCOUNT = { buffercount | @buffercount_variable } | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

Books Online gives the following definition for these two options:

BUFFERCOUNT = { buffercount | @ buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.

Based on the values that you provide or do not provide, SQL Server specifies the size contiguous buffers that it will use to perform the backup. This is of utmost importance in 32-bit environments as large amount of contiguous memory allocation can prove to be fatal to the non-Buffer Pool region of the SQL Virtual Address Space. This can cause your backups to fail. When SQLVDI is being used, this is of utmost importance because we cannot change the MAXTRANSFERSIZE after the VDI Configuration has been completed. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of volumes on which the database files reside on.

You can use Trace Flag 3213 to review your backup/restore configuration parameters while performing a backup/restore operation. I shall proceed to show you how specifying the incorrect BUFFERCOUNT values or not providing it can prove to be fatal.

So how to get the buffercount?

DBCC TRACEON (3605); 
DBCC TRACEON (3213); 
BACKUP DATABASE WideWorldImporters TO DISK = 'WideWorldImporters.bak';
DBCC TRACEOFF (3213); 
DBCC TRACEOFF (3605);

DBCC TRACEON (3605);

DBCC TRACEON (3213);

BACKUP DATABASE WideWorldImporters TO DISK = 'WideWorldImporters.bak';

DBCC TRACEOFF (3213);

DBCC TRACEOFF (3605);

Sample result:

out of memory backup BUFFERCOUNT and MAXTRANSFERSIZE