SQL

Removing a Orphaned Virtual Machine from vRealize Automation

**** ATTENTION !!! ****
**** Please be sure to BACKUP any related databases

 

The following steps provide information on removing virtual machines from VMWare vRealize Automation (vRA).

These steps are to be used when the following conditions exist:

  • A virtual machine that is being managed without being deleted from the endpoint.
  • You want to manually remove the machine from the endpoint.

 

vRA Application Steps:

Log into vRA – https://vcac.yourdomain.tld using your-domain\*your-user-id*

  1. Click the Infrastructure Tab
  2. Click Machines > Reserved Machines
  3. Search for Service Name (e.g. VRA-FAQ360)
  4. Delete the associated service

SQL Database Steps:

  1. Connect to SQL Database Server: VRA-SQL
  2. In “Object Explorer” window, Locate database vCAC
  3. Backup the vCAC database
  4. Expand the vCAC database
  5. Under Programmability > Stored Procedures, locate ‘dbo.usp_RemoveVMFromVCAC’
  6. Execute Procedure and apply ID of the Multi-Machine Service (e.g. VRA-FAQ360)
  7. Repeat this step for each instance

Notes:

The store procedure may look like the following:

USE [vCAC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_RemoveVMFromVCAC]
@MachineName = N’VRA-FAQ360′

SELECT ‘Return Value’ = @return_value

GO

Install Microsoft SQL on Linux – Ubuntu Server

I recently had the pleasure of installing Microsoft SQL Server on Linux – Ubuntu Server. This was a very straight-forward installed and just works. The following steps are what were taken to install and configure this server.

My Setup:

  • Ubuntu 17.10 Server – VMware Template
  • Network Connectivity
  • SQL Server Management Studio 17 – Testing connectivity to SQL Server

Prerequisites:

  • Ubuntu Linux Server – Memory: 3.25, Disk Space: 6GB, CPU (x64): 2 Cores
  • Internet Access – Offline Installs are also possible
  • Root or SU Access
  • Time – 5-6 Minutes

Steps:

  1. Log into Ubuntu Linux server via console or SSH (Preferred), su into root
  2. We need to import the repository GPG Keys by first downloading and adding it with he following command: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
  3. Next we register the repository by entering: add-apt-repository “$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)”
  4. Next we need to upload the repository list and install SQL with the following commands: apt update | apt install mssql-server -y
  5. After the SQL Server package has completed installing.  You will be instructed to run mssql-config setup to setup the SQL Server version you will be installing, in addition to password credentials.  This is done by issue the following command: /opt/mssql/bin/mssql-conf setup
  6. Optional – Open your firewall if enabled to allow for SQL’s TCP/1433 from remote hosts.
  7. Test connecting to your newly install SQL Server via SSMS.
  8. Done!

Screenshot:

Video:

Tech Short: TSQL to find Consumed Memory

Poking around in Microsoft SQL Server looking for a way to give me some statistics on memory usage, primarily consumed memory.

My first attempt was using ‘dbcc memorystatus’.  This gives me a good snapshot of the current memory status of the SQL Server. It will take me sometime to fully understand and parse the output.

So I kept looking for more details on how to show the consumed memory. And that is where I found the following query that gives me just what I am looking for.

 

Declare @MemTable Table
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );
INSERT INTO @MemTable EXEC ( ‘DBCC MEMORYSTATUS’ );

SELECT top 1 Name,convert(decimal(16,2),value) / 1024/1024 ‘MBUsed’
FROM @MemTable
WHERE RTRIM(LTRIM(name)) = ‘Working Set’;

So if your looking for just the working set here you go

Thank you Jason Brimhall for the tip

O365: Forefront Identity Manager & Office 365 DirSync Failing

I encountered an issue where both Forefront Identity Manager and Office 365 DirSync both failed to start.

My investigation of this after I received an email from @MicrosoftOnline.com which had informed me that Windows Azure Active Directory did not register a synchronization attempt from the Directory Sync tool.

First

I attempted to do was start the Microsoft Online Services Directory Synchronization Service. This had failed because depends on Forefront Identity Manager Synchronization Service which was also no longer starting.

Second

I attempted to start the Forefront Identity Manager Synchronization Service this failed with the following message:

Verify that the service account has permissions to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Forefront Identity Manager\2010\Synchronization Service

If the problem persists, run setup and restore the encryption keys from backup.

Third

After my verification I attempted I uninstalled the Office 365 DirSync along with Forefront Identity Manager and SQL which were all installed.  This time around I unable to even install the Office 365 DirSync

All three of my attempts had failed.

So what changed?

I rebooted the system; and after it had resumed the services which worked seemed to no longer function.

Then it *clicked* after much investigation and review.  The question I did not ask.  Could Office 365 exist on the same system that’s also running ADFS.  I soon found out the answer is ” *NO* “.

The Directory Synchronization tool cannot be installed on Active Directory Federation Service.

So I uninstalled the Office 365 DirSync, along with SQL. Followed by the removal of the ADFS Role from the server.

After the restart I installed the Office 365 DirSync again and configured it as I have done before and all is working once again.

And now I and you all know 

I hope this post help you and saves you some time.  I spent a day working on this and waiting for Microsoft to call me.  I seems like I have resolved this issue on my own; once again.

Summery

If your using Office 365 DirSync do NOT enable the ADFS Role if you do, you run the sure chance of breaking your working Office 365 DirSync.

Environment: Windows Server 2012 R2 Update 1 (x64)

– Jermal

SQL Report Server ERROR: The number of requests for domain\jermsmit has exceeded the maximum number allowed for a single user

It’s always simple when you see a error message you know.  This time its with SQL Server Report Server.  Error Message: The number of requests for domain\jermsmit  has exceeded the maximum number allowed for a single user

What does that mean?

Well to keep it simple.  The user is running a report which requires far more request that the report server is configured to handle.

So what do you do about this?

One thing would be to isolate the issue and find out why so many requests are being made and perhaps re/wright the report.  Hahaa! Joke!  That doesn’t happen often now does it.

So what will happen is this:

On the report server navigate to the following path:  C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer  *your install path may be different*

Locate the XML configuration file rsreportserver.config

Open the configuration file with notepad.

Press Crtl F and search for MaxActiveReqForOneUser you may get a result such as ‘<Add Key=”MaxActiveReqForOneUser” Value=”20″/>

Change the value to the number of request being made to remove this issue.  Then again if you didn’t correct the above throw some crazy value to it like 256 and call it a day.

What the trade off?

You just use more system resources:  cpu, memory.

More details

Being that these type of request should happen quickly; this may be a sign that something is hogging CPU and SQL time running reports. You should evaluate this rather than just tossing resources at the issue.

Ref:  RSReportServer Configuration File

– Jermal