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:
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”
Then execute the “C:\ds2\CreateConfigFile.pl” script:
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 🙂
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/NAC_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)