Check Mirroring Status in SQL Server 2012

We can check mirroring status of the SQL Server 2012 using below command.

 SELECT   
     @@SERVERNAME as Server_Name,  
     DB_NAME(database_id) as Database_Name,   
     mirroring_state_desc,  
     mirroring_role_desc,  
     mirroring_safety_level_desc  
 FROM   
     sys.database_mirroring  
 WHERE   
 mirroring_role IS NOT NULL  

Getting free space information in SQL Server VM

We can get freespace information in a SQL Server VM using below TSQL command.

 SELECT DISTINCT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
   volume_mount_point [Disk],   
   file_system_type [File System],   
   logical_volume_name as [Logical Drive Name],   
   CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes  
   CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],   
   CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]   
 FROM sys.master_files   
 CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)  

Alternate way for FULL OUTER JOIN

Below is an one more way of doing FULL OUTER JOINs involving multiple tables. This gives a simpler approach, instead of having multiple OR conditions in subsequent tables.

1:  DECLARE @a table(id int, data int)  
2:  DECLARE @b table(id int, data int)  
3:  DECLARE @c table(id int, data int)  
4:    
5:  insert into @a values(1, 9), (2, 8),(3,7)  
6:  insert into @b values(2, 6), (3, 5)  
7:  insert into @c values(1, 4), (2, 5)  
8:    
9:  ;WITH CTE_IDs as  
10:  (  
11:  SELECT ID from @a  
12:  UNION  
13:  SELECT ID FROM @b  
14:  UNION  
15:  SELECT ID FROM @c  
16:  )  
17:  SELECT c.ID,t.* FROM CTE_IDs as c  
18:  cross apply  
19:  (  
20:  VALUES((select data from @a where id = c.id),  
21:  (select data from @b where id = c.id),  
22:  (select data from @c where id = c.id))   
23:  ) as t(a_data,b_data,c_data)  

Saving CSV file in UTF8, Compress and Archive them

We had a requirement, where the CSV files generated by SSIS were not in UTF8. We had to resave in UTF8 format. We also had to compress the group of files and archive the original set of files. We achieved that using Powershell.

 $CSVfilesFolder = "C:\CSVFiles\"  
 $CompressFolder = "C:\CSVFiles\Compress"  
 $ArchiveFolder = "C:\CSVFiles\Archive"  
 $filesForZip = Get-ChildItem -Path $CSVfilesFolder -Filter *.csv | Sort-Object LastWriteTime | Select-Object -First $CountofFilesToZip | select fullname | ForEach-Object { $_.FullName }  
 $newFileName = "$($CompressFolder)\GeneratedZipFile.zip"  
 #Encoding to UTF8   
 foreach($file in $filesForZip)  
 {  
   $UTF8Only = New-Object System.Text.UTF8Encoding($false)  
   $FileContent = Get-Content -Path $file  
   [System.IO.File]::WriteAllLines($file, $FileContent, $UTF8Only)     
 }  
 #Generate Zip file  
 $compress = @{  
  Path = $filesForZip  
  CompressionLevel = "Fastest"  
  DestinationPath = $newFileName  
 }  
 Compress-Archive @compress -Force  
 #Archive the files  
 foreach($file in $filesForZip)  
 {  
   Move-Item $file -Destination $ArchiveFolder -Force  
 }  

Maintain timestamp of CSV File

We were having a scenario, where we had to fix few CSV field issues. But, we also wanted to retain timestamp. We used below script to fix the CSV and maintain the timestamp.

 $BulkLoadFolder = "C:\Projects\Sntrax\Bulkload\FilesToFix"  
 $Folder = "C:\FilesToFix\"  
 $filesList = Get-ChildItem -Path $Folder -Filter *.csv | select fullname | ForEach-Object { $_.FullName }  
 foreach($file in $filesList)  
 {   
     $newfile = $file.Replace("FilestoFix","FilestoFix\fixed")  
     #Get lastwritetime for the filename   
     $LastWriteDateTime = Get-ItemPropertyValue -Path $file -Name LastWriteTime  
     #Import file and Export   
     Import-Csv $file | ForEach-Object {  
  if ($_.Field1.Trim() -eq '') {  
         $_.Field1 = '--'  
       }  
       if ($_.Field2.Trim() -eq '') {  
         $_.Field2 = '--'  
       }  
       $_  
  } | Export-Csv $newfile -NoTypeInformation -Force  
     Set-ItemProperty -Path $newfile -Name LastWriteTime -Value $LastWriteDateTime  
 }  

Powershell alert, once server is up and running

 We have got regular server patches every month. When the server is restarted, we have to keep checking whether server is up again. The below powershell script will be helpful, to let us know, once server is up and running.

1:  # Load assembly  
2:  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")  
3:  $ComputerName = "FQDN of Server"  
4:  $ServerOnline = $false  
5:  while($ServerOnline -eq $false)  
6:  {  
7:    if(Test-Connection -BufferSize 32 -Count 1 -ComputerName $ComputerName -Quiet)  
8:    {  
9:     [System.Windows.Forms.Messagebox]::Show("The server $computerName is up now");  
10:     $serverOnline = $true;  
11:     break;  
12:    }  
13:    else  
14:    {  
15:      [System.Windows.Forms.Messagebox]::Show("The server $computerName is offline now");  
16:      Start-Sleep -Seconds 5  
17:    }  
18:  }  

Copy web.config between two web servers

We are having load balancing of web portal, in two different web servers. The web.config has to be same between web portal. If one of them is changed, we want to copy the same to another server. Below script can be used to do the same:

I have put local path just for reference. UNC path can be used in place of local path. 

1:  $strSourceFile = "C:\\dev\\wv02\\test.txt"  
2:  $strDestFile = "C:\\dev\\wv01\\test.txt"  
3:  if ((Get-ChildItem $strSourceFile).LastWriteTimeUtc -gt (Get-ChildItem $strDestFile).LastWriteTimeUtc)  
4:  {  
5:    write-host "$($strSourceFile) is latest than $($strDestFile)"  
6:    copy-item -Path $strSourceFile -Destination $strDestFile  
7:  }  
8:  else  
9:  {  
10:     write-host "$($strDestFile) is latest than $($strSourceFile)"  
11:     copy-item -Path $strDestFile -Destination $strSourceFile  
12:  }