Thursday, July 28, 2016

Checking Server online Status - PowerShell

We managed 100 + servers and we want make sure they come back after monthly maintenance reboot. We will run the following script by confirming the servers are Up. I retrieve servername from a Table called SQLServers from Database - DBA_ServerDW from ServerInstance name SQLTEST.

$servername = invoke-sqlcmd -ServerInstance SQLTEST -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\%'"
ForEach ($server in $servername)
{
   # Ping the machine to see if it's on the network
   $ServerN=$server.ServerName
   $results = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$ServerN'"
   $responds = $false  
   ForEach($result in $results) {
      # If the machine responds break out of the result loop and indicate success
      if ($result.statuscode -eq 0) {
         $responds = $true
         break
      }
   }
         If ($responds) {
      # Gather info from the server because it responds
      Write-Output "$ServerN responds"
   } else {
      # Let the user know we couldn't connect to the server
      Write-Output "$ServerN does not respond"
   }
}

Thursday, July 14, 2016

Tempdb.mdf file takes on Model database initial size

I changed model database size  to 5 Gbs so new user databases will take on Model database size.

USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 5242880KB )
GO

Before restart SQL instance, I have sized tempdb datafiles size to 512 MB



Then I restarted SQL instance and agent. It takes a while to connect to SQL server via SSMS.

Tempdb.mdf file is recreated with 5 Gbs which takes on Model database initial size.



According to MS KB, tempdb should not take on Modeldb.
https://support.microsoft.com/en-us/kb/307487




How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...