Event Filtering now available

http://blog.dbhistory.com/event-filtering-now-available/

Some servers added to DBHistory.com show a high level of unexpected activity, some reporting thousands of DDL events per day despite no changes being actually deployed on them. After collecting data over a longer period, it had become clear that two DDL events truly stand out in this regard:

  • UPDATE STATISTICS events reported when running sp_updatestats.
  • CREATE/ALTER/DROP EVENT SESSION events related to Microsoft telemetry XEvents session.

In fact the two categories above accounted for more than 75% of all events reported to DBHistory.com. These events can overwhelm the analysis of what changes actually occurred on a server.

Now you can define filters in your DBHistory.com account. Creating a filter allows you to ignore certain events reported to DBHistory.com. Events that satisfy a filter are ignored and not stored. To add an event filter expand the Show More option on the left hand navigation bar and click on the Event Filters. The following video shows how to add a filter that will ignores all events that occur on tempdb:

event-filters-tempdb

Event Filters allow you to specify any combination of event type, object type, object location (server, database, schema) and object name to define what events are filtered. If a field is left blank then the filter will match any value for that field. All field values are case sensitive, even for events originating from case insensitive collation SQL Server instances.

Event Filters allow you to specify also as action Include Event. This is intended to allow specific events to be captured when another, more generic, filter would normally disqualify the event. An example would be a generic filter that causes all UPDATE STATISTICS events to be dropped and a specific Include Event type filter that specifies event type UPDATE STATISTICS and object name ImportantTable. This combination will ignore all update statistics events except for the update statistics events on the ImportantTable, which will be captured.

The DBHistory.com Desktop Application

http://blog.dbhistory.com/the-dbhistory-com-desktop-application/

I’m happy to announce the availability of a desktop application for adding SQL Server instances to DBHistory.com. The application is available by downloading and running the DBHistoryApp.msi installer. The application will be available in the DBHistory startup folder. Note tthat this is a .Net 4.5 application that will require at least .Net 4.5 Framework to be installed on the machine where you’re running the application.

To add a new SQL Server instance to DBHistory.com launch the DBHistory Application, provide your DBHistory.com login information and then provide the SQL Server instance name and login credential. The wizard will then configure the SQL Server instance to monitoring report information to DBHistory.com. If required, it will prompt you for a database master key password in master database.

DBHistoryApp

At this stage the DBHistory Application does not support all the configuration options that the PowerShell cmdlet exposes, like choosing the Service Broker listening port and the name of database objects created by the application when configuring your SQL Server instance. If you need a finer level of control, please keep using the PowerShell cmdlet as it allows you to configure those options.

Login options for the DBHistory PowerShell

http://blog.dbhistory.com/login-options-for-the-dbhistory-powershell/

The DBHistory.com PowerShell cmdlets now asks for login credentials with an interactive prompt if credentials are not provided as parameters. Also, the login credentials can be provided as a PSCredential for secure scripting. The cmdlet requires two set of credentials:

  • The DBHistory.com credentials required to log in into DBHistory.com.
  • Credentials required to log into the SQL Server being added. By default the credentials for logging in into SQL Server are implicit as Windows authentication is used (Integrated Authentication, or SSPI). SQL Server credentials are only needed if SQL Authentication is desired.

Provide explicit DBHistory.com user name and password

If both -DBHistoryUserName and DBHistoryUserPassword are present and no -DBHistoryCredential is provided then the user name and password provided will be used to log in into DBHistory.com.

PS>Install-DBHistory -DBHistoryUserName me@example.com `
    -DBHistoryUserPassword BgfS9DQh `
    -ServerName ...

This option is recommended only when working in an interactive PowerShell session in a completely secure environment. Note that the passwords you type may be persisted in PowerShell session history. Do not use explicit passwords in scripts.

Prompt for DBHistory.com user name or password

If any of -DBHistoryUserName or -DBHistoryPassword is omitted and no -DBHistoryCredential is provided, then a credential prompt is displayed asking for the user name and password to be used to log in into DBHistory.com.

PS>Install-DBHistory -ServerName ...

This option is perfect for interactive PowerShell session in an insecure environment. The prompt will hide the password you type, and the password will not be persisted anywhere.

Using a PSCredential for DBHistory.com

If a -DBHistoryCredential parameter is provided then this will be used to log in into DBHistory.com. You can create the PSCredential type parameter by using the Get-Credential cmdlet or via New-Object, see PowerShell – How to create a PSCredential object for details.

PS>$credential = Get-Credential
Install-DBHistory -DBHistoryCredential $credential `
    -ServerName ...

This option is perfect for using Install-DBHistory cmdlet in automated scripts because the PSCredential object can be passed as an argument to the script.

Provide explicit SQL Authentication user name and password

If you provide both -SQLUserName and -SQLUserPassword then the cmdlet will use them to log in, as SQL Authentication, into the SQL Server instance you are adding.

PS>Install-DBHistory -ServerName ... `
    -SQLUserName sa `
    -SQLUserPassword Pc8qV3h2

This option is only recommended when working in a completely secure environment, as the password provided may be saved by the PowerShell session in the commands history. Do not use explicit passwords in scripts.

Prompt for SQL Authentication password

If you only provide the -SQLUserName parameter then the cmdlet will use SQL authentication and it will prompt for the password to use.

PS>Install-DBHistory -ServerName ... `
    -SQLUserName sa

This option is perfect for interactive PowerShell session in an insecure environment. The prompt will hide the password you type, and the password will not be persisted anywhere.

Use PSCredential for SQL Authentication

You can also provide a PSCredential object for the -SQLUserCredential parameter. In this case the cmdlet will use SQL Authentication, using the user name and password from the provided PSCredential object.

PS>$credential = Get-Credential
PS>Install-DBHistory -ServerName ... `
   -SQLUserCredential $credential

This option is recommended when using Install-DBHistory is scripts, as the credential object can be passed as argument to the script.

Install DBHistory.com from PowerShell Gallery

http://blog.dbhistory.com/install-dbhistory-com-from-powershell-gallery/

You can install the DBHistory.com PowerShell module from PowerShell Gallery. The PowerShell Gallery is a catalog of PowerShell modules and scripts hosted by Microsoft. Please refer to The PowerShell Gallery: Get Started for help how to enable the PowerShell Gallery on your local machine. Once you enable the PowerShell Gallery, you can install the DBHistory.com module:

PS> Install-Module -Name com.dbhistory.powershell

This will download all the files required by the DBHistory.com PowerShell module, and install them into the appropriate Modules location on your machine. You only need to install the module once. After this, you can import the DBHistory.com module into your PowerShell session:

PS>Import-Module com.dbhistory.powershell

You will have to import the module again, any time you open a PowerShell session. Once the module is imported, the Install-DBHistory cmdlet is available in your session. Use this cmdlet to add a new SQL Server instance to DBHistory.com:

DBHistory PowerShell cmdlet from PowerShellGallery.com