Microsoft SQL Server, often simply called SQL Server for short, is one of the three most popular enterprise-class DBMS packages. SQL Server is a high quality, genuinely enterprise-class DBMS that is both well known and well respected.
SQL Server is pronounced as "Sequel Server," while the language SQL itself is pronounced "Ess Cue Ell", saying the letters.
Microsoft provides SQL Server in various editions, including a free edition called Microsoft SQL Server 2017 Express, which we call SQL Server Express for short in this documentation. SQL Server Express may be deployed in production and used at no charge, including commercial use if desired. Another free edition is SQL Server 2017 Developer, which is a full featured version of SQL Server, but which is licensed only for use as a development and test database in a non-production environment. This documentation, therefore, uses SQL Server Express as an example.
The main page for SQL Server downloads, including the free SQL Server 2017 Developer edition, at the time of this writing is at https://www.microsoft.com/en-us/sql-server/sql-server-downloads with the main page for different SQL Server 2017 Express versions at https://www.microsoft.com/en-us/sql-server/sql-server-editions-express
SQL Server Express is free, but it does come with significant limitations compared to other SQL Server editions:
Main memory - The database engine can use a maximum of 1 GB of main memory.
Database size - 10 GB is the maximum size of each database. However, SQL Server Express can host up to 32,767 databases.
No Agent - SQL Agent, a background tool used to automate administrative tasks, is not included in SQL Server Express.
Buffer cache - Buffer cache for each SQL Server Express instance is 1 MB of memory.
1 Socket / 4 Cores - SQL Server Express can only utilize either 4 CPU cores or one CPU socket, whichever is less.
However, SQL Server Express imposes no limit to the number of simultaneous users, and SQL Server Express includes native spatial data support.
As a practical matter, the above limitations will mean that for GIS use SQL Server Express is limited primarily by the 10 GB limit on database size. We can chop up projects into multiple databases to store more than 10 GB overall, but that becomes inconvenient compared to using, say, MySQL, which has no such limits. The limit to 1 GB of main memory will also impact performance when working with individual objects (and hence the geometry field size of individual records) that exceed the 1 GB main memory maximum.
However, given that most Microsoft applications with which we might interchange using SQL Server as a common data store will be totally clobbered long before they reach 1 GB per object, it is unlikely we would be interchanging such large objects with a Microsoft application in any event.
This topic is the first step in a five topic sequence showing a complete installation and then utilization of SQL Server 2017 Express from the very beginning, using a simplified approach intended to facilitate experimentation by individuals. A full enterprise installation should be undertaken by experts, to insure all required security facilities are correctly configured. The examples these topics show, especially in the last topic in the sequence, will be useful for working with an existing SQL Server installation our organization may have.
There is no requirement to install
SQL Server or any other DBMS for using Manifold. Manifold includes
as built-in capabilities everything you need to do Manifold. In
general, storing GIS data within Manifold .map project
files will be faster, and will deliver higher performance with greater
ease of use and more capabilities than storing GIS data within SQL Server
or some other DBMS.
So why install or use SQL Server? Manifold users often use SQL Server or any other DBMS for three reasons:
Access to data - Organizations often use enterprise-class DBMS packages to store data. Being able to reach into organizational data provides tremendous access to data that can be combined with geospatial data in Manifold or in other systems, or to simply get access to a seemingly infinite range of data of all kinds in tables. Topics showing how to connect to a given database teach us how to access that data so we can use it.
Capabilities not in Manifold - While Manifold generally provides far broader geospatial capabilities than any DBMS, big DBMS packages such as SQL Server have very many capabilities evolved over many years, including capabilities not found in Manifold. By running server-side queries we can take advantage of those capabilities.
Alternative data storage - Whatever storage infrastructure has been invented, there is a database that uses it. Connecting to different DBMS packages allows Manifold users to store their data in whatever data technology they like, such as cloud storage, massively distributed storage, and memory resident storage.
Interchange with other applications - There are many other applications which can interact with data stored in a SQL Server database. Using SQL Server as a common data store allows easy interchange of data between those applications.
Multiuser, simultaneous use of GIS data - Manifold .map files at the present time do not allow simultaneous access by multiple Manifold sessions. If a .map file is open in one Manifold session it cannot be opened in another. If we require simultaneous access to data by multiple Manifold sessions, such as might be run by different people on different machines, we can store the data in a DBMS, such as PostgreSQL, Oracle, MySQL, SQL Server, DB2, or other DBMS.
Disclaimer:
These topics were accurate when written, using the versions of installation
software indicated. Third party packages can and do change, so this
topic may be out-of-date. It is provided as an example of how such
installations can be approached.
Installing SQL Server and readying it for use in typical installations involves five steps:
Install SQL Server
Open a Firewall Port for SQL Server
Create a Database in SQL Server
This is the first topic in the above sequence of steps.
We are using a 64-bit Windows 10 system to which we have connected via Remote Desktop (RDP). This entire topic was conducted on that machine via RDP. We have downloaded the Microsoft installation package for SQL Server 2017 express from https://www.microsoft.com/en-us/download/details.aspx?id=55994 - the file we have downloaded and have now launched is called SQLServer2017-SSEI-Expr.exe. When launched, that installation package downloads a collection of installation files approximately 364 MB in size.
We right-click on the installation file and choose Run as administrator.
We choose the Basic installation.
Accept the license.
We accept the default installation location. Press Install.
The installer begins downloading installation files through the web, providing useful tips as it does so.
When all downloads are accomplished and unpacked into an installation folder, the installation program begins installing them.
When installation is completed, the dialog provides useful information on a connection string to use, which cites localhost\SQLEXPRESS as the name of the server, and master as the name of the default database. The installation resources folder at C:\Program Files\Microsoft SQL Server\140\SSEI\Resources is a key folder we will need to visit to read about further steps required for SQL Server configuration, as accomplished in the Open a Firewall Port for SQL Server and Enable TCP/IP for SQL Server topics.
We press Customize to see if there are any updates.
We can do much more with the Customize dialogs, but for this example we want to see only the first step, where the Customize dialogs begin by checking if there are any updates to the version of SQL Server we have just installed. There are none, so we press Cancel.
Back in the installation dialog, we press Install SSMS to launch a browser window loaded with the download page for the SQL Server Management Studio (SSMS) application. The button does not actually install anything: it simply launches a page from which we can download the SMSS install package.
We press Close.
Using the web page opened in the previous section, at https://docs.microsoft.com/sql/ssms/download-sql-server-management-studio-ssms , we download the 826 MB installation package for SQL Server Management Studio 17.9.1, called SSMS-Setup-ENU.exe.
We right-click on that installation file and choose Run as Administrator to launch installation of SSMS. Strictly speaking, we do not need to download and install SQL Server Management Studio, but if we operate a SQL Server installation it is only a matter of time before we will want to turn to SSMS for various routine administrative tasks. Therefore, we show the installation of SSMS in this topic even though this sequence of topics on SQL Server does not use SSMS.
Press Install.
After the package installs, press Close.
We have accomplished the raw installation of SQL Server Express, and we now have an operating SQL Server Express server running on our machine. We also have SQL Server Management Studio installed in case of need.
However, so far our SQL Server Express installation only operates on the local machine on which it has been installed. We have yet to alter the SQL Server Express configuration to allow use of SQL Server Express through the network. That comes next in two steps, first, opening a firewall port and then, second, enabling use of TCP/IP by SQL Server Express.
Continue this case study on installing SQL Server Express with the Open a Firewall Port for SQL Server topic.
Limits on Simultaneous Users? - SQL Server Express does not limit the number of concurrent connections, but the Windows system on which it is installed may limit the number of concurrent connections. For example, if we install SQL Server Express on a Windows 10 machine we will be limited by Windows, and not by SQL Server Express, to a maximum of 20 connections. If we install on a Windows Server machine we can have more connections.
File - Create - New Data Source
Open a Firewall Port for SQL Server
Create a Database in SQL Server
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines