Tuesday, June 25, 2019

Connecting to vCenter from PowerShell

For a few years now I have been pulling data from the vCenter SQL Server database about my SQL Server virtual machines and storing that data into my own database.  After systems did the last upgrade, vCenter no longer stores the information in a SQL database. I asked them how to connect and they suggested that I use the PowerShell library that VMware provides. This is how I got it to work.
NOTE: I am only going to be using Get commands when I connect. You can change settings or start and stop VMs and even hosts from the tools if you have the proper permissions so know what a command does before you run it.  I don't believe you can use the -whatif parameter with any of the commands so be extra careful.

Installation

First I need to install the library, also known as VMware PowerCLI. I tried downloading the install package but it is an older version. After some web searching, I found that you can get it from PowerShell Gallery
You can get the most current pre-release version using this code. NOTE: I ran into some issues using pre-release versions.
Install-Module -Name VMware.PowerCLI
If you want to install a specific version, you can specify the version number. Check this PowerShell Gallery page for version numbers. I am going to install the current version listed on that page
Install-Module -Name VMware.PowerCLI -RequiredVersion 6.5.4.7155375
If you find you need to remove the module you can do so with this command. I had to use this to uninstall an older version and install a newer one
get-module -Name VMware.PowerCLI -ListAvailable | uninstall-module -force

Getting Connected

Now that I have it installed I want to see what I have available and then I will connect to a vCenter server. To list the modules that were installed use this command
get-module -ListAvailable |where-object {$_.name -like 'VMWare*'}
On my machine I get this list back.






You could then use Get-Command -Module [Module Name] to list out all the function and CmdLets for each module. Feel free to test that out but we are going to be using the VMware.VimAutomation.Core module today to get some information.

First we are going to connect to a vCenter server. We need to import the module first then we can connect (Remember to change the parameters for your environnment)
Import-Module -Name VMware.VimAutomation.Core
Connect-VIServer "vCenterServer" -User "DFA_Primary\MYUSERNAME" -Password "MYPASSWORD"
In my environment I got the following error message:

Connect-VIServer : 6/25/2019 2:18:48 PM Connect-VIServer Error: Invalid server certificate. Use Set-PowerCLIConfiguration to set the value for the InvalidCertificateAction option to Prompt if you'd like 
to connect once or to add a permanent exception for this server.
This is because we use a self-signed certificate. You need to execute this command once to fix this:
Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false
After I executed this command, I was able to use the Connect-VIServer command.

Basic Commands

Now that we are connected we can use some basic commands to get inventory information. First I am going to list all of the hosts with some of their properties
Get-VMHost |select-object Name, ConnectionState, PowerState, NumCPU, MemoryTotalGB | Format-Table
If you want to see all the properties of just one host you can do it like this
#This gets basic info on a host
Get-VMHost |where-object {$_.name -eq "astdc-esxi02.dimensional.com"} |select-object -Property *
 
#This will get you even more info but you have to supply a -VMHost paramater
Get-VMHostAdvancedConfiguration -VMHost "astdc-esxi02.dimensional.com"
Now lets do the same thing for virtual machines. First we will list them 
Get-VM
Then we can dig in and see all the available properties of a particular virtual machine
Get-VM |where-object {$_.name -eq "VMNAME"} |select-object -Property *
These are just some basic commands to get started but they give me the data that I need to collect the inventory information I used to be able to collect directly from the old SQL Server database.

Wednesday, May 15, 2019

Issue with sys.servers and the distribution database in SQL 2016


First post in six months, I'd like to think I have been busy but maybe I just haven't had anything new to add to the SQL server conversation. Hopefully this helps someone else as I have been working with MS support for 2 weeks now on this issue and I just figured it out today. (Do I get my support dollars back?)

We have been running an agent job for years now to pull data from our replication distributors about the subscribers. It runs the following code against the distribution database

SELECT DISTINCT ms.subscriber_id,
        s.name AS subscriber,
        ms.subscriber_db,
        ms.subscription_type
FROM distribution.dbo.MSsubscriptions AS ms WITH(NOLOCK)
JOIN master.sys.servers AS s WITH(NOLOCK)
        ON (ms.subscriber_id=s.server_id)

It returns a list of subscribers and the local databases. Something like what I have below, there are two different subscribers who have different numbers of subscriptions and local subscription databases.

subscriber_id
subscriber
subscriber_db
subscription_type
9
SubscriberA
Database1
1
9
SubscriberA
Database2
1
9
SubscriberA
Database3
1
30
SubscriberB
Database1
1
30
SubscriberB
Database2
1

We implemented a SQL 2016 distributor about a year ago and everything worked fine until we added a new subscriber last month, lets call it SubscriberC. After the subscriber was added the job started failing with an error message like this:

Msg 20032, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 52 [Batch Start Line 11]
'LinkedServer-PRD' is not defined as a Subscriber for 'PublisherA'.

Linkedserver-PRD is a linked server i setup on the distributor using a CNAME and the data source for that linked server is not the same server as SubscriberC. I excluded the 2016 distributor from the collection so the job would run without issue and began troubleshooting. Now when I run my query from above I get this output

subscriber_id
subscriber
subscriber_db
subscription_type
9
SubscriberA
Database1
1
9
SubscriberA
Database2
1
9
SubscriberA
Database3
1
30
SubscriberB
Database1
1
30
SubscriberB
Database2
1
31
LinkedServer-PRD
Database1
1

I checked sys.servers and sure enough server_id 31 is LinkedServer-PRD and my new subscriber, SubscriberC is server_id 32. I must point out that replication was working with no error and replication monitor showed all the correct server names.

At this point I opened a ticket with Microsoft and we have been going back and forth for a few weeks now. The support engineer sent me a new query yesterday but it still used sys.servers so the output was still showing LinkedServer-PRD. I asked him if there was another table with the subscriber name in it and he mentioned MSsubscriber_info but that it didn't have an id columns in it, just names. I thought to myself that there must be another table in the distribution database with the data in it so I started querying random tables until I found this one


SELECT *
FROM distribution.dbo.MSreplservers AS ms

BAM! There were the server names with the proper IDs
srvid
srvname
8
PublisherA
9
SubscriberA
30
SubscriberB
32
SubscriberC

Now I can update my original query to this and get the data I need:

SELECT DISTINCT ms.subscriber_id,
        s.srvname AS subscriber,
        ms.subscriber_db,
        ms.subscription_type
FROM distribution.dbo.MSsubscriptions AS ms WITH(NOLOCK)
JOIN distribution.dbo.msreplservers AS s WITH(NOLOCK)
        ON (ms.subscriber_id=s.srvid)

I was about the update the code that our job uses but decided to check our SQL 2014 distributors to make sure the code works there and unfortunately I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.msreplservers'.

It seems that the table was added in SQL 2016. I don't have any SQL 2017 servers to test it on but I bet it is there as well. Now I need to go back and refactor my query to use the new table for 2016 and sys.servers for 2014 and lower but at least the mystery is solved.

That's it for now. I plan to start posting more and hopefully do better than the six posts I did last year. Maybe I can get back up to the glory days of 2011 when I had 10, although I should probably go back and review all those for accuracy. I have another MS ticket open right now dealing with Kerberos issues (see my post about Windows 10 and Credential Guard for a primer). Hopefully that one will get resolved soon and I can make a post out of it. Other than that I hope to clean up some of the older pages. My cut and paste jobs from confluence really leave something to be desired.


UPDATE: Since I knew the table name I was able to better search the internet and found that this is because starting with SQL 2016 SP2 -CU3 there is now support for using a distribution database in an availability group. It makes sense that they had to add the table because there is not an easy way to synchronize sys.servers in the master database between all the nodes.
See the Microsoft Announcement Here