NetWrix SQL Server Change Reporter. Quick Start Guide

(1)

NetWrix SQL Server Change Reporter

Quick Start Guide

(2)

Introduction

SQL Server is a complex system that involves many different types of objects and requires almost daily changes to server configurations, security, databases, etc. It's very hard to keep track of changes and enforce fine-grained delegation rules for environments managed by multiple DBAs and operators. A hot topic today is compliance with government and industry regulations, which adds challenges to all types of IT

infrastructures-especially SQL databases that store business-critical data and that support business applications.

NetWrix SQL Server Change Reporter is an easy-to-use auditing solution that reports changes made to your SQL Server's configurations and databases. The product reports on changes made to server instances, databases, users, roles, logins, schema changes, and many other objects.

Product Features

● Reports on changes made to server instances, databases, users, roles, logins, schema changes, and many other objects.

● Reports include information about what changes were made, who (*) made changes and when (*) they were made.

● Provides on-demand Web-based reporting.(*)

● Custom reports can be created or ordered from NetWrix.(*)

● Provides storage for collected audit data and enables historical reporting for any period of time.(*) * Features marked with (*) are only available in the commercial version of the product.

(4)

Licensing

NetWrix SQL Server Change Reporter comes in two variants: free and commercial. The list below outlines the differences between them.

Feature Freeware Version Commercial Version

Who and When fields for every change No Yes

Track database changes

Changes made to database users, roles and schemas only.

Changes made to server instances, databases, users, roles, logins, schema changes, etc.

Advanced reports based on SQL Reporting Services,

with filtering, grouping and sorting No Yes

Predefined reports Daily report with

recent changes

Multiple predefined reports

Custom reports No Yes. Create manually or

order from NetWrix (3 reports at no charge!)

Historical archiving and reporting Only for last two SQL

Server snapshots Any period of time

Technical Support Support forum Phone, e-mail

Licensing Free of charge

Per SQL Server or site license, please see our pricing information or request a quote

The free version can be used by businesses and individuals for unlimited time, at no charge. Commercial version can be evaluated for free during 20 days.

(5)

How It Works

Typical NetWrix SQL Server Change Reporter data flow is described below.

1. SQL Server changes are periodically collected and stored as snapshot files. Reports displaying changes to SQL servers are generated on schedule and then sent to the specified e-mail recipient(s).

Optionally, reports can be viewed with SQL SRS Report Manager.

2. You can set up advanced reporting based on SQL Server Reporting Services. For that, you can use the Advanced Reporting Configuration Wizard (it can be invoked during installation, or later from the Start | NetWrix SQL Server Change Reporter Commercial Version | Configurator). There you have to specify: SQL Server, SQL SRS location and the SQL database where information on

configuration changes will be stored. If the database does not exist at that moment, it will be created after you complete the wizard.

Note: If the database was not created during installation for this or that reason (for example, policy settings that require a database admin privileges to create a database), run the acr_db.sql script from installation folder using the account that possesses the required rights and privileges.

3. When information about SQL server changes is collected for a snapshot, it will be also automatically stored in the specified database and become available for report generation.

4. Also, after you complete the Advanced Reporting Configuration Wizard, predefined reports RDL files) will be loaded to the Home>NetWrix SQL Server Change Reporter folder on SQL SRS.

5. You can view HTML reports in the SQL SRS Report Manager if you enter the URL in your web browser (you can find this URL in Advanced Reporting Settings), or click the More reports link from the email report that you have received.

The Netwrix SQL Server Change Reporter collection and reporting workflow is usually as follows:

1. A user launches the Configurator and sets the parameters for automated data collection and reporting.

2. The Netwrix SQL Server Change Reporter scheduled task is launched periodically (typically, every night, at 3 AM by default; it can also be launched manually when needed). This task collects configuration snapshots, and e-mails reports on databases and configurations changes to the specified recipients.

3. If SQL SRS-based reporting was configured, the task also stores information about SQL Server changes to the specified SQL server database (if automatic data import fails, you can use Database Importer to import data when necessary).

4. A user launches mail client to view the reports sent by e-mail.

5. If SQL SRS-based reporting was configured, a user launches the web browser and views the reports in Report Manager.

(6)

Getting Started

Follow the instructions below to install and configure SQL Server Change Reporter.

System Requirements

The product can be installed on any computer running Windows XP SP2 or higher with Microsoft .NET Framework 2.0. The computer must belong to the domain where managed computers are located

Change Reporter audits changes made to the following versions of SQL Server:

● Microsoft SQL Server 2005 Standard and Express Edition

● Microsoft SQL Server 2008 Standard and Express Edition

Optionally you will need SQL Server 2005 Express Edition with Advanced Services to create and view the advanced Web-based reports. You can get a free copy from Microsoft Download Center.

(7)

Setup

To install SQL Server Change Reporter, run the setup program on any computer in the domain where managed SQL Servers are located. On the last step of the installation wizard, the configuration dialog box opens.

(8)

In this dialog box, specify the following:

● The Enable SQL Server Change Reporting check box is selected by default.

● Use Add, Remove or Import (*) to specify a list of SQL Servers you want to monitor. The Import button lets you add a batch of SQL Servers from an import file. The import file contains a list of SQL Servers names, each server name is on one line.

● Specify the data storage path in the Store data to: text box.

Note: Disk space required for 1 snapshot = (SQL Objects Count + Changes Count)*1Kb

"SQL Objects Count" - number of objects in SQL Server (databases, tables, columns, indexes, stored procedures, roles, users, etc.)

"Changes Count" - number of changes occurred

● By default, NetWrix SQL Server Change Reporter stores data for the last two days. Select the Enable long-term archiving for: option if you need tracking for longer periods and specify its value.

(*)

● To provide for advanced reporting based on SQL Server Reporting Services, click Configure.... For more details, see the Using SQL SRS-based Reporting section in this document.(*)

● Click Change to change the report delivery schedule. By default, reports are delivered at 3.00 AM every day.

● Under Email report delivery settings, enter the e-mail addresses to which reports are delivered (multiple addresses should be separated by comma) and supply SMTP server settings (name, port,) and From address).

When you have finished with these settings, click Apply. You will be prompted for the credentials to run data collection and report generation. The account you specify will be used to run the NetWrix SQL Server Change Reporter scheduled task (it can also be launched manually, as described later in this document).

Features marked with (*) are only available in the commercial version of the product.

If necessary, you can later change configuration settings by invoking Configurator from the Start menu (select NetWrix SQL Server Change Reporter Commercial Version and then click Configurator).

(9)

Viewing the Reports

The reports are e-mailed to the addresses specified during the configuration.

Reports on Changes

At the first run of the scheduled task, the message notifies you of the initial analysis completed. Next, you can make some changes to your SQL Server to see how they will be reported. For example, you can change database permissions. After that, you can launch the scheduled task again, and then check the mailbox for the new report. The commercial version of the product provides information about who made the change and when it occurred. The changes should be reported like shown in the figure below; if so, consider the product installation and configuration completed.

(10)

Next Steps

This section tells you how to manage SQL Server Change Reporter beyond the initial configuration. For more details, see the NetWrix SQL Server Change Reporter help.

Running an On-Demand Report

To get a report on changes made to SQL Server right away, you can select the Netwrix SQL Server Change Reporter in the list of the scheduled tasks, and select Run from its shortcut menu. The program will check for SQL Server changes and automatically e-mail the report to the specified recipient(s).

Reporting on Changes Occurred Between Two Snapshots

To view the changes that occurred between the particular snapshots, Report Viewer can be used.

Select the SQL Server and snapshots (by date) and click Generate to generate and save a report on changes between them (in the HTM format). The report will be opened in the web browser to show you the changes that occurred between selected snapshots.

Note: Actually, this report will be identical to the report on changes you received by email at the time of the

(11)

Importing Changes Occurred Between Two Snapshots

Database Importer lets you import changes occurred between two snapshots saved by Change Reporter to an SQL server database for performing the advanced analysis through Microsoft SQL Server SRS.

In most cases the use of Database Importer is not required, because the data is imported according to the schedule and automatically stored in the specified database if the corresponding option is selected.

However, you may need to manually import the data when, for example, the database fails, or any other error occurs.

Advanced Reporting

With SQL Server Reporting Services deployed, you can configure advanced reporting (SQL SRS-based). In this case, you can use the advantages of SQL SRS-based reporting:

● Change the report filters to fine-tune the data view according to your needs.

● Use one of popular formats: PDF, XLS, etc. to save the report.

● Apply grouping and sorting to report data, and so on.

To use this type of reporting, you can either click Configure when supplying configuration settings during the setup, or invoke the Configurator later on.

For more details on configuring SQL SRS-based reporting, refer to the NetWrix SQL Server Change Reporter help.

(12)

Editing the Scheduled Task Directly

SQL Server Change Reporter uses a standard Windows scheduled task called Netwrix SQL Server Change Reporter to schedule its operation. In addition to using the Change button in the program's configuration dialog box, you can edit the schedule and other parameters of this task directly in its properties.

©2010 NetWrix Corporation. All rights reserved. NetWrix SQL Server Change Reporter is a trademark of NetWrix Corporation and/or one or more of its subsidiaries, and may be registered in the U.S. Patent and Trademark Office and in other countries. All other trademarks and registered trademarks are the property of their respective owners.

NetWrix SQL Server Change Reporter. Quick Start Guide