World Wide Importers – Using On SQL Server 2014

Background

A few posts back, spoke to how we took beginning steps towards using Microsoft Sample database, AdventureWorks, for playing around with Column Indexes.

During that exercise found out that MSFT has a new database, World Wide Importers.

That new database is targeted for SQL Server v2016 and beyond.

 

World Wide Importers

Download

GitHub

The scripts for World Wide Importers are available here.

Image

 

Image – DW

The choices for Data Warehouse are the DW files.

And, here they are:

Explanation

  1. SQL Server Version
    • Enterprise/Developer
      • WideWorldImportersDW-Full.bacpac
      • WideWorldImportersDW-Full.bak
    • Standard
      • WideWorldImportersDW-Standard.bacpac
      • WideWorldImportersDW-Standard.bak

 

Will not deal with the steps to use bacpac files in this post.

Depending on your targeted SQL Server Instance, if Standard or your OS bit is 32-bit then please go with WideWorldImportersDW-Standard.bak.

If your SQL instance is not Standard and it is 64-bit, please choose WideWorldImportersDW-Full.bak.

Restore Backup File

Restore backup to SQL Server Instance v2016 or later.

There are two database files,WideWorldImportersDW-Standard.bak and WideWorldImportersDW-Full.bak.

The WideWorldImportersDW-Full.bak file has an in-memory component.

If you are running 2016 SP1 or 2016 Enterprise Edition, you can go with the WideWorldImportersDW-Full.bak file.
Else, you will likely have to go with WideWorldImportersDW-Standard.bak.

In the script below, we have sample scripts for both options.

Script

Standard


USE [master]
go

exec master.dbo.xp_create_subdir 'Z:\Microsoft\SQLServer\DataFiles\'
exec master.dbo.xp_create_subdir 'E:\APPS\Microsoft\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log'
go

if db_id('WideWorldImportersDW') is not null
begin

	ALTER DATABASE [WideWorldImportersDW] 
		set AUTO_UPDATE_STATISTICS_ASYNC OFF
		with ROLLBACK IMMEDIATE
		;


	ALTER DATABASE [WideWorldImportersDW] 
		set SINGLE_USER
			with ROLLBACK IMMEDIATE
		;

end
go



RESTORE DATABASE [WideWorldImportersDW] 
FROM  DISK = N'Z:\Microsoft\SQLServer\v2016\WideWorldImportersDW\Enterprise\WideWorldImportersDW-Full.bak' 
WITH  FILE = 1
   ,  MOVE N'WWI_Primary' TO N'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW.mdf'
   ,  MOVE N'WWI_UserData' TO N'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_UserData.ndf'
   ,  MOVE N'WWI_Log' TO N'E:\APPS\Microsoft\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\WideWorldImportersDW.ldf'


GO

 

Enterprise


USE [master]
go

exec master.dbo.xp_create_subdir 'Z:\Microsoft\SQLServer\DataFiles\'
exec master.dbo.xp_create_subdir 'E:\APPS\Microsoft\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log'
go

if db_id('WideWorldImportersDW') is not null
begin

	ALTER DATABASE [WideWorldImportersDW] 
		set AUTO_UPDATE_STATISTICS_ASYNC OFF
		with ROLLBACK IMMEDIATE
		;


	ALTER DATABASE [WideWorldImportersDW] 
		set SINGLE_USER
			with ROLLBACK IMMEDIATE
		;

end
go



RESTORE DATABASE [WideWorldImportersDW] 
FROM  DISK = N'Z:\Microsoft\SQLServer\v2016\WideWorldImportersDW\Enterprise\WideWorldImportersDW-Full.bak' 
WITH  FILE = 1
		,  MOVE N'WWI_Primary' TO N'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW.mdf'
		,  MOVE N'WWI_UserData' TO N'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_UserData.ndf'
		,  MOVE N'WWI_Log' TO N'E:\APPS\Microsoft\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\WideWorldImportersDW.ldf'
		,  MOVE N'WWIDW_InMemory_Data_1' TO N'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_InMemory_Data_1'
		,  NOUNLOAD
		,  STATS = 1
		,  REPLACE

GO


 

Script Objects And Data

Using SQL Server Management Studio (SSMS) connect to the 2016 Instance and script out the restored database.

Scripting Options

Here are the scripting options that we chose:

Option value
Append To File False
Check for object existence True
Continue Scripting on Error False
Script Drop and create Script Create
Script for Server Version 2014
Script for database engine type Standalone
Azure
 Script Statistics  Script Statistics and Histograms
 Type of data to script  Schema and data
 Script Foreign Keys  True
 Script Full-Text Indexes  True
 Script Indexes  True
 Script Primary Keys  True
 Script Triggers  True
 Script Unique Keys  True

Images

Introduction

Choose Objects

Set Scripting Options

  1. Output Type
    • Save scripts to a specified location
  2. Save to file
    • File to generate
      • Single file

 

Summary

 

Save or Publish Scripts

Build and Execute DB Creation Script

Connect to the 2014 Instance and prepare a consolidated file.

wrapperTop.sql

  1. Drop database, if it exists
  2. Create database in specific folders
  3. If Instance supports In-Memory, please add In-Memory datafile

 


USE [master]
go

if db_id('WideWorldImportersDW') is not null
begin

	
	ALTER DATABASE [WideWorldImportersDW] 
		set AUTO_UPDATE_STATISTICS_ASYNC OFF
		with ROLLBACK IMMEDIATE
		;


	ALTER DATABASE [WideWorldImportersDW] 
		set SINGLE_USER
			with ROLLBACK IMMEDIATE
		;

	print 'Drop database [WideWorldImportersDW] in wrapperTop.sql...'

	DROP DATABASE [WideWorldImportersDW];
	
	print 'Dropped database [WideWorldImportersDW] in wrapperTop.sql'
	
end
go

DECLARE @IsXTPSupported varchar(1)

set @IsXTPSupported =  cast(SERVERPROPERTY(N'IsXTPSupported') as varchar(1))

print '@IsXTPSupported ' + cast(@IsXTPSupported as varchar(10))

IF NOT EXISTS 
(
	SELECT name 
	FROM sys.databases 
	WHERE name = N'WideWorldImportersDW'
)
BEGIN

	print 'Create database [WideWorldImportersDW] in wrapperTop.sql'

	exec master.dbo.xp_create_subdir 'C:\Microsoft\SQLServer\DataFiles\'
	exec master.dbo.xp_create_subdir 'C:\Microsoft\SQLServer\LogFiles\'
	
	CREATE DATABASE [WideWorldImportersDW]

		CONTAINMENT = NONE
		ON  PRIMARY 

	( 
			NAME = N'WWI_Primary'
		, FILENAME = N'C:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW.mdf' 
		, SIZE = 100MB 
		, MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
	)
	, FILEGROUP [USERDATA]  DEFAULT
	( 
			NAME = N'WWI_UserData'
		, FILENAME = N'C:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_UserData.ndf' 
		, SIZE = 100MB 
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 65536KB 
	)
		LOG ON 
	( 
			NAME = N'WWI_Log'
		, FILENAME = N'C:\Microsoft\SQLServer\LogFiles\WideWorldImportersDW.ldf' 
		, SIZE = 100MB 
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 65536KB 
	)

		
	if (@IsXTPSupported = 0)	
	begin
	
		print'Skip Adding In Memory data file'
		set noexec on
	
	end
	else if (@IsXTPSupported = 1)
	begin
	
		print 'Adding WWIDW_InMemory_Data to [WideWorldImportersDW] in wrapperTop.sql'

		exec
		('
			ALTER DATABASE [WideWorldImportersDW]
			ADD FILEGROUP [WWIDW_InMemory_Data] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
			( 
				  NAME = N''WWIDW_InMemory_Data_1''
				, FILENAME = N''C:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_InMemory_Data_1'' 
				, MAXSIZE = UNLIMITED
			)
			
		')
				
		print 'Added WWIDW_InMemory_Data to [WideWorldImportersDW] in wrapperTop.sql'
	
	end
	
	set noexec off
	
	
END

GO


alter database [WideWorldImportersDW] set recovery simple;
go


set nocount on;
set XACT_ABORT on;
go


getTimeStamp.cmd


@echo off
@setlocal enableextensions enabledelayedexpansion
 
rem Creating a file name as a timestamp in a batch job
rem BeConcise
rem http://stackoverflow.com/questions/1064557/creating-a-file-name-as-a-timestamp-in-a-batch-job
rem copy "{path}\example.txt" "{path}\_%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%.txt"
rem set "_timestampSaved=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%"
 
rem Need leading zero for batch script using %time% variable
rem http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable
 
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%
  
(
    endlocal
 
    rem SET _timestamp=%_timestamp%
    SET _timestampSaved=%_timestamp%
)

Submit.cmd

  1. Concatenate header file and generated file
  2. Apply concatenated file to SQL Instance

Code


@ECHO OFF
set _DATEANDTIME=
set _DATEANDTIME_INTERNAL=
set _timestampSaved=

REM Capture Current Folder
REM By Raymond Chen - MSFT https://blogs.msdn.microsoft.com/oldnewthing/20050128-00/?p=36573
set "_dir=%CD%"

set "_textToFind=DROP INDEX  IF EXISTS"
set "_textToReplaceWith=DROP INDEX " 


set "_infile=WideWorldImportersDW.sql"
set "_infileFull=WideWorldImportersDW_full.sql"

set "_SQLInstance=.\v2014"

REM Christian.K - How to stop the “Changed database context to …” message
REM https://stackoverflow.com/questions/10412673/how-to-stop-the-changed-database-context-to-message
set "_SQLCMD_OPTIONS= -m 1"

REM Get Current Timestamp
call getTimeStamp.cmd


set "_logFileName=log\sqlcmd_%_timestampSaved%.log"

REM Display Log Filename
echo Log File Name :- %_logFileName%


rem goto textReplace

REM Display Start Date and Time
time /t

REM Delete Concatenated SQL File ( if exists)
if exist %_infileFull% del %_infileFull%

REM Concatenate SQL Files
TYPE wrapperTop.sql > %_infileFull%
TYPE %_infile% >> %_infileFull%
TYPE wrapperBottom.sql >> %_infileFull%

:textReplace
REM Replace "DROP INDEX IF EXISTS " with "DROP INDEX"
ECHO Replacing Text in SQL Input File
fnr.exe --cl --find "%_textToFind%" --replace "%_textToReplaceWith%" --dir "%_dir%" --fileMask %_infileFull%

REM Create Log Folder
if not exist log mkdir log 

time /t

REM Run Generated SQL Script against Database Server
sqlcmd -S %_SQLInstance% -d tempdb -i %_infileFull%   -o %_logFileName% 

REM Display End Date and Time
time /t

:eof


Output

Image

Textual

Changed database context to 'master'.
Drop database [WideWorldImportersDW] in wrapperTop.sql...
Dropped database [WideWorldImportersDW] in wrapperTop.sql
@IsXTPSupported 0
Create database [WideWorldImportersDW] in wrapperTop.sql
Skip Adding In Memory data file
The module 'Configuration_ConfigureForEnterpriseEdition' depends on the missing object 'Application.Configuration_EnableInMemory'. The module will still be created; however, it cannot run successfully until the object exists.
Msg 156, Level 15, State 1, Server ITS-DADENIJI-9\V2014, Procedure Configuration_PopulateLargeSaleTable, Line 47
Incorrect syntax near the keyword 'IF'.
The module 'ReseedAllSequences' depends on the missing object 'Sequences.ReseedSequenceBeyondTableValues'.
The provided statistics stream is corrupt.
Msg 9105, Level 16, State 106, Server ITS-DADENIJI-9\V2014, Line 3
The provided statistics stream is corrupt.

Explanation
  1. Error Messages
    • Procedure Configuration_PopulateLargeSaleTable
      • Incorrect Syntax near the keyword ‘IF’ ( Msg 156 )
    • Table Statistics
      • The provided statistics stream is corrupt ( Msg 9105 )

 

Source Code Version Control

GitHub

Posted to GitHub:

DanielAdeniji/wideWorldImportersV2014
Link

 

Filename Usage
WideWorldImportersDW.sql.zip General WideWorldImportersDW.sql ( compressed as zip file )
_getTimeStamp.cmd MS Windows Batch file for getting current Time Stamp
fnr.exe Find and Replace File
submitSQL.cmd Submit SQL file to SQL Server
wrapperFooter.sql Footer for SQL File
wrapperHeader.sql Header for SQL File

Summary

Will come back and do the following

  1. Cite the warnings and error messages that we captured when we applied the generated SQL against the SQL Server Instance
  2. Review for accuracy the database we just created.

 

2 thoughts on “World Wide Importers – Using On SQL Server 2014

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s