Infrastructure at your Service

Nathan Courtine

The (almost) same sample schema for all major relational databases (4) – MSSQL

In previous posts, my colleague Daniel Westermann explained how to install the “Dell DVD Store Database Test Suite” into PostgreSQL, Oracle and MySQL.

This time, this is my job to present you how to proceed with a SQL Server instance 😉

I will proceed this installation on SQL Server 2014 running under Windows Server 2012 R2. To be coherent with the other RDBMS examples, all the manipulation will be made on Windows core. Yes, it is possible to deal with Windows only in command lines 🙂

 

Please download the ds21_sqlserver.tar.gz and ds21.tar.gz archives. In my example, I extracted the ds2 archives on “C:\”. The extraction create a ds2 folder, where you find the following items:

PS C:\> ls ds2

    Directory: C:\ds2

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        13.04.2016     10:21            data_files
d----        13.04.2016     10:21            drivers
d----        13.04.2016     11:19            sqlserverds2
-a---        12.08.2010     17:08       5308 CreateConfigFile.pl
-a---        13.05.2011     04:43      30343 ds2.1_Documentation.txt
-a---        09.11.2011     12:48      10103 ds2_change_log.txt
-a---        01.07.2005     04:21       1608 ds2_faq.txt
-a---        05.05.2011     00:25       2363 ds2_readme.txt
-a---        21.04.2011     03:12       5857 ds2_schema.txt
-a---        12.05.2005     20:38      18013 gpl.txt
-a---        09.11.2011     12:45      32827 Install_DVDStore.pl

If you want to obtain more information, a detailed description is available in “C:\ds2\ds2.1_Documentation.txt” file.

I recommend you to follow the paths and naming used in this blog, otherwise you must change the different scripts to adapt your own configuration.

First, create the following path: “C:\sql\dbfiles\”. Then, open and edit the script located at “C:\ds2\”:

PS C:\ds2\sqlserverds2> notepad.exe .\sqlserverds2_create_all_small.sql
PS C:\ds2\sqlserverds2>

Choose a password for the login “ds2user” created in the script:

ds2user_password

Save and close the edited file.

As ds2 use Perl scripts, Perl must be installed on the server. Simply download the free Community Edition from ActiveState website, and run the installer.

After Perl is installed on the server, run the “InstallDVDStore.pl” script located at “C:\ds2\sqlserverds2”:

PS C:\ds2> .\Install_DVDStore.pl
PS C:\ds2>

It will ask for 5 different inputs:

  • An integer for the size
  • The unit of the size: MB or GB
  • The RDBMS type: SQL Server (mssql), MySQL (mysql), Oracle (oracle) or PostgreSQL (PGSQL)
  • The OS type: Windows (win) or Linux (linux)
  • The folder path of the database files: “C:\sql\dbfiles”

Install_DVDStore

Then execute the “C:\ds2\CreateConfigFile.pl” script:

CreateConfigFile

Now run this script on the SQL Server instance as follows (in my example, the SQL Server instance is named “SQL2014”. Having a different name does not have any impact here):

PS C:\ds2\sqlserverds2> Invoke-Sqlcmd -ServerInstance ".\SQL2014" -InputFile ".\sqlserverds2_create_all_small.sql"

You can ignore the errors: indeed, it is just some data which have been truncated from the original CSV file which is provided.

 

What did happen on my instance?

 

PS SQLSERVER:\> Invoke-Sqlcmd -ServerInstance ".\SQL2014" -Database "DS2" -Username 'ds2user' -Password 'myPa$$w0rd' -Query 'SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS'

TABLE_NAME                    TABLE_SCHEMA                  COLUMN_NAME                   DATA_TYPE
----------                    ------------                  -----------                   ---------
CUSTOMERS                     dbo                           CUSTOMERID                    int
CUSTOMERS                     dbo                           FIRSTNAME                     varchar
CUSTOMERS                     dbo                           LASTNAME                      varchar
CUSTOMERS                     dbo                           ADDRESS1                      varchar
CUSTOMERS                     dbo                           ADDRESS2                      varchar
CUSTOMERS                     dbo                           CITY                          varchar
CUSTOMERS                     dbo                           STATE                         varchar
CUSTOMERS                     dbo                           ZIP                           int
CUSTOMERS                     dbo                           COUNTRY                       varchar
CUSTOMERS                     dbo                           REGION                        tinyint
CUSTOMERS                     dbo                           EMAIL                         varchar
CUSTOMERS                     dbo                           PHONE                         varchar
CUSTOMERS                     dbo                           CREDITCARDTYPE                tinyint
CUSTOMERS                     dbo                           CREDITCARD                    varchar
CUSTOMERS                     dbo                           CREDITCARDEXPIRATION          varchar
CUSTOMERS                     dbo                           USERNAME                      varchar
CUSTOMERS                     dbo                           PASSWORD                      varchar
CUSTOMERS                     dbo                           AGE                           tinyint
CUSTOMERS                     dbo                           INCOME                        int
CUSTOMERS                     dbo                           GENDER                        varchar
CUST_HIST                     dbo                           CUSTOMERID                    int
CUST_HIST                     dbo                           ORDERID                       int
CUST_HIST                     dbo                           PROD_ID                       int
ORDERS                        dbo                           ORDERID                       int
ORDERS                        dbo                           ORDERDATE                     datetime
ORDERS                        dbo                           CUSTOMERID                    int
ORDERS                        dbo                           NETAMOUNT                     money
ORDERS                        dbo                           TAX                           money
ORDERS                        dbo                           TOTALAMOUNT                   money
ORDERLINES                    dbo                           ORDERLINEID                   smallint
ORDERLINES                    dbo                           ORDERID                       int
ORDERLINES                    dbo                           PROD_ID                       int
ORDERLINES                    dbo                           QUANTITY                      smallint
ORDERLINES                    dbo                           ORDERDATE                     datetime
PRODUCTS                      dbo                           PROD_ID                       int
PRODUCTS                      dbo                           CATEGORY                      tinyint
PRODUCTS                      dbo                           TITLE                         varchar
PRODUCTS                      dbo                           ACTOR                         varchar
PRODUCTS                      dbo                           PRICE                         money
PRODUCTS                      dbo                           SPECIAL                       tinyint
PRODUCTS                      dbo                           COMMON_PROD_ID                int
INVENTORY                     dbo                           PROD_ID                       int
INVENTORY                     dbo                           QUAN_IN_STOCK                 int
INVENTORY                     dbo                           SALES                         int
CATEGORIES                    dbo                           CATEGORY                      tinyint
CATEGORIES                    dbo                           CATEGORYNAME                  varchar
REORDER                       dbo                           PROD_ID                       int
REORDER                       dbo                           DATE_LOW                      datetime
REORDER                       dbo                           QUAN_LOW                      int
REORDER                       dbo                           DATE_REORDERED                datetime
REORDER                       dbo                           QUAN_REORDERED                int
REORDER                       dbo                           DATE_EXPECTED                 datetime


PS SQLSERVER:\>

 

PS SQLSERVER:\> Invoke-Sqlcmd -ServerInstance ".\SQL2014" -Database "DS2" -Username 'ds2user' -Password 'myPa$$w0rd' -Qu
ery 'SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS'

CONSTRAINT_SCHEMA             CONSTRAINT_NAME               CONSTRAINT_TYPE               TABLE_NAME
-----------------             ---------------               ---------------               ----------
dbo                           PK_CATEGORIES                 PRIMARY KEY                   CATEGORIES
dbo                           PK_CUSTOMERS                  PRIMARY KEY                   CUSTOMERS
dbo                           PK_ORDERS                     PRIMARY KEY                   ORDERS
dbo                           PK_ORDERLINES                 PRIMARY KEY                   ORDERLINES
dbo                           FK_ORDERID                    FOREIGN KEY                   ORDERLINES
dbo                           PK_INVENTORY                  PRIMARY KEY                   INVENTORY
dbo                           PK_PRODUCTS                   PRIMARY KEY                   PRODUCTS


PS SQLSERVER:\>

Regarding the size of the database:

PS SQLSERVER:\> Invoke-Sqlcmd -ServerInstance ".\SQL2014" -Database "DS2" -Username 'ds2user' -Password 'myPa$$w0rd' -Query 'SELECT SUM(size/128) AS Size_MB from sys.database_files'

Size_MB
-------
   1019


PS SQLSERVER:\>

As you can see, we are close to 1000 MB 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nathan Courtine
Nathan Courtine

Senior Consultant