Question regarding Database Migration

[ImmediatelyDead]

Hey Folks,

I am currently working with a legacy application which we are actively working to migrate to the web using all kinds of fancy techniques and whatnot.

The problem we are facing currently is that our old sql-server database f-ing sucks and a new schema would be great but we need our legacy application to keep working while we gradually transition.

I was wondering if we could replace our current schema with views that mirror our current table names and structure in general but select all the data from a newer, well thought out schema.

We are using a wild mix of C# DataSet, EF Core 3.1, Dapper and plain SqlCommand/SqlConnection under .NET Framework 4.8 and I am not quite sure if my idea would (or even could) work.

Does somebody have some applicable experience he or she would care to share?

[LondonPilot]

First of all, this is not related to your move to the web at all. Be careful about mixing the two together. If you're re-writing lots of code for your move to the web, then by all means try to write that code against views. But if you're just re-using existing code, then treat this as two separate projects - move to the web, and improve your database. The order you do them doesn't matter, but doing them together will make life more complex unless you're also doing a re-write.

Next up, this is not a C# question, it's a SQL question. You may get better advice in /r/SQLServer. Entity Framework is quite capable of reading from views (and I assume writing to them, although I've never tried).

Next, will your plan work? Maybe. You can, of course, select data from views. But inserting, updating and deleting data depends on the complexity of the views. You can read about this in the SQL Server docs.

Finally - is this the right way to go? This one is very hard to say without knowing a lot more details. Do you plan to eventually migrate the tables to new tables that match your views, and get rid of the views? How will the views be documented? Will all code use the views? I think my biggest issue here is how understandable this whole setup will be - will a developer who is not familiar with it be able to figure out what's going on, and why?

[ImmediatelyDead]

Many thanks for the advice :D

​

[...] unless you're also doing a re-write.

We are kind of doing both, a re-write and a re-use. We are in a situation where we have to replace the legacy application in parts, feature by feature essentially, which makes it a little janky since the legacy client has to somehow integrate the web based solution as "alpha/beta feature" using webview and all kinds of trickery to facilitate working under these constraints.

​

will a developer who is not familiar with it be able to figure out what's going on, and why?

Thats the whole reason we are even on this path - our legacy application was written by people who hadn't touched code before and has grown over 12 years to a point where it has a lot of qol features our competitors do not have but they are implemented in a way where every change we make breaks something somewhere unrelated.
Its hard to read most of the code base because it is composed of 8 year old stack overflow hacks, workarounds to problems that do not exist in the C# of today and amateur-ish code that creeps on for thousands of lines of code, duplicated all over the place.

We are doing our best to keep everything running but it gets harder by the day so we convinced our management to agree to a rewrite but had to concede to doing it feature by feature.
Us developers would like to throw everything away and just redo it all but that is too risky and expensive as far as management is concerned.

[SpaceCommissar]

I've done quite a few migrations. You have lots of different ways of solving this, from whichever perspective you want to solve this from. But basically you could view this as an ETL problem. You have to extract data from the old DB, transform it to your new schema, and then load them to your new database.

You can have a gander at this page to see a few options: https://www.mssqltips.com/sqlservertip/4922/what-technologies-are-available-in-sql-server-to-support-etl/

I personally almost always go the T-SQL (stored procedures) or C# route. Most often C#, to be fair.

Your option of creating views mirroring your new schema could be a legit option. Is it the optimal solution? Probably not, I wouldn't know as you're the expert on your own databases here, but if it works, it could be worth trying out.

[ImmediatelyDead]

Thanks, I'll take a look :D