SQL Installation Automation

I would like to begin by expressing my enthusiasm for dbatools, a remarkable tool for database administrators (DBAs). If you haven’t already, I highly recommend familiarizing yourself with it by visiting their website: https://dbatools.io/.

In this blog post, I will be sharing a few scripts that can be used to perform a seamless SQL installation using the powerful commands provided by dbatools.

check if system needs a reboot or not

Get-WUIsPendingReboot

First, check if SQL Server is already installed

$SqlInstance = Find-DbaInstance -ComputerName localhost

Set the path to the install files (lets say you have kept the binaries in E:\abhi folder)

Set-DbatoolsConfig -Name Path.SQLServerSetup -Value ‘E:\abhi\SQL_Server_2019-Standard’

create sqlserviceaccount account which will run the SQL services and make it OS admin

$Username3 = “sqlserviceaccount”
$Password3 = “Password”
$SecurePassword3 = ConvertTo-SecureString -String $Password3 -AsPlainText -Force
$User3 = New-LocalUser -Name $Username3 -Password $SecurePassword3 -PasswordNeverExpires

Add sqlserviceaccount user to local administrators group

$Group3 = “Administrators”
Add-LocalGroupMember -Group $Group3 -Member $Username3

And run the installation:

if ($SqlInstance -eq $null) {

ASK which version of SQL to install:

[string]$Version = Read-Host -Prompt ‘Install SQL Server Version (2008R2, 2012, 2014, 2016, 2019)’

Get SA credentials:

$sacredential = Get-Credential

Install SQL Server with providing details like data , log file path , sa credential, mixed mode authentication

Install-DbaInstance -Version $Version -AuthenticationMode Mixed -Feature Engine -SaCredential $sacredential -InstancePath “F:\SQLDATA” -DataPath “F:\SQLDATA” -LogPath “L:\SQLLOGS” -TempPath “T:\TEMPDB” -BackupPath “J:\SQLBACKUPS” -Port 1440 -PerformVolumeMaintenanceTasks -Confirm:$false -enginecredential “sqlserviceaccount” -agentcredential “sqlserviceaccount” -restart
}

Keep learning!!

CP and chmod command in Unix

You can use the cp command for copying files and the chmod command to set permissions.

Here’s an example:

# Copy files from source to destination

$ cp /path/to/source/file.txt /path/to/destination/

Make sure to replace /path/to/source/file.txt and /path/to/destination/ with the actual paths and filenames relevant to your use case.

# Grant read, write, and execute permissions to the owner and read and execute permissions to the group and others

$ chmod 755 /path/to/destination/file.txt

7 corresponds to read (4) + write (2) + execute (1) for the owner.

• 5 corresponds to read (4) + execute (1) for the group.

• 5 corresponds to read (4) + execute (1) for others.

The chmod 755 command grants read, write, and execute permissions to the owner, and read and execute permissions to both the group and others.

Unveiling “su root” and “su enterprise db”

Section 1: su root

The “su root” command is a gateway to the root user, holding unparalleled privileges. While its power is unmatched, wielding it demands caution.

Example:$ su root Password: [Enter root password]

Section 2: su enterprise db

For database administrators, “su enterprise db” (or a similar command) is the key to accessing the enterprise database user.

Example:$ su enterprise db Password: [Enter database user password]

In Place SQL upgrade steps

Steps to perform in place SQL upgrade

1. Ensure your operating system is up-to-date with the latest patches or the recommended OS version.
2. After OS updates, initiate a waiting period before proceeding with the SQL in-place upgrade.
3. Utilize the Database Migration Assistant (DMA) tool to conduct an assessment, checking for potential blockers or issues.

• You can run DMA using a script to automate the assessment. Example:
  "C:\Program Files (x86)\Microsoft Data Migration Assistant\DmaCmd.exe" /Action=Analyze /S /U /F="C:\Path\to\AssessmentReport.xml"

1. Create a comprehensive VM backup for the entire system.

• Use a VM snapshot or backup tool to capture the entire virtual machine state.

1. Back up all system and user databases to safeguard data integrity.

• Script to backup all user databases:
  sql USE master; BACKUP DATABASE [YourDatabase] TO DISK = 'C:\Backup\YourDatabase.bak';

1. Commence the downtime by stopping SQL services.

• You can stop SQL services using SQL Server Configuration Manager or PowerShell scripts.

1. Execute the SQL upgrade by running the setup file, selecting the “Perform an upgrade from the existing version” option.

• Ensure the setup file path is correct and use the /Action=Upgrade command-line parameter.

1. Apply the latest SQL patch level to ensure security and performance enhancements.

• Download and apply the SQL Server cumulative update or service pack.

1. Reboot the server to finalize the upgrade process.
2. Run database maintenance tasks and perform a DBCC CHECKDB to maintain database health.
   • Example maintenance tasks:
   -- Rebuild all indexes ALTER INDEX ALL ON YourTable REBUILD; -- DBCC CHECKDB DBCC CHECKDB ('YourDatabase');
3. Update the compatibility level of all databases to the latest version.
   • Script to change compatibility level:
   ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 150; -- Replace 150 with the desired level
4. Conduct thorough database health checks to verify the system’s stability.
5. Perform application health checks to confirm that applications are functioning correctly.
6. Upon successful completion of the above steps, the in-place SQL upgrade is finished.

Tempdb data and log file in SQL Server

Tempdb is a system database in SQL Server that is used to store temporary data. It serves as a workspace for various operations, and both data and log files are essential for its functioning:

1. Tempdb Data Files:

• Tempdb data files store temporary user data, tables, and internal objects used by SQL Server during query processing.
• Multiple data files can be created to help distribute the workload and improve performance, especially on multi-core processors. This is known as filegroup scaling.
• Common uses of tempdb data files include storing temporary tables, table variables, sorting, and row versioning information for operations like snapshot isolation and online index rebuilds.

1. Tempdb Log File:

• The tempdb log file records all the activities and transactions within the tempdb database.
• While tempdb doesn’t maintain durable data like user databases, it still maintains transactional consistency within itself.
• The log file ensures that if there is a crash or a rollback is required, tempdb can be restored to a consistent state.

In summary, tempdb data files are used to store temporary data that is generated during the execution of queries and operations within SQL Server. The tempdb log file is responsible for recording the transactional activities within tempdb. Proper management and monitoring of tempdb are essential for the overall performance of SQL Server, as contention or improper configuration of tempdb can lead to performance bottlenecks.