migrating super old app databases

Recently, a colleague asked me to assist with the migration of some older, customized application databases to a SQL Server 2017 instance. Most of the migrations I perform are rather vanilla, but this one was a bit more involved.

Setup

Imagine this scenario:

Source (APPSQL1)

  • Dedicated SQL Server 2008 R2 failover clustered instance with a non-default collation (SQL_Latin1_General_CP1_CI_AI)
  • Custom .NET application with an intense database containing a non-default collation (also SQL_Latin1_General_CP1_CI_AI), multiple CLR assemblies, and thousands of tables, views, stored procedures, functions
  • Nearly 30 SQL Agent jobs, many of which had to be disabled over the years due to compatibility issues and scope changes
  • Out-of-support for both Microsoft and the application vendor

Destination (APPSQL2)

  • Shared server
  • SQL Server 2017
  • Default collation (SQL_Latin1_General_CP1_CI_AS)

There was even a linked server in the mix, but our biggest concerns revolved around the changing collation and the Agent jobs, which were known to be brittle.

The destination test server was an existing shared server, which mirrored the scenario that would play out in production. And while the databases only needed to exist on the new server for a limited period of time, these migrated databases were going to be the most important databases on the entire instance. This meant that the SQL Server configs were going to have to cater to this app’s needs. One exception was the collation, as the accent sensitivity was determined not to be a big deal and the vendor agreed.

Interesting requirements, no doubt!

Prep

Fortunately, my colleague kept a login inventory, and we used this to determine that there would be 5 servers with connection strings that’d have to be updated with the new server name. Generally, I try to see if creating SQL client aliases is a suitable solution and this was no exception. Creating SQL aliases satisfies my curiosity and can let us know early on if the migration was a success.

This blog post refers to options added in 1.0.34. If you’d like to follow along, please ensure you’ve updated to the latest version of dbatools.

So let’s take a look at the prep work.

Since the old server was going to be turned off entirely, I thought it’d be a good idea to export all of the objects (logins, jobs, etc) so that the DBA could see the state they were in at the time of migration. While we were at it, we threw in the destination server as well.

Once the final log backup was made, it was time to perform the migration!

Migration

Because the destination server was a shared server, it wasn’t a likely candidate for Start-DbaMigration as this command is intended for instance-to-instance migrations. In general, when I migrate to shared servers, I’ll run each individual Copy-Dba command for things like databases, logins and jobs.

In this case, however, I recommended Start-DbaMigration since the application was high-priority and I wanted the migration to be as thorough as possible. I knew that the SQL Server configuration values would change, but because they were exported when we ran Export-DbaInstance, we could easily reference old settings.

I did decide to exclude a few things that could needlessly pollute the new server like user objects in the system databases (Copy-DbaSysDbUserObject) and I also excluded the Agent Server Properties, because the destination server properties (like fail-safe operator) were already set.

Post-migration

So the migration went decently well. While we didn’t have to use the fallback commands, we did have to investigate a couple failures.

Failed job migration

A couple jobs didn’t migrate because they were rejected by the new server. Seems that scripted export code referenced “server=”, which was invalid because it contained the name of the old server.

The above code resulted in the T-SQL CREATE scripts being added to the clipboard. We then pasted that code into SSMS, performed a find/replace for @server=N’APPSQL1′ with @server=N’APPSQL2′ then executed the T-SQL. Boom, it worked. Jobs were created and scheduled. I also updated dbatools to avoid this failure in the future.

While I used SSMS to perform the find/replace, this could have been done in PowerShell as well.

Now we needed to run each of the newly created jobs to see if they work, as it make me feel more confident in the migration’s success. After evaluating each job’s purpose, we determined it would be okay to run every job outside of their scheduled execution times.

Nooo, all the failed jobs ran successfully on the APPSQL1! Now we’d have to dig into the code to see what code is failing and how it can be fixed. I imagined the failures were due to collation issues and I was right. Good ol’ Cannot resolve the collation conflict between….

Maybe we can update the collation in the databases? I recall years ago someone asked for this functionality in dbatools but thoroughly changing the collation of an existing database is so complicated as it requires changing the collation of so many objects including tables and indexes.

Let’s try changing the database collation anyway, just to see if it’s possible and if it helps. This was mostly to satisfy my curiosity.

Nope. Our attempted collation change using SSMS didn’t even work because there were some dependent features in a couple of the databases. So now we had to find and edit the impacted views.

We decrypted the views then added COLLATE DATABASE_DEFAULT to some queries, altered the views and voila! We were set. After the jobs ran successfully, we handed the migration off to the application team.

SSPI failures

The application team immediately handed it right back to us 😅. Seems they encountered some “Cannot Generate SSPI Context” failures. Wait, what? The SPNs are set, right?

Oh, no: our dbatools connection was also using NTLM. So we checked to see if there were some stale DNS records. Nope. Cleaned out the Kerberos tickets. Works? Still no. Triple checked with Microsoft’s tools and really, Kerberos should be working.

Turns out it was an issue with a setting in Windows (didn’t record which, oops), and after that was adjusted, Kerberos worked!

Now for the application

After confirming that Kerberos was totally working, I removed the newly created SQL Client aliases and they updated all of the connection strings. Still there were problems. The error message was written in en-gb (“initalised”) so I figured it was an application error. If this was an error coming from SQL Server or IIS, it would have been written in en-us.

We figured that if the error is referencing database initialization, there must be some config table that has the name of the server. What a nightmare. Now we have to search through the databases, guessing at the potential name for this configuration object.

Oh, la la! We found a candidate, updated the values and we were back in action! The migration was successful and the test results were accepted. With this knowledge, they were able to quickly perform a the production migration.

Out of curiosity

What was your most challenging migration with dbatools like? How often do you have to modify jobs and database objects like stored procedures/views?

- Chrissy 🚀

2 thoughts on “migrating super old app databases

  1. Garry Bargsley Reply

    We did a migration once where we did not know column level encryption was in use. So the night of the migration no one knew the password to the cert/key and we could not encrypt/decrypt this column.

    Needless to say the application would not function correctly.

    Luckily, my boss was online and he wipped up a script to decrypt the data on the old server and re-encrypt on the new server.

    So he saved the day and I did an audit the following week to make sure we knew where encryption was in use.

    • Chrissy LeMaire Post authorReply

      Close call! What did your boss use to decrypt the data, just straight T-SQL or a mix of PS and T-SQL? What did you use for the audit?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.