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 final 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
Connect to SQL Server
This is the last topic in the above sequence of steps.
So far, we have installed SQL Server on our target machine. We enabled use of SQL Server through networks. We have created a database within SQL Server called gisdb. Now, we will use that database to show an example of copying and pasting data from Manifold into SQL Server.
SQL Server must be running on the server
machine. If we cannot drag and drop components into the SQL
Server database, check to make sure the SQL Server DBMS is running. Login
to the server machine (perhaps using Remote Desktop), launch the Sql Server Configuration Manager
applet and see if the SQL Server (SQLEXPRESS) service is Running.
If not, right-click
onto that service and choose Start
to start it manually.
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, as discussed in the Create a Database in SQL
Server topic.
If we have teleported into this topic without reading the preceding topics in this case study, we should make sure to install client software as illustrated in the Create a Database in SQL Server topic.
We continue from the end of the Create a Database in SQL Server topic. We right-click into the Project pane to create a new data source.
Right-click into the Manifold part of the Project pane and choose 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 gisdb, and choose Database: sqlserver as the type. Click the browse [...] button to launch the Database Login dialog.
Enter the server name using the IP address of the server, which in this example is 192.168.2.44. The server name, appending the name of the SQL Server Express instance and the port, is
92.168.2.44\SQLEXPRESS,1433
We do not check Use login and password, since our SQL Server installation does not require a login name and password.
We enter gisdb as the database to which we will connect with this data source.
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 gisdb appears in our Project pane. That data source is a connection to the database called gisdb within our SQL Server DBMS.
We can expand the data source and then expand the System Data folder to see the virtual tables added by Manifold. In addition to the usual mfd_meta and mfd_root tables in any project or data source, two additional tables appear called mfd_collate, which provides the collations available in the database, and mfd_srid, which provides the spatial reference identifiers (SRIDs) available within the database that identify projections.
We double-click open the mfd_srid table to see the coordinate systems for which SRID codes are available. Many of these are taken from EPSG. The table uses gray background color because it is a report of SRIDs the database provides and is read-only.
We double-click open the mfd_collations table to see the collations available in the DBMS. The table uses gray background color because it is a report of collations the database provides and is read-only.
Note from the illustrations above we have two data sources simultaneously active into the same remote SQL Server installation. One of them is a data source connected to the master database. The other is a data source connected to the gisdb database. If we like, we can create additional databases in the SQL Server installation and create additional data sources connected to those additional databases. We can save those data source as Favorite Data Sources so they are available to us with one-click convenience. If we save a project with multiple data sources in it, when we next open that project those data sources will still be there, ready for use.
Using multiple data sources within the same SQL Server Express installation is a handy way of getting around the 10 GB limitation on each database's size. If we have three databases in SQL Server Express, each of the three can be up to 10 GB in size for a total of 30 GB stored in the SQL Server Express installation.
We can load the DBMS with components from Manifold projects by simply copying and pasting them into the DBMS data source, or even more simply, using drag and drop. We can also copy data from the DBMS by simply copying from the DBMS data source and pasting into the Manifold project.
First, we clean up our display a bit. We close the two tables that we opened, and then we click the SQL Server data source to highlight it and we press the Delete button to delete it. This simply removes it from our project and does not delete anything in the SQL Server Express DBMS.
Next, we open another Manifold session (not illustrated) in which we have stored some drawings of Mexico using different projections. We copy and paste those into our working session, as seen above. We have pasted two drawings, each with its table. One drawing shows Mexico using Latitude / Longitude projection, and the other shows Mexico using Pseudo-Mercator projection.
In the illustration above, we have opened the Latitude / Longitude drawing, named MexicoLL.
To copy that drawing and its table into our SQL Server database, we ctrl-click on MexicoLL and MexicoLL Table, to highlight them...
...and then we drag and drop the highlighted items into the SQL Server gisdb portion of the project.
The new components appear in the SQL Server database, with their names automatically converted into SQL Server names and some new, virtual components, such as drawings added. In the illustration above, we have closed the MexicoLL drawing previously opened.
To provide a consistent, orderly, user interface, Manifold will automatically generate virtual components, such as folders, some Manifold system tables, and some automatically-generated drawings, that appear to be in the database but which are generated on the fly from metadata and are not a new, real table or other new item added to the database. See the Real and Virtual Components topic for discussion and examples.
We double-click the dbo.MexicoLL drawing within the SQL Server Express database to open it. It opens, still retaining the Style with which it was formatted within Manifold. Copying drawings and their tables into SQL Server saves the style those drawings use as well. See the Note at the end of this topic for a discussion of how normalization can avoid any strange effects in the SQL Server version of a drawing that may be different from the drawing as seen in Manifold.
We can switch to the Component tab of the Info pane to get information on the drawing we have opened. Manifold has automatically specified the coordinate system within SQL Server as required by SQL Server.
We can copy another drawing into our SQL Server database by ctrl-clicking on MexicoPM and MexicoPM Table to highlight them, and then we drag and drop the highlighted items into the SQL Server gisdb portion of the project. In the illustration above, we have closed the dbo.MexicoLL drawing previously opened.
We double-click on the dbo.MexicoPM drawing within the SQL Server gisdb data source to open that drawing. It opens, also still retaining the Style with which it was formatted within Manifold.
The Component tab of the Info pane shows information on the dbo.MexicoPM drawing. Manifold has automatically specified the coordinate system within SQL Server as required by SQL Server.
If we compare the above illustration to the analogous illustrations for the Info pane of the gisdb.MexicoPM in the Connect to MySQL topic, and the public.MexicoPM drawing in the Connect to PostgreSQL topic, we see a difference between how MySQL and SQL Server name indexes and how PostgreSQL names indexes. SQL Server and My SQL require that an index name be unique within a table, but they both allow the same index name to be used in different tables. If we have an index called Geom_x in one table we are free to use that same name for an index, Geom_x in different tables. That is the same as in Manifold, so there is no need to rename an index when copying multiple tables to MySQL or to SQL Server that use the same name for an index. Our drawing as seen above therefore uses the same Geom_x name for the spatial index that most Manifold drawings use by default.
PostgreSQL is different in that PostgreSQL requires index names to be unique throughout an entire database. If one table in the database uses Geom_x as an index name, then no other table in the database can use Geom_x as a name for an index. When Manifold copies multiple tables to PostgreSQL, if an index name is already used by one of those tables, Manifold will, on the fly, synthesize a new index name that does not conflict, automatically adjusting references to that index name in other components.
We can work with components, like drawings and tables, saved into the database as if they were local components stored within our project. So far, we have just opened and viewed the dbo.MexicoLL drawing and the dbo.MexicoPM drawing, but we can do more than viewing. We can work with them as if they were resident within the project.
For example, we can Alt-click the province of Durango in the dbo.MexicoPM drawing and immediately the attributes will appear in the Info pane. We can click any segment or handle in that province to enable it for editing with the Coordinates tab, just as if it were a local drawing.
Or we can click on the Coordinates tab to show the coordinates, and thus enable the object for editing.
If we Ctrl-click the province of Chihuahua, it will immediately be selected and appear in red selection color, just like it would in a local drawing.
Open the drawing's table in the database, dbo.MexicoPM table, and we see that the corresponding record for Chihuahua is also selected and shown in red selection color, just as it would be with a local drawing's table.
We have finished the last topic in the case study on installing SQL Server.
See the preceding topic: Create a Database in SQL Server
Jump to the beginning of the case study: Install SQL Server
Normalize before copying into SQL Server - Manifold copies data into a closely-supported DBMS such as SQL Server in a way that causes geometry and other data to be translated from native Manifold data types into corresponding, and hopefully equivalent, data types within SQL Server. While normally very successful, the translation of data types from Manifold into corresponding SQL Server types may cause strange effects in the case of geometry. Such effects are rare, but are possible, for example, when a drawing containing areas is copied into SQL Server and some of the areas appear in the SQL Server version of the drawing as boundary lines. Such effects arise due to different geometry normalization within SQL Server when presented with a non-normalized object from Manifold. They can be avoided by normalizing objects within a drawing, using the Topology: clean (generalize) transform template for geometry fields, before exporting the drawing to SQL Server. The normalization process will simplify geometry from difficult edge cases to more normal form that is easier for SQL Server to digest in a simple way.
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