We have shut down DBHistory.com. Thanks for everyone that tried it out.

• We’ve received 7251033 Event Notification messages.
• We’ve processed 2089110 events, after filtering out the Microsoft phone-home XE session restarts.
• We’ve tracked 631864 database and server level objects.

These are the event types captured, with counts:

           event_type_name           | count
-------------------------------------+--------
 DROP_PROCEDURE                      | 328546
 UPDATE_STATISTICS                   | 322522
 CREATE_PROCEDURE                    | 310862
 CREATE_INDEX                        | 107034
 DROP_INDEX                          | 101340
 ALTER_DATABASE                      |  96889
 ALTER_TABLE                         |  75119
 CREATE_SERVICE                      |  72704
 CREATE_QUEUE                        |  72687
 DROP_SERVICE                        |  72659
 DROP_QUEUE                          |  72641
 ALTER_INDEX                         |  68694
 CREATE_TABLE                        |  68342
 DROP_TABLE                          |  60133
 ALTER_EVENT_SESSION                 |  53977
 GRANT_DATABASE                      |  31419
 CREATE_TRIGGER                      |  30369
 DROP_TRIGGER                        |  29929
 ALTER_INSTANCE                      |  19570
 ALTER_PROCEDURE                     |  16371
 DROP_FUNCTION                       |  10548
 CREATE_FUNCTION                     |   8014
 DROP_VIEW                           |   6375
 CREATE_EXTENDED_PROPERTY            |   5669
 CREATE_CERTIFICATE                  |   4564
 DROP_CERTIFICATE                    |   4383
 ALTER_AUTHORIZATION_DATABASE        |   3929
 ALTER_USER                          |   3861
 CREATE_VIEW                         |   3235
 CREATE_EVENT_SESSION                |   2730
 DROP_EVENT_SESSION                  |   2706
 DROP_DATABASE                       |   2334
 CREATE_DATABASE                     |   2208
 DENY_SERVER                         |   2181
 ADD_ROLE_MEMBER                     |   1388
 ALTER_FUNCTION                      |   1234
 RENAME                              |   1089
 ALTER_TRIGGER                       |    983
 ALTER_VIEW                          |    976
 DROP_STATISTICS                     |    927
 CREATE_USER                         |    921
 ALTER_DATABASE_SCOPED_CONFIGURATION |    758
 ALTER_LINKED_SERVER                 |    654
 CREATE_TYPE                         |    612
 DROP_USER                           |    475
 DROP_SYNONYM                        |    438
 CREATE_DATABASE_ENCRYPTION_KEY      |    393
 REVOKE_DATABASE                     |    283
 CREATE_LOGIN                        |    239
 DROP_TYPE                           |    226
 CREATE_SCHEMA                       |    215
 GRANT_SERVER                        |    190
 ALTER_LOGIN                         |    150
 CREATE_SYNONYM                      |    130
 CREATE_CONTRACT                     |    125
 CREATE_MESSAGE_TYPE                 |    120
 CREATE_ROLE                         |    107
 DENY_DATABASE                       |    105
 ALTER_EXTENDED_PROPERTY             |    103
 CREATE_ROUTE                        |    101
 DROP_CONTRACT                       |    101
 ADD_SERVER_ROLE_MEMBER              |     99
 CREATE_ENDPOINT                     |     92
 ALTER_QUEUE                         |     87
 CREATE_LINKED_SERVER_LOGIN          |     86
 CREATE_MASTER_KEY                   |     86
 CREATE_XML_SCHEMA_COLLECTION        |     76
 DROP_XML_SCHEMA_COLLECTION          |     70
 DROP_MESSAGE_TYPE                   |     65
 DROP_LOGIN                          |     64
 CREATE_STATISTICS                   |     59
 ALTER_ASSEMBLY                      |     59
 DROP_LINKED_SERVER                  |     57
 CREATE_LINKED_SERVER                |     56
 ALTER_REMOTE_SERVER                 |     55
 DROP_ROLE_MEMBER                    |     49
 ALTER_AVAILABILITY_GROUP            |     39
 DROP_DATABASE_ENCRYPTION_KEY        |     35
 DROP_SCHEMA                         |     32
 CREATE_ASSEMBLY                     |     26
 CREATE_SEQUENCE                     |     23
 ALTER_SCHEMA                        |     15
 ALTER_SERVER_AUDIT                  |     15
 ALTER_ENDPOINT                      |     14
 DROP_EXTENDED_PROPERTY              |     14
 DROP_ROUTE                          |     14
 CREATE_EVENT_NOTIFICATION           |     12
 ALTER_ROUTE                         |     12
 ALTER_SEQUENCE                      |     11
 CREATE_REMOTE_SERVER                |     11
 ALTER_DATABASE_AUDIT_SPECIFICATION  |     11
 CREATE_CREDENTIAL                   |     11
 DROP_SERVER_ROLE_MEMBER             |     11
 CREATE_SECURITY_POLICY              |     11
 DROP_CREDENTIAL                     |     10
 CREATE_EXTENDED_PROCEDURE           |      9
 DROP_ASSEMBLY                       |      9
 DROP_SECURITY_POLICY                |      9
 ALTER_MESSAGE_TYPE                  |      9
 DROP_SYMMETRIC_KEY                  |      9
 CREATE_SYMMETRIC_KEY                |      9
 REVOKE_SERVER                       |      8
 CREATE_PARTITION_SCHEME             |      6
 ALTER_SECURITY_POLICY               |      6
 ALTER_SERVICE                       |      6
 ALTER_CREDENTIAL                    |      6
 CREATE_XML_INDEX                    |      6
 ALTER_SERVER_AUDIT_SPECIFICATION    |      5
 ALTER_MASTER_KEY                    |      5
 CREATE_COLUMN_MASTER_KEY            |      5
 CREATE_DATABASE_AUDIT_SPECIFICATION |      4
 DROP_ROLE                           |      4
 CREATE_SERVER_AUDIT                 |      3
 DROP_REMOTE_SERVER                  |      3
 DROP_COLUMN_MASTER_KEY              |      2
 DROP_EVENT_NOTIFICATION             |      2
 DROP_ENDPOINT                       |      2
 ALTER_AUTHORIZATION_SERVER          |      2
 DROP_SERVER_AUDIT                   |      1
 DROP_DATABASE_AUDIT_SPECIFICATION   |      1
 CREATE_EXTERNAL_RESOURCE_POOL       |      1
 CREATE_SERVER_AUDIT_SPECIFICATION   |      1
 ALTER_PARTITION_SCHEME              |      1
 ALTER_PARTITION_FUNCTION            |      1
 CREATE_COLUMN_ENCRYPTION_KEY        |      1

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 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.

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.

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.

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.

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: