SQL Server Setup and Configuration
TicketDesk is designed for wide compatibility with most editions of SQL Server, version 2008 or newer. This includes Express, Express LocalDB, Standard, Web, Enterprise, and DataCenter editions.
TicketDesk is not compatible with SQL Server Compact Edition (SQL CE).
Azure SQL Server is covered in a separate location here
If SQL CE support is desired it is not terribly difficult to modify the source code for SQL CE compatibility, but you will need to modify the Entity Model classes in some cases, and generate new Entity Framework Migrations for SQL CE.
Connection strings for SQL Server are a very common source of trouble for many admins, and problems related to connection strings are the most commonly discussed topic in the online discussions.
The connection string is also the only setting that requires manual editing in the site's configuration files. If not set correctly, nothing else in TicketDesk will work.
While this isn't a comprehensive guide to connection strings, we will cover the most common scenarios for TicketDesk here.
An excellent resource for information about connection strings and the various parameters can be found at connectionstrings.com
User-Instance Database with SQL Express 2014 LocalDB
connectionString="data source=(LocalDb)\MSSQLLocalDB;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
User-Instance Database with SQL Express 2012 LocalDB
connectionString="data source=(LocalDb)\v11.0;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
User-Instance Database with SQL 2008 Express & SQL 2008 R2 Express
connectionString="data source=.\SQLExpress;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
SQL Logins with SQL 2014, 2012, 2008 R2 or 2008 - Express, Standard, Enterprise, etc.
connectionString="data source=server/instance;database=TicketDesk;user id=TicketDeskUser;password=TicketDeskUserPwd;
Trusted Connections with SQL 2014, 2012, 2008 R2 or 2008 - Express, Standard, Enterprise, etc.
connectionString=data source=server/instance;database=TicketDesk;trusted_connection=True"
LocalDB, SQL Express 2008, and SQL Express 2008 R2 support user instance databases, which are sometimes called a 'File based Database'.
What this means is that when the web application starts up, it will attach a database file to SQL Server. When the web application shuts down, it will detach that database file automatically. In web applications, the file is physically stored within the application's file system (in the /app_data folder).
User instance databases are great during development. You start your site, the database is automatically attached, and when you shut down it goes away; no mucking about with SQL management tools or configurating windows services. But user instance databases run in an applcation process instead of as a windows service. Since Windows server is optimized to favor services, you will notice a performance decrease on production servers that isn't apparent when using this kind of databae on a developer's workstation. There are also limits on how much data they can hold, and not all of SQL Servers features are supported with user instance DBs.
Fortunately, you can copy a user instace database file to regular SQL server instances, and attach them to any edition of SQL server. Physically, the database file is 100% compatible with regular SQL servers, making upgrades to more powerful SQL editions simple and easy.
User Instance databases are supported only by a few very specific SQL Server editions.
- SQL Server 2008 Express
- SQL Server 2008 Express R2
- SQL Server 2012 Express LocalDB
- SQL Server 2014 Express LocalDB
SQL Server 2012 Express and SQL Server 2014 Express DO NOT support user instance databases.
This is a common point of confusion. SQL Express 2005 and 2008 supported both server managed databases, as well as user instance databases.
Because Microsoft hates you, they decided in 2012 to split regular server managed databases from user instance databases into two different products.
Starting with SQL 2012, user instances databases were moved to a new SQL edition called 'LocalDB', while regular SQL Express would support only server managed databases. To further confuse people, they decided at the last minute to tack on the word "express" to the product name for both editions.
Connection strings for user instance databases will include the AttachDBFilename parameter, which identifies where to find the file to attach. The |DataDirectory| portion of the path will map to the /app_data folder within the web application. You can also specify UNC or full local file paths.
User instance databases only work when an appropriate instance of SQL is installed on the web server. Your application cannot connect to user instance databases that are located on a different physical server.
Another common issue people encounter is that SQL Server does not enable TCP/IP by default. You will need to explicitly enable it using the SQL Configuration Manager tool.
To enable TCP/IP follow these steps:
- Open the SQL Server Configuration Manager (see image below)
- Expand SQL Server Network Configuration, then click 'Protocols for [instance name]'
- In the details pane, click TCP/IP and then change Enabled to 'Yes'
if you have 32 bit versions of SQL server installed, repeat these steps uner the 'SQL Server Network Configuration (32bit) node
If you have SQL Server 2014 installed along side any previous edition of SQL Server, then there will be two SQL Configuration Manager tools; one specifically for 2014, and the other for all earlier versions. If this is the case, you will need to enable TCP/IP in BOTH tools, though the steps are nearly identical in both cases.
You may need to enable TCP/IP even if you are running the Database and IIS servers on the same machine.
You should not need to change any other settings or specify IP addresses or specific ports in most cases
If you have multiple instances of SQL Server on the same machine, you should also enable the SQL browser service. This service is installed along with SQL Server, but is not enabled by default. The SQL browser brokers connections between multiple SQL instances. If you don't enable it, you will need to specify different IP addresses and/or ports for each SQL instance.
To enable the service:
- Open the SQL Server configuration manager (see image below)
- Choose the 'SQL Server Services' node
- In the details pane, right-click SQL Server Browser and choose properties
- Change to the Service tab in the pop-up dialog
- Set the 'Start Mode' to automatic.
If you have SQL Server 2014 and earlier editions installed on the same machine, you do not need to repeat these steps in both versions of SQL Server Configuration Manager. There should only be one Browser service running on the machine.
The SQl Browser listens on port 1434, so make sure your firewall permits traffic on that port.
If your database server is not on the same machine as IIS, you will likely need to open some ports in your firewall on the SQL Server system. If you only have one instance of SQL server, just open TCP/IP Port 1433. If you have multiple instances, you should enable the SQL Browser service (see above) and also open TCP/IP Port 1434.
TicketDesk uses Entity Framework Migrations to manage, and potentially even create, its own database automatically.
For best results, the user account that TicketDesk uses to connect to the SQL server should be the db_owner for the TicketDesk database.
To create databases, this account must also have create database permissions at the server level; however, if that makes you (or your DBA) uncomfortable then you can manually create an empty database for TicketDesk to use.
The simplest way to manage security is to create a SQL Login for use with TicketDesk, and grant that user db_owner permissions to the database (and optionally give it create database permissions to the server). Then you specify the user name and password for this account as part of the connection string in the TicketDesk web.config file.
SQL Server must have been installed with the mixed mode security option in order to use SQL Logins.
To create a SQL Login that has create database rights:
- Open SQL Server Management Studio
- Connect to the SQL Server with a server admin account
- Right click the Security Node, choose New Login
- Under the 'General' section:
- Change the type to SQL Login
- Enter the login name and password
- (recommended) Uncheck the password expiration option
- Under the 'Server Roles' section
- Check the 'dbcreator' option
Again, you can skip adding the login to the dbcreator role, but you will need to manually create the TicketDesk database, and make your login the db_owner.
(coming soon)