Add SQL Server to DBHistory.com
When you add a new SQL Server instance to DBHistory.com, the instance is configured to report all changes to DBHistory.com. This is done by adding an Event Notifications object to your SQL Server to track all configuration and schema changes. The target service of this notifications is DBHistory.com and the configuration will setup the required infrastructure needed for these events to be delivered:
- Routing information for DBHistory.com.
- Service Broker endpoint for remote message delivery.
- Certificate for authentication.
- Permissions and authorization.
At the same time your DBHistory.com account is being configured to recognize and authorize this SQL Server instance, by uploading the certificate used by this instance Service Broker endpoint into your account.
When the configuration is complete, the SQL Server instance is able to connect and exchange messages with DBHistory.com. All configuration is automatic, done by our DBHistory Application or by the DBHistory PowerShell module. Please see Add SQL Server for details how to download and run the configuration wizard.
Detecting Changes
DBHistory.com does not require an agent to monitor your server. Once is successfully configured, the SQL Server itself will push all change notifications to DBHistory.com. SQL Server Event Notifications are generating an event for every modification of the server. This include configuration modifications (eg. modifying a server setting with sp_configure) and any creation, alteration of dropping of any type of object, in any database on the monitored SQL Server. It doesn’t matter how the modification occurs, it can be done with a T-SQL script, it can be done from the Management Studio designers, using PowerShell or SMO. In all cases the modification generates an event that is immediately pushed to DBHistory.com.
Processing the event data
As the event data about the change reaches DBHistory.com, it is automatically processed and the data is visible immediately on your DBHistory.com account. The event data contains:
- The change server, the SQL Server instance where the change occurred.
- The change author, the login on the SQL Server that initiated the change.
- The change moment, the exact timestamp when the change occurred.
- The changed objects, the objects affected by the change1.
- The object location, in which server, database and schema does the modified object reside2.
- The change script, the actual T-SQL executed that triggered the change.
1 Some changes affect multiple objects, for example an index change affects both the table on which the index is defined and the index itself.
2 Some objects are server scoped and for them the location will be a server, other are database scoped and they will have a database location, while the schema scoped objects will have a schema location.
How you can use the DBHistory.com data
DBHistory.com processes the event data received from your SQL Server instance and makes it available in a usable format where you can search for events by time, by author, by location, by object, by change type, by text contained in the change and so on and so forth.
Because DBHistory.com relies on SQL Server itself to report any change, as it occurs, it can catch all changes, even those done by accident or by someone that omitted your Change Management protocols.
DBHistory.com offers a central repository for all changes. If you have many SQL Server instances to look at you can get a history of what changes occurred by simply going to one place: your DBHistory.com account.
Your DBHistory.com account can span heterogeneous environments without any requirement for a common Active Directory. Because it relies solely on secure outbound communication from SQL Server instances to DBHistory.com, your account can collect information without a complex VPN setup.
DBHistory.com keeps a long history of the changes, you will be able go back months of years of history and find how an object was modified, by whom and when, to be the way it is today.
You can access the DBHistory.com data via the Web, by browsing your account data on DBHistory.com. Work is in progress to make the data available by HTTP Rest interface and by PowerShell.
Notifications
Notifications allow you to react to unplanned changes. You can mark certain objects as important for you and get notified immediately when a change occurred, via email or by mobile application. You can also receive daily or weekly compilations of changes that occurred on your servers.
At the time of writing Notifications are not yet publicly available