Home » Articles posted by Anders Bengtsson (Page 25)

Author Archives: Anders Bengtsson

Look for new databases (…with a pinch of DPM)

With SQL Server Audit, SQL Server 2008 introduces an important new feature that provides a true auditing solution for enterprise customers. While SQL Trace can be used to satisfy many auditing needs, SQL Server Audit offers a number of attractive advantages that may help DBAs more easily achieve their goals such as meeting regulatory compliance requirements. These include the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. Perhaps most significantly, SQL Server Audit permits fine-grained auditing whereby an audit can be targeted to specific actions by a principal against a particular object. This paper provides a comprehensive description of the new feature along with usage guidance and then provides some practical examples. Source MSDN

If you want to get an alert when a new database is created in SQL 2008 you will first need to configure auditing on SQL side, and then a rule in Operations Manager to generate an alert. Configure a new audit with audit destination equals Application log or security log. If you select security log you might need to configure some extra security permissions. Create a new server audit policy, configure audit action type equals DATABASE_CHANGE_GROUP.

sqlAudit01

Next step is to create the rule that will pickup the SQL event and generate an alert. Create a new event based rule, target it to for example SQL Servers, to monitor all your SQL machines. Configure the rule to look for event ID 33205 including CREATE and DATABASE in the event description.

sqlAudit02

In the expression of the rule, we use “.” to tell Operations Manager “any character before, around or after the two keywords, CREATE and DATABASE.

sqlAudit03

 

Another step that you might want to do is to verify that you backup the new SQL database. I modified a power shell script and the result is that it will connect to your DPM server and a SQL box, it will then ask you if you want to add any of the unprotected databases on that server to a protection group in DPM. 

param([string] $ProductionServer, [string] $PGName)
if(!$ProductionServer)
{
$ProductionServer = read-host "Enter the production server name (a SQL server protected by DPM)"
}
if(!$PGName)
{
$PGName = read-host "Enter the name of your existing SQL protection group name"
}

$dpmservername = read-host “Enter the name of your DPM server”

connect-dpmserver $dpmservername
$dpmservername

$PGList = @(Get-ProtectionGroup $dpmservername)

foreach ($PG in $PGList)
{
if($PG.FriendlyName -eq $PGName)
{
write-host “Found protection group $PGName”
$MPG = Get-ModifiableProtectionGroup $PG
$PGFound=$true
}
}

if(!$PGfound)
{
write-host “Protection Group $PGName does not exist”
exit 1
}

$PSList=@(Get-ProductionServer $dpmservername)
$DsList = @()

foreach ($PS in $PSList)
{
if($PS.NetBiosName -eq $ProductionServer)
{
write-host “Running Inquiry on” $PS.NetbiosName
$DSlist += Get-Datasource -ProductionServer $PS -Inquire
$PSFound=$true
}
}

if(!$PSfound)
{
“Production Server $PS does not exist”
exit 1
}

$protectedDsList = @()
foreach ($ds in $dslist)
{
if($ds.ToString(“T”, $null) -match “SQL” -and !$ds.Protected)
{
$toadd = read-host “Do you want to protect the” $ds.Name “database? (y/n)?”
If ($toadd -eq “y”)
{
$protectedDsList += $ds
Add-ChildDatasource -ProtectionGroup $MPG -ChildDatasource $ds
$x=Get-DatasourceDiskAllocation -Datasource $ds
Set-DatasourceDiskAllocation -Datasource $x -ProtectionGroup $MPG
}
}
}

Set-ReplicaCreationMethod -ProtectionGroup $MPG -Now

if($protectedDsList.Length)
{
write-host “Adding new SQL DBs to” $MPG.FriendlyName
Set-protectiongroup $MPG
}

disconnect-dpmserver $dpmservername
“Exiting from script”

sqlAudit04

(tested in a sandbox, so I am aware that the ops mgr databases are not protected and all the test databases) If you want to integrate the script into Ops Mgr you should read this post from David Allen.

Windows 7 Tour [Sweden]

timthumb

Atea, Knowledge Factory and Cornerstone will this fall visit 7 cities in Sweden on the Windows 7 Tour.

We’ll show you Microsofts new client operating system Windows 7 in cooperation with their simultaneously released server operating system Windows Server 2008 R2. On top of this we’ll add the management software suite, including System Center, and show you why they’re stronger together.

Speakers:
Me, Anders Bengtsson from Atea
Lasse Frändeby from Cornerstone
Joachim Nässlander from Knowledge Factory

City / Date
Umeå 12/10
Sundsvall 13/10
Borlänge 14/10
Stockholm 15/10
Malmö 20/10
Göteborg 21/10
Karlskrona 22/10

Tour site (in Swedish): http://www.cornerstone.se/sv/ExpertZone/Windows7/tour/

Meet MEET and get to know Exchange 2010 [Sweden]

Wednesday 2:nd of September MEET (Microsoft Extended Expert Team) is hosting an After Work at Grodan in Stockholm, Sweden. The exact location is Grev Turegatan 16. The topic of this night is Exchange 2010, and if you’re into Exchange or the technologies related to Exchange there’ll be a whole bunch of knowledgeable experts to meet. If you’re in Stockholm, pass by and grab a beer! The more the merrier!

2007 R2 Universal Connector

If you have not seen it yet, Microsoft has now released a number of connectors for Ops Mgr 2007 R2. The System Center Operations Manager 2007 R2 Connectors provide System Center Operations Manager 2007 R2 alert forwarding to remote systems, such as an Enterprise Management System (EMS) or service desk system. One of the connectors that Microsoft has released is the universal connector, a connector that can be installed and configured for potentially any remote system that is hosted on a Windows system or on a supported UNIX system. More info and download here.

Each deployed Operations Manager 2007 R2 Connector has the following components:

  • Interop Provider – This service is installed on a Windows or UNIX server in a supported remote system environment and is automatically started at install. The Interop Provider receives alerts from the Connector Service in the Operations Manager 2007 R2 environment and forwards them to the supported remote system through APIs of that system. The Interop Provider also sends updates on those events back to the Connector Service.
  • Connector Service – This service is installed on a server in the Operations Manager 2007 R2 environment and is automatically started after configuration is completed. The Connector Service gathers alerts from the Operations Manager 2007 R2 RMS and sends them to the Interop Provider that is installed on a remote system server. The Connector Service also receives updates from that Interop Provider for remote system events that were created from Operations Manager alerts.
  • Connector Configuration UI – This configuration dialog box is installed on a server on which an Operations Manager 2007 R2 console is installed, and it becomes an integrated component in that console. Use the Connector Configuration dialog box to configure communications for Operations Manager 2007 R2 servers with remote system servers. Tabs on the Connector Configuration dialog box also provide for mapping Operations Manager alert properties to properties of the remote system’s events and for configuring the High Availability feature.
  • The installation was pretty simple, the manual explain the different steps in a good way. You install the “Interop Provider” and the connector service. The connector configuration UI needs to be installed on a machine with the Ops Mgr R2 console as there is a integration between the two. It is not possible to upgrade any pre-RTM version of the connector, so if you have been running a pre-RTM version make sure you uninstall it before you tries to install the RTM version. When the installation is complete you will see a new connector in the console

     

    Connector02

    Connector01

    Connector03

    Connector04

    Connector05

    Connector06

    On the Ops Mgr Universal Connector you can configure where your EMS server is, how ofter to synchronize, where your Ops Mgr server is and of course which alert fields to synchronize. You will also see a new connector under Internal Connectors. This is where you can configure which alerts that will be forwarded to the EMS server. With default settings all alerts are forwarded.

    When a new alert is generated a XML file will be generated on the EMS server side. In this example I generate an alert for a event in the event viewer.

    connector07

    As you can see below, the alert has ID 8f474850-7308-41f8-ba40-0fb27b72084e, and there are XML files generated for the alert. The filesname is <AlertID>. <sequence number>.XML. The sequence number starting at 1, is added so that if an alert is forwarded and not processed in a timely manner of if an alert is updated multiple times in a short time period, the files are not overwritten. Overwritting the files could result in lose updates. It is important that the EMS system process the file with the lower numbers first so that the sequence of activities is not lost. Note that if you get two alerts at the same time they will be named <AlertID001>.1.XML and <AlertID002>.2.XML, the .X. number is common for the management group, and not a sequence number of each alert ID.

    Connect08

    The next step is that the remote system needs to acknowledge (event type = 2) the alert back to Ops Mgr. This is done with a new XML file. Dont forget to delete the first XML file, so you dont process it again.

    Connector09

     

    If you then want to update (event type = 1) the alert you generate a new XML file, in the following example the resolution state is set to 100. You can use the same XML code to update the resolution state to 255, in other words close an alert.

    Connector10

     

    There are a couple of different event types you can use with the universal connector

    • <EventType>0</EventType> = New Ops Mgr alert being forwarded
    • <EventType>1</EventType> = Update to an Ops Mgr alert being forwarded to the remote system or update to the event/ticket on a remote system being forwarded to Ops Mgr
    • <EventType>2</EventType> = Remote system acknowledgement of a new alert
    • <EventType>3</EventType> = Remote system acknowledgement of an alert update

    Summary: You can use the Universal connector to forward alert in XML or EVT format to remote systems. Remote systems can then generate files that the connector picks up and process in Operations Manager, for example update resolution state of an alert. You can run the connector against both Microsoft and non-Microsoft systems.

    Monitor multiple events in a logfile within X minutes

    This is a example how you can configure a monitor to generate an alert if a log file contains a word more then X times during Y minutes. In the following example I have configure a monitor to generate a critical alert if the logfile contains “error” more then four times during a minute. The following example look in any file named logfile*.log in the C:\logfiles folder. It the application writes a “success” to the logfile, the monitor will be reset back to healthy.

    1. Go to the Authoring workspace and create a new monitor, Log Files/Text Log/Repeated Event Detection/Event Reset

    2. General
    Name: Contoso – Logfile – Repeated Event w event reset
    Monitor Target: for example Windows Server 2008 Computer (more about targeting here and here)
    …next

    3. Single Generic Log
    Directory: C:\logfiles
    Pattern: logfile*.log
    …next

    4. Single Event Expression
    Parameter Name: Params/Param[1]
    Operator: Contains
    Value: success
    …next

    5. Repeated Generic Log
    Directory: C:\logfiles
    Pattern: logfile*.log
    …next

    6. Repeated Event Expression
    Parameter Name: Params/Param[1]
    Operator: Contains
    Value: error
    …next

    7. Repeated Event Description
    Counting mode: Trigger on count
    Compare Count: 4
    Based on items occurrence within a time interval: 1 Minutes
    …next

     

    8. Health
    Event Raised: Healthy
    Repeated Event Raised: Critical
    …next

    9. Alerting
    Check “Generate alerts for this monitor”
    Input a suitable alert description, also try include a couple of the data parameters
    …create

    If any file named logfile*.log in the C:\logfiles folder now writes four “error” within one minute an critical alert will generated. Then, if a “success” is any file the monitor will be set back to healthy state. Step 3 and 4 configure the event that will set the monitor back to healthy.

    (click on a picture twice to enlarge it)

    Configure Command Notification in R2

    There has been a number of questions about the command notification channel in Technet forums. Here is a example how to configure a command notification channel, a subscriber for the channel and a subscription. This example configure a command notification channel to run a vbscript. The vbscript echo a time stamp and the alertname to a local file.

    1. Go to Administration/Notification/Channels and create a new command notification channel. For example
    Channel name: Contoso Command Notification Channel
    Full path of the command file: C:\windows\system32\cmd.exe
    Command line paramters: /c C:\logfile.vbs $Data/Context/DataItem/AlertName$
    Startup folder for the command line: C:\

    2. Go to Subscribers and create a new subscriber
    Subscriber name: Contoso Command Line Subscriber (note that you do not need to select a AD user)
    Schedule: Always send notification
    Addresses: Added a new address
    -Address name: Contoso Command Address
    -Channel type: Command
    -Command Channel: Contoso Command Notification Channel
    -Delivery address for the selected channel: I just left this with default, something about sip: but it resultet in a blank field, so just leave it
    -Schedule: Always send notification

    3. Goto Subscriptions and add a new subscription, for example
    Name: Contoso Command Line Subscription
    Criteria: with a specific resolution state: new(0) and closed (255)
    Subscribers: Contoso Command Line Subscriber
    Channel: Contoso Command Notification Channel
    Summary: Make sure the channel is enabled

    Thats it, and the logfile.vbs looks like:

    ' ******************************************
    ' GET PARAMETERS INTO SCRIPT
    ' ******************************************
    strAlert = Wscript.Arguments.Item(0)

    ‘ ******************************************
    ‘ LOG TO FILE (DATE,TIME,TO,ALERT NAME,STATE
    ‘ ******************************************
    Dim Stuff, myFSO, WriteStuff, dateStamp
    strdateStamp = Date()
    strTimeStamp = Time()
    ‘Write information to Text File
    Stuff = strDateStamp & “,” & strTimeStamp & “,” & strAlert
    Set myFSO = CreateObject(“Scripting.FileSystemObject”)
    Set WriteStuff = myFSO.OpenTextFile(“opsmgr_notification_logfile.txt”, 8, True)
    WriteStuff.WriteLine(Stuff)
    WriteStuff.Close

    More info about command notification channel here.

    Ops Mgr R2 and multiple gateway servers

    Some time ago I posted an article about gateway servers, Ops Mgr R2 and Server 2008. This week I have extended that scenario by adding two more management servers and one more gateway server.

    To make this scenario work I had to configure both my gateway servers to use corp-MS03 and corp-MS02 as primary and secondary management servers. You can do that with operations manager command shell.

    Log on to a machine with command shell installed, for example a workstation. Run the following script (save it as a .ps1 script file)

    $primaryMS = Get-ManagementServer | where {$_.Name -eq 'corp-ms02.corp.contoso.local'}
    $failoverMS = Get-ManagementServer | where {$_.Name -eq 'corp-r2.corp.contoso.local'}
    $gatewayMS = Get-GatewayManagementServer | where {$_.Name -eq 'dmz01'}
    Set-ManagementServer -GatewayManagementServer: $gatewayMS -primarymanagementserver: $primaryMS -FailoverServer: $failoverMS

    That script configure the gateway server, DMZ01, to use corp-MS02 as primary management server and corp-R2 as failover management server. When you run this command you have to be aware that when the current management server gets this update it will stop accepting connections from your gateway server (if it is not primary or failover management server in the new configuration). That could result in a orphaned gateway server, the gateway server is not allowed to communicate with any management server. If this happends you will have to logon to your gateway server, check HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Server Management Group\<Management Group Name>\Parent Health Services\0 and look for the authenticationName string. You must then run the set-managementserver cmdlet again and change the management server back to the one in the register. To make sure you don´t end up with orphaned gateway servers use your current management server as failover server in your script, then the gateway server can receive new configuration from the new failover server (current management server) and when you have confirm the configuration change, you can update the failover server for the gateway server.

    To verify that your gateway server has received the new configuration you can look in the OpsMgrConnector.Config.XML file on your Gateway server, search for Parents in the file. Default location is C:\Program Files\System Center operations Manager 2007\Health Service State\Connector Configuration Cache\.

    You can also run the following command shell commands to see he primary and failover server for a gateway server

    Get-GatewayManagementServer | where {$_.Name -like 'DMZ01'} | Get-PrimaryManagementServer
    Get-GatewayManagementServer | where {$_.Name -like 'DMZ01'} | Get-FailoverManagementServer

    If everything looks ok, you could now run the power shell script again, and update the failoverMS to the correct failover management server.

    To configure the agent to communicate with the two gateway servers I ran the following two scripts. corp-r2 was the primary/only management server for the agent when I started.

    $primaryMS = Get-GatewayManagementServer | where {$_.Name -eq 'dmz01'}
    $failoverMS = Get-ManagementServer | where {$_.Name -eq 'corp-r2.corp.contoso.local'}
    $agent = Get-agent | where {$_.Name -eq 'DMZ-A01'}
    Set-ManagementServer -AgentManagedComputer: $agent -PrimaryManagementServer: $primaryMS -FailoverServer: $failoverMS

    after I verified that the agent had recived the new configuration I ran

    $primaryMS = Get-GatewayManagementServer | where {$_.Name -eq 'dmz01'}
    $failoverMS = Get-gatewayManagementServer | where {$_.Name -eq 'dmz-02'}
    $agent = Get-agent | where {$_.Name -eq 'DMZ-A01'}
    Set-ManagementServer -AgentManagedComputer: $agent -PrimaryManagementServer: $primaryMS -FailoverServer: $failoverMS

    The result of these commands is that my agent (DMZ-A01) can communicate with both gateway servers. Both my gateway servers can communicate with both my management servers. If I shutdown DMZ01 my agent, DMZ-A01, failover to DMZ-02. If I start DMZ01 again the agent will automatic return to DMZ01. If I shutdown corp-MS02 my gateway server will automatic failover to corp-ms03. If I shutdown one gateway server and one management server the agent will still communicate with the management group.

    List Status for Resource Groups

    If you want to see the active node for a cluster group you can do that with a task. Create a task target to “Cluster Node”, then add the following command line configuration

    Full path to file: %WINDIR%\system32\cluster.exe
    Parameters: $Target/Property[Type="MicrosoftWindowsClusterManagementLibrary6065680!Microsoft.Windows.Cluster.Node"]/ClusterName$ group

    You can then run the task from the Cluster Node State view in the cluster MP. The task will list status for all available resource groups.

    Detect all SQL Server cluster resources

    A couple of days I ago when I was working with a SQL cluster I found something interesting. I had problem with a SQL cluster, Operations Manager 2007 R2 only found the first network name in my cluster group, not the one for my SQL cluster. After some time I found the following text in the SQL management pack guide:

    Having a SQL Server cluster resource group that contains more than one network name resource might mean that the clustered SQL Server resource is not monitored. For more information, see Knowledge Base article 919594.

    …and KB 919594 tells you

    To resolve this issue, configure existing resources in the cluster group so that System Center Operations Manager or MOM detects the virtual server, or create and configure the necessary resources. For example, you may have to create a generic application resource that does nothing. This generic application resource will enable System Center Operations Manager or MOM to discover the virtual server on which the resource is hosted.

    Note If more than one Network Name resource is configured in a group, System Center Operations Manager or MOM uses the first network name that it detects as the name of the virtual server..

    As soon as I had re-organized my cluster resources for SQL into a new cluster group Operations Manager 2007 R2 found the other cluster resource group and the SQL running on it. It also discovered all the SQL components for example databases and logical disks.

    There is a KB article about this, KB959865, that shows you another solution, where you can do a override and enable “Multiple Servers Discovery” within the cluster MP.

    Cluster

    50216 Operations Manager 2007: Advanced Configuration and Administration

    This week Microsoft Learning released a new Operations Manager 2007 R2 instructor-led (classroom) course for IT Professionals. It is a 3-days course that will cover the following topics in deep, level 400.

     This course was written by myself and Maarten Goet. Maarten is also a Microsoft MVP within Operations Manager. We have tried to add as much “real world scenarios” and field experience as possible into this course and hope you will learn a lot!

    Module 1: Architecture and Troubleshooting
    This module will explain how the ‘internals’ of Operations Manager work and how to troubleshoot

    Module 2: Management Packs
    This module will explain management pack architecture and how to author a management pack.

    Module 3: Reporting
    This module will explain how to develop custom reports.

    Module 4: Connectors
    This module will explain how to connect Operations Manager to remote systems, like a ticketing system.

    Module 5: Advanced Scripting and PowerShell
    This module will explain how to build and work with Operations Manager scripting and PowerShell.

    For more info about the course, please take a look here. If you want to attend this course in the the Nordic countries, please take a look here.