Nội Dung Chính

Install SQL Server

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 Limitations

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.

Using SQL Server

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

Enable
TCP/IP for SQL Server

Create
a Database in SQL Server

Connect
to SQL Server

This is the first topic in the above sequence of steps.

Download and Launch SQL Server Installation Package

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.

Install SQL Server Management Studio (Optional)

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.

Next Steps

Continue this case study on installing SQL Server Express with the Open
a Firewall Port for SQL Server topic.

Notes

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.

Install SQL Server

Install SQL Server

SQL Server Express Limitations

Using SQL Server

Download and Launch SQL Server Installation Package

Install SQL Server Management Studio (Optional)

Next Steps

Notes

See Also