Tech Blog

Hello,

Been a while since I posted but here I am!

I recently needed to copy SQL users from one DB to another without changing SIDs etc, while googling how best to do this a colleague just told me to use dbatools.

Well I’m glad I did, they are amazing! If you’re not sure what dbatools is, its PowerShell for SQL!!

Here’s what I did:

Install the tools on my device:

Install-Module dbatools -Scope CurrentUser

Then I simply ran:

Copy-DbaLogin -Source SourceDB -Destination DestDB -force

and thats it.

Hello,

As many of you may know the latest round updates have disabled TLS 1.0 and TLS 1.2

For us that meant either enabling TLS everywhere or using newer methods. Below is a URL to enable:

https://blogs.windows.com/msedgedev/2020/03/31/tls-1-0-tls-1-1-schedule-update-edge-ie11/

A much better option is to install Microsoft® ODBC Driver 13.1 for SQL Server on the clients:

https://www.microsoft.com/en-us/download/details.aspx?id=53339

Then update a whole bunch of ODBC system DSNs.

Thats great but some of our Citrix servers have a lot of system DSNs and I didnt much fancy doing them one by one so here’s some PowerShell to do it for you:

$DsnArray = Get-OdbcDsn -DriverName 'SQL Server' ForEach ($Dsn in $DsnArray) { Remove-OdbcDsn -InputObject $Dsn Add-OdbcDsn -Name $Dsn.Name -DsnType $Dsn.DsnType -Platform $Dsn.Platform -DriverName 'ODBC Driver 13 for SQL Server' -SetPropertyValue $Dsn.PropertyValue }

Boom.

Below is a SQL script that will show how many updates are missing that are classified as critical or security for servers on WSUS:

SET NOCOUNT ON DECLARE @TargetGroup nvarchar(30) 
DECLARE @Days int SELECT @TargetGroup = 'All Computers' 
SELECT @Days = 7 DECLARE @groups AS TABLE (Id uniqueidentifier NOT NULL) 
DECLARE @groupId uniqueidentifier SET @groupId = (
     SELECT ComputerTargetGroupId
     FROM PUBLIC_VIEWS.vComputerTargetGroup
     WHERE vComputerTargetGroup.Name = @TargetGroup )
WHILE @groupId IS NOT NULL BEGIN
     INSERT INTO @groups SELECT @groupId
     SET @groupId = (
         SELECT ParentTargetGroupId
        FROM PUBLIC_VIEWS.vComputerTargetGroup
         WHERE vComputerTargetGroup.ComputerTargetGroupId = @groupId     )
END 
DECLARE @updates AS TABLE (Id uniqueidentifier NOT NULL PRIMARY KEY) 
INSERT INTO @updates SELECT distinct vUpdate.UpdateId 
FROM     PUBLIC_VIEWS.vUpdate WHERE    vUpdate.MsrcSeverity is NOT NULL
        AND vUpdate.defaultTitle like '%Security%'
		OR vUpdate.defaultTitle like '%critical%'
SELECT vComputerTarget.Name as 'Computer Name', COUNT(*) AS 'Missing Updates' 
FROM PUBLIC_VIEWS.vComputerGroupMembership
     INNER JOIN PUBLIC_VIEWS.vComputerTarget 
on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId
     INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup 
on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId
     INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic 
on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId
     INNER JOIN @updates GROUPS
on vUpdateInstallationInfoBasic.UpdateId = GROUPS.Id 
WHERE vComputerTarget.ComputerTargetId = vUpdateInstallationInfoBasic.ComputerTargetId
     AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)
       AND vComputerTargetGroup.Name = @TargetGroup 
GROUP BY vComputerTarget.Name 
ORDER BY 'Missing Updates' DESC

Today I wanted to migrate the WSUS server to a SQL server and as such you have to connect to:

\\.\pipe\MICROSOFT##WID\tsql\query

But I kept getting:

event ID 18456 – Token-based server access validation failed with an infrastructure error… was being generated in the Application event log along with Login failed for user…. server is in script upgrade mode in the C:\Windows\WID\Log\error.log.

Long story short I opened SQL Management Studio as the local admin (not my domain admin) and bobs your uncle.

So today a vCenter Server Service wouldnt start Java was going mental and we kept seeing event IDs 1105 and 1827.  So what this means is our SQL Express instance reached the limits the size of databases i.e. 10GB on SQL Express 2008 R2.

You can confirm this by looking at the size of the VIM_VCDB.MDF file in c:\program files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\MSSQL\Data, if its 10GB then your in trouble.

So the first thing to do is fire up Microsoft SQL Server Management studio and run a standard report against the VIM_VCDB by right clicking the DB > Reports > Standard Reports > Disk Usage by Top Tables:

From here you can see which tables are causing the problems, in my case its dbo.VPX_EVENT_ARG which is massive.

So from here you need to purge the above table:

1. From Microsoft SQL Server Management studio
2. Click databases to expand and select VIM_VCDB > Tables.
3. Right-click the dbo.VPX_PARAMETER table and select Open.Note

   : If you are using SQL Server 2008, right-click the dbo.VPX_PARAMETER table and click Edit Top 200 Rows.
   

4. Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.
5. Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

1. Next run a built-in stored procedure to shink the logs:
2. Go to VIM_VCDB > Programmability > Stored Procedures.

3. Right-click dbo.cleanup_events_tasks_proc and select Execute Stored Procedure. This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.

Now the records have been purged you need to shrink the DB:

1. As before connect using SQL Server Management Studio.

2. Right click on the VIM_VCDB database and New Query, when the white SQLQuery box opens type the below and click ! Execute

dbcc shrinkdatabase ( VIM_VCDB , 5 )

3. It’ll start running and when its finished you should see:

Check the size of the file again and you should be able to start the services.