Stage 1 in Practice: Windows Server 2008 R2 and SQL Server 2008 R2

A quick recap

Testing is under way, and so far the old lady is holding up. The application ran on Windows Server 2003, IIS 6 and SQL Server 2005 Enterprise. It has five web applications, five databases in scope, and no source code. The key design decision was to build the new server with the same machine name, SQL instance name and database names. That way, every reference in the configuration files, the XML login map and the subsystem registry table stays valid without changing the application.

The target for Stage 1 was Windows Server 2008 R2 with SQL Server 2008 R2 Express. This isn't the final home. It's the step that upgrades the databases to the SQL 2008 format, which is what the move to SQL Server 2025 in Part 3 depends on.

Building the new server

We built the server offline, so it could have the same name as the live server without a conflict on the network:

• Windows Server 2008 R2, same computer name, workgroup (like the original). Patching a fresh 2008 R2 install up to its final updates takes ages. Plan for that, and for a few coffees.
• .NET Framework 3.5.1 as a Windows feature.
• SQL Server 2008 R2 Express with Tools (SP2), same named instance, same Hungarian collation as the old server (set during installation), and Mixed Mode authentication.
• TCP/IP enabled and SQL Server Browser started. Express turns TCP/IP off by default, but the application requires it.

Before going ahead with Express, we ran sys.dm_db_persisted_sku_features on every database. The result was empty, so nothing Enterprise-only would block the restore.

Issue 1: the restore that stopped at 90%

The first restore failed with error 3183, reporting a problem on page (0:0). That isn't a real data page. It almost always means the backup file itself is damaged or incomplete, not the database. The error also named a different database from the one we thought we were restoring, which pointed to a mixed-up or appended backup file.

The fix was simple and is worth making a habit:

BACKUP DATABASE [AppDb] TO DISK = 'D:\mig\AppDb.bak'
   WITH INIT, FORMAT, CHECKSUM, STATS = 10;
RESTORE VERIFYONLY FROM DISK = 'D:\mig\AppDb.bak' WITH CHECKSUM;

INIT, FORMAT makes sure each file contains exactly one backup set, and CHECKSUM plus VERIFYONLY confirm the file is readable before you depend on it. Comparing file hashes (certutil -hashfile) on both servers rules out a bad copy. After a fresh, verified full backup, all five databases restored cleanly.

We kept the databases on their original compatibility level, so their behaviour stays the same. Raising it is a separate step later.

Choosing the IIS components from evidence, not guesswork

Instead of ticking every IIS role service, we worked out what was needed from what the application actually uses:

We copied the web root and the configuration files over unchanged, so the existing handler registrations for the third-party controls came across intact.

Issue 2: “Unrecognized attribute 'targetFramework'”

The main application failed straight away with a configuration error. The targetFramework attribute only exists in .NET 4.0 and later. This one application had been built for .NET 4, while the others ran on .NET 2.0. On the old IIS 6 server this had been set per application, and of course there was no documentation to mention it.

The fix was on the server side, with no changes to the configuration:

• Install .NET Framework 4.8 (.NET 3.5.1 only provides the 2.0 runtime).
• Register ASP.NET 4 with IIS: aspnet_regiis -iru (both 32-bit and 64-bit).
• Create a separate application pool: .NET v4.0, Classic pipeline, 32-bit enabled.
• Allow ASP.NET v4.0 under ISAPI and CGI Restrictions.

Lesson: an application pool runs one runtime version. Check each application (findstr /S /I targetFramework *.config) instead of assuming they all match.

Issue 3: the password that was “wrong” even after a reset

The login page loaded, but valid users got “unknown username or password”. This one took some careful digging, and to be honest, it was partly my own doing.

1. Make the failure visible. At first, there was nothing in the SQL Server log. With login auditing switched to both failed and successful logins, error 18456, state 8 appeared: the login exists, but the password doesn't match. It also showed the request was coming from the web server over TCP, so the IIS and network side was working.
2. Understand why a reset didn't help. Earlier, while the web-to-SQL connection still wasn't working, I'd reset a password in Management Studio to rule that out. Classic move. The reset worked in Management Studio but not in the application. The reason: the application doesn't send the password exactly as the user types it. It creates and uses its SQL logins in its own format. A reset in Management Studio stores the plain-text version, which the application never sends.
3. Fix it at the source. We copied every SQL login from the old server with Microsoft's sp_help_revlogin, which keeps the original password hash and SID. With the original hashes, logins worked immediately. With the original SIDs, the database users linked up without any orphaned-user fixes.

Lesson: with legacy applications that manage their own SQL logins, don't reset passwords outside the application. Migrate the hashes.

Result

The application now runs on Windows Server 2008 R2 and SQL Server 2008 R2, with no changes to its code or connection settings. The old Windows Server 2003 machine is switched off but intact, so we can roll back in minutes if needed. User acceptance testing is next.

Windows Server 2008 R2 and SQL Server 2008 R2 are out of support too. This stage was always meant as a stepping stone: it gets the databases into the SQL 2008 format that newer SQL versions can restore directly.

Key takeaways

• Back up with CHECKSUM and verify before every migration restore.
• Check the .NET version of each application, not just the server.
• Choose IIS components based on what the application actually uses.
• If logins fail, turn on login auditing before changing anything.
• Migrate SQL logins with their original hashes and SIDs, and never “fix” passwords by resetting them.

Coming next

In Part 3, we finish the journey: moving to Windows Server 2025 and SQL Server 2025 Express, a supported platform, and planning the compatibility level upgrade.