This PowerShell script will check if all the metadata of a hub and member is in place and also validate the scopes against the information we have on the sync metadata database (among other validations). This script will not make any changes, will just validate data sync and user objects. It will also gather some useful information for faster troubleshooting during a support request.
In order to run it you need to:
- Open Windows PowerShell ISE
- Open a New Script window
- Paste the following in the script window (please note that, except databases and credentials, the other parameters are optional):
$parameters = @{
## Databases and credentials
# Sync metadata database credentials (Only SQL Authentication is supported)
SyncDbServer = '.database.windows.net'
SyncDbDatabase = ''
SyncDbUser = ''
SyncDbPassword = ''
# Hub credentials (Only SQL Authentication is supported)
HubServer = '.database.windows.net'
HubDatabase = ''
HubUser = ''
HubPassword = ''
# Member credentials (Azure SQL DB or SQL Server)
MemberServer = ''
MemberDatabase = ''
MemberUser = ''
MemberPassword = ''
# set MemberUseWindowsAuthentication to $true in case you wish to use integrated Windows authentication (MemberUser and MemberPassword will be ignored)
MemberUseWindowsAuthentication = $false
## Optional parameters (default values will be used if ommited)
## Health checks
HealthChecksEnabled = $true #Set as $true (default) or $false
## Monitoring
MonitoringMode = 'AUTO' #Set as AUTO (default), ENABLED or DISABLED
MonitoringIntervalInSeconds = 20
MonitoringDurationInMinutes = 2
## Tracking Record Validations
ExtendedValidationsTableFilter = @('All') #Set as "All" or the tables you need using '[dbo].[TableName1]','[dbo].[TableName2]'
ExtendedValidationsEnabledForHub = $true #Set as $true (default) or $false
ExtendedValidationsEnabledForMember = $true #Set as $true (default) or $false
ExtendedValidationsCommandTimeout = 900 #seconds (default)
## Other
SendAnonymousUsageData = $true #Set as $true (default) or $false
DumpMetadataSchemasForSyncGroup = '' #leave empty for automatic detection
DumpMetadataObjectsForTable = '' #needs to be formatted like [SchemaName].[TableName]
}
$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/AzureSQLDataSyncHealthChecker/master'
cls
Write-Host 'Trying to download the script file from GitHub (https://github.com/Microsoft/AzureSQLDataSyncHealthChecker), please wait...'
try {
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls
Invoke-Command -ScriptBlock ([Scriptblock]::Create((Invoke-WebRequest ($scriptUrlBase +'/AzureSQLDataSyncHealthChecker.ps1') -UseBasicParsing -TimeoutSec 60).Content)) -ArgumentList $parameters
}
catch {
Write-Host 'ERROR: The script file could not be downloaded:' -ForegroundColor Red
$_.Exception
Write-Host 'Confirm this machine can access https://github.com/Microsoft/AzureSQLDataSyncHealthChecker/' -ForegroundColor Yellow
Write-Host 'or use a machine with Internet access to see how to run this from machines without Internet. See how at https://github.com/Azure/SQL-Connectivity-Checker/' -ForegroundColor Yellow
}
#end-
Set the parameters on the script, you need to set server names, database names, users and passwords.
-
Run it.
-
The major results can be seen in the output window. If the user has the permissions to create folders, a folder with all the resulting files will be created. When running on Windows, the folder will be opened automatically after the script completes. When running on Azure Portal Cloud Shell the files will be stored in the file share (clouddrive). A zip file with all the files (AllFiles.zip) will be created.
- Detect issues and export action plans automatically is the next major goal.
This project collects usage data and sends it to Microsoft to help improve our products and services. Read our privacy statement to learn more. Telemetry can be disabled by setting the parameter SendAnonymousUsageData = $false (default value is $true).
Security issues and bugs should be reported privately, via email, to the Microsoft Security Response Center (MSRC) at secure@microsoft.com. You should receive a response within 24 hours. If for some reason you do not, please follow up via email to ensure we received your original message. Further information, including the MSRC PGP key, can be found in the Security TechCenter.
Copyright (c) Microsoft Corporation. All rights reserved.
Licensed under the MIT License.