Category: SQL Server

ConfigMgr Package Status Reports

Found a nice idea for a report on Eswar Koneti’s blog. That query combined with some info from a blogpost by Jörgen Nilsson will give you two reports to dig into status of packages.

Status of Distribution Points with Package Compliance

SELECT DISTINCT 
	CDR.DPNALPath AS DPNalPath,
	UPPER(SUBSTRING(CDR.DPNALPath,13,CHARINDEX('.', CDR.DPNALPath) -13)) AS ServerName,
	CDR.PkgCount AS Targeted,
	CDR.NumberInstalled AS Installed,
	CDR.PkgCount-CDR.NumberInstalled AS NotInstalled,
	PSd.SiteCode AS ReportingSite,
	ROUND((100 * CDR.NumberInstalled/CDR.pkgcount), 2) AS Compliance

FROM
	v_ContentDistributionReport_DP CDR LEFT JOIN v_PackageStatusDistPointsSumm PSd
	ON CDR.DPNALPath=PSD.ServerNALPath

This report can be linked into the next one (using DPNalPath as a parameter)

Package Compliance on a single Distribution Point

Updated 2013-10-08 Join in tables instead of Select Case on State and PackageType

SELECT
	v_ContentDistribution.State AS StateNo,
	DPStatusInfo.StateName AS State,
	v_ContentDistribution.PkgID AS PackageID,
	v_ContentDistribution.PackageType AS PackageTypeNo,
	SMSPackageTypes.Name AS PackageType,
	SUBSTRING(v_ContentDistribution.Path,CHARINDEX(']', v_ContentDistribution.Path)+1, LEN(v_ContentDistribution.Path) - CHARINDEX(']', v_ContentDistribution.Path)-1) AS PackagePath,
	v_ContentDistribution.SiteCode,
	v_ContentDistribution.SourceVersion,
	v_ContentDistribution.SummaryDate

FROM
	v_ContentDistribution LEFT JOIN DPStatusInfo ON v_ContentDistribution.State = DPStatusInfo.State
	LEFT JOIN SMSPackageTypes ON v_ContentDistribution.PackageType = SMSPackageTypes.PackageTypeID

WHERE DistributionPoint = @DistributionPoint

This report need a parameter for DistributionPoint, to list all in a drop-down, use the query below on the parameter. (If you want to use the report without going thru a link)

SELECT DISTINCT
	ServerNALPath,
	SiteCode + ' - ' + SUBSTRING(ServerNALPath,CHARINDEX(']\\', ServerNALPath)+3, LEN(ServerNALPath) - CHARINDEX(']\\', ServerNALPath)-3) AS DistributionPoint
FROM v_DistributionPoint

Update ConfigMgr packages with Hotfix-information

When you install a hotfix and/or a cumulative update in ConfigMgr you can select the option to let the installer create some packages.

But, those packages are missing some info… For instance Manufacturer and Version.

A quick and dirty SQL update will do the trick.

UPDATE SMSPackages_G
SET
	Version = LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9),
	Manufacturer = 'Microsoft',
	Language = 'All'
WHERE Source LIKE '\\server.domain.com\SMS_ABC\Hotfix%'

This is of course not supported… but afaik it works. :D

If you want to see what would be updated, run this first

SELECT
	PkgID,
	Version AS OrgVersion,
	LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9) AS NewVersion,
	Manufacturer AS OrgManufacturer,
	'Microsoft' AS NewManufacturer,
	Language AS OrgLanguage,
	'All' AS NewLanguage
FROM SMSPackages_G
WHERE Source LIKE '\\server.domain.com\SMS_ABC\hotfix\%'

(And yes, you need to replace servername and sitecode)

SQL in Powershell without cmdlets

I am currently playing around with ConfigMgr 2012 cmdlets… and they only run in X86 (*doh*).

Anyway, I need to query a database with information about some collections and other stuff I need to create… and running SQL Server cmdlets in X86 doesn’t work.

So to get this to work I found a Powershell function that uses SqlClient from .NET instead… I rewrote the function a bit and here is the result:

Function Run-SqlQuery {
	PARAM(
		[string] $Server = ".",
		[string] $Database = "master",
		[string] $Query,
		[Int32]  $QueryTimeout=60
	)
	$sqlConnection = New-Object System.Data.SqlClient.SQLConnection
	$sqlConnection.ConnectionString = "Data Source=$($Server);Initial Catalog=$($Database);Integrated Security=True;"
	$sqlConnection.Open()
	$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($Query,$sqlConnection)
	$sqlCommand.CommandTimeout = $QueryTimeout
	$sqlDataSet = New-Object System.Data.DataSet
	$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlCommand)
	[void]$sqlDataAdapter.fill($sqlDataSet)
	$sqlConnection.Close()

	return $sqlDataSet.Tables[0]
}

Then you can use it like this:

Run-SqlQuery -Query "EXEC SP_HelpDB"
Run-SqlQuery -Query "SELECT Col1, Col2 FROM MyTable" -Database "MyDatabase" -Server "SomeServer" | Format-Table -AutoSize

This can probably be useful if you need to run a SQL command from a box where your’e not sure if you have SQL cmlets or not.

Update ConfigMgr Site Description

I did an install of a secondary site and accidently put in the wrong description…

Anyway, easy to fix with some SQL commands.

If you use this you can see the current sitenames and descriptions (change CM_XYZ to your database name):

SELECT SiteCode, SiteName, SiteServerName
FROM CM_XYZ..SC_SiteDefinition

And here is the way to update it (Change to you database name and the site code of the site you want to change)

UPDATE SC_SiteDefinition
SET SiteName = 'My Site Description'
WHERE CM_XYZ.SiteCode = 'ABC'

FYI: When running this you will se “Updated X rows… Updated Y rows…” and so on.

List MOM2005 agents with IP-adress

A small SQL-script to list all agents on a MOM2005 installation.

USE OnePoint

SELECT Computer.Name AS ComputerName, ComputerAttribute.[Value] AS IP
FROM ComputerAttribute
	INNER JOIN Computer ON ComputerAttribute.idComputer = Computer.idComputer
WHERE ComputerAttribute.idComputerAttributeDefinition = (
	SELECT ClassAttributeID as idComputerAttributeDefinition
	FROM ClassAttribute
	WHERE ClassAttributeName = 'IPAddress')
ORDER BY Computer.Name

Backup av SQL-databaser

Att ta backup på SQL-databaser & loggar är lite lagomt tråkigt.
Gör man det med maintenance-plan’s så får man en miljard (nåja… ett ganska rejält gäng iaf) filer. Kör man det genom någon annan wizard så blir det måttligt bra.

Nu har jag gjort samma backup-script i femtielva varianter och det känns som att det här fungerar riktigt bra.

Vad gör det då? Jo det är tvådelat, ett script för transaktionsloggar och ett för databasbackup.
Det script som är för dbbackup kör man lämpligtvis efter att bandaren har plockat upp filerna på band. Varför? Scriptet kör med “WITH INIT” vilket tömmer filerna och alla transloggar försvinner.
Det andra scriptet kör backup på transloggar på de databaser som är i recovery-model “Full” och inte är satta till “read only”.
Båda scripten kör backup till samma fil vilket gör det mycket enkelt att återläsa, och även att återläsa till en viss tidpunkt.

Databas-backup (Körs lämpligtvis efter bandbackup, en gång per dygn)

-- -------------------------------------------------------------------
--  Funktion: Backup av samtliga lokala databaser
--  Skapat:   2007-05-10 av rikard@ronnkvist.nu
--
--  Filerna namnsätts \\XYZ\DBBackup\SQLSERVER-INSTANS\DBnamn.bak
-- -------------------------------------------------------------------

-- Deklaration av variabler
DECLARE 
	@backupPath sysname,
	@DBname sysname,
	@backupfile sysname

-- Konfiguration
SET @backupPath = '\\XYZ\DBBackup\'

-- -------------------------------------------------------------------
DECLARE myCur CURSOR
	-- Läser ur samtliga lokala databaser (förutom tempdb), sortera systemdatabaser först därefter i namnordning
	FOR SELECT NAME FROM MASTER..SYSDATABASES WHERE NAME NOT IN ('tempdb') ORDER BY SID, NAME
	FOR READ ONLY
OPEN myCur

-- Start av loop
FETCH NEXT FROM myCur INTO @DBname
WHILE (@@FETCH_STATUS <> -1) BEGIN
	-- Sätter sökvägen för backupfilerna
	SET @backupfile = @backupPath + RTrim(REPLACE(@@SERVERNAME, '\', '-')) + '\' + RTrim(REPLACE(@DBname, ' ', '_')) + '.bak'

	-- Skriver info till användaren/loggen
	PRINT ''
	PRINT '--- DB-backup to: ' + @backupfile

	-- Kör backup
	BACKUP DATABASE @DBname TO DISK = @backupfile WITH NAME = 'Scripted backup', DESCRIPTION = 'Full Backup', INIT

	-- Fortsätter loopen
	FETCH NEXT FROM myCur INTO @DBname
END
CLOSE myCur
DEALLOCATE myCur
-- -------------------------------------------------------------------

Translogg-backup (Körs lämpligtvis varje halvtimme mellan 06:00 & 19:00, om det nu är tiderna då användare arbetar i databaserna)

-- -------------------------------------------------------------------
--  Funktion: Backup av transaktionsloggen på lokala databaser
--  Skapat:   2007-05-10 av rikard@ronnkvist.nu
--
--  Filerna namnsätts \\XYZ\DBBackup\SQLSERVER-INSTANS\DBnamn.bak
-- -------------------------------------------------------------------

-- Deklaration av variabler
DECLARE 
	@backupPath sysname,
	@DBname sysname,
	@backupfile sysname

-- Konfiguration
SET @backupPath = '\\XYZ\DBBackup\'

-- -------------------------------------------------------------------
DECLARE myCur CURSOR
	-- Läser ur samtliga lokala databaser (förutom tempdb & model), sortera systemdatabaser först därefter i namnordning
	FOR SELECT NAME FROM MASTER..SYSDATABASES WHERE NAME NOT IN ('tempdb', 'model') ORDER BY SID, NAME
	FOR READ ONLY
OPEN myCur

-- Start av loop
FETCH NEXT FROM myCur INTO @DBname
WHILE (@@FETCH_STATUS <> -1) BEGIN
	-- Kollar att det är recoverymodel full och att den inte är skrivskyddad
	IF (convert(sysname,DatabasePropertyEx(@DBname,'Recovery')) = 'FULL') AND (convert(sysname,DatabasePropertyEx(@DBname,'Updateability')) = 'READ_WRITE') BEGIN
		-- Sätter sökvägen för backupfilerna
		SET @backupfile = @backupPath + RTrim(REPLACE(@@SERVERNAME, '\', '-')) + '\' + RTrim(REPLACE(@DBname, ' ', '_')) + '.bak'

		-- Skriver info till användaren/loggen
		PRINT ''
		PRINT '--- Log-backup to: ' + @backupfile

		-- Kör backup
		BACKUP LOG @DBname TO DISK = @backupfile WITH NAME = 'Scripted backup', DESCRIPTION = 'Logbackup', NOINIT
	END

	-- Fortsätter loopen
	FETCH NEXT FROM myCur INTO @DBname
END
CLOSE myCur
DEALLOCATE myCur
-- -------------------------------------------------------------------

Det som är (mest) imponerande är att det finns kommentarer! :-)

Bygga om index i en SQL-DB

Så här bygger man om alla index i en SQL-databas.

DECLARE @TableName varchar(255) 

DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.TABLES 
WHERE table_type = ‘base table’ 

OPEN TableCursor

	FETCH NEXT FROM TableCursor INTO @TableName 
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		DBCC DBREINDEX(@TableName,‘ ‘,90) 
		PRINT ‘Reindex index on table: ‘ + @TableName
		FETCH NEXT FROM TableCursor INTO @TableName 
	END 

CLOSE TableCursor 

DEALLOCATE TableCursor

Uppdatera GroomDays i SCDW

För att minska ner storleken på SystemCenterReporting-databasen kan man dra ner på antalet dagar man sparar data.

Genom att köra det här SQL-scriptet så drar man ner till 7 dagar.

USE SystemCenterReporting

Declare @Groomdays int
Select @Groomdays=7

exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays

Backup/Restore av SQL-databas

Jag har nog svarat femtielva gånger på varför användarna inte följer med när man flyttar en SQL-databas från en server till en annan

Så här är det.
– Användarna finns i databasen MSDB
– Rättigheterna finns i den “vanliga” databasen

Så för att göra det enkelt… om du ska flytta en liten databas med ett fåtal användare, exempelvis ett datastore för en citrix-farm, gör så här:
På gamla servern:
– Ta backup av databasen
På nya server:
– Skapa upp en användare med samma namn som på gamla servern
– Kör restore av databasen
– Kicka igång Query Analyzern
– Se till att du är ansluten mot rätt databas (Finns en drop-down i mitten av toolbaren) eller så använder du use-kommandot
– Kör:

use DatabasensNamn
sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern  = 'minfinauser'

Ja, det är bra att ändra minfinauser mot rätt namn :-)

Nu ska den ha länkat ihop din nya användare med rätt rättigheter i din gamla databas.