SQLComparer

Functionality

• SQL diff viewer
• Comparing Stored Procedures across data sources
• Comparing a schema across data sources
• Pushing a Stored Procedure to selected data sources

Configuration

An appsettings.json file is necessary to run correctly. Create this file and place it in the root folder. Afterwards, make sure it is filled in entirely with your specific settings.

Example config file:

{
    "ConnectionStrings": {
        "dev": "Data Source=FirstSource;Initial Catalog=FirstDatabase;Integrated Security=true",
        "qa": "Data Source=SecondSource;Initial Catalog=SecondDatabase;Integrated Security=true",
        "prod": "Data Source=ThirdSource;Initial Catalog=ThirdDatabase;Integrated Security=true"
    },
    "DatabaseSettings": {
        "DefaultDatabase": "DefaultDb",
        "DefaultSchema": "dbo"
    },
    "Permissions": {
        "PushToProd": [
            "dbadmins"
        ],
        "PushToQA": [
            "dbadmins",
            "devs"
        ],
        "PushToDev": [
            "dbadmins",
            "devs"
        ],
        "MinimumAccess": [
            "dbadmins",
            "devs"
        ]
    },
    "ComparisonSettings": {
        "IgnoreWhitespace": true,
        "IgnoreNewLines": true,
        "IgnoreEndOfFile": true,
        "CaseInsensitive":  true  
    },
    "Logging": {
        "IncludeScopes": false,
        "LogLevel": {
            "Default": "Debug",
            "System": "Information",
            "Microsoft": "Information"
        }
    }
}

Adding more environments

By default 3 environments are supported: Dev, QA and Prod.
If you wish to add more environments, perform the following steps:

1. Add a new entry to the appsettings.json file

"ConnectionStrings": {
    ...,
    "Staging": "Data Source=StagingSource;Initial Catalog=StagingDb;Integrated Security=true"
},

2. Open SqlComparer.Web.Models.Options.ConnectionStrings

• Create a new property

public string Staging { get; set; }

• Edit GetConfigConnections() to provide an alias

return new List<Tuple<string, string>>
{
    ...,
    new Tuple<string, string>("staging", Staging)
}

3. (Optionally) Open SqlComparer.Web.Models.Options.Permissions to configure permissions

If no permissions are set, anybody will be able to push to it.

• Add a new property to store the allowed AD groups

public IList<string> PushToStaging { get; set; } = new List<string>();

• Edit CanPushToConnectionAliases() to include your newly configured alias and the corresponding permission config.

Authorization

Authorization is done using your Windows Identity. This will compare the user's groups to the groups configured in appsettings.json and allow/disallow based on that. Any database interaction happens as the user the server is running as -- no impersonation occurs at any time.