Applies to
Summary
SQL Server configuration is not something that is meant to be changed on a frequent basis. Once the optimum configuration settings have been set, altering them can lead to potential performance and security issues. Keeping track of all changes is time-consuming and prone to human error. This article describes how to create SQL Server comparison between two SQL Server instances or SQL Server snapshots to determine which SQL Server settings have been changed.
Description
SQL Server comparison is used to analyze differences and determine what changes occurred on the target SQL Server. A comparison between two instances can be used to adjust configuration on either instance or, by comparing an instance with a SQL Server snapshot, get a detailed report of changed configurations or added user roles. For this example, a clean SQL Server snapshot was created and changes were made on the live instance. The changes made are two new users and two new databases.
Before diving into the comparison, a SQL Server snapshot must be created, detailed information on the process can be found in Create SQL Server snapshots in ApexSQL Manage article.
Comparison
In order to perform the SQL Server comparison, a Source and Target are needed, as well as some filter configuration. This can be done by clicking the Compare button in the Comparison tab:
From the Compare window in the Data Sources tab a Source and a Target for the SQL Server comparison can be specified:
Both Source and Target have two types of selection, SQL Server or SQL Server snapshot:
Server and Server objects can be specified in the Filters tab. Hand-picking specific filters will provide a more selective comparison with key points in focus:
The Summary tab provides an overview of the comparison parameters. For this example, the Source where the changes have been made is a live SQL Server and the Target (Destination) is a previously made “clean” SQL Server snapshot:
Comparison details
The first visible difference of the SQL Server comparison is in the Compared tab.
The PhysicalMemory property from the SERVER (Information) shows that the source value has increased, this is because two databases were added after creating the SQL Server snapshot:
In the Equal tab are all the compared SQL Server properties between the SQL instance and the SQL Server snapshot which resulted with no difference:
Since the two new logins and the two databases were not present in the SQL Server snapshot, they can be found in the Incomparable tab:
Additionally, if the two logins were, by malicious intent or even by mistake, given roles which they shouldn’t have, performing a SQL Server comparison of the live SQL Server with the previously made SQL Server snapshot will show the differences between them. In this example, a SQL Server snapshot was created encompassing the two new logins, Nenad1 and Nenad2, but the logins only had public roles. Upon creating the snapshot, the role of serveradmin has been given to Nenad1 login and the roles of sysadmin and securityadmin were given to the Nenad2 login.
Comparing the live SQL Server instance with the SQL Server snapshot shows that there were additions to the logins. Selecting the login Nenad1 in the Source category column will show details of the SQL Server comparison.
The Property column shows what was changed and when, in this case, the Roles for the selected login.
The Source value column shows what role was given and the exact time when it happened on the live SQL instance.
The Destination value column shows the previous state of the selected login.
Since additional roles were given to the login Nenad2, they are also shown in the Source value column:
