Set up SQL SSEI & SSMS for Offline Support of Microsoft Dynamics 365

Description: This document provides instructions and walks you through setting up SQL Server 19 to support Offline Processing for Microsoft D365. These steps must be performed to ensure that offline processing is properly set up for your device. 

---

Installing Microsoft SQL Server 2019 Express through CMD

For best practices of offline processing in Microsoft D365, it is recommended to set up a specific dedicated instance of SQLServer Express. If you already have an SQL Database you are planning to use, you should skim this step to see how the CMD Installation process works and run the setup to rejoin us in Step 2. Otherwise, follow the steps below:

1. Go to the Microsoft SQL Server 2019 Express Installer page and download SQL Server 2019 Express. 
2. Double-click the file that you just downloaded to start the installer, then press Download Media.
   
3. Select Express Advanced, then select Download. Note the download path, as we will be using that shortly.
   
4. Follow the file path, then select and install the new SQLEXPRADV_x64_ENU.exe file. It will automatically begin extracting the file.
5. Close out of the automated installation package window that pops up. We are not going to be installing using that installer. We will be installing using the command prompt app.
6. Search for the Command Prompt app on your device. Right-click to open the dialogue menu, and select Open as Administrator.
7. With the Command Prompt opened as an Administrator, it should start you on the command line: C:\Windows\System32> you are going to want to change this so that we may access the file you installed previously. Type cd then copy with exact case sensitivity the download path that was used in step 3. For example, cd C:\Users\POSAdmin\Downloads; then add the name of the installation folder that was just extracted in step 4, so it should look like:
   cd C:\Users\POSAdmin\Downloads\SQLEXPRADV_x64_ENU
8. Next, with the window properly showing C:\Users\<Your Username>\Downloads\SQLEXPRADV_x64_ENU or the equivalent file path leading to SQLEXPRADV_x64_ENU type the following: SETUP.EXE /UIMODE=Normal /ACTION=INSTALL and press Enter when ready to submit.
   

We are doing this from the command prompt with these additional commands so that it will run the installer and record our choices to generate a file needed later in this installation.

Running Installation Package

1. After this, the installer will display. Press Next until you reach Installation Type. Here, you may select either to modify your existing SQL Database or install a new one. Press Next when done.
2. Specify Express under "Select a free edition," then press Next.
3. Checkmark the box next to "I accept the license terms and Privacy Statement" then press Next.
4. Select Database Engine Services and Full-Text and Semantic Extractions for Search, and specify where you want this instance’s files to be stored by selecting the ellipses (...) to the right of the Instance root directory and then press Next.
   
5. Next, it will ask us to choose our SQLExpress name, the default SQLEXPRESS will be fine as long as there are not multiple SQL Instances on the device. Press Next.
   
6. Afterwards, in Database Engine Configuration, we will want to ensure that it is set to Mixed mode. Then, enter your password and confirm it, then press Next (default=Shift4).
7. Next, the Ready to Install tab will show a summary of what will be installed and updated. Copy the configuration file path at the bottom and paste it into your notes. Then, using File Explorer, find and open that file path up until the configuration file. Press Cancel on the installation and confirm you want to cancel the installation.
8. Once you find the configuration file at the end of the installer's file path, copy it into your SQL Installation folder. Then right-click on the file, press Open with… and select Notepad. 
9. Once the configuration file is opened with the Notepad app, we are going to be editing the document for us to use. We are going to want to make the following changes:
   1. At the top of the configuration file, change IACCEPTPYTHONLICENSETERMS="False" to IACCEPTPYTHONLICENSETERMS="True"
   2. Beneath that line, on line 18, change IACCEPTROPENLICENSETERMS="False" to IACCEPTROPENLICENSETERMS="True"
   3. On line 30, ensure QUIET=”False” - This makes the installer run without a UI, making the installer run completely in the background without displaying progress. 
   4. On line 34, set QUIETSIMPLE=”True” - This allows the installer to run with a minimal UI that the user can not interact with but displays the progress of the installation. We will be using this rather than the normal installer.
   5. After setting QuietSimple to True, press Enter twice to get yourself on line 36, then type IACCEPTSQLSERVERLICENSETERMS=”True” 
   6. Go to line 40, or find the line with UIMode = Normal and put a semicolon ( ; ) in front of UIMode so it looks like ;UIMode = Normal (This is to allow us to run the installation in Quiet or QuietSimple modes).
   7. Press Save and then Close the configuration file.
   8. Afterward, open up a command prompt as an Administrator once more.
   9. With the command prompt, we’re going to be going back to the SETUP.EXE file we used in Step 1.7, so type cd C:\Users\<Your Username>\Downloads\SQLEXPRADV_x64_ENU or the equivalent file path leading to SQLEXPRADV_x64_ENU then press Enter. 
   10. Once the command prompt command line shows C:\Users\<Your Username>\Downloads\SQLEXPRADV_x64_ENU (or your equivalent for installing SQL), then type Setup.exe /Configurationfile-Configurationfile.ini /SAPWD=”OfflineTT” 
   11. A popup will appear, and show the progress of the installation, once complete, it will automatically close, SQL Server Express 2019 has been installed.
       

Installing SQL Server Management Studio

1. After installing SQL Server Express 2019, we will need to install SQL Server Management Studio. If you already have SQL Server Management Studio installed, you may skip this step. 
2. Go to the Microsoft SQL Server Management Studio Download Page and install the package there 
3. Run the SQL Server Management Studio installer from your downloads folder. 
4. Once finished, open SQL Server Management Studio, ensure the right server name is shown, and press Connect.
   
5. You should now be able to successfully connect and manipulate the offline database for Microsoft Dynamics 365 Store Commerce app.