Yusuf Ozturk » Windows Server

Blogroll

Badges

Community

Agentless Monitoring of Nano Server on Azure

Posted in Windows Server | No Comment | 2,536 views | 31/10/2016 13:35

Nano Server monitoring is kind of a challenge for System Administrators. Even if your current monitoring product supports Nano Server, installing agents on Nano could be a headache.

In this article, I will show you how to monitor your Nano Server with VirtualMetric’s Bare Metal Monitoring feature.

VirtualMetric supports following Windows OS without agent and additional software requirements:

* Windows Server 2003/2003 R2
* Windows Server 2008/2008 R2
* Windows Server 2012/2012 R2
* Windows Server 2016
* Nano Server
* Windows 7/8/10

VirtualMetric Bare Metal Monitoring provides:

* More than 100 Performance Counters (All Customizable)
* Hardware Health (Currently supported hardwares are IBM, Dell, HP, SuperMicro, Fujitsu and Cisco)
* Change Tracking
* Event and Security Log Collection
* TCP Activity for Network Based Attacks like DDoS etc.
* Firewall Log Collection (Dropped & Allowed Packets)
* Assessments like Pending Updates, Hotfixes, Services, Processes etc.

First, lets start installing our Nano Server on Azure.

1. Go to your Azure Dashboard and find Nano server in compute:

MSSQL Server Index Reports

Posted in Windows Server | No Comment | 1,747 views | 25/11/2015 15:00

This is a query of mine, which gives you detailed information about indexes on one query:

SELECT SO.NAME AS TableName, PS.object_id AS ObjectId, SI.NAME AS IndexName, PS.index_id AS IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage,
SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS ROW_COUNT,
MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent',
MAX(fragment_count) AS FragmentCount,
MAX(ips.page_count) AS PageCount,
MAX(ius.user_scans) AS UserScans, MAX(ius.user_seeks) AS UserSeeks, MAX(ius.user_lookups) AS UserLookups
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') AS ips
ON ps.object_id = ips.object_id AND
       ps.index_id = ips.index_id
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id
WHERE
SO.IS_MS_SHIPPED = 0
GROUP BY so.name, ps.object_id, si.name, ps.index_id
ORDER BY UserLookups DESC

You can also add Schema column if you have different db schemeas.

MSSQL Database Performance Tricks

Posted in Windows Server | No Comment | 1,898 views | 10/11/2015 15:14

Check following query to improve your database performance:

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

This is a note for myself. So you should edit config to meet your requirements.

MSSQL Database Index Fragmentation Reports

Posted in Windows Server | No Comment | 2,202 views | 10/11/2015 15:10

You can get Database Index Fragmentation Reports with following query:

SELECT object_name(ps.object_id) AS [name], 
	ps.index_id,
	i.name AS IndexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ps
LEFT JOIN sys.indexes i ON i.object_id=ps.object_id AND i.index_id=ps.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ps.page_count  > 100
ORDER BY ps.avg_fragmentation_in_percent DESC

You can check MSDN for more information about sys.dm_db_index_physical_stats.

Getting Stored Procedure Stats from MSSQL

Posted in Windows Server | No Comment | 1,926 views | 10/11/2015 15:07

Following query will give you details about SP usage:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id = DB_ID()

If you need to get all custom SPs in one query:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id > 4 AND database_id < 32767

Just filtered last stats, but you can use SELECT * to get all stats.

Getting all table column names of MSSQL database

Posted in Windows Server | No Comment | 1,843 views | 10/11/2015 15:01

If you need to get all table column names from MSSQL database, you can use following query:

SELECT 
o.name, c.name 
FROM sys.columns c
INNER JOIN sys.objects  o ON c.object_id=o.object_id
ORDER BY o.name, c.column_id

That will output all column names.

Windows Containers Management with PowerShell Series – Part 1

Posted in Windows Powershell, Windows Server | No Comment | 1,912 views | 04/10/2015 20:22

Windows Containers are finally here! Lets start our new series :)

Container management is very similar to Hyper-V management. For example:

Lets check our commands:

PS C:\Users\Administrator> get-command *container*
 
CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Function        Install-ContainerOSImage                           1.0.0.0    Containers
Function        Uninstall-ContainerOSImage                         1.0.0.0    Containers
Cmdlet          Add-ContainerNetworkAdapter                        1.0.0.0    Containers
Cmdlet          Connect-ContainerNetworkAdapter                    1.0.0.0    Containers
Cmdlet          Disconnect-ContainerNetworkAdapter                 1.0.0.0    Containers
Cmdlet          Export-ContainerImage                              1.0.0.0    Containers
Cmdlet          Get-Container                                      1.0.0.0    Containers
Cmdlet          Get-ContainerHost                                  1.0.0.0    Containers
Cmdlet          Get-ContainerImage                                 1.0.0.0    Containers
Cmdlet          Get-ContainerNetworkAdapter                        1.0.0.0    Containers
Cmdlet          Import-ContainerImage                              1.0.0.0    Containers
Cmdlet          Move-ContainerImageRepository                      1.0.0.0    Containers
Cmdlet          New-Container                                      1.0.0.0    Containers
Cmdlet          New-ContainerImage                                 1.0.0.0    Containers
Cmdlet          Remove-Container                                   1.0.0.0    Containers
Cmdlet          Remove-ContainerImage                              1.0.0.0    Containers
Cmdlet          Remove-ContainerNetworkAdapter                     1.0.0.0    Containers
Cmdlet          Set-ContainerNetworkAdapter                        1.0.0.0    Containers
Cmdlet          Start-Container                                    1.0.0.0    Containers
Cmdlet          Stop-Container                                     1.0.0.0    Containers
Cmdlet          Test-ContainerImage                                1.0.0.0    Containers

I can create a new container with following command:

New-Container -Name "MyContainer" -ContainerImageName WindowsServerCore -SwitchName "Virtual Switch"

If you install Windows Containers with Microsoft Documentation, then you should have a virtual switch called “Virtual Switch” in your environment.

New-Container accepts many parameters:

NAME
    New-Container
 
SYNTAX
    New-Container [[-Name] <string>] -ContainerImageName <string> [-ContainerImagePublisher <string>]
    [-ContainerImageVersion <version>] [-CimSession <CimSession[]>] [-ComputerName <string[]>] [-Credential
    <pscredential[]>] [-MemoryStartupBytes <long>] [-SwitchName <string>] [-Path <string>] [-AsJob] [-WhatIf]
    [-Confirm]  [<CommonParameters>]
 
    New-Container [[-Name] <string>] -ContainerImage <ContainerImage> [-MemoryStartupBytes <long>] [-SwitchName
    <string>] [-Path <string>] [-AsJob] [-WhatIf] [-Confirm]  [<CommonParameters>]

There is a parameter called “MemoryStartupBytes” but it seems it just changes startup memory. It seems there is no memory or processor limit yet.

Starting Container is similar like Hyper-V VMs:

Start-Container -Name "MyContainer"

After start your container, you should configure your Host’s Firewall because Container uses Host Firewall. If you try to configure firewall inside Container, you will get warning.

You can use Enter-PSSession like in Hyper-V VMs to connect Container:

Enter-PSSession -ContainerId (Get-Container MyContainer).ContainerId -RunAsAdministrator

You can disable firewall with following command to test RDP connection:

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

After that, you may have to enable Administrator account in Container with:

net use administrator /active:yes
net use administrator Password1!

See you in next part!