Handmatig opschonen vCenter 4.x database

In een eerdere blogpost meldde ik dat het met vCenter 4.x mogelijk is om met een 'Database Retention Policy' de hoeveelheid bewaarde task- en event data te beheren. Dit is een handige functie om te voorkomen dat de vCenter database volloopt met gegevens waar je toch niets meer mee doet.

Dat mag dan wel erg handig zijn, maar stel dat je een oude database wilt herstellen en daar helemaal geen task- en event data in wilt hebben? Hoe los je zoiets op?

Met vCenter 2.x was er een cleanup database script waarmee dit kon. Deze controleert op de kolom VER_ID in de VPX_VERSION tabel. Bij vCenter 4.x heeft deze kolom een waarde groter dan 5 en dan krijg je de volgende foutmelding:

FATAL ERROR: This script was designed for a previous version of VirtualCenter.

Maar niet getreurd, er is ook een cleanup script beschikbaar voor vCenter 4.x. Deze is beschikbaar op de VMware knowledge base site via het volgende URL: http://kb.vmware.com/kb/1025914

Het is overigens wel een beetje jammer dat ze de meegeleverde stored procedure cleanup_events_tasks_proc niet geparameteriseerd hebben. Dat zou ik een mooiere oplossing vinden dan een los script dat je van de website moet downloaden.

SQL 2008 Express backups met PowerShell

Als je gebruikt maakt van de Express editie van SQL Server, dan zul je het moeten doen zonder Maintenance Plans. Hoewel dit een zeer eenvoudige tools is om te gebruiken, is er voor SQL 2008 Express een goed alternatief beschikbaar; PowerShell!

Beter goed gejat dan slecht verzonnen was mijn insteek bij deze oplossing. Ik heb gebruik gemaakt van de volgende artikelen:

• Running SQL Server PowerShell


• Backup SQL Server Databases with a Windows PowerShell Script


• Retrieve Default SQL Server Backup Folder using PowerShell

Daarnaast had ik zelf een one-liner om bestanden ouder dan een bepaalde tijd op te ruimen. Als je dit combineert, dan krijg je het onderstaande resultaat.

#
# Loads the SQL Server Management Objects (SMO)
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


$assemblylist =
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"


foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

Push-Location
cd $sqlpsPath
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

$s = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

$Date = Get-Date
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
foreach ($db in $dbs)
{
if($db.Name -ne "tempdb") #We don't want to backup the tempdb database
{
$dbname = $db.Name
$dt = get-date $Date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_" + $dt + ".bak", "File")
$dbBackup.Initialize = $true
$dbBackup.SqlBackup($s)
}
}
$CutOff = 7
$bkdir | get-childitem | where {$_.PSIsContainer -eq $false -AND $_.LastWriteTime.date -lt ($Date).AddDays(-$CutOff)} | Remove-Item


vCenter database cleanup

Met vCenter 2.5 draaide ik regelmatig een SQL query die de historie van events en tasks opruimde. Deze procedure had ik van de VMware Knowledge Base, artikel nummer 1000125.

Onlangs upgrade ik een vCenter server naar versie 4.1 en liep daarna door de instellingen. Het viel me toen ineens op dat er onder de noemer 'Database Retention Policy' een functie beschikbaar is die hetzelfde doet.

Deze benadering is nog beter dan het SQL script, want om die uit te kunnen voeren moest eerst de vCenter service gestopt zijn.

Controle beveiligingsinstellingen SQL Server

Tijdens een security audit kwam ik twee controles tegen die ik wel de moeite vond om even te bloggen. Ze liggen misschien wel erg voor de hand, maar daarom misschien des te meer een reden om ze eens tegen jouw eigen infrastructuur aan te houden.

1. Logins zonder wachtwoord
   select name from sys.sql_logins where password_hash is null
   Als deze query geen resultaat geeft, dan kun je gerust adem halen. ;-)
   
2. Logins die geen gebruik maken van de account policy
   select name from sys.sql_logins where type='S' and is_policy_checked <> '1'
   

Tip: Gebruik de systeem stored procedure sp_MSforeachdb om deze sql opdrachten over alle databases heen te draaien.

Wijzigen schema voor vCenter database objecten

Als je bij het installeren van Virtual Center niet zo goed hebt opgelet, dan zou het kunnen dat alle database objecten onder een nieuw schema worden aangemaakt.

Dit is op zich geen bezwaar als je een geroutineerde DBA'er bent, maar een reguliere beheerder wil bij het uitvoeren van SQL opdrachten nog wel eens vergeten om van tevoren een 'EXECUTE AS LOGIN' opdracht te geven om in het juiste schema terecht te komen.

Als oud-beheerder van Pre-SQL 2005 systemen wil mij dit ook nog wel eens overkomen, dus vroeg ik mij af of het mogelijk was om achteraf alle Virtual Center objecten naar het vertrouwde dbo schema te verplaatsen.

Na enig zoekwerk in de online documentatie van SQL server bleek dat met het commando 'ALTER SCHEMA' een object verhuisd kan worden. Maar hoe weet je nu welke objecten verhuisd moeten worden? Voor Virtual Center 2.5 databases heb ik proefondervindelijk vastgesteld dat dit alleen objecten zijn van het type USER_TABLE, SQL_STORED_PROCEDURE en VIEW.

Als ik dit vertaal naar een SQL query dan krijg je het volgende resultaat:

select 'ALTER SCHEMA dbo TRANSFER ' + '['+SCHEMA_NAME(schema_id)+'].['+name+']' FROM sys.objects (nolock)
where type_desc in ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')
and is_ms_shipped = 0
and schema_id = schema_id ('vul hier uw schemanaam in')

Waarschijnlijk kan het nog makkelijker, want feitelijk genereer ik hier alleen de commando's die aan SQL server gevoed moeten worden voor de daadwerkelijke verhuizing. Ik kan me voorstellen dat iemand die handiger is met SQL queries dit ook rechtstreeks kan.

Zodra alle objecten netjes zijn verhuisd naar het dbo schema, vergeet dan niet om met het commando 'ALTER USER' het standaard schema van de gebruiker op dbo te zetten. Na deze handeling kan de Virtual Center service weer worden gestart en zou alles naar behoren moeten werken.

Update 2 maart 2010: Het gebruik van schema's anders dan dbo zorgt ook voor problemen bij een upgrade van VI3 naar vSphere. Zie KB1011386 voor meer informatie.

Herstellen resource pools door middel van SQL server en PowerCLI

Ooit per ongeluk DRS uitgezet op een VMware cluster? Onlangs gebeurde dit bij mijn huidige opdrachtgever en als gevolg van deze actie waren alle resource pools verdwenen. Gelukkig waren er maar 20 pools en konden we deze met de hand weer aanmaken, echter de toewijzing van virtuele machines (> 200) aan deze pools was wel een uitdaging.

Gelukkig werd er van de vCenter database regelmatig een backup gemaakt, dus de informatie was beschikbaar. Hoe deze te herstellen was wel een uitdaging. Deze procedure omschrijft de stappen die we hebben uitgevoerd om weer tot de originele configuratie te komen.

1. Herstellen vCenter database

   Localiseer de backup die je eerder maakte en voer een restore uit onder een andere naam dan de originele database. Zo kunnen beide databases naast elkaar draaien.
   

2. Haal de lijst van resource pools uit de VPX_ENTITY tabel

   Voer de volgende query uit om een lijst van resource pools uit de herstelde database te halen:
   select * from VPX_ENTITY (nolock)
where TYPE_ID = 4
and name != 'Resources'
   Controleer de output en exporteer deze lijst vervolgens door middel van een export wizard naar een CSV bestand.
   

3. Aanmaken resource pools

   Het aanmaken van resource pools gaat het eenvoudigst door het eerder gemaakte CSV bestand te importeren in PowerCLI met het Import-CSV CMDlet. Gebruik hiervoor de volgende code:
Import-Csv –Path FileName | foreach {
New-Resourcepool -Location (Get-ResourcePool -Location ( Get-Cluster ClusterName ) -Name Resources) -Name $_.ColumnName
}
   

4. Ophalen resource pool configuratie

   Naast de namen is het ook wenselijk om reserveringen, shares en dergelijke te configureren. Deze waarden zijn niet in aparte kolommen opgeslagen, maar samengevoegd in een XML bericht in de kolom CONFIG_SPEC. Haal deze informatie op met de volgende query:
   select ID.NAME, RP.CONFIG_SPEC
from VPX_RESOURCE_POOL RP (nolock)
join VPX_ENTITY ID (nolock)
on RP.ID = ID.ID
where ID.TYPE_ID = 4
and ID.NAME != 'Resources'
order by ID.NAME
   In mijn situatie was het minder werk om deze informatie handmatig over te nemen in plaats van de XML berichten te parsen. Voor complexere omgevingen is het handiger om dit te automatiseren. Dit zou vanaf SQL 2005 met XQuery moeten kunnen, maar hier heb ik helaas nog niet mee gewerkt. :-(
   

5. Lijst virtuele machines en resource pools maken

   Om te zorgen dat alle virtuele machines weer in hun oorspronkelijke resource pool worden geplaatst moet de relatie tussen VMs en resource pools worden opgehaald uit de database. Gebruik hiervoor de volgende query:
   select ID2.NAME AS VM_NAME, ID1.NAME AS RP_NAME
from VPX_VM VM (nolock)
join VPX_ENTITY ID1 (nolock)
on ID1.ID = VM.RESOURCE_GROUP_ID
join VPX_ENTITY ID2 (nolock)
on ID2.ID = VM.ID
order by ID2.NAME
   Controleer de output en exporteer deze lijst vervolgens door middel van een export wizard naar een CSV bestand.
   

6. Virtuele machines verplaatsen

   Het eerder gemaakte CSV bestand kan ten slotte gebruikt worden om alle virtuele machines in de juiste resource groups te zetten. Gebruik hiervoor de volgende powershell code:
   
Import-Csv –Path FileName | foreach {
Get-VM -Name $_.ColumnNameVM | Move-VM -Destination (Get-ResourcePool $_.ColumnNameRP) -RunAsync
}
   

Voor omgevingen met namen die niet-ASCII bevatten is het misschien handiger om niet met VM namen, maar met ID's te werken. Dit omdat de gegevens in de database met een andere codering wordt opgeslagen dan in de vCenter GUI of PowerCLI.

Een ander alternatief is om de urlencode functie van System.Web .NET class te gebruiken. Zie voor meer informatie hierover de volgende link: http://www.microsoft.com/technet/scriptcenter/topics/winpsh/convert/escape.mspx

VMware Virtual Center database cleanup

Tijdens de upgrade van Virtual Center krijg je de keuze om historische data zoals tasks, events en performance data wel of niet te bewaren. Ik had het echter fijn gevonden als een deel van de data bewaard kon blijven. Zo maak je namelijk wel weer opslagcapaciteit beschikbaar, maar heb je toch nog een deel van je historische gegevens.

Wellicht komt dit ooit nog eens in een volgende versie van Virtual Center, maar voor nu is er ook een andere oplossing in de vorm van een SQL script. Dit script en de werking ervan is te vinden op de knowledge base van VMware op het volgende url: http://kb.vmware.com/kb/1000125. Er is een versie beschikbaar voor zowel SQL server als Oracle.

Het komt er in het kort op neer dat je door middel van een zogeheten 'cutoff date' kunt instellen hoeveel dagen historie je wilt bewaren. Met het instellen van de variabele DELETE_DATA (waarde 0 of 1) kun je bepalen of je alleen een rapport wilt krijgen, of ook de data daadwerkelijk wilt verwijderen.

Mijn suggestie is om periodiek, of tenminste vóór iedere update van Virtual Center even een cleanup uit te voeren.

Restoren van 'in use' MSSQL database

Mocht je net zoals ik geregeld een SQL database moeten restoren, dan ken je wellicht het verschijnsel dat deze actie stopt met de volgende foutmelding: "Exclusive access could not be obtained because the database is in use".

Mijn standaard reactie op deze melding was het opvragen van de actieve users met sp_who en deze met het kill commando van de server trappen. Er zijn echter alternatieven om dit slimmer en sneller te doen.

Mijn eerste gedachte was om de systeem tabel sysprocesses op te vragen en vervolgens met een loopje de actieve processen te killen.Echter na enig zoekwerk op Google naar alternatieven kwam ik een betere oplossing tegen, namelijk het in single user mode zetten van de database. Hierdoor worden alle actieve users uitgelogd en kan de restore zonder problemen gestart worden. Helaas is deze oplossing pas beschikbaar vanaf SQL server 2000, maar eerdere versies zijn gelukkig een snel uitstervend ras.

De T-SQL opdracht gebruik ik voor het in single user mode zetten van een database:

ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Windows builtin groups gebruiken voor SQL server login

Zou je binnen SQL server een builtin group rechten willen geven om in te loggen, dan werkt dit niet via de Enterprise Manager. Je selecteert de groep en ziet in het venster MachineNaam\GroepNaam staan. Zodra je op OK klikt zul je de volgende foutmelding krijgen: "Windows NT user or group 'MachineNaam\GroepNaam' not found. Check the name again. (Microsoft SQL Server, Error: 15401)".

Dit blijkt een bug te zijn in de Enterprise Manager. Voor builtin groups kun je dus beter een sp_grantlogin opdracht geven in de Query Analyzer. De syntax voor deze opdracht is:

EXEC sp_grantlogin 'Builtin\GroepNaam'.