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

How to Migrate a Database to a Newer Version of Microsoft SQL Server

Get Version of new SQL server

Create a sql-dump script on the old SQL server:

Run SQL Management Studio (“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”) > right-click DB > Tasks > Generate Scripts

Choose Objects > ‘Script entire database and all database objects’ > Next > Advanced

Target the correct database version

Set SQL Server version to match the target > Types of data to script option=Schema and data > Script Logins=True > Script Full-Text Indexes=True > Script Triggers=True > OK > Next > Next > Finish

Execute Script on New Server:

Fix Database paths before execution:

USE [master]
GO
/****** Object: Database [testDb] Script Date: 11/3/2018 5:10:13 PM ******/
CREATE DATABASE [testDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'testDb', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\testDb.mdf' , SIZE = 598016KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'testDb_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\testDb.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [testDb] SET COMPATIBILITY_LEVEL = 130
GO

Use PowerShell to invoke SQL Query:

Invoke-Sqlcmd -InputFile "B:\Backups\testDb.sql" | Out-File -FilePath "B:\Backups\testDb.rpt"

T-SQL Msg 3101 Exclusive access could not be obtained because the database is in use

Symptom:
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Completion time: 2012-10-27T20:46:07.0030490-07:00
Resolution:

Assuming that the database name is TEST_MSCRM and it’s backup file is B:\Backup\TEST_MSCRM.bak’

USE [master]
GO

ALTER DATABASE [TEST_MSCRM]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; --This rolls back all uncommitted transactions

RESTORE DATABASE [TEST_MSCRM]
FROM DISK = N'B:\Backup\TEST_MSCRM.bak' WITH FILE = 1,
MOVE N'mscrm' TO N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\TEST_MSCRM.mdf',
MOVE N'mscrm_log' TO N'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

SQL Error Msg 3201, Level 16, State 2

Sample Error Message:

Msg 3201, Level 16, State 2, Line 2
Cannot open backup device '\\1.1.1.1\d$\backup\TEST_MSCRM.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Resolution:

  1. Get the SQL Service run-as account
    $sqlServiceRunas=(Get-WMIObject win32_service |?{$_ -like "*MSSQLSERVER*"}).StartName
  2. Grant SQL Service account Full access to parent folder of backup directory
    $backupFile='\\1.1.1.1\d$\backup\TEST_MSCRM.bak'
    $parentDirectory=split-path $backupFile -parent
    $acl = Get-ACL $parentDirectory
    $allowFullAccesss=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlServiceRunas,"Full","Allow")
    $acl.AddAccessRule($allowFullAccesss)
    Set-Acl $parentDirectory $acl
  3. Since the referenced path is an ‘Administrative Share’, the SQL account must be added to the local ‘Administrators’ group of the File Server
    $backupFile='\\1.1.1.1\d$\Backup\TEST_MSCRM.bak'
    [regex]$regexFileServer='^\\\\([\d\w\.\-]+)\\'
    $fileServer=.{$address=$regexFileServer.Match($backupFile).Groups[1].Value
    [System.Net.Dns]::GetHostByAddress($address).HostName
    }
    $session=new-pssession $fileServer
    if($session){
    invoke-command -session $session -scriptblock{
    param($principleName,$groupName)
    write-host "Adding $principleName into $groupName";
    Add-LocalGroupMember -Group $groupName -Member $principleName;
    write-host "$env:computername group $groupName now has these members:`r`n";
    get-localgroupmember $groupName|ft -autosize
    } -args $sqlServiceRunas,'Administrators'
    remove-pssession $session
    }else{
    write-warning "Unable to connect to $fileServer"
    }

Invoke T-SQL on Multiple Servers

$computersAndCredentials=@{
    'SQL01'=@{'intranet\sql01Admin'='PASSWORD'}
    'SQL02'=@{'intranet\sql02Admin'='PASSWORD'}
    'SQL03'=@{'intranet\sql03Admin'='PASSWORD'}
    'SQL04'=@{'intranet\sql04Admin'='PASSWORD'}
}
$tSql="
    --Update the Setting with a new value:
    USE MSCRM_CONFIG
    GO
    UPDATE ServerSettingsProperties
    SET IntColumn='600' where ColumnName='LongQueryThresholdInSeconds'
    GO
    --View the current Setting:
    select IntColumn from MSCRM_CONFIG..ServerSettingsProperties
    where ColumnName='LongQueryThresholdInSeconds'
    GO
    "

function invokeSql($computersAndCredentials,$tSql){
    
    foreach ($item in $computersAndCredentials.GetEnumerator()){
        $computerName=$item.Name
        $item.Value.getEnumerator()|%{$userName=$_.Name;$plainTextPassword=$_.Value}
        $password=ConvertTo-securestring $plainTextPassword -AsPlainText -Force
        $credentials=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $userName,$password
        #write-host "$($item.Name) = $username / $plainTextPassword"
        $scriptBlock={
            param($tSql)
            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{
                        Clear-Host;
                        write-host $($content|out-string).Trim()
                        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;
            }
            write-host "Executing function on $env:computername"
            import-module sqlps
            $confirmed=confirmation -content $tSql
            if($confirmed){
                try{
                    invoke-sqlcmd -Query $tSql -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
                    return $true
                }catch{
                    write-warning $_
                    return $false
                }
            }else{
                write-host 'No confirmations received. Changes have NOT been made.'
                return $false
            }
        }
        $result=invoke-command -ComputerName $computerName -Credential $credentials -ScriptBlock $scriptBlock -Args $tSql
        write-host $result
    }

}

invokeSql $computersAndCredentials $tSql

Microsoft Dynamics Email Router Failed to Start

Symptoms

Event Log:

Item 1: highest correlating event ID 26234

#26234 - The Email Router service could not process a provider work item using assembly:
Microsoft.Crm.Tools.EmailProviders.dll and class:
Microsoft.Crm.Tools.Email.Providers.SmtpPollingSendEmailProvider.
System.Threading.ThreadAbortException: Thread was being aborted.
at Microsoft.Crm.Tools.Email.Providers.CrmPollingSendEmailProvider.Run()
at Microsoft.Crm.Tools.Email.Agent.ServiceCore.ExecuteProviderWork(ObjectproviderQueueRequestObject)

Item 2: high correlation warning event ID 17972

Query execution time of 35.5 seconds exceeded the threshold of 10 seconds. Thread: 11; Database: TestOrg_MSCRM; Server:crm-sql01; Query: select 
top 5 "email0".Subject as "subject"
, "email0".Description as "description"
, "email0".PriorityCode as "prioritycode"
, "email0".ActivityId as "activityid"
, "email0".ModifiedOn as "modifiedon"
, "email0".StateCode as "statecode"
, "email0".StatusCode as "statuscode"
, "email0".DeliveryAttempts as "deliveryattempts"
, "email0".AttachmentCount as "attachmentcount"
, convert(bigint, "email0".VersionNumber) as "versionnumber"
, N'' as "safedescription"
from
Email as "email0" WITH (NOLOCK)
join ActivityParty as "activityparty1" WITH (NOLOCK) on ("email0".ActivityId = "activityparty1".ActivityId and ("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0 and ("activityparty1".PartyId in (@PartyId0
, @PartyId1
, @PartyId2
, @PartyId3
, @PartyId4))))
where
("email0".StateCode = @StateCode0 and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null) and "email0".DirectionCode = @DirectionCode0 and ("email0".DeliveryAttempts = @DeliveryAttempts0))
order by
"email0".ActualEnd asc.

Item 3: high correlation event ID 61346

#61346 - An error occurred while checking for outgoing email messages to process for SMTP: https://crm.kimconnect.com/testorg. System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: SQL timeout expired. (Fault Detail is equal to Exception details: 
ErrorCode: 0x80044151
Message: SQL timeout expired.
TimeStamp: 2012-10-05T11:54:12.3179277Z
--
Exception details:
ErrorCode: 0x80044151
Message: SQL timeout expired.
TimeStamp: 2012-10-05T11:54:12.3179277Z
--

Item 4: not a direct cause, but usually occurs as a precursor

#61346 - An error occurred while checking for outgoing email messages to process for SMTP: https://testorg.kimconnect.com. System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: User does not have send-as privilege. (Fault Detail is equal to Exception details: 
ErrorCode: 0x8004480D
Message: User does not have send-as privilege.
TimeStamp: 2012-10-05T11:57:06.5096616Z
Resolution:
1. Increase the SQL execution time threshold to 60 seconds or more for the specific application database.
 

To fix a single CRM database

USE OrgName_MSCRM 
GO
EXEC sp_configure 'remote query timeout', 0
GO
RECONFIGURE
GO

To fix all CRM databases (on all CRM servers)

--View the current Setting:
select IntColumn from MSCRM_CONFIG..ServerSettingsProperties where ColumnName='LongQueryThresholdInSeconds'
--Update the Setting with a new value:
USE MSCRM_CONFIG
GO
UPDATE ServerSettingsProperties
SET IntColumn='600' where ColumnName='LongQueryThresholdInSeconds'

Source: https://social.technet.microsoft.com/wiki/contents/articles/12601.crm-2011-timeouts-and-limits.aspx

Explanation: by default the SQL Server’s query execution timeout is 600 seconds. Applications may override that with other values, such as 10s for MSCRM. Therefore, it’s necessary to fix these databases to make them less ‘aggressive.’
 
2. Fix the ErrorCode: 0x8004480D on any Org that exhibits such

 

Other Solutions:

Set a scheduled task on CRM servers to call this script every 30 minutes:

# processWatcher.ps1
# Version 0.0.2

# Modify these variables
$computername=$env:computername
$serviceName='MSCRMEmail'
$desiredStatus='Running'
$credential=$false # set this to false to use the default running context
$actionScript={
    param($serviceName)
    $erroractionpreference='stop'
    $emailAgentXml='C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.SystemState.xml'
    try{
        stop-service $serviceName
        Rename-Item $emailAgentXml "$emailAgentXml.old"
        Rename-Item "$emailAgentXml.bak" "$emailAgentXml.bak.old"
        start-service $serviceName
        return $true
    }catch{
        write-host $_
        return $false
    }
}
function watchProcess{
    param(
        $serviceName,
        $computername=$env:computername,
        $desiredStatus='Running',
        $action,
        $credential
        )
    function checkService($serviceName,$computername=$env:computername,$status='Running'){
        # Sanitation
        #$invalidChars=$processName.IndexOfAny([System.IO.Path]::GetInvalidFileNameChars())
        $systemInvalidChars =[Regex]::Escape(-join [System.Io.Path]::GetInvalidFileNameChars())
        $regexInvalidChars = "[$systemInvalidChars]"
        $serviceName=$serviceName -replace [regex]::Matches($serviceName, $regexInvalidChars, 'IgnoreCase').Value
        if($desiredStatus -ne 'Stopped'){
            $desiredStatus='Running'
        }
        try{
            $service=get-service -name $serviceName -ComputerName $computername|select -first 1
            if($service.Status -eq $status){
                write-host "status matches the desired state" -foregroundcolor Green
                return $true
            }elseif($null -ne $service.Status){
                write-host "status doesn`'t match the desired state" -foregroundcolor Red
                return $false
            }else{
                write-host "$serviceName was not found" -foregroundcolor Red
                return $false         
            }
        }catch{
            Write-Error $_
            return $false
        }
    }
    $serviceIsRunning=checkService $serviceName $computername $desiredStatus
    if ($serviceIsRunning){
        write-host "$serviceName is running on $computerName." -foregroundcolor Green
        return $true
    }else{
        if($session.state -eq 'Opened'){remove-pssession $session}
        do{
            $session=if($credential){
                    try{
                        New-PSSession -ComputerName $computer -Credential $credential -ea Stop
                    }catch{
                        New-PSSession -ComputerName $computer -Credential $credential -SessionOption $(new-pssessionoption -IncludePortInSPN)
                    }
                }else{
                    try{
                        New-PSSession -ComputerName $computer -ea Stop
                    }catch{
                        New-PSSession -ComputerName $computer -SessionOption $(new-pssessionoption -IncludePortInSPN)
                    }
                }
        } until ($session.state -match "Opened")
        $success=if($session -and $action -and $serviceName){
                invoke-command -Session $session -scriptblock $action -Args $serviceName
            }else{
                $false
            }
        if($session){Remove-PSSession $session}
        return $success
    }
}

watchProcess $servicename $computername $desiredStatus $actionScript $credential

PowerShell: Restore SQL Database from Full and/or Differential Backups

$databaseName='BALOO_MSCRM'
$newDatabaseName='BALOO_MSCRM'
$dbData='mscrm' # set this value to $null for autogen defaults
$dbLog='mscrm_log' # set this value to $null for autogen defaults

function restoreDatabase{
    param(
        $databaseName,
        $dbData,
        $dbLog,
        $newDatabaseName
    )

    if(!$dbData){$dbData=$databaseName}
    if(!$dbLog){$dbData=$databaseName+'_log'}
    if(!$newDatabaseName){$newDatabaseName=$databaseName}
    function pickIndex($list){
        function displayList($list){
            for ($i=0;$i -lt $list.count;$i++){
                    write-host "$i`: $(($list[$i]|out-string).trim())"
            }
        }
        
        do {
            displayList $list
            try {
                $flag = $true
                [int]$pick=Read-Host -Prompt "`n--------------------------------------------------------`nPlease type the number corresponding to the desired item`n--------------------------------------------------------"
                } # end try
            catch {$flag = $false}
        } until ($pick -lt $list.count -and $flag)    
        $pickIndex=$pick
        $pickedItem=$list[$pick]  
        write-host "Picked index is $pickIndex, which corresponds to: $pickedItem"
        return $pickIndex
    }

    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{
                Clear-Host;
                write-host $($content|out-string).Trim()
                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;
    }

    <# SQLSERVER module is not being used in this iteration because the Get-SqlBackupHistory does not show backup file locations
    if(!(get-module sqlserver -ea Silentlycontinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        #The Tls12 setting preempts this error:
        #WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'.
        #PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlserver'.
        #Try Get-PSRepository to see all available registered module repositories.
        #At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:1772 char:21
        #+ ...          $null = PackageManagement\Install-Package @PSBoundParameters
        #+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        #    + CategoryInfo          : ObjectNotFound: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package], Exception
        #    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage
        install-module sqlserver -allowclobber -force
    }
    $backups=Get-SqlBackupHistory -ServerInstance $env:computername -DatabaseName $databaseName
    $backupDates=$backups.BackupStartDate
    $pickIndex=pickIndex $backupDates
    $backupItem=$backups[$pickIndex]
    #>

    import-module sqlps
    $getBackups="-- Get Backup History for required database
        USE $databaseName
        GO
        SELECT TOP 100
        s.database_name,
        m.physical_device_name AS location,
        CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
        CAST(DATEDIFF(second, s.backup_start_date,
        s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
        s.backup_start_date,
        CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
        CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
        CASE s.[type] WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END AS BackupType,
        s.server_name,
        s.recovery_model
        FROM msdb.dbo.backupset s
        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
        WHERE s.database_name = DB_NAME() -- Remove this line for all the database
        ORDER BY backup_start_date ASC, backup_finish_date
        GO
    "
    $backups=invoke-sqlcmd $getBackups
    $backupDates=$backups.backup_start_date
    $pickIndex=pickIndex $backupDates
    $backupItem=$backups[$pickIndex]
    $backupFile=$backupItem.location
    #This SQLSERVER module cmdlet is not being used
    #Restore-SqlDatabase -ServerInstance "." -Database $databaseName -BackupFile $backupItem.location

    <# Some errors that have to do with DB being in use. Also, WITHNORECOVERY option must specified for FULL restores.
    Restore-SqlDatabase : System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no
    files are ready to rollforward.
    At line:1 char:1
    + Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
        + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

    Restore-SqlDatabase : System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
    At line:1 char:1
    + Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
        + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

    #>

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            $sqlDefaults=@{
                'dataDirectory'=$defaultDataDirectory
                'logDirectory'=$defaultLogDirectory
                'backupDirectory'=$defaultBackupDirectory
                }

    $backupFileIsComplete=if($backupFile -match 'bak$'){$true}else{$false}
    if($backupFileIsComplete){
        $restoreDatabase="
            USE master
            GO
            ALTER DATABASE $databaseName
                SET SINGLE_USER
                WITH ROLLBACK IMMEDIATE
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH FILE = 1,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
        "
    }else{
        $lastFullBackup=.{
            $backupLocations=$backups.location
            $resultFound=$false
            $currentIndex=$pickIndex-1
            Do{
                $currentIndex-=1
                if($backupLocations[$currentIndex] -match 'bak$'){
                    $resultFound=$true
                    return $backupLocations[$currentIndex]
                }
            }until($resultFound)
        }    
        $restoreDatabase="
            USE master
            GO
            ALTER DATABASE $databaseName
                SET SINGLE_USER
                WITH ROLLBACK IMMEDIATE
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$lastFullBackup' WITH NORECOVERY,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH RECOVERY,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO        
        "
    }

    $confirmed=confirmation -content $restoreDatabase
    if($confirmed){
        try{
            invoke-sqlcmd -Query $restoreDatabase -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
            return $true
        }catch{
            write-warning $_
            return $false
        }
    }else{
        write-host 'No confirmations received. No changes have been made.'
        return $false
    }
}

restoreDatabase $databaseName $dbData $dbLog $newDatabaseName

Microsoft SQL Cloning Database to a Different DB Name

# User defined variables
$sourceSqlServer='DEV-SQL01'
$sourceDatabaseName='TEST_MSCRM'
$sourceSaCredential=$(get-credential)
$destinationSqlServer='DEV-SQL01'
$destinationDatabaseName='Test_MSCRM'
$destinationSaCredential=$(get-credential)

function copyDatabase{
    param(
        $sourceSqlServer,
        $sourceDatabaseName,
        $sourceSaCredential,
        $destinationSqlServer,
        $destinationDatabaseName,
        $destinationSaCredential
    )
    function convertLocalToUnc($localPath,$computername){    
        $uncPath=.{$x=$localPath -replace "^([a-zA-Z])\:","\\$computername\`$1`$";
                    if($x -match '\\$'){return $x.Substring(0,$x.length-1)}else{return $x}
                    }
        return $uncPath
    }
 
    function mountDriveAsUser($username,$password,$driveLetter,$uncPath){
        if(get-psdrive $driveLetter -ea SilentlyContinue){
            #Remove-PSDrive $firstAvailableDriveLetter -ea SilentlyContinue #This does not affect drives being mounted by 'net use' command
            net use /delete ($driveLetter+':') 2>&1>null
            }    
        try{
            # This command cannot persist when out of scope of function; hence, net use is required
            # New-PSDrive –Name $mountLetter –PSProvider FileSystem –Root $uncPath –Persist -Credential $mountAsCred|out-null
            net use "$driveLetter`:" "$uncPath" /user:$username $password /persistent:Yes 2>&1>null
            if(test-path "$driveLetter`:\"){
                write-host "$driveLetter`: has successfully mounted.";
                #return $true;
                }
            else{
                write-host "Unable to mount drive $driveLetter";
                #return $false
                }
            }
        catch{
            write-warning "$error"
            #return $false
            }
        }
    $triggerSqlBackup={
        param ($databaseName)
        try{
            import-module sqlps
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $database=Get-SqlDatabase -ServerInstance $env:computername|?{$_.Name -eq $databaseName}
            Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly
            return $true
        }catch{
            write-warning $_
            return $false
        }
    }

    $restoreDbChangeName={
        param(
            $dbBackupFile,
            $newDatabaseName,
            $dbData='mscrm',
            $dbLog='mscrm_log'
            )
        import-module sqlps	
        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{
                    Clear-Host;
                    write-host $($content|out-string).Trim()
                    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;
        }
        
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
        $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
        $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
        $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
        $sqlDefaults=@{
            'dataDirectory'=$defaultDataDirectory
            'logDirectory'=$defaultLogDirectory
            'backupDirectory'=$defaultBackupDirectory
            }
        #$originalDb=invoke-sqlcmd -query "restore filelistonly from disk = '$dbBackupFile'"
        #$originalDbDataPath=$originalDb[0].PhysicalName
        #$originalDbLogPath=$originalDb[1].PhysicalName
        $sqlRestoreChangeDbName="
            USE [master]
            GO
            ALTER DATABASE [$newDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            GO
            RESTORE DATABASE [$newDatabaseName] FROM DISK = N'$dbBackupFile' WITH FILE = 1,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
            "	
        $confirmed=confirmation -content $sqlRestoreChangeDbName
        if($confirmed){
            try{
                invoke-sqlcmd -Query $sqlRestoreChangeDbName -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
                $removeBackupFile=confirmation -content "Delete backup file: $dbBackupFile?"
                if($removeBackupFile){
                    $null=remove-item -path $dbBackupFile -force -ea SilentlyContinue
                }else{
                    write-host "Backup file NOT removed: $dbBackupFile"
                }
                return $true
            }catch{
                write-warning $_
                return $false
            }
        }else{
            write-host 'No confirmations received. No changes have been made.'
            return $false
        }
    }
    function invokeGetDefaultSqlPaths($sqlServer,$saCredential){
        $defaultValues=invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{      
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            return @{
                'dataDirectory'=$defaultDataDirectory
                'logDirectory'=$defaultLogDirectory
                'backupDirectory'=$defaultBackupDirectory
                }
        }
        if($defaultValues){return $defaultValues}
        else{return $false}    
    }

    $successfulBackup=invoke-command -computername $sourceSqlServer `
        -credential $sourceSaCredential `
        -scriptBlock $triggerSqlBackup `
        -Args $sourceDatabaseName

    if($successfulBackup){
        if ($sourceSqlServer -ne $destinationSqlServer){
            # Extract username and passwords to be used as mounts
            $sourceUsername=$sourceSaCredential.Username
            $sourcePassword=$sourceSaCredential.GetNetworkCredential().password
            $destinationUsername=$destinationSaCredential.Username
            $destinationPassword=$destinationSaCredential.GetNetworkCredential().password

            # Scan for next available drive letter, excluding D "CD Rom" and H "Home"
            $unavailableDriveLetters=(Get-Volume).DriveLetter|sort
            $availableDriveLetters=.{(65..90|%{[char]$_})|?{$_ -notin $unavailableDriveLetters}}
            [char]$firstAvailableDriveLetter=$availableDriveLetters[0]
            [char]$secondAvailableDriveLetter=$availableDriveLetters[1]
        
            # Mount Source UNC path
            $sourceSqlPaths=invokeGetDefaultSqlPaths $sourceSqlServer $sourceSaCredential
            $sourceUncPath=convertLocalToUnc $sourceSqlPaths.BackupDirectory $sourceSqlServer
            mountDriveAsUser $sourceUsername $sourcePassword $firstAvailableDriveLetter $sourceUncPath
            $sourceFolder="$firstAvailableDriveLetter`:\"
        
            # Mount Destination UNC path
            $destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential
            $destinationUncPath=convertLocalToUnc $destinationSqlPaths.BackupDirectory $destinationSqlServer
            mountDriveAsUser $destinationUsername $destinationPassword $secondAvailableDriveLetter $destinationUncPath
            $destinationFolder="$secondAvailableDriveLetter`:\" 
            
            $sourceBackupFile="$sourceFolder$sourceDatabaseName.bak"
            $sanityPassed=(test-path $sourceFolder) -and (test-path $destinationFolder) -and (test-path $sourceBackupFile)
            if ($sanityPassed){
                write-host "Now copying $sourceBackupFile to $destinationFolder. Please wait..."
                $fileSize=(get-item $sourceBackupFile).Length/1GB
                $transferTime=(measure-command {robocopy "$sourceFolder" "$destinationFolder" "$sourceDatabaseName.bak"}).TotalHours
                $transferSpeed=[math]::round($fileSize/$transferTime,2)
                write-host "$([math]::round($fileSize,2)) GB was copied in $([math]::round($transferTime,2)) hours => Speed: $transferSpeed GB/Hour"
            }else{
                write-warning "Unable to reach one of these paths:`r`n$sourceFolder`r`n$destinationFolder`r`n$sourceBackupFile"
            }
            # Cleanup Routine
            write-host "Deleting backup file at $sourceBackupFile..."
            $null=remove-item -path $sourceBackupFile -force -ea SilentlyContinue
            write-host "Removing drive mounts..."
            net use /delete $($firstAvailableDriveLetter+':') 2>&1>null
            net use /delete $($secondAvailableDriveLetter+':') 2>&1>null         
        }else{
            $destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential            
        }
        $destinationBackupFile="$($destinationSqlPaths.BackupDirectory)\$sourceDatabaseName.bak"
        $result=invoke-command -computername $destinationSqlServer `
            -credential $destinationSaCredential `
            -scriptBlock $restoreDbChangeName `
            -Args $destinationBackupFile,$destinationDatabaseName
        return $result
    }else{
        write-warning "SQL Backup has failed at the source server $sourceSqlServer"
        return $false
    }
}

copyDatabase $sourceSqlServer `
            $sourceDatabaseName `
            $sourceSaCredential `
            $destinationSqlServer `
            $destinationDatabaseName `
            $destinationSaCredential

Microsoft Dynamics Audit History Data Missing After Org Migration

Issue:

We have recently moved some Orgs from CRM version 8.x to 9.x, and audit history of some intakes shows data as just these container icons

Resolution:

It appears that the upgrading process somehow has generated the Audit Change ([Action] = 102) at Case Entity Level (ObjectTypeCode 112) as Disabled Events (ChangeData = True). Therefore, the recommended action would be:

  1. Make a full backup of the DB prior to any changes!
  2. Run this TSQL command against the DB
    USE DatabaseName_MSCRM -- fill in the correct database name here
    GO
    DELETE FROM [dbo].[AuditBase]
    WHERE
    Action = 102 AND -- Audit Change at Entity Level
    ChangeData = 'True' AND -- For Disabled Events
    (ObjectTypeCode = 112 OR ObjectTypeCode = 2) -- Case OR Contact Entity Object Type Codes
    Sample Output:
  3. Refresh the case records via the CRM UI to verify that new values have been generated in the Audit History query.

 

How to Add Domain Admins to SQL Server

Step 1:

Right-click Start > Search > type in ‘ssms.exe’ > right-click Microsoft SQL Server Management Studio > Run as Administrator

Authenticate using an sa account > New Query > paste this line:

/* Adding Principle into SQL sysadmin role */
exec sp_addsrvrolemember 'whatDomain\Domain Admins', 'sysadmin';
go

Observe a result similar to this:

Commands completed successfully.
Completion time: 2012-09-03T18:42:58.1788611-07:00

That’s it. There is no ‘Step 2.’ Fin.

# Alternative Method via PowerShell
$sa='sa'
$saPassword='whatpassword'
$domainAdmins="$env:USERDOMAIN\Domain Admins"

import-module sqlps
try{
    if($sa -and $saPassword){
        invoke-sqlcmd -username $sa -password $saPassword -Query "exec sp_addsrvrolemember '$domainAdmins', 'sysadmin'"
    }else{
        invoke-sqlcmd -Query "exec sp_addsrvrolemember '$domainAdmins', 'sysadmin'"
    }
}catch{
    write-warning $_
}

Alternative Graphical Interface Method:

Run Microsoft SQL Server Management Studio (ssms.exe) > authenticate as an sa > navigate to [Server Named Instance] > Security > right-click Logins > New Logins > set Login Name = YOURDOMAIN\Domain Admins & select ‘Windows authentication’

Click on Server Roles > put a check mark next to ‘sysadmin’ > click OK

Visually verify that ‘Domain Admins’ has been added to the security group

Troubleshooting:
Issue:

A member of the Domain Admins group could not logon to SQL Server even though such group has been granted the ‘sysadmin’ role on such database machine.

Resolution:

By default, Windows User Access Control (UAC) will associate the run-time of an un-elevated instance of SQL Server Management Studio in the context of a regular user, not ‘BUILTIN\Administrators’. To ensure that the GUI is initiated in the Admin contexts, one must right-click SSMS > select Run as Administrator. On then would members of the Domain Admins be granted access.