BitLocker – Configuration – Error – “Unable to find the Reporting Services instance name”

Background

Recently we ran into an “ha ha” moment installing Microsoft BitLocker.

 

BitLocker Administration and Monitoring

Configuring Reports

Here is the “Configuring Reports” window.

ConfigureReports_20180720_1008AM

Error

Error Image

unableToFindTheReportingServicesInstanceNameMSSQLServer

Error Text

Unable to find the Reporting Services instance name <server-name>\MSSQLServer

Trouble Shooting

Reporting Services Configuration Tool ( RSConfigTool )

Launched SQL Server Reporting Services Configuration Tool ( RSConfigTool).

RSConfig_ReportServerStatus_20180720_1033AM.png

Ensured that the service is running and noted the Instance ID.

The Instance ID is SSRS.

Remediation

BitLocker Administration and Monitoring

Configuring Reports

For the instance name, please note the instance name recorded earlier.

That instance name is SSRS.

Configuration SSRSDB.20180720_1041AM

 

 

StackOverflow DB ( 2010 ) – Foreign Keys

Background

Unfortunately, the StackOverflow DB packaged by Brent Ozar and Co does not include indexes and foreign keys.

The premise is that the package is a learning tool and thus encourage the recipient to go in and create useful amenities.

Foreign Key

Our initial effort will be to create Foreign Key Constraints.

Script

Here is the script …


if
     (  object_id('[dbo].[Badges]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Badges_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Badges]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Badges]
        ADD CONSTRAINT [FK_Badges_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Comments]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Comments_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Comments]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Comments]
        ADD CONSTRAINT [FK_Comments_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_PostLinks_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[LinkTypes]')  is not null )
 and  (  object_id('[FK_PostLinks_LinkTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[LinkTypes]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_LinkTypes]
        FOREIGN KEY ([LinkTypeId])
        REFERENCES [dbo].[LinkTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[dbo].[PostTypes]')  is not null )
 and  (  object_id('[FK_Posts_PostTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Posts]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[PostTypes]')
)
begin
    ALTER TABLE [dbo].[Posts]
        ADD CONSTRAINT [FK_Posts_PostTypes]
        FOREIGN KEY ([PostTypeId])
        REFERENCES [dbo].[PostTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_Votes_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Votes_Users]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Users]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[VoteTypes]')  is not null )
 and  (  object_id('[FK_Votes_VoteTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[VoteTypes]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_VoteTypes]
        FOREIGN KEY ([VoteTypeId])
        REFERENCES [dbo].[VoteTypes]
        ([Id])
end

 

Database Model

DBeaver

Original

DBeaver_Diagram_20180719_1117AM

Revised

DatabaseModel_20180720_0950AM_Revised

 

Source Control

GitHub

DanielAdeniji/StackExchangeDB
Link

 

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

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 – Database Files – Minimum File Size

Background

There is a setting that is unfortunately difficult to determine within Transact SQL.

That setting is the smallest size a data or log file can be shrunk down to.

GUI

SQL Server Management Studio ( SSMS )

Database Property

Image

bigfix_Enterprise_initialSizes_20180711_1119AM

Code

Syntax


dbcc page
(
      {'dbname' | dbid}
    , filenum
    , pagenum
    [, printopt={0|1|2|3} ]
)

Explanation

  1. DBCC PAGE
    • Database
    • FileID
    • Pagenum
      • 0
    • printopt
      • 3 – page header plus detailed per-row interpretation

SQL

Data files

Code


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 @tblDBCCPage Table
(

	  [parentObject] sysname not null
	, [object]		 sysname not null
	, [field]		 sysname not null
	, [value]		 varchar(255) null	

	, [computedInKB]
		as case
				when ([field] like '%size%') then
						[value] * 8

				else null
		  end

	, [computedInMB]
		as case
				when ([field] like '%size%') then
						(
							[value] * 8
						)
						/ ( 1024)

				else null
		  end
)

set @FORMAT_SQL = 'DBCC PAGE(''%s'',%s,0,3) with tableresults, no_infomsgs';

set @database = 'BFEnterprise';

set @fileID = 1
set @fileID = 1
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));

if (@fileID is not null)
begin

	DBCC TRACEON(3604) with no_infomsgs;

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

	print @sql

	insert into @tblDBCCPage
	(
		  [parentObject]
		, [object]
		, [field]
		, [value]
	)
	exec(@sql)

	-- exec sp_helpdb [model]
	select
		      [database] = @database
			, [fileSymbolicName] = @fileSymbolicName
			, [filePhysicalName] = @filePhysicalName
			, [parentObject]
			, [object]
			, [field]
			, [value]
			, [computedInKB]
			, [computedInMB]

	from   @tblDBCCPage tbl

	where  (
			( field in
				(
					'MinSize'
					, 'Size'
				)
			)

	DBCC TRACEOFF(3604) with no_infomsgs

end -- if (@fileID is not null)

Output

bigfix_Enterprise_Code_20180711_1130AM.png

Log File

Explanation

Unfortunately, same does not work for log files.

You will get the error pasted below.

Output


DBCC PAGE('BFEnterprise',2,0,3) with tableresults, no_infomsgs
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1585190943824412672 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF-&gt;bstat)) failed. Values are 2057 and -1.
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13792724832485376 (type Unknown), page ID (2:3) contains an incorrect page ID in its page header. The PageId in the page header = (102:7274528).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 12947986374328320 (type Unknown), page ID (2:6) contains an incorrect page ID in its page header. The PageId in the page header = (117:7077985).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9007714657435648 (type Unknown), page ID (2:7) contains an incorrect page ID in its page header. The PageId in the page header = (32:7536756).
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7036874954637312 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF-&gt;bstat)) failed. Values are 2057 and -14.

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Crediting

Edward Dortland
File Header Properties
Link

Summary

Unfortunately, the word size is imprecise and thus need to be surrounded to facilitate context.

When Database properties are viewed through SSMS, for each data and log file, there is a number identified as “Initial Size“.

I think one has to be careful and read more into what it means and how it is sourced.

References

  1. Edward Dortland
    • Stack Overflow
      • File Header Properties
        Link
  2. Paul Randal
    • Server & Tools Blogs > Data Platform Blogs > SQL Database Engine Blog
      • How to use DBCC PAGE
        Link