SQL Server Express Edition

At the time of writing, Microsoft provides at no charge a downloaded distribution of Express versions of SQL Server. Because it has been widely used in the past, this documentation uses Microsoft® SQL Server™ 2005 Express Edition as an example. Procedures for more recent versions of SQL Server Express are similar

 

Microsoft also available by download the newest editions of SQL Server, such as Microsoft® SQL Server™ 2008 Express Edition. SQL Server 2008 has built-in spatial capabilities. SQL Server 2005 does not, but can be used as a spatial DBMS using the free Manifold Spatial Extender for SQL Server.

 

Introduction

 

SQL Server 2005 Express Edition is derived from the same engine upon which Microsoft SQL Server 2005 is built. It is a newer and, in many key ways, better alternative to the SQL Server 2000 Desktop Engine (known as MSDE) distributed in earlier Manifold releases.

 

When delivered in SQL Server Express form, Microsoft has limited SQL Server in several ways:

 

§      A database cannot exceed 4 gigabytes in size.

§      SQL Server Express may be installed on a multiple CPU machine, but it will execute (run) only on a single processor or a single processor core if multi-core processors are used.

§      SQL Server Express may be installed on a server with any amount of memory, but will use only up to 1 GB of available RAM memory.

 

Other than these limitations SQL Server Express provides virtually the full power and breadth of SQL Server capabilities. Although MSDE was limited to no more than five users, SQL Server Express no longer has such an artificial throttle on performance. Within the limits of processor, RAM and maximum database size, SQL Server Express always runs at full speed.

 

Manifold documents use of SQL Server Express within Manifold applications for several purposes:

 

§      All Manifold System users can use SQL Server Express to create high performance databases in which tables, drawings, images and surfaces may be stored. Those tables, drawings, images and surfaces can then be imported or linked into Manifold projects. Users may also write scripts that exploit the power of SQL Server for the creation of databases and tables that may be used by their projects.

§      All Manifold System users can store drawings within SQL Server databases for concurrent, multi-user editing of drawings. If we have Manifold Database Administrator Edition we can configure SQL Server storage using the Administrator Console to enable user-friendly features such as friendly names, formatting storage for drawings and pre-set import and link options.

§      Manifold Enterprise Edition users can create Enterprise servers within SQL Server databases hosted by a SQL Server Express server.

§      Manifold Enterprise Edition users can create a spatial index for drawings within SQL Server Express databases to confer true spatial DBMS capability unto SQL Server Express. Once Enterprise Edition has been used to create a spatial index within a given SQL Server Express database, users of any Manifold System edition can take advantage of that spatial index, for example, to link objects from drawings within a given area of interest.

§      Manifold users may download at no charge the Manifold Spatial Extender for SQL Server that provides high performance spatial indices for SQL Server. Users working with SQL Server 2005 Express should always download and install the Manifold spatial extender.

§      Microsoft provides native spatial DBMS capability in SQL Server 2008. Manifold supports native SQL Server 2008 spatial functionality. Although Manifold generic spatial indices and the Manifold Spatial Extender for SQL Server will work with SQL Server 2008 spatial, if you have access to SQL Server 2008 spatial you should use that in preference to either generic spatial indices or the Manifold spatial extender.

 

Because SQL Server Express is the same as SQL Server except for the processor and RAM limitations and four-gigabyte database limit, if a Manifold application outgrows these limits it is very easy to scale upward by installing SQL Server. Usually when an application grows so large that it outgrows SQL Server Express limits the application is sufficiently important to merit the additional expense of procuring SQL Server.

 

This topic introduces SQL Server Express in the context of simple, default use with Enterprise Edition. If you do not have Enterprise Edition you will not have Enterprise features such as the Server Console. For more sophisticated configuration and usage of SQL Server Express, see the Microsoft documentation for SQL Server and SQL Server Express.

 

Installation and Configuration

 

The installation package currently downloadable from Microsoft installs SQL Server Express and configures it to start automatically on system startup. Install SQL Server Express and restart the system. SQL Server Express will appear as a SQL Server installation on the system.

 

Installing SQL Server Express Edition requires that we first install .NET Framework 2.0. The latest version of .NET Framework 2.0 available at the time of publication may be downloaded from Microsoft.

 

Installing SQL Server Express Edition:

 

1. In Windows Explorer, navigate into the folder into which you downloaded the installation files for SQL Server Express Edition and double-click the .EXE file.

2. In the End User License Agreement step of the installation, check "I accept the licensing terms and conditions" and click Next.

3. In the Installing Prerequisites step, click Install, wait until all prerequisite components are installed, and then click Next.

4. In the Welcome to the Microsoft SQL Server Installation Wizard step, click Next.

5. In the System Configuration Check step, inspect all generated warnings and click Next.

6. In the Registration Information step, click Next.

7. In the Feature Selection step, select the desired features and click Next. Default settings work OK.

8. In the Authentication Mode step, select Windows Authentication Mode and click Next. It is recommended to always use Windows Authentication Mode, unless there is a well-understood, significant reason not to.

9. In the Error and Usage Report Settings step, choose to automatically send error reports or feature use data to Microsoft, if you so desire, and click Next.

10. In the Ready to Install step, click Install.

11. In the Setup Progress step, wait until the installation completes, then click Next.

12. In the Completing Microsoft SQL Server 2005 Setup dialog, click Finish.

 

After the installation completes, you will be able to connect to the newly created instance of SQL Server using an administrative login from the machine on which SQL Server has been installed. You may want to configure the instance so it can be accessed by users who do not have administrative rights and / or users working on different machines.

 

Users planning on using SQL Server 2005 Express as a spatial DBMS should download and install the Manifold Spatial Extender for SQL Server onto the SQL Server machine.

 

While SQL Server configuration questions are outside of the scope of this Help topic, the following section provides an example of configuring an instance of SQL Server Express for use by ordinary users of a particular domain over a network. The following assumes execution right after completing the installation as shown above, using the same account which has administrative rights:

 

Allowing other machines to see an instance of SQL Server

 

1. Press the Start menu button and launch Microsoft SQL Server 2005 - Configuration Tools - SQL Server Configuration Manager.

2. In the tree on the left, select SQL Server 2005 Configuration Manager (Local) - SQL Server 2005 Network Configuration - Protocols for SQLEXPRESS. In the pane on the right, right click the TCP/IP item and select Enable. Click OK to dismiss the warning that the changes will not take effect until the SQL Server service is stopped and restarted.

3. In the tree on the left, select SQL Server 2005 Configuration Manager (Local) - SQL Native Client Configuration - Client Protocols. In the tree on the right, make sure the following items are enabled: Shared Memory, TCP/IP, Named Pipes. If any of the items is not enabled, right click it and select Enable.

4. In the tree on the left, select SQL Server Configuration Manager (Local) - SQL Server 2005 Services. In the pane on the right, right click the SQL Server Browser item and select Properties. In the SQL Server Browser Properties dialog, switch to the Service tab, make sure the Start Mode option is set to Automatic, then click OK. Right click the SQL Server Browser item again and select Start. Right click the SQL Server (SQLEXPRESS) item and select Restart.

5. Close SQL Server Configuration Manager.

6. If you have Windows Firewall turned on, turn it off. Alternatively, use the SQL Server Configuration Manager application to configure SQL Server to use specific TCP ports, then launch Control Panel - Windows Firewall, select the Exceptions tab, and specify the configured ports as exceptions.

 

(Allowing other users to connect to and use an instance of SQL Server)

 

7. Press the Start menu button and launch Command Prompt.

8. Execute the following command line:

 

SQLCMD -S (local)\SQLEXPRESS -E

 

9. Execute the following commands to create a new database and switch to it:

 

CREATE DATABASE test

GO

USE test

GO

 

10. Execute the following commands to allow users of the domain MYDOMAIN to connect to SQL Server and access the new database:

 

CREATE LOGIN [MYDOMAIN\Domain Users] FROM WINDOWS

GO

EXEC sp_grantdbaccess 'MYDOMAIN\Domain Users'

GO

 

11. Execute the following commands to assign one of the users of the domain MYDOMAIN as the owner of the new database:

 

EXEC sp_changedbowner 'MYDOMAIN\John'

GO

 

12. Execute the following commands to allow another user of the domain MYDOMAIN to write data to the new database:

 

EXEC sp_addrolemember 'db_ddladmin', 'MYDOMAIN\Jane'

EXEC sp_addrolemember 'db_datawriter', 'MYDOMAIN\Jane'

GO

 

13. Execute the following commands to allow all other users of the domain MYDOMAIN to read data from the new database:

 

EXEC sp_addrolemember 'db_datareader', 'MYDOMAIN\Domain Users'

GO

 

14. Execute the following command to exit SQL Server console:

 

EXIT

 

After following the above steps it is easy to test that a SQL Server Express installation is functional by using the Database Console in Manifold to quickly connect to SQL Server.

 

It is possible to connect using either ODBC, OLE DB or ADO .NET. We recommend using OLE DB for read-write connections and for all spatial DBMS work. We also recommend installing SQL Server Native Client on all client machines and then using the SQL Native Client provider. If we haven't installed the SQL Server Native Client we can connect to SQL Server using the default OLE DB Provider for SQL Server.

 

Connecting to a SQL Server Express Installation:

 

1. Launch Manifold and launch the Tools - Database Console command.

2. Press the [...] browse button to the right of the Data source box.

3. Follow the instructions in the Create a Data Source for SQL Server Express example section of the Data Source Dialog topic to create a data source for the desired SQL Server Express installation in the Data Source dialog. Double click on that data source to connect to it.

 

Once a data source for a SQL Server Express installation has been created the Data Source dialog will remember it. It will be available for subsequent uses with Database Console as well as with Administrator Console (if we have Database Administrator Edition installed).

 

Configuring SQL Server Express as an Enterprise Server

 

This section is for Manifold Enterprise Edition users. As discussed in the Data Storage Strategies topic, Enterprise Edition allows a simplified "shared" storage strategy that combines the benefits of file-based storage with those of full scale DBMS storage.

 

After installing SQL Server Express we can configure the SQL Server installation for use as an Enterprise Server.

 

To read (get) components shared on an Enterprise server, a user account must be allowed to connect to SQL Server, access the database, and be allowed to read data in the MFD_ROOT table that lists available components and data in the data tables for components stored in the server.

 

To read and write (get, check out, check in, undo check out, share, delete) components shared on an Enterprise server, a user account must be allowed to do all of the above and also to alter data in the MFD_ROOT table and in the data tables, as well as to create and drop tables.

 

If we install SQL Server Express as given above and if we have followed steps 1 through 14 above given in the section titled Allowing other machines to see an instance of SQL Server then the user accounts MYDOMAIN\John and MYDOMAIN\Jane will be able to use the database Test as an Enterprise server in read/write mode, and all other users of MYDOMAIN will be able to use it as an Enterprise server in read-only mode.

 

If we had installed SQL Server Express on a machine that is not part of any domain we could get the same result (John and Jane having read/write and everyone else read-only rights) by altering the procedure given above so that commands used in SQLCMD in steps 9 through 14 use BUILTIN instead of the domain name MYDOMAIN.

 

Using Database Console for Configuration

 

While it is certainly possible to configure SQL Server Express by using SQLCMD from the Windows Command Prompt on the machine on which it has been installed as illustrated above, as a practical matter it is frequently more convenient to configure it remotely by using Database Console.

 

Instead of logging in to the machine running an instance of SQL Server Express and launching SQLCMD we could log in to one of the machines that can connect to the server using a user account that has sufficient permissions on the instance of SQL Server Express (for example, a member of the sysadmin server role), launch Manifold, connect to SQL Server Express using Database Console and execute commands by typing them in the query pane and clicking the Run toolbar button. When executing a command in the query pane we do not have to use GO to signal the server that it is time to execute the command, since the Run button does that for us.

 

A quick and dirty way to allow all users of a particular domain to use a particular database as an Enterprise server in read/write mode is to make them all members of the built-in db_owner role for that database.

 

To do this via SQLCMD, we can execute the following commands (assuming the configuration above):

 

USE test

GO

EXEC sp_addrolemember 'db_owner', 'MYDOMAIN\Domain Users'

GO

 

Here are the equivalent commands for an instance of SQL Server on a machine that is not on a domain and with no configuration done previously via SQLCMD:

 

CREATE DATABASE test

GO

USE Test

GO

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS

EXEC sp_grantdbaccess 'BUILTIN\Users'

EXEC sp_addrolemember 'db_owner', 'BUILTIN\Users'

GO

 

Here is how we would do the above using Database Console instead of SQLCMD:

 

CREATE DATABASE test

 

(click the Run button)

 

USE Test

 

(click the Run button)

 

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS

EXEC sp_grantdbaccess 'BUILTIN\Users'

EXEC sp_addrolemember 'db_owner', 'BUILTIN\Users'

 

(click the Run button)

 

To remove a database from SQL Server Express, in SQLCMD use:

 

DROP DATABASE test

GO

 

Use with Enterprise Edition

 

Once SQL Server Express has been installed and a database has been created on the server we can use it as an Enterprise server. We can create an ODBC or OLE DB data source to connect to it as set forth in the Creating an Enterprise Server topic, or we can use shorthand Enterprise Edition syntax provided for a simplified connection to SQL Server databases.

 

Enterprise dialogs allow a simplified connection string syntax for SQL Server databases as an optional alternative to full ODBC syntax. "\\systemname" will connect to the default database on a given system. "\\systemname:database" connects to the specified database on the given system. The connection established with a simplified connection string that uses Windows integrated security (suitable for users working in Windows Server 2003, Windows XP and Windows 2000). Using simplified connection strings is a fast way to connect to SQL Server or to SQL Server Express using default options without dealing with the ODBC dialogs.

 

To connect to a non-default instance of SQL Server Express, expand systemname into systemname\instancename. Thus, connecting to \\myserver\sqlexpress:test will connect to a machine named mysystem, an instance of SQL Server Express on that machine named sqlexpress, and a database on that instance named test.

 

For example, if we have installed SQL Server on a system called PROJECTS and we have created a database called hydrography to use as our Enterprise server database, we could connect in the Server Console by entering \\projects:hydrography into the Server box of the Server Console and then pressing the Refresh button.

 

Sharing a Component into a SQL Server Express Enterprise server

 

1. Launch Manifold and open the project containing the component of interest.

2. Right click onto the component and choose Share.

3. Suppose the system hosting the SQL Server Express server is called PROJECTS and the database we created is called manifold. In the Share on box enter \\projects:manifold and press OK.

 

The Enterprise server will be created in the manifold database and the component will be shared into that server. Thereafter, whenever we want to work with that Enterprise server we can open the Server Console and in the Server box enter \\projects:manifold to work with the contents of that SQL Server Express server. Of course, if we work with this server on a regular basis Manifold will remember the last-used entries in the Server box for our convenience.

 

Using an Enterprise server with SQL Server Express

 

1. Launch Manifold and open a project.

2. Choose Tools - Server Console to launch the Server Console dialog that allows browsing Enterprise servers.

3. In the Server box, the data source connection string or data source name that was last used will appear. To see the contents of any Enterprise server, enter the data source name for that server (such as Example Enterprise server) into the Server box. Press the Refresh button at any time to see the updated contents of that server if you think there have been changes. When working with SQL Server Enterprise servers we can use simplified syntax, as in \\systemname:database

4. Click on the component desired and the toolbar buttons to Import or Link will be enabled.

5. Press Import to import the component into the project. This fetches a copy of the component from the Enterprise server into local project storage. Press Link to link the component into the project, leaving it stored within the Enterprise server and under control of the Enterprise server.

 

Example

 

Suppose we've just installed SQL Server Express on a machine called PROJECTS. We will create a database called counties and we will grant permissions to all users in our domain to access the database. Our Windows domain is called GISDEPT.

 

1. Launch Manifold and open Tools - Database Console. The Database Console uses the Data Source dialog to specify connections.

a. Click the [...] browse button to the right of the Data Source box to open the Data Source dialog. which is similar to the File - Open dialog. In the Files of type box choose OLE DB Data Sources (). This opens the Data Link Properties dialog.

b. Follow the instructions in the Create a Data Source for SQL Server Express example section of the Data Source Dialog topic to create a data source for the desired SQL Server Express installation in the Data Source dialog, using the Microsoft OLE DB Provider for SQL Server and connecting to the PROJECTS machine. Double click on that data source to connect to it.

2. In the command pane (the lower pane in the Database Console) enter the following command and press the Execute:

 

CREATE DATABASE counties

 

3. In the command pane enter the following four lines. After all four lines have been entered press the Execute button:

 

USE counties

EXEC sp_grantlogin 'GISDEPT\Domain Users'

EXEC sp_grantdbaccess 'GISDEPT\Domain Users'

EXEC sp_addrolemember 'db_owner', 'GISDEPT\Domain Users'

 

4. Close the Database Console. To populate the Enterprise server with components, right click on a component to be shared to the server and choose Share. In the Share dialog, enter the following into the Share on box:

 

\\projects:counties

 

5. The Enterprise server is now fully operational and populated with the shared component. Use \\projects:counties in the Server box of the Server Console to work with that Enterprise server.

 

Comments

 

Any machine can host an Enterprise server run on SQL Server Express databases. Although Microsoft refers to it as a "desktop" engine it is actually SQL Server in all its full power and elegance, excepting the limitations of CPU / RAM usage and four gigabytes per database. It has full networking capability as does SQL Server and will appear on the local network as a SQL Server installation. For most practical purposes of interest to Manifold users everything that may be done with SQL Server may be done with SQL Server Express as well.

 

If we install Enterprise Edition with SQL Server Express on a given machine, that machine can be both a client and a server to other machines on the network. For example, if we have a local area network with ten different machines we can have each of the ten be an Enterprise server for itself and for other machines on the network, even as the machines are simultaneously used as Manifold Enterprise Edition client workstations.

 

The short examples given above show one database created within a SQL Server Express server. We could create many databases within each SQL Server Express server and use them all simultaneously. Each database is used as a separate Enterprise server.

 

For example, suppose on the machine called PROJECTS we install a SQL Server Express server and then create databases called hydrography, boundaries, transportation and utilities. We could work with each of these in the Server Console as four different Enterprise servers using the shorthand connection syntax of \\projects:hydrography, \\projects:boundaries, \\projects:transportation and \\projects:utilities. We could include shared components from each of these in the same project.

 

Further Reading

 

Because SQL Server Express is, in fact, SQL Server there is a massive amount of reading material available to users who wish to learn more. In general, most documentation for SQL Server will apply to SQL Server Express so long as one keeps in mind the minor differences between SQL Server and SQL Server Express.

 

When searching the microsoft.com site for additional information, search for SQL Server 2008 Express Edition, which has superseded the earlier SQL Server 2005 product.

 

No Technical Support

 

Important: Manifold.net does not provide any support whatsoever for installation, administration, management, configuration or use of the DBMS products provided by Microsoft. See the Database Installations topic for details.

 

See Also

 

Database Administrator Edition

Database Console

Database Installations

Enterprise Edition