PowerShell: Automatically Fix SQL Server by Killing Long Running Queries

# autofixSqlLongRunningQueries.ps1
# version 0.01
# This version is limited to SQL Server Localhost execution, Not Remote [yet]

# Kill session_id basing on these variables
$sqlServername=$env:computername
$maxBlockDuration=3600000 # 1 hour = 3600000 ms
$maxRunningDuration=3600000
$excludeKeywords='BACKUP','ROLLBACK'

function autofixSqlLongRunningQueries{
    param(
        $sqlServername=$env:computername,
        $maxBlockDuration=3600000,
        $maxRunningDuration=3600000,
        $excludeKeywords=@('BACKUP','ROLLBACK')
    )
    $checkRunningQueries="
    USE Master
    SELECT s.TEXT,r.total_elapsed_time,r.session_id,r.status,r.command
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) s
    " 
    $checkBlockingQueries="
        USE Master
        GO
        SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
        FROM sys.dm_os_waiting_tasks 
        WHERE blocking_session_id <> 0
        GO
        "
    function killSqlSession($sessionId){
        return "
        USE Master
        GO
        KILL $sessionId
        "
    }
    $runningQueries=try{
            $result=Invoke-Sqlcmd -ServerInstance $sqlServername -Query $checkRunningQueries
            $result|?{$_.total_elapsed_time -ge $maxRunningDuration -and $($excludeKeywords|?{$_.TEXT -notmatch $_}) }
        }catch{write-warning $_}
    $blockingQueries=try{
            $result=Invoke-Sqlcmd -ServerInstance $env:computername -Query $checkBlockingQueries
            $result=|?{$_.wait_duration_ms -ge $maxBlockDuration}
        }catch{write-warning $_}
    $sessionsToKill=$runningQueries.session_id+$blockingQueries.blocking_session_id
    foreach($sessionId in $sessionsToKill){
        $killSessionCommand=killSqlSession $sessionId
        write-host $killSessionCommand
        # pause
        Invoke-Sqlcmd -ServerInstance $sqlServername -Query $killSessionCommand
    }
}

autofixSqlLongRunningQueries $sqlServername `
    $maxBlockDuration `
    $maxRunningDuration `
    $excludeKeywords

T-SQL: Ad-hoc Commands to Backup All Databases

-- turn on Windows cmd shell
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

-- use Windows command to map a drive
EXEC XP_CMDSHELL 'net use B: \\SMBSERVER\ShareName'
-- check the drive
EXEC XP_CMDSHELL 'Dir B:'

-- backup all databases, AD-HOC method, not recommended for daily backups
DECLARE @bk_path VARCHAR(256);
DECLARE @db_name VARCHAR(50);
DECLARE @bk_file_date VARCHAR(20);
DECLARE @bk_file_name VARCHAR(256);
DECLARE @i INT = 1;
 
/* Set backup path */
SET @bk_path = 'B:\Backup\';
 
/* Backup file format DBname_YYYYMMDD_HHMMSS.BAK. Change this as needed. */
SELECT @bk_file_date = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss');
 
DECLARE @db_names TABLE (
   id INT IDENTITY(1,1) PRIMARY KEY, 
   db_name VARCHAR(50) NOT NULL );
 
INSERT INTO @db_names
   SELECT name 
   FROM master.sys.databases 
   WHERE name NOT IN ('master','model','msdb','tempdb')  /* Databases which needs to be excluded */
      AND state = 0 /* Include only the database which are online */
      AND is_in_standby = 0; /* database is not read only for log shipping */
 
WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
    
   SELECT @db_name = db_name from @db_names WHERE Id = @i;
   PRINT 'Backup Started: ' + @db_name
    
   SET @bk_file_name = @bk_path + @db_name + '_' + @bk_file_date + '.BAK';
 
   BACKUP DATABASE @db_name TO DISK = @bk_file_name WITH STATS;
 
   SET @i = @i + 1;
    
END
GO

-- delete a mapped drive after back has completed
EXEC XP_CMDSHELL 'net use B: /delete'

Microsoft SQL Server Connection Timeout

Error Message:

Resolution:

a. Check SQL Server’s Query Execution Timeout: SSMS > Login > Tools > Options > Query Execution

b. Test port 1433 (known common default sql port) from client to server

# Quick commands
$servername='sql01'
Test-NetConnection $servername -port 1433
portqry.exe -n $servername -p tcp -e 1433
# Sample output
PS C:\Users\sqladmin> Test-NetConnection $servername -port 1433
ComputerName : sql01
RemoteAddress : x.x.x.x
RemotePort : 1433
InterfaceAlias : Ethernet
SourceAddress : y.y.y.y
TcpTestSucceeded : True

PS C:\Users\sqladmin> portqry.exe -n $servername -p tcp -e 1433
Querying target system called:
sql01
Attempting to resolve name to IP address...
Name resolved to x.x.x.x
querying...
TCP port 1433 (ms-sql-s service): LISTENING

Microsoft SQL Server Replication Setup Error Number MSSQL_REPL2110 and Resolution

Symptom:

The distribution agent failed to create temporary files in ‘C:\Program Files\Microsoft SQL Server\130\COM’ directory
The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\130\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)
Get help: http://help/MSSQL_REPL21100

Resolution:
– Add ‘NT Service\SQLSERVERAGENT’ account FULL permissions into the ‘C:\Program Files\Microsoft SQL Server\[NNN]\COM’ directory (e.g. ‘C:\Program Files\Microsoft SQL Server\130\COM’)
– Assuming that the default account has been used as the run-as account for the SQL Server Agent service.
– Assuming the referenced account above is assigned to the replication job.
– If the Distribution Agent is invoked from a command line, one must grant write permissions to the COM folder for the account that is used to run the Distribution Agent.

SQL: Truncate Transaction Logs

Overview:

Significance: many small teams lack Database Admin resource to babysit SQL databases; therefore, certain transaction logs would grow to be oversized and cause performance issues with SQL Server. Here are the comparisons:

SIMPLE: the SQL Server Transaction Log would automatically truncate on every transaction being committed if Database is configured with the Simple recovery model. This also happens when a Checkpoint operator is triggered.

FULL: the transaction log (LDF) will grow continuously. It will be cleared when a backup of the transaction log triggered (e.g. BACKUP LOG dbname TO DISK = ‘DB_LOG.bak’.

BULK LOGGED: minimal log space usage mode. The transaction log files would clear during a backup operation. There is no automatic log truncation prior.

Practical T-SQL Commands
-- Get Recovery Model of All Databases:
SELECT Name,DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]
FROM master.dbo.sysdatabases

-- Sample Output
Name	Recovery Model
master	SIMPLE
tempdb	SIMPLE
model	SIMPLE
msdb	SIMPLE
TestDB1	FULL
TestDB2	SIMPLE

-- Check log sizes 
DBCC SQLPERF (LOGSPACE);
GO

-- Sample output
Database Name	Log Size (MB)	Log Space Used (%)	Status
master	2.242188	39.89547	0
tempdb	71.99219	20.26587	0
model	7.992188	17.64418	0
msdb	28.80469	5.994033	0
TestDB1	71.99219	5.225176	0
TestDB2	7.992188	29.86315	0

-- Get logical name of log file
SELECT name,physical_name FROM sys.master_files WHERE type_desc = 'LOG'

-- Shrink database log
USE [TestDB1]
GO
DBCC SHRINKFILE (N'TestDB1_LOG' , 0, TRUNCATEONLY)
GO

-- Shrink log by performing backup
BACKUP LOG [TestDB1] TO T:\TestDB1_log.bak

-- Set recovery model to SIMPLE and shrink log to 8GB
USE [TestDB1]
ALTER DATABASE [TestDB1] SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([TestDB1_log], 8192);
GO

-- Set recovery model to SIMPLE and shrink log to 1MB
USE TestDB1
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TestDB1
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB
DBCC SHRINKFILE (TestDB1_log, 1);
GO
-- Optional: reset the database recovery model to FULL mode  
ALTER DATABASE TestDB1  
SET RECOVERY FULL;  
GO

-- Change recovery model to FULL
ALTER DATABASE [TestDB1] SET RECOVERY FULL
GO

-- Truncating Data File - NOT log file
USE TestDB1;  
GO  
SELECT file_id, name  -- obtain file_id prior to truncation
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY); 

-- Set all databases recovery model to SIMPLE and truncate all logs
declare @db_name nvarchar(124)
declare cursor cursor FOR
SELECT name AS DBName
FROM sys.databases
where name NOT IN ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor
FETCH NEXT FROM cursor INTO @db_name
    WHILE (@@FETCH_STATUS=0)
    BEGIN
        exec ('declare @logname nvarchar(124)
    USE [' + @db_name + ']
        SELECT @logname = name FROM sys.database_files where type = 1
        ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
        DBCC SHRINKFILE (@logname , 0, TRUNCATEONLY)')
        ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
        FETCH NEXT FROM cursor INTO @db_name
    END
CLOSE cursor
DEALLOCATE cursor

T-SQL: How To Rename Database in Microsoft SQL Server

The T-SQL

USE master;  
GO  
ALTER DATABASE TEST_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TEST_MSCRM MODIFY NAME = RENAMED_MSCRM;
GO  
ALTER DATABASE RENAMED_MSCRM SET MULTI_USER;
GO

Sample Output

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
The database name 'RENAMED_MSCRM' has been set.

Completion time: 2012-10-29T16:09:11.0019350-07:00

PowerShell: Detect Microsoft SQL Version and Installed Location



function getSqlInfo{
    $results=@()
    $instances=(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
    foreach ($i in $instances){
        $p=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
        $instance=Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup"
        $name=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
        $edition=$instance.Edition
        $version=$instance.Version
        $label = switch ($version) {
                "14.0.3076.1" {"SQL Server 2017 CU14"; break}
                "14.0.3006.16" {"SQL Server 2017 CU1"; break}
                "14.0.2000.63" {"SQL Server 2017 RTM with Security Update KB4057122"; break} 
                "14.0.1000.169" {"SQL Server 2017 (vNext) RTM"; break}
                "13.0.1601.5" {"SQL Server 2016 RTM"; break}
                "12.0.5207.0" {"SQL Server 2014 SP2 with Security Update KB4019093"; break}
                "12.2.5000.0" {"SQL Server 2014 SP2"; break}
                default {"Unknown Version"; break}
            }
        $bootStrap=switch ($version.Substring(0,2)) {
        "14" {"C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"; break} 
        "13" {"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"; break}
        "12" {"C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"; break}
        "11" {"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"; break}
        default {"Unknown"; break}
        }
    $bin=switch ($version.Substring(0,2)) {
        "14" {"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"; break} 
        "13" {"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"; break}
        "12" {"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn"; break}
        "11" {"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn"; break}
        default {"Unknown"; break}
        }
        $backupDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer" -Name 'BackupDirectory'
        $dataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\Setup" -Name 'SQLDataRoot'
        $masterDataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg0' | ForEach-Object {$_.Substring(2)}
        $masterLogDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg2' | ForEach-Object {$_.Substring(2)}
    $result=[pscustomobject]@{
        version=$version
        name=$name
        edition=$edition
        label=$label
        backupDirectory=$backupDirectory
        dataDirectory=$dataDirectory
        masterDataDirectory=$masterDataDirectory
        masterLogDirectory=$masterLogDirectory
        bin=$bin
        bootStrap=$bootStrap
        }
    $results+=$result
    }
    return $results
}
PS C:\Users\sqlAdmin> getSqlInfo

bin                 : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn
bootStrap           : C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017
version             : 14.0.1000.169
name                : MSSQL14.MSSQLSERVER
edition             : Standard Edition
label               : SQL Server 2017 (vNext) RTM
backupDirectory     : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup
dataDirectory       : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL
masterDataDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
masterLogDirectory  : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

PowerShell: Get SQL Job History

$computername='sql01'

function getSqlJobHistory($sqlServerName){
  try{
    if(!(get-module SqlServer)){
      Install-Module -Name SqlServer
    }
    Import-Module -Name SqlServer
    $sqlServerInstance=Get-SqlInstance -ServerInstance $sqlServerName
    $sqlAgent=Get-SqlAgent -ServerInstance $sqlServerInstance.Name
    $sqlJobs=$sqlAgent|Get-SqlAgentJob|Get-SqlAgentJobSchedule|?{$_.IsEnabled}
    $sqlJobs|select-object Name,ActiveStartTimeOfDay,FrequencyTypes,FrequencySubDayTypes|ft -autosize
  }catch{
    write-warning $_
    return $false
  }
}

getSqlJobHistory $computername
PS C:\Users\sqladmin1> getSqlJobHistory sql01

Name                               ActiveStartTimeOfDay FrequencyTypes FrequencySubDayTypes
----                               -------------------- -------------- --------------------
Check Database Integrity.Subplan_1 03:00:00                     Weekly                 Once
Differential Database Backups      22:00:00                      Daily                 Hour
Daily 10PM                         22:00:00                      Daily                 Once
Beginning of Month                 00:00:00                    Monthly                 Once
Schedule                           00:30:00                      Daily                 Once
syspolicy_purge_history_schedule   02:00:00                      Daily                 Once
Schedule1                          00:00:00                      Daily               Second

PowerShell: Get SQL Server Backup Statuses

$computername='sql01'

function getSqlBackupInfo ($sqlInstanceName=$env:computername, $dbName){
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  
  $location=if($sqlInstanceName.Contains("`\")){
      "SQLSERVER:\SQL\$sqlInstanceName\Databases"
    }else{
      "SQLSERVER:\SQL\$sqlInstanceName\DEFAULT\Databases"
    }
    function getPacificTime($time){
      if($time){
        [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($time,'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
      }else{
        [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId((Get-Date),'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
      }      
    }
    $displayFormat=@{Label='dbName';Expression={$_.Name}},
        @{Label='lastFull';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'NA'}
            ELSE {getPacificTime $_.LastBackupDate}}},
        @{Label='lastDifferential';Expression={IF ($_.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
             ELSE {getPacificTime $_.LastDifferentialBackupDate}}},
        # @{Label='mostRecentBackupType';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
        #     ELSEIF ($_.LastBackupDate -gt $_.LastDifferentialBackupDate) {'FULL'}
        #     ELSE {'DIFF'}}},
        # @{Label='daysSinceLastBackup';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed Up!"}
        #     ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-Date) – $_.LastDifferentialBackupDate).Days}
        #     ELSE {((Get-Date)-$_.LastBackupDate).Days}}},
        @{Label='dbLocation';Expression={$_.PrimaryFilePath}}
  if ($dbName){
    ls -force $location | where-object {$_.Name -eq $DBName; $_.Refresh()} |ft $displayFormat
  }else{
    ls -force $location | where-object {$_.Name -notin 'tempdb','master','model','msdb'; $_.Refresh()} |ft $displayFormat
  }
}

getSqlBackupInfo $computername

Use PowerShell to Set Microsoft SQL Database Owner

$owner='CAP\SQL Admins'
$databaseName='TestDb'
$sqlServer=$env:computername

function setDbOwner{
  param(
    $principle=$env:USERDOMAIN+'\Domain Admins',
    $databaseName='TestDB',
    $sqlServer
  )
  
  function includeSqlTools{
    $ErrorActionPreference='stop'
    try{
      $trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted'
      if(!$trustedPsgallery){
          Set-PSRepository -Name PSGallery -InstallationPolicy Trusted 
      }
      if(!(Get-Module sqlserver)){
          Install-Module sqlserver -Confirm:$False
      }
      if(!(Get-Module dbatools)){
          Install-Module dbatools -Confirm:$False
      }
      Import-Module sqlserver  
      Import-Module dbatools
      return $true
    }catch{
      write-warning $_
      return $false
    }
  }

  try{
    if(!(includeSqlTools)){
      write-warning "Cannot proceed with SQL Tools"
      return $false
    }
    $server=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
    $db=New-Object Microsoft.SqlServer.Management.Smo.Database
    $db=$server.Databases.Item($databaseName)
    $db.SetOwner($principle, $TRUE)
    $db.Alter()
    return $true
  }catch{
    Write-Warning
    return $false
  }
}

setDbOwner $owner $databaseName $sqlServer

Use PowerShell to Grant SysAdmin Role to Certain Users

$principle=$env:USERDOMAIN+'\Domain Admins'
$sqlServer=$env:computername

function includeSqlTools{
  $ErrorActionPreference='stop'
  try{
    $trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted'
    if(!$trustedPsgallery){
        Set-PSRepository -Name PSGallery -InstallationPolicy Trusted 
    }
    if(!(Get-Module sqlserver)){
        Install-Module sqlserver -Confirm:$False
    }
    if(!(Get-Module dbatools)){
        Install-Module dbatools -Confirm:$False
    }
    Import-Module sqlserver  
    Import-Module dbatools
    return $true
  }catch{
    write-warning $_
    return $false
  }
}

function grantSysadmin($principle,$sqlServer=$env:computername){
    if(!(includeSqlTools)){
        write-warning "Unable to proceed without SQL Tools"
        return $false
    }
    $sqlPortOpen=test-netconnection $sqlServer -port 1433 -informationlevel quiet
    $result=.{if($sqlPortOpen){
      try{
        $server=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
        $sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
        $sysadmins.AddMember($principle)
        write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
        return $true
      }catch{
        Write-Warning
        return $false
      }
    }else{
      # Workaround for firewall issues blocking SQL port between jump host and SQL Server
      invoke-command -computername $sqlServer {
        param($principle,$includeSqlTools)
        $sqlTools=[scriptblock]::create($sqlTools).invoke()
        if(!$sqlTools){
          write-warning "Unable to proceed without SQL Tools"
          return $false
        }
        try{
          $server=New-Object Microsoft.SqlServer.Management.Smo.Server("(localhost)")
          $sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
          $sysadmins.AddMember($principle)
          write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
          return $true
        }catch{
          write-warning $_
          return $false
        }
      } -Args $principle,${function:includeSqlTools}
    }
  }
  return $result   
}

grantSysadmin $principle $sqlServer

How To Move WordPress Site To Kubernetes Cluster

a. Create backups of source files and database

  - Logon to Current Hosting Provider to make backups
  - Files:
    - Assuming cPanel:
      - Login to cPanel
      - Click on 'File Manager'
      - Select public_html or the directory containing WordPress files
      - Select Compress from the top-right menu
      - Select 'Bzip2ed Tar Archive' (better compression than Gzip)
      - Click 'Compress File(s)' and wait for the process to finish
      - Right-click the newly generated public_html.tar.bz2 from cPanel File Manager > select Download
      - Find the file in a default download directory (e.g. /home/$(whoami)/Downloads/public_html.tar.bz2)
  - Database:
    - Assuming cPanel with phpMyAdmin
      - Click 'phpMyAdmin' from the 'DATABASES' control group
      - Click 'Export'
      - Set Export method = Quick, Format = Custom
      - Click Go
      - Find the *.sql file being downloaded into a default download directory (e.g. /home/$(whoami)/Downloads/localhost.sql)

b. Install Bitnami WordPress in a Kubernetes Cluster

# Add helm chart if not already available
helm repo add bitnami https://charts.bitnami.com/bitnami

# Install WordPress with Dynamic NFS Provisioning
# Documentation: https://hub.kubeapps.com/charts/bitnami/wordpress/10.0.1
# Set variables
appName=kimconnectblog
domainName=blog.kimconnect.com
wordpressusername=kimconnect
wordpressPassword=SOMEPASSWORDHERE
rootPassword=SOMEPASSWORDHERE2
storageClass=nfs-client
# Install
helm install $appName bitnami/wordpress \
  --set persistence.accessMode=ReadWriteMany,persistence.storageClass=nfs-client \
  --set mariadb.primary.persistence.storageClass=nfs-client \
  --set wordpressUsername=$wordpressusername,wordpressPassword=$wordpressPassword \
  --set mariadb.auth.rootPassword=$rootPassword \
  --set mariadb.auth.password=$rootPassword \
  --set ingress.enabled=true,ingress.hostname=$domainName
# Patch the deployed ingress with an existing SSL cert
# Assuming the $appName-cert has already been generated
appName=kimconnectblog
domainName=blog.kimconnect.com
certName=$appName-cert
serviceName=$appName-wordpress
servicePort=80
cat <<EOF > $appName-patch.yaml
spec:
  tls:
  - hosts:
    - $domainName
    secretName: $certName
  rules:
  - host: $domainName
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: $serviceName
            port:
              number: $servicePort         
EOF
kubectl patch ingress/$appName-wordpress -p "$(cat $appName-patch.yaml)"

c. Import files and database onto new hosting server

  - Database:
    - Access DB server and import sql dump
      podName=kimconnectblog-mariadb-0
      kubectl exec --stdin --tty $podName -- /bin/bash
      rootPassword=SOMEPASSWORD
      echo "show databases;" | mysql -u root -p$rootPassword
      MariaDB [(none)]> show databases;exit;
        +--------------------+
        | Database           |
        +--------------------+
        | bitnami_wordpress  |
        | information_schema |
        | mysql              |
        | performance_schema |
        | test               |
        +--------------------+
        5 rows in set (0.009 sec)
      oldDb=kimconne_blog
      sqlDump=/bitnami/mariadb/data/kimconnect.sql
      mysql -uroot -p$rootPassword test < $sqlDump
      grantUser=bn_wordpress # this is the default Bitnami WordPress user
      echo "GRANT ALL PRIVILEGES ON $oldDb.* TO $grantUser;" | mysql -uroot -p$rootPassword
      #echo "create database $databaseName;" | mysql -uroot -p$rootPassword
      #mysql -uroot -p$rootPassword $oldDb -sNe 'show tables' | while read table; do mysql -uroot -p$rootPassword -sNe "RENAME TABLE $oldDb.$table TO $newDb.$table"; done
      #echo "create user kimconne_blog@localhost;grant all privileges on kimconne_blog.* to 'kimconne_blog';"| mysql -uroot -p$rootPassword
      #ALTER USER 'kimconne_blog'@'localhost' IDENTIFIED BY 'SOMEPASSWORDHERE';
  - Files:
    - Assuming nfs:
      nfsShare=k8s
      nfsServer=10.10.10.5
      sharePath=/volume1/$nfsShare
      mountPoint=/mnt/$nfsShare
      sudo mkdir $mountPoint
      sudo mount -t nfs $nfsServer:$sharePath $mountPoint # Test mounting
      sudo mount | grep $nfsShare # validate mount
      # Assuming Kubernetes NFS
      # sudo mv /home/$(whoami)/Downloads/localhost.sql $mountPoint/path_to_default-data-sitename-mariadb/data/localhost.sql
      # sudo mv /home/$(whoami)/Downloads/public_html.tar.bz2 $mountPoint/public_html.tar.bz2
      bz2File=/mnt/k8s/kimconnectblog/public_html.tar.bz2
      containerPath=/mnt/k8s/default-kimconnectblog-wordpress-pvc-9f1dd4bd-81f3-489f-9b76-bf70f4fd291c/wordpress/wp-content
      tar -xf $bz2File -C $containerPath
      cd $containerPath
      mv public_html/wp-content wp-content
      vim wp-config.php # edit wp config to match the imported database and its prefix

Microsoft SQL: Login failed for user ‘sa’. (.Net SqlClient Data Provider)

Error:
===================================
Cannot connect to SQL-SERVER
===================================
Login failed for user 'sa'. (.Net SqlClient Data Provider)
Resolution:

1. Ensure that the ‘sa’ account is enabled: security > right click user sa > Properties > Status > Enabled > save
2. Enable SQL Server & Windows authentication: right click on the server > Security > select radio button next to ‘SQL and Windows Mode’ > restart SQL or the OS

How to Get Table Sizes of a Database in Microsoft SQL Server

Obtain Table Sizes of One or Multiple Names

-- Get sizes of multiple tables
use DATABASENAME
GO
IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
	DROP TABLE #SpaceUsed

CREATE TABLE #SpaceUsed (
	 TableName sysname
	,NumRows BIGINT
	,ReservedSpace VARCHAR(50)
	,DataSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)
	) 

DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed 
EXEC sp_msforeachtable @command1=@str

SELECT TableName, NumRows, 
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpaceMB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpaceMB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpaceMB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpaceMB
FROM #SpaceUsed
WHERE TableName IN ('[dbo].[Table1]','[dbo].[Table2]')
ORDER BY TableName asc

Obtain Table Sizes of All Databases

-- Get sizes of all tables
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Microsoft SQL Server: How to Use T SQL to Move Transaction Log Files

Automated Script:

# moveDbStorage.ps1
# Version 0.0.1
# This version is intended for a local MS SQL Server
# Requirements:
# - Integrationed authentication has been enabled within local SQL Server
# - Each database is expected to have been configured with 1 db file and 1 transaction logs file. Deviation to this will be handled in next script version.

$intendedDbLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
$intendedDbLogLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
# $intendedDbBackupLocation='Z:\Backups' # this iteration doesn't deal with backups
function moveDbStorage($databaseName,$intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation){
$erroractionpreference='stop'
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;                
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }

    $results=[hashtable]@{}
    try{
        # Ensure that the server has SQL PowerShell tools 
        if(!(Get-Module -ListAvailable -Name 'sqlps' -ea SilentlyContinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        $nugetInstalled=Get-PackageProvider nuget
        if(!$nugetInstalled){Install-PackageProvider -Name Nuget -RequiredVersion 2.8.5.201 -Force}          
        $trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
        If($trustPSGallery -ne 'Trusted'){
            Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
            }
        Install-Module PSWindowsUpdate -Confirm:$false -Force
        }         
        # Setting correct ACLs on provided storage locations
        $sqlInstance='MSSQLSERVER' # Assming default instance
        $sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='$sqlInstance'").StartName
        foreach ($path in @($intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation)){
            write-host "Setting correct permissions on $path"
            if(!(Test-Path $path)){
                $null=New-Item -ItemType Directory -Force -Path $path
            }
            try{
                write-host "Granting $env:username and Administrators full access to $path..."
                $acl=Get-ACL $path
                $originalOwnerAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($acl.Owner,"FullControl","Allow")
                $administratorsAccess=New-Object System.Security.AccessControl.FileSystemAccessRule('Administrators',"FullControl","Allow")
                #$thisUserAccess = New-Object System.Security.AccessControl.FileSystemAccessRule($env:username,"FullControl","Allow")
                $sqlAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"FullControl","Allow")
                $acl.AddAccessRule($originalOwnerAccess)
                $acl.AddAccessRule($administratorsAccess)                
                #$acl.AddAccessRule($thisUserAccess)
                $sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
                if(!$sqlHasFullPermissions){
                    $acl.AddAccessRule($sqlAccess)
                }                           
                Set-Acl $path $acl
            }catch{
                Write-warning $_
            }
        }

        # Get default locations    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        # $sqlServer=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
        # $defaultBackupDirectory=$sqlServer.Settings.BackupDirectory
        # $defaultDataDirectory=$sqlServer.Settings.DefaultFile
        # $defaultLogDirectory=$sqlServer.Settings.DefaultLog
        # Change Default backup directory
        # if($defaultBackupDirectory -ne $intendedDbBackupLocation){
        #     write-host "Setting default backup location to $dbBackupLocation"
        #     $sqlServer.Properties["BackupDirectory"].Value = $dbBackupLocation
        #     $sqlServer.Alter()
        # }

        # Change database storage locations
        $databases=Get-SqlDatabase -ServerInstance $env:computername
        # $nonSystemDatabases=$databases|?{$_.Name -notin @('master','model','msdb','tempdb')}|sort -property Size
        $nonSystemDatabases=$databases|?{$_.IsSystemObject -ne $true}|sort -property Size
        Clear-Host
        $confirmedAll=confirmation 'RUN ALL CHANGES AUTOMATICALLY'
        
        foreach($database in $nonSystemDatabases){
            #$database=$nonSystemDatabases[0]
            $dbName=$database.Name
            $tsql="exec sp_helpdb @dbname='$dbName'"
            $dbProfile=Invoke-Sqlcmd -Query $tsql -ServerInstance $env:computername
            if($null -eq $dbProfile){
                write-warning "$dbName status is currently unknown"
                $results[$dbName]="$(get-date) : status unknown - no changes"
            }else{
                $filePaths=$dbProfile.GetEnumerator()|%{$_.filename}
                $dbFile=$filePaths[1]
                $dbLogFile=$filePaths[2]
                $intendedDbFile=Join-Path -Path $intendedDbLocation -ChildPath $($dbName+'.mdf')
                $intendedDbLogFile=Join-Path -Path $intendedDbLogLocation -ChildPath $($dbName+'_log.ldf')
                $moveDbStorage=$dbFile -ne $intendedDbFile
                $moveDbLogStorage=$dbLogFile -ne $intendedDbLogFile
                if($moveDbStorage -or $moveDbLogStorage){
                    # Alternative method:
                    # $logicalFileNames=Invoke-Sqlcmd -Query "
                    #     USE $dbName;
                    #     GO
                    #     SELECT file_id, name AS logical_name
                    #     FROM sys.database_files"
                    # $dbLogicalName=$logicalFileNames.logical_name[0]
                    # $dbLogLogicalName=$logicalFileNames.logical_name[0]
                    # $tSqlAlterInPlace="
                    #     Use MASTER
                    #     GO
                    #     ALTER DATABASE [$dbName] SET OFFLINE;  
                    #     GO
                    #     ALTER DATABASE [$dbName]
                    #         MODIFY FILE (NAME = '$dbLogicalName',   
                    #         FILENAME = '$intendedDbFile');  
                    #     GO
                    #     ALTER DATABASE [$dbName]   
                    #         MODIFY FILE ( NAME = '$dbLogLogicalName',   
                    #         FILENAME = '$intendedDbLogFile');  
                    #     GO
                    #     ALTER DATABASE AdventureWorks2014 SET ONLINE;  
                    #     GO"
                    # Invoke-Sqlcmd -Query $tSqlDetachDb

                    $confirmMessage="Set $dbName with these paths:`r`n$dbFile => $intendedDbFile`r`n$dbLogFile => $intendedDbLogFile"
                    $confirmed=if(!$confirmedAll){confirmation $confirmMessage}else{$true}
                    if($confirmed){
                        write-host "Detaching database $dbName..."
                        $timer=[System.Diagnostics.Stopwatch]::StartNew()
                        $startTime=get-date
                        $tSqlDetachDb="
                            Use MASTER
                            GO
                            ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                            GO
                            sp_detach_db @dbname = '$dbName', @skipchecks = 'true';
                            GO"
                        try{
                            Invoke-Sqlcmd -Query $tSqlDetachDb -EA Stop
                            if($moveDbStorage){
                                write-host "Moving $dbFile to $intendedDbFile..."
                                $null=move-item $dbFile $intendedDbFile
                            }
                            if($moveDbLogStorage){
                                write-host "Moving $dbLogFile to $intendedDbLogFile..."
                                $null=move-item $dbLogFile $intendedDbLogFile
                            }
                            $tSqlAttachDb="
                                Use MASTER
                                GO
                                sp_attach_DB '$dbName',
                                '$intendedDbFile',
                                '$intendedDbLogFile'
                                GO"
                            Invoke-Sqlcmd -Query $tSqlAttachDb
                            $elapsed=[math]::round($timer.Elapsed.TotalMinutes,2)
                            $endTime=get-date
                            $resultMessage="$dbName detached at $startTime and attached at $endTime - duration of $elapsed minutes."
                            write-host $resultMessage -foregroundcolor Green
                            $results[$dbName]="$endTime : $resultMessage"
                        }catch{
                            write-warning $_
                        }                          
                    }                    
                }else{
                    $results[$dbName]="$(get-date) : no changes"
                }
            }
        }
    }catch{
        Write-Warning $_
        }
    return $results
}

$results=moveDbStorage $databaseName $intendedDbLocation $intendedDbLogLocation $intendedDbBackupLocation
$results|write-host

Prior to making any changes to a SQL Server, one should gather information about the databases residing on such machines

-- Step 0: access the level of activities of all databases on SQL Server
WITH fs
AS
(
    select database_id, type, size * 8.0/1048576 size
    from sys.master_files
)
SELECT 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB  
FROM sys.databases db
ORDER BY LogFileSizeGB Desc

/*
name	        DataFileSizeGB	LogFileSizeGB
-------------   -------------   -------------
REPORTINGDB	    1.298828125	    1.713317871
DATABASE009	    235.080322265	1.407531738
DATABASE010	    6.718688964	    1.359436035
DATABASE999	    2.700012207	    0.869995117
*/

How To Manually Move Transaction Log File of a Single Database

-- Step 1: get database size of a single database
USE REPORTINGDB;
SELECT
    name,
    size,
    size * 8.0/1048576 'Size (GB)',
    max_size
FROM sys.database_files;

/* Sample output:
name	            size	Size (GB)	max_size
------------        ------  ---------   ---------
REPORTINGDBDat.mdf	155680	1.187744140	-1
REPORTINGDBLog.ldf	33592	0.256286621	268435456
*/

-- Step 2: get database and transaction log file locations
USE REPORTINGDB;
SELECT
  name 'Logical Name', 
  physical_name 'File Location'
FROM sys.database_files;

/* Sample Output
Logical Name        File Location
------------        ------------
REPORTINGDBDat.mdf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf
REPORTINGDBLog.ldf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf
*/

-- Step 3: set database to single user mode and detach database
-- WARNING: this will render database as OFFLINE !!!
Use MASTER
GO
ALTER DATABASE REPORTINGDB -- Set database to single user mode
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO 
sp_detach_db @dbname = 'REPORTINGDB', @skipchecks = 'true'; -- Detach the database
GO

-- Step 4: Move the files to new locations using PowerShell
-- move-item $dbFile $intendedDbFile
-- move-item $dbLogFile $intendedDbLogFile

-- Step 5: re-attach database with new log file location
USE master
GO
sp_attach_DB 'REPORTINGDB',  -- Re-attach the database
'D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf',
'E:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf'
GO

Microsoft SQL: Shrink vs Truncate

Shrink

The shrink command is to reduce the physical log file size of a database. That can be understood as de-fragmentation, file compaction, and resetting file physical pointers. There’s an inherent risk of data loss should this process fails.

Often databases that are setup with the simple recovery model have corresponding backup jobs that would run during non-business hours (e.g. 2AM) as this will automatically shrink log files. Hence, manual intervention may not be required for this design choice.

Other recovery models such as ‘FULL’ and ‘BULK-Logged’ will require special considerations. The former will store all transactions Data Definition Language (DDL) and Data Manipulation Language (DML) in transaction log files with a sequential order. These logs will not auto-truncate during CHECKPOINT operations. The latter, BULK-Logged is similar to FULL with the main difference in that certain bulk operations are minimally logged. Hence, specific point-in-time data cannot be instantaneously restored without retrieving certain log files in whole. Hence, shrink operations are often necessary for these types of recovery models.

Here’s a sample T-SQL to perform this task on a Full-Recovery-Model database:

Use [master]
Go
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (TestDb, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
Truncate

The truncate command changes the status of one or more virtual log files (VLF) from active to inactive, which marks those files for overwriting or reuse. The truncate command can also be substituted with ‘NO_LOG’, which will purge a database’s transaction log. This may be interpreted as actual deletion. It’s notable that sql backup jobs will also automatically truncate log files. However, such process will not delete files unless ‘NO_LOG’ is specified after the backup job.

Here’s a sample T-SQL to perform this task manually:

Use [master]
Go
DBCC SHRINKFILE(TestDb_Log, 1)
BACKUP LOG TestDb WITH TRUNCATE_ONLY -- NO_LOG (to delete log file)
DBCC SHRINKFILE(TestDb_Log, 1)
GO
DBA Comment:
  1. Adequate disk space provisioning for log files are necessary for the health and longevity of SQL Servers. It’s recommended that log files be placed on a separate volume from the database files for better performance and administration.
  2. Assuming Simple Recovery models, daily database backup duration should be estimated to account for disk space truncation as that would occur automatically.
  3. Assuming Full or Bulk_Logged Recovery models, the ALTER DATABASE … SET RECOVERY SIMPLE command as precursor to the SHRINK command is necessary to reduce disk usage on a periodic basis. This sequence should only be executed after each BACKUP operations.

Microsoft Dynamics 365: System Administrator Profile Corruption Problem

Symptoms:
  1. Field Security Profile Does Not List Enabled Custom Fields
  2. Certain forms would show a lock symbol with masked contents ‘*****’
  3. Field Security enable and disable options cannot be saved without throwing errors
  4. Data management imports fail with error code 0x80040217

Although this issue has been known to affect Microsoft Dynamics 365, the cloud version of Microsoft Products as well as ‘Microsoft Dynamics 365 Customer Engagement’ or on-premise version, our blog shall focus on the latter as the former has been patched by Microsoft since October 2020.

This may occur after Microsoft Dynamics has been upgraded to version 9.0 from earlier versions, certain forms would show a lock symbol with masked contents ‘*****’ when viewed via the GUI web portal. Further more, these are the steps to reproduce and observe other symptoms of this issue, after login to CRM as SystemAdmin:

  1. Settings > Customizations > Customize the system > Components > Entities > Lead > Fields > change View = Custom > double-click on a customized field (e.g. ‘crm_internalnotes’)

    A new window appears, Under the ‘General’ tab > any attempt to toggle to ‘disable’ or ‘enable’ the ‘Field Security’ will generate the ‘Field Security Profile Error’ code ‘-2147158771’. Sometimes, the radio button is even grayed-out so that no changes can be made to that field security
  2. Settings > Customizations > Customize the system > Components > Field Security Profiles > double click on a a field (e.g. ‘common’) > field permissions > [custom field name] would not appear on the list as expected
  3. Settings > Data management > Imports > click on any item > see error code 0x80040217 (this has happened since the move)

Error Message:

Field Security Profile Error
Activity ID: [ACTIVITYGUID]
Timestamp: Sun Nov 01 2020 05:14:05 (Pacific Standard Time)
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Caller [SYSTEMUSERID] does not have full permissions on the attribute '[FIELDNAME]' to unsecure the attributeDetail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ActivityId>[ACTIVITYGUID]</ActivityId>
  <ErrorCode>-2147158771</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>Caller [SYSTEMUSERID] does not have full permissions on the attribute [ACTIVITYGUID] to unsecure the attribute</Message>
  <Timestamp>2020-11-01</Timestamp>
  <ExceptionRetriable>false</ExceptionRetriable>
  <ExceptionSource i:nil="true" />
  <InnerFault i:nil="true" />
  <OriginalException i:nil="true" />
  <TraceText i:nil="true" />
</OrganizationServiceFault>
Resolution:
Backup database and prepare to restore:
  • Sample Backup T-SQL
    BACKUP DATABASE TEST_MSCRM
    TO DISK = '\\BackupServerUNCPath\TEST_MSCRM.bak' WITH COMPRESSION,COPY_ONLY
    GO
  • Sample Restore T-SQL
    USE master
    GO
    ALTER DATABASE TEST_MSCRM
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE [TEST_MSCRM] FROM DISK = N'\\BackupServerUNCPath\TEST_MSCRM.bak' WITH FILE = 1,
    MOVE N'mscrm' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.mdf',
    MOVE N'mscrm_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.ldf',
    NOUNLOAD, REPLACE, STATS = 5;
    ALTER DATABASE [TEST_MSCRM] SET MULTI_USER
    GO
Run this T-SQL:

Source: courtesy of my anonymous friend from Microsoft

USE TEST_MSCRM
GO
declare @testOnlyDoNotCommit int = 1; -- Change this value to 0 to apply the fix
DECLARE @SysAdminProfileId uniqueidentifier = '572329C1-A042-4E22-BE47-367C6374EA45'
DECLARE @SecuredAttributeMissingInSysAdminProfile TABLE(AttributeName nvarchar(100), ObjectTypeCode int, AttributeDependencyNodeId uniqueidentifier, CanBeSecuredForCreate bit, CanBeSecuredForRead bit, CanBeSecuredForUpdate bit)
DECLARE @NonSecuredAttributeInSysAdminProfile TABLE(FieldPermissionId uniqueidentifier, AttributeName nvarchar(100), ObjectTypeCode int)

-- Identify field permissions missing in System Administrator profile for secured attributes.
INSERT INTO @SecuredAttributeMissingInSysAdminProfile (AttributeName, ObjectTypeCode, AttributeDependencyNodeId, CanBeSecuredForCreate, CanBeSecuredForRead, CanBeSecuredForUpdate )
SELECT a.LogicalName, e.ObjectTypeCode, dn.DependencyNodeId, a.CanBeSecuredForCreate, a.CanBeSecuredForRead, a.CanBeSecuredForUpdate 
  FROM AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
    LEFT OUTER JOIN FieldPermission fp ON (fp.EntityName = e.ObjectTypeCode AND fp.AttributeLogicalName = a.LogicalName AND FieldSecurityProfileId = @SysAdminProfileId)
    LEFT JOIN DependencyNodeBase dn ON dn.ObjectId = a.AttributeId
 WHERE a.IsSecured = 1 AND fp.AttributeLogicalName IS NULL
 
-- Identify invalid field permissions defined in System Administrator profile for non-secured attributes.
INSERT INTO @NonSecuredAttributeInSysAdminProfile (FieldPermissionId, AttributeName, ObjectTypeCode)
SELECT fp.FieldPermissionId, fp.AttributeLogicalName, fp.EntityName
  FROM FieldPermission fp LEFT OUTER JOIN (
        SELECT a.LogicalName, e.ObjectTypeCode
          FROM  AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
         WHERE a.IsSecured = 1
    ) ae ON (fp.EntityName = ae.ObjectTypeCode AND fp.AttributeLogicalName = ae.LogicalName)
 WHERE FieldSecurityProfileId = @SysAdminProfileId AND ae.LogicalName IS NULL

IF EXISTS(SELECT 1 FROM @SecuredAttributeMissingInSysAdminProfile UNION SELECT 1 FROM @NonSecuredAttributeInSysAdminProfile)
 BEGIN
    SELECT FORMATMESSAGE('@testOnlyDoNotCommit = %d', @testOnlyDoNotCommit)
    -- Output missing permissions.  
    SELECT 'Field permissions missing in System Administrator profile for secured attributes.'
    SELECT * FROM @SecuredAttributeMissingInSysAdminProfile 
    -- Output invalid permissions.
    SELECT 'Invalid field permissions defined in System Administrator profile for non-secured attributes.'
    SELECT * FROM @NonSecuredAttributeInSysAdminProfile
    BEGIN TRAN
     BEGIN TRY    
        DECLARE @BasicSolutionId uniqueidentifier = '25A01723-9F63-4449-A3E0-046CC23A2902'
        DECLARE @RecreatedFieldPermission TABLE(FieldPermissionId uniqueidentifier, AttributeLogicalName nvarchar(50), EntityName int, DependencyNodeId uniqueidentifier)
        -- Create missing System Administrator field permissions. 
        INSERT INTO FieldPermissionBase (FieldPermissionIdUnique, 
                         ComponentState,
                         SolutionId,
                         CanRead,
                         SupportingSolutionId,
                         CanCreate,
                         FieldSecurityProfileId,
                         EntityName,
                         FieldPermissionId,
                         IsManaged,
                         OverwriteTime,
                         CanUpdate,
                         AttributeLogicalName)
        OUTPUT inserted.FieldPermissionId, inserted.AttributeLogicalName, inserted.EntityName, NEWID() INTO @RecreatedFieldPermission
        SELECT NEWID(), 
             0,
             @BasicSolutionId,
             CASE WHEN a.CanBeSecuredForRead = 0 THEN 0 ELSE 4 END,
             NULL,
             CASE WHEN a.CanBeSecuredForCreate = 0 THEN 0 ELSE 4 END,
             @SysAdminProfileId,
             a.ObjectTypeCode,
             NEWID(),
             1,
             0,
             CASE WHEN a.CanBeSecuredForUpdate = 0 THEN 0 ELSE 4 END,
             a.AttributeName
        FROM @SecuredAttributeMissingInSysAdminProfile a
        SELECT * FROM @RecreatedFieldPermission
        -- Create dependency. 
        DECLARE @SystemAdminProfileDependencyNodeId uniqueidentifier
        SELECT @SystemAdminProfileDependencyNodeId = DependencyNodeId FROM DependencyNodeBase WHERE ObjectId = @SysAdminProfileId
        INSERT INTO DependencyNodeBase (BaseSolutionId, ObjectId, TopSolutionId, ParentId, IsSharedComponent, DependencyNodeId, ComponentType)
        OUTPUT inserted.*
        SELECT @BasicSolutionId, rfp.FieldPermissionId, @BasicSolutionId, '00000000-0000-0000-0000-000000000000', 0, rfp.DependencyNodeId, 71 
        FROM @RecreatedFieldPermission rfp
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, a.AttributeDependencyNodeId
        FROM @RecreatedFieldPermission rfp JOIN @SecuredAttributeMissingInSysAdminProfile a ON rfp.EntityName = a.ObjectTypeCode AND rfp.AttributeLogicalName = a.AttributeName 
        WHERE a.AttributeDependencyNodeId IS NOT NULL
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, @SystemAdminProfileDependencyNodeId
        FROM @RecreatedFieldPermission rfp
        WHERE @SystemAdminProfileDependencyNodeId IS NOT NULL
        -- Delete invalid System Administrator field permissions.
        DELETE FROM FieldPermissionBase
        OUTPUT deleted.*
        WHERE FieldPermissionId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        -- Delete invalid dependencies.
        DELETE FROM DependencyBase
        OUTPUT deleted.*
        WHERE DependentComponentNodeId IN (
            SELECT DependencyNodeId FROM DependencyNodeBase WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        )
        DELETE FROM DependencyNodeBase
        OUTPUT deleted.*
        WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        IF (@testOnlyDoNotCommit = 1)
         BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
         END
        ELSE
         BEGIN
            -- Recalculate PrincipalAttributeAccessMap for System Administrator profile members.
            DECLARE @principalUserIds EntityIdCollection
            DECLARE @principalTeamIds EntityIdCollection
            INSERT INTO @principalUserIds SELECT SystemUserId FROM SystemUserProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            INSERT INTO @principalTeamIds SELECT TeamId from TeamProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            EXEC p_PrincipalAttributeAccessMapReinitBulk @principalUserIds, @principalTeamIds, 1
            COMMIT TRAN 
            SELECT 'TRANSACTION COMMITED'
         END
     END TRY
     BEGIN CATCH
        SELECT 'ERROR while executing script. Transaction rolled back. ' + ERROR_MESSAGE()
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
        END
     END CATCH 
 END
ELSE
 BEGIN
    SELECT 'System administrator profile is healthy. Did not find any corruption.'
 END

SQL: Microsoft Dynamics – Rebuilding a Database View Named ‘FilteredContact’

Why?

Update: currently, I’m unable to rebuild this complex view due to SQL constraints. Hence, this article is a story of a workaround – tracing the object (db table) where a certain view is based. Indexes can often be created on such tables to improve SQL performance.

Because this MS Dynamics db object cannot be indexed, leading to high CPU consumption during runtime. Here’s an example of an occasion when SQL Server has pegged CPU utilization at 100% for hours.

How?
  1. Read the ‘FilteredContact’ view build code to see that it’s based on the ‘Contacts’ view
  2. Read the ‘Contacts’ view to realize that it’s based on the ‘ContactBase’ table
  3. Add an index to ‘ContactBase’ table

Part 1: get the definitions views of ‘Filtered Contact’

--
-- Method 1: Obtaining definition (code) to create object
--
-- Object: 'FilteredContact' view
DECLARE @objectid int
DECLARE @code VARCHAR(MAX)
SET @objectid=(SELECT object_id FROM sys.objects
WHERE name = 'FilteredContact')
SET @code=(SELECT definition from sys.sql_modules
WHERE object_id = @objectid)

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@code) / 8000) + 1
WHILE @count < @maxcount
BEGIN
print(substring(@code, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

An attempt to alter an existing view

USE TEST_MSCRM
GO
--ALTER VIEW [dbo].[FilteredContact] WITH SCHEMABINDING
--AS
--SELECT ...;

Another attempt to recreate view

When trying to create an index on such view using a t-sql such as the one shown below:

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO

This error message occurs:

Msg 1939, Level 16, State 1, Line 4
Cannot create index on view 'FilteredContact' because the view is not schema bound.

Completion time: 2018-11-04T18:58:37.6112165-08:00

Red letters are added strings to the @code output from prior

USE [TEST_MSCRM]
GO
DROP VIEW IF EXISTS dbo.[FilteredContact];
GO

--
/*create view dbo.[FilteredContact] (
[accountid],
[accountiddsc],
[accountidname],
[accountidyominame],
...
) with view_metadata WITH SCHEMABINDING AS
select
[Contact].[AccountId],
--[Contact].[AccountIdDsc]
0,
[Contact].[AccountIdName],
[Contact].[AccountIdYomiName],
[Contact].[AccountRoleCode],
AccountRoleCodePLTable.Value,
[Contact].[Address1_AddressId],
[Contact].[Address1_AddressTypeCode],
Address1_AddressTypeCodePLTable.Value,
...
*/

The idea to recreate a table or view with the Schema Binding option so it can then be indexed to optimize runtime querying CPU utilization. However, some complex tsql has inner joins that runs into the constraints of ‘object referencing itself.’

Part 2: read ‘Contacts’ view

--
-- Method 2: Obtaining definition (code)
--
-- Object: 'Contact' view
DECLARE @sql VARCHAR(MAX);

SELECT @sql = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('Contact');

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@sql) / 8000) + 1
WHILE @count < @maxcount
BEGIN
print(substring(@sql, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

Error message to indicate ‘Contact’ view was based on ‘ContactBase’ table:

Msg 4512, Level 16, State 3, Procedure Contact, Line 437 [Batch Start Line 2]
Cannot schema bind view 'dbo.Contact' because name 'ContactBase' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Part 3: Creating a full-text index

It’s often better to run a SQL Profiler Trace and Database Engine Tuning Advisor to automate this process. Hence, the below instructions can be used for targeted database objects with much more effort than following the instructions provided in the above link.

Sample codes to create an index

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO
-- Create a nonclustered index on a table or view
CREATE INDEX ContactBase ON ContactBase ('EmployeeId');

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX ContactBase ON TEST_MSCRM.dbo.ContactBase ('EmployeeId');

Navigate to TEST_MSCRM > Tables > right-click ‘dbo.ContactBase’ > Full-Text Index > Define Full-Text Index

click Next to create an index basing on cndx_PrimaryKey_Contact

Select ‘EmployeeId’ > click Next > Next > Next > Next > Finish

click Finish

USE [TEST_MSCRM]
GO
--DROP VIEW IF EXISTS [dbo].[FilteredContact];
--GO
--
-- report view for contact
--
CREATE view [dbo].[FilteredContact](
    [accountid],
    [accountiddsc],
    [accountidname],
    [accountidyominame],
    [accountrolecode],
    [accountrolecodename],
    [address1_addressid],
    [address1_addresstypecode],
    [address1_addresstypecodename],
    [address1_city] -- truncated for brevity  
) WITH view_metadata AS --WITH SCHEMABINDING AS
select
    [Contact].[AccountId],
    -- Truncated for brevity
    [Contact].[YomiMiddleName],
   dbo.fn_GetNumberFormatString(t.CurrencyPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode),
   dbo.fn_GetNumberFormatString(o.PricingDecimalPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode)
from Contact
    join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
    left join OrganizationBase o on u.OrganizationId = o.OrganizationId
    left join TransactionCurrencyBase t on t.TransactionCurrencyId = [Contact].TransactionCurrencyId
    left outer join StringMap [AccountRoleCodePLTable] on 
		([AccountRoleCodePLTable].AttributeName = 'accountrolecode'
		and [AccountRoleCodePLTable].ObjectTypeCode = 2
		and [AccountRoleCodePLTable].AttributeValue = [Contact].[AccountRoleCode]
		and [AccountRoleCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_AddressTypeCodePLTable] on 
		([Address1_AddressTypeCodePLTable].AttributeName = 'address1_addresstypecode'
		and [Address1_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address1_AddressTypeCodePLTable].AttributeValue = [Contact].[Address1_AddressTypeCode]
		and [Address1_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_FreightTermsCodePLTable] on 
		([Address1_FreightTermsCodePLTable].AttributeName = 'address1_freighttermscode'
		and [Address1_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address1_FreightTermsCodePLTable].AttributeValue = [Contact].[Address1_FreightTermsCode]
		and [Address1_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_ShippingMethodCodePLTable] on 
		([Address1_ShippingMethodCodePLTable].AttributeName = 'address1_shippingmethodcode'
		and [Address1_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address1_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address1_ShippingMethodCode]
		and [Address1_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_AddressTypeCodePLTable] on 
		([Address2_AddressTypeCodePLTable].AttributeName = 'address2_addresstypecode'
		and [Address2_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address2_AddressTypeCodePLTable].AttributeValue = [Contact].[Address2_AddressTypeCode]
		and [Address2_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_FreightTermsCodePLTable] on 
		([Address2_FreightTermsCodePLTable].AttributeName = 'address2_freighttermscode'
		and [Address2_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address2_FreightTermsCodePLTable].AttributeValue = [Contact].[Address2_FreightTermsCode]
		and [Address2_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_ShippingMethodCodePLTable] on 
		([Address2_ShippingMethodCodePLTable].AttributeName = 'address2_shippingmethodcode'
		and [Address2_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address2_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address2_ShippingMethodCode]
		and [Address2_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_AddressTypeCodePLTable] on 
		([Address3_AddressTypeCodePLTable].AttributeName = 'address3_addresstypecode'
		and [Address3_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address3_AddressTypeCodePLTable].AttributeValue = [Contact].[Address3_AddressTypeCode]
		and [Address3_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_FreightTermsCodePLTable] on 
		([Address3_FreightTermsCodePLTable].AttributeName = 'address3_freighttermscode'
		and [Address3_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address3_FreightTermsCodePLTable].AttributeValue = [Contact].[Address3_FreightTermsCode]
		and [Address3_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_ShippingMethodCodePLTable] on 
		([Address3_ShippingMethodCodePLTable].AttributeName = 'address3_shippingmethodcode'
		and [Address3_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address3_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address3_ShippingMethodCode]
		and [Address3_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CreditOnHoldPLTable] on 
		([CreditOnHoldPLTable].AttributeName = 'creditonhold'
		and [CreditOnHoldPLTable].ObjectTypeCode = 2
		and [CreditOnHoldPLTable].AttributeValue = [Contact].[CreditOnHold]
		and [CreditOnHoldPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerSizeCodePLTable] on 
		([CustomerSizeCodePLTable].AttributeName = 'customersizecode'
		and [CustomerSizeCodePLTable].ObjectTypeCode = 2
		and [CustomerSizeCodePLTable].AttributeValue = [Contact].[CustomerSizeCode]
		and [CustomerSizeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerTypeCodePLTable] on 
		([CustomerTypeCodePLTable].AttributeName = 'customertypecode'
		and [CustomerTypeCodePLTable].ObjectTypeCode = 2
		and [CustomerTypeCodePLTable].AttributeValue = [Contact].[CustomerTypeCode]
		and [CustomerTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkEMailPLTable] on 
		([DoNotBulkEMailPLTable].AttributeName = 'donotbulkemail'
		and [DoNotBulkEMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkEMailPLTable].AttributeValue = [Contact].[DoNotBulkEMail]
		and [DoNotBulkEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkPostalMailPLTable] on 
		([DoNotBulkPostalMailPLTable].AttributeName = 'donotbulkpostalmail'
		and [DoNotBulkPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkPostalMailPLTable].AttributeValue = [Contact].[DoNotBulkPostalMail]
		and [DoNotBulkPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotEMailPLTable] on 
		([DoNotEMailPLTable].AttributeName = 'donotemail'
		and [DoNotEMailPLTable].ObjectTypeCode = 2
		and [DoNotEMailPLTable].AttributeValue = [Contact].[DoNotEMail]
		and [DoNotEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotFaxPLTable] on 
		([DoNotFaxPLTable].AttributeName = 'donotfax'
		and [DoNotFaxPLTable].ObjectTypeCode = 2
		and [DoNotFaxPLTable].AttributeValue = [Contact].[DoNotFax]
		and [DoNotFaxPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPhonePLTable] on 
		([DoNotPhonePLTable].AttributeName = 'donotphone'
		and [DoNotPhonePLTable].ObjectTypeCode = 2
		and [DoNotPhonePLTable].AttributeValue = [Contact].[DoNotPhone]
		and [DoNotPhonePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPostalMailPLTable] on 
		([DoNotPostalMailPLTable].AttributeName = 'donotpostalmail'
		and [DoNotPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotPostalMailPLTable].AttributeValue = [Contact].[DoNotPostalMail]
		and [DoNotPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotSendMMPLTable] on 
		([DoNotSendMMPLTable].AttributeName = 'donotsendmm'
		and [DoNotSendMMPLTable].ObjectTypeCode = 2
		and [DoNotSendMMPLTable].AttributeValue = [Contact].[DoNotSendMM]
		and [DoNotSendMMPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [EducationCodePLTable] on 
		([EducationCodePLTable].AttributeName = 'educationcode'
		and [EducationCodePLTable].ObjectTypeCode = 2
		and [EducationCodePLTable].AttributeValue = [Contact].[EducationCode]
		and [EducationCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FamilyStatusCodePLTable] on 
		([FamilyStatusCodePLTable].AttributeName = 'familystatuscode'
		and [FamilyStatusCodePLTable].ObjectTypeCode = 2
		and [FamilyStatusCodePLTable].AttributeValue = [Contact].[FamilyStatusCode]
		and [FamilyStatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FollowEmailPLTable] on 
		([FollowEmailPLTable].AttributeName = 'followemail'
		and [FollowEmailPLTable].ObjectTypeCode = 2
		and [FollowEmailPLTable].AttributeValue = [Contact].[FollowEmail]
		and [FollowEmailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [GenderCodePLTable] on 
		([GenderCodePLTable].AttributeName = 'gendercode'
		and [GenderCodePLTable].ObjectTypeCode = 2
		and [GenderCodePLTable].AttributeValue = [Contact].[GenderCode]
		and [GenderCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [HasChildrenCodePLTable] on 
		([HasChildrenCodePLTable].AttributeName = 'haschildrencode'
		and [HasChildrenCodePLTable].ObjectTypeCode = 2
		and [HasChildrenCodePLTable].AttributeValue = [Contact].[HasChildrenCode]
		and [HasChildrenCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsBackofficeCustomerPLTable] on 
		([IsBackofficeCustomerPLTable].AttributeName = 'isbackofficecustomer'
		and [IsBackofficeCustomerPLTable].ObjectTypeCode = 2
		and [IsBackofficeCustomerPLTable].AttributeValue = [Contact].[IsBackofficeCustomer]
		and [IsBackofficeCustomerPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsPrivatePLTable] on 
		([IsPrivatePLTable].AttributeName = 'isprivate'
		and [IsPrivatePLTable].ObjectTypeCode = 2
		and [IsPrivatePLTable].AttributeValue = [Contact].[IsPrivate]
		and [IsPrivatePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [LeadSourceCodePLTable] on 
		([LeadSourceCodePLTable].AttributeName = 'leadsourcecode'
		and [LeadSourceCodePLTable].ObjectTypeCode = 2
		and [LeadSourceCodePLTable].AttributeValue = [Contact].[LeadSourceCode]
		and [LeadSourceCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MarketingOnlyPLTable] on 
		([MarketingOnlyPLTable].AttributeName = 'marketingonly'
		and [MarketingOnlyPLTable].ObjectTypeCode = 2
		and [MarketingOnlyPLTable].AttributeValue = [Contact].[MarketingOnly]
		and [MarketingOnlyPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MergedPLTable] on 
		([MergedPLTable].AttributeName = 'merged'
		and [MergedPLTable].ObjectTypeCode = 2
		and [MergedPLTable].AttributeValue = [Contact].[Merged]
		and [MergedPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [msdyn_gdproptoutPLTable] on 
		([msdyn_gdproptoutPLTable].AttributeName = 'msdyn_gdproptout'
		and [msdyn_gdproptoutPLTable].ObjectTypeCode = 2
		and [msdyn_gdproptoutPLTable].AttributeValue = [Contact].[msdyn_gdproptout]
		and [msdyn_gdproptoutPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ParticipatesInWorkflowPLTable] on 
		([ParticipatesInWorkflowPLTable].AttributeName = 'participatesinworkflow'
		and [ParticipatesInWorkflowPLTable].ObjectTypeCode = 2
		and [ParticipatesInWorkflowPLTable].AttributeValue = [Contact].[ParticipatesInWorkflow]
		and [ParticipatesInWorkflowPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PaymentTermsCodePLTable] on 
		([PaymentTermsCodePLTable].AttributeName = 'paymenttermscode'
		and [PaymentTermsCodePLTable].ObjectTypeCode = 2
		and [PaymentTermsCodePLTable].AttributeValue = [Contact].[PaymentTermsCode]
		and [PaymentTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentDayCodePLTable] on 
		([PreferredAppointmentDayCodePLTable].AttributeName = 'preferredappointmentdaycode'
		and [PreferredAppointmentDayCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentDayCodePLTable].AttributeValue = [Contact].[PreferredAppointmentDayCode]
		and [PreferredAppointmentDayCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentTimeCodePLTable] on 
		([PreferredAppointmentTimeCodePLTable].AttributeName = 'preferredappointmenttimecode'
		and [PreferredAppointmentTimeCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentTimeCodePLTable].AttributeValue = [Contact].[PreferredAppointmentTimeCode]
		and [PreferredAppointmentTimeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredContactMethodCodePLTable] on 
		([PreferredContactMethodCodePLTable].AttributeName = 'preferredcontactmethodcode'
		and [PreferredContactMethodCodePLTable].ObjectTypeCode = 2
		and [PreferredContactMethodCodePLTable].AttributeValue = [Contact].[PreferredContactMethodCode]
		and [PreferredContactMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ShippingMethodCodePLTable] on 
		([ShippingMethodCodePLTable].AttributeName = 'shippingmethodcode'
		and [ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [ShippingMethodCodePLTable].AttributeValue = [Contact].[ShippingMethodCode]
		and [ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StateCodePLTable] on 
		([StateCodePLTable].AttributeName = 'statecode'
		and [StateCodePLTable].ObjectTypeCode = 2
		and [StateCodePLTable].AttributeValue = [Contact].[StateCode]
		and [StateCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StatusCodePLTable] on 
		([StatusCodePLTable].AttributeName = 'statuscode'
		and [StatusCodePLTable].ObjectTypeCode = 2
		and [StatusCodePLTable].AttributeValue = [Contact].[StatusCode]
		and [StatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [TerritoryCodePLTable] on 
		([TerritoryCodePLTable].AttributeName = 'territorycode'
		and [TerritoryCodePLTable].ObjectTypeCode = 2
		and [TerritoryCodePLTable].AttributeValue = [Contact].[TerritoryCode]
		and [TerritoryCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    cross join dbo.fn_GetMaxPrivilegeDepthMask(2) pdm
where
(
	-- privilege check
	pdm.PrivilegeDepthMask is not null and
	(
	-- Owner check
	-- If the user has global access, then skip the ownership check
	((pdm.PrivilegeDepthMask & 0x8) != 0) or
	[Contact].OwnerId in 
		( -- returns only principals with Basic Read privilege for entity
			select pem.PrincipalId from PrincipalEntityMap pem WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId 
			where sup.SystemUserId = u.SystemUserId 
				and pem.ObjectTypeCode = 2
		)
		
	-- role based access
	or 
	
exists
(
	select 
	1
	where
	(
		-- deep/local security
		(((pdm.PrivilegeDepthMask & 0x4) != 0) or ((pdm.PrivilegeDepthMask & 0x2) != 0)) and 
		[Contact].[OwningBusinessUnit] in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = u.SystemUserId and ObjectTypeCode = 2)
	) 
	or
	(
		-- global security
		((pdm.PrivilegeDepthMask & 0x8) != 0) and 
		[Contact].[OwningBusinessUnit] is not null 
	) 
)

	
	-- object shared to the user 
	or 
	[Contact].[ContactId] in 
		(
			select POA.ObjectId from PrincipalObjectAccess POA WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId
			where sup.SystemUserId = u.SystemUserId
				and POA.ObjectTypeCode = 2
				and ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
		)
	)
)

Sample code for ‘Contact’ view:
USE TEST_MSCRM
GO
--
-- base view for Contact
--
create view dbo.[Contact] (
    -- logical attributes
    [CreatedByYomiName],
    [CreatedByName],
    [OriginatingLeadIdYomiName],
    [OriginatingLeadIdName],
    [ModifiedByExternalPartyYomiName],
    [ModifiedByExternalPartyName],
    [CreatedByExternalPartyYomiName],
    [CreatedByExternalPartyName],
    [new_RetainedByYomiName],
    [new_RetainedByName],
    [DefaultPriceLevelIdName],
    [SLAName],
    [PreferredSystemUserIdName],
    [PreferredSystemUserIdYomiName],
    [TransactionCurrencyIdName],
    [PreferredServiceIdName],
    [SLAInvokedIdName],

    -- linked address entities
    [Address1_AddressTypeCode],
    [Address1_City],
    [Address1_Composite],
    [Address1_Country],
    [Address1_County],
    [Address1_AddressId],
    [Address1_Fax],
    [Address1_FreightTermsCode],
    [Address1_Latitude],
    [Address1_Line1],
    [Address1_Line2],
    [Address1_Line3],
    [Address1_Longitude],
    [Address1_Name],
    [Address1_PostalCode],
    [Address1_PostOfficeBox],
    [Address1_PrimaryContactName],
    [Address1_ShippingMethodCode],
    [Address1_StateOrProvince],
    [Address1_Telephone1],
    [Address1_Telephone2],
    [Address1_Telephone3],
    [Address1_UPSZone],
    [Address1_UTCOffset],

    [Address2_AddressTypeCode],
    [Address2_City],
    [Address2_Composite],
    [Address2_Country],
    [Address2_County],
    [Address2_AddressId],
    [Address2_Fax],
    [Address2_FreightTermsCode],
    [Address2_Latitude],
    [Address2_Line1],
    [Address2_Line2],
    [Address2_Line3],
    [Address2_Longitude],
    [Address2_Name],
    [Address2_PostalCode],
    [Address2_PostOfficeBox],
    [Address2_PrimaryContactName],
    [Address2_ShippingMethodCode],
    [Address2_StateOrProvince],
    [Address2_Telephone1],
    [Address2_Telephone2],
    [Address2_Telephone3],
    [Address2_UPSZone],
    [Address2_UTCOffset],

    [Address3_AddressTypeCode],
    [Address3_City],
    [Address3_Composite],
    [Address3_Country],
    [Address3_County],
    [Address3_AddressId],
    [Address3_Fax],
    [Address3_FreightTermsCode],
    [Address3_Latitude],
    [Address3_Line1],
    [Address3_Line2],
    [Address3_Line3],
    [Address3_Longitude],
    [Address3_Name],
    [Address3_PostalCode],
    [Address3_PostOfficeBox],
    [Address3_PrimaryContactName],
    [Address3_ShippingMethodCode],
    [Address3_StateOrProvince],
    [Address3_Telephone1],
    [Address3_Telephone2],
    [Address3_Telephone3],
    [Address3_UPSZone],
    [Address3_UTCOffset],

    -- ownership entries
    OwnerId,
    OwnerIdName,
    OwnerIdYomiName,
    OwnerIdDsc,
    OwnerIdType,
    OwningUser,
    OwningTeam,


	[AccountId],
	[AccountIdName],
	[AccountIdYomiName],
	[ParentContactId],
	[ParentContactIdName],
	[ParentContactIdYomiName],
    -- physical attributes
    [ContactId],
    [DefaultPriceLevelId],
    [CustomerSizeCode],
    [CustomerTypeCode],
    [PreferredContactMethodCode],
    [LeadSourceCode],
    [OriginatingLeadId],
    [OwningBusinessUnit],
    [PaymentTermsCode],
    [ShippingMethodCode],
    [ParticipatesInWorkflow],
    [IsBackofficeCustomer],
    [Salutation],
    [JobTitle],
    [FirstName],
    [Department],
    [NickName],
    [MiddleName],
    [LastName],
    [Suffix],
    [YomiFirstName],
    [FullName],
    [YomiMiddleName],
    [YomiLastName],
    [Anniversary],
    [BirthDate],
    [GovernmentId],
    [YomiFullName],
    [Description],
    [EmployeeId],
    [GenderCode],
    [AnnualIncome],
    [HasChildrenCode],
    [EducationCode],
    [WebSiteUrl],
    [FamilyStatusCode],
    [FtpSiteUrl],
    [EMailAddress1],
    [SpousesName],
    [AssistantName],
    [EMailAddress2],
    [AssistantPhone],
    [EMailAddress3],
    [DoNotPhone],
    [ManagerName],
    [ManagerPhone],
    [DoNotFax],
    [DoNotEMail],
    [DoNotPostalMail],
    [DoNotBulkEMail],
    [DoNotBulkPostalMail],
    [AccountRoleCode],
    [TerritoryCode],
    [IsPrivate],
    [CreditLimit],
    [CreatedOn],
    [CreditOnHold],
    [CreatedBy],
    [ModifiedOn],
    [ModifiedBy],
    [NumberOfChildren],
    [ChildrensNames],
    [VersionNumber],
    [MobilePhone],
    [Pager],
    [Telephone1],
    [Telephone2],
    [Telephone3],
    [Fax],
    [Aging30],
    [StateCode],
    [Aging60],
    [StatusCode],
    [Aging90],
    [PreferredSystemUserId],
    [PreferredServiceId],
    [MasterId],
    [PreferredAppointmentDayCode],
    [PreferredAppointmentTimeCode],
    [DoNotSendMM],
    [ParentCustomerId],
    [Merged],
    [ExternalUserIdentifier],
    [SubscriptionId],
    [PreferredEquipmentId],
    [LastUsedInCampaign],
    [ParentCustomerIdName],
    [ParentCustomerIdType],
    [TransactionCurrencyId],
    [OverriddenCreatedOn],
    [ExchangeRate],
    [ImportSequenceNumber],
    [TimeZoneRuleVersionNumber],
    [UTCConversionTimeZoneCode],
    [AnnualIncome_Base],
    [CreditLimit_Base],
    [Aging60_Base],
    [Aging90_Base],
    [Aging30_Base],
    [ParentCustomerIdYomiName],
    [CreatedOnBehalfBy],
    [ModifiedOnBehalfBy],
    [IsAutoCreate],
    [StageId],
    [ProcessId],
    [EntityImageId],
    [TraversedPath],
    [SLAId],
    [SLAInvokedId],
    [OnHoldTime],
    [LastOnHoldTime],
    [FollowEmail],
    [TimeSpentByMeOnEmailAndMeetings],
    [Business2],
    [Callback],
    [Company],
    [Home2],
    [CreatedByExternalParty],
    [ModifiedByExternalParty],
    [MarketingOnly],    
    [msdyn_gdproptout]
) with view_metadata as
select
    -- logical attributes
    [lk_contactbase_createdby].[YomiFullName],
    [lk_contactbase_createdby].[FullName],
    [contact_originating_lead].[YomiFullName],
    [contact_originating_lead].[FullName],    
    [sla_contact].[Name],

    -- linked address entities
    [XXaddress1].[AddressTypeCode],
    [XXaddress1].[City],
    [XXaddress1].[Composite],
    [XXaddress1].[Country],
    [XXaddress1].[County],
    [XXaddress1].[CustomerAddressId],
    [XXaddress1].[Fax],
    [XXaddress1].[FreightTermsCode],
    [XXaddress1].[Latitude],
    [XXaddress1].[Line1],
    [XXaddress1].[Line2],
    [XXaddress1].[Line3],
    [XXaddress1].[Longitude],
    [XXaddress1].[Name],
    [XXaddress1].[PostalCode],
    [XXaddress1].[PostOfficeBox],
    [XXaddress1].[PrimaryContactName],
    [XXaddress1].[ShippingMethodCode],
    [XXaddress1].[StateOrProvince],
    [XXaddress1].[Telephone1],
    [XXaddress1].[Telephone2],
    [XXaddress1].[Telephone3],
    [XXaddress1].[UPSZone],
    [XXaddress1].[UTCOffset],
    [XXaddress2].[AddressTypeCode],
    [XXaddress2].[City],
    [XXaddress2].[Composite],
    [XXaddress2].[Country],
    [XXaddress2].[County],
    [XXaddress2].[CustomerAddressId],
    [XXaddress2].[Fax],
    [XXaddress2].[FreightTermsCode],
    [XXaddress2].[Latitude],
    [XXaddress2].[Line1],
    [XXaddress2].[Line2],
    [XXaddress2].[Line3],
    [XXaddress2].[Longitude],
    [XXaddress2].[Name],
    [XXaddress2].[PostalCode],
    [XXaddress2].[PostOfficeBox],
    [XXaddress2].[PrimaryContactName],
    [XXaddress2].[ShippingMethodCode],
    [XXaddress2].[StateOrProvince],
    [XXaddress2].[Telephone1],
    [XXaddress2].[Telephone2],
    [XXaddress2].[Telephone3],
    [XXaddress2].[UPSZone],
    [XXaddress2].[UTCOffset],
    [XXaddress3].[AddressTypeCode],
    [XXaddress3].[City],
    [XXaddress3].[Composite],
    [XXaddress3].[Country],
    [XXaddress3].[County],
    [XXaddress3].[CustomerAddressId],
    [XXaddress3].[Fax],
    [XXaddress3].[FreightTermsCode],
    [XXaddress3].[Latitude],
    [XXaddress3].[Line1],
    [XXaddress3].[Line2],
    [XXaddress3].[Line3],
    [XXaddress3].[Longitude],
    [XXaddress3].[Name],
    [XXaddress3].[PostalCode],
    [XXaddress3].[PostOfficeBox],
    [XXaddress3].[PrimaryContactName],
    [XXaddress3].[ShippingMethodCode],
    [XXaddress3].[StateOrProvince],
    [XXaddress3].[Telephone1],
    [XXaddress3].[Telephone2],
    [XXaddress3].[Telephone3],
    [XXaddress3].[UPSZone],
    [XXaddress3].[UTCOffset],
    -- ownership entries
    OwnerId = [ContactBase].OwnerId,
    OwnerName = XXowner.Name,
    OwnerYomiName =  XXowner.YomiName,
    OwnerDsc = 0, -- DSC is removed, stub it to 0
    OwnerIdType = XXowner.OwnerIdType,
    OwningUser = case 
 		when XXowner.OwnerIdType= 8 then XXowner.OwnerId
		else null
		end,
    OwningTeam = case 
 		when XXowner.OwnerIdType= 9 then XXowner.OwnerId
		else null
		end,


	[AccountId] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[AccountIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[AccountIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
	[ParentContactId] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[ParentContactIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[ParentContactIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
    -- physical attribute
    [ContactBase].[ContactId],
    [ContactBase].[DefaultPriceLevelId],
    [ContactBase].[CustomerSizeCode],
    [ContactBase].[CustomerTypeCode],
    [ContactBase].[PreferredContactMethodCode],
    [ContactBase].[LeadSourceCode],
    [ContactBase].[OriginatingLeadId],
    [ContactBase].[OwningBusinessUnit],
    [ContactBase].[PaymentTermsCode],
    [ContactBase].[ShippingMethodCode],
    [ContactBase].[ParticipatesInWorkflow],
    [ContactBase].[IsBackofficeCustomer],
    [ContactBase].[Salutation],
    [ContactBase].[JobTitle],
    [ContactBase].[FirstName],
    [ContactBase].[Department],
    [ContactBase].[NickName],
    [ContactBase].[MiddleName],
    [ContactBase].[LastName],
    [ContactBase].[Suffix],
    [ContactBase].[YomiFirstName],
    [ContactBase].[FullName],
    [ContactBase].[YomiMiddleName],
    [ContactBase].[YomiLastName],
    [ContactBase].[Anniversary],
    [ContactBase].[BirthDate],
    [ContactBase].[GovernmentId],
    [ContactBase].[YomiFullName],
    [ContactBase].[Description],
    [ContactBase].[EmployeeId],
    [ContactBase].[GenderCode],
    [ContactBase].[AnnualIncome],
    [ContactBase].[HasChildrenCode],
    [ContactBase].[EducationCode],
    [ContactBase].[WebSiteUrl],
    [ContactBase].[FamilyStatusCode],
    [ContactBase].[FtpSiteUrl],
    [ContactBase].[EMailAddress1],
    [ContactBase].[SpousesName],
    [ContactBase].[AssistantName],
    [ContactBase].[EMailAddress2],
    [ContactBase].[AssistantPhone],
    [ContactBase].[EMailAddress3],
    [ContactBase].[DoNotPhone],
    [ContactBase].[ManagerName],
    [ContactBase].[ManagerPhone],
    [ContactBase].[DoNotFax],
    [ContactBase].[DoNotEMail],
    [ContactBase].[DoNotPostalMail],
    [ContactBase].[DoNotBulkEMail],
    [ContactBase].[DoNotBulkPostalMail],
    [ContactBase].[AccountRoleCode],
    [ContactBase].[TerritoryCode],
    [ContactBase].[IsPrivate],
    [ContactBase].[CreditLimit],
    [ContactBase].[CreatedOn],
    [ContactBase].[CreditOnHold],
    [ContactBase].[CreatedBy],
    [ContactBase].[ModifiedOn],
    [ContactBase].[ModifiedBy],
    [ContactBase].[NumberOfChildren],
    [ContactBase].[ChildrensNames],
    [ContactBase].[VersionNumber],
    [ContactBase].[MobilePhone],
    [ContactBase].[Pager],
    [ContactBase].[Telephone1],
    [ContactBase].[Telephone2],
    [ContactBase].[Telephone3],
    [ContactBase].[Fax],
    [ContactBase].[Aging30],
    [ContactBase].[StateCode],
    [ContactBase].[Aging60],
    [ContactBase].[StatusCode],
    [ContactBase].[Aging90],
    [ContactBase].[PreferredSystemUserId],
    [ContactBase].[PreferredServiceId],
    [ContactBase].[MasterId],
    [ContactBase].[PreferredAppointmentDayCode],
    [ContactBase].[PreferredAppointmentTimeCode],
    [ContactBase].[DoNotSendMM],
    [ContactBase].[ParentCustomerId],
    [ContactBase].[Merged],
    [ContactBase].[ExternalUserIdentifier],
    [ContactBase].[SubscriptionId],
    [ContactBase].[PreferredEquipmentId],
    [ContactBase].[LastUsedInCampaign],
    [ContactBase].[ParentCustomerIdName],
    [ContactBase].[ParentCustomerIdType],
    [ContactBase].[TransactionCurrencyId],
    [ContactBase].[OverriddenCreatedOn],
    [ContactBase].[ExchangeRate],
    [ContactBase].[ImportSequenceNumber],
    [ContactBase].[TimeZoneRuleVersionNumber],
    [ContactBase].[UTCConversionTimeZoneCode],
    [ContactBase].[AnnualIncome_Base],
    [ContactBase].[CreditLimit_Base],
    [ContactBase].[Aging60_Base],
    [ContactBase].[Aging90_Base],
    [ContactBase].[Aging30_Base],
    [ContactBase].[ParentCustomerIdYomiName],
    [ContactBase].[CreatedOnBehalfBy],
    [ContactBase].[ModifiedOnBehalfBy],
    [ContactBase].[IsAutoCreate],
    [ContactBase].[StageId],
    [ContactBase].[ProcessId],
    [ContactBase].[EntityImageId],
    [ContactBase].[TraversedPath],
    [ContactBase].[SLAId],
    [ContactBase].[SLAInvokedId],
    [ContactBase].[OnHoldTime],
    [ContactBase].[LastOnHoldTime],
    [ContactBase].[FollowEmail],
    [ContactBase].[TimeSpentByMeOnEmailAndMeetings],
    [ContactBase].[Business2],
    [ContactBase].[Callback],
    [ContactBase].[Company],
    [ContactBase].[Home2],
    [ContactBase].[CreatedByExternalParty],
    [ContactBase].[ModifiedByExternalParty],
    [ContactBase].[MarketingOnly],    
    [ContactBase].[msdyn_gdproptout]
from [ContactBase] 
    left join [CustomerAddressBase] XXaddress1 on ([ContactBase].[ContactId] = XXaddress1.ParentId and XXaddress1.AddressNumber = 1)
    left join [CustomerAddressBase] XXaddress2 on ([ContactBase].[ContactId] = XXaddress2.ParentId and XXaddress2.AddressNumber = 2)
    left join [CustomerAddressBase] XXaddress3 on ([ContactBase].[ContactId] = XXaddress3.ParentId and XXaddress3.AddressNumber = 3)
    left join [ContactBase] [contact_master_contact] on ([ContactBase].[MasterId] = [contact_master_contact].[ContactId])
    left join [LeadBase] [contact_originating_lead] on ([ContactBase].[OriginatingLeadId] = [contact_originating_lead].[LeadId])
    left join [EquipmentBase] [equipment_contacts] on ([ContactBase].[PreferredEquipmentId] = [equipment_contacts].[EquipmentId])    
    left join [ImageDescriptor] [lk_contact_entityimage] on ([ContactBase].[EntityImageId] = [lk_contact_entityimage].[ImageDescriptorId])
    left join [SystemUserBase] [lk_contactbase_createdby] with(nolock) on ([ContactBase].[CreatedBy] = [lk_contactbase_createdby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_createdonbehalfby] with(nolock) on ([ContactBase].[CreatedOnBehalfBy] = [lk_contactbase_createdonbehalfby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedby] with(nolock) on ([ContactBase].[ModifiedBy] = [lk_contactbase_modifiedby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedonbehalfby] with(nolock) on ([ContactBase].[ModifiedOnBehalfBy] = [lk_contactbase_modifiedonbehalfby].[SystemUserId])
    left join [ExternalPartyBase] [lk_externalparty_contact_createdby] on ([ContactBase].[CreatedByExternalParty] = [lk_externalparty_contact_createdby].[ExternalPartyId])
    left join [ExternalPartyBase] [lk_externalparty_contact_modifiedby] on ([ContactBase].[ModifiedByExternalParty] = [lk_externalparty_contact_modifiedby].[ExternalPartyId])
    left join [SLABase] [manualsla_contact] on ([ContactBase].[SLAId] = [manualsla_contact].[SLAId] and [manualsla_contact].OverwriteTime = 0 and [manualsla_contact].ComponentState = 0)
    left join [SystemUserBase] [new_systemuser_contact_RetainedBy] with(nolock) on ([ContactBase].[new_RetainedBy] = [new_systemuser_contact_RetainedBy].[SystemUserId])    
    left join [SLABase] [sla_contact] on ([ContactBase].[SLAInvokedId] = [sla_contact].[SLAId] and [sla_contact].OverwriteTime = 0 and [sla_contact].ComponentState = 0)
    left join [SystemUserBase] [system_user_contacts] with(nolock) on ([ContactBase].[PreferredSystemUserId] = [system_user_contacts].[SystemUserId])
    left join [TransactionCurrencyBase] [transactioncurrency_contact] on ([ContactBase].[TransactionCurrencyId] = [transactioncurrency_contact].[TransactionCurrencyId])
    left join OwnerBase XXowner with(nolock) on ([ContactBase].OwnerId = XXowner.OwnerId)

SQL Server: Use Profiler to Tune Database Indexes

Run Microsoft SQL Server Management Studio > Tools > SQL Server Profiler > Use the template = “Tuning” > click Run

Let the tracer run for awhile > click Stop when ready

Click File > Save as > Trace File > place the trace file onto the Desktop directory

 Click on Tools > ‘Database tuning advisor’ > Login as Sysadmin > click on the binocular icon within the Workload group to select the previously captured file

Open the trace file > select the database(s) to optimize > ‘Start Analysis’

If there were errors during analysis processing, increase the tuning options space; otherwise, review the Tuning session result > the ‘Recommendations’ tab should be displayed > wait for the analysis engine to complete > select All Recommendations > Actions > Save Recommendations > Name the script TuningRecommendations.sql > Click on Actions > Evaluate Recommendations

Input the name of new session as ‘Tuning Evaluation’ > click on Binocular Icon to locate the saved TuningRecommendations.sql file > set Database for workload analysis = master > click Start Analysis

Review the TuningRecommendations.sql file with Application Specialist(s) and/or DBA(s) prior to applying recommendations > make edits as necessary

Although the probability of having these metadata interfering with data is low, is worth noting that many of the recommended indexes and statistic objects need to be validated DEV/QA/STAGE prior to applying to PROD. These are the two types of objects that the Database Tuning Advisor (DTA) would recommend by default:

  1. Indexes: once created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. Occasionally, DBAs would run this T-SQL, sys.dm_db_index_physical_stats (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15) to detect fragmentation. Reorganize and rebuild are the common user actions taken to maintain indexes. Here are some a quick rules of thumb regarding indexes:
    • avg_fragmentation_in_percent between 10% to 30% > REORGANIZE
    • avg_fragmentation_in_percent between 10% to 30% > REBUILD
  2. Statistics: this object type does not get updated automatically. Thus, DBAs should contemplate their effects on existing scheduled maintenance jobs (e.g. additional sequences to update statistics should be considered to keep these objects up-to-date)

When ready to apply DTA Recommendations in PROD, access the DTA GUI > Load the approved TuningRecommendations.sql file > put a check mark next to each desired recommendation > Actions > ‘Apply Recommendations’ > select ‘Apply Now’ > press OK when done

Another screenshot of a recommendation for ‘80% improvement’

Click ‘Close’ when done

A screenshot of a successful SQL tune-up

How To Reverse Changes:

It’s likely that only indexes and statistics would be recommended by the Database Tuning Advisor (DTA) wizard. Those objects would often have a prefix of ‘_dta_‘. Other times, custom indexes would follow the convention of ‘_tuning_index_‘, or the like. Therefore, it is relatively easy to locate and drop those objects to revert changes.

A) Scripted Process:

Copy the TuningRecommendations.sql file into a new file and edit it to drop named indexes and statistics similar to this sample before invoking T-SQL:

use [TESTDB001]
go

DROP INDEX _dta_index_TestTable_10_1954106002__K1_12_36_83_84
ON [dbo].[TestTable];
DROP STATISTICS _dta_index_TestTable_10_1954106002__S1_12_36_83_84
ON [dbo].[TestTable];

go

use [TESTDB100]
go

DROP INDEX [_dta_index_PM00300_c_7_944722418__K1_K2] ON [dbo].[PM00300]

-- truncated for brevity --

B) Manual Process:

Step 1: Find indexes and statistics with a preceding ‘_dta_‘ or ‘_tuning_index_‘ in their names

-- View indexes of a table
USE TEST_MSCRM
GO

EXEC sp_helpindex '[dbo].[TestTable]'
GO

/* Sample output:
_dta_index_TestTable_10_1954106002__K1_12_36_83_84 nonclustered located on PRIMARY SystemUserId
_dta_index_TestTable_10_1954106002__K1_12_53 nonclustered located on PRIMARY SystemUserId
cndx_PrimaryKey_SystemUser clustered, unique, primary key located on PRIMARY SystemUserId
fndx_for_cascaderelationship_calendar_system_users nonclustered located on PRIMARY CalendarId
fndx_for_cascaderelationship_site_system_users nonclustered located on PRIMARY SiteId
fndx_for_cascaderelationship_territory_system_users nonclustered located on PRIMARY TerritoryId
fndx_for_cascaderelationship_user_parent_user nonclustered located on PRIMARY ParentSystemUserId
fndx_Sync_VersionNumber nonclustered, unique located on PRIMARY VersionNumber
ndx_application_user nonclustered, unique located on PRIMARY ApplicationId
ndx_azure_active_directoryobjectid nonclustered, unique located on PRIMARY AzureActiveDirectoryObjectId
ndx_Cover nonclustered located on PRIMARY FullName, YomiFullName
ndx_firstname nonclustered located on PRIMARY FirstName
ndx_for_cascaderelationship_position_users nonclustered located on PRIMARY PositionId
ndx_for_cascaderelationship_queue_system_user nonclustered located on PRIMARY QueueId
ndx_for_cascaderelationship_systemuser_defaultmailbox_mailbox nonclustered located on PRIMARY DefaultMailbox
ndx_for_cascaderelationship_TransactionCurrency_SystemUser nonclustered located on PRIMARY TransactionCurrencyId
ndx_IdentityId nonclustered, unique located on PRIMARY IdentityId
ndx_internalemailaddress nonclustered located on PRIMARY InternalEMailAddress
ndx_isdisabledaccessmodetitle nonclustered located on PRIMARY IsDisabled, AccessMode, Title
ndx_IsLicensed nonclustered located on PRIMARY IsLicensed
ndx_IsSyncWithDirectory nonclustered located on PRIMARY IsSyncWithDirectory
ndx_lastname nonclustered located on PRIMARY LastName
ndx_Security nonclustered located on PRIMARY BusinessUnitId
UQ_TestTableActiveDirectoryGuid nonclustered, unique, unique key located on PRIMARY ActiveDirectoryGuid
*/

Step 2: Remove Indexes and Statistics

-- Removing indexes created by Tuning Advisor
USE TEST_MSCRM
GO

DROP INDEX _dta_index_TestTable_10_1954106002__K1_12_36_83_84
ON [dbo].[TestTable];
DROP STATISTICS _dta_index_TestTable_10_1954106002__S1_12_36_83_84
ON [dbo].[TestTable];
GO

Step 3: Repeat the search and removal processes (step 1 & 2)

An example app, such as Dynamics 365 (On Premise version), these tables are known to have indexes recommended by the Tuning Advisor:

[dbo].[AccountBase]
[dbo].[ActionCardBase]
[dbo].[ActivityPointerBase]
[dbo].[AsyncOperationBase]
[dbo].[ContactBase]
[dbo].[CustomerAddressBase]
[dbo].[InternalAddressBase]
[dbo].[PhoneCallBase]
[dbo].[PostBase]
[dbo].[SystemUserBase]
[dbo].[WorkflowBase]
Exceptions:
TITLE: DTAEngine
------------------------------

1% of consumed workload has syntax errors. Check tuning log for more information.

------------------------------
BUTTONS:

OK
------------------------------

When generating trace files via SQL Management Studio, this error was caused by ‘Showplan Statistics Profile’ option under the ‘Performance’ category on the Event Selection tab in SQL Server Profiler – be sure to uncheck that when generating a trace file to preempt this issue.

 

As it is often not recommended to apply all items from DTA, here is a sample of a refined set of Transactional SQL (T-SQL) that Application Specialists & DBAs have agreed upon in the past:

-- Optimize Dynamics Database
use [DYNAMICS]
go

CREATE CLUSTERED INDEX [_tuning_index_TemplateID] ON [dbo].[SY20000]
(
	[TemplateID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE CLUSTERED INDEX [_tuning_index_USERID_SectionID_MetricSequence] ON [dbo].[SY08100]
(
	[USERID] ASC,
	[SectionID] ASC,
	[MetricSequence] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_USERID_CmdParentDictID_CmdParentFormID] ON [dbo].[SY07110]
(
	[USERID] ASC,
	[CmdParentDictID] ASC,
	[CmdParentFormID] ASC,
	[CmdParentCmdID] ASC,
	[CmdSequence] ASC,
	[CmdDictID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [INBR]
go

CREATE CLUSTERED INDEX [_tuning_index_DUEDATE_CURTRXAM] ON [dbo].[RM20101]
(
	[DUEDATE] ASC,
	[CURTRXAM] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_DUEDATE_CURTRXAM] ON [dbo].[RM30101]
(
	[DUEDATE] ASC,
	[CURTRXAM] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_CRLMTTYP] ON [dbo].[RM00101]
(
	[CRLMTTYP] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_CUSTNMBR] ON [dbo].[RM00103]
(
	[CUSTNMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_CUSTNMBR_AGPERAMT_1_AGPERAMT_2] ON [dbo].[RM00103]
(
	[CUSTNMBR] ASC,
	[AGPERAMT_1] ASC,
	[AGPERAMT_2] ASC,
	[AGPERAMT_3] ASC,
	[AGPERAMT_4] ASC,
	[AGPERAMT_5] ASC,
	[AGPERAMT_6] ASC,
	[AGPERAMT_7] ASC
)
INCLUDE([TNSFCLIF],[NONSFLIF],[CUSTBLNC],[LASTAGED],[FRSTINDT],[LSTNSFCD],[LPYMTAMT],[LASTPYDT],[LSTTRXDT],[LSTTRXAM],[LSTFCHAM],[UPFCHYTD],[AVDTPLYR],[AVDTPLIF],[AVGDTPYR],[NUMADTPL],[NUMADTPY],[NUMADTPR],[TDTKNYTD],[TDTKNLYR],[TDTKNLTD],[TDISAYTD],[RETAINAG],[TNSFCYTD],[NONSFYTD],[UNPSTDSA],[UNPSTDCA],[UNPSTOSA],[UNPSTOCA],[NCSCHPMT],[TTLSLYTD],[TTLSLLTD],[TTLSLLYR],[TCOSTYTD],[TCOSTLTD],[TCOSTLYR],[TCSHRYTD],[TCSHRLTD],[TCSHRLYR],[TFNCHYTD],[TFNCHLTD],[TFNCHLYR],[FNCHCYTD],[FNCHLYRC],[TBDDTYTD],[TBDDTLYR],[TBDDTLTD],[TWVFCYTD],[TWVFCLTD],[TWVFCLYR],[TWROFYTD],[TWROFLTD],[TWROFLYR],[TTLINYTD],[TTLINLTD],[TTLINLYR],[TTLFCYTD],[TTLFCLTD],[TTLFCLYR],[WROFSLIF],[WROFSLYR],[WROFSYTD],[HIBALLYR],[HIBALYTD],[HIBALLTD],[LASTSTDT],[LSTSTAMT],[DEPRECV],[ONORDAMT],[TTLRTYTD],[TTLRTLTD],[TTLRTLYR],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_NOTEINDX_RevisionNumber] ON [dbo].[CN30100]
(
	[NOTEINDX] ASC,
	[RevisionNumber] DESC
)
INCLUDE([CUSTNMBR],[CPRCSTNM],[DATE1],[Contact_Date],[TIME1],[Contact_Time],[RevisionDate],[RevisionTime],[CN_Group_Note],[Caller_ID_String],[Action_Promised],[ActionType],[Action_Date],[Action_Assigned_To],[Action_Completed],[Action_Completed_Date],[Action_Completed_Time],[Amount_Promised],[USERID],[Note_Display_String],[CNTCPRSN],[ADRSCODE],[USERDEF1],[USERDEF2],[USRDAT01],[PRIORT],[NOTECAT],[NoteStatus],[Action_Cancelled_By],[Action_Cancelled_Date],[MODIFDT],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE NONCLUSTERED INDEX [_tuning_index_CUSTNMBR_Contact_Date_Contact_Time] ON [dbo].[CN00100]
(
	[CUSTNMBR] ASC,
	[Contact_Date] DESC,
	[Contact_Time] DESC,
	[NOTEINDX] ASC
)
INCLUDE([CPRCSTNM],[DATE1],[TIME1],[RevisionNumber],[CN_Group_Note],[Caller_ID_String],[Action_Promised],[ActionType],[Action_Date],[Action_Assigned_To],[Action_Completed],[ACTCMDSP],[Action_Completed_Date],[Action_Completed_Time],[Amount_Promised],[Amount_Received],[USERID],[Note_Display_String],[CNTCPRSN],[ADRSCODE],[USERDEF1],[USERDEF2],[USRDAT01],[PRIORT],[NOTECAT],[NoteStatus],[Action_Cancelled_By],[Action_Cancelled_Date],[MODIFDT],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_NOTEINDX_RevisionNumber_RMDTYPAL] ON [dbo].[CN30200]
(
	[NOTEINDX] ASC,
	[RevisionNumber] DESC,
	[RMDTYPAL] ASC,
	[DOCNUMBR] ASC
)
INCLUDE([CUSTNMBR],[CPRCSTNM],[RevisionDate],[RevisionTime],[USERID],[ActionType],[ActionAmount],[CURTRXAM],[CURNCYID],[CURRNIDX],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [ManagementReporter]
go

CREATE NONCLUSTERED INDEX [_tuning_index_StartBoundary_IsEnabled_Id] ON [Scheduling].[Trigger]
(
	[StartBoundary] ASC,
	[IsEnabled] ASC,
	[Id] ASC
)
INCLUDE([Type],[UnitOfMeasure],[EndBoundary],[RecurrenceLimit],[Interval],[DaysOfWeek],[DayOfMonth],[WeekOfMonth],[MonthOfYear],[OnlyWeekday],[TimeZoneId],[RunImmediately],[Version]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [OFFIC]
go

CREATE CLUSTERED INDEX [_tuning_index_ACTINDX] ON [dbo].[GL30000]
(
	[ACTINDX] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE CLUSTERED INDEX [_tuning_index_ACTNUMBR_1_ACTNUMBR_4_ACCTTYPE] ON [dbo].[GL00100]
(
	[ACTNUMBR_1] ASC,
	[ACTNUMBR_4] ASC,
	[ACCTTYPE] ASC,
	[ACTNUMBR_2] ASC,
	[ACTNUMBR_3] ASC,
	[ACTNUMBR_5] ASC,
	[ACTNUMBR_6] ASC,
	[ACTNUMBR_7] ASC,
	[ACTNUMBR_8] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [SC]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

-- Optimize DB Related to Dynamics
use [DF]
go

CREATE CLUSTERED INDEX [_tuning_index_CUSTNMBR] ON [dbo].[RM00101]
(
	[CUSTNMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_CURTRXAM_RMDTYPAL_DOCNUMBR] ON [dbo].[RM20101]
(
	[CURTRXAM] ASC,
	[RMDTYPAL] ASC,
	[DOCNUMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go