LocalDB to SQL Server

OptimiDoc ships with SQL Server LocalDB so it runs out of the box with no separate database to install. LocalDB is fine for a pilot or a small site, but it is single-machine, size-limited and unattended-maintenance-free — which makes it unsuitable for production at scale or for a multi-node cluster. This guide explains how OptimiDoc uses its database and walks through migrating from LocalDB to a full SQL Server (or SQL Server Express) instance.

How OptimiDoc Uses the Database

OptimiDoc stores everything except spooled files in SQL Server: users, groups and departments; devices, rules and connectors; job metadata, accounting and reporting data; audit logs; and (because sessions are database-backed) user sessions. Two points matter for this migration:

• The connection string lives in web.config. The main database is configured by the connection string named OptimiDoc.Dao.OptimiDocDbContext. Out of the box it points at LocalDB with the catalog optimidocX (file optimidocX.mdf in App_Data). Migrating simply means restoring that database onto SQL Server and repointing this connection string.

• Quotas use a second database. If you use print quotas, OptimiDoc keeps them in a separate database configured by the connection string named OptimiDoc.Dao.QuotaDbContext (default catalog optimidocQ). The presence of that connection string is what enables the quota feature. If you rely on quotas, migrate this database too — see Step 8.

OptimiDoc maintains its own performance indexes through Entity Framework migrations, which run automatically when the application starts against the new database — you do not create them by hand.

Why Migrate?

Recommendation: migrate to SQL Server when your environment grows beyond a couple of dozen devices or a few hundred users, when you need scheduled backups and maintenance, or when you plan a multi-node cluster.

Prerequisites

• SQL Server installed and running. SQL Server Express (free) suits most sites; Standard or above is recommended for large deployments.

• SQL Server Management Studio (SSMS) for the backup/restore and permission steps.

• Administrator access to both the OptimiDoc server and the SQL Server.

• Sufficient disk space on the SQL Server (check the current LocalDB file size first).

Step 1 — Install SQL Server

If SQL Server is not already available:

1. Download SQL Server Express (or use a licensed Standard/Enterprise instance).

2. Run the installer and choose Basic or Custom installation.

3. Note the instance name (default: SQLEXPRESS).

4. Choose Windows Authentication or Mixed Mode as appropriate for your environment.

5. In SQL Server Configuration Manager, enable TCP/IP if OptimiDoc and SQL Server are on different machines.

6. Open port 1433 in the Windows Firewall if the SQL Server is remote.

Step 2 — Stop the OptimiDoc Application

1. Open IIS Manager, select the OptimiDoc site and click Stop.

2. Open the Services console and stop the OptimiDoc Service as well, so nothing writes to the database mid-migration.

3. Wait for active requests and jobs to finish.

Step 3 — Back Up the LocalDB Database

The LocalDB database files are in the OptimiDoc App_Data folder (for example C:\inetpub\wwwroot\OptimiDoc\App_Data), named optimidocX.mdf / optimidocX.ldf.

Method A — Backup and Restore (recommended)

1. In SSMS, connect to the LocalDB instance using the server name (LocalDb)\v11.0 (or (localdb)\MSSQLLocalDB, depending on your LocalDB version).

2. Right-click the optimidocX database and choose Tasks > Back Up.

3. Create a Full backup to a .bak file in a location the target SQL Server can read.

Method B — Detach and Attach

1. Detach the database from LocalDB.

2. Copy the .mdf / .ldf files to the SQL Server data directory.

3. Attach the database on the target SQL Server instance.

Step 4 — Restore to SQL Server

Using Method A:

1. In SSMS, connect to the target SQL Server instance.

2. Right-click Databases and choose Restore Database.

3. Select Device, browse to the .bak file from Step 3, and confirm the destination database name (keeping optimidocX avoids extra edits, but you may choose your own).

4. Click OK and verify the database appears with all its tables.

Step 5 — Configure Database Permissions

1. In SSMS, expand Security > Logins on the SQL Server instance.

2. Create a login for the account OptimiDoc runs as:

   • Windows Authentication: add the IIS application-pool identity (for example IIS AppPool\OptimiDoc) and the OptimiDoc Service account.

   • SQL Authentication: create a SQL login with a strong password.

3. Map the login to the restored database with the db_owner role.

Step 6 — Update the Connection String

1. Open web.config in the OptimiDoc web application root.

2. Find the connection string named OptimiDoc.Dao.OptimiDocDbContext.

3. Replace its connectionString value with one that points at SQL Server, keeping providerName="System.Data.SqlClient":

Windows Authentication:

Server=<sql-server>\SQLEXPRESS;Initial Catalog=optimidocX;Integrated Security=SSPI;

SQL Authentication:

Server=<sql-server>\SQLEXPRESS;Initial Catalog=optimidocX;User Id=optimidoc_user;Password=<password>;

4. Remove the LocalDB-specific AttachDBFilename=|DataDirectory|\optimidocX.mdf part — it does not apply to a full SQL Server instance.

5. Save web.config.

Step 7 — Start and Verify

1. Start the OptimiDoc Service and then the OptimiDoc site.

2. Browse to the console (it redirects to /ui) and log in as administrator. On first start, OptimiDoc applies any pending migrations to the new database automatically.

3. Confirm:

Step 8 — Migrate the Quota Database (If You Use Quotas)

If print quotas are in use, repeat the backup/restore for the quota database:

1. Back up the optimidocQ LocalDB database and restore it onto the same SQL Server (Steps 3–4).

2. Grant the OptimiDoc login db_owner on it (Step 5).

3. In web.config, add or update the connection string named OptimiDoc.Dao.QuotaDbContext to point at the restored optimidocQ database (same server, same authentication style as the main connection).

4. Restart OptimiDoc. Quota migrations run automatically; confirm quota balances and history are intact under the quota pages.

If you do not use quotas, there is no optimidocQ database to move and no OptimiDoc.Dao.QuotaDbContext connection string to add — skip this step.

Step 9 — Set Up Database Maintenance

Now that you are on SQL Server, configure regular maintenance (SQL Server Express has no Agent, so use Windows Task Scheduler with sqlcmd there):

• Full backup — daily, retained for at least seven days (back up both optimidocX and, if present, optimidocQ).

• Index rebuild — weekly, out of hours.

• Update statistics — weekly, after the index rebuild.

• Transaction-log backup — every 30 minutes if you use the Full recovery model.

Troubleshooting

In Summary

Migrating OptimiDoc off LocalDB is a database restore plus a connection-string change: back up the optimidocX database, restore it onto SQL Server, grant the OptimiDoc accounts db_owner, and repoint the OptimiDoc.Dao.OptimiDocDbContext connection string in web.config. If you use quotas, do the same for the optimidocQ database and its OptimiDoc.Dao.QuotaDbContext connection string. OptimiDoc applies its schema and index migrations automatically on first start, and a full SQL Server unlocks scheduled maintenance, remote access and clustering.

Related articles

• General Upgrade

• Migration to a New Server

• Failover Architecture

• Database and Performance Issues

• Upgrade and Migration