Microsoft SQL Server, often simply called SQL Server for short, is one of the three most popular enterprise-class DBMS packages. This topic is the fourth step in a five topic sequence showing a complete installation and then utilization of SQL Server 2017 Express from the very beginning. Please see the Install SQL Server topic for basic info on SQL Server.
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:
Open a Firewall Port for SQL Server
Create a Database in SQL Server
This is the fourth topic in the above sequence of steps.
In prior topics, we have enabled network use of SQL Server by opening a port in the firewall on the server machine, and by enabling use of TCP/IP by SQL Server. We will now connect to the SQL Server installation from a remote machine. We will use Manifold, and issue commands in Manifold that will create a database in our SQL Server Express installation. We could use SQL Server Management Studio on the local machine to do that, but since it is more convenient to use Manifold from our desktop machine, connecting through the network to the SQL Server Express machine, we will use Manifold.
Every machine running Manifold which
will be used to connect to SQL Server must
have a Microsoft SQL Server client driver installed. Manifold uses
that Microsoft client software to connect to SQL Server. If
we want to connect to our SQL Server installation from a remote machine
running Manifold, we must install
SQL Server client driver on that remote machine. That is easy to
do, and Manifold can utilize all three of the usual options for SQL Server
client software that are used:
Install the newest SQL Server driver - The newest SQL Server driver as of this writing is named MSOLEDBSQL. This driver appeared fairly recently as a result of Microsoft un-deprecating OLE DB, and is recommended for all modern uses of SQL Server. The current latest version of the driver is available from: https://www.microsoft.com/en-us/download/details.aspx?id=56730
Using the newest SQL Server driver is best. A second best choice is installing Microsoft SQL Server Native Client software. That sounds appealing, but the latest version of the Native Client software as of this writing is the 2012 version of the native client, significantly older than more recent MSOLEDBSQL driver. However, the 2012 Native Client continues to work with later SQL Server editions, such as SQL Server 2017 as illustrated in this topic. The 2012 Native Client package may be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=50402.
The Native Client page provides two downloads, one for 32-bit Windows and one for 64-bit Windows. Download and install the 64-bit version, which will provide native client software that can be used by 64-bit Manifold running in 64-bit Windows.
Although the ideal way to connect to SQL Server is to install the newest SQL Server driver, or the Native Client, Manifold can also connect to SQL Server using the old SQL Server driver, SQLOLEDB, if that has been installed. That allows working with SQL Server on systems which do not have the latest driver, MSOLEDBSQL, or newer drivers from SQL Server Native Client, such as SQLNCLI10 or SQLNCLI11.
Launch Manifold.
Choose File - Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name, SQL Server, and choose Database: sqlserver as the type. Click the browse [...] button to launch the Database Login dialog.
Enter 192.168.2.44.\SQLEXPRESS,1433 as the name of the server. In the case of this example, the IP address of the machine on which SQL Server was installed is 192.168.2.44. For the database, we choose master.
If we were running Manifold on the same machine as the SQL Server machine, we could connect using localhost\SQLEXPRESS as the name of the server.
We do not check Use login and password, since our SQL Server installation does not require a login name and password.
It is a good idea to check for errors by pressing the Test button.
If that does not report a connection, we should fix any mistakes. When we see Connection established we press OK.
Back in the Database Login dialog, press OK.
Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.
A new data source called SQL Server appears in our Project pane. That data source is a connection to our SQL Server installation. We can expand it to see the various SQL Server system tables it contains. We now will issue a command to that data source.
We often use the word database casually, as a synonym for Database Management System (DBMS), or, in the case of file databases, in the sense of connecting to a particular database file, such as a particular GPKG database file or a particular Microsoft Access database .mdb file.
In this topic we use the special SQL Server meaning of database, to mean a collection of tables, queries and other infrastructure grouped together under a name within a particular SQL Server DBMS installation. When connecting to a SQL Server installation, SQL Server allows us to specify the particular database within that server to which we want to connect. If we do not specify a particular database, we will simply connect to the overall master database by default.
We could, in theory, connect to and use the default master database without ever creating specific databases. But that would be disorderly, somewhat like keeping hundreds of files in a Windows system in the root of the C: drive without ever organizing those hundreds of files into sensible folders. It is much more orderly and efficient to create one or more databases within which we group tables and other components. For example, within our SQL Server installation we might create a database called gisdb within which we will keep all of our GIS data.
We also create a database so we can take advantage of how Microsoft's free version of SQL Server, SQL Server Express, allows up to 10 GB storage within each database. If we saved everything within the default master database, we would be limited to only 10 GB. If we create multiple databases in which we store our work we can have up to 10 GB in each databases.
In the Project pane we right-click on the SQL Server data source and choose New Command Window.
That launches a Command Window that operates in the context of the SQL Server data source, that is, within the SQL Server installation. Whatever SQL and other commands we enter into that command window will be executed within the SQL Server installation using the native SQL of SQL Server.
For example, we can enter the SQL statement
SELECT @@VERSION;
And then we press the ! button to run the query.
The result reports the version.
We enter the command
CREATE DATABASE gisdb;
And then we press the ! button to run the command.
We have now created a new database, called gisdb, within the SQL Server installation. In the next topic, we look at an example using the gisdb database we have created.
Continue this case study on installing SQL Server with the Connect to SQL Server topic.
See the preceding topic: Enable TCP/IP for SQL Server
Jump to the beginning of the case study: Install SQL Server
Deleting a database - If we make a typographical error when creating a database or if we simply want to delete a database we used for experimentation, we can open a command window on the SQL Server data source and enter a command such as:
DROP DATABASE gisdb;
That will instantly delete the database and all it contains. SQL Server is designed for experts, so when issuing commands like that there are no confirmation dialogs: give a command to drop a database and SQL Server immediately and irreversibly deletes that database. Handle with care.
File - Create - New Data Source
Open a Firewall Port for SQL Server
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines