How to migrate your Space database from LocalDB to SQL Server

Descripción general

This guide provides step-by-step instructions for migrating your Space database from LocalDB to a full SQL Server instance. This migration is necessary to ensure compatibility with Space 6.13 and later versions, which no longer support LocalDB.

Starting with Space 6.13, LocalDB is no longer supported. This change aligns with Microsoft's end-of-support timelines and allows Space to use more modern, secure, and performant database technologies.

Prerequisites

Before starting the migration, ensure you have:

• Administrative access to the Space server machine
• Sufficient disk space for database backup and new SQL Server installation
• The Space service stopped during the migration process
• A maintenance window for the migration (recommended during off-peak hours)

Migration steps

Step 1: Install SQL Server

1. Download SQL Server:

   • Visit the Microsoft SQL Server downloads page
   • Download SQL Server 2022 Express (free) or a newer version, such as SQL Server 2025 Express

2. Install SQL Server:

   • Run the installer as Administrator
   • Choose Basic installation for a quick setup, or Custom for more control

   For Basic installation (recommended for most users):

   • Accept the license terms
   • Choose installation location
   • The installer will configure default settings automatically

   For Custom installation:

   • Select the following features (minimum required):
     • Database Engine Services
     • SQL Server Replication (optional but recommended)
     • Client Tools Connectivity
   • Configure the instance:
     • Use Default instance (recommended) or create a Named instance
     • Set Mixed Mode authentication
     • Create a strong password for the sa account
     • Add your Windows account as a SQL Server administrator

3. Note the instance name:

   • Default instance: Use localhost or your computer name for connection
   • Named instance: Use localhost\InstanceName for connection
   • Write down this information for Step 4

4. Install SQL Server Management Studio (SSMS) (optional but recommended):

   • Download SSMS from the Microsoft website
   • Install for easier database management and troubleshooting

Step 2: Create a database backup

1. Stop the Space service:

   • Stop the Space service from the ProAccess Space Configurator

2. Create a backup of your current LocalDB database:

   • Open Command Prompt as Administrator
   • Navigate to your Space installation directory (typically C:\SALTO\ProAccess Space)
   • Run the backup command:
   cmd

   sqlcmd -S "(localdb)\MSSQLLocalDB" -E -Q "BACKUP DATABASE [SpaceDB] TO DISK='C:\Temp\SpaceDB_Backup.bak'"

3. Verify the backup was created successfully:
   • Check that the backup file exists at the specified location
   • Note the file size to ensure it's not empty

Step 3: Restore the database backup

1. Connect to the new SQL Server instance:

   • Open SQL Server Management Studio or use sqlcmd
   • Connect using Windows Authentication (recommended) or the sa account

2. Restore the database:

   Using SQL Server Management Studio (easier method):

   • Right-click on Databases in Object Explorer
   • Select Restore Database…
   • Choose Device and click the … button
   • Click Add and browse to your backup file (C:\Temp\SpaceDB_Backup.bak)
   • The database name should auto-populate (typically SpaceDB)
   • Click OK to restore

   Using Command Line (alternative method):

   cmd

   sqlcmd -S "localhost" -E -Q "RESTORE DATABASE [SpaceDB] FROM DISK='C:\Temp\SpaceDB_Backup.bak'"

3. Verify the restore was successful:
   • In SSMS, refresh the Databases folder
   • You should see your SpaceDB database listed
   • Expand the database to ensure tables are visible

Step 4: Install Space 6.13

1. Download Space 6.13:

   • Obtain the installer from the software area

2. Run the Space installer:

   • Execute the installer as Administrator
   • Follow the installation wizard
   • When prompted for database configuration, select Use existing SQL Server database

3. Configure database connection:

   • Server name: localhost (or your server name/instance)
   • Database name: SpaceDB (or your database name)
   • Authentication: Use Windows Authentication or SQL Server Authentication
   • Test the connection before proceeding

Step 5: Verify settings

1. Verify database connection settings:

   • Open the ProAccess Space Configurator
   • Go to the Database tab
   • Ensure the connection points to your new SQL Server instance
   • Test the connection

2. Verify Space is working:

   • Open a web browser
   • Navigate to your Space web interface (typically http://localhost:8100)
   • Log in and verify that your data is intact

Post-migration verification

After completing the migration, verify the following:

• Space service starts successfully
• Web interface is accessible
• User accounts and permissions are intact
• Device configurations are preserved
• Audit trails and historical data are available
• All Space functionality works as expected

Troubleshooting

Common issues and solutions

Database connection fails:

• Verify SQL Server service is running
• Check firewall settings (SQL Server typically uses port 1433)
• Ensure the Space service account has appropriate database permissions

Space service won't start after migration:

• Check Windows Event Log for detailed error messages
• Verify database connection settings in Space Configurator
• Ensure the Space service account has proper permissions

Performance issues after migration:

• Update SQL Server statistics: UPDATE STATISTICS
• Consider rebuilding indexes if the database is large
• Verify adequate system resources (RAM, CPU)

Cleanup

After successful migration and verification:

1. Remove LocalDB backup files (if no longer needed)
2. Uninstall LocalDB instance (optional, to free disk space)
3. Document the new database connection details for future reference

Common questions

• "I'm not sure what my LocalDB database name is": Check your Space Configurator settings under the Database tab
• "The backup command isn't working": Verify that LocalDB is running: sqllocaldb info MSSQLLocalDB
• "I can't connect to the new SQL Server": Try using .\SQLEXPRESS instead of localhost if you installed a named instance