Create a Database in SQL Server

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:

 

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 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.  

Install SQL Server Client Software

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:

 

 

 

 

 

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.   If we are running 64-bit Windows we download and install the 64-bit version, which will provide native client software that can be used both by 64-bit Manifold running in 64-bit Windows and also by 32-bit Manifold running in 64-bit Windows.   If we are running 32-bit Windows, we download and install the 32-bit package.

 

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.

Connect to SQL Server Express

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.

 

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.

Security

The best option for secure database connections is to use integrated security, as shown in this topic, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply.  When using integrated security, no passwords or other credentials appear within connection strings.  Most DBMS packages support the use of integrated security.

 

Other options should leverage the extensive security facilities already built into Window and databases.   If we do not use integrated security, but instead create a data source that uses login and password credentials we should take advantage of Windows security to protect those credentials.   It should go without saying that if we have access to a database in a particular role, we must know the password for the database user login that we will use.   We therefore should not fear using that password in plain text in the connection string if we have protected our credentials using the usual Windows security features.

 

For example, if we do not want other users on our computer to open the project and to use our access privileges to reach into the database, then we should Windows security features to ensure other users on the machine do not have permission to use the .map file.   If they have permissions to launch the .map file, it does not matter whether the password is stored in the connection string as plain text or in encrypted form: they still get the same level of access to the database when they connect through the data source using our credentials.  

 

Another way to enforce security is to save the connection to the database in a Windows DSN file as a user DSN.  User DSNs are specific to a particular user.   We then create a data source in Manifold by using File: dsn as the type in the New Data Source dialog, so that no connection strings are stored in the .map file.  Using a DSN has the advantage that we need only specify desired access to that one file to protect credentials, instead of to every .map project file we create.

 

 

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.

Create a Database

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.

Next Steps

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

 

Notes

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.

 

See Also

Schema

 

DBMS Data Sources - Notes

 

Project Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

Install MySQL

 

Install SQL Server

 

Open a Firewall Port for SQL Server

 

Enable TCP/IP for SQL Server

 

Connect to SQL Server

 

Install PostgreSQL

 

Install Oracle

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines