Foreign Key Constraints – SSMS

Background

Having restored Stack Overflow’s Database version 2008-2010, reviewing the tables and their relationships.

BTW, that post is here.

 

Database Diagram

DBeaver

Original

Image

 

Explanation

  1. No Relationship

 

Create Foreign Key

SSMS

Objective

Let us relate the dbo.Badges table to the dbo.Users table.

The correlating columns are dbo.Badges.UserId and dbo.Users.Id.

Outline

  1. Launch SSMS
  2. Connect to the SQL Server Instance
  3. Choose the database
  4. Select the table that will we will be targeting
    • Access the Keys node
    • Right click on the Keys Node
    • From the drop-down menu, Select “new Foreign-Key…” option
  5. “Foreign Key relationships” window
    • The “Foreign Key relationships” window appears
    • Group Area :- Tables and Column specifications
      • Please zero in on “Tables and Column specifications” group area
      • Click the eclipse ( “…”) button
  6. Tables and Column Window
    • The “Table and Column” window appears
    • Both sides ( Primary and Foreign Key ) show our targeted table
    • Primary
      • Please adjust our primary, the left side as stated below
        • Table Name
          • Users
        • Column Names
          • Id
  7. Exit Designer Window
    • Please press OK as many times as possible to exit the table designer windows
  8. Generate Change Script
    • Please access the menu items ( Table Designer, Generate Change Script )
  9. Save Change Script
    • The “Save Change Script” window appears
  10. Review Generated Script

Images

New Foreign Key

Foreign Key relationships
Display – Original

The original window shows a self relationship, the Badges table related to the Badges table.

 

Tables and Columns
Display – Original

Display – Revised

We want to state that our targeted table is dbo.Badges and the referenced table is dbo.Users.

The columns that participate from the Badges table is UserId.  And, the Id column in the Users table.

Foreign Key Relationships
Display – Revised

Generate Change Script
Save Change Script

 

Database Diagram

Microsoft

SSMS

Image

databaseDiagram_20180719_0453PM

Stack-overflow Database ( Years 2008 through 2010 )

Background

As always looking for sample code and databases.

This time it is a sample database.

Occasionally, new users ask a Database Administrator for learning tools and materials.

 

Stack Exchange

Stack Exchange, the parent company for Stack Overflow, thankfully avails their database has XML files.

Brent Ozar and Co avails the XML files as Microsoft SQL Server Data & log files.

 

Download

Read about downloading the 10 GB data which covers years 2008 thru 2010 from here.

The actual file is  here. And, it is packaged as a 1 GB compressed file.

 

Extract

Extract using 7-Zip.

 

Attach to SQL Server Instance

Launch SSMS and attach data and log file using something like this.

SQL


USE [master]
GO

CREATE DATABASE [StackOverflow2010]
ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Datafiles\StackExchange\Stackoverflow\Stackoverflow.2010\StackOverflow2010.mdf'
)
LOG ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Logfiles\StackExchange\Stackoverflow\Stackoverflow2010\StackOverflow2010_log.ldf'
)

FOR ATTACH

GO

 

Output

 

Converting database 'StackOverflow2010' from version 655 to the current version 869.
Database 'StackOverflow2010' running the upgrade step from version 655 to version 668.
...
...
Database 'StackOverflow2010' running the upgrade step from version 866 to version 867.
Database 'StackOverflow2010' running the upgrade step from version 867 to version 868.
Database 'StackOverflow2010' running the upgrade step from version 868 to version 869. 

Explanation

The version of the packaged database is v2008.

When attached to a later model SQL Server, it is upgraded to that later version.

That explains the output entries “upgrade step from version [m] to version [n].

 

Create Indexes

Based on your query needs please create indexes

Here are targeted indexes for our lone test query

Table – [dbo].[Votes]

Index – [dbo].[Votes].[INDX_UserId_BountyAmount]


use [StackOverflow2010]
go

create index [INDX_UserId_BountyAmount]
on [dbo].[Votes]
(
	  [UserId] asc
	, [BountyAmount] desc
)
with
(
	  FillFactor=100
	, DROP_EXISTING=OFF
)

 

Sample Queries

Query – Users with highest bounties

Query


set transaction isolation level read uncommitted
go

use [StackOverflow2010]
go

; with [cteBountyAmount]
as
(
	select top 15
			  tblV.[UserId]
			, [BountyAmount]
				= sum(tblV.[BountyAmount])

	from [dbo].[Votes] tblV

	group by
		tblV.[UserId]

	order by
		sum(tblV.[BountyAmount]) desc

)

select
		  [UserId]
			= tblU.[Id]

		, [name]
			= tblU.DisplayName

		, [url]
			= tblU.WebsiteUrl

		, [bountyAmount]
			= cteBA.[BountyAmount]

from   [dbo].[Users] tblU

inner join [cteBountyAmount] cteBA

	on tblU.[Id] = cteBA.UserId

order by
	cteBA.[BountyAmount] desc

 

Output

Bigfix – Error – “The specified target is unknown or unreachable”

Background

Trying to configure our BigFix Application Server, but running into the error pasted below.

Error

Image

failedToConnectTheSpecificTargetIsUnknown_20180713_0400PM [BrushedUp

Textual


Failed to connect to https://bigfixsrv:52311:
Windows Error 0x80090303

Remediation

Network

netstat

Command


netstat -an | find "52311"

Explanation

Noticed network connections on our Application’s port ( 52311).

Active Directory

Service Principal Name ( SPN )

setspn

Overview

SPN is formatted as such :-

serviceclass/host:port servicename

Here is a quick breakdown of each element

  1. Serviceclass
    • For Bigfix, the service class registered is iem
  2. Host
    • The computer name
  3. Port
    • If other than the default port number for the application
  4. Service Name
    • It our case it will be the Active Domain Account that our service will be running under

 

Syntax
Syntax – List

REM List SPN entries for computer
setspn -L [computer]

Syntax – Add

REM Add iem account for computer
setspn -S iem/[computer] [user]

Sample

setlocal

set "_comp=BIGFIXSRV"
set "_compFQDN=BIGFIXSRV.LAB"
set "_user=LAB\servicebf"

REM Remove Previous iem account ( Computer )
setspn -D iem/%_comp% %_comp%

REM Remove Previous iem account ( FQDN )
setspn -D iem/%_compFQDN% %_comp%

REM Add iem account (Computer) 
setspn -S iem/%_comp%  %_user%

REM Add iem account  ( FQDN )
setspn -S iem/%_compFQDN% %_user%

REM List spn for computer
setspn -l %_comp%

REM List spn for user
setspn -l %_user%


endlocal

References

  1. IBM
    • Support
      • Pass-Through Authentication failing
        Link
    • IBM Knowledge Center \ SSPS Modeler  \ Configure Single Sign-On
      • The Service Principal Name
        Link
  2. Microsoft
    • Setspn
  3. Viruk67 – You Want It When!
    • Setting the NetworkService as an SPN account
      Link

SQL Server – System populated columns

 

Background

Thankfully in SQL Server there are some column decorators that specify that the system should auto-populate the column for us.

Recently, I needed to have a quick list of which columns those are.

SQL

Here is a working code that I will come back and tighten up:

Code


select 
          [table] = 
                      quotename(tblSS.name)
                    + '.'
                    + quotename(tblSO.name)

        , [column] = tblSC.name
        
        , [dataType] = tblST.name
        
        , [citationPremise]
            = case

                when (tblSC.[is_identity] =1) 
					then 'Identity'

                when (tblSC.[is_computed] =1) 
					then 'Computed'

                when (tblST.[name] = 'timestamp') 
					then 'Timestamp'

              end

from   sys.objects tblSO

inner join sys.schemas tblSS

    on tblSO.schema_id = tblSS.schema_id

inner join sys.columns tblSC

    on tblSO.object_id = tblSC.object_id

inner join sys.types tblST

    on  tblSC.system_type_id = tblST.system_type_id
    and tblSC.user_type_id = tblST.user_type_id

where tblSO.is_ms_shipped = 0

and tblSO.[type] = 'U'

and   (

                ( tblSC.[is_identity] = 1)
            or  ( tblSC.[is_computed] = 1)
            or  ( [tblST].[name]
                    in
                        (
                            'timestamp'		
                        )	
                )
      )

order by
          [table] asc
        , [column] asc

Output

SQLServerAutoPopulated_Bigfix_BESReporting_20180713_1056AM

SQL Server – Database Files – Minimum File Size Via “DBCC FILEHEADER”

Background

In our last post, we spoke of using “DBCC PageHeader” to fetch minimum file sizes.

And, as closing, we touched on the fact that the code pasted does not work for log files, just data files.

Code

Syntax


dbcc fileheader
(
      {'dbname' | dbid}
    , filenum

)

Explanation

  1. DBCC FileHeader
    • Database
    • FileID

SQL


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblFileHeader Table
(

      [RecoveryUnitID]			smallint
    , [FileId]					smallint
    , [LogicalName]				sysname
    , [BindingID]				uniqueidentifier
    , [FileGroup]				smallint

    , [Size]					bigint
    , [MaxSize]					bigint
    , [MinSize]					bigint
    , [UserShrinkSize]			varchar(255)

    , [Growth]					varchar(255)

    , [BackupLSN]				decimal(38, 0)
    , [RedoStartLSN]			decimal(38, 0)
    , [FirstLSN]				decimal(38, 0)
    , [MaxLSN]					decimal(38, 0)
    , [FirstUpdateLSN]			decimal(38, 0)
    , [CreateLSN]				decimal(38, 0)

    , [SectorSize]				smallint
    , [ActualSectorSize]		smallint

    , [RecoveryForkGUID]		uniqueIdentifier
    , [RecoveryForkLSN]			decimal(38, 0)

    , [DifferentialBaseLSN]		decimal(38, 0)
    , [DifferentialBaseGuid]	uniqueidentifier

    , [Status]					smallint
    , [RestoreStatus]			smallint

    , [ReadOnlyLsn]				decimal(38, 0)
    , [ReadWriteLsn]			decimal(38, 0)
    , [MaxLsnBranchId]			uniqueIdentifier

    , [RedoTargetPointLsn]		decimal(38, 0)
    , [RedoTargetPointGuid]		uniqueIdentifier

    , [RestoreDiffBaseLsn]		decimal(38, 0)
    , [RestoreDiffBaseGuid]		uniqueIdentifier

    , [RestorePathOriginLsn]	decimal(38, 0)
    , [RestorePathOriginGuid]	uniqueIdentifier

    , [OldestRestoreLsn]		decimal(38, 0)

    , [sizeInKB]
        as (
                [Size] * 8
            )

    , [sizeInMB]
        as
             (
                [Size] * 8
             )
             / ( 1024)

    , [MinSizeInMB]
        as
             (
                [MinSize] * 8
             )
             / ( 1024)

)

set @FORMAT_SQL = 'DBCC FILEHEADER(''%s'',%s) with tableresults, no_infomsgs';

-- exec sp_helpdb 'skySync'
set @database = 'skySync';

set @fileID = 1
set @fileID = 2

set @dbid = db_id(@database);

select
          @fileSymbolicName = tblSMF.[name]
        , @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

exec master..xp_sprintf
            @sql output
        , @FORMAT_SQL
        , @database
        , @fileIDAsString

DBCC TRACEON(3604) with no_infomsgs;

print @sql

insert into @tblFileHeader
exec(@sql)

-- exec sp_helpdb [model]
select
          [database] = @database
        , [fileSymbolicName] = @fileSymbolicName
        , [filePhysicalName] = @filePhysicalName

        , tblFH.[Size]
        , tblFH.[SizeInMB]

        , tblFH.[MinSize]
        , tblFH.[MinSizeInMB]

from   @tblFileHeader tblFH

DBCC TRACEOFF(3604) with no_infomsgs

Output

skySync_20180711_0140PM

Credits

Crediting sqllity

DBCC FILEHEADER: Reading the File Header Page
Link

SQL Server – BulkCopy ( BCP ) – Which data file?

Background

Using BCP, we are churning though quite a bit of files.

Unfortunately, the DOS batch file that I quickly stitched together missed an importantly functionality.

And, that functionality is to log the current file being processed.

Diagnostic

Microsoft

Resource Monitor

I am a big fan of Microsoft’s Resource Monitor.

Let us use it.

Preparation

We remote connect to the source computer and launched task Manager; from Task Manager accessed Resource Monitor.

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_CPU_ProcessesAndAssociatedHandles_20180706_0850AM.png

Explanation
  1. When we filter on the bcp.exe process, we see our data file as one of the files mentioned in the “Associated Handles” tab

 

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_Memory_20180706_0852AM.png

Explanation
  1. The BCP process is using about 14 MB

 

Resource Monitor – Tab – Disk

Image

ResourceMonitor_Tab_Disk_20180706_0907AM.png

Explanation
  1. sqlserver,exe is mentioned
  2. But, not our bcp.exe file

 

Summary

To get a reliable insight into which files are being accessed, please consider Microsoft’s own Resource Monitor; specifically the CPU tab.

 

 

Error: “The requested operation could not be completed due to a file system limitation (mscorlib”

Background

Experiencing an error when trying to run an embedded process.  Part of the functionality that is bundled in the process, is a file copying module.

Error Message

Here is the error message :-

*** Error: The requested operation could not be completed due to a file system limitation (mscorlib) ***

Trouble Shooting

Application Message

Thankfully the particular file that is copied is logged, as well.


Copying log backup file to temporary work file. Source: '\\LABDB\Backup\hrdb_20180703104507.trn', Destination: '\\LABMirror\backup\HRDB_20180703104507.wrk'

Fast Copy

Attempted same file copy in FastCopy.

And, again thankfully it reported an error message; along with an error number.

Image

FastCopy_20180705_0712PM_v2.png

Text


WriteFileWait(The requested operation could not be completed due to a file system limitation665) : \\LABMirror\backup\HRDB_20180703104507.wrk
TotalRead  = 40,414 MB
TotalWrite = 40,289 MB
TotalFiles = 0 (0)
TotalTime  = 26:50
TransRate  = 25.0 MB/s
FileRate   = 0.00 files/s 

Explanation

  1. Error
    • Error Operation :- WriteFileWait
    • Error Description :- The requested operation could not be completed due to a file system limitation
    • Error Number :- 665

 

Error Causation

There are a few probably causes for error number 665.

Compression?

File

Finally, we checked the compression setting on others files in the targeted folder.

File Property

Image

file_20180706020000_trn_brushedup.png

Textual
  1. Size :- 2.07 GB
  2. Size on Disk :- 760 MB
Explanation

We can see that our file is compressed.

Folder Settings

Reviewed the Folder’s Advanced Attributes.

Image

AdvancedSettings_20180705_0717PM.png

Explanation

  1. Compress Contents to save disk space ( is enabled )

 

Remediation

Compression

Setting

We turned off compression on the targeted folder.

Fast Copy

Retried Fast Copy and it worked successfully.

NoError__20180705_1014PM_v2.png

 

Summary

Wished the original application displayed the OS’s error number and not just a simple error text that read “Error: The requested operation could not be completed due to a file system limitation (mscorlib)“.

Thankfully, there was enough diagnostic data in regards to the original file and destination folder that allowed us to replay via a more illuminating tool.