Skip to content

Commit 98f7086

Browse files
jodebruijodebrui
committed
Merge pull request microsoft#38 from Microsoft/jodebrui
wide-world-importers sample source code
2 parents 6f2a9bd + 4c01fe4 commit 98f7086

File tree

91 files changed

+92944
-1
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

91 files changed

+92944
-1
lines changed

‎media/wide-world-importers-etl-workflow.png

70.2 KB
Loading

‎media/wide-world-importers-order-insert-app.png

44.4 KB
Loading

‎media/wide-world-importers-vehicle-location-insert-app.png

37.5 KB
Loading

‎samples/databases/README.md

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,10 @@
22

33
Contains samples databases for Microsoft's SQL databases include SQL Server, Azure SQL Database, and Azure SQL Data Warehouse.
44

5-
Samples are coming soon!
5+
__[world-wide-importers](world-wide-importers/)__
66

7+
The new sample database for SQL Server 2016 and Azure SQL Database. It illustrates best practices in database design, as well as the core capabilities of SQL Server 2016 and Azure SQL Database, for transaction processing (OLTP), data warehousing and analytics (OLAP) workloads, as well as hybrid transaction and analytics processing (HTAP) workloads.
78

9+
__[contoso-data-warehouse](contoso-data-warehouse/)__
10+
11+
Sample data warehouse that illustrates loading data into Azure SQL Data Warehouse.

‎samples/databases/wide-world-importers/README.md

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
# WideWorldImporters Sample Database for SQL Server and Azure SQL Database
2+
3+
WideWorldImporters is a sample for SQL Server and Azure SQL Database. It showcases best practices in database design, as well as how to best leverage SQL Server features in a database.
4+
5+
WideWorldImporters is a wholesale company. Transactions and real-time analytics are performed in the database WideWorldImporters. The database WideWorldImportersDW is an OLAP database, focused on analytics.
6+
7+
The sample includes the databases that can be explored, as well as sample applications and sample scripts that can be used to explore the use of individual SQL Server features in the sample database.
8+
9+
### Contents
10+
11+
[About this sample](#about-this-sample)<br/>
12+
[Before you begin](#before-you-begin)<br/>
13+
[Sample structure](#run-this-sample)<br/>
14+
[Disclaimers](#disclaimers)<br/>
15+
[Related links](#related-links)<br/>
16+
17+
18+
<a name=about-this-sample></a>
19+
20+
## About this sample
21+
22+
<!-- Delete the ones that don't apply -->
23+
1. **Applies to:** SQL Server 2016 (or higher), Azure SQL Database
24+
1. **Key features:** Core database features
25+
1. **Workload:** OLTP, OLAP, IoT
26+
1. **Programming Language:** T-SQL, C#
27+
1. **Authors:** Greg Low, Jos de Bruijn
28+
1. **Update history:** 25 May 2016 - initial revision
29+
30+
<a name=before-you-begin></a>
31+
32+
## Before you begin
33+
34+
To run this sample, you need the following prerequisites.
35+
36+
**Software prerequisites:**
37+
38+
<!-- Examples -->
39+
1. SQL Server 2016 (or higher) or an Azure SQL Database.
40+
2. SQL Server Management Studio, preferably 2016 April Preview or later (version >= 13.0.14000.36).
41+
3. (to build sample apps) Visual Studio 2015.
42+
4. (to run ETL jobs) SQL Server 2016 Integration Services
43+
44+
<a name=run-this-sample></a>
45+
46+
## Sample structure
47+
48+
The latest release of this sample is available here: TBD
49+
50+
The source code for the sample is structured as follows:
51+
52+
__[sample-scripts] (sample-scripts/)__
53+
54+
Sample scripts that illustrate the use of various SQL Server features with the WideWorldImporters sample database.
55+
56+
__[workload-drivers] (workload-drivers/)__
57+
58+
Simple apps that simulate workloads for the WideWorldImporters sample database.
59+
60+
__[wwi-database-scripts] (wwi-database-scripts/)__
61+
62+
T-SQL scripts to create the main WideWorldImporters database.
63+
64+
__[wwi-dw-database-scripts] (wwi-database-scripts/)__
65+
66+
T-SQL scripts to create the analytics database WideWorldImportersDW.
67+
68+
__[wwi-integration-etl] (wwi-integration-etl/)__
69+
70+
SQL Server Integration Services (SSIS) project for the Extract, Transform, and Load (ETL) process that takes data from the transactional database WideWorldImporters and loads it into the WideWorldImportersDW database.
71+
72+
73+
<a name=disclaimers></a>
74+
75+
## Disclaimers
76+
The code included in this sample is not intended to be used for production purposes.
77+
78+
<a name=related-links></a>
79+
80+
## Related Links
81+
<!-- Links to more articles. Remember to delete "en-us" from the link path. -->
82+
For more information, see these articles:
83+
- [SQL Server 2016 product page](https://www.microsoft.com/server-cloud/products/sql-server-2016/)
84+
- [SQL Server 2016 download page](https://www.microsoft.com/evalcenter/evaluate-sql-server-2016)
85+
- [Azure SQL Database product page](https://azure.microsoft.com/services/sql-database/)
86+
- [What's new in SQL Server 2016](https://msdn.microsoft.com/en-us/library/bb500435.aspx)

‎samples/databases/wide-world-importers/sample-scripts/always-encrypted/DemonstrateAlwaysEncrypted - Window 2.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
-- Always Encrypted Demo - Window 2
2+
-- This connection is used to simulate a client app
3+
4+
-- note this demo is continued from the first demo window
5+
6+
-- 5b. Right-click in this window and choose Connection, then Change Connection.
7+
-- 5c. In the connection dialog, click Options.
8+
-- 5d. Type WideWorldImporters for the database name.
9+
-- 5e. Click on Additional Connection Parameters and enter: Column Encryption Setting=enabled
10+
-- 5f. Click Connect
11+
12+
-- Note that when acting as a client with access to the certificate, we
13+
-- can see the data. Remember that this can only work because
14+
-- the client happens to be the same machine as the server in our
15+
-- case.
16+
17+
SELECT * FROM Purchasing.Supplier_PrivateDetails ORDER BY SupplierID;
18+
GO
19+
20+
21+
22+
-- Continue on the first window.

‎samples/databases/wide-world-importers/sample-scripts/always-encrypted/DemonstrateAlwaysEncrypted.sql

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
-- Always Encrypted Demo
2+
3+
USE WideWorldImporters;
4+
GO
5+
6+
-- WWI have decided to store some national ID and credit card details for suppliers
7+
-- but these details need to always be encrypted
8+
9+
-- Remove any existing column keys and/or table
10+
DROP TABLE IF EXISTS Purchasing.Supplier_PrivateDetails;
11+
IF EXISTS (SELECT 1 FROM sys.column_encryption_keys WHERE name = N'WWI_ColumnEncryptionKey')
12+
BEGIN
13+
DROP COLUMN ENCRYPTION KEY WWI_ColumnEncryptionKey;
14+
END;
15+
IF EXISTS (SELECT 1 FROM sys.column_master_keys WHERE name = N'WWI_ColumnMasterKey')
16+
BEGIN
17+
DROP COLUMN MASTER KEY WWI_ColumnMasterKey;
18+
END;
19+
GO
20+
21+
-- We need a column master key. This key is used to encrypt the column encryption keys.
22+
-- The column master key isn't really stored in the database. It's created and stored on the
23+
-- client system. SQL Server only holds a link to it so that SQL Server can tell the
24+
-- client application where to locate the master key. The client system will encrypt a column
25+
-- encryption key with this master key.
26+
27+
-- The wizard will create a certificate, install it in the certificate store, then
28+
-- register it with SQL Server via CREATE COLUMN MASTER KEY
29+
30+
-- 1a. In Object Explorer, expand the security node in WideWorldImporters, then expand
31+
-- the Always Encrypted Keys node and note the contents.
32+
-- 1b. Right-click the Column Master Keys node and click New Column Master Key.
33+
-- 1c. For the name, enter WWI_ColumnMasterKey.
34+
-- 1d. Note the available entries in the Key store dropdown list. Choose Windows Certificate Store - Current User.
35+
-- This will only be a temporary location for the certificate.
36+
-- 1e. Click Generate Certificate to create the new certificate. Note that an Always Encrypted certificate
37+
-- has been created. Ensure that it is selected, then click OK.
38+
39+
-- We have used the MSSQL_CERTIFICATE_STORE which uses the Windows store
40+
-- but we can use any store that implements the SqlColumnEncryptionKeyStoreProvider
41+
-- class. (And is registered by calling the SqlConnection.RegisterColumnEncryptionKeyStoreProviders()
42+
-- method). This requires .NET framework 4.6.1 or later on the client.
43+
44+
-- The certificate could also have been created via the makecert utility and just loaded on the client.
45+
46+
-- We can see the newly created master key. Note the key_path. This path is relative to the client.
47+
48+
SELECT * FROM sys.column_master_keys;
49+
50+
-- The next key that we need is used for performing column encryption. It's held encrypted on the
51+
-- database server and is decrypted (and cached) on the client application before use.
52+
-- On the client system, it is protected by the column master key.
53+
54+
-- 2a. In Object Explorer, right-click the Column Encryption Keys node and click New Column Encryption Key.
55+
-- 2b. In the Name textbox, enter WWI_ColumnEncryptionKey and from the Column master key dropdown list,
56+
-- select WWI_ColumnMasterKey to be used to encrypt this new key. Then click OK.
57+
58+
-- We can see the newly created encryption key.
59+
60+
SELECT * FROM sys.column_encryption_keys;
61+
62+
-- Now let's create the table that will use always encrypted.
63+
-- We'll have one deterministic encryption column and two random
64+
-- encryption (salted) columns.
65+
66+
CREATE TABLE Purchasing.Supplier_PrivateDetails
67+
(
68+
SupplierID int
69+
CONSTRAINT PKFK_Purchasing_Supplier_PrivateDetails PRIMARY KEY
70+
CONSTRAINT FK_Purchasing_Supplier_PrivateDetails_Suppliers
71+
FOREIGN KEY REFERENCES Purchasing.Suppliers (SupplierID),
72+
NationalID nvarchar(30) COLLATE Latin1_General_BIN2
73+
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey,
74+
ENCRYPTION_TYPE = DETERMINISTIC,
75+
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
76+
CreditCardNumber nvarchar(30) COLLATE Latin1_General_BIN2
77+
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey,
78+
ENCRYPTION_TYPE = RANDOMIZED,
79+
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
80+
ExpiryDate nvarchar(5) COLLATE Latin1_General_BIN2
81+
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = WWI_ColumnEncryptionKey,
82+
ENCRYPTION_TYPE = RANDOMIZED,
83+
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
84+
);
85+
GO
86+
87+
-- Note that we can't directly insert unencrypted data
88+
-- Note the error returned. The data in the columns is only
89+
-- understood by the client system.
90+
91+
INSERT Purchasing.Supplier_PrivateDetails
92+
(SupplierID, NationalID, CreditCardNumber, ExpiryDate)
93+
VALUES
94+
(1, N'93748567', N'7382-5849-2903-2838', N'11/19');
95+
GO
96+
97+
-- Let's ensure the table is empty, then we'll use a client application
98+
-- to populate the data. Note that we can still perform standard
99+
-- table operations like truncation.
100+
101+
TRUNCATE TABLE Purchasing.Supplier_PrivateDetails;
102+
GO
103+
104+
-- 3. Run the client application PopulateAlwaysEncryptedData.exe to insert sample data
105+
106+
-- 4. Verify that, on the server side (which does not have the keys), all the data is encrypted
107+
108+
SELECT * FROM Purchasing.Supplier_PrivateDetails ORDER BY SupplierID;
109+
GO
110+
111+
-- To emulate a client application that has access to the keys, we
112+
-- can use SSMS to connect. Note that this can only work because
113+
-- the client happens to be the same machine as the server in our
114+
-- case.
115+
116+
-- 5a. Open the second query window for this demonstration and follow the instructions there.
117+
118+
119+
120+
-- 6. (optional) Clean up afterwards.
121+
122+
-- Remove any existing column keys and/or table
123+
DROP TABLE IF EXISTS Purchasing.Supplier_PrivateDetails;
124+
IF EXISTS (SELECT 1 FROM sys.column_encryption_keys WHERE name = N'WWI_ColumnEncryptionKey')
125+
BEGIN
126+
DROP COLUMN ENCRYPTION KEY WWI_ColumnEncryptionKey;
127+
END;
128+
IF EXISTS (SELECT 1 FROM sys.column_master_keys WHERE name = N'WWI_ColumnMasterKey')
129+
BEGIN
130+
DROP COLUMN MASTER KEY WWI_ColumnMasterKey;
131+
END;
132+
GO
133+
134+

‎samples/databases/wide-world-importers/sample-scripts/always-encrypted/PopulateAlwaysEncryptedData.sln

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
2+
Microsoft Visual Studio Solution File, Format Version 12.00
3+
# Visual Studio 14
4+
VisualStudioVersion = 14.0.25123.0
5+
MinimumVisualStudioVersion = 10.0.40219.1
6+
Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "PopulateAlwaysEncryptedData", "PopulateAlwaysEncryptedData\PopulateAlwaysEncryptedData.csproj", "{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}"
7+
EndProject
8+
Global
9+
GlobalSection(SolutionConfigurationPlatforms) = preSolution
10+
Debug|Any CPU = Debug|Any CPU
11+
Release|Any CPU = Release|Any CPU
12+
EndGlobalSection
13+
GlobalSection(ProjectConfigurationPlatforms) = postSolution
14+
{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
15+
{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}.Debug|Any CPU.Build.0 = Debug|Any CPU
16+
{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}.Release|Any CPU.ActiveCfg = Release|Any CPU
17+
{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}.Release|Any CPU.Build.0 = Release|Any CPU
18+
EndGlobalSection
19+
GlobalSection(SolutionProperties) = preSolution
20+
HideSolutionNode = FALSE
21+
EndGlobalSection
22+
EndGlobal

‎samples/databases/wide-world-importers/sample-scripts/always-encrypted/PopulateAlwaysEncryptedData/App.config

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
<?xml version="1.0" encoding="utf-8" ?>
2+
<configuration>
3+
<configSections>
4+
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
5+
<section name="PopulateAlwaysEncryptedData.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
6+
</sectionGroup>
7+
</configSections>
8+
<startup>
9+
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
10+
</startup>
11+
<userSettings>
12+
<PopulateAlwaysEncryptedData.Properties.Settings>
13+
<setting name="WWI_ConnectionString" serializeAs="String">
14+
<value />
15+
</setting>
16+
</PopulateAlwaysEncryptedData.Properties.Settings>
17+
</userSettings>
18+
</configuration>

‎samples/databases/wide-world-importers/sample-scripts/always-encrypted/PopulateAlwaysEncryptedData/PopulateAlwaysEncryptedData.csproj

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,90 @@
1+
<?xml version="1.0" encoding="utf-8"?>
2+
<Project ToolsVersion="14.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
3+
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
4+
<PropertyGroup>
5+
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
6+
<Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
7+
<ProjectGuid>{83DD3CB9-58BA-46F4-8E7C-3F749A659C53}</ProjectGuid>
8+
<OutputType>WinExe</OutputType>
9+
<AppDesignerFolder>Properties</AppDesignerFolder>
10+
<RootNamespace>PopulateAlwaysEncryptedData</RootNamespace>
11+
<AssemblyName>PopulateAlwaysEncryptedData</AssemblyName>
12+
<TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
13+
<FileAlignment>512</FileAlignment>
14+
<AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
15+
</PropertyGroup>
16+
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
17+
<PlatformTarget>AnyCPU</PlatformTarget>
18+
<DebugSymbols>true</DebugSymbols>
19+
<DebugType>full</DebugType>
20+
<Optimize>false</Optimize>
21+
<OutputPath>bin\Debug\</OutputPath>
22+
<DefineConstants>DEBUG;TRACE</DefineConstants>
23+
<ErrorReport>prompt</ErrorReport>
24+
<WarningLevel>4</WarningLevel>
25+
</PropertyGroup>
26+
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
27+
<PlatformTarget>AnyCPU</PlatformTarget>
28+
<DebugType>pdbonly</DebugType>
29+
<Optimize>true</Optimize>
30+
<OutputPath>bin\Release\</OutputPath>
31+
<DefineConstants>TRACE</DefineConstants>
32+
<ErrorReport>prompt</ErrorReport>
33+
<WarningLevel>4</WarningLevel>
34+
</PropertyGroup>
35+
<ItemGroup>
36+
<Reference Include="System" />
37+
<Reference Include="System.Core" />
38+
<Reference Include="System.Xml.Linq" />
39+
<Reference Include="System.Data.DataSetExtensions" />
40+
<Reference Include="Microsoft.CSharp" />
41+
<Reference Include="System.Data" />
42+
<Reference Include="System.Deployment" />
43+
<Reference Include="System.Drawing" />
44+
<Reference Include="System.Net.Http" />
45+
<Reference Include="System.Windows.Forms" />
46+
<Reference Include="System.Xml" />
47+
</ItemGroup>
48+
<ItemGroup>
49+
<Compile Include="PopulateAlwaysEncryptedDataMain.cs">
50+
<SubType>Form</SubType>
51+
</Compile>
52+
<Compile Include="PopulateAlwaysEncryptedDataMain.Designer.cs">
53+
<DependentUpon>PopulateAlwaysEncryptedDataMain.cs</DependentUpon>
54+
</Compile>
55+
<Compile Include="Program.cs" />
56+
<Compile Include="Properties\AssemblyInfo.cs" />
57+
<EmbeddedResource Include="PopulateAlwaysEncryptedDataMain.resx">
58+
<DependentUpon>PopulateAlwaysEncryptedDataMain.cs</DependentUpon>
59+
</EmbeddedResource>
60+
<EmbeddedResource Include="Properties\Resources.resx">
61+
<Generator>ResXFileCodeGenerator</Generator>
62+
<LastGenOutput>Resources.Designer.cs</LastGenOutput>
63+
<SubType>Designer</SubType>
64+
</EmbeddedResource>
65+
<Compile Include="Properties\Resources.Designer.cs">
66+
<AutoGen>True</AutoGen>
67+
<DependentUpon>Resources.resx</DependentUpon>
68+
</Compile>
69+
<None Include="Properties\Settings.settings">
70+
<Generator>SettingsSingleFileGenerator</Generator>
71+
<LastGenOutput>Settings.Designer.cs</LastGenOutput>
72+
</None>
73+
<Compile Include="Properties\Settings.Designer.cs">
74+
<AutoGen>True</AutoGen>
75+
<DependentUpon>Settings.settings</DependentUpon>
76+
<DesignTimeSharedInput>True</DesignTimeSharedInput>
77+
</Compile>
78+
</ItemGroup>
79+
<ItemGroup>
80+
<None Include="App.config" />
81+
</ItemGroup>
82+
<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
83+
<!-- To modify your build process, add your task inside one of the targets below and uncomment it.
84+
Other similar extension points exist, see Microsoft.Common.targets.
85+
<Target Name="BeforeBuild">
86+
</Target>
87+
<Target Name="AfterBuild">
88+
</Target>
89+
-->
90+
</Project>

0 commit comments

Comments
 (0)