Thursday, January 7, 2010

Query to Get Paged Out VMs from Virtual Center Database

Brief Description: Query the Virtual Center database for guests with swapped out memory.

Solution:

Just a quick handy script. In our implementation, we've seen swap out correlate to discrepancies between the Guest and host tools version, and found it handy to pull back tools and build info when querying for swap out. I use the (possibly overly convoluted, corrections are always welcome) query to find VMs which are swapping in the last hour, their tools version, and the build of their parent host.

Written with a table variable to make it clearer if you want to modify which columns are pulled back. Our VMware database records stat sample time in UTC (hence the +6), I'm unclear whether that's always the case.
Use [YourVMwareDB]
go
declare @tblToolsVer table
(
strVMName nvarchar(255),
pageout int,
intToolsVer int,
strStatus
nvarchar(255),
strHostName nvarchar(255),
intHostVer int
)

insert into @tblToolsVer(strVMName, pageout, intToolsVer, strStatus, strHostName, intHostVer)

select ev.name as VMName, CONVERT(decimal(12,2),ROUND(AVG(s.stat_value)/1024,2)) as swapped, vm.tools_version,
case vm.tools_status
when 0 then 'Not Installed'
when 1 then 'Not Running'
when 2 then 'Out of Date'
when
3 then 'OK'
when NULL then 'Indeterminate'
end as Tools_Status,
eh.name as Host_Name, ho.Product_Build as Host_Build
from vpx_vm vm
join vpx_entity ev on ev.id = vm.id
join vpx_entity eh on eh.id = vm.host_id
join vpx_host ho on ho.id = vm.host_id
join vpxV_hist_stat_daily s on s.entity = ('vm-'+ CONVERT (Varchar, vm.ID))
Where
s.stat_name = 'swapped' and ev.id in(
select distinct(h.id) 
from vpx_entity h 
join vpxV_hist_stat_daily s on s.entity = ('vm-'+ CONVERT (Varchar, h.ID))
where h.type_id = 0 and s.stat_name =
'swapped' and s.stat_value > 800 and s.sample_time >= dateadd(hh, +6,
getdate())
)
group by ev.name, vm.tools_version, vm.tools_status, eh.name, ho.product_build

select * from @tblToolsVer

Tuesday, January 5, 2010

Installing SQL Reporting Services on an existing instance

Brief Description: It can look as though reporting services is not available for install when running the sql installer on a machine with an existing SQL install but no IIS install.

Solution:

Install NET Framework and service packs for 2.x and 3.x
If IIS is not installed, do a base install (if this is external facing follow your external lock down procedures).


  • Add/Remove Programs

  • Add/Remove Windows Components

  • Choose application server

  • Leave Network COM+ access checked unless you're locking it down

  • Select ASP.NET

  • The rest of the defaults will do a basic IIS install only




  • If the server already has IIS check the .net version mapped to the default site
    Presuming your SQL server is not also a web server for exciting other exciting applications verify the default site is unused and continue to map asp.net to IIS


  • pushd c:\windows\Microsoft.NET\Framework\v2.xxx

  • If this is a new install, register 2.x to the default site with the scriptmap overwrite

  • aspnet_regiis -s /W3SVC/1/Root

  • Otherwise aspnet_regiis help has syntax for your choice of install.

  • aspnet_regiis -ir

  • run inetmgr

  • in Web Service Extensions 'ASP.NET v2.x should be installed and enabled

  • If you didn't install scriptmaps with aspnet_regiis, go the properties of the default website and choose the asp.net tab and change the version to 2.x




  • Run SQL Setup


  • You'll get a warning on edition change. Ignore this for the time being

  • Select the instance you're upgrading.

  • Install Reporting Services.
    Re-Run the current service pack install.
  •