Commands / executables used for MSSQL on EC2 backups and restores

Commands used in heartbeats

Powershell file locationDescriptionPowershell Commands usedTSQL commands used
hcm/hcmcommon/powershell/install_mssql_ec2_binaries.ps1Script to install binaries/excutables on hostGet-Service

Get-CimInstance

Remove-CimInstance (used only if CJtracker is present)

[System.Environment]: :SetEnvironmentVariableCopy-S3Object

Remove-Item (only in binaries folder)

new-service (to register CJTracker service)

Start-Service
NA
hcm/hcmcommon/powershell/upgrade_mssql_ec2_binaries.ps1Script to upgrade binaries on hostsame commands as aboveNA
hcm/hcmcommon/powershell/copy_host_key.ps1Used to copy host encryption to hostCopy-S3ObjectNA
lib/mssql/common_powershell_script/check_sys_admin_permission.ps1Used to verify pre-req on windows host[System.Security.Principal.WindowsIdentity]: :GetCurrent().NameGet-Service

vssadmin list writers
select IS_SRVROLEMEMBER
lib/mssql/common_powershell_script/mssql_ec2_normal_heartbeat.ps1Normal heartbeat scriptGet-ServiceNA
lib/mssql/common_powershell_script/mssql_ec2_system_hb.ps1System heartbeat scriptGet-WmiObject

Get-NetAdapter

Get-CimInstance
NA

 

Commands used for inventory

PowerShell file locationDescriptionPowershell Commands usedTSQL commands used
inventory/invmssql/invagentmssql/powershell/get_all_services_v2.ps1Script to get all SQL server service on ec2-instanceGet-ServiceNA
inventory/invmssql/invagentmssql/powershell/instance_details_using_sql.ps1Script to get SQL server instance data for inventoryInvoke-SQLCMDAll are select queries, all we need is select access

SELECT * from sys.databases

SELECT * FROM sys.availability_databases_cluster

SELECT * FROM sys.master_files

SELECT * FROM sys.dm_hadr_availability_group_states

SELECT * FROM sys.availability_groups

SELECT * FROM sys.availability_replicas

SELECT * FROM sys.dm_hadr_availability_group_states
inventory/invmssql/invagentmssql/powershell/ag_db_details.ps1Script to get ag database detailsAll are select queries, need select access in master DB

same as above

 

Commands used for backup and restore

PowerShell file locationDescriptionPowershell Commands usedTSQL commands used
dpmssql/dpmssqlagent/Powershell/backuplog.ps1Script for log backupInvoke-SQLCMDbackup log
dpmssql/dpmssqlagent/Powershell/get_backup_history.ps1Script to get db backup historyInvoke-SQLCMDSELECT Top 1 backup_start_date, backup_finish_date, first_lsn, last_lsn, database_backup_lsn, time_zone, recovery_model,checkpoint_lsn, last_recovery_fork_guid FROM msdb.dbo.backupset
dpmssql/dpmssqlagent/Powershell/get_db_file_types.ps1Script to get file type of databaseInvoke-SQLCMDSELECT type, type_desc, sys.master_files.name, physical_name FROM sys.master_files INNER JOIN sys.databases ON sys.databases.database_id = sys.master_files.database_id
dpmssql/dpmssqlagent/Powershell/recover_database.ps1Script to recover the databaseInvoke-SQLCMDrestore database
dpmssql/dpmssqlagent/Powershell/restorelog.ps1script to recover log backup fileInvoke-SQLCMDrestore log
dpmssql/cjtracker/Powershell/get_cjtracker_stats.ps1Script used by cj tracker for statsRead process stats 

 

Run Custom command Allowed commands

PowerShell Commands(CommandType = 1):

1: "ls"  
2: "vssadmin list writers"  
3: "Get-Service -DisplayName \"SQL Server (*\""  
4: "vssadmin list providers"  
5: "Get-Service"  
6: "vssadmin list shadows"  
7: "(Get-ChildItem $Env:CLUMIO_INSTALL_DIR\\logs).Count"  
8: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
9: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddHours(-1)) | Format-List"  
10: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
11: "Get-EventLog -LogName 'Application' -Source 'MSSQLSERVER' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
12: "Get-EventLog -LogName 'System' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
13: "Get-EventLog -LogName 'System' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
14: "Get-EventLog -LogName 'Application' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
15: "Get-EventLog -LogName 'Application' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
16: "Get-Counter \"\\Process(sqlservr)\\% Processor Time\" -SampleInterval 30"  
17: "Get-Counter \"\\Process(sqlservr)\\IO Read Bytes/sec\" -SampleInterval 30"  
18: "Get-Counter \"\\Process(sqlservr)\\IO Write Bytes/sec\" -SampleInterval 30"  
19: "Get-Counter \"\\Process(sqlservr)\\IO Read Operations/sec\" -SampleInterval 30"  
20: "Get-Counter \"\\Process(sqlservr)\\IO Write Operations/sec\" -SampleInterval 30"  
21: "Get-Counter \"\\Process(_total)\\% Processor Time\" -SampleInterval 30"  
22: "Get-Counter \"\\Process(idle)\\% Processor Time\" -SampleInterval 30"  
23: "Get-WmiObject -Class Win32_Processor | Select-Object -Property * "  
24: "fsutil usn readjournal", // Pass arguments = ['C:', 'startusn=0', 'csv']  
25: "winsat disk"  
26: "Get-WmiObject Win32_Product" // used to get all installed application on windows host  
27: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\amazon-ssm-agent.log" // used to get info logs of ssm  
28: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\errors.log" // used to get error logs of ssm  
29: "Restart-Service AmazonSSMAgent"  
30: "fsutil usn"  
31: "Get-Process"  
32: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-agent-worker.log"  
33: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-document-worker.log"  
34: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-session-worker.log"  
35: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\hibernate.log"  
36: "chkdsk" // only used to get state of the disk

TSQL commands(CommandType = 2)

1: "select * from sys.databases"  
2: "select * from sys.availability_groups"  
3: "select * from sys.availability_replicas"  
4: "select * from sys.availability_databases_cluster"  
5: "select * from sys.dm_hadr_availability_group_states"  
6: "select @@version"  
7: "select * from sys.master_files"  
8: "select * from msdb.dbo.backupset"

 

Executables used for mssql on ec2

Clumio ExecutablesShort descriptionExecutable location relative to binary path
cjtracker.exeUsed for tracking changed blocked in mdf and ldf files%binary_path%/mssql/dp/cjtracker.exe
dpmssqlcloudagent.exeResponsible for taking mssql server instance snapshot%binary_path%/mssql/dp/dpmssqlcloudagent.exe
uploader.exeResponsible to upload chunks from host to S3%binary_path%/mssql/dp/uploader.exe
restoreagent.exeResponsible for restoring database%binary_path%/mssql/dp/restoreagent.exe
winhostutil.exeResponsible for inventory sync files upload and log collection%binary_path%/hcm/winhostutil.exe