Microsoft SQL Server - Target environment

Applies to
Platform: Microsoft SQL Server

A Microsoft SQL Server target environment needs PowerShell 7, sqlcmd and a SQL Server instance for the databases of the layers and the load control. Prepare it before you replace the placeholders and deploy the artifacts of a Microsoft SQL Server generator. Afterwards deploy.ps1 can connect and create the databases, schemas and objects.

The steps apply to all Microsoft SQL Server generator configurations; Stage Files and Stage OpenQuery need the extra preparation in Provide the source data. biGENIUS support does not cover the installation and configuration of SQL Server beyond the example in this article.

Prerequisites

A SQL Server instance that the deploying machine reaches by server name, instance name or port.
A SQL Server login, or a Windows account for Windows authentication, that can create the databases, schemas and objects of the layers and the load control. The deployment creates databases and schemas that are missing.

Install the tools

Install PowerShell 7. The scripts deploy.ps1 and execute.ps1 require it.
Install the sqlcmd utility and add its folder to the PATH environment variable.
Open a new PowerShell 7 window and run sqlcmd -?. sqlcmd prints its version and its options.
Optionally, install SQL Server Management Studio to query the databases and the load control.

Provide the source data

Which source system types a generator configuration accepts is listed in Source systems per generator configuration. To create source model objects from a SQL Server database, see Discover a Microsoft SQL Server database. A Data Mart project has no discovery; it uses model objects of linked projects, see Manage Linked Projects.

CSV files and databases that SQL Server reaches only through a linked server are loaded by a separate Stage Files or Stage OpenQuery project. A second project, for example a Data Vault project, then uses its Stage model objects as a linked project.

CSV files with Stage Files

The Stage Files loader runs xp_cmdshell to list and move the files, so SQL Server must run on Windows. Prepare the SQL Server host:

Create the four folders Source, Format, Archive and Error, for example below C:\data.
Give the SQL Server service account, for example NT Service\MSSQLSERVER, the permission Modify on the four folders.

Enable xp_cmdshell as a member of the sysadmin role:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Copy the .fmt files from the folder Format of the artifacts into Format, and the CSV files into Source. For example:
Enter the four paths in the placeholder values.

Databases through a linked server with Stage OpenQuery

The Stage OpenQuery loader reads each source table with OPENQUERY through a linked server. The linked server name is the server_name value of the source system. For an Oracle source with the Oracle OLE DB provider:

Install the Oracle provider OraOLEDB.Oracle of the Oracle Data Access Components on the SQL Server host. In SQL Server Management Studio, Server Objects > Linked Servers > Providers lists OraOLEDB.Oracle once it is installed.

Allow the provider to run in process:

EXEC master.dbo.sp_MSset_oledb_prop
  N'OraOLEDB.Oracle', N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop
  N'OraOLEDB.Oracle', N'DynamicParameters', 1;

Create the linked server and its login:

EXEC master.dbo.sp_addlinkedserver
  @server = N'<linked server>', @srvproduct = N'Oracle',
  @provider = N'OraOLEDB.Oracle',
  @datasrc = N'//<host name>:<port>/<service name>';
EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'<linked server>', @useself = N'False',
  @locallogin = NULL,
  @rmtuser = N'<user name>', @rmtpassword = N'<password>';

Here <linked server> is a name you choose, and the other values identify the Oracle database and user.

Example

For a test environment, biGENIUS provides the Docker image bigeniusx/training-sqlserver:2022-latest. It contains SQL Server 2022 on Linux and the example source databases AdventureWorks2019 and CRM_Example. With Docker Desktop installed, create the network that the Apache Airflow environment also uses, and start the container:

docker network create training-network
docker run --name training-sqlserver-2022 `
  --network training-network -p 1460:1433 `
  -d bigeniusx/training-sqlserver:2022-latest

Connect with the server name localhost,1460, SQL Server authentication, the login sa and the password Admin2022!. Object Explorer then lists the two example databases:

A Linux container cannot load CSV files, because xp_cmdshell is not supported on Linux. For Stage Files, use the Windows image and adapt the shared folder:

docker run -d --name training-sqlserver-windows-2022 `
  --network training-network -p 1470:1433 `
  -v C:\shared:C:\shared `
  bigeniusx/training-sqlserver-on-windows:latest `
  powershell -file C:\start.ps1

Check the result

Connect with sqlcmd as the deployment will, for example with Windows authentication: sqlcmd -S <server name> -Q "SELECT @@VERSION". With a login, add -U <login> -P <password>. sqlcmd prints the SQL Server version. For Stage Files, EXEC xp_cmdshell 'dir <source folder>' lists the CSV files. For Stage OpenQuery, a query such as SELECT * FROM OPENQUERY(<linked server>, 'SELECT 1 FROM DUAL') returns a row.

Failure symptoms and recovery

The deployment script does not find sqlcmd

Message: SqlCmd is not installed, not recognized or the PATH environment variable is not properly configured
Cause: The sqlcmd folder is not in PATH of the PowerShell window.
Fix: Add the folder to PATH, open a new PowerShell 7 window and check sqlcmd -?.

For failures in other steps, see Troubleshoot generation, deployment and load failures.