Migrate SQL database

## SQL database migration to SQL Server 2022 – what you can do and how to troubleshoot

You’re on v24.4.60 b83, which is good (latest branch). Moving the DB to SQL 2022 is technically fine, but the service not starting means either:

- DB is not reachable / permissions issue, or
- The connection details in SysAid are not fully correct, or
- The DB was not moved/restored cleanly.

Below is a practical procedure plus checks to isolate the problem.

---

## 1. Recommended migration procedure (step-by-step)

This is a standard, supported way to move the SysAid DB between SQL Servers.

### A. Preparation

1. **Confirm SQL 2022 is ready**
- Ensure SQL Server service is running.
- Ensure TCP/IP is enabled in SQL Server Configuration Manager.
- Confirm the port (default 1433 or custom).
- Ensure firewall allows inbound on that port.

2. **Permissions for SysAid DB user**
- The SQL login used by SysAid should have at least:
- `db_owner` on the SysAid database.
- If you’re changing the SQL login (username/password) on the new server, note the new credentials.

3. **Full backup on old server**

In SQL Server Management Studio (SSMS):

- Right‑click SysAid DB → **Tasks → Back Up…**
- Type: **Full**, destination `.bak`.
- Run and verify backup completes successfully.

### B. Restore to the new SQL 2022 server

4. **Restore**

On the new SQL server in SSMS:

- Right‑click **Databases → Restore Database…**
- Source: **Device** → select the `.bak`.
- Ensure the database name is exactly the same as before (recommended).
- Check **Files** tab to ensure data/log paths are correct.
- Run restore.

5. **Fix user mapping (if needed)**

If you’re using the same SQL login name:

- In SSMS: **Security → Logins → [your SysAid login] → User Mapping**
- Map to the restored SysAid DB and set role `db_owner`.

If the login didn’t exist yet:
- Create login, then map it as above.

---

## 2. Point SysAid to the new SQL server

This is where most issues happen.

6. **Stop SysAid service**

On the SysAid application server:

- Open `services.msc`
- Stop **SysAid Server** (or similar service name).

7. **Edit `serverConf.xml`**

- File path (default):
`C:\Program Files\SysAidServer\root\WEB-INF\conf\serverConf.xml`
(Path may differ but always under `SysAidServer\root\WEB-INF\conf`.)
- Locate DB properties (examples):

```xml
jdbc:sqlserver://OLD_SQL_SERVER:1433;databaseName=SysAidDB;encrypt=false
sysaid_user
ENCRYPTED_OR_PLAIN_PASSWORD
```

- Update:
- `OLD_SQL_SERVER` → **new SQL server name or IP**
- Port, if not default.
- `databaseName` if you used a different name (recommended to keep same).
- Username / password if they changed.

- Make sure:
- No extra spaces or unsupported characters.
- If you changed to encrypted connections on SQL 2022, check the JDBC URL parameters (e.g., `encrypt=true;trustServerCertificate=true` if using self-signed).

8. **Test connectivity from the SysAid server**

From the SysAid app server:

- `ping NEW_SQL_SERVER`
- If using hostname, confirm DNS resolves.
- Optionally run:

```powershell
Test-NetConnection NEW_SQL_SERVER -Port 1433
```

- If this fails, it’s a **network/firewall** issue, not SysAid.

9. **Start SysAid service**

- Start **SysAid Server** again.
- If it stops immediately, go to log checks.

---

## 3. Where to check logs to identify the problem

With the service not starting, the logs are critical.

10. **Check SysAid logs**

Typical locations:

- `SysAidServer\root\WEB-INF\logs\server.log`
- `SysAidServer\root\WEB-INF\logs\wrapper.log`
- Sometimes Tomcat logs under `SysAidServer\tomcat\logs\`.

Look for messages right after startup such as:

- `Cannot open database`
- `Login failed for user`
- `The TCP/IP connection to the host ... has failed`
- `com.microsoft.sqlserver.jdbc.SQLServerException: ...`

These messages will usually tell you exactly whether it’s:
- Bad hostname / port.
- SQL login/password error.
- Database is offline / not accessible.
- SSL / encryption issue.

11. **Check SQL error log / SSMS**

On SQL 2022:

- Open **Management → SQL Server Logs**.
- Look for:
- `Login failed for user 'sysaid_user'` (wrong password or no mapping).
- Permissions issues.
- Connection refused / TLS mismatch.

---

## 4. Common pitfalls specific to SQL Server 2022

On newer SQL versions, you may hit:

1. **Encryption/TLS issues**
- If SQL is configured to require encryption, but JDBC URL has `encrypt=false`, connection may fail.
- Try parameters like:

```xml
jdbc:sqlserver://NEW_SQL_SERVER:1433;databaseName=SysAidDB;encrypt=true;trustServerCertificate=true
```

- Adjust according to your security policy.

2. **Different authentication mode**
- Ensure SQL Server is set to **Mixed Mode** if you are using SQL authentication.
- Confirm the login is not locked or disabled.

3. **Database compatibility level**
- After restore, in SSMS:
DB → Properties → **Options** → **Compatibility level**.
If you experience strange behavior, you can set it back to SQL 2019/2017 for testing, but usually not required just to connect.

---

## 5. If you want an alternative migration approach

If you suspect the backup/restore is the issue, you can:

- **Detach/attach approach**

On old SQL server:
1. Stop SysAid service.
2. In SSMS: DB → Tasks → **Detach**.
3. Copy MDF and LDF files to new server.

On new SQL server:
4. In SSMS: Databases → **Attach…**, pick MDF, ensure LDF resolves.
5. Re-map SysAid login as `db_owner`.
6. Update `serverConf.xml` as above and start service.

Functionally it’s similar to backup/restore, but sometimes clearer for DBAs.

---

## 6. What I need from you to help pinpoint the issue

If you want more targeted help, please share (sanitized as needed):

1. The **exact DB-related entries** from your `serverConf.xml` (mask username/password).
2. The **exact error** from:
- `server.log` or `wrapper.log` around the time the service tries to start.
3. Whether from the SysAid server you can:
- Ping the SQL 2022 host.
- Reach the SQL port (e.g., using `Test-NetConnection` or `telnet`).

With those, I can help you narrow down the precise cause (connection, authentication, or configuration) and suggest the exact change to make.

If after these steps the service still doesn’t start and the logs show an unclear or severe DB error, you should open a Service Record with SysAid Customer Care so they can review your logs and configuration directly.