Thursday 28 March 2019

Enable AlwaysOn Feature using powershell



I have noticed many might be enabling or disabling SQLServer Alwayson using GUI which is time-consuming and also tedious. Once you have failover cluster manager setup on a collection of the node. You can use the below commands to enable Alwayson feature and restart SQL server on all the instance which are part of the failover clustering. 

Default Instance 

$Nodes  =('ULDB01')
$InstanceName='MSSQLSERVER'
foreach($node in $Nodes ){
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$node\DEFAULT" -Force
Get-service "$InstanceName" -Computername $node | stop-service -passthru -force
Get-service "$InstanceName" -Computername $node | start-service -passthru
Get-service "SQLSERVERAGENT" -Computername $node | start-service -passthru
}

Named Instance

$Nodes  =('ULDB01')
$InstanceName='MSSQL'
$NamedInstance='$MON'
foreach($node in $Nodes ){
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$node\DEFAULT" -Force
Get-service "$InstanceName$NamedInstance" -Computername $node | stop-service -passthru -force 
Get-service "$InstanceName$NamedInstance" -Computername $node | start-service -passthru
Get-service "SQLAGENT$NamedInstance" -Computername $node | start-service -passthru
}

    


    

Error Configuring the file share witness using powershell



Issue:

There can errors when configuring the file share witness using PowerShell commands. The simplest command will be to remove "\" in the last and retry the same command.

Resolution:

Set-Clusterquorum -NodeFilesharemajority "\\system\clustername"

Error Adding Node 'xxxxxxx' to the Cluster



When you have an issue creating a new cluster, the below steps need to be followed. 















Import-Module FailoverClusters
Clear-ClusterNode -Name ULDB1 -Force
Clear-ClusterNode -Name ULDB2-Force
Clear-ClusterNode -Name ULDB3 -Force

Clear-ClusterNode -Name ULDB4 -Force

Wednesday 27 March 2019

Get SQL Server Backup History With Compression Ratio



There are many occasions where backup compression is not enabled on the SQL Server by default. There are a lot of third-party tools like IBM Tivoli, Netbackup, Netvault which we can use to backup databases.  It is always handy to have a script to check which are compressed using the SQL compression which was introduced from SQL Server 2005 and later versions.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
bs.database_name, 
bs.backup_start_date, 
bs.backup_finish_date, 
bs.expiration_date, 
CASE bs.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
bs.backup_size, 
bmf.logical_device_name, 
bmf.physical_device_name, 
bs.name AS backupset_name, 
bs.description,
bs.compressed_backup_size/1024/1024 AS 'Compressed_Backup_Size_MB',
bs.backup_size/1024/1024 AS 'Backup_Size_MB',
CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time],
100-(CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, bs.compressed_backup_size) /CONVERT (FLOAT, bs.backup_size))*100)) 'Compression Ratio'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1)   
ORDER BY 
bs.database_name 

Wednesday 13 March 2019

Get Database Information


Use the below T-SQL script to get the database information.

SELECT  
SERVERPROPERTY('InstanceName') AS InstanceName,
        d.name AS 'Database_Name',
        d.create_date AS 'Create_Date' ,
        d.[compatibility_level] AS [Compatibility_Level],
CAST(((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) AS DECIMAL(38,2))  AS 'Total_Database_Size_MB',
        d.state_desc AS 'State_Description',
d.recovery_model_desc AS 'Recovery_Model',
CASE
              WHEN SERVERPROPERTY('ProductMajorVersion')='10' THEN 'SQL Server 2008'
              WHEN SERVERPROPERTY('ProductMajorVersion')='11' THEN 'SQL Server 2012'
              WHEN SERVERPROPERTY('ProductMajorVersion')='12' THEN 'SQL Server 2014'
              WHEN SERVERPROPERTY('ProductMajorVersion')='13' THEN 'SQL Server 2016'
              WHEN SERVERPROPERTY('ProductMajorVersion')='14' THEN 'SQL Server 2017'
              ELSE 'NONE' 
             END AS  'SQL Version',
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS 'Edition',
'PROD' AS 'Environment'
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE d.database_id >4
GROUP BY d.name,d.create_date,d.[compatibility_level],d.state_desc,d.recovery_model_desc
ORDER BY d.name