November 2, 2023 at 3:06 pm
I've used the Import Wizard in SSMS a few times. Mostly to get data from one server to another. Occasionally making some transformations.
I may be involved in a new project to migrate an old PHP app to .NET. The app uses an old version of MySQL, version 5.1.41-community. I don't know, yet, if there will be any upgrading of the database. But in any case, I'm wondering if the Import Wizard in Management Studio can import the schema and data from that old MySQL database?
Kindest Regards, Rod Connect with me on LinkedIn.
November 2, 2023 at 3:54 pm
Not had to do that in a hot minute, but last time I did it was 90% painless.
As long as you have an appropriate driver to connect to MySQL you should be pretty much OK, maybe a few data types that don't want to port over nicely but it's possible with a bit of tweaking.
I would personally dump the MySQL DB to flatfiles/CSV and then recreate the schema, then I can make the needed schema changes and import the data all in code than the wizard so that I know I have done everything.
But any reason the .NET app can't just still use the MySQL DB, why does it need to be converted to MSSQL?
November 2, 2023 at 5:54 pm
To add to what Ant-Green suggests (dumping to flat files), that also would allow you to build a proper schema for it. I've seen some of the automated tools for importing set all columns to "NOT NULL" or character data come in as "VARCHAR(MAX)" when the column could better be suited in a smaller VARCHAR value (VARCHAR(25) for example) OR possibly even CHAR datatype instead of VARCHAR. Or sometimes it'll pull in character data as NVARCHAR when VARCHAR would have been a better choice.
If you are importing a flat file, you can build the schema up how it SHOULD be rather than letting automated tools do a best guess that MAY be wrong.
As for converting it from MySQL to MSSQL I can think of a few reasons for it (familiarity with syntax, tool preference, compatibility with 3rd party tools), but converting from PHP to .NET isn't one of those. I do personally prefer MSSQL over MySQL, but for a project of converting an application from A to B, I would put converting from MySQL to MSSQL as a "nice to have" not a requirement.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 3, 2023 at 2:36 pm
Ant-Green and Brian,
Personally, I'm in favor of leaving the data in the MySQL database, although it is (so I understand) an old version of MySQL. The arguments against leaving it in MySQL are that it is an old version of MySQL and our DBAs haven't any experience with MySQL. Also, I'm concerned the Entity Framework Core may not support the old version of MySQL involved. (I'm a software engineer, not a DBA.) There may even be a chance that it isn't being backed up. I don't know this for a fact, but it's a possibility. The argument to migrate it to MSSQL is our DBAs do know MSSQL.
I'm anticipating what I believe will likely happen. I've been involved in two or three upgrade/migration projects in this job. For some reason every one of those have involved redesigning the database. Certainly, if the old database is comprised of just a few tables with 500 columns each and not even in first normal form, then yeah I see the need to redesign it. However, the last one I was involved in, until the project died early this year, I realized that the old database schema really wasn't that bad. Nevertheless, those in charge wanted to redesign the database, before the first line of C# code was written. I'm just betting that this will be the idea going forward with this rewrite. It might be a good idea. I don't know the MySQL database that well, so perhaps a database schema redesign is a good idea. I just don't want to do it automatically, without considering the fitness of the current database design. If the current database design is acceptable, then I'd be in favor of just migrating it out of MySQL into MSSQL, where our DBAs have experience and regular maintenance can be performed.
Kindest Regards, Rod Connect with me on LinkedIn.
November 3, 2023 at 3:02 pm
To me that is a good argument to move it from MySQL to MSSQL. That is one of the use cases where I'd do the migration.
As for the database schema, even if it is "decent", I have found that automated tools err on the side of caution and you will end up with all columns being NULLable (which is not ideal) and you MAY have columns that are much wider than necessary. Plus, depending on how the old system was set up on MySQL, you MAY want to create new views and stored procedures or tune the stored procedures to behave nicer with MSSQL.
My opinion though, data migration from one database to another is more of a job for a DBA than a software engineer. The DBA should do the data export, do some analysis on the data and discussion with end users to determine correct datatypes and constraints and build up the structure. Once that is done, they should work with the developers in building good stored procedures. Having a software developer build stored procedures is how my company ended up with a few poorly performing stored procedures (cursors and while loops being big culprits). Or worse - you end up with applications that have hard-coded the SQL into them rather than calling stored procedures and you end up with a bunch of ad-hoc queries in the execution plan cache and very few reused execution plans which can make performance tuning tricks (like query store) a LOT more challenging...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 3, 2023 at 3:20 pm
I don't know who the original developers were of the old PHP app, but having the SQL strings in it with no stored procedures, is exactly how they did it.
I totally love the idea of letting the DBAs do this migration! Our DBAs tend to make maintenance plans, create new databases, migrate from servers, etc. But for some reason they're never involved in designing databases.
Kindest Regards, Rod Connect with me on LinkedIn.
November 3, 2023 at 8:58 pm
DBA's should be involved in database design (my opinion)... otherwise what is stopping a developer from making tables that are not normalized or using bad datatypes (TEXT for example)... Makes tuning a nightmare if you have to redesign the entire database to get the desired performance.
A DBA should be at the very least INVOLVED in the database design decisions so they can see what they need to do.
And no stored procedures would be something I'd look at fixing when moving from PHP to C# personally. More hoops to jump through when trying to figure out what the button in the app does (for example) because you load up the source code, find the button only to find you need to go to the database and look at the stored procedure. BUT if I need to normalize the tables in the stored procedure or archive some of the data (for example), I can update the stored procedure and the app will just run happily. If the code is in the app and I move old data to a historical table AND the app is unaware of this change, that data is as good as deleted until someone updates the app. Plus, the DBA or DB Developer can tune the stored procedure and as long as the input and output doesn't change, the app doesn't need to know about the changes. If the app is directly interacting with the tables, making changes to the tables can break the app and need you to rebuild the app.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 4, 2023 at 5:33 pm
I'm concerned the Entity Framework Core...
While EF has some nice advanced features, is there any reason why the bulk of the DB access cannot be done with Dapper and Stored Procedures? As a DBA I find Dapper is much less prone to causing problems.
Nevertheless, those in charge wanted to redesign the database, before the first line of C# code was written.
A refreshing change from those wanting to do code first! (Code first is nice for proof of concept, prototypes etc. but DB design is needed for production systems.)
November 5, 2023 at 2:59 pm
Ken,
No one here has any experience with Dapper. We could learn Dapper, but it would be additional time spent not developing while we're learning. And I've got a feeling my fellow developers would balk at choosing Dapper over EF. One guy, no longer with us, wanted us to use Petapoco. He was convinced it was superior to all other ORMs. That went nowhere.
Kindest Regards, Rod Connect with me on LinkedIn.
November 5, 2023 at 4:47 pm
No one here has any experience with Dapper.
Fair enough, the SQL generated by EF is a lot better than it used to be. It is just my experience that it is a lot easier to get a simpler framework, like Dapper, to produce good results even though the developers do need to be better at SQL. You might want to quickly read the following:
Welcome To Learn Dapper ORM - A Dapper Tutorial for C# and .NET Core
November 6, 2023 at 4:29 pm
Are there actually still places in the wild that implement database logic primarily with stored procedures in applications?
Don't get me wrong, I wish it were more of a thing, eliminating the aggravation of arguing about performance problems created by ORMs would be fantastic, but i've just never seen it other than extreme old legacy apps and pretty much every organization wants things to be developed much faster than they used to.
November 6, 2023 at 4:59 pm
Are there actually still places in the wild that implement database logic primarily with stored procedures in applications?
Yes. I would have thought this is the main use of Micro ORMs like Dapper. ie Call a stored procedure and just use Dapper to save a lot of typing in C# by mapping the SQL results to the objects.
November 6, 2023 at 6:00 pm
We have a policy at our company that the C# code must call stored procedures to get or manipulate any data. It gives us better tuning opportunities and allows us to test things on the database side during upgrades without needing to test all of the applications. If your application is talking to the database directly, any database upgrades, patches, and modifications require extensive testing of the applications to make sure you didn't introduce any problems with the upgrade or modification.
If it is all in the database, I can issue stored procedure calls before the upgrade and after and compare the performance and results. If they match or are improved by the new version, then I am safe going live. I will still often test 1 or 2 apps to make sure they can connect to the new version, but my testing can be done without involving the application teams. I did an upgrade of a SQL 2008 R2 instance to 2017 and was able to do the whole upgrade with only pulling in 1 or 2 other people for testing performance of their apps and reports rather than needing to pull in all application groups that touch the database. If I needed to do that, the upgrade would have taken a LOT more time to complete.
The other fun thing is our developers currently are not using any ORMs or EF - we do it all by hand, and we have a lot of SQL instances (30+ on our live systems, although we are working to reduce that number as we can consolidate the instances) and applications (30+). SOME are for 3rd party vendor tools and those are the nicest to upgrade as we just check the docs and if the version is supported, we plan the upgrade.
That is just one scenario as a DBA where I prefer having the applications call stored procedures rather than direct SQL. Other benefits are the SP's can often be shared between applications, so if a bug is found in one application due to the SP, it is a single point to fix it across all apps. And query tuning is a HUGE one. Ad-Hoc queries are best avoided if you care about SQL performance and I've read (and seen) how EF can result in a bunch of single-use ad-hoc queries hitting the plan cache...
Now, that being said, I think if you were trying to build an application that worked with multiple database platforms, then stored procedures may not be the best option, or may not even be an option (SQLite for example). BUT if I was designing an application for sale at the enterprise level, I would be looking to support the most popular database engines that are out there and as far as I know, MSSQL, Oracle SQL, MySQL, and PostgreSQL all support stored procedures, so I would make use of them.
Also, a little off topic, but I feel that the "rush to release" mentality that a lot of people have now results in buggy software. I would much rather build it right the first time and try to minimize technical debt rather than need to have a launch day patch just so it works as expected. I know I won't ever release a bug free app unless the app is 100% useless, but if I can minimize technical debt, then fixing the bugs becomes easier. To me, hard-coded SQL in my application is technical debt.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply