Install SQL Server and Oracle on Windows Containers

By: Pablo Echeverria | Updated: 2022-01-05 | Comments (2) | Related: > SQL Server and Oracle Comparison

Problem

There are multiple database platforms to choose from, each with unique characteristics that differentiate it from the others. What are the differences between SQL Server and Oracle in terms of the software installation on Windows?

Solution

In this tip we'll show you how to install SQL Server and Oracle in Windows using PowerShell. We will take advantage of Docker on Windows, as this allows us to easily create and destroy containers running Windows Server Core 2019 to test different configurations in the software. We won't cover Oracle Real Application Clusters, as there is no counterpart in SQL Server and because it is a separate installer independent from the database. Oracle installation in Linux is very similar as you can see in this article I wrote here, but it is totally different from SQL Server installation in Linux so they can't be compared. The differences when creating a database will be covered in another tip.

Environment Preparation

The first requirement is that you have Windows installed on your PC, then you can download Docker for Windows from here and in that link you can also find the installation instructions. Once you have Docker running, perform the following steps:

Make sure you're running Windows containers: right-click on the Docker icon and if needed, click "Switch to Windows containers".
Create a local folder under the C:\ drive named "temp", this is where you'll place the installers.

Then open a PowerShell prompt and run the commands below. You can find detailed explanation about them in the Docker documentation.

First pull the Windows Server Core 2019 image by running the command below:

docker pull mcr.microsoft.com/windows/servercore:ltsc2019

Then start a container by running the command below:

docker run -it --name MyContainer -v C:\temp:C:\setup mcr.microsoft.com/windows/servercore:ltsc2019 powershell

The above command opens a PowerShell prompt inside your container, and there's where you need to run the commands to install the software. To exit the console, just type "exit".

If you want to view all your running containers in your local PC, run the command below:

docker ps -a

If you want to restart a container, run the command below:

docker restart MyContainer

And if you want to delete a container, run the command below:

docker rm MyContainer

To be able to download software within the container, you need to create a Docker network with the command below:

docker network create -d nat --gateway 172.8.128.1 --subnet 172.8.128.0/20 -o com.docker.network.windowsshim.dnsservers=4.4.4.4,8.8.8.8 -o com.docker.network.windowsshim.disable_gatewaydns=true MyPublic

And then connect this created network to the container with the command below:

docker network connect MyPublic MyContainer

If you exit the container, you need to start it using the command below:

docker start -ai MyContainer

And if your container is already running and you need to open another PowerShell session, run the command below:

docker exec -it MyContainer powershell

Remember the commands related to the installation must be run in the PowerShell prompt inside your container, not in your local PC.

Oracle Installation

Because you will need to modify a file within the container, first install "chocolatey" with the command below:

Invoke-WebRequest https://chocolatey.org/install.ps1 -UseBasicParsing | Invoke-Expression

Then you need to install "vim" with the command below:

choco install -y vim

And then you need to refresh the environment variables with the command below:

refreshenv

Outside of the container (in your PC) you need to download the Oracle software. The latest downloadable version is 19.3 which you can get from here, note the download will prompt you to register and it's also free. The downloaded file is named "WINDOWS.X64_193000_db_home.zip" with a size of 2.89GB, and you need to copy it to c:\temp for the container to be able to see it. Once that is done, in the PowerShell console inside your container, unzip the contents with the command below (in my case the unzip took 45 mins), the place where you extract it will be your Oracle Home:

Expand-Archive c:\setup\WINDOWS.X64_193000_db_home.zip c:\app\Oracle19c -force

You need to edit a text file because the installer doesn't recognize two parameters passed to it, so run the command below:

vim C:\app\Oracle19c\install\response\db_install.rsp

And the lines you need to edit are these:

oracle.install.IsBuiltInAccount=false
oracle.install.IsVirtualAccount=true

To do that, once you're inside "vim", press the key combination "Shift+G" to go to the bottom where those two lines are located, press the key "i" to be able to insert text, use the arrow keys to move to the desired location, and type "false" and "true" where needed. Once you're done, press the "Esc" key and then type ":x" to save the file.

By examining this file "db_install.rsp" you can see the different options available when installing the software, most are non-mandatory:

oracle.install.option: only install software (INSTALL_DB_SWONLY) or install and configure software but not the listener (INSTALL_DB_AND_CONFIG)
UNIX_GROUP_NAME: Unix group to be set for the inventory directory
ORACLE_HOME: complete path of the Oracle Home (which holds the database binaries)
ORACLE_BASE: complete path of the Oracle Base (which holds multiple database versions)
oracle.install.db.InstallEdition: installation edition of the component: EE (Enterprise Edition) or SE2 (Standard Edition 2), see this link for some of their differences
oracle.install.db.isCustomInstall: Only for EE, indicates if you will specify individual components (true) or not (false)
oracle.install.db.customComponents: Only if EE and isCustomInstall are both true, below is a non-exhaustive list of some of the components that were able to be installed in 11g

  oracle.oraolap:11.2.0.0.2 - Oracle OLAP
  oracle.rdbms.dm:11.2.0.0.2 - Oracle Data Mining RDBMS Files
  oracle.rdbms.dv:11.2.0.0.2 - Oracle Database Vault option
  oracle.rdbms.lbac:11.2.0.0.2 - Oracle Label Security
  oracle.rdbms.partitioning:11.2.0.0.2 - Oracle Partitioning
  oracle.rdbms.rat:11.2.0.0.2 - Oracle Real Application Testing
  oracle.clrintg.ode_net_2:11.2.0.0.2 - Oracle Database Extensions for .NET 2.0 (Windows) 
  oracle.server:11.2.0.1.0
  oracle.sysman.ccr:10.2.7.0.0
  oracle.xdk:11.2.0.1.0
  oracle.rdbms.oci:11.2.0.1.0
  oracle.network:11.2.0.1.0
  oracle.network.listener:11.2.0.1.0
  oracle.rdbms:11.2.0.1.0
  oracle.options:11.2.0.1.0

oracle.install.db.OSDBA_GROUP: Only for Unix based OS
oracle.install.db.OSOPER_GROUP: Only for Unix based OS
oracle.install.db.OSBACKUPDBA_GROUP: OS group for backup privileges
oracle.install.db.OSDGDBA_GROUP: OS group for diskgroup privileges
oracle.install.db.OSKMDBA_GROUP: Only for RAC configuration
oracle.install.db.OSRACDBA_GROUP: Only for RAC configuration
oracle.install.db.CLUSTER_NODES: Only for RAC configuration
oracle.install.db.config.starterdb.type: A starter database designed for general purpose use (GENERAL_PURPOSE) or optimized transaction-heavy data warehousing applications (DATA_WAREHOUSE)
oracle.install.db.config.starterdb.globalDBName: Starter Database Global Database Name
oracle.install.db.config.starterdb.SID: Service Identifier of the Starter Database
oracle.install.db.ConfigureAsContainerDB: Specify whether the database should be configured as a Container database (true) or not (false, default)
oracle.install.db.config.PDBName: Pluggable Database name in Container Database
oracle.install.db.config.starterdb.characterSet: One of the following: AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2, EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257, BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6, AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8, IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE, KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950, ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
oracle.install.db.config.starterdb.memoryOption: If Automatic Memory Management is desired (true) or memory allocation is to be done manually (false)
oracle.install.db.config.starterdb.memoryLimit: total memory allocation for the database, value (in MB) should be at least 256 MB, and should not exceed the total physical memory available on the system
oracle.install.db.config.starterdb.installExampleSchemas: whether to load Example Schemas onto the starter database or not
oracle.install.db.config.starterdb.password.ALL: NOT RECOMMENDED, password that is to be used for all schemas in the starter database
oracle.install.db.config.starterdb.password.SYS: SYS password for the starter database
oracle.install.db.config.starterdb.password.SYSTEM: SYSTEM password for the starter database
oracle.install.db.config.starterdb.password.DBSNMP: Only when using Cloud Control, DBSNMP (Enterprise Manager) password for the starter database
oracle.install.db.config.starterdb.password.PDBADMIN: PDBADMIN password required for creation of Pluggable Database in the Container Database
oracle.install.db.config.starterdb.managementOption: If you want to manage your database with Enterprise Manager Cloud Control along with Database Express (CLOUD_CONTROL) or if you want to manage your database using the default Enterprise Manager Express option (DEFAULT)
oracle.install.db.config.starterdb.omsHost: Only when using Cloud Control, the OMS host
oracle.install.db.config.starterdb.omsPort: Only when using Cloud Control, the OMS port
oracle.install.db.config.starterdb.emAdminUser: Only when using Cloud Control, the EM Admin user name
oracle.install.db.config.starterdb.emAdminPassword: Only when using Cloud Control, the EM Admin password
oracle.install.db.config.starterdb.enableRecovery: if database recovery is required (true) or not (false)
oracle.install.db.config.starterdb.storageType: the type of storage to use for the database, FILE_SYSTEM_STORAGE or ASM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation: Only for file system storage, the database file location which is a directory for datafiles, control files, redo logs
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation: Only for file system storage, the recovery files location
oracle.install.db.config.asm.diskGroup: Only for ASM storage, the existing ASM disk groups to be used for storage
oracle.install.db.config.asm.ASMSNMPPassword: Only for ASM storage, the password for ASMSNMP user of the ASM instance
oracle.install.IsBuiltInAccount: false to use Windows Account user
oracle.install.IsVirtualAccount: true to use Virtual Account, false to use Windows Account user
oracle.install.OracleHomeUserName: if required, it is recommended that you specify a Windows User Account with limited privilege to install and configure a secure Oracle home
oracle.install.OracleHomeUserPassword: password of the Windows User Account

Now you will be able to run the installer "c:\app\Oracle19c\setup.exe" specifying the response file and the variables to be set, but if you run "setup.exe -silent -help" you will see a list of available parameters:

Usage: runInstaller [<flag>] [<option>]
Following are the possible flags:
-help - display help.
-silent - run in silent mode. The inputs can be a response file or a list of command line variable value pairs.
[-ignorePrereqFailure - ignore all prerequisite checks failures.]
-responseFile - specify the complete path of the response file to use.
-logLevel - enable the log of messages up to the priority level provided in this argument. Valid options are: severe, warning, info, config, fine, finer, finest.
-executePrereqs | -executeConfigTools | -createGoldImage
-executePrereqs - execute the prerequisite checks only.
-executeConfigTools - execute the config tools for an installed home.
-createGoldImage - create a gold image from the current Oracle home.
-destinationLocation - specify the complete path to where the created gold image will be located.
[-exclFiles - specify the complete paths to the files to be excluded from the new gold image.]
-debug - run in debug mode.
-printdiskusage - log the debug information for the disk usage.
-printmemory - log the debug information for the memory usage.
-printtime - log the debug information for the time usage.
-waitForCompletion - wait for the completion of the installation, instead of spawning the installer and returning the console prompt.
-noconfig - do not execute the config tools.
-noconsole - suppress the display of messages in the console. The console is not allocated.
-ignoreInternalDriverError - ignore any internal driver errors.
-noCopy - perform the configuration without copying the software on to the remote nodes.
-applyRU - apply release update to the Oracle home.
-applyOneOffs - apply one-off patch to the Oracle home. Multiple one-off patches can be passed as a comma separated list of locations.

Now you will be able to run the installer with the command below, note these are the minimum parameters that need to be supplied but there are many more options when installing:

& c:\app\Oracle19c\setup.exe -silent -waitForCompletion -responseFile c:\app\Oracle19c\install\response\db_install.rsp `
  oracle.install.option=INSTALL_DB_SWONLY `
  INVENTORY_LOCATION=c:\app\oraInventory\ `
  SELECTED_LANGUAGES=en `
  ORACLE_BASE=c:\app\ `
  oracle.install.db.InstallEdition=EE

SQL Server Installation

Outside of the container (in your PC) you need to download the SQL Server software from here. You must click on "Free trial", and on the next page choose the "EXE" type and click "Continue". After you provide your contact information you will obtain a downloader program. When you run it, choose "Download ISO", choose the language, and enter the download location. Once the ISO is downloaded, extract its contents into your PC in the c:\temp folder for the container to be able to see it.

You need to login to the container as explained before, and then run the command below:

& "c:\setup\setup.exe" /Q /Action=Install /IAcceptSQLServerLicenseTerms /IndicateProgress /Features=SQLEngine,Conn /InstanceName=MSSQLSERVER /TcpEnabled=1 /SecurityMode=SQL /SaPwd=@Sq1T3st /SqlSysAdminAccounts="ContainerAdministrator"

This is for a minimum install, but the list of features can be found here, some of them are below excluding the GUI and the obsolete ones:

/Action: can be Install, Upgrade, PrepareImage, CompleteImage, Repair, RebuildDatabase, Uninstall, InstallFailoverCluster, PrepareFailoverCluster, CompleteFailoverCluster, AddNode, RemoveNode
/IAcceptPythonLicenseTerms: required to acknowledge acceptance of Anaconda Python license terms
/IAcceptROpenLicenseTerms: required to acknowledge acceptance of Microsoft R Open license terms
/ENU: install the English version of SQL Server on a localized operating system when the installation media includes language packs for both English and the language corresponding to the operating system
/UpdateEnabled: whether SQL Server setup should discover and include product updates
/UpdateSource: location where SQL Server setup will obtain product updates, "MU" to search Microsoft Update, a valid folder path, a relative path such as ".\MyUpdates" or a UNC share
/ConfigurationFile: specifies the ConfigurationFile to use
/Features or /Role: Features can be SQLEngine, Replication, FullText, DQ (Data Quality), AS (Analysis Services), AS_SPI, DQC (Data Quality Client), Conn (Connectivity components), IS (Integration Services), IS_Master, IS_Worker, BC (Backward Compatibility), SDK, DREPLAY_CTLR (Distributed Replay controller), DREPLAY_CLT (Distributed Replay client), SNAC_SDK (SDK for Microsoft SQL Server Native Client), SQLODBC, SQLODBC_SDK, LocalDB, MDS (Master Data Services), Polybase, PolyBaseCore, PolyBaseJava, AdvancedAnalytics, SQL_INST_MR (R for Machine Learning), SQL_SHARED_MR, SQL_INST_MPY (Python for Machine Learning), SQL_SHARED_MPY, SQL_INST_JAVA; enter Role to install a predetermined configuration: SPI_AS_ExistingFarm, SPI_AS_NewFarm, AllFeatures_WithDefaults
/Help, ?: displays the usage options for installation parameters
/IndicateProgress: for the verbose Setup log file to be piped to the console
/InstallSharedDir: nondefault installation directory for 64-bit shared components
/InstallSharedWOWDir: nondefault installation directory for 32-bit shared components
/InstanceDir: nondefault installation directory for instance-specific components
/InstanceID: nondefault value for an InstanceID
/InstanceName: SQL Server instance name
/PBEngSvcAccount: account for the Polybase engine service
/PBEngSvcPassword: password for the Polybase engine service
/PBDMSSvcAccount: account for the Polybase DMS service
/PBDMSSvcPassword: password for the Polybase DMS service
/PBENGSvcStartupType: startup mode for the PolyBase engine service: Automatic (default), Disabled, and Manual
/PBPortRange: port range with at least 6 ports for PolyBase services
/PBScaleOut: if the SQL Server Database Engine instance will be used as a part of PolyBase Scale-out computational group
/PID: product key for the edition of SQL Server, if not specified Evaluation is installed
/Q or /Quiet: quiet mode without any user interface for unattended installations
/AgtSvcAccount: account for the SQL Server Agent service
/AgtSvcPassword: password for SQL Server Agent service account, can be omitted when using a managed service account, virtual account, or built-in account
/AgtSvcStartupType: startup mode for the SQL Server Agent service, Automatic, Disabled or Manual
/ASBackupDir: directory for Analysis Services backup files
/ASCollation: collation setting for Analysis Services, default Latin1_General_CI_AS
/ASConfigDir: directory for Analysis Services configuration files
/ASDataDir: directory for Analysis Services data files
/ASLogDir: directory for Analysis Services log files
/ASServerMode: server mode of the Analysis Services instance, must be uppercase: MULTIDIMENSIONAL, POWERPIVOT or TABULAR
/ASSvcAccount: account for the Analysis Services service
/ASSvcPassword: password for the Analysis Services service, can be omitted when using a managed service account, virtual account, or built-in account
/ASSvcStartupType: startup mode for the Analysis Services service, Automatic, Disabled or Manual
/ASSysAdminAccounts: administrator credentials for Analysis Services
/ASTempDir: directory for Analysis Services temporary files
/ASProviderMSOLAP: whether the MSOLAP provider can run in-process, default value 1 (enabled)
/FarmAccount: only for SPI_AS_NewFarm, domain user account for running SharePoint Central Administration services and other essential services in a farm
/FarmPassword: only for SPI_AS_NewFarm, password for the farm account, can be omitted when using a managed service account, virtual account, or built-in account
/Passphrase: only for SPI_AS_NewFarm, passphrase that is used to add additional application servers or Web front-end servers to a SharePoint farm
/FarmAdminIPort: only for SPI_AS_NewFarm, port used to connect to the SharePoint Central Administration web application
/BrowserSvcStartupType: startup mode for SQL Server Browser service, Automatic, 
Disabled or Manual
/EnableRANU: enables run-as credentials for SQL Server Express installations
/InstallSQLDataDir: data directory for SQL Server data files
/SAPwd: only when /SecurityMode=SQL, password for the SQL Server SA account
/SecurityMode=SQL: security mode for SQL Server; if not supplied it uses Windows-only authentication mode
/SqlBackupDir: directory for backup files
/SqlCollation: collation settings for SQL Server, to take full advantage of SQL Server features, change the default installation settings to use Windows collations; for example, for the OS locale English (United States) (code page 1252), the default collation during setup is SQL_Latin1_General_CP1_CI_AS and can be changed to its closest Windows collation counterpart Latin1_General_100_CI_AS_SC
/AddCurrentUserAsSqlAdmin: adds the current user to the SQL Server sysadmin fixed server role, true for SQL Server Express, false for all other editions
/SqlSvcAccount: startup account for the SQL Server service
/SqlSvcPassword: password for SqlSvcAccount, can be omitted when using a managed service account, virtual account, or built-in account
/SqlSvcStartupType: startup mode for the SQL Server service, Automatic, Disabled, Manual
/SqlSysAdminAccounts: only when not SQL Server Express, logins to be members of the sysadmin role
/SqlTempDBDir: directories for tempdb data files separated with a blank space
/SqlTempDBLogDir: directory for tempdb log file
/SqlTempDBFileCount: number of tempdb data files to be added by setup, can be up to the number of cores
/SqlTempDBFileSize: initial size of each tempdb data file
/SqlTempDBFileGrowth: file growth increment of each tempdb data file in MB
/SqlTempDBLogFileSize: initial size of each tempdb log file
/SqlTempDBLogFileGrowth: file growth increment of each tempdb log file in MB
/SqlUserDBDir: directory for the data files for user databases
/SqlSvcInstantFileInit: enables instant file initialization for performance, but you must consider security
/SqlUserDBLogDir: directory for the log files for user databases
/SqlMaxDOP: max degree of parallelism, which determines how many processors a single statement can utilize during the execution of a single statement
/UseSqlRecommendedMemoryLimits: will use calculated recommended values that align with the server memory configuration guidelines for a standalone SQL Server instance omitting /SqlMinMemory and /SqlMaxMemory
/SqlMinMemory: min Server memory configuration in MB
/SqlMaxMemory: max Server memory configuration in MB
/FileStreamLevel: access level for the FILESTREAM feature, 0=Disabled, 1=Enabled for Transact-SQL access, 2=Enabled for Transact-SQL and file I/O streaming access, 3=Allow remote clients to have streaming access to FILESTREAM data (Not valid for cluster scenarios)
/FileStreamShareName: name of the Windows share in which the FILESTREAM data will be stored
/ISSvcAccount: account for Integration Services
/ISSvcPassword: Integration Services password, can be omitted when using a managed service account, virtual account, or built-in account
/ISSvcStartupType: startup mode for the Integration Services service
/NPEnabled: state of the Named Pipes protocol for the SQL Server service, 0=Disabled, 1=Enabled
/TCPEnabled: state of the TCP protocol for the SQL Server service, 0=Disabled, 1=Enabled
/MRCacheDirectory: specify the Cache directory for Microsoft R Open, SQL Server 2016 R Services, SQL Server 2016 R Server (Standalone), or R feature support in SQL Server Machine Learning Services or Machine Learning Server (Standalone)
/SQL_INST_JAVA, /SQLJAVADIR: specifies installing Java with Language Extensions, if /SQLJAVADIR parameter not provided it's assumed you want to install the Zulu Open JRE that is provided by the installation media, otherwise indicates you would like to use an already-installed JRE or JDK
/FTUpgradeOption: Full-Text catalog upgrade option, REBUILD, RESET, IMPORT
/FailOverClusterGroup: name of the resource group to be used for the SQL Server Database Engine failover cluster, can be the name of an existing cluster group or the name of a new resource group
/FailOverClusterDisks: list of shared disks to be included in the SQL Server Database Engine failover cluster resource group
/FailOverClusterIPAddresses: semicolon-delimited encoded IP addresses separated with a space in between, format is <IP Type>;<address>;<network name>;<subnet mask>
/FailOverClusterNetworkName: used to identify the new SQL Server Database Engine failover cluster instance on the network
/ConfirmIPDependencyChange: consent to set the IP address resource dependency to OR for multi-subnet failover clusters
/FailoverClusterRollOwnership: 0 will not roll cluster ownership (move group) to upgraded nodes, and does not add this node to the list of possible owners of the SQL Server cluster at the end of upgrade, 1 will roll cluster ownership (move group) to upgraded nodes, and will add this node to the list of possible owners of the SQL Server cluster at the end of upgrade, 2 indicates that SQL Server Setup will manage cluster ownership (move group) as needed

Conclusion

Both installations are similar in many ways: they let you debug the installation process, choose the language, choose either Standard or Enterprise, choose the specific components to install, choose the file locations, enter the passwords to use, specify automatic memory management, and patch the software during the installation to reduce the overall time and avoid restarts. Now SQL Server comes with Machine Learning tools which you can take advantage of right away.

SQL Server Management Studio is a separate installer, while Oracle includes Enterprise Manager Express by default. In my personal opinion, when you install SQL Server you define the TempDB initial setup and very often forget about its performance and start creating databases non-related between them and without taking into account other instances installed in the same server, while in Oracle you manage the temporary tablespace individually per database and instead of databases you have schemas which are almost always interrelated.

Next Steps

You can create your own SQL Server container to test different configurations.
You can create your own Oracle container to test different configurations.
Having SQL Server and Oracle containers with similar characteristics, enables you to compare their performance under the same circumstances.
You can find additional information regarding SQL Server installation in a previous tip by Joe Gavin here.
You can find additional information regarding Oracle installation in Windows in the official documentation here.

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-01-05

Install SQL Server and Oracle using PowerShell and Windows Containers