Microsoft

SQL Server – Clone Database – Schema & Statistics

Daniel Adeniji Database Consistency Checker (DBCC), DBCC CLONEDATABASE, Microsoft, Query Plan, SQL Server, Technical

Background

Working with my best friend to troubleshoot a non-performant query and so here we go.

Scenario

The database is too big and contains data that should be kept private and so what to do.

DBCC Clone

Outline

  1. Source SQL Instance
    • dbcc  clone
    • Backup resulting database
  2. Destination SQL Instance
    • Restore database
  3. Compare Queries

Source SQL Instance

DBCC Clone

SQL

DBCC CLONEDATABASE 

	(
		  [WideWorldImportersDW]
		, [WideWorldImportersDW_DBCCClone]
	);   

GO
GO

Database Backup

SQL

exec master..xp_create_subdir 'E:\temp'
go

backup database [WideWorldImportersDW_DBCCClone]
to disk = 'E:\temp\WideWorldImportersDW_DBCCClone.bak'
with init, format, stats=1 

GO

Destination SQL Instance

Database Restore

SQL

USE [master]
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone'
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone'
go

RESTORE DATABASE [WideWorldImportersDW_DBCCClone] 

FROM  DISK = N'C:\Temp\WideWorldImportersDW_DBCCClone.bak' WITH  FILE = 1

	,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_167133806.mdf'
	,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_UserData_1341343279.ndf'
	,  MOVE N'fg_Partition_Year_Base__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Base_file_001_4162930605.ndf'
	,  MOVE N'fg_Partition_Year_2010__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2010_file_001_3028494415.ndf'
	,  MOVE N'fg_Partition_Year_2011__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2011_file_001_474007300.ndf'
	,  MOVE N'fg_Partition_Year_2012__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2012_file_001_3961387451.ndf'
	,  MOVE N'fg_Partition_Year_2013__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2013_file_001_2554884830.ndf'
	,  MOVE N'fg_Partition_Year_2014__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2014_file_001_3195214954.ndf'
	,  MOVE N'fg_Partition_Year_2015__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2015_file_001_2796606126.ndf'
	,  MOVE N'fg_Partition_Year_2016__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2016_file_001_2922730419.ndf'
	,  MOVE N'fg_Partition_Year_2017__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2017_file_001_89564842.ndf'
	,  MOVE N'fg_Partition_Year_2018__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2018_file_001_2594545631.ndf'
	,  MOVE N'fg_Partition_Year_2019__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2019_file_001_102789485.ndf'
	,  MOVE N'fg_Partition_Year_2020__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2020_file_001_2559250543.ndf'
	,  MOVE N'fg_Partition_Year_Null__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Null_file_001_3677708393.ndf'
	,  MOVE N'fg_Partition_Year_Next__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Next_file_001_2414564952.ndf'
	,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_3476128794.ldf'
	,  NOUNLOAD
	,  STATS = 5

GO

Compare Queries

Outline

  1. Compare Queries
    • Query 1

Compare Queries

Query 1

SQL
set nocount on
go

set statistics io on
go

declare @dateTS datetime
declare @date datetime

set @dateTS = getdate()
set @date = convert(varchar(10), @dateTS, 23)

select
          @dateTS as [@dateTS]
        , @date as [@date]

select top 10 *

from   [Dimension].[Date] tblD

select top 10 *

from   [Dimension].[Date] tblD

where  tblD.[Date] = @date
Output
Output – Source

query1_Source_20181115_0911AM

Output – Destination

query1_Destination_20181115_0912AM

Explanation
  1. We have data from original source
  2. And, not data from the resultant cloned database

 

Query Plan
Query Plan – Destination

queryPlan_Destination_20181115_0853AM

Query Plan – Source

queryPlan_Source_20181115_0905AM.PNG

Statistics IO
Image – Destination

statisticsIO.Destiation.2018115.0855AM

Image – Source

statisticsIO.Source.2018115.0856AM

Explanation
  1. Statistics IO
    • Scan Counts will match
    • Logical & Physical Reads will not
      • As Source has actual data, but destination does not

 

References

  1. Microsoft
    • Docs / SQL / T-SQL / Database Console Commands
      • DBCC CLONEDATABASE (Transact-SQL)

 

Reporting Services – Errors – ‘The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)’

Daniel Adeniji Microsoft, Reporting Services, Reporting Services Configuration Manager, SQL Server, Technical , , , , ,

Background

Post upgrade of Microsoft’s SQL Server, trying to access Reporting Services, ran into an error.

Error

Error Image

TheFeatureScaleOutDeploymentIsNotSupported.20181111.0342PM.PNG

Error Textual

The feature “Scale-out deployment” is not supported in this edition of Reporting Services.  (rsOperationNotSupported).

Trouble Shooting

Outline

  1. Confirm SQL Server Edition
  2. Review registered encryption keys for the Report Server Instance

Confirm SQL Server Edition

Launch SQL Server Query tool and issue a query that will help determine SQL Server Edition:

Code


select @@version as [version]

Output

Output Image

@@version.20181115.0551AM

Output Textual


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)
Oct 18 2018 23:11:05
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Explanation

  1. We are running SQL Server
    • Marketing :- v2017
    • RTM/Service Pack :- RTM
    • Cumulative Patch :- CU12
    • Edition :- Standard Edition

Review registered encryption keys for the Report Server Instance

using same query tool you used to determine SQL Server’s Version Number, please review the contents of the ReportServer.dbo.keys table.

Code

select *

from [ReportServer].dbo.[Keys]

Output

Output Image

ReportServer.dbo.keys.01.20181111.0342PM [brushedup].png

Explanation

  1. We have three entries
    • Records
      • Installation ID
        • 00000000-0000-0000-0000-000000000000
          • We can skip for now
        • BDEBD908-9B5E-4971-B6A5-0E6EB267A2CA
          • InstanceName = SSRS
            • SSRS is the new instance name for SQL Server Reporting Services
            • And, we can confirm that it is the one post upgrade
          • InstanceName= MSSQLSERVER
            • MSSQLSERVER is pre-upgrade name
            • We can remove it

Remediation

Outline

  1. Backup database table
  2. Remove previous key

Backup database table

Let us backup ReportServer.dbo.keys

Code


if schema_id('dbBackup') is null
begin

create schema [dbBackup] authorization [dboo];

end

select *

into   [ReportServer].[dbBackup].[keys.20181115.0607AM]

from   [ReportServer].[dbo].[keys]

Remove Previous Key

Remove previous key from ReportServer.dbo.keys

Code


delete
from   [ReportServer].[dbo].[keys]
where  InstanceName = 'MSSQLSERVER'

PowerShell – Get Computer name

Daniel Adeniji PowerShell, Linux, CentOS ,

Background

Wanted to highlight a couple of APIs for retrieving the computer name in Powershell.

Platform

Linux/Cent OS.

 

Outline

  1. Environment Variable
    • Get-ChildItem Env:HOSTNAME
  2. system.environment
    • machineName
  3. system.net.Dns
    • GetHostName
  4. Get-WmiObject ( only works on MS Windows )
    • Win32_ComputerSystem
      • Name

 

Environment Variables

Get-ChildItem Env:

Syntax


pwsh -Command "Get-Childitem Env:"

Output

Get-ChildItem.Env.20181112.0729PM

 

Get-ChildItem Env:HOSTNAME

Syntax


pwsh -Command "Write-Host( (Get-ChildItem Env:HOSTNAME).Value )"

Output

Get-ChildItem.Env.HostName.20181112.0723PM

system.environment

machineName

Syntax


pwsh -Command "Write-Host([system.environment]::machineName)"

Output

system.environment.machineName.20181112.0708PM

system.net.Dns

GetHostName

Syntax


pwsh -Command "Write-Host([system.net.Dns]::GetHostName())

Output

system.net.Dns.GetHostName.20181112.0706PM

 

Get-WmiObject.Win32_ComputerSystem.Name.20181113.0159AM.PNG

Win32_ComputerSystem

Syntax


powershell -Command "Write-Host((Get-WmiObject Win32_ComputerSystem).Name)"

Output

OS – MS Windows

Get-WmiObject.Win32_ComputerSystem.Name.20181113.0159AM.PNG

OS – Linux

Image

Get-WmiObject.Win32_ComputerSystem.Name.Linux.20181113.0203AM

Textual


>pwsh -Command "Write-Host( Write-Host((Get-WmiObject Win32_ComputerSystem).Name))"
Get-WmiObject : The term 'Get-WmiObject' is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:25
+ Write-Host( Write-Host((Get-WmiObject Win32_ComputerSystem).Name))
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Get-WmiObject:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

PowerShell Install On Linux / CentOS

Daniel Adeniji CentOS, Installation ( PowerShell ), Linux, Microsoft, PowerShell, Technical

Background

Still preparing our CentOS computer.

Our current step is to Install Microsoft’s PowerShell.

Guide

Our guide is :-

Installing PowerShell Core on Linux
Link

Outline

  1. Install
    • yum
      • Install
        • Install Powershell
      • Registered Repositories – Review
        • yum repolist
        • ls /etc/yum.repos.d
      • Registered Repositories – Update
        • Curl/tee
      • Install
        • Install Powershell
  2. Run Sample Powershell Scripts

Install

Install Powershell

yum install powershell

Syntax


yum install powershell

Output

Output – Image

yum.install.powershell.20181112.1030AM.PNG

Output – Text

>sudo yum install powershell
[sudo] password for dadeniji:
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.hostduplex.com
* extras: mirror.keystealth.org
* updates: mirror.keystealth.org
No package powershell available.
Error: Nothing to do
>

Explanation

Microsoft’s repository not registered on machine.

Registered Repositories – Review

Let us review the yum repositories that we have registered.

yum repolist

Syntax


yum repolist

Output

Output – Image

yum.repolist.20181112.1028AM.PNG

Output – Textual

>yum repolist
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.sonic.net
* extras: mirrors.usc.edu
* updates: sjc.edge.kernel.org
repo id repo name status
base/7/x86_64 CentOS-7 - Base 9,911
extras/7/x86_64 CentOS-7 - Extras 434
updates/7/x86_64 CentOS-7 - Updates 1,614
repolist: 11,959

 

/etc/yum.repos.d :- List Files

Syntax


ls -la /etc/yum.repos.d

Output

Output – Image

ls.etc.yum.repos.d.20181112.1033AM.PNG

Explanation

  1. The only repositories registered are CentOS

 

Registered Repositories – Update

Create File /etc/yum.repos.d/Microsoft.repo

Let us add Microsoft’s Redhat repository to the list of registered repositories.

Syntax


curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/microsoft.repo

Output

Output – Image

curl.tee.20181112.1035AM.PNG

Output – Textual

>curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum .repos.d/microsoft.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 504 0 --:--:-- --:--:-- --:--:-- 505
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/7/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
>

 

yum install

Using yum, let us install powershell.

yum install powershell

Syntax


sudo yum install -y powershell

Output

Output – Image
Output – Image – #1

yum.install.powershell.20181112.1037AM.PNG

Output – Image – #2

yum.install.powershell.20181112.1039AM.PNG

Output – Image – #3

yum.install.powershell.20181112.1041AM.PNG

Output – Image – #4

yum.install.powershell.20181112.1043AM.PNG

Output – Image – #5

yum.install.powershell.20181112.1045AM.PNG

 

 

Sample

Run Sample Scripts

Sample #1

Get PowerShell Version Number

Syntax


pwsh -Command "Write-Host((Get-Host).version) "

Output

Output – Image

powerShell.version.20181112.1218PM

Sample #2

Get Username for current user.

Syntax


pwsh -Command "Write-Host( 'Hello ' +  [Environment]::Username) "

Output

Output – Image

helloWorld.20181112.1137AM

 

Summary

Couple of quick points :-

  1. Installation Errors
    • Error :- Repository not available
      • Message
        • No package powershell available.
          Error: Nothing to do
      • Remediation
        • Update Repository – Add Microsoft
  2. In MS Windows, the Powershell executable is powershell.
    In Linux, is is pwsh.

 

 

Keep your laptop running when LID is Closed

Daniel Adeniji CentOS, Linux, Microsoft, MS Windows, Redhat, Technical, Windows 10

Background

There is really not much need to keep a laptop’s lid opened when connected to an external monitor.

OS

Linux

Outline

  1. Launch terminal
  2. sudo to root
  3. edit /etc/systemd/logind.conf
    • Change folder to /etc/systemd
    • Backup current logind.conf
    • Launch editor ( vi) and pass along file name ( logind.conf )
    • Keep current HandleLidSwitch=suspend commented out
    • Add HandleLidSwitch=lock
    • Save Changes
    • Exit Editor
  4. Restart Services
    • Restart systemd-logind.service
      • Command :- systemctl restart systemd-logind.service

 

Images

logind.conf
logind.conf – Original

logind.conf.20181108.0740PM.PNG

logind.conf – Revised

logind.conf.20181108.0746PM.PNG

 

Windows

Outline

  1. Access Control Panel \ Power Options\ System Settings
    • Select “Choose what the Power Buttons do”
    • The “Control Panel\All Control Panel Items\Power Options\System Settings” window appears
      • Access the “Power and sleep buttons and lid settings” group box
      • Focus on “when I close the lid” area
        • There are two options
        • The first been “On Battery” and the other “When plugged in”

Images

Control Panel \ All Control Panel Items \ Power Options \ System Settings

Original

systemSettings.closeLid.20181108.0756PM.PNG

Revised

systemSettings.closeLid.20181108.0758PM.PNG

 

Validation

The steps above were validated on the OSes listed below:

  1. Linux
    • Cent OS
  2. MS Windows
    • Version 10

References

  1. Redhat
    • Products & Services > Product Documentation > Red Hat Enterprise Linux > 7 > Desktop Migration and Administration Guide
      • 13.10. Preventing the Computer from Suspending when Closing the Lid
        Link

Transact SQL and the Null Character

Daniel Adeniji SQL Server, Networking ( SQL Server ), Microsoft, Transact SQL, sys.dm_server_registry

Background

Transact SQL is a very high level language and 99.99% of the time one does not really have to pay attention.

This morning for me was not one of those times that I did not have to be attentive.

Lineage

A quick following up to our last post :-

SQL Server – Transact SQL – Get Network Info
Link

In that post we discussed a couple of options for discovering network metadata per SQL Server Instances.

Query

Original Query

Here is our original query

SQL


select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

Output

sysDOTdm_server_registry__20180607_0918PM

Revised Query – Filter Out Null and Empty Records

In our revised query we will filter out null and empty records

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

Output

sysDOTdm_server_registry__20180607_0918PM

Explanation

Our filter clauses “is Not null” and != ” did not help.

Revised Query – Filter Out Null Character

In our second attempt, we filter out records that simply have the NULL CHARACTER as there whole value

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

and tblDSR.value_data != char(0)

Output

sysDOTdm_server_registry__20180607_0925AM

Explanation

Our filter clause is != char(0) and did discard the unwanted records.

SQL Server – Transact SQL – Get Network Info

Daniel Adeniji Microsoft, Networking ( SQL Server ), SQL Server, sys.dm_exec_connections, sys.dm_tcp_listener_states, Technical, Transact SQL

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG