1

I have a .NET Core 3.0 MVC website with Identity. The database is a Postgresql database (mainly because of better performance with geographic data). But since the only other person that can work with postgresql has quit, i have to migrate to SQL Server (because of internal policy).

But there isn't much information on the big web on this specific migration.

I got a few ideas but since setting up a test takes quite some time, I wanted to check here first.

  • Is it just a matter of copying all tables between the databases (Copy/export data - change connectionstring - people won't even notice the change)?
  • write a small script using entity framework, copying all users to the new database with a default password - users have to change password on first login
  • people have to re-register
  • a combination of the above

EDIT: the problem is not the tables and data types but my concern is the passwords and the hashes. Can I just copy all the values to the SQL database and can people just log in?

There is a password hash in the table and I was thinking it maybe used other variables like the database engine to create the hash.

Improve this question
5
  • 1
    Does this answer your question? How to migrate a PostgreSQL database into a SQLServer one? Commented Feb 10, 2020 at 8:16
  • 1
    If the application is build on the same stack. Dot net core in your case lets say with Aspnet Identity, then the hashes can be migrated with no issue at all. Everything in handled by dotnet and it is not bound to the underlying datastore. Create the schema and populate it and you will be good to go. No need to rehash or make your users chage their passwords. Just move the data Commented Feb 10, 2020 at 8:39
  • @cleftheris Thanks for your response. I'm going to test this, probably next week. Still getting the hang of stackoverflow but i don't think i can mark your answer as correct (if it turns out it is correct) Commented Feb 12, 2020 at 13:35
  • @kenhas I moved the comment to an answer so you can mark it as correct if all goes well. Commented Feb 12, 2020 at 13:53
  • you can use my answer here: stackoverflow.com/questions/6563846/… Commented Dec 7, 2021 at 4:38

2 Answers 2

Reset to default
0

If the application is build on the same stack, lets say in your case Dot net core with Aspnet Identity, then the hashes can be migrated with no issue at all. Everything is handled by dotnet and it is not bound to the underlying datastore.

Create the schema and populate it and you will be good to go. No need to rehash or make your users change their passwords. Just move the data

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

migrating from postgresql to ms sql is a bit more work than i expected but copied everything and was able to log in using the new database. Thank
0

You will need to figure out which data types you are using in Postgresql and what their equivalents are on MSSQL. Most data types are same/similar while there might be a few where there is no direct equivalent.

There are lots of ways to move data between databases. One possible simple way in this case is to dump your postgres db using pg_dump. This will get you a text file with sql statements to recreate the database. Then you can modify the sql statements as necessary to work on your MSSQL database.

Improve this answer

1 Comment

By reading your answer, i see i have to change my question. It's not really about datatypes and tables but more about the passwords. The password is saved but also a hash. I can't find if this is always the same algorithm or if depends on other variables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.